Recipes for Creating a Managed Standby with RMAN

Over-the-Top Tales from the Trenches.
Motto: Bringing order to the chaos of every-day DBA life.

Dear Diary,

Today I created safety nets — redundant databases in the form of managed standbys.

Like a lot of DBA-related work, this comes in the form of a recipe. This recipe is not set in stone. Rather, it’s an evolving work — a tinker here, a tinker there, always looking for the extra ounce of speed, like a F1 Ferrari mechanic working on the racing car that Michael Schumacher hurls round the track.

The real aim of the managed standby is redundancy. (That is where Michael sloughs off the track at 250 miles per hour and magically is back on the track racing after a short downtime.)

So, on to the recipes.

This recipe is based on Oracle 10G Release 2 and assumes:

1. Directory structure is the same on both primary and standby machines.
2. Backup is going locally to disk at location source_backup_directory
3. TNSNAMES entry STANDBY points to standby db on both machines.
4. TNSNAMES entry PROD points to prod db on both machines.
5. RMAN catalog exists, and is resolved via TNSNAMES entry rcatdb.
6. RMAN default channel locations.

On Source/Primary db:

su - oracle
rman target / catalog [email protected]
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP CHECK LOGICAL FULL DATABASE PLUS ARCHIVELOG;
RMAN> exit;

scp source_backup_directory [email protected]:standby_backup_directory

On Destination or Standby db:

sqlplus "/ as sysdba"
startup nomount
exit;

On Source/Primary db:

rman target / catalog [email protected] auxiliary [email protected]

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
RMAN> exit;

On Source/primary db:

show parameter log_archive_dest;
alter system set log_archive_dest_x = 'SERVICE=STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD';
alter system set log_archive_dest_state_x = 'ENABLE';

On Destination or Auxiliary STANDBY:

alter system set FAL_SERVER = 'PROD';
alter system set FAL_CLIENT = 'STANDBY';

shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

To check progress:

On Primary:

column destination format a30

select dest_id,destination,status,database_mode,recovery_mode,error from V$ARCHIVE_DEST_STATUS
where status != 'INACTIVE';

To check progress on Primary or Standby:

select * from v$managed_standby;

For the complete recipe and for other versions please visit the online documentation.

• Oracle 10G Release 2
• Oracle 10G Release 1
• Oracle 9i Release 2

Everyone knows the idea. In the cookbook, the picture of the pavlova¹ is a picture of delicious, crunchy but soft meringue dripping with passionfruit sauce, topped with ripe strawberries. The recipe seems simple, but you end up with something like a white flat dinner plate with the consistency of styrofoam.

So how does your Grandma use the pavlova recipe and not make the infamous styrofoam plate? Practice (of course), and insider tips.

The final steps in the ordination to the RMAN-managed standby priesthood are the insider secrets. Listen up, padawan².

Caveats:

1. Make sure you take a backup of the controlfile for standby before any level 0 backup…

   RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

   This will allow RMAN to recover the standby and roll forward using all available archive logs.

2. Either have the backup go to a tape library, a NFS/SAN volume which is accessible by both boxes, or get all the backup files to the proposed standby.
3. Having a weird or complex or substantially different directory on the proposed standby makes baby Jesus cry³. Try to get them the same. The return on investment over time will be substantial, not only for this project, but for ongoing maintenance, ease of administration, and so on.
4. Make sure that the standby and primary can see each other via tnsnames first.

So why go to the trouble of learning how to implement a managed standby using RMAN? If your primary db is in archivelog mode the whole operation is online. Online as in, no downtime on the primary.

If you are proactive and take a backup of the controlfile for standby every night as part of an RMAN backup, you can create a new standby from last night’s RMAN backup whereever and whenever you like.

The take-home message:
Implementing standbys has never been easier or more straight-forward than using
RMAN DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;

Have Fun.
Paul

Update: 2006/11/04: Adding the startup nomount to standby database. This is makes sure the pfile is readable and the memory can be sourced.

See prior articles in this series.

References:
1. Pavlova
1. Pavlova, again
2. Padawan
3. make baby Jesus cry