SQL> ALTER DATABASE RECOVER AUTOMATIC STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
select DEST_ID,dest_name,status,type,srl,recovery_mode FROM V$ARCHIVE_DEST_STATUS where recovery_mode = 'MANAGED REAL TIME APPLY';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;
select thread#,max(sequence#) from v$archived_log group by thread# order by 1;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY NEXT_TIME ;
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES' ORDER BY SEQUENCE#;
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;
select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
select thread#, max(sequence#) "Last Standby Seq Received"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread#
order by 1;
select thread#, max(sequence#) "Last Standby Seq Applied"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY')
group by thread#
order by 1;
select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "Redo onsite" from v$standby_log;
select NAME Name, VALUE Value, UNIT Unit
from v$dataguard_stats
union
select null,null,' ' from dual
union
select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
from v$dataguard_stats;
select name,value,time_computed,datum_time from v$dataguard_stats where name='apply lag' and value > '+00 00:01:00';
SELECT (SELECT name FROM V$DATABASE),
(SELECT MAX (sequence#)
FROM v$archived_log
WHERE dest_id = 1)
Current_primary_seq,
(SELECT MAX (sequence#)
FROM v$archived_log
WHERE trunc(next_time) > SYSDATE - 1
AND dest_id = 2)
max_stby,
(SELECT NVL (
(SELECT MAX (sequence#) - MIN (sequence#)
FROM v$archived_log
WHERE trunc(next_time) > SYSDATE - 1
AND dest_id = 2
AND applied = 'NO'),
0)
FROM DUAL)
"To be applied",
( (SELECT MAX (sequence#)
FROM v$archived_log
WHERE dest_id = 1)
- (SELECT MAX (sequence#)
FROM v$archived_log
WHERE dest_id = 2))
"To be Shipped"
FROM DUAL;
clear column
col primary form a40
col standby form a40
select t1.name, t1.value primary, t2.value standby
from v$parameter t1, v$parameter@DBLINK t2
where t1.name = t2.name
and t1.value <> t2.value
and t1.name not in ('instance_name', 'control_files','user_dump_dest','core_dump_dest',
'background_dump_dest','log_archive_dest_1','log_archive_dest_state_2',
'log_archive_dest_state_3','log_archive_format','db_recovery_file_dest',
'utl_file_dir','local_listener','service_names','db_name','db_unique_name',
'dg_broker_config_file1','dg_broker_config_file2','diagnostic_dest','audit_file_dest')
order by t1.name;
The following queries can be used to assess overall health on both the primary and the standby.
| Goal | Query | Expected Results |
|---|---|---|
| Check if any remote standby archive destination is getting errors. Check if all remote standby archive destinations is enabled or "VALID". |
select sysdate,status,error from gv$archive_dest_status where type='PHYSICAL' and status!='VALID' or error is not null; |
Good behavior - no rows returned. If query returns rows, then raise an alert with the returned data. |
| Check if any nologging activity has occurred on the primary for the last day. | select file#, name, unrecoverable_change#, unrecoverable_time from v$datafile where unrecoverable_time > (sysdate - 1); |
Good behavior - no rows returned. If query returns rows, then the standby is vulnerable and the subsequent file needs to be refreshed on the standby. |
| Detect gaps on the standby | select sysdate,database_mode,recovery_mode, gap_status from v$archive_dest_status where type='PHYSICAL' and gap_status !='NO GAP'; |
Good behavior - no rows returned. If query returns rows, then there's an existing gap between the primary and the standby. Subsequent action is to run the same query on the standby, if the output is identical, then no action required. If the output on the standby does not match the primary then the datafile on the standby should be refreshed. |
| Assess if any severe Data Guard event occurred in the last day | select * from v$dataguard_status where severity in ('Error','Fatal') and timestamp > (sysdate -1); |
Good behavior - no rows returned. If query returns rows then the result should raise an alert with the returned output. |
| FOR SYNC ENVIRONMENTS ONLY: Assess if running in Maximum Availability mode and configuration is in sync. | select sysdate,protection_mode, synchronized, synchronization_status from v$archive_dest_status where type='PHYSICAL' and synchronization_status !='OK'; |
Good behavior - no rows returned. If query returns rows then the result should raise an alert with the returned output. |
| Goal | Query | Expected Results |
|---|---|---|
| Determine if there is a transport lag | select name,value,time_computed,datum_time from v$dataguard_stats where name='transport lag' and value > '+00 00:01:00'; |
Good behavior - no rows returned. If no rows are returned then this implies that there is no transport lag |
| Determine if there is an apply lag | select name,value,time_computed,datum_time from v$dataguard_stats where name='apply lag' and value > '+00 00:01:00'; |
Good behavior - no rows returned. If no rows are returned then this implies that there is no apply lag |
| Standby data file check (offline files or files that are not accessible) | select * from v$datafile_header where status ='OFFLINE' or ERROR is not null; |
Good behavior - no rows returned. Any rows returned will show which file(s) are having IO or recovery issues. |
| Verify that the Media Recovery Process is currently running. | select * from v$managed_standby where process like 'MRP%'; |
Good behavior - rows returned. If no rows are returned then the MRP process is not running. |
| Assess if any severe Data Guard event occurred in the last day | select * from v$dataguard_status where severity in ('Error','Fatal') and timestamp > (sysdate -1); |
Good behavior - no rows returned. If query returns rows, then the result should raise an alert with the returned output. |
set lines 300
set feedback off
select name||': '||database_role||' ('||version||')' NAME_DB_ROLE_VERSION from v$database, v$instance;
select log_mode from v$database;
select force_logging from v$database;
col value form a120
select name, value from v$parameter where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1',
'log_archive_dest_2','remote_login_passwordfile','log_archive_dest_state_1',
'log_archive_dest_state_2'
);
select name, value from v$parameter where name in ('db_file_name_convert','log_file_name_convert','log_archive_max_processes',
'fal_client','fal_server','standby_file_management','remote_listener','local_listener',
'dg_broker_start'
);
prompt
prompt Listeners
prompt ---------
host ps -ef | grep tns | grep -v "grep tns"
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
col destination form a40
SELECT DEST_ID, STATUS , DESTINATION, ERROR FROM V$ARCHIVE_DEST where status <> 'INACTIVE';
prompt
prompt Verify the Physical Standby Database Is Performing Properly
SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0';