Oracle DBA – A lifelong learning experience

Incorrectly sized standby redo logs

Posted by John Hallas on February 28, 2011

When we have a situation where the standby redo logs on the standby database are not the same size as the online redo logs on the primary database, there is a strange redo log switching behaviour on the primary database. The primary  continually switched online redo logs every 2 minutes no matter how little activity was happening on the primary database.

Scenario

Primary database online redo logs 1000MB
Standby database online redo logs 1000MB
Standby redo logs 100MB

From v$log_history :

SEQUENCE# FIRST_TIME
---------- --------------------
      7388 08-FEB-2011 12:08:14
      7389 08-FEB-2011 12:10:16
      7390 08-FEB-2011 12:12:11
      7391 08-FEB-2011 12:14:16
      7392 08-FEB-2011 12:16:18
      7393 08-FEB-2011 12:18:20
      7394 08-FEB-2011 12:20:18
      7395 08-FEB-2011 12:22:17
      7396 08-FEB-2011 12:24:22
      7397 08-FEB-2011 12:26:24
      7398 08-FEB-2011 12:28:23

The following error is reported in the primary database alert log :
ORA-16086: standby database does not contain available standby log files

The standby database alert log reports the following error :

RFS[602]: No standby redo logfiles of size 1024000 blocks exist

RFS[602]: No standby redo logfiles selected (reason:7)

Errors in file /app/oracle/diag/rdbms/wfspre1b/WFSPRE1B/trace/WFSPRE1B_rfs_8236.trc:

ORA-16086: standby database does not contain available standby log files

The v$standby_log view on the standby database shows that the standby redo logs are not being used :

THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS     ARC Fisrt SCN Number First SCN Time              Last SCN Time

-------- ---------- ---- ---------- ---------- --- ---------------- --------------------------- ---------------------------
       1          0    4        100 UNASSIGNED YES                0
                  0    5        100 UNASSIGNED YES                0
                  0    6        100 UNASSIGNED YES                0
                  0    7        100 UNASSIGNED YES                0

Once the standby redo logs have been sized correctly, the redo log switching on the primary database resumes as normal, only switching when a log fills up.

THREAD#  SEQUENCE#  GRP    SIZE_MB STATUS     ARC Fisrt SCN Number First SCN Time              Last SCN Time

-------- ---------- ---- ---------- ---------- --- ---------------- --------------------------- ---------------------------
       1          0    5       1000 UNASSIGNED NO                 0
                  0    6       1000 UNASSIGNED YES                0
                  0    7       1000 UNASSIGNED YES                0
               7469    4       1000 ACTIVE     YES        154458055 08-Feb-11 23:01:51          09-Feb-11 13:41:06

The issue was fixed  by dropping the standby redo logs and recreating. We did try and repeat the issue on another database but could not get the same results

I have uploaded the scripts I used to resize the standby log  files into a seperate entry for convenience

2 Responses to “Incorrectly sized standby redo logs”

  1. […] Top Posts RMAN backup script – example – logging outputORA-19809: limit exceeded for recovery files – db_recovery_file_dest_size and archiver errorPurging statistics from the SYSAUX tablespaceApplying the 10.2.0.4.1 Patch Set Update (PSU)Reducing RMAN backup time for unevenly sized tablespaces Recovering from a cold backup using RMANHow to move the central Inventory (oraInventory) to another location. 11g – library cache mutex X – known bugPurging ADR log and trace files automaticallyWhat does the SQL92_SECURITY parameter actually do? « Incorrectly sized standby redo logs […]

  2. […] https://jhdba.wordpress.com/2011/02/28/incorrectly-sized-standby-redo-logs/ […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: