Problem Description
Oracle physical standby database is not able to synchronize with the primary database. From the standby database alert log we see the following logs.
Thu Sep 23 14:57:47 2010
RFS[2237]: Assigned to RFS process 8174
RFS[2237]: Identified database type as ‘physical standby’: Client is ARCH pid 25089
RFS[2236]: Opened log for thread 2 sequence 1532 dbid 1323895516 branch 728508318
RFS[2237]: Opened log for thread 2 sequence 1533 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:32 2010
RFS[2226]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_1_seq_2044.9918.730479031
Thu Sep 23 14:58:33 2010
RFS[2176]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1466.9896.730477033
Thu Sep 23 14:58:35 2010
RFS[2168]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1545.9817.730476973
Thu Sep 23 14:58:40 2010
RFS[2238]: Assigned to RFS process 8319
RFS[2238]: Identified database type as ‘physical standby’: Client is ARCH pid 2094
RFS[2238]: Opened log for thread 2 sequence 1466 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:41 2010
RFS[2239]: Assigned to RFS process 8321
RFS[2239]: Identified database type as ‘physical standby’: Client is ARCH pid 25230
Thu Sep 23 14:58:43 2010
RFS[2240]: Assigned to RFS process 8323
RFS[2240]: Identified database type as ‘physical standby’: Client is ARCH pid 31899
RFS[2240]: Opened log for thread 1 sequence 2044 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:51 2010
RFS[2241]: Assigned to RFS process 8334
RFS[2241]: Identified database type as ‘physical standby’: Client is LGWR SYNC pid 22086
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[2241]: No standby redo logfiles of size 102400 blocks available
RFS[2241]: No standby redo logfiles selected (reason:7)
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc:
ORA-16086: Redo data cannot be written to the standby redo log
Thu Sep 23 14:58:59 2010
RFS[2242]: Assigned to RFS process 8341
RFS[2242]: Identified database type as ‘physical standby’: Client is LGWR SYNC pid 6586
Thu Sep 23 14:58:59 2010
RFS[2178]: Possible network disconnect with primary database
From the trace file it is logged following entry.
[oracle@DRS-DB-01 ~]$ cat /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Trace file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: DRS-DB-01
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: bdafisdc1
Redo thread mounted by this instance: 1
Oracle process number: 124
Unix process pid: 8334, image: oracle@DRS-DB-01
*** 2010-09-23 14:58:52.578
*** SESSION ID:(88.91) 2010-09-23 14:58:52.578
*** CLIENT ID:() 2010-09-23 14:58:52.578
*** SERVICE NAME:() 2010-09-23 14:58:52.578
*** MODULE NAME:(oracle@DC-DB-01 (TNS V1-V3)) 2010-09-23 14:58:52.578
*** ACTION NAME:() 2010-09-23 14:58:52.578
The primary database is operating in MAXIMUM PROTECTION
or MAXIMUM AVAILABILITY mode, and the standby database does
not contain any viable standby redo logfiles.
ORA-16086: Redo data cannot be written to the standby redo log
Cause of the Problem
The problem happened due to recovery area of standby database becomes full and standby redo logs have no place for archival. As the standby redo logs can’t be archived, so they can’t free space in order to accommodate/write new logs.
Solution of the Problem
Increase the recovery area size of standby database. You can check the location of archived redo log file by,
SQL> connect / as sysdba
SQL> archive log list
If you see that archive destination is USE_DB_RECOVERY_FILE_DEST then the location will be specified by parameter DB_RECOVERY_FILE_DEST.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
+RECOVERY
Note that you have sufficient storage under +RECOVERY and also you have set proper size of the parameter db_recovery_file_dest_size.
To check the size of parameter db_recovery_file_dest_size issue,
SQL> show parameter db_recovery_file_dest_size
In order to increase the size of db_recovery_file_dest_size parameter issue,
SQL> alter system set db_recovery_file_dest_size = 1000G scope=both sid=’*’;