Physical Standby Dataguard Health Checks

Physical Standby Dataguard Health Checks

Check dataguard status of errors:

select message,to_char(timestamp,'DD-MM-YY HH24:MI'),error_code from v$dataguard_status where error_code != 0;

SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

Check transport status:

primary> select * from v$archive_processes where status <> 'STOPPED';

primary> select status,error from v$archive_dest where status <>'INACTIVE';

Background process status in DG:

standby or primary> select process,status,client_process,sequence#,block#,active_agents,known_agents from v$managed_standby;

To know LOG status:

standby> select 'Last Applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union select 'Last Received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);

To know last sequence released and applied:

Standby> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" from (select thread# thrd, max(sequence#) almax from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) al, (select thread# thrd, max(sequence#) lhmax from v$log_history where first_time=(select max(first_time) from v$log_history)
group by thread#) lh where al.thrd = lh.thrd;

To know transport lag time, apply lag time and apply finish time:

standby>
set lines 180
col name for a40
col value for a40
col unit for a40
select NAME, VALUE, UNIT from v$dataguard_stats union select null,null,' ' from dual union select null,null,'Time Computed: '||MIN(TIME_COMPUTED) from v$dataguard_stats;

Ensure everything is ok from PRIMARY:

select status,error from v$archive_dest where status <>'INACTIVE';
select * from v$archive_processes where status <> 'STOPPED';
show parameter dest_2
show parameter state_2
alter system set log_archive_dest_state_2=enable scope=both sid='*';
alter system switch logfile;
alter system switch logfile;