Oracle数据库 v$archived_log

v$archived_log详解

V$ARCHIVED_LOG视图描述了系统中已经归档的日志文件的相关信息。归档日志是ARCHIVELOG模式的一种，用来记录DML以及DDL对数据库中对象所做的更改，保护数据库以及实施重做数据库恢复。

V$ARCHIVED_LOG视图的主要用途是查看已经归档的日志的信息，确定要恢复的日志。可以直接执行：

#查询当前被归档日志的相关信息SELECT ARCHIVED, DELETED, SEQUENCE#, RESETLOGS_CHANGE#,
FLAGS, ARCHIVE_DATE, ARCHIVE_SCN, SESSION_INFO
FROM V$ARCHIVED_LOG;

V$ARCHIVED_LOG

displays archived log information from the control file, including archive log names.

An archive log record is inserted after the online redo log is successfully archived or cleared (name column is

NULL

if the log was cleared). If the log is archived twice, there will be two archived log records with the same

THREAD#

,

SEQUENCE#

, and

FIRST_CHANGE#

, but with a different name. An archive log record is also inserted when an archive log is restored from a backup set or a copy and whenever a copy of a log is made with the RMAN

COPY

command.

ColumnDatatypeDescription解释

RECID

NUMBER

Archived log record ID归档日志记录的唯一标识号

STAMP

NUMBER

Archived log record stamp时间戳，与

RECID

一起构成归档日志记录的全局唯一标识

NAME

VARCHAR2(513)

Archived log file name. If set to

NULL

, either the log file was cleared before it was archived or an RMAN backup command with the “delete input” option was executed to back up archivelog all (

RMAN> backup archivelog all delete input;

).归档日志文件名

DEST_ID

NUMBER

Original destination from which the archive log was generated. The value is

0

if the destination identifier is not available.归档生成的目标

THREAD#

NUMBER

Redo thread number数据库线程的编号。Oracle数据库可以由多个线程组成，每个线程负责处理特定的事务。

SEQUENCE#

NUMBER

Redo log sequence number归档日志的序列号。表示日志的生成顺序。

RESETLOGS_CHANGE#

NUMBER

Resetlogs change number of the database when the log was written重置日志序列号，用于标识新的一组日志

RESETLOGS_TIME

DATE

Resetlogs time of the database when the log was written日志重置的时间戳编号

RESETLOGS_ID

NUMBER

Resetlogs identifier associated with the archived redo log重置日志的时间戳

FIRST_CHANGE#

NUMBER

First change number in the archived log归档日志中第一个重做记录的重做号（Redo Log Sequence Number）

FIRST_TIME

DATE

Timestamp of the first change归档日志的创建时间

NEXT_CHANGE#

NUMBER

First change in the next log归档日志中最后一个重做记录的重做号

NEXT_TIME

DATE

Timestamp of the next change下一个归档日志的预计创建时间

BLOCKS

NUMBER

Size of the archived log (in blocks)归档日志的块数

BLOCK_SIZE

NUMBER

Redo log block size. This is the logical block size of the archived log, which is the same as the logical block size of the online log from which the archived log was copied. The online log logical block size is a platform-specific value that is not adjustable by the user.归档日志块的大小

CREATOR

VARCHAR2(7)

Creator of the archivelog:

ARCH

– Archiver process

FGRD

– Foreground process

RMAN

– Recovery Manager

SRMN

– RMAN at standby

LGWR

– Logwriter process触发归档的原因

REGISTRAR

VARCHAR2(7)

Registrar of the entry:

RFS

– Remote File Server process

ARCH

– Archiver process

FGRD

– Foreground process

RMAN

– Recovery manager

SRMN

– RMAN at standby

LGWR

– Logwriter process条目注册

STANDBY_DEST

VARCHAR2(3)

Indicates whether the entry is an archivelog destination (

YES

) or not (

NO

)显示条目是否是归档日志目标

ARCHIVED

VARCHAR2(3)

Indicates whether the online redo log was archived (

YES

) or whether RMAN only inspected the log and created a record for future application of redo logs during recovery (

NO

).显示是否归档(YES)或是否是RMAN恢复创建的记录(NO)

APPLIED

VARCHAR2(9)

Indicates whether an archived redo log file has been applied to the corresponding physical standby database. The value is always

NO

for local destinations.This column is meaningful on a physical standby database for rows where

REGISTRAR

=

RFS

:If

REGISTRAR

=

RFS

and

APPLIED

=

NO

, then the log file has been received but has not yet been applied.If

REGISTRAR

=

