Oracle hot-stand-by how-to

Come attivare un sistema Oracle hot-stand-by? Ecco come fare.

1. Install a target SAP instance on serverTGT with same sid XYZ.  This can be done either by a fresh install, or by copying over SAP/Oracle executables.

2. Create a standby controlfile on serverSRC to be used on serverTGT:
a)    sqlplus “/ as sysdba”
b)    alter database create standby controlfile as ‘cntrlXYZ.stdby’;

Copy standby controlfile to serverTGT and rename as cntrlXYZ.dbf.  Ensure it is copied to all 3 locations.

3. Make Net8 configuration and Oracle Instance adjustments:
a)    $ORACLE_HOME/network/admin/


The standby instance needs to be able to communicate with the SRC so add a section with a new SID that points to serverSRC and the correct listener port ons erverTGT i.e. ABC.

The new SID can be anything you want it to be, it’s only used by Oracle net8 to communication with the SRC instance and is not used anywhere else.

For example, primary database is called DTS.  This is actually the Source database on source server:

(SDU = 32768)
(HOST = <source-server-name>)
(PORT = 1527)

b)    $ORACLE_HOME /dbs/
Add line primary_db = ABC (if ABC was the SID added to tnsnames.ora)
This will be used by brarchive to communicate with serverSRC when it applies logfiles to serverTGT.

4. Start up standby database:
a)    sqlplus “/ as sysdba”
b)    startup nomount;
c)     alter database mount standby database;

The standby database should now be in active standby mode.

5. The next step is to automatically get offline redolog files from serverSRC and apply to serverTGT.
This is done using brarchive on both serverSRC and serverTGT, with serverSRC sending files across an NFS mount (serverd from serverTGT).

Alternatively, they could be FTP’d automatically with brarchive.

On serverTGT:
a)    export /oracle/XYZ/saparch ensuring serverSRC is in access list.

On serverSRC:
a)    mount /oracle/XYZ/saparch from serverTGT onto a local mount point i.e. /usr/redolog/saparchXYZ.
b)     adjust as follows:

a.     archive_copy_dir = /usr/redolog/saparchXYZ (see step a)
b.     archive_stage_dir = /usr/redolog/saparchXYZ (see step a)

Test this as user oraxyz by typing: brarchive -s -d disk -w -c force -u /

This should start brarchive and will save and offline redolog files to /usr/redolog/saparchXYZ.  Once all files have been saved the brarchive command exits as normal.

Another similar command is: brarchive -s -d disk –f -w -c  force -u /

This command will perform the same as the command above but will then wait, after all files have been saved, for the next offline file and perform the same action.

The only way to abort this cleanly is to issue another command on another terminal session as follows: brarchive –c –u / -f stop

6. The final step is to ensure that the files are applied to the standby database, and removed from the file-system on serverTGT.  This is done as follows:
a)    Read oss note 216108 and make appropriate changes to
b)    Create a script called recovery.ksh containing:


export SID=XYZ
export ORACLE_HOME=/oracle/$SID/102_64
export SAPDATA_HOME=/oracle/$SID
export ORACLE_BASE=/oracle
export BR_RSH_CMD=true
export BR_RCP_CMD=true

if [ ! -e $checkfile ]
/sapmnt/$SID/exe/brarchive -sd -d disk -m 1 -f -c force -u /
echo “`date` – brarchive currently running”

Run this script.  It will apply redologs to the standby instance and will then delete any logs it has successfully applied from serverTGT.  This way, no manual cleanup is required.

After applying/deleting logs, the script will wait for more logs.

The brarchive command will fail if /oracle/<SID>/saparch contains any offline log files that are older than is necessary.

7. Activation is easy.
All this is done on serverTGT.
Ensure all available logs have been applied, or at least up to the point you need.
Then brarchive needs to be killed:  brarchive –c –u / -f stop
Ensure the recovery.ksh script is no longer going to run from crontab.

Check the directories required for PSAPTEMP datafiles exist.
Otherwise, the database creation will work but there will be a missing datafile and using the normal method of adding PSAPTEMP after a refresh will not work.

The simple solution, if this situation happens, is to create the directories and stop/restart database.

Alternatively, add another datafile to PSAPTEMP using the normal PSAPTEMP SQL statement, but giving a new datafile name.

Remove the reference to primary_db in $ORACLE_HOME/dbs/init<SID>.sap.
As ora<sid>, activate as follows:
a)    sqlplus “/ as sysdba”
b)    alter database activate standby database;
This will create controlfiles, online logfiles etc.
c)     alter database open;
d)    recreate PSAPTEMP if necessary

Once activated, start the oracle listener and check brconnect or sapdba can connect to the database.

[Ringrazio Stephen Kerr per questi preziosi appunti]

2 risposte a “Oracle hot-stand-by how-to

  1. Magnificent web site. Lots of helpful information here.
    I’m sending it to several friends ans also sharing in delicious. And naturally, thank you for your sweat!


Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:


Stai commentando usando il tuo account Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...