RMAN Recipes: How to Switch Oracle Logs Automatically

So you have swallowed the standby bait. You have used RMAN duplicate to create a sparkling new standby and things are looking rosy. Then, when you check the lag between the primary and standby (in MAXIMUM PERFORMANCE mode), you discover the standby is miles behind.

You were expecting a solution that kept the standby as close to the primary as possible without the expense of making the primary wait until all changes are applied on the standby, as happens in MAXIMUM PROTECTION mode or MAXIMUM AVAILABILITY mode.

Oracle (at least from 9iG) rides to the rescue again. The parameter ARCHIVE_LAG_TARGET tells Oracle to make sure to switch a log every n seconds. It can be dynamically set using ALTER SYSTEM, e.g.:

ALTER SYSTEM SET ARCHIVE_LAG_TARGET = 600 SCOPE=BOTH;

This sets the maximum lag to 10 mins.

No more stuffing around with shell scripts to run essentially: ALTER SYSTEM SWITCH LOGFILE;. Oracle does it for you.

Feedback for Oracle: make the default something other than 0, maybe 1800. I am a crusty old DBA (well, not that crusty compared to some) and nowadays I expect stuff to work like this out-of-the-box. DWIM!

Have Fun!

Paul.

Update: Thanks to Howard for the correction, this has been around since 9i

Links: More info about Oracle data protection modes