Recently, I have involved a recovery assesment project leaded by EMC.
One of the assignments of this project was to gather standby related information and analyze the dataguard configuration. I had no connection to the databases, so I had to request the information by already prepared queries and here is the list of the queries I have used to gather the standby related information.
Note that: these queries need to be executed in standby database.
generic information and the data protection mode.
Select database_role role, name, db_unique_name, platform_id, open_mode, log_mode, flashback_on, protection_mode, protection_level FROM v$database;
Force logging enabled or not, dataguard broker used or not
Select force_logging, remote_archive, supplemental_log_data_pk, supplemental_log_data_ui, switchover_status, dataguard_broker FROM v$database;
Thread numbers and instance names
Select thread#, instance, status FROM v$thread;
Are the source db and standby db in the same oracle version or not
Select thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;
Number and sizes of the redologs
Select thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
standby redolog information , size of standby redologs equal the size of online redologs or not
Select thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
archive log destinations
Select thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
Select thread#, dest_id, gvad.archiver, transmit_mode, affirm, async_blocks, net_timeout, delay_mins, reopen_secs reopen, register, binding FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
any errors occured the last time an attempt to archive to the destination was attempted
Select thread#, dest_id, gvad.status, error from gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND
destination is NOT NULL ORDER BY thread#, dest_id;
status of processes involved in the shipping redo on this system
Select thread#, process, pid, status, client_process, client_pid, sequence#, block#, active_agents, known_agents
from gv$managed_standby ORDER BY thread#, process;
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 resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
how often and how far the apply lagsSelect name, time, unit, count, TO_DATE(last_time_updated, 'MM/DD/YYYY HH24:MI:SS') FROM v$standby_event_histogram
ORDER BY unit DESC, time;
any archive gaps
Select * From v$archive_gap;
how much redo data generated by the primary database is not yet available on the standby database, how much redo data could be lost if the primary database were to crash at the time I queried
Select * from v$dataguard_stats WHERE name LIKE '%lag%';
Non default standby init parameters
SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE'
OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%' MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb
WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name
NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name
NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num
FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;
