Monitoring SQL APPLY process

+Current state of Logical standby

SELECT INST_ID, SESSION_ID, STATE FROM GV$LOGSTDBY_STATE
/

INST_ID,SESSION_ID,STATE
====================
1,22,NULL
2,22,APPLYING

+Current active processes assiciated with SQL APPLY

SELECT SID, SERIAL#, SPID, TYPE FROM GV$LOGSTDBY_PROCESS
/

SID,SERIAL#,SPID,TYPE
=================
376,14368,1904,COORDINATOR
363,8,14936,READER
364,5,14942,BUILDER
368,21,14948,PREPARER
365,5,14950,PREPARER
378,1540,14952,PREPARER
366,9,14954,PREPARER
351,12,15047,ANALYZER
350,5,15049,APPLIER
379,1014,15051,APPLIER
344,4,15053,APPLIER
348,13,15055,APPLIER
381,24216,15057,APPLIER
342,6,15059,APPLIER
339,4,15069,APPLIER
362,11,15071,APPLIER

+How updated logical standby is?

SELECT SYSDATE, APPLIED_TIME, APPLIED_SCN, MINING_TIME, MINING_SCN FROM GV$LOGSTDBY_PROGRESS
/

SYSDATE,APPLIED_TIME,APPLIED_SCN,MINING_TIME,MINING_SCN
=================================================
26/02/2008 15:12:56,26/02/2008 15:12:42,530574207,null,null
26/02/2008 15:12:56,26/02/2008 15:12:42,530574207,26/02/2008 15:12:45,530574517

+Archive log files not yet applied on logical Standby

SELECT THREAD#, FILE_NAME, TIMESTAMP, APPLIED FROM DBA_LOGSTDBY_LOG WHERE APPLIED ‘YES’ ORDER BY FILE_NAME
/

+Archive files applied, but not yet deleted from logical Standby

SELECT THREAD#, FILE_NAME, TIMESTAMP, APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY FILE_NAME
/

THREAD#,FILE_NAME,TIMESTAMP,APPLIED
================================
1,+ASMF/TESTDB/SARCH/1_1782_641082275.dbf,26/02/2008 13:44:38,YES
2,+ASMF/TESTDB/SARCH/2_1497_641082275.dbf,26/02/2008 14:05:05,YES
3,+ASMF/TESTDB/SARCH/3_2089_641082275.dbf,26/02/2008 15:00:13,YES

+Recent activities on logical standby

SELECT EVENT_TIME, TO_CHAR(EVENT), STATUS_CODE, STATUS FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP DESC
/

EVENT_TIME,TO_CHAR(EVENT),STATUS_CODE,STATUS
====================================
26/02/2008 15:19:01,truncate table T1,16204,ORA-16204: DDL successfully applied
26/02/2008 15:19:00,truncate table T2,16204,ORA-16204: DDL successfully applied
26/02/2008 15:19:00,truncate table T3,16204,ORA-16204: DDL successfully applied

By default DBA_LOGSTDBY_EVENTS will only retain last 100 events. To change this limit we can use

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
EXECUTE DBMS_LOGSTDBY.APPLY_SET (‘MAX_EVENTS_RECORDED’, ‘5000’);
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Advertisements
This entry was posted in Oracle Data Guard, Oracle Logical Data Guard and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s