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.

Column	Datatype	Description	解释
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 events	SWITCHOVER ：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，对于多租户数据库，用于标识租户的容器