监控Logical standby database

最新推荐文章于 2023-03-01 15:32:27 发布

转载最新推荐文章于 2023-03-01 15:32:27 发布 · 98 阅读

本文介绍了如何通过查询Oracle数据库中的视图来了解SQL Apply的状态，包括初始化、应用中及等待日志间隙等不同阶段的具体情况，并提供了实际的SQL查询示例。

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ls.htm#CHDGBCGI

SQL> SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID    STATE
----------    -------------
1             INITIALIZING

SQL> SELECT SID, SERIAL#, SPID, TYPE FROM V$LOGSTDBY_PROCESS;

SID     SERIAL#     SPID       TYPE
------  ---------   ---------  ---------------------
47      3           11438      COORDINATOR
50      7           11334      READER
45      1           11336      BUILDER
44      2           11338      PREPARER
43      2           11340      PREPARER

SQL> SELECT PERCENT_DONE, COMMAND
     FROM   V$LOGMNR_DICTIONARY_LOAD
     WHERE  SESSION_ID = (SELECT SESSION_ID FROM V$LOGSTDBY_STATE);

PERCENT_DONE     COMMAND
-------------    -------------------------------
40               alter table SYSTEM.LOGMNR_CCOL$ exchange partition 
                 P101 with table SYS.LOGMNRLT_101_CCOL$ excluding
                 indexes without validation

If the PERCENT_DONE or the COMMAND column does not change for a long time, query the V$SESSION_LONGOPS view to monitor the progress of the DDL transaction in question.

Applying State

In this state, SQL Apply has successfully loaded the initial snapshot of the LogMiner multiversioned data dictionary, and is currently applying redo data to the logical standby database.

For detailed information about the SQL Apply progress, query the V$LOGSTDBY_PROGRESS view:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SQL> SELECT APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN,
     FROM V$LOGSTDBY_PROGRESS;

APPLIED_TIME            APPLIED_SCN   MINING_TIME           MINING_SCN
--------------------    -----------   --------------------  -----------
10-JAN-2005 12:00:05    346791023     10-JAN-2005 12:10:05  3468810134

Waiting On Gap State

This state occurs when SQL Apply has mined and applied all available redo records, and is waiting for a new log file (or a missing log file) to be archived by the RFS process.

SQL> SELECT STATUS FROM V$LOGSTBDY_PROCESS WHERE TYPE = 'READER';

STATUS
------------------------------------------------------------------------
ORA:01291 Waiting for logfile

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-611970/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-611970/