Tuesday, October 6, 2020

Troubleshot snapshot database


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;

1 comment:

Change Sys Password

Change Sys password at primary and standby database (ADG) server: +++++++++++++++++++++++++++++++++++++++++++++++++++++ step 1: cancel MRP p...

Data Guard Broker Configuration