Standby database

A standby database maintains a duplicate, or standby copy of your primary (also known as production database) and provides continued primary database availability in the event of a disaster (when all media is destroyed at your production site). A standby database is constantly in recovery mode. If a disaster occurs, you can take the standby database out of recovery mode and activate it for online use.

Some of the characteristics of Standby Databases are:

• You cannot query or open a standby database for any other purpose other than to activate disaster recovery. i.e. standby database is just a dummy sitting at a different location always in recovery mode, in case the production database goes down, then the standby database can be activated to become production.
• Once you activate your standby database, i.e. it becomes production, you cannot return it back to standby mode, because the standby database is now your current production, and you will have to re-create another standby database for the current production (your previous standby).
• You must place the datafiles, log files and control files of your primary and standby database on separate physical media. More Preferable to place the standby database on separate machine and media.
• It is advised to keep the datafile names and location/directories, the same on your production and standby database, if this is not possible use the datafile name conversion parameters. If you do not follow either of the approaches you may end up crashing your standby database.
• Your production database should be in archivelog mode, to create standby database. Else creating standby database makes no sense.
• To implement the standby database, you should have followed OFA, wherein all the datafiles should be present in single mount point, say /u01

Create a Standby database

• In order to create a standby database, the init.ora parameter COMPATIBLE must be 7.3 or higher, and the value of COMPATIBLE in the standby database *must* be identical to its value in the primary database.
• Create the control file on the Primary database (this will be used to build your standby database), substitute the filename with your choice. Once the command is executed the file with the name you have specified (filename) is created, usually in BACKGROUND_DUMP_DEST directory. It is advisable to have the filename (control filename) the same as the one you have in your production database.
  ALTER DATABASE CREATE STANDBY CONTROLFILE AS filename
• Archive the current online logs of the production database. So that the most recent transaction activities is trapped in your archive files. This command also ensures consistency among data files, control files, and log files.
  ALTER SYSTEM ARCHIVE LOG CURRENT
• Backup the datafiles, redolog files (either online or offline) of your production database.
• Transfer/Copy the control file, backed up data files and logfiles, archived redo logs to a remote machine using appropriate method (FTP, TAR.etc.)
• Copy the initprod.ora file pertaining to your production database to the server where standby database resides (let us call this file initstandby.ora).
• Add the following entries in your initstandyby.ora file for your standby database. These entries are added so that all filenames (datafiles listed in your control file) from your production database control file are converted for use by your standby database. For example you have your production datafiles in location /oracle and you want your datafiles pertaining to your standby database in directory /oracle/dbfiles then
  DB_FILE_STANDBY_NAME_CONVERT="/oracle","/oracle/standby"
  You can also set your LOG_FILE_STANDBY_NAME_CONVERT accordingly.
• Start up the Oracle instance of the standby database using NO MOUNT option.
  STARTUP NOMOUNT
• Mount the database in standby mode
  ALTER DATABASE MOUNT STANDBY DATABASE [EXCLUSIVE / PARALLEL]
• Place the standby database in recovery mode
  RECOVER [FROMlocation] STANDBY DATABASE
• As the archive logs are generated on your production box, you should continually transfer and apply them to the standby database. So that your standby and production are in sync.

In case of disaster (production database being lost due to media failure), your should follow the following steps to activate your standby database and make it production:

• Activate your standby database
  ALTER DATABASE ACTIVATE STANDBY DATABASE
• Shutdown your standby database
  SHUTDOWN
• Startup the standby database, which will be now your production database.
  STARTUP

Toggling Between Primary and Standby Databases

The procedure described here enables you to temporarily switch from your primary to your standby database, and then to switch back, without copying all of your resetlogs and datafiles. This approach is based on the assumption that the primary database's online logs and control file are available at the standby site via remote disk mirroring or another operational procedure (for example, copying the files over). This switchover approach also helps ensure that you don't lose transaction data recorded in the online logs of the primary database. This procedure is an alternative to what is currently recommended in the 7.3 Server documentation. Exercise caution when using this procedure; operational errors committed during a switchover can corrupt both your primary and standby databases.

• The initial configuration of the sites described here is:
  • Primary database = PROD
  • Standby database = STDB
• The final configuration of the sites described here is:
  • Primary database = STDB
  • Standby database = PROD
• PROD contains a control file, prodcontrol1.ctl, and the following datafiles:
  • proddata_01.dbf
  • proddata_02.dbf
  • proddata_03.dbf
• PROD contains the following online logfiles:
  • prodlog1.rdl
  • prodlog2.rdl
• STDB contains a control file, stdbprodcontrol1.ctl, and the following datafiles:
  • stdbproddata_01.dbf
  • stdbproddata_02.dbf
  • stdbproddata_03.dbf
• STDB refers to the following online logfiles in the control file, stdbprodcontrol1.ctl however, the files do not exist at STDB::
  • stdbprodlog1.rdl
  • stdbprodlog2.rdl
• The following initialization parameters are also set at STDB site:
  • db_file_standby_name_convert = ("/dbs/","/dbs/stdb")
  • log_file_standby_name_convert = ("/dbs/","/dbs/stdb")
• To Perform a Switchover i.e make STDB as your PROD, Shutdown the primary database "PROD".
  • Apply all archived redo logs to the standby database "STDB".
  • Issue the CANCEL command to halt recovery at standby site.
  • Shutdown the standby instance.
  • Obtain copies of the current control file and online logs from PROD for use at standyby site
  • if you are using filename conversion, issue the STARTUP MOUNT command at the standby site, ollowed by a series of ALTER DATABASE RENAME FILE... commands to manually rename all PROD files at STDB.
    for example alter database rename file ' proddata_01.dbf ' to 'stdbproddata_01.dbf ';
  • Issue the RECOVER DATABASE command.
  • Open your STDB database which is now actually your "PROD" database.
• To Make PROD Your New Standby site:
  • At STDB Create a new standby database control file.
    alter database create standby controlfile as 'prodcontrol1.ctl';
  • Archive the logs from your "new" primary database. alter database archive log current;
  • At PROD delete the control file and all logfiles
  • Copy the new standby control file ( prodcontrol1.ctl) from STDB to PROD.
  • Edit the initialization parameter file to reverse the file re-names.
    db_file_standby_name_convert = ("/dbs/s","/dbs/");
    log_file_standby_name_convert = ("/dbs/s","/dbs/");
  • Mount PROD in standby recovery mode and resume normal standby operations.
    alter database mount standby database;
    alter database recover standby database;