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:

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

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.

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