Troubleshoot snapshot database:
....................................................................
~Problem:
------------
When convert physical standby to snapshot database the following error can be raised.
Alert log error:
ORA-00313: open failed for members of log group 7 of thread 0
grep message from V$DATAGUARD_STATUS from standby database:
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
RFS[4]: No standby redo logfiles created for T-1 dataguard
~Solution:
------------
Troubleshoot snapshot database technique is drawn below in the following steps.
step 1:
crosscheck thread# from v$log and v$standby_log from both primary standby database.
SQL> SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 2532 209715200 NO CURRENT
1 2 2530 209715200 YES INACTIVE
1 3 2531 209715200 YES ACTIVE
SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------
0 4 0 419430400 YES UNASSIGNED
0 5 0 419430400 YES UNASSIGNED
0 6 0 419430400 YES UNASSIGNED
0 7 0 419430400 YES UNASSIGNED
step 2:
if mismatch then match thread# number in standby log according to thread# number of physical standby.
In standby:
alter database recover managed standby database cancel;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 4;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 5;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 6;
ALTER DATABASE DROP STANDBY LOGFILE GROUP 7;
alter database add standby logfile thread 1 'path/stbr1.log' size 200M;
alter database add standby logfile thread 1 'path/stbr2.log' size 200M;
alter database add standby logfile thread 1 'path/stbr3.log' size 200M;
alter database add standby logfile thread 1 'path/stbr4.log' size 200M;
step 3:
startup mount force;
step 4:
alter database convert to snapshot database;
step 5:
alter database open;
Very informative write up. Thank you for your effort.
ReplyDelete