The following steps can be performed to check the redo transport service status:
- The first query to be executed to be sure that the redo transport service is working properly will be the
V$DATAGUARD_STATSview.SQL> SELECT NAME, VALUE, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME='TRANSPORT LAG'; NAME VALUE TIME_COMPUTED ---------------------- -------------------- ---------------------- transport lag +00 00:00:00 08/27/2012 18:06:30
The
TIME_COMPUTEDvalue has to be up-to-date. We can see that there is no redo transport lag in our logical standby configuration. We'll see a time value if there is a problem with the redo transport. Also, if there is an excessive redo generation on the primary database, this value may increase because the redo transport may not catch up with the redo generation. The lag must be zero again when the standby synchronized at the end. - By executing the following SQL query on the logical standby, we can check logs with which sequences are being transferred from primary and also which sequences are being archived from the local database online redo logs.
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------- ------------ ---------- ---------- ---------- ---------- ARCH CLOSING 1 90 90112 1026 ARCH CONNECTED 0 0 0 0 ARCH CLOSING 1 91 90112 1026 ARCH CLOSING 1 92 90112 1018 RFS IDLE 0 0 0 0 RFS RECIEVING 1 114 6828 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0
The primary database is currently sending redo to the logical standby. We can see that the RFS process, which is responsible for redo transportation on standby databases, is currently receiving the redo with sequence number
114. It's also obvious that the ARCH processes are archiving the online redo logs of the logical standby database and the last archived log has the sequence number92.Tip
Don't forget that the sequences being received by RFS and the sequences being archived from the online redo logs by ARCH have no relationships. For example, the log sequence
90archived from the online redo log of the logical standby database does not contain the same redo data with the sequence90, which is received from the primary database. - On the other hand, we can use the following query to check which sequences were received from the primary database and if they were applied or not:
SQL> SELECT FILE_NAME, SEQUENCE# as SEQ#, DICT_BEGIN AS BEG, DICT_END AS END,APPLIED FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#; FILE_NAME SEQ# BEG END APPLIED ------------------------------------------- --- --- --- -------- /u01/app/oracle2/archive_std/1_105_791552282.arc 105 YES YES YES /u01/app/oracle2/archive_std/1_106_791552282.arc 106 NO NO YES /u01/app/oracle2/archive_std/1_107_791552282.arc 107 NO NO YES /u01/app/oracle2/archive_std/1_108_791552282.arc 108 NO NO YES /u01/app/oracle2/archive_std/1_109_791552282.arc 109 NO NO YES /u01/app/oracle2/archive_std/1_110_791552282.arc 110 NO NO YES ...
The YES value of the DICT_BEGIN and DICT_END columns show by the archived log sequences that the LogMiner dictionary build was in place. The APPLIED column shows whether the archived log sequence was applied by SQL Apply or not.