Queries to check DataGuard status
In the primary database
|
1 |
select sequence#, first_time, next_timefrom v$archived_log order by sequence#; |
|
1 |
SELECT min( DURATION) FROM V$REDO_DEST_RESP_HISTOGRAM -WHERE DEST_ID=2 AND FREQUENCY>1; |
|
1 2 3 4 |
COLUMN FILE_NAME FORMAT a55SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L - WHERE NEXT_CHANGE# NOT IN - (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#) - ORDER BY THREAD#, SEQUENCE#; |
|
1 2 3 |
COLUMN NAME FORMAT A24COLUMN VALUE FORMAT A16 COLUMN DATUM_TIME FORMAT A24 SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS; |
|
1 2 |
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)FROM V$SESSION GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER; |
In the standby database
|
1 |
select thread#,sequence#,applied from v$archived_log; |
|
1 2 3 4 5 6 |
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (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; |
|
1 |
select max(sequence#) as STANDBY,applied from v$archived_log group by applied ; |
|
1 |
SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2; |
|
1 |
Select process,status from v$managed_standby; |
|
1 |
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY; |
|
1 |
SELECT MESSAGE FROM V$DATAGUARD_STATUS; |
|
1 |
select sequence#, first_time, next_time from v$archived_log order by sequence#; |
|
1 2 3 |
ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; |
|
1 2 3 4 |
SELECT THREAD#, SEQUENCE#, APPLIED,TO_CHAR(FIRST_TIME, 'MM/DD/YY HH24:MI:SS') FIRST, TO_CHAR(NEXT_TIME, 'MM/DD/YY HH24:MI:SS') NEXT, TO_CHAR(COMPLETION_TIME, 'MM/DD/YY HH24:MI:SS') COMPLETION FROM V$ARCHIVED_LOG ORDER BY FIRST_TIME |
To view the last archived redo logs
|
1 |
SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#; |
|
1 2 3 4 5 6 |
col DESTINATION format a40;col status format a40; col ARCHIVED_THREAD# format 999999; col ARCHIVED_SEQ# format 9999; SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# - FROM V$ARCHIVE_DEST_STATUS - WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE' |
|
1 2 3 4 5 |
SELECT LOCAL.THREAD#, LOCAL.SEQUENCE# FROM -(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) - LOCAL WHERE - LOCAL.SEQUENCE# NOT IN - (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND - THREAD# = LOCAL.THREAD#) |
|
1 |
SELECT FREQUENCY, DURATION FROM V$REDO_DEST_RESP_HISTOGRAM WHERE DEST_ID=2 AND FREQUENCY>1; |
Using RMAN for...
12 March 2019