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后原主备库状态：

--orcl SQL> select name,database_role,open_mode from v$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- -------------------- ORCL PRIMARY READ WRITE SQL> select status from v$instance; STATUS ------------ OPEN --orcl_adg SQL> select name,database_role,open_mode from v$database; NAME      DATABASE_ROLE    OPEN_MODE --------- ---------------- -------------------- ORCL      PRIMARY          READ WRITE SQL> select status from v$instance; STATUS ------------ OPEN

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

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 776646656 bytes Fixed Size 2257272 bytes Variable Size 507514504 bytes Database Buffers 264241152 bytes Redo Buffers 2633728 bytes Database mounted.

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

[oracle@orcl ~]$ dgmgrl sys/oracle@orcl_adg DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production Copyright (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 reinstated Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> help reinstate; Changes a database marked for reinstatement into a viable standby Syntax: 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 open Database 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 si SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 24 14:57:47 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name,database_role,open_mode from v$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- -------------------- ORCL PHYSICAL STANDBY READ ONLY WITH APPLY SQL> 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 1550863 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 776646656 bytes Fixed Size 2257272 bytes Variable Size 507514504 bytes Database Buffers 264241152 bytes Redo Buffers 2633728 bytes Database 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 mounted ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area  776646656 bytes Fixed Size                  2257272 bytes Variable Size             507514504 bytes Database Buffers          264241152 bytes Redo Buffers                2633728 bytes Database 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.