Skip to end of metadata
Go to start of metadata

How to create a standby DB (Oracle 10g)

Recipes for Creating a Managed Standby with RMAN

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

  • Directory structure is the same on both primary and standby machines.
  • Backup is going locally to disk at location source_backup_directory
  • TNSNAMES entry STANDBY points to standby db on both machines.
  • TNSNAMES entry PROD points to prod db on both machines.
  • RMAN catalog exists, and is resolved via TNSNAMES entry rcatdb.
  • 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:

  • create minimal initSID.ora:

DB_NAME=PRIMARYDBNAME

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:

  • 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.
  • 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.
  • 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.
  • 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;

References:

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