Useful Standby database commands

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;