Dataguard Monitoring Scripts

Dataguard Monitoring Scripts

LAG AND STATUS CHECK 

SET LINESIZE 1000

select name,value from v$dataguard_stats;

ARCHIVE SEQUENCE NUMBER COMES FROM THE PRIMARY DATABASE LASTLY AND WHICH IS LAST APPLIED IN STANDBY, YOU CAN LEARN THE FOLLOWING SCRIPT.

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last in Sequence", APPL.SEQUENCE# "Last Applied Sequence", (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;

To stop dataguard you can use following query. When dataguard is stopped, MRP

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

To start dataguard starting MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If you don’t want any lag you should start dataguard with using logfile option. You should add standby logs to the standby database in this case.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Check which of database is Standby and which of database is Primary,

SELECT DATABASE_ROLE, OPEN_MODE FROM V$DATABASE;

Display the status of background processes in a standby database

SELECT PROCESS, CLIENT_PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

YOU CAN JUST WANT TO SEE MRP PROCESS STATUS

 

SELECT PROCESS, CLIENT_PROCESS,THREAD#,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE '%MRP%';

WHAT IS THE MRP PROCESS WAITING FOR STATUS ?

SELECT A.EVENT, A.WAIT_TIME, A.SECONDS_IN_WAIT FROM GV$SESSION_WAIT A, GV$SESSION B WHERE A.SID=B.SID AND B.SID=(SELECT SID FROM V$SESSION WHERE PADDR=(SELECT PADDR FROM V$BGPROCESS WHERE NAME='MRP0'));

SWITCHOVER AND THE ROLE OF DATABASE WHICH IS IN STANDBY OR PRIMARY STATE

select switchover_status,database_role from v$database;

TO SEE ARCHIVE GAP IN DATAGUARD

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

INFORMATION ABOUT APPROXIMATE COMPLETION TIME OF THE RECOVERY PROCESS

SELECT TO_CHAR(START_TIME,'DD-MON-RR HH24:MI:SS') START_TIME,ITEM,ROUND(SOFAR/1024,2) "MB/SEC" FROM V$RECOVERY_PROGRESS 

WHERE (ITEM='ACTIVE APPLY RATE' OR ITEM='AVERAGE APPLY RATE');

SEE ALL PARAMETERS OF ORACLE DATAGUARD

SET LINESIZE 500 PAGES 0

COL VALUE FOR A80

COL NAME FOR A50

SELECT NAME, VALUE FROM V$PARAMETER

WHERE NAME IN ('DB_NAME','DB_UNIQUE_NAME','LOG_ARCHIVE_CONFIG',    

'LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3',

'LOG_ARCHIVE_DEST_STATE_1','LOG_ARCHIVE_DEST_STATE_2','LOG_ARCHIVE_DEST_STATE_3', 'REMOTE_LOGIN_PASSWORDFILE',

'LOG_ARCHIVE_FORMAT','LOG_ARCHIVE_MAX_PROCESSES','FAL_SERVER','FAL_CLIENT','DB_FILE_NAME_CONVERT',

'LOG_FILE_NAME_CONVERT', 'STANDBY_FILE_MANAGEMENT') ORDER BY 1;

SEE APPLIED ARCHIVELOGS IN DATAGUARD

SELECT THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM GV$ARCHIVED_LOG WHERE APPLIED='YES';

PROVIDES INFORMATION ABOUT MAX SEQUENCE OF DATAGUARD

SELECT THREAD#,MAX(SEQUENCE#) FROM GV$ARCHIVED_LOG GROUP BY THREAD#;