hot standby how-to,

[Ganimede-Dignan]

Hi,

I would like to test Oracle hot standby on a 10.2.0.4 or 10.2.0.5, it's a 300 gb database. Where can I find documentation, how-to, guides, ecc. ?

It's for an R/3 4.6C that will be upgraded to 6.04 asap.

Regards.

[Former Member · Accepted solution]

We use a hot stand-by whenever we upgrade/migrate our Prod R3 system.

It's fairly simple to set-up and invoke.

This is from my documentation:

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 u201C/ as sysdbau201D

b) alter database create standby controlfile as u2018cntrlXYZ.stdbyu2019;

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/tnsnames.ora

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, itu2019s 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:

DTS.WORLD=

(DESCRIPTION =

(SDU = 32768)

(ADDRESS_LIST =

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = IPC)

(KEY = DTS)

)

(ADDRESS =

(COMMUNITY = SAP.WORLD)

(PROTOCOL = TCP)

(HOST = <source-server-name>)

(PORT = 1527)

)

)

(CONNECT_DATA =

(SID = <SOURCE SID>)

(GLOBAL_NAME = <SOURCE SID>.WORLD)

)

)

b) $ORACLE_HOME /dbs/initZYX.sap

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 u201C/ as sysdbau201D

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 FTPu2019d 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 initXYZ.sap 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 u2013f -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 u2013c u2013u / -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 initXYZ.sap.

b) Create a script called recovery.ksh containing:

#!/bin/ksh

export SID=XYZ

export ORACLE_SID=$SID

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

checkfile=/oracle/$SID/saparch/.lock.bra

if [ ! -e $checkfile ]

then

/sapmnt/$SID/exe/brarchive -sd -d disk -m 1 -f -c force -u /

else

echo "`date` - brarchive currently running"

fi

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 u2013c u2013u / -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 u201C/ as sysdbau201D

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.

[markus_doehr2]

you can start with

http://www.oracle.com/pls/db102/homepage?remark=tahiti

Markus