Useful Standby database commands

To see if the MRP is running or not

 SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

How to Check if Active Data Guard is Already Enabled :

SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';

ADG

-----------------------                                               (if ENABLED)

Using Active Data Guard                     

no rows selected.                                                              (if NOT ENABLED)

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop recovery of a standby database:

SQL>   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary:

SQL >  SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1;

On Standby:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Check Archive gap on Standby:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Check the max log sequence on Primary DB

 SELECT Max(sequence#) FROM   v$log_history;

# check the last log applied on STANDBY
SELECT thread#,   Max(sequence#) "Last Standby Seq Applied"
FROM   v$archived_log   WHERE  applied = 'YES'  GROUP  BY thread#   ORDER  BY 1; 

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’transport lag’;

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’apply lag’;

On primary:  (Where dblink_stby à dblink on the primary that points to the standby database )

SQL> select scn_to_timestamp((select current_scn from v$database))-scn_to_timestamp((select current_scn from v$database@dblink_stby)) from dual;

The value returned from the query indicates the number of seconds that data on the standby lags behind the current position of the primary database.

Find Redo GAP: (on STANDBY)

SQL> select * from v$archive_gap;

thread#    low_sequence#    high_sequence#
---------------------------------------------------------------------------
2              222                   222
3              341                   342

Identify missing archive logs from above output:

SQL> select name from v$archived_log where thread# = 2 and dest_id = 2 and sequence# = 222;