ORACLE DATA GUARD COMMANDS

Hey Guys!! I have provided some important and useful commands that is used during monitoring and troubleshooting Oracle Data Guard related Issues. PFB.

TO CHECK THE STATUS OF THE DATABASE - PRIMARY AND STANDBY:

==========================================================

SET LINESIZE 189 PAGES 2000
COL NAME FOR A10
COL HOST_NAME FOR A30

SELECT NAME, INSTANCE_NAME, DB_UNIQUE_NAME, HOST_NAME, STATUS, OPEN_MODE, DATABASE_ROLE AS DB_ROLE, LOGINS FROM V$DATABASE, GV$INSTANCE;

TO CHECK STANDBY INFO :

=======================

SET LINESIZE 189 PAGES 2000
COL NAME FOR A10
COL HOST_NAME FOR A20
COL DB_UNIQUE_NAME FOR A20
COL OPEN_MODE FOR A10
COL DG_BROKER FOR A15

SELECT NAME, DB_UNIQUE_NAME, HOST_NAME, DATABASE_ROLE AS DB_ROLE, OPEN_MODE, PROTECTION_MODE AS PROTECT_MODE, PROTECTION_LEVEL AS PROTECT_LEVEL, DATAGUARD_BROKER AS DG_BROKER, SWITCHOVER_STATUS FROM V$DATABASE, GV$INSTANCE;

TO CHECK CURRENT DG CONFIGURATION :

===================================

SHOW PARAMETER LOG_ARCHIVE_CONFIG;

SHOW PARAMETER LOG_ARCHIVE_DEST_2;

ARCHIVE LOG LIST;

SHOW PARAMETER RECO;

TO FIND CURRENT_SCN IN PRIMARY :
================================

SET LINESIZE 189 PAGES 2000
COL CURRENT_SCN FOR 99999999999999

SELECT A.NAME AS DB_NAME, B.DB_UNIQUE_NAME, B.PARENT_DBUN, A.OPEN_MODE, B.DEST_ROLE, B.CURRENT_SCN FROM GV$DATAGUARD_CONFIG B, V$DATABASE A WHERE DEST_ROLE LIKE '%PRIMARY%';

TO FIND CURRENT_SCN IN STANDBY :
================================

SET LINESIZE 189 PAGES 2000
COL CURRENT_SCN FOR 99999999999999

SELECT A.NAME AS DB_NAME, B.DB_UNIQUE_NAME, B.PARENT_DBUN, A.OPEN_MODE, B.DEST_ROLE, B.CURRENT_SCN FROM GV$DATAGUARD_CONFIG B, V$DATABASE A WHERE DEST_ROLE NOT LIKE '%PRIMARY%';

TO CHECK SEQUENCE RECEIVED/APPLIED ON ALL INSTANCES AND THE GAP
=================================================================

SELECT A.THREAD#, MAX(A.SEQUENCE#) LAST_SEQ_RECVD, MAX(B.SEQUENCE#) LAST_SEQ_APPLD, MAX(A.SEQUENCE#) - MAX(B.SEQUENCE#) GAP FROM V$ARCHIVED_LOG A, V$LOG_HISTORY B WHERE A.THREAD# = B.THREAD# GROUP BY A.THREAD#;

TO CHECK LAST_APPLIED_TIMESTAMP IN STANDBY :=============================================

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT A.THREAD#, B.LAST_SEQ, A.APPLIED_SEQ, A.LAST_APP_TIMESTAMP, B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF FROM ( SELECT THREAD#, MAX (SEQUENCE#) APPLIED_SEQ, MAX (NEXT_TIME) LAST_APP_TIMESTAMP FROM GV$ARCHIVED_LOG WHERE APPLIED = 'YES' GROUP BY THREAD#) A, ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ FROM GV$ARCHIVED_LOG GROUP BY THREAD#) B WHERE A.THREAD# = B.THREAD#;

TO CHECK IF THERE IS ANY ERROR REPORTED DURING LOG SHIPPING AND LOG APPLYING :=========================================================================

SET LINESIZE 189 PAGES 2000
COL INSTANCE_NAME FOR A15
COL DESTINATION FOR A50
COL ERROR FOR A40

SELECT A.INST_ID, B.INSTANCE_NAME,  A.DEST_ID, A.PROCESS, A.STATUS, A.DESTINATION, A.ERROR FROM GV$ARCHIVE_DEST A, GV$INSTANCE B WHERE A.STATUS NOT IN ('INACTIVE');

TO CHECK THE MOST RECENT SEQUENCE# AND THEIR STATUS (STANDBY) :===============================================================

SET LINESIZE 189 PAGES 2000
COL DB_NAME FOR A10
COL ARC_NAME FOR A70

SELECT B.NAME AS DB_NAME, B.DATABASE_ROLE AS DB_ROLE, A.NAME AS ARC_NAME, A.THREAD# , A.SEQUENCE#, A.APPLIED  FROM V$ARCHIVED_LOG A , V$DATABASE B ORDER BY SEQUENCE# DESC
FETCH FIRST 10 ROWS ONLY
/

TIP : CHECK IF ARCHIVES ARE PROPERLY SHIPPED AND APPLIED ONTO STANDBY BY COMPARING THE SEQUENCE# ON BOTH PRIMARY AND STANDBY. CHECK ALERT LOG AND TAKE ACTION ACCORDINGLY.