Wednesday, August 20, 2014

ORA-10458: standby database requires recovery


We want to place the Standby database to READ ONLY mode which is in Managed recovery mode of state MOUNTED.

##########################
## Error
##########################

ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

### Full Error

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

##########################
#  Error Occurred
##########################

Error occured while placing a standby database which is in mount stage to READ ONLY mode in 11.2.0.3.0 version

##########################
## Command Executed
##########################

alter database open read only;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check MRP is running
#########################################

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

To make standby database READ ONLY, all the datafiles should be consistent. Error is occuring because MRP is still running in the database which keeps the media recover on.

=====================================================================================================================

##########################
## Solution
##########################

#### Stop the Media recovery and then place the database in read only mode..

SQL> recover managed standby database cancel;
Media recovery complete.

SQL>  alter database open read only;
Database altered.



=====================================================================================================================
Then if you want to start the MRP, we can do like below,
=====================================================================================================================

SQL> Recover managed standby database disconnect from session using current logfile;
Media recovery complete.
SQL>

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp


=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================