Dataguard Administration Find archive generation over last one month SELECT TRUNC(FIRST_TIME) , COUNT(*) FROM V$ARCHIVED_LOG WHERE FIRST_TIME BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE AND DEST_ID = 1 GROUP BY TRUNC(FIRST_TIME) ORDER BY 1 To check the gap history (last 24 hours) in DR run the below query: select thread#, sequence#, to_char(next_time,'DD-HH24:MI'), to_char(completion_time,'DD-HH24:MI'), round((completion_time-next_time)*24*60) as delta_minutes from v$archived_log where completion_time>SYSDATE-1 and (completion_time-next_time)*24*60>30; Run the primary and see the any Gap in the sequence select thread#, applied, max(sequence#) from gv$archived_log group by thread#, applied order by thread#, applied desc; Last applied and Last received status 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); - Verify that the last sequence# received and the last sequence# applied to standby -- database. 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; The following query can be used on the primary database to identify peak archive times for a specified day: SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM", TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM" FROM V$LOG_HISTORY GROUP BY TRUNC(FIRST_TIME) ORDER BY TRUNC(FIRST_TIME) Archive log gaps can be monitored by examining the low and high sequence numbers in the V$ARCHIVE_GAP view, as shown here: SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP; When you detect a redo transport delay, execute the following query on the primary database to identify archive logs that have not made it to the standby destination: SELECT L.THREAD#, L.SEQUENCE# FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) L WHERE L.SEQUENCE# NOT IN (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = L.THREAD#); Archive log genarating per hour select day, MAX(DECODE(thread#, 1, switches_per_thread, NULL)) thread_1, MAX(DECODE(thread#, 2, switches_per_thread, NULL)) thread_2,MAX(DECODE(thread#, 3, switches_per_thread, NULL)) thread_3 from (select trunc(first_time) day, thread#, sum(1) switches_per_thread from v$log_history group by trunc(first_time), thread#) group by day order by 1 how to calculate the amount of redo per day and thread. To find out, use this query: SELECT TRUNC(first_time), thread#, ROUND(SUM(blocks * block_size) / 1024/ 1024,2) m FROM v$archived_log WHERE dest_id = 1 GROUP BY TRUNC(first_time), thread #ORDER BY 1 standby logs are being used by running following query : set lines 155 pages 9999 col thread# for 9999990 col sequence# for 999999990 col grp for 990 col fnm for a50 head "File Name" col "Fisrt SCN Number" for 999999999999990 break on thread # skip 1 select a.thread# ,a.sequence# ,a.group# grp , a.bytes/1024/1024 Size_MB ,a.status ,a.archived ,a.first_change# "First SCN Number" ,to_char(FIRST_TIME,'DD-Mon-RR HH24:MI:SS') "First SCN Time" ,to_char(LAST_TIME,'DD-Mon-RR HH24:MI:SS') "Last SCN Time" from v$standby_log a order by 1,2,3,4 / column name format a45 trunc set lines 300 set pages 999 set trimspool on column dest_id format 9999 column thread# format 9999 alter session set nls_date_format='dd-mon-yy hh24:mi:ss'; select sequence#, name, dest_id, thread#, first_time, round(sum((blocks*block_size/1024/1024))) mbytes from v$archived_log where first_time >=trunc(sysdate-3) group by sequence#, name, dest_id, thread#, first_time order by sequence# desc, dest_id asc; PROMPT PROMPT PROMPT Run on Standby Database. This script checks last log applied and last log received time PROMPT PROMPT col time format a40 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); PROMPT last sequence# received and the last sequence# applied to standby database. PROMPT 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; 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; ######### Datagurd standby troubleshooting Ref: http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/ http://shivanandarao-oracle.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/ Run Below scripts from SYS user from Both Primary & Standby databases. Primary Script:- spool dg_Primary_output.log set feedback off set trimspool on set line 500 set pagesize 50 column name for a30 column display_value for a30 column ID format 99 column "SRLs" format 99 column active format 99 col type format a4 column ID format 99 column "SRLs" format 99 column active format 99 col type format a4 col PROTECTION_MODE for a20 col RECOVERY_MODE for a20 col db_mode for a15 SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name; col name for a10 col DATABASE_ROLE for a10 SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database; select thread#,max(sequence#) from v$archived_log group by thread#; col severity for a15 col message for a70 col timestamp for a20 select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2; select ds.dest_id id , ad.status , ds.database_mode db_mode , ad.archiver type , ds.recovery_mode , ds.protection_mode , ds.standby_logfile_count "SRLs" , ds.standby_logfile_active active , ds.archived_seq# from v$archive_dest_status ds , v$archive_dest ad where ds.dest_id = ad.dest_id and ad.status != 'INACTIVE' order by ds.dest_id; column FILE_TYPE format a20 col name format a60 select name , floor(space_limit / 1024 / 1024) "Size MB" , ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name; spool off This script will generate dg_Primary_output.log file where user is connected to SQLPlus from shell. Standby Script:- spool dg_standby_output.log set feedback off set trimspool on set line 500 set pagesize 50 set linesize 200 column name for a30 column display_value for a30 col value for a10 col PROTECTION_MODE for a15 col DATABASE_Role for a15 SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name; col name for a10 col DATABASE_ROLE for a10 SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database; select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; select process, status,thread#,sequence# from v$managed_standby; 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; col name for a30 select * from v$dataguard_stats; select * from v$archive_gap; col name format a60 select name,floor(space_limit / 1024 / 1024) "Size MB" ,ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name; spool off column applied_time for a30 set linesize 140 select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual; SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP , (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or ((LOG_ARCHIVED-LOG_APPLIED) > 5)) then 'Error! Log Gap is ' else 'OK!' end) Status FROM ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE where INST_ID = 1 ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1 ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1 ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1 ) UNION SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP, (case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or (APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or ((LOG_ARCHIVED-LOG_APPLIED) > 5)) then 'Error! Log Gap is ' else 'OK!' end) Status from ( SELECT INSTANCE_NAME DB_NAME FROM GV$INSTANCE where INST_ID = 2 ), ( SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2 ), ( SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2 ), ( SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2 ) /