Steps to restore SAP Oracle production DB backup to another SAP oracle DB using TSM for ERP

You might required to restore the current SAP oracle database to another SAP database according to the clients requirement. For example, it is required to restore the SAP production database to SAP development/quality database for testing any upgrade/modifications on the existing database before making it live. It is the TSM administrators job to restore the database within the given downtime. TSM administrator should also make sure that the offline backup of SAP production database is successful before going for restoration process. In this post we will see how to restore Production (PRD) SAP database to Quality (QTY) SAP database.

How to restore SAP Oracle database backup to another SAP system ?

Step 1: First, you need to plan for a offline backup of production SAP database and it should be successful. Make sure that the tapes used for this backup are secure and are inside the library slots for immediate mounting. 

Step 2: Make all the SAP file systems on QTY server like PRD server with  same size (or +1GB % utilize). If filesystems already exist then clean or empty them. Any SAP filesystems will be in the format of 

/oracle/SID/sapdata1

/oracle/SID/orilog1

/oracle/SID/sapreorg

/oracle/SID/saparch1

/oracle/SID/saptrace/usertrace

/oracle/SID/saptrace/background

/oracle/SID/sapcheck

/oracle/SID/sapbackup

Also Read: Steps to Install TSM for ERP (SAP with Oracle DB) - Video Tutorial

Here SID is the Oracle SID - ideally PRD for production & QTY for quality systems. You have to remove any existing data in these filesystems by using rm -rf command (command depends on OS).

Step 3: In this step you have to copy all the TDP SAP configuration files from Production system to the Quality system. The TDP SAP configuration files and their locations are 

• /oracle/PRD/102_64/dbs/initPRD.sap

• /oracle/PRD/102_64/dbs/initPRD.utl

You need to copy these files and paste them in the same location in the Quality System by changing their name as shown below

• /oracle/QTY/102_64/dbs/initQTY.sap

• /oracle/QTY/102_64/dbs/initQTY.utl

If files with the same name already exists, then rename the old files with another name before copying above files.

Step 4: In TDP SAP, after the database backup (online & offline) completes it generates the log file. For offline the log file extension is (.aff) and for online backup the log file extension is (.anf). Because we are restoring latest offline backup we need to copy the latest .aff file  from production system to quality system. The file location is shown below.

 Copy from /oracle/PRD/sapbackup/brnnfghrk.aff and paste it in /oracle/QTY/sapbackup filesystem.

Step 5: From the same location, we also have to copy the backPRD.log file to the same location in the quality system.

Step 6: Next, we need to change permissions and ownership (dba user) of all the files which are copied in the above steps.

chown oraqty:dba initQTY.sap initQTY.utl

chmod 777 initQTY.sap initQTY.utl

chown oraqty:dba brnnfghrk.aff backPRD.log 

chmod 777 brnnfghrk.aff backPRD.log

Step 7: In this step, we need to open initQTY.sap & initQTY.utl files and change the PRD to QTY. Make sure you change these below important parameters.

Also Read: Steps to configure SAP Oracle DB backup with TSM for ERP using BRTools

In initQTY.sap file

util_par_file=/oracle/QTY/102_64/dbs/initQTY.utl    (PRD  → QTY)

compress_dir = /oracle/QTY/sapreorg

In initQTY.utl file

CONFIG_FILE       /oracle/QTY/102_64/dbs/initQTY.bki    (PRD  → QTY)

Step 8: Next, we need to change the node in the /usr/tivoli/tsm/client/api/bin64/dsm.sys file. use the same node name which you have used for taking the production database backup.  You have to make sure that the SERVERNAME in all the files dsm.opt, initQTY.utl and dsm.sys are same.

cd /usr/tivoli/tsm/client/api/bin64/dsm.sys

vi dsm.sys

nodename PRDnode

Step 9: Next, we need to authenticate the TDP node with TSM server. This TDP node name is nothing but the production TDP node name, we have to authenticate PRD TDP node from QTY system.To do this you have to login using oracle user

su - oraqty

cd /oracle/QTY/102_64/dbs

./backint -p initQTY.utl  -f password

or 

su - oraqty

cd /oracle/QTY/102_64/dbs

./usr/tivoli/tsm/tdp_r3/ora64/backint -p initQTY.utl  -f password

Type the TDP node password and press enter. It should successfully authenticate the password for the production node through which you have taken the offline backup.

Note: backint utility is by default located in /usr/tivoli/tsm/tdp_r3/ora64 directory. You can either make a soft link to the /oracle/PRD/102_64/dbs directory or use the whole path name for authenticating TDP node as above.

Also Read: Tips to increase TDP for ERP backup and restore performance

Step 10: Finally, restoring the backup data, you have to login as oracle user and run the following command to restore whole database.

su - oraqty

cd /oracle/QTY/102_64/dbs

brrestore –m full –b brnnfghrk.aff –p initQTY.sap –c

Note: You can run brrestore utility from anywhere if the environment variable setting is enabled for this utility. Otherwise you need to create soft link from /usr/sap/QTY/sys/exe/run to the /oracle/QTY/102_64/dbs directory.

Commands to restore single database and individual archivelog files

1) For restoring only single file use the following command

brretore –m /oracle/QTY/sapdata7/btabd_250/btabd.data250 –b brnnfghrk.aff  –p initQTY.sap  –c

2) For restoring one database file use the following command

brretore –m /oracle/PRD/sapdata38/btabd_250/btabd.data250 –b  brnnfghrk.aff   –p initQTY.sap -c

3) For restoring archive files online use below steps

• Copy archPRD.log to QTY server  
• Change permission and owner as above
• su - oraQTY

3.1) Restoring one archive file

brrestore -u system/passw0rd -a 519407  (No. of archive file)

3.2) Restoring multiple archive files

 brrestore -u system/passw0rd -a '519408-519498'  (range of archive files)