Tuesday, January 21, 2014

SQL: standby database information


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