Data Guard standby redo logs / resizing the primary’s redo logs

Data Guard standby redo logs are worth a few comments.  The misconception that there have to be the exact same number of standby redo logs as there are primary redo logs seems to come up often.  In fact, the size has to be the same, but the standby should always have at least one more standby redo log group than the primary does.  As well, if you are using threads/RAC on the primary, then you need to have one extra standby redo log group for each primary thread.

For example, a RAC database with three threads and 3 log groups in each thread would have 9 redo logs.  A physical standby hanging off this primary database would then need to have a minimum of 12 standby redo logs.  A logical standby would as well, but could be given even more standby redo logs to help with realtime log apply.

If you experience a large load/throughput on your primary database, and you do not have enough standby redo logs, you may run into this error:

RFS[89]: No standby redo logfiles of size 203806 blocks available

This is not to be confused with the error received when there is no standby redo log that matches the primary redo log’s size.

This section of the data guard documentation explains how to configure standby redo logs.

To see your standby redo logs, you use a special v$ view, v$standby_log.  The only thing about this view to note is that “UNASSIGNED” means the same thing as “INACTIVE” does in the v$log view of normal redo logs.

Recently due to log file switch contention, I had to increase the size and number of the primary’s redo logs, which then meant doing the same to the standby logs.  Since they are 10g logical standbys, they are open and also have their own redo logs.  Those logs did not have to be modified.

To make primary redo log changes:
-- prod and standbys: set STANDBY_FILE_MANAGEMENT to MANUAL so file changes
-- are not transferred to standbys
 alter system set STANDBY_FILE_MANAGEMENT=MANUAL scope=both;
-- standbys: stop log apply
 ALTER DATABASE STOP LOGICAL STANDBY APPLY;
-- add new redo groups/logs - first a set of new ones,
-- then log switch until the database
-- is using the new ones, then drop the old groups and
-- replace them with the new size/naming
-- and the old group number
alter database add logfile group 4 ('/u03/oradata/MYDB/redo04.log') size 512M;
 -- repeat until the new logs are active/current and the old ones are not
 alter system switch logfile;
 -- then:
 alter system checkpoint;
 select * from v$log;
 -- can't drop a logfile group until it is archived
 alter system archive log group X;
 -- drop the old groups
 alter database drop logfile group 1;
-- on the file system:
## clean up the old files
 mv /u03/oradata/MYDB/redo101.log /u03/oradata/MYDB/old
alter database add logfile group 1 ('/u03/oradata/MYDB/redo01.log') size 512M;

---------------------------------------------------------------------
-- logical standbys - run on each - run on standby db unless noted
---------------------------------------------------------------------

-- stop log apply again to be sure
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 101 
('/u03/oradata/MYDB/stbyredo101.log') SIZE 512M;
-- repeat log switches on PRODUCTION as necessary for standby redo
-- logs to become "unassigned"
alter system switch logfile;
-- remove one standby redo log at a time, then replace it with the
-- renamed log
ALTER DATABASE DROP STANDBY LOGFILE GROUP 10;

-- on the file system:
## clean up the old files
mv /u03/oradata/MYDB/redo10.log /u03/oradata/MYDB/old
-- rename the online redo files on the standby and add more logs to match prod
-- online redo in standbys is also overtaxed by reporting activity and log apply
alter database add logfile group 4 ('/u03/oradata/MYDB/redo04.log') size 512M;
-- issue this in the STANDBY db as often as needed to free up the online redo files
alter system switch logfile;
alter database drop logfile group 1;

-- on the file system:
alter database add logfile group 1
('/u03/oradata/MYDB/redo01.log') size 512M;
## clean up the old files
mv /u03/oradata/MYDB/redo101.log /u03/oradata/MYDB/old
-- in Production: set STANDBY_FILE_MANAGEMENT back to AUTO
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

-- may want to restart the standbys here due to possible bug.
-- start log apply in standbys
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

One minor tricky thing — while the add group syntax uses parentheses, the drop logfile member syntax does not:

ALTER DATABASE DROP LOGFILE MEMBER '/u03/oradata/MYDB/redo10.log';  -- does not work
                                                                       with ()
Advertisements

8 thoughts on “Data Guard standby redo logs / resizing the primary’s redo logs

  1. Riz says:

    I want to resize my redo logs and standby redo logs on both at primary and physical standby databases.Could you please provide the step by step approach for resizing the both in a physiacl standby scenario.I would appreciate your swift response since generous people like you are the only source for a new DBA like me.

    Thanks,
    Riz

    • tylerv says:

      Riz — the link I provided above is actually to the documentation for physical standbys. The procedure really isn’t that different. http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#i1225703 Both physical and logical standbys should have N + 1 standby redo logs, where “N” is the number of redo logs in production (threads also should be taken into account — again see the documentation link above). Logicals sometimes need more standby redo logs than physicals, since they also write to normal redo logs as part of their normal work (since they are open and can have writes that are not from logical apply).

      Set up a test scenario in your lab or non-production environment to practice this on. I think you’ll find that the steps are the same for a physical standby, except that you won’t need to add the ‘normal’ redo logs to the physical standby. So just use the same steps, substitute the stop/start commands for a physical, and leave out the parts about creating redo logs and just create standby redo logs.

  2. Riz says:

    Dear Mr Tyler,

    You are indeed a great person.I wish there were other people like you around.
    Hats off to you.Thanks for a prompt response.

    • tylerv says:

      Riz – thanks, no problem at all, it’s nice to hear about people using my blog to solve problems!

  3. Srinath Nanduri says:

    I want to move the redologs & standby logs on the primary to a new location. For this, do I need to drop the logs and recreate? Can’t I just put the primary in a mount state & run the “alter database rename file ‘file1’ to ‘file2’ ? Please let me know… we are trying to save a drive letter by moving the logs to a different drive

    • tylerv says:

      I apologize, I didn’t get a notification – or missed it – for this comment. I’m sure you’ve solved this by now! What you are saying sounds like it would work just fine. If you have the space, you could also just create a whole set of new logs on the new drive, then drop all the old ones, which can all be done with the database online.

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