RFS

and

APPLIED

=

IN-MEMORY

, then the log file has been applied in memory, but the data files have not yet been updated.If

REGISTRAR

=

RFS

and

APPLIED

=

YES

, then the log file has been applied and the data files have been updated.This column can be used to identify log files that can be backed up and deleted. When used for this purpose, the value

IN-MEMORY

should be treated as if it were

NO

.显示归档日志已被应用到了physical standby中,对本地节点值为NO在物理standby中的意义：
REGISTRAR=RFS && APPLIED=NO,表示logfile已被接受并注册但没有应用
REGISTRAR=RFS && APPLIED=IN-MEMORY,表示在内存中已应用logfile,还没有更新到datafiles中
REGISTRAR=RFS && APPLIED=YES,表示已应用logfile并更新到datafiles

DELETED

VARCHAR2(3)

Indicates whether an RMAN

DELETE

command has physically deleted the archived log file from disk, as well as logically removing it from the control file of the target database and from the recovery catalog (

YES

) or not (

NO

)显示是否使用了RMAN DELETE命令物理删除了归档日志,是否在逻辑上从controlfile恢复目录移除(YES/NO)

STATUS

VARCHAR2(1)

Status of the archived log:

A

– Available

D

– Deleted

U

– Unavailable

X

– Expired归档日志状态

COMPLETION_TIME

DATE

Time when the archiving completed归档完成时间

DICTIONARY_BEGIN

VARCHAR2(3)

Indicates whether the log contains the start of a LogMiner dictionary (

YES

) or not (

NO

)显示是否是LogMiner数据字典包含日志的开始(YES/NO)

DICTIONARY_END

VARCHAR2(3)

Indicates whether the log contains the end of a LogMiner dictionary (

YES

) or not (

NO

)显示是否是LogMiner数据字典包含日志的结束(YES/NO)

END_OF_REDO

VARCHAR2(3)

Indicates whether the archived redo log contains the end of all redo information from the primary database (

YES

) or not (

NO

)显示primary端归档日志是否包含redo结束信息

BACKUP_COUNT

NUMBER

Indicates the number of times this file has been backed up. Values range from 0-15. If the file has been backed up more than 15 times, the value remains 15.显示文件被备份的次数(0-15),如果次数超出15,则保留显示为15

ARCHIVAL_THREAD#

NUMBER

Redo thread number of the instance that performed the archival operation. This column differs from the

THREAD#

column only when a closed thread is archived by another instance.执行归档操作的实例线程号,当关闭的线程被其他实例归档时值与THREAD#不同

ACTIVATION#

NUMBER

Number assigned to the database instantiation被实例分配的次数

IS_RECOVERY_DEST_FILE

VARCHAR2(3)

Indicates whether the file was created in the fast recovery area (

YES

) or not (

NO

)显示文件是否被创建到fast recovery ares(YES/NO)

COMPRESSED

VARCHAR2(3)

Reserved for internal use保留作内部使用

FAL

VARCHAR2(3)

Indicates whether the archive log was generated as the result of a FAL request (

YES

) or not (

NO

)显示归档日志是否为FAL请求产生的(YES/NO)

END_OF_REDO_TYPE

VARCHAR2(10)

Possible values are as follows:

SWITCHOVER

– Shows archived redo log files that are produced at the end of a switchover

TERMINAL

– Shows archived redo log files produced after a failover

RESETLOGS

– Shows online redo log files archived on the primary database after an

ALTER DATABASE OPEN RESETLOGS

statement is issued

ACTIVATION

– Shows any log files archived on a physical standby database after an

ALTER DATABASE ACTIVATE STANDBY

DATABASE

statement is issued

empty string

– Any empty string implies that the log is just a normal archival and was not archived due to any of the other eventsSWITCHOVER ：Switchover产生的EOR
TERMINAL : Failover产生的EOR
RESETLOGS : 主库执行ALTER DATABASE OPEN RESETLOGS语句后产生的归档
ACTIVATION ：物理standby执行ALTER DATABASE ACTIVATE STANDBY DATABASE语句后产生的归档
空值 ：正常的归档

BACKED_BY_VSS

VARCHAR2(3)

Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is reserved for internal use.显示文件是否以Volume Shadow Copy Service(VSS)备份,保留作内部使用

CON_ID

NUMBER

The ID of the container to which the data pertains. Possible values include:

0

: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs.

1

: This value is used for rows containing data that pertain to only the rootn: Where n is the applicable container ID for the rows containing data容器ID，对于多租户数据库，用于标识租户的容器