CNDBA社区
Oracle 11g Data Guard ORA-16086: Redo data cannot be written to the standby redo log 解决方法
作者：
dave
1现象
新搭建的11g Data Guard, 主库报如下错误：
[dave@www.cndba.cn1 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:24:12 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME
--------------------------------------------------------------------------------
STATUS PROCESS
--------- ----------
ERROR TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2
ERROR LGWR
ORA-16086: Redo data cannot be written to the standby redo log PARALLELSYNC
SQL>
[root@cd1 ~]# orz chkredo
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| GROUP | THREAD | SEQUENCE | STATUS | ARCHIVED | TYPE | SIZE(M) | MEMBER |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| 6 | 1 | 6 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log |
| 7 | 1 | 7 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log |
| 8 | 1 | 8 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log |
| 9 | 1 | 9 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log |
| 10 | 1 | 10 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log |
| 1 | 1 | 11 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log |
| 2 | 1 | 12 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log |
| 3 | 1 | 13 | INACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log |
| 4 | 1 | 14 | ACTIVE | YES | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log |
| 5 | 1 | 15 | CURRENT | NO | ONLINE | 2048 | /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log |
| 20 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log |
| 13 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log |
| 16 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log |
| 14 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log |
| 18 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log |
| 17 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log |
| 19 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log |
| 41 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log |
| 12 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log |
| 11 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log |
| 15 | | | | | STANDBY | | /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log |
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[root@cd1 ~]#
备库查询standby redo log 是有记录，但物理上不存在：
[dave@www.cndba.cn2 archivelog]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:22:48 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member for a60
SQL> select group#,type, member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ------- ------------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log
2 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log
3 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log
4 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log
5 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log
6 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log
7 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log
8 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log
9 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log
10 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log
11 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log
12 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log
13 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log
14 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log
15 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log
16 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log
17 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log
18 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log
19 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log
20 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log
41 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log
21 rows selected.
SQL>
SQL>
SQL>
[dave@www.cndba.cn2 onlinelog]$ ls /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log
ls: cannot access /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log: No such file or directory
2解决方法
此时直接drop 掉原来的redo 文件，并重建即可。
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
alter database drop logfile group 11;
alter database drop logfile group 12;
alter database drop logfile group 13;
alter database drop logfile group 14;
alter database drop logfile group 15;
alter database drop logfile group 16;
alter database drop logfile group 17;
alter database drop logfile group 18;
alter database drop logfile group 19;
alter database drop logfile group 20;
alter database drop logfile group 41;
alter database add standby logfile group 11 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log') size 2048M;
alter database add standby logfile group 12 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log') size 2048M;
alter database add standby logfile group 13 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log') size 2048M;
alter database add standby logfile group 14 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log') size 2048M;
alter database add standby logfile group 15 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log') size 2048M;
alter database add standby logfile group 16 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log') size 2048M;
alter database add standby logfile group 17 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log') size 2048M;
alter database add standby logfile group 18 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log') size 2048M;
alter database add standby logfile group 19 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log') size 2048M;
alter database add standby logfile group 20 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log') size 2048M;
alter database add standby logfile group 41 ('/u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log') size 2048M;
SQL> select group#,type, member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ------- ------------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo01.log
2 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo02.log
3 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo03.log
4 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo04.log
5 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo05.log
6 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo06.log
7 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo07.log
8 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo08.log
9 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo09.log
10 ONLINE /u01/app/oracle/oradata/CD_PD/onlinelog/redo10.log
11 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo11.log
12 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo12.log
13 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo13.log
14 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo14.log
15 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo15.log
16 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo16.log
17 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo17.log
18 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo18.log
19 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo19.log
20 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo20.log
41 STANDBY /u01/app/oracle/oradata/CD_PD/onlinelog/std_redo41.log
21 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
主库查询状态没变：
SQL> select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME
--------------------------------------------------------------------------------
STATUS PROCESS
--------- ----------
ERROR TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2
ERROR LGWR
ORA-16086: Redo data cannot be written to the standby redo log PARALLELSYNC
SQL>
重启下备库后再查询，恢复正常：
--备库：
[dave@www.cndba.cn2 archivelog]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 20 11:22:48 2020
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5344731136 bytes
Fixed Size 2237776 bytes
Variable Size 1895828144 bytes
Database Buffers 3388997632 bytes
Redo Buffers 57667584 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL>
--主库：
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL>
SQL> select DEST_NAME,STATUS,PROCESS,ERROR,TRANSMIT_MODE from v$archive_dest WHERE TARGET='STANDBY';
DEST_NAME
--------------------------------------------------------------------------------
STATUS PROCESS
--------- ----------
ERROR TRANSMIT_MOD
----------------------------------------------------------------- ------------
LOG_ARCHIVE_DEST_2
VALID LGWR
PARALLELSYNC
SQL>
实际上这个环境是RMAN异机迁移后重新搭建的DG， 源环境也是一个DG，但是RMAN 迁移过程中，没有将redo 和standby redo 文件还原出来，虽然这些文件在控制文件中有记录，但实际上没有，从而导致了一系列问题。
版权声明：本文为博主原创文章，未经博主允许不得转载。
