Thursday, December 3, 2015

RDBMS -- Standby database analysis.

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 lags

Select 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;

No comments :

Post a Comment