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
=====================================================================================================================

2 comments:

Stephen Otieno said...

Have same error but checked with MRP and it is not running!! and my Managed Standby Recovery is not active..Please help.

Sri said...

Please try to start the mrp process and check what are the errors recorded in the alert log file? And also check if any zombie mrp process is running.