Data Guard高级玩法：failover备库后，通过闪回恢复DG备库

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

为了能够在failover后能够恢复DG,需要在备库上开启flashback，如果不开启flashback的话，DG就要重新搭建。当然这也可以作为灾难演练一种方式，就是希望在备库上做一下演练工作，
而这个演练又不会想影响到目前的主库，而且能够尽可能模拟真实的情况。

一、开启Flashback Database

1、使用Flashback Database的前提条件

1）启用了flashback database
2）必须打开flash recovery area,若为RAC，flash recovery area必须位于共享存储中。
3）必须处于archivelog模式，开启FORCE LOGGING

2、开启Flashback Database步骤

以下步骤在备库执行：

1）验证是否启用了flashback database并确认FORCE LOGGIN是否开启
SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON       FORCE_LOGGING
------------------ ---------------------------------------
NO           YES

2）若flashback_on为"NO"，请开启flashback database,具体步骤如下：
SQL> ALTER SYSTEM SET db_recovery_file_dest_size=5g SCOPE=BOTH;
SQL> ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' SCOPE=BOTH;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;
SQL> alter database force logging;

3、确认数据库可以前滚到的SCN和Time的方法

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI
-------------------- -------------------
	     3012239 2024-07-10 16:56:11

二、DG备库failover操作

备库执行failover操作：

sys@ORA11G> alter database recover managed standby database cancel;

Database altered.

sys@ORA11G> alter database recover managed standby database finish force;

Database altered.

sys@ORA11G> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

sys@ORA11G> alter database commit to switchover to primary;

Database altered.

sys@ORA11G> alter database open;

Database altered.

sys@ORA11G> select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;
NAME	   OPEN_MODE		DATABASE_ROLE	 SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
---------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G	   READ WRITE		PRIMARY 	 FAILED DESTINATION   YES DISABLED NONE

进行数据读写测试

sys@ORA11G>  create table tt(id int,name varchar2(10));

Table created.

sys@ORA11G> insert into tt values (1,'AA');   

1 row created.

sys@ORA11G> insert into tt values (2,'BB');

1 row created.

sys@ORA11G> commit;

Commit complete.

sys@ORA11G> select * from tt;

	ID NAME
---------- ----------
	 1 AA
	 2 BB

三、利用flashback database重建DG

–在新的主库（原备库）上执行下面操作：

SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
3013053
SQL> shutdown immediate;
SQL> startup mount;      
SQL> flashback database to scn 3013053;              //这个值为在新主库上查询到的SCN值
SQL> select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;

NAME		OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
--------------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G		MOUNTED 	     PRIMARY	      NOT ALLOWED	   YES DISABLED NONE
SQL> alter database convert to physical standby;
SQL> shutdown immediate
SQL> startup
SQL> select name,open_mode, database_role, switchover_status, force_logging, dataguard_broker, guard_status from v$database;sys@ORA11G> sys@ORA11G> 

NAME		OPEN_MODE	     DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
--------------- -------------------- ---------------- -------------------- --- -------- -------
ORA11G		READ ONLY	     PHYSICAL STANDBY RECOVERY NEEDED	   YES DISABLED NONE
SQL> alter database recover managed standby database using current logfile disconnect from session;

验证备库同步正常，继续接受主库日志。

关注我，学习更多的数据库知识