FAILOVER失败切换后复原DATAGUARD环境

FAILOVER失败切换后复原DATAGUARD环境

为了确保DG环境可靠，有时需要按计划执行failover验证，执行完验证后，还要复原DG环境，为了简化过程可以考虑使用flashback技术进行复原。

1      备库创建还原点

1.1   检查备库闪回区设置

如果没有设置闪回区，先设置。

show parameter db_recovery_file_dest

会显示两个参数，db_recovery_file_dest_size，为闪回区空间大小。db_recovery_file_dest，为闪回区目录位置。应尽量设置大一些。

show parameter db_flashback_retention_target

闪回区内对象（主要考虑闪回日志）保留时间，以分钟为单位，通常验证时间不会超过1天，因此默认值1440即可。

如果没有设置闪回，则按以下步骤设置备库闪回区。

alter system set db_recovery_file_dest_size=500G;

alter system set db_recovery_file_dest='/oradata/flash';

shu immediate

startup mount

alter database flashback on;

alter database open;

alter database recover managed standby database using current logfile disconnect from session;

1.2   备库创建还原点

为了利用flashback database闪回特性，需要先创建一个还原点，后面会用到。

alter database recover managed standby database cancel;

create restore point rp_yanlian guarantee flashback database;

确认还原点

select name,scn,time from v$restore_point;

应该有rp_yanlian的信息

主库切归档并禁止传输

alter system archive log current;

alter system set log_archive_dest_state_3=defer;

1.3   备库failover后打开

备库执行failover，强制切换为主库(模拟灾难场景：主库断电)

alter database recover managed standby database finish force;

alter database commit to switchover to primary;

确认控制文件状态

select CONTROLFILE_TYPE from v$database;

应该是current

alter database set standby database to maximize performance;

alter database open;

因为强制断开主库，因此备库后台告警日志会出现FAL[server, ARC2]: Error 16009 creating remote archivelog，忽略。

1.4   验证激活的备库可访问性

sqlplus / as sysdba

create table test1 (id int);

insert into test1 values (1);

commit;

select * from test1;

通知业务系统访问，验证连通性及可读写性。

2      数据库恢复DG

等待演练结束后，恢复DG环境。

2.1   关闭备库并启动到mount状态

shutdown abort

startup mount

2.2   闪回数据库

  flashback database to restore point rp_yanlian;

  select controlfile_type from v$database;

  应该是BACKUP

  alter database convert to physical standby;

  shutdown immediate

  startup mount

  alter database recover managed standby database disconnect;

  select controlfile_type from v$database;

  应该是STANDBY

2.3   主库继续同步

主库上

  alter system archive log current;

  alter system set log_archive_dest_state_3=enable;

备库上验证

  alter database recover managed standby database cancel;

  alter database open read only;

  select * from test1;

这里应该报错ORA-00942，说明备库已经与主库一致，测试数据被清除。

  shutdown immediate 

  startup mount

  drop restore point rp_yanlian ;

alter database open;

  alter database recover managed standby database using current logfile disconnect;

2.4   验证DG同步状态

set lin 120 pages 100

col PROCESS for a11

col PID for 999999999

col STATUS for a15

col CLIENT_PROCESS for a11

col CLIENT_PID for a11

col GROUP# for a5

col SEQUENCE# for 99999999

col BLOCK# for 99999999

col BLOCKS for 99999999

col DELAY_MINS for 99999999

col THREAD# for 99

select PROCESS,PID,STATUS,CLIENT_PROCESS,CLIENT_PID,GROUP#,THREAD#,SEQUENCE#,BLOCK#,BLOCKS,DELAY_MINS from v$managed_standby;

如果观察到mrp进程对应的block#在增加则说明DG正常。

参考：How To Open Physical Standby For Read Write Testing and Flashback (Doc ID 805438.1)