RECOVER managed standby database finish ADG停止恢复的处理 OMF datafile copy Switch to copy

 

备份库切换

alter database commit to switchover to physical standby with session shutdown; //切换至standby模式
shutdown immediate
startup mount
select switchover_status from v$database; //检查状态
alter database recover managed standby database disconnect; //开启standby应用恢复模式
select switchover_status from v$database; //再检查状态

•  

切换至主库

alter database commit to switchover to primary;
shutdown immediate
startup
select switchover_status from v$database;
select open_mode,database_role from v$database;

•  

检查状态

select sequence#,applied from v$archived_log; //检查DG归档日志是否完成恢复
select open_mode,database_role,switchover_status from v$database; //备库状态

select protection_mode,protection_level from v$database; //检查DG的保护模式

•  

应急切换Failover

alter database recover managed standby database finish; //停止应用恢复模式
alter database commit to switchover to primary; //转换standbydb为primary db
shutdown immediate
startup
select open_mode,database_role from v$database;

•  

PS：failover将破坏dataguard模式,需要重新配置dataguard,听说可以不用重新配置,。

1. flashback standby 

2. 重建contronfile 注意OMF下Oracle datafile 操作

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.

CHANGES

 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

SOLUTION

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;----- rename datafile auto

  

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)