Reinstate Failed Primary Database into Physical Standby

官方文档：

Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby (Doc ID 738642.1)

一. 利用闪回

前提条件：

a. failover之前需要在主库和备库开启闪回

b. 必须有"备转主"的时刻到现在所有的闪回日志

c. 主备数据库能够通过网络连接

d. failover中的原主库必须在dg broker的配置范围内

1. 使用dgmgrl管理工具

step1：failover to orcl_adg

DGMGRL> failover to orcl_adg;Performing failover NOW, please wait...Failover succeeded, new primary is "orcl_adg"

failover后原主备库状态：

--orclSQL> select name,database_role,open_mode from v$database;NAME      DATABASE_ROLE    OPEN_MODE--------- ---------------- --------------------ORCL      PRIMARY          READ WRITESQL> select status from v$instance;STATUS------------OPEN--orcl_adgSQL> select name,database_role,open_mode from v$database;NAME      DATABASE_ROLE    OPEN_MODE--------- ---------------- --------------------ORCL      PRIMARY          READ WRITESQL> select status from v$instance;STATUS------------OPEN

step2：将原主库启动到mount状态

SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area  776646656 bytesFixed Size                  2257272 bytesVariable Size             507514504 bytesDatabase Buffers          264241152 bytesRedo Buffers                2633728 bytesDatabase mounted.

step3：使用dgmgrl连接到任意数据库执行恢复命令

[oracle@orcl ~]$ dgmgrl sys/oracle@orcl_adgDGMGRL for Linux: Version 11.2.0.4.0 - 64bit ProductionCopyright (c) 2000, 2009, Oracle. All rights reserved.Welcome to DGMGRL, type "help" for information.Connected.DGMGRL> show configuration;Configuration - c1  Protection Mode: MaxPerformance  Databases:    orcl_adg - Primary database    orcl     - Physical standby database (disabled)      ORA-16661: the standby database needs to be reinstatedFast-Start Failover: DISABLEDConfiguration Status:SUCCESSDGMGRL> help reinstate;Changes a database marked for reinstatement into a viable standbySyntax:  REINSTATE DATABASE <database name>;DGMGRL> reinstate database orcl;Reinstating database "orcl", please wait...Operation requires shutdown of instance "orcl" on database "orcl"Shutting down instance "orcl"...ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.Operation requires startup of instance "orcl" on database "orcl"Starting instance "orcl"...ORACLE instance started.Database mounted.Continuing to reinstate database "orcl" ...Reinstatement of database "orcl" succeeded

二. 利用sqlplus手工执行

step1：手工failover

[oracle@orcl ~]$ ora siSQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 24 14:57:47 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name,database_role,open_mode from v$database;NAME      DATABASE_ROLE    OPEN_MODE--------- ---------------- --------------------ORCL      PHYSICAL STANDBY READ ONLY WITH APPLYSQL> alter database recover managed standby database cancel;Database altered.SQL> alter database recover managed standby database finish;Database altered.SQL> alter database activate standby database;Database altered.

step2：确定failover备转主时的scn

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)----------------------------------------1550469

step3：将原主库闪回到上一步备转主时刻的scn

SQL> select name,current_scn from v$database;NAME      CURRENT_SCN--------- -----------ORCL          1550863SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area  776646656 bytesFixed Size                  2257272 bytesVariable Size             507514504 bytesDatabase Buffers          264241152 bytesRedo Buffers                2633728 bytesDatabase mounted.SQL> flashback database to scn 1550469;Flashback 

step4:：将原主库转换到备库并重启到Mount状态

SQL> alter database convert to physical standby;Database altered.SQL> shutdown immediate; ORA-01507: database not mountedORACLE instance shut down.SQL> startup mount;ORACLE instance started.Total System Global Area  776646656 bytesFixed Size                  2257272 bytesVariable Size             507514504 bytesDatabase Buffers          264241152 bytesRedo Buffers                2633728 bytesDatabase mounted. 

step5：设置新主库日志传输参数

SQL> alter system set log_archive_dest_2='service=orcl_adg lgwr async valid_for=(online_logfiles,primary_roles) db_unique_name=orcl_adg' sid='orcl';System altered.SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;System altered.SQL> alter system switch logfile; --确认日志能够传送到新备库System altered.

step5：开启日志应用

SQL> alter database recover managed standby database disconnect from session;Database altered.SQL> alter database open read only;Database altered.--开启实时日志应用SQL> alter database recover managed standby database cancel;Database altered.SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered.