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:
scp source_backup_directory oracle@standby:standby_backup_directory
On Destination or Standby db:
- create minimal initSID.ora:
sqlplus "/ as sysdba"
On Source/Primary db:
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK DORECOVER;
On Source/primary db:
On Destination or Auxiliary STANDBY:
To check progress:
select dest_id,destination,status,database_mode,recovery_mode,error from V$ARCHIVE_DEST_STATUS
To check progress on Primary or 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.
- Make sure you take a backup of the controlfile for standby before any level 0 backup… 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;