Oracle Data Guard
Resolving Wait_For_Log on Standby database
Today, I going to show how to resolve WAIT_FOR_LOG on standby database.
After applying incremental backup on standby, everything
goes smooth but after an hour I found we have experience
"WAIT_FOR_LOG". That means Real
Time data apply is not working (ie. Data
apply in memory using standby redo logs) .
BRIEF IMPLEMENTATION SUMMARY
- · Check alert logfile on standby
- · Stop Media recovery
- · Create standby redo on Primary & Standby
- · Start Media recovery
- · Validate the MRP process
mp.sql => select PROCESS,STATUS, THREAD#,SEQUENCE#,
BLOCK#, BLOCKS from v$managed_standby ;
open.sql=> select name, instance_name, open_mode, database_role,
flashback_on , current_scn from v$database,v$instance
assl.sql=> alter system switch logfile ;
MRP status on Standby
PROCESS STATUS THREAD#
SEQUENCE# BLOCK# BLOCKS
------- ------------ ------- ---------- ------
------
ARCH CONNECTED
0 0 0 0
ARCH CONNECTED
0 0 0 0
ARCH CONNECTED
0 0 0 0
ARCH CONNECTED
0 0 0 0
MRP0
WAIT_FOR_LOG 1
68 0 0
RFS IDLE
0 0 0 0
RFS IDLE
0 0 0 0
RFS IDLE
1 68 5 1
ON PRIMARY
SQL> @open
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN
--------- ---------------- -----------
---------------- -----------
PROD
PROD READ WRITE PRIMARY 1193534
ON STANDBY
SQL> @open
NAME
INSTANCE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN
--------- ---------------- -----------
---------------- -----------
PROD
STBY MOUNTED PHYSICAL STANDBY 1192213
From the above example we see Primary (PROD) is not sync with
Standby (STBY). We have approx SCN (1200+) number of difference. Standby will apply with archive log not
standby redo logs (in memory).
Checking standby redo log file on primary and standby. Since I have already created standby redo
logfile at the time building standby database.
ON PRIMARY
SQL> select GROUP#,DBID, THREAD#,STATUS from
v$standby_log ;
GROUP# DBID THREAD# STATUS
------ ------------- ---------- ----------
4
UNASSIGNED 0 UNASSIGNED
5
UNASSIGNED 0 UNASSIGNED
6
UNASSIGNED 0 UNASSIGNED
7
UNASSIGNED 0 UNASSIGNED
ON STANDBY
SQL> select GROUP#,DBID, THREAD#,STATUS from
v$standby_log ;
GROUP# DBID
THREAD# STATUS
------ ----------- ---------- ----------
4
UNASSIGNED 0 UNASSIGNED
5
UNASSIGNED 0 UNASSIGNED
6
UNASSIGNED 0 UNASSIGNED
7
UNASSIGNED 0 UNASSIGNED
Media Recovery status on Standby
Database
SQL> @mp
PROCESS
STATUS THREAD#
SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ----------
---------- ----------
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
MRP0
WAIT_FOR_LOG 1 70 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 1 70 5 1
From Standby Alert logfile :
From the alert logfile its clearly shows that Media Recovery is
waiting for sequence 71. To get rid of
this issue "WAIT_FOR_LOG" we have ensure that media recovery should
be done in memory.
Tue Sep 22 20:17:20 2015
Archived Log entry 35 added for thread 1 sequence 69
rlc 891019946 ID 0x120cba27 dest 2:
RFS[2]: No standby redo logfiles created for thread
1
RFS[2]: Opened log for thread 1 sequence 70 dbid
302858279 branch 891019946
Tue Sep 22 20:17:20 2015
Media Recovery Log /u01/archive/1_69_891019946.dbf
Media Recovery Waiting for thread 1 sequence 70 (in
transit)
Archived Log entry 36 added for thread 1 sequence 70
rlc 891019946 ID 0x120cba27 dest 2:
RFS[2]: No standby redo logfiles created for thread
1
RFS[2]: Opened log for thread 1 sequence 71 dbid
302858279 branch 891019946
Media Recovery Log /u01/archive/1_70_891019946.dbf
Media Recovery Waiting for thread 1 sequence 71
(in transit)
For this scenario I am Adding
Standby Redo Logfile on PRIMARY and STANDBY
SQL>
recover managed standby database cancel
;
Media recovery complete.
SQL>
alter database add standby logfile ;
Database altered.
SQL>
alter database add standby logfile ;
Database altered.
SQL>
recover managed standby database using current logfile disconnect ;
Media recovery complete.
Do some log switch on PRIMARY
DATABASE (PROD)
SQL> alter system swtich logfile ;
SQL> alter system swtich logfile ;
SQL> alter system swtich logfile ;
SQL> alter system swtich logfile ;
ON STANDBY
SQL> @mp
PROCESS
STATUS THREAD# SEQUENCE#
BLOCK# BLOCKS
--------- ------------ ---------- ----------
---------- ----------
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
ARCH
CONNECTED 0 0 0 0
MRP0
APPLYING_LOG 1 73 1
204800
RFS
IDLE 0 0 0 0
RFS
IDLE 0 0 0 0
RFS
IDLE 1 73 1 1
8 rows selected.
ALERT LOGFILE ON STANDBY DATABSE
Now we can see from alert logfile on standby database changes
are applying in memory using newly created standby redo logfiles.
.....
Tue Sep 22 20:36:09 2015
Archived Log entry 41 added for thread 1 sequence 75
rlc 891019946 ID 0x120cba27 dest 2:
RFS[2]: Selected log 8 for thread 1 sequence 76
dbid 302858279 branch 891019946
Media Recovery Log /u01/archive/1_75_891019946.dbf
Media Recovery Waiting for thread 1 sequence 76
(in transit)
Recovery of Online Redo Log: Thread 1 Group 8 Seq
76 Reading mem 0
Mem# 0:
/u01/app/ORADATA/STBY/onlinelog/o1_mf_8_c02vlbjc_.log
Tue Sep 22 20:36:39 2015
RFS[2]: Selected log 9 for thread 1 sequence 77 dbid
302858279 branch 891019946
Tue Sep 22 20:36:39 2015
Archived Log entry 42 added for thread 1 sequence 76
ID 0x120cba27 dest 1:
Tue Sep 22 20:36:39 2015
Media Recovery Waiting for thread 1 sequence 77
(in transit)
Recovery of Online Redo Log: Thread 1 Group 9 Seq
77 Reading mem 0
Mem# 0:
/u01/app/ORADATA/STBY/onlinelog/o1_mf_9_c02vld2c_.log
Validating the Status on primary
and standby site
ON PRIMARY
SQL>
@error
DEST_NAME
ERROR STATUS
APPLIED_SCN VER
-------------------- ------ --------- -----------
---
LOG_ARCHIVE_DEST_1
VALID 0 NO
LOG_ARCHIVE_DEST_2
VALID
1305612 NO
LOG_ARCHIVE_DEST_3
INACTIVE 0 NO
LOG_ARCHIVE_DEST_4
INACTIVE 0 NO
ON PRIMARY
SQL>
@open
NAME INST
OPEN_MODE DATABASE_ROLE CURRENT_SCN
---------- -------------------- ---------------------------
PROD PROD READ WRITE PRIMARY 1305612
ON STANDBY
SQL>
@open
NAME INST
OPEN_MODE DATABASE_ROLE
CURRENT_SCN
----- ---- -------------------- ----------------- ----------
PROD STBY READ ONLY WITH APPLY PHYSICAL STANDBY 1305613
Media Recovery status
SQL>
@mp
PROCESS
STATUS HREAD# SEQUENCE#
BLOCK# BLOCKS
--------- ------------ ------ ---------- ----------
----------
ARCH
OPENING 1
77 0 0
ARCH
CLOSING 1
78 1 2
ARCH
CONNECTED 0
0 0 0
ARCH
CONNECTED 0
0 0 0
MRP0
APPLYING_LOG 1 79 2003
204800
RFS
IDLE 0
0 0 0
RFS
IDLE 0
0 0 0
RFS
IDLE 1
79 2003 1
So, looking now everything
fine.
Hope this will help
No comments:
Post a Comment