DR QUIRIES

Major things to check when sync is not working

è  Password file across all nodes

è  Flash Recovery area space

è  Missing archives

è  Select * from v$pwfile_users (all nodes)

è  Log archive Destinations and parameters

Health checks to be done in case if we receive alerts related to time lag between primary and  standby Databases.

1. To check availability of redo on standby. This can be done, by checking the output of “last Sequence Generated” in script 1(at primary)

with “Last Sequence Received” in script 2(at standby). If the value differs that shows the archive is missing on standby.

2. To Check for redo applied on standby. The column “difference” in script 2(at standby) should be 0.

This specifies that the primary is in sync with physical standby.

If there are frequent redo log switches it will take some time to apply all archs so please monitor for some time using below queries.

Script 1 :

select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') Date from dual;

SELECT distinct SEQUENCE# "Last Sequence Generated", THREAD# "Thread"

FROM V$ARCHIVED_LOG

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

ORDER BY 1;

Script 2:

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#;

àStandby Database Does Not Receive Redo Data from the Primary Database

If the standby site is not receiving redo data, query the V$ARCHIVE_DEST view and check for error messages. For example, enter the following query:

SELECT DEST_ID "ID",

STATUS "DB_status",

DESTINATION "Archive_dest",

ERROR "Error"

FROM V$ARCHIVE_DEST WHERE DEST_ID <=3;

GENERAL QUIRIES

select DEST_ID,DEST_NAME,DESTINATION,TARGET,STATUS,ERROR from v$archive_dest where dest_id=2;

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

SELECT  PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
select log_mode,database_role,open_mode from v$database;

srvctl stop database -d <dbname> 

srvctl start database -d tlp1 -o mount

select log_mode,database_role,open_mode from v$database;

alter database recover managed standby database disconnect from session;