数据字典视图之：DBA_LOGSTDBY_LOG结构

« 使用Oracle Database 11g创建Interval分区表 | Blog首页 | 《深入解析Oracle》一书的销售情况 »

DBA_LOGSTDBY_LOG是逻辑Standby数据库中比较重要的一个字典视图，该视图用来记录当前的归档日志应用情况，通过该视图可以查看应用日志的SEQUENCE#,APPLIED状况等。

该视图的构建语法如下（来自Oracle 11gR1数据库）：

SELECT thread#, resetlogs_change#, reset_timestamp resetlogs_id, sequence#,
       first_change#, next_change#, first_time, next_time, file_name,
       TIMESTAMP, dict_begin, dict_end,
       (CASE
           WHEN l.next_change# < p.read_scn
              THEN 'YES'
           WHEN ((BITAND (l.CONTENTS, 16) = 16) AND (BITAND (l.status, 4) = 0))
              THEN 'FETCHING'
           WHEN ((BITAND (l.CONTENTS, 16) = 16) AND (BITAND (l.status, 4) = 4))
              THEN 'CORRUPT'
           WHEN l.first_change# < p.applied_scn
              THEN 'CURRENT'
           ELSE 'NO'
        END
       ) applied
FROM SYSTEM.logmnr_log$ l, dba_logstdby_progress p
WHERE session# = (SELECT VALUE
                     FROM SYSTEM.logstdby$parameters
                    WHERE NAME = 'LMNR_SID')
   AND (flags IS NULL OR BITAND (l.flags, 16) = 0);

引用Oracle文档说明如下：

DBA_LOGSTDBY_LOG displays information about the logs registered for a logical standby database. This view is for logical standby databases only.

Column	Datatype	NULL	Description
`THREAD#`	`NUMBER`	`NOT NULL`	Thread ID of the archive log. The THREAD number is `1` for a single instance. For Real Application Clusters, this column will contain different numbers.
`RESETLOGS_CHANGE#`	`NUMBER`	`NOT NULL`	Start SCN of the branch
`RESETLOGS_ID`	`NUMBER`	`NOT NULL`	Resetlogs identifier (a numerical form of the timestamp of the branch)
`SEQUENCE#`	`NUMBER`	`NOT NULL`	Sequence number of the archive log file
`FIRST_CHANGE#`	`NUMBER`	`NOT NULL`	System change number (SCN) of the current archive log
`NEXT_CHANGE#`	`NUMBER`		SCN of the next archive log
`FIRST_TIME`	`DATE`		Date of the current archive log
`NEXT_TIME`	`DATE`		Date of the next archive log
`FILE_NAME`	`VARCHAR2(513)`		Name of the archive log
`TIMESTAMP`	`DATE`		Time when the archive log was registered
`DICT_BEGIN`	`VARCHAR2(3)`		Indicates whether the beginning of the dictionary build is in this archive log (`YES`) or not (`NO`)
`DICT_END`	`VARCHAR2(3)`		Indicates whether the end of the dictionary build is in this archive log (`YES`) or not (`NO`)
`APPLIED`	`VARCHAR2(8)`		Indicates primarily whether a given foreign archived log has been applied fully by SQL Apply: `YES` - SQL Apply has fully applied the foreign archived log and no longer needs it `CURRENT` - SQL Apply is currently applying changes contained in the foreign archived log `NO` - SQL Apply has not started applying any changes contained in the foreign archived log `FETCHING` - SQL Apply encountered a corruption while reading redo records from this foreign archived log, and is currently using the automatic gap resolution to refetch a new copy of the log from the primary database `CORRUPT` - SQL Apply encountered a corruption while reading redo records from this foreign archived log, and refetching a new copy of the archived log did not resolve the problem. SQL Apply will not refetch a new copy of this archived log automatically, and will require user intervention to manually register a new copy of the foreign archived log.