A high-level guide to creating a manual standby Oracle Database

istock_000068972263_large.jpg

This blog post lists the high-level steps to create a manual single instance standby database from a RAC primary database using a backup-based replica. It is possible to also perform an active duplication directly from the primary server, however, that can take a very long time especially with VLDBs.

Install Oracle Software on the Standby Server

  • Install Oracle software (GI and RDBMS) and patch to the same version/level as Production.
  • You may choose to utilize a FS, but this document is for a server with the same directory structure as Primary which required Grid Infrastructure and the use of ASM as the filesystem.
  • Create a database using DBCA – don’t worry that the database name/DBID don’t match production, that will be corrected by the duplicate command.
  • If utilizing a SAN/NAS, confirm that the System Admin mounted the shared located correctly with Read-write permissions.

Prepare the Primary database

  • Take a full backup (plus archivelogs) of the primary database and save to the shared location.
  • BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG format '/PATH/TO/SHARED/DRIVE/manualDR_%s';
  • Take backup of current controlfile
  • Backup current controlfile format '/PATH/TO/SHARED/DRIVE/controlDR.ctl';
  • Create a TNS entry for the standby database
  • Edit tnsnames.ora file
    • The listener in a RAC environment is usually started /owned by the Grid user

    • Add the connection data for the standby server

EPISODE_II =
(DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL=TCP) (HOST=ATTACKOFTHECLONES) (PORT=1521))
)
 (CONNECT_DATA =
 (ORACLE_SID =STARWARS)
)
 )
  • Add the connection data for the Primary
EPISODE_I =
 (DESCRIPTION =
(ADDRESS = (PROTOCOL=TCP) (HOST=THEPHANTOMMENACE-SCAN) (PORT=1521)
(CONNECT_DATA =
(SERVER = DEDICATED)
 (SERVICE_NAME = SKYWALKER)
 (UR = A)
 )
  • Create password file for DR        
    • orapwd file=$ORACLE_HOME/dbs/orapwEIIstarwars password=temp123 entries=3;
  • Create pfile for DR database:
  • Sqlplus / as sysdba
    • SQL> CREATE PFILE FROM SPFILE;
      • The pfile would be saved in the dbs directory of the Oracle home
      • cd $ORACLE_HOME/dbs
      • cat initSTARWARS.ora
  • Create a backup copy of the pfile to be moved to the standby
    •  cp initSTARWARS.ora initSTARWARS.ora.bak
  • Test the connection to the standby database
    • tnsping EPISODE_II
      • It should result in OK
      • If not, troubleshoot and make sure there are no firewall issues, errors in the tnsnames files, etc.

Prepare the Standby Server:

  • Edit the listener.ora file – will be owned by the Grid User
  • Add the connection info for the standby database
LISTENER =
 (DESCRIPTION_LIST =
(DESCRIPTION =
 (ADDRESS = (PROTOCOL=TCP) (HOST=THECLONEWARS) (PORT=1521)
 (ADDRESS = (PROTOCOL=IPC) (KEY=EXTPROC1521)
)
 )
SID_LIST_LISTENER =
(SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STARTWARS)
 (ORACLE-HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STARWARS)
)
 (SID_DESC =
 (SID_NAME = STARWARS02)
 (ORACLE-HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 )
 )
  • Add the connection info for the Primary server
EPISODE_I=
 (DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=THEPHANTOMMENACE-SCAN)(PORT=1521)
(CONNECT_DATA=
(SERVER = DEDICATED)
 (SERVICE_NAME= SKYWALKER)
 )
  • Test connection to RAC (Primary) database:
    • tnsping EPISODE_I --make sure result is OK
  • Change service name of database so it’s not the same as primary
    •  alter system set service_names = 'Jedi' scope=both;
  • Change parameter db_recovery_file­_dest to the shared drive
    • alter system set db_recovery_file_dest= '/PATH/TO/SHARED/DRIVE' scope=both;
  • Start NETCA
    • Reconfigure the network name for Primary & DR databases and make sure test is successful.
  • Reload listener to pick up changes
    • lsnrctl reload
    • lsnrctl status – should see the service for RAC database and DR database
  • Shut down the standby database.
    • Use the pfile created on the primary server to start the standby database in NOMOUNT MODE;
    • CREATE SPFILE FROM PFILE =’/PATH/TO/SHARED/DRIVE/initSTARWARS.ora’;
    • STARTUP NOMOUNT;
  • Connect to RMAN on Primary and start the duplication:
    • rman target /
      • RESTORE CONTROLFILE FROM '/PATH/TO/SHARED/DRIVE/controlDR.ctl';
      • Restore database;
  • After the restore is done, recover the database using the archivelogs being saved in the shared location:
    • Recover database;
  • To keep the standby consistent, you can automate the recovery to run every 30 minutes on the standby database. The standby database will always be ~45minutes behind the primary since this is not a dataguard environment.
  • The standby database will always remain in MOUNT mode, if the database is opened (using RESETLOGS), you will have to recreate the environment.
  • For validation purposes, you can open the standby database in READ ONLY mode to avoid resetting the logs and creating a new incarnation of the database.

Note: An alternative method would be to use a backup based duplication

  • There would be no need to manually backup and restore the controlfile.
  • You would edit the DB_NAME in the pfile you created in the primary to whatever you want the standby database to be called (assuming you want it to have a different name), if you want it to have the same name as primary, don’t make any changes to the pfile.
  • The standby database could have a different DB_UNIQUE_NAME (the name given upon creation) and then the duplicate command would just overwrite the DB_NAME to whatever your primary database is called.
  • Make sure the standby is in NOMOUNT mode using the pfile you created in the primary
  • You would connect to the target(Primary) database and the auxiliary (standby database):
    • rman target sys/temp1234@EPISODE_I auxiliary /
RUN {
DUPLICATE DATABASE
TO STARWARS
BACKUP LOCATION '/PATH/TO/SHARED/DRIVE/DIRECTORY'
NOFILENAMECHECK;
}
  • Once the duplication is done, recover the database using the archivelogs and set up a cron job to run the recovery automatically every half-hour.
  • The duplicate command will do the following automatically:
  • Restore the controlfile from backup and open the standby database in MOUNT mode.
  • Restore all datafiles and perform incomplete recovery.
  • Recreate a new controlfile allowing to change the name of the standby database if you wish.
  • To open the database, you would need to use the RESETLOGS option.

TIPS:

  • A simple validation of the lag between the primary and standby would be to create a "heartbeat" table on the primary database. Automate the timestamps being added to the table every minute.
    • To validate the actual lag between primary and standby, open the standby database in read only mode and select from the heartbeat table to verify the last timestamp recorded. The difference between the last recorded time on the table in the standby database vs the current time would be the lag and potential data loss window in case of failure on the primary database.
    • Keep in mind that the heartbeat table will grow very large quickly, so it is imperative you automate the clean-up of that table. e.g. schedule a job that truncates the table and resets the sequence on the heartbeat table once a week in order to control the size of the table.
*Images from maxta.com and cloudtweaks.com
OraclePatricia AnongDisaster Recovery, Standby Database, Oracle Database, Recovery, Backups, HowToComment