Recipes for Creating a Managed Standby with RMAN

Sep 25, 2006 / By Paul Moen

Tags: ,

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 rcat_owner@rcatdb
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
RMAN> BACKUP CHECK LOGICAL FULL DATABASE PLUS ARCHIVELOG;
RMAN> exit;

scp source_backup_directory oracle@standby:standby_backup_directory

On Destination or Standby db:

sqlplus "/ as sysdba"
startup nomount
exit;

On Source/Primary db:

rman target / catalog rcat_owner@rcatdb auxiliary sys@STANDBY

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 pavlova1 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, padawan2.

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 cry3. 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

12 Responses to “Recipes for Creating a Managed Standby with RMAN”

  • ChuckSchultz says:

    In 10gR2, lock_name_space is deprecated, but that is the only way I have found of getting this recipe to work when both the primary and standby are on the same host. Is that not kind of strange? If that parameter were to go away, how does one have both databases cohabitate?

    Yes, I realize cohabitation is “bad”. That’s not the point.

  • [...] you have created your standby database using the RMAN DUPLICATE command, you have set the minimum log switch individual using ARCHIVE_LAG_TARGET, and you have sorted out [...]

  • naqi says:

    just in the process of creating a data guard environment myself, and found this page searching for ‘duplicate for standby..’
    .
    This is in response to chuck’s post – the parameter lock_name_space is deprecated, the replacement for that is to use the db_unique_name.

  • [...] you have created your standby database using the RMAN DUPLICATE command, you have set the ARCHIVE_LAG_TARGET to maintain a minimum lag target, and you have sorted out [...]

  • Iggy says:

    Will the DUPLICATE command work even though you have issued it while connected to the primary server? Can RMAN do its job across a network?

  • Paul M says:

    As long as the files RMAN are available on the machine to be the standby it should be fine.
    If you review the recipe and caveat 2, I recommended you push the backups to auxilary location or use shared storage which is visible to both primary and standby.

    In RMAN Standby naming, the primary db (which you are duplicating) is the target and the standby (which you are creating) is the auxiliary.

    Hope this helps
    Paul

  • Kumar says:

    Hi Paul
    This is very useful note. I noticed that the standby control file is created in $ORACLE_HOME/dbs (BACKUP CURRENT CONTROLFILE FOR STANDBY). Is there a way to specify this to be created in a particular Disk group if ASM is being used.

  • Zaffer Khan says:

    Request you to please take back your comments on Jesus.

    Regards,

  • PaulM says:

    Please check the recipe post again rather than just the summary at the end, before running the DUPLICATE command you must run the startup nomount on auxiliary (soon to be standby) database.

    Hope this helps
    Paul

  • Pedro says:

    Isn’t it missing here the password file for the auxiliary database connection (STANDBY)?

  • Pedro says:

    i am getting error ORA-01665

    something is missing in these steps…

  • Kam says:

    Hi,
    It wud be great if you add listener config details in standby server. Bcos it is essential to connect to standby using @connect_string during duplicate command.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>