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;
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;
No comments:
Post a Comment