Oracle 19C Data Guard 基础运维 -0 7 failover 后闪回恢复 dg 架构
将失败的主数据库闪回物理备用数据库
Flashing Back a Failed Primary Database into a Physical Standby Database
这些步骤将旧的 primary database 作为 physical standby database 带回到Oracle Data Guard 配置中。
These steps bring the old primary database back into the Oracle Data Guard configuration as a physical standby database.
以下步骤假设已对物理备用数据库执行 failover ，并且在 failover 时已在旧主数据库上启用了闪回数据库。
The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover.
原主库提前启用闪回数据库
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
SQL> show parameter recover y
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL>alter system set db_recovery_file_dest_size=10 g scope=spfile;
[root@cjcos02 ~]# mkdir /flashbak
[root@cjcos02 ~]# chown oracle.oinstall /flashbak
SQL>alter system set db_recovery_file_dest='/flashbak' scope=spfile;
SQL> alter database flashback on;
SQL> shutdown immediate
SQL> Startup
SQL>select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
原主库因故障宕机无法open,dg 备库系统执行 failover;
备库执行failover( 可参考如下链接 ) ：
http://blog.itpub.net/29785807/viewspace-2686922/
http://blog.itpub.net/29785807/viewspace-2687052/
此时原主库无法直接转换成备库
SQL> alter database commit to switchover to physical standby with session shutdown;
SQL> recover managed standby database using current logfile disconnect from session;
ORA-01665: control file is not a standby control file
需要执行如下步骤，将原主库转换为Physical Standby Database ：
1. 在新主数据库上，发出以下查询以确定旧备用数据库成为新主数据库的 SCN
On the new primary database, issue the following query to determine the SCN at
which the old standby database became the new primary database:
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
3874221
2. 关闭旧的主数据库 ( 如果需要的话 ) ，挂载，并将它闪回到上一步中查出的 STANDBY_BECAME_PRIMARY_SCN 值。
Shut down the old primary database (if necessary), mount it, and flash it back to the value for STANDBY_BECAME_PRIMARY_SCN that was determined in the previous step.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
---SQL> FLASHBACK DATABASE TO SCN standby_became_primary_scn;
SQL> FLASHBACK DATABASE TO SCN 3874221;
3. 若要将数据库转换为物理备用数据库，请在旧主数据库上发出以下语句
To convert the database to a physical standby database, issue the following
statement on the old primary database:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
4. 要开始将 redo 传输到新的物理备用数据库，请在新的主数据库上执行以下步骤
To start transporting redo to the new physical standby database, perform the
following steps on the new primary database:
a. 发出以下查询，查看存档目的地的当前状态 :
a. Issue the following query to see the current state of the archive destinations:
SQL> col dest_name for a20
SQL> col destination for a10
SQL> col protection_mode for a25
SQL> col error for a15
SQL> col srl for a10
SQL> set linesize 200
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
......
b. 如有必要，启用 destination:
b. If necessary, enable the destination:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
执行日志切换，以确保备用数据库开始接收来自新主数据库的重做数据，并验证它已成功发送。在新的主数据库上发出以下SQL 语句 :
c. Perform a log switch to ensure the standby database begins receiving redo
data from the new primary database, and verify it was sent successfully. Issue
the following SQL statements on the new primary database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION,ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
在新的备用数据库上，您可能还需要更改LOG_ARCHIVE_DEST_n 初始化参数，以便重做传输服务不会将重做数据传输到其他数据库。
On the new standby database, you may also need to change the
LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services
do not transmit redo data to other databases.
5. 启动重做应用到新的物理备用数据库
Start Redo Apply on the new physical standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
6. 查看数据同步
新主库31.100 chendb:
SQL> insert into test1 values(4);
1 row created.
SQL> insert into test1 values(5);
1 row created.
SQL> commit;
新备库31.90 cjcdb:
SQL> select * from test1;
ID
----------
1
2
3
4
5
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
