This script is validated against Oracle 11g.
Run this script on the standby database to obtain basic information about the database state and the managed recovery processes:
set linesize 300
set pagesize 100
col "controlfile type" format a30
col "Unique name" format a12
col "Open Mode" format a12
col "Flashback on" format a15
col "Protection Mode" format a30
col "Current SCN" format 9999999999999
col "Database Role" format a20
col "Log Mode" format a20
col "DG broker" format a20
col "Destination name" format a20
col "Destination" format a20
col member format a70
col type format a20
col units format a20
col name format a15
set feedback off
alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:SS:MI';
Prompt
Prompt ======================================================================================================================
prompt General information:
select name,db_unique_name as "Unique name",created,open_mode as "Open Mode",flashback_on as "Flashback on",resetlogs_time as "Resetlogs time",current_scn as "Current SCN"
from v$database
/
Prompt ======================================================================================================================
prompt Standby database information, if relevant:
select database_role as "Database Role",log_mode as "Log Mode",controlfile_type as "Controlfile type",protection_mode as "Protection Mode"
from v$database
/
Prompt ======================================================================================================================
select dest_name as "Destination Name",status,type,database_mode,recovery_mode,destination,archived_seq#,applied_seq#
from v$archive_dest_status
where status <> 'INACTIVE'
/
Prompt ======================================================================================================================
prompt Is the MRP process running?
select process, status from v$managed_standby
where process like '%MRP%'
/
Prompt ======================================================================================================================
Prompt Are there standby redo logs configured?
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#
/
Prompt ======================================================================================================================
Prompt Check the recovery progress:
select START_TIME
,TYPE
,ITEM
,UNITS
,SOFAR
,TOTAL
,TIMESTAMP
from v$recovery_progress
/
Prompt ======================================================================================================================
prompt Data guard broker information, if relevant:
select dataguard_broker as "DG broker"
from v$database
/
Prompt ======================================================================================================================
exit
Example output. Note particularly the output in red. When the archived_seq# and the applied_seq# match, together with an MRP process that is in status "WAITING_FOR_LOG", your standby database is in synch with the primary.
======================================================================================================================
General information:
NAME Unique name CREATED Open Mode Flashback on Resetlogs time Current SCN
--------------- ------------ ------------------- ------------ --------------- ------------------- --------------
PRODDB01 STBDB01 19-01-2009 15:25:32 MOUNTED YES 17-11-2012 13:17:40 7203583291843
======================================================================================================================
Standby database information, if relevant:
Database Role Log Mode Controlfile type Protection Mode
-------------------- -------------------- ------------------------------ ------------------------------
PHYSICAL STANDBY ARCHIVELOG STANDBY MAXIMUM PERFORMANCE
======================================================================================================================
Destination Name STATUS TYPE DATABASE_MODE RECOVERY_MODE DESTINATION ARCHIVED_SEQ# APPLIED_SEQ#
-------------------- --------- -------------------- --------------- -------------- ------------ ------------- ------------
LOG_ARCHIVE_DEST_2 DEFERRED PHYSICAL MOUNTED-STANDBY MANAGED PRODDB01 0 0
LOG_ARCHIVE_DEST_10 VALID PHYSICAL MOUNTED-STANDBY MANAGED 48610 0
STANDBY_ARCHIVE_DEST VALID PHYSICAL MOUNTED-STANDBY MANAGED 48609 48609
======================================================================================================================
Is the MRP process running?
PROCESS STATUS
--------- ------------
MRP0 WAIT_FOR_LOG
======================================================================================================================
Are there standby redo logs configured?
GROUP# THREAD# SEQUENCE# ARC STATUS MEMBER TYPE
---------- ---------- ---------- --- ---------- ---------------------------------------------------------------------- --------------------
6 1 48611 YES ACTIVE /data/oracle/u01/STBDB01/stb_redo_6a.dbf STANDBY
6 1 48611 YES ACTIVE /data/oracle/u01/STBDB01/stb_redo_6b.dbf STANDBY
7 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7a.dbf STANDBY
7 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_7b.dbf STANDBY
8 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8a.dbf STANDBY
8 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_8b.dbf STANDBY
9 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9a.dbf STANDBY
9 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_9b.dbf STANDBY
10 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10a.dbf STANDBY
10 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_10b.dbf STANDBY
11 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11a.dbf STANDBY
11 1 0 NO UNASSIGNED /data/oracle/u01/STBDB01/stb_redo_11b.dbf STANDBY
======================================================================================================================
Check the recovery progress:
START_TIME TYPE ITEM UNITS SOFAR TOTAL TIMESTAMP
------------------- -------------------- -------------------------------- -------------------- ---------- ---------- -------------------
15-11-2013 17:11:58 Media Recovery Log Files Files 7571 0
15-11-2013 17:11:58 Media Recovery Active Apply Rate KB/sec 8118 0
15-11-2013 17:11:58 Media Recovery Average Apply Rate KB/sec 110 0
15-11-2013 17:11:58 Media Recovery Redo Applied Megabytes 620510 0
15-11-2013 17:11:58 Media Recovery Last Applied Redo SCN+Time 923136452 0 21-01-2014 12:00:08
15-11-2013 17:11:58 Media Recovery Active Time Seconds 80785 0
15-11-2013 17:11:58 Media Recovery Apply Time per Log Seconds 8 0
15-11-2013 17:11:58 Media Recovery Checkpoint Time per Log Seconds 1 0
15-11-2013 17:11:58 Media Recovery Elapsed Time Seconds 5767807 0
======================================================================================================================
Data guard broker information, if relevant:
DG broker
--------------------
DISABLED
======================================================================================================================
No comments:
Post a Comment