--On Standby:
--(a) Role Verification
Code: Select all
set lines 1000
col STARTUP_TIME for a30
select DATABASE_ROLE, db_unique_name, i.INSTANCE_NAME, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, TO_CHAR(i.startup_time, 'DD/MM/YYYY HH24:MI:SS') as startup_time
from v$instance i, v$database;
-- Last Seq# Applied To Standby Site should match the Last Seq# Generated on Primary
Code: Select all
SELECT a.thread#,
a.Last_Seq_Received-b.Last_Seq_Applied "Difference",
a.Last_Seq_Received,
b.Last_Seq_Applied
FROM
(SELECT thread#,
MAX(sequence#) Last_Seq_Received
FROM v$archived_log
GROUP BY thread#
ORDER BY 1
) a ,
(SELECT thread#,
MAX(sequence#) Last_Seq_Applied
FROM v$archived_log
WHERE applied IN ('YES','IN-MEMORY')
GROUP BY thread#
ORDER BY 1
) b
WHERE a.thread#=b.thread# ;
Code: Select all
SET lines 1000
COLUMN NAME FORMAT A30
COLUMN VALUE FORMAT A16
COLUMN TIME_COMPUTED FORMAT A24
SELECT * FROM v$dataguard_stats WHERE NAME IN ('transport lag', 'apply lag');
--Show which recovery mode is in place (should be MANAGED REAL TIME APPLY), run the following on the Primary database:
Code: Select all
set pages 999
set lines 999
col DEST_NAME for a20
col DESTINATION for a40
col DB_UNIQUE_NAME for a14
col STATUS for a10
col PROCESS for a7
SELECT vads.dest_name,
vads.recovery_mode,
vads.status,
vads.type,
vad.process,
vads.database_mode,
vad.destination,
vads.db_unique_name
FROM v$archive_dest_status vads,
v$archive_dest vad
WHERE vad.dest_id =vads.dest_id
AND vads.status <> 'INACTIVE'
AND vads.dest_name <> 'STANDBY_ARCHIVE_DEST';
Code: Select all
select dest_id, recovery_mode from v$archive_dest_status where dest_id=1;
Code: Select all
col STARTUP_TIME for a30
select INSTANCE_NAME,STATUS,HOST_NAME,TO_CHAR (STARTUP_TIME, 'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME",THREAD# from gv$instance order by INSTANCE_NAME;
Code: Select all
SELECT THREAD#, LOW_SEQUENCE# as "Low Seq Gap", HIGH_SEQUENCE# as "High Seq Gap" FROM V$ARCHIVE_GAP;
Code: Select all
SET lines 999
col name for a20
col total_mb for 999,999,999.99
col group_number for 99 heading "Group|Num"
col free_mb for 999,999,999.99 heading "Free|Size|(MB)"
col usable_file_mb for 999,999,999.99 heading "Usable|File|(MB)"
col "% Free" for 999.99
col total_mb format 999,999,999,999.9 heading "Total|Size|(MB)"
SELECT group_number,name,
free_mb/total_mb*100 "% Free",
free_mb,
total_mb,
usable_file_mb
FROM V$ASM_DISKGROUP;
Code: Select all
select process, client_process, thread#, sequence#, status from v$managed_standby order by THREAD#;
Code: Select all
set pages 999
set lines 999
col MEMBER for a85
col BYTES for 999999999999
select d.db_unique_name, i.thread# from v$database d, v$instance i;
prompt Online Redo Logs
select GROUP#,THREAD#,sequence#,BYTES,status,ARCHIVED from v$log order by THREAD#,GROUP# asc;
prompt Standby Redo Logs
select GROUP#,THREAD#,sequence#,BYTES,status,ARCHIVED from v$standby_log order by THREAD#,GROUP# asc;
select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by GROUP#;
Code: Select all
alter database recover managed standby database cancel;
Code: Select all
alter database recover managed standby database using current logfile disconnect from session;
------------------------------------------------------------------------------------------------------------------------------------------
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
------------------------------------------------------------------------------------------------------------------------------------------
--On Primary:
--(a) Role Verification
Code: Select all
set lines 1000
col STARTUP_TIME for a30
select DATABASE_ROLE, db_unique_name, i.INSTANCE_NAME, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, TO_CHAR(i.startup_time, 'DD/MM/YYYY HH24:MI:SS') as startup_time
from v$instance i, v$database;
Code: Select all
SET lines 999
col DEST_NAME FOR a20
col destination FOR a30
SELECT thread#,
MAX(sequence#) "Last Primary Seq Generated"
FROM v$archived_log
WHERE first_time BETWEEN (sysdate-1) AND (sysdate+1)
GROUP BY thread#
ORDER BY 1;
Code: Select all
alter system switch logfile;
SET lines 999
col "Instance" FOR a10
col DEST_NAME FOR a20
col destination FOR a35
col "Error" FOR a40
col DB_UNIQUE_NAME FOR a14
SELECT inst_id,
dest_name,
status,
NVL (error, 'No Issues Found') AS "Error",
target,
archiver,
destination,
DB_UNIQUE_NAME
FROM gv$archive_dest
WHERE status <> 'INACTIVE' and DEST_NAME <> 'STANDBY_ARCHIVE_DEST'
ORDER BY inst_id, dest_name;
Code: Select all
select INSTANCE_NAME,STATUS,HOST_NAME,TO_CHAR (STARTUP_TIME, 'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME",THREAD# from gv$instance order by INSTANCE_NAME;
Code: Select all
SET lines 999
col name for a20
col total_mb for 999,999,999.99
col group_number for 99 heading "Group|Num"
col free_mb for 999,999,999.99 heading "Free|Size|(MB)"
col usable_file_mb for 999,999,999.99 heading "Usable|File|(MB)"
col "% Free" for 999.99
col total_mb format 999,999,999,999.9 heading "Total|Size|(MB)"
SELECT group_number,name,
free_mb/total_mb*100 "% Free",
free_mb,
total_mb,
usable_file_mb
FROM V$ASM_DISKGROUP;
Code: Select all
set pages 999
set lines 999
col MEMBER for a85
col BYTES for 999999999999
select d.db_unique_name, i.thread# from v$database d, v$instance i;
prompt Online Redo Logs
select GROUP#,THREAD#,sequence#,BYTES,status,ARCHIVED from v$log order by THREAD#,GROUP# asc;
prompt Standby Redo Logs
select GROUP#,THREAD#,sequence#,BYTES,status,ARCHIVED from v$standby_log order by THREAD#,GROUP# asc;
select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by GROUP#;