Dataguard Monitoring Scripts

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAPFROM(SELECT NAME DB_NAMEFROM V$DATABASE),(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,’.’),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,’.’)-1))))) HOSTNAMEFROM V$INSTANCE),(SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’),(SELECT TO_CHAR(MAX(COMPLETION_TIME),’DD-MON/HH24:MI’) APPLIED_TIMEFROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);
================================================================================
SQL> select status,instance_name,database_role fromv$instance,v$database;
STATUS       INSTANCE_NAME    DATABASE_ROLE
——————————————–
OPEN         prim             PRIMARY
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
————–
40
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.
SQL> select status,instance_name,database_role fromv$database,v$instance;
STATUS   INSTANCE_NAME DATABASE_ROLE
——————————————
MOUNTED  stnd          PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log where applied=’YES’;
MAX(SEQUENCE#)
————–
40
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
——————————-
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
RFS       IDLE          41
RFS       IDLE          0
RFS       IDLE          0
RFS       IDLE          0
MRP0      WAIT_FOR_LOG  41
9rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select status,instance_name,database_role,open_mode fromv$database,v$instance;
STATUS INSTANCE_NAME  DATABASE_ROLE    OPEN_MODE
—————————————————
OPEN   stnd           PHYSICAL STANDBY READ ONLY
Step 6:
Now start the MRP on the physical standby database.
SQL> alter database recover managed standby database disconnectfrom session;
Database altered.
SQL> select process,status,sequence# from v$managed_standby;
PROCESS   STATUS        SEQUENCE#
——————————-
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
ARCH      CONNECTED     0
RFS       IDLE          41
RFS       IDLE          0
RFS       IDLE          0
RFS       IDLE          0
MRP0      WAIT_FOR_LOG  41
9rows selected.
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
Managing dataguard – monitoring scripts
  LOGS             TIME—————- ——————
Last applied   :  16-JUL-09:09:24:16
Last received :  16-JUL-09:09:28:36
select ‘Last applied  : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Timefrom v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
NAME                   VALUE                  UNIT———————- ———————- ———————————–
apply finish time      +00 00:02:07.2         day(2) to second(1) interval
apply lag              +00 00:01:59           day(2) to second(0) interval
estimated startup time 16                     second
standby has been open  N
transport lag          +00 00:00:00           day(2) to second(0) interval
Time Computed: 16-JUL-2009 09:33:16
selectNAME Name,
VALUE Value,
UNIT Unit
from v$dataguard_stats
union
select null,null,’ ‘ from dual
union
select null,null,’Time Computed: ‘||MIN(TIME_COMPUTED)
from v$dataguard_stats;
  Redo onsite
——————–
16-JUL-2009 09:42:44
select to_char(max(last_time),’DD-MON-YYYY HH24:MI:SS’) “Redo onsite”from v$standby_log