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.