監控Logical standby database

 

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