Sunday 21 February 2016

ORA-16136: Managed Standby Recovery not active

Problem:
There is some problem in Oracle Dataguard. I want to switch the standby database to readonly mode. Firstly I cancel the standby managed recovery but it gave me this error:
SQL> alter database recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active



Solution:
1. Shutdown the standby db
SQL> shutdown immediate;

2. Start the standby
% set ORACLE_SID={ORACLE_SID}
% sqlplus /nolog
SQL> conn / as sysdba
SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;

2. Inspect the alert log i.e. %ORACLE_HOME%\admin\{ORACLE_SID}\bdump\alert_{ORACLE_SID}.log
===========================================================================
Managed Standby Recovery not using Real Time Apply
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001
Errors with log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001
MRP0: Background Media Recovery terminated with error 328
Fri Feb 02 12:15:48 2007
Errors in file c:\oracle\product\10.2.0\admin\{ORACLE_SID}\bdump\{ORACLE_SID}_mrp0_3800.trc:
ORA-00328: Message 328 not found; No message file for product=RDBMS, facility=ORA; arguments: [48917243] [51266725]
ORA-00334: Message 334 not found; No message file for product=RDBMS, facility=ORA; arguments: [E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001]
===========================================================================3. Inspect c:\oracle\product\10.2.0\admin\{ORACLE_SID}\bdump\{ORACLE_SID}_mrp0_3800.trc, it shows that MRP0 process tried to start but failed.===========================================================================
MRP0: Background Managed Standby Recovery process started
*** 2007-02-02 12:15:48.796 1011 krsm.c
Managed Recovery: Initialization posted.
*** 2007-02-02 12:15:48.796 60680 kcrr.c
Managed Standby Recovery not using Real Time Apply
Recovery target incarnation = 2, activation ID = 1875773575
Influx buffer limit = 38173 (50% x 76347)
Start recovery at thread 1 ckpt scn 51266725 logseq 6280 block 2
*** 2007-02-02 12:15:48.906
Media Recovery add redo thread 1
*** 2007-02-02 12:15:48.906 1011 krsm.c
Managed Recovery: Active posted.
*** 2007-02-02 12:15:48.968
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001
*** 2007-02-02 12:15:48.984 60680 kcrr.c
MRP0: Background Media Recovery terminated with error 328
ORA-00328: Message 328 not found; No message file for product=RDBMS, facility=ORA; arguments: [48917243] [51266725]
ORA-00334: Message 334 not found; No message file for product=RDBMS, facility=ORA; arguments: [E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001]
===========================================================================4. Check the file E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001 (on standby) with the one in primary database. They are different in size!!

5. Copy the ARC06280_0575569794.001 from primary to standby.

6. Shutdown and restart the standby db again.

7. Inspect the alert log on standby again. It seems standby database running normally again!
===========================================================================
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06280_0575569794.001
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06281_0575569794.001
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06282_0575569794.001
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06283_0575569794.001
Media Recovery Log E:\XXX_DB\ARCH\{ORACLE_SID}\ARC06284_0575569794.001
===========================================================================

8. Check the progress of apply of archive.
SQL> select sequence#, to_char(first_time,'YYYY-MM-DD HH24:MI') as first_time,
to_char(next_time,'YYYY-MM-DD HH24:MI') as next_time, applied from v$archived_log order by sequence#;

9. Finally turn off recover of standby and open the db in readonly mode. Great, It runs without error!
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;

No comments:

Post a Comment