DG related query

1.Standby database process status: You can run following query on standby database to see what MRP and RFS processes are doing, which block of which archivelog sequences are being shipped or being applied.

SQL > select process, status, thread#, sequence#, block#, blocks from v$managed_standby PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS--ARCH CLOSING1 301 75776 1432ARCH CLOSING1 299 77824 1825ARCH CONNECTED0 00 0ARCH CLOSING1 75776 1422RFS IDLE0 00 0RFS IDLE0 0 0 0RFS IDLE1 302 72377 1MRP0 WAIT_FOR_LOG1 302 0 08 rows selected.

2.Last applied log: Run this query on the standby database to see the last applied archivelog sequence number for each thread.

SQL > SELECT thread#, max (SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#; THREAD# MAX (SEQUENCE#)-- 1 301

3.Archivelog difference: Run this on primary database. (not for real time apply)

SQL > ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS' Session altered.SQL > SELECT a.thread, b.last_seq, a.applied_seq, a. Last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX (sequence#) applied_seq, MAX (next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread# THREAD# LAST_SEQ APPLIED_SEQ LAST_APP_TIMESTAMP ARC_DIFF- 1 301 301 08-OCT-2015 01:06:460

4.Apply/transport lags: v$dataguard_stats view will show the general synchronization status of standby database.

SQL > select * from v$dataguard_stats NAME VALUE UNIT TIME_COMPUTED DATUM_TIME -transport lag + 00 00:00:00 day (2) to second (0) interval 10 day 08 2015 09:21:40 10 09:21:39apply lag + 00 08:14:59 day (2) to second (0) interval 10 Grey 08 2015 09:21:40 10 interval 082015 09:21 : 39apply finish time + 00 00 09:21:40estimated startup time 00 to second 02.091 day (2) to second (3) 2015 09:21:40estimated startup time 19 second 10 day (3)

5.

SQL > select * from v$recovery_progress START_TIM TYPE ITEM UNITSSOFARTOTAL TIMESTAMP COMMENTS -23-SEP-15 Media Recovery Log Files Files 229 023-SEP-15 Media Recovery Active Apply Rate KB/sec16045 023-SEP-15 Media Recovery Average Apply Rate KB/sec 6 023-SEP-15 Media Recovery Maximum Apply Rate KB/sec17761 023-SEP-15 Media Recovery Redo Applied Megabytes 7891 023-SEP-15 Media Recovery Last Applied Redo SCN+Time 0 0 08-OCT-15 SCN: 395781823-SEP -15 Media Recovery Active Time Seconds 871 023-SEP-15 Media Recovery Apply Time per Log Seconds 3 023-SEP-15 Media Recovery Checkpoint Time per Log Seconds 0 023-SEP-15 Media Recovery Elapsed Time Seconds 1248075 023-SEP-15 Media Recovery Standby Apply Lag Seconds14784 011 rows selected.