Oracle DataGaurd Useful Monitoring Scripts!

December 26, 2019  

Your dataguard is running but is there any lag ? You can learn below script.

SQL> set lines 300 pages 2000
SQL> select name,value from v$dataguard_stats;

If you want to know which archive sequence number comes from the Primary database lastly and which is last applied in Standby, you can learn the following script.

SQL>  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 ( Media Recovery Process ) won’t run.

SQL> alter database recover managed standby database cancel;

To start dataguard you can use following query. When dataguard is started, MRP ( Media Recovery Process ) will run.

SQL> 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.

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


If you want to learn which of database is Standby and which of database is Primary, you can execute below query.

SQL> SELECT database_role, open_mode FROM v$database;

You can display the status of background processes in a standby database with below script.

SQL> select process, client_process,thread#,sequence#,status from v$managed_standby;

You can just want to see MRP process status then you can execute below script.

SQL> select process, client_process,thread#,sequence#,status from v$managed_standby where process like '%MRP%';

What is the MRP process waiting for status ? You can check with below script.

SQL> 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'));

To see the status of the switchover and the role of database which is in Standby or Primary state, execute following script.

SQL> select switchover_status,database_role from v$database;

To see archive gap in dataguard, execute following script.

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

Below script provides information about approximate completion time of the recovery process

SQL> 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');

To see all parameters of Oracle dataguard, you can execute following query.

SQL> set lines 3000 pages 0
SQL> col value for a80
SQL> col name for a50
SQL> 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;

To see applied archivelogs in dataguard, execute following script.

SQL> select thread#,sequence#,first_time,next_time,applied from gv$archived_log where applied='YES';

Below script provides information about max sequence of dataguard

SQL> select thread#,max(sequence#) from gv$archived_log group by thread#;