起因:闪存区空间不足,导致DG出现异常。
恢复正常后,发现主、备库DG状态PROTECTION_LEVEL均为RESYNCHRONIZATION,
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM RESYNCHRONIZATION
此时DG同步状态虽说设置为“MAXIMUM AVAILABILITY”,但实际上却非实时同步,而变为了异步同步了。
1、检查DG最高可用性(MAXIMUM AVAILABILITY)的
SQL> show parameter log_archive_dest_2
包含有lgwr sync affirm ,是正确的。
2、检查alert日志,发现有如下提示:
RFS[6]: No standby redo logfiles available for thread 1
RFS[6]: No standby redo logfiles selected (reason:7)
发现备库缺少standby logfile。
数据库目录下 /data/oracle/app/oracle/oradata/orcl/ 下无 std_redo*.log 文件,而主库里有
3、查看备库redo 信息
SQL> select group#,type, member from v$logfile;
GROUP# TYPE MEMBER
---------- ------- -------------------------------------------------------
3 ONLINE /data/oracle/app/oracle/oradata/orcl/redo03.log
2 ONLINE /data/oracle/app/oracle/oradata/orcl/redo02.log
1 ONLINE /data/oracle/app/oracle/oradata/orcl/redo01.log
11 STANDBY /data/oracle/app/oracle/oradata/orcl/std_redo11.log
12 STANDBY /data/oracle/app/oracle/oradata/orcl/std_redo12.log
13 STANDBY /data/oracle/app/oracle/oradata/orcl/std_redo13.log
4、删除备库redo的standby列表
SQL> alter database drop logfile group 11;
SQL> alter database drop logfile group 12;
SQL> alter database drop logfile group 13;
5、暂时关闭取消Redo Apply(否则会报 ORA-01156: recovery or flashback in progress may need access to files错误)
SQL> alter database recover managed standby database cancel;
6、添加备用日志(standby redo )
SQL> alter database add standby logfile group 11 ('/data/oracle/app/oracle/oradata/orcl/std_redo11.log') size 50M;
SQL> alter database add standby logfile group 12 ('/data/oracle/app/oracle/oradata/orcl/std_redo12.log') size 50M;
SQL> alter database add standby logfile group 13 ('/data/oracle/app/oracle/oradata/orcl/std_redo13.log') size 50M;
7、启用实时重做应用Redo Apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
现在再看看DG状态,已经恢复正常了。
SQL> select protection_mode,protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY