Managed recovery Failing with ORA-16157 after Finish Command was Executed by ...

刘泽宇

现象：
Disclaimer:


NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Primary Database:ORCL
standby database: standby
AUX  database name :dup


Managed recovery Fails with ORA-16157.
Below command was issued by mistake on standby
ALTER DATABASE RECOVER managed standby database finish

Alert log of standby

Mon Aug 29 12:16:48 2016
ALTER DATABASE RECOVER managed standby database finish     ==============> Finish command
Mon Aug 29 12:16:52 2016
Killing 4 processes with pids 10752,11048,10468,11480 (all RFS, wait for I/O) in order to disallow current and future RFS connections. Requested by OS process 10624

Terminal Recovery: thread 1 seq# 118 redo required
Media Recovery Waiting for thread 1 sequence 118
Terminal Recovery: End-Of-Redo log allocation   ---------------------END OF REDO
Terminal Recovery: standby redo logfile 4 created 'D:\11G\FAST_RECOVERY_AREA\STANDBY\ARCHIVELOG\2016_08_29\O1_MF_1_0_****_.ARC'
This standby redo logfile is being created as part of the
failover operation. This standby redo logfile should be
deleted after the switchover to primary operation completes.
Media Recovery Log D:\11G\FAST_RECOVERY_AREA\STANDBY\ARCHIVELOG\2016_08_29\O1_MF_1_0_****_.ARC
Terminal Recovery: log 4 reserved for thread 1 sequence 118
Recovery of Online Redo Log: Thread 1 Group 4 Seq 118 Reading mem 0
Mem# 0: D:\11G\FAST_RECOVERY_AREA\STANDBY\ARCHIVELOG\2016_08_29\O1_MF_1_0_****_.ARC
Identified End-Of-Redo (failover) for thread 1 sequence 118 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 4583146 time 08/29/2016 12:10:55
Terminal Recovery: successful completion

Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 16157
Errors in file D:\11G\diag\rdbms\standby\standby\trace\standby_pr00_11064.trc:
ORA-16157: media recovery not allowed following successful FINISH recovery





Restart of standby and restart of Managed recovery still gives the same issue.

改动：
ALTER DATABASE RECOVER managed standby database finish

Cause
The issue is caused by

ALTER DATABASE RECOVER managed standby database finish

was issued by mistake and received End-Of-Redo (failover) from the primary database

处理方法：
Goal of this Article is to assist customer when the above command was issued by mistake and they want the standby to be in sync with primary and receive and apply the new logs without having to recreate the standby database from fresh backup.

Step 1 :- Note down the path of the standby datafiles



Spool log to '/tmp/standbydetail.log'
Select name,file# from v$datafile ;
Show parameter control_file
Spool off
Shutdown immediate;

Rename the existing controlfile of standby using an OS command such as 'mv':


$ mv <original name> <backup name>


Step 2 :- From primary database create a standby controlfile

Option a :- Using sqlplus

Option b :-Using rman

Option a:- Using sqlplus


SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE as '/u01/controlfile_standby.ctl' ;
Scp this file to standby server in the original location of controlfile as specified in spfile/pfile of standby


On standby server

SQL>  Startup mount;


Option b :- Using Rman

RMAN> Connect target /
RMAN> backup current controlfile for standby format ‘/u01/standby_backup.ctl' ;

Starting backup at 29-AUG-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=12 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 29-AUG-16
channel ORA_DISK_1: finished piece 1 at 29-AUG-16
piece handle=/u01/standby_backup.ctl tag=TAG20160829T124451 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-AUG-16

Copy this backuppiece  created on primary database over to standby database

Now On standby server

$ rman target /
RMAN>Startup nomount ;
RMAN> restore standby controlfile from '<Path and file name you copied to standby from the backup taken on primary>' ;
RMAN>alter database mount ;


Step 3 :-  Verify the path of datafile on standby is correct   

SQL>  Select name ,file# from v$datafile ;


Since this controlfile was copied from the primary, you must check that the location and name of the datafiles correponds with those used by the standby database.  This is espcially an issue with OMF
datafile names.  

If the location OR datafile name for the standby datafiles needs updating, execute:

RMAN> connect target /
RMAN> catalog start with '<Directory where the file exist>' noprompt;
RMAN> switch database to copy;


Check that the datafile information:  

SQL>  select error,name from v$datafile_header;
Ensure the error column is NULL and the name of datafile is shown as the standby datafile path and name.

Step 4 :- Now restart managed recovery

SQL> Alter database recover managed standby database disconnect from session .


Clearing standby activation ID 2839755145 (0xa9433989)


The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: ALTER DATABASE CREATE STANDBY CONTROLFILE as 'u01:/standby/c2.CTL'
Mon Aug 29 12:26:06 2016
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='*';
Mon Aug 29 12:26:09 2016
Thread 1 advanced to log sequence 123 (LGWR switch)
Current log# 3 seq# 123 mem# 0: u01:\standby\standby\REDO03.LOG
Mon Aug 29 12:26:09 2016
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Mon Aug 29 12:26:09 2016
Archived Log entry 146 added for thread 1 sequence 122 ID 0xa9433989 dest 1:
Mon Aug 29 12:40:16 2016
Thread 1 advanced to log sequence 124 (LGWR switch)
Current log# 1 seq# 124 mem# 0: u01:\standby\standbyREDO01.LOG
Mon Aug 29 12:40:17 2016
Archived Log entry 153 added for thread 1 sequence 123 ID 0xa9433989 dest 1:
Thread 1 advanced to log sequence 125 (LGWR switch)
Current log# 2 seq# 125 mem# 0: u01:\standby\standby\REDO02.LOG
Mon Aug 29 12:40:19 2016
Archived Log entry 154 added for thread 1 sequence 124 ID 0xa9433989 dest 1:
Mon Aug 29 12:44:51 2016
Clearing standby activation ID 2839755145 (0xa9433989)



Standby is in sync with primary and receiving changes