Friday, August 15, 2008

Dataguard Diagnostics Select Statements

1. run on primary to detect failures :-

select destination, status, fail_date, valid_now
from v$archive_dest
where dest_name in ('LOG_ARCHIVE_DEST_1','LOG_ARCHIVE_DEST_2','LOG_ARCHIVE_DEST_3','LOG_ARCHIVE_DEST_4')
and (status <> 'VALID' or VALID_NOW <> 'YES');

2. run on standby to get exact position of rollforward :-

select thread#, to_char(snapshot_time,'dd-mon-yyyy:hh24:mi') primary,
to_char(newest_time,'dd-mon-yyyy:hh24:mi') received,
to_char(applied_time,'dd-mon-yyyy:hh24:mi') applied
from V$STANDBY_APPLY_SNAPSHOT;