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 |
Filed under: Oracle 11g, Standby Database | Leave a comment »