Backup Of oracle db in SAP environment(all aspects)

BRTOOLS

BR= Backup & Recovery                       

a)      BRTOOLS-      To add/resize/db admin tasks

b)      BRBACKUP-   To perform SAP backup of data file/parameter files

c)      BRARCHIVE- To perform backup of offline archive log files

d)     BRRESTORE- Restore database

e)      BRRECOVER- Recover Database

f)       BRCONNECT- Perform daily DBA activities like check task/ indexes/ clean up

g)      BRPACE- Space management

BACKUP OF SAP DATABASE

                                                          BRTOOLS

BRTOOLS is the tool provided by SAP and is widely used from SAP 4.7E onwards which replaces the utilization sapdba tool used before . BRTOOL provides some of the extra features than sapdba and makes System Adminitrators easy to use.

BRTOOLS consists of following utilities.

BRBACKUP : Used for SAP backup

BRARCHIVE : Used for Archive log backup

BRRESTORE : Used for Restoring Data

BRRECOVER : Used for Recovering Database

BRSPACE : used to manage space in your database

BRCONNECT: All DB13 tasks are performed using BRCONNECT.

BRTOOLS: performs all the above BR* functions at command level.

Backup Strategy

1. What is the database size in my SAP systems (DEV, QAS, and PRD)

     Check the space using DB02.

2. Media?  

     Generally there are two medias frequently used in the real time.

            a.  TAPE drive

            b.  Hard disk drive  (HDD)

      Based on your comfortability and talking to your System admin you have to decide which one is better to use.

3. Level of backup

       In  real time we use two levels of backups generally

              a.  DISK-to -DISK

              b.  DISK-to-TAPE

       First level backup will be performed to local disk of server as it is faster and server will not be busy for long time

       2nd level is backup is performed  as per the available time and before the next backup starts.

4. Time of Backup

         Find out when is the server idle time or when server is less load. This time is more critical when your server is used by diff users from diff geographical region.

         So you have to find the time when there are very less users load to server so that server doesn't go into hand position.

5. Frequency  :   Hourly/Daily / Weekly/Monthly / Quarterly 

     Recommended:  DEV , QAS, PRD daily full online backup

    Recommended Weekly on DEV and QAS   ,  Monthly/Quarterly on Prodction (PRD

6. Type of backup

           a. Online  (Recommended:  DEV , QAS, PRD daily full online backup)

           b. Offline   ( Recommended Weekly on DEV and QAS   ,  Monthly/Quarterly on Prodction (PRD))

   

 7. Backup mode   (All, Full, Tablespace, Incremental etc.) 

           All :  This is higly recommended

8. Scheduler

       Scheduler is the software or utility provided by most of Operating systems. In order to perform backup automaticlly without running daily manually the   command ,we take the help of scheduler to perform the backup either daily /weekly/Monthly

   Unix:  There is scheduler available in Unix called   " Crontab"  .  In order to put backup into scheduler you have to write a script which will have your backup    command.

      This script generally an excuatable file called  .sh file

      and call this .sh file using crontab.

      Take the help of your Unix admin guy to setup this because to configure crontab u need   user  "root" access.

      NT:   There is a scheduler available in Windows  called "Scheduled Task"

      Similarly you put your command into an excutable file like .bat file or .exe file

      And call this executable file from Schdule Task

Backup Parameter files

1.   init<SID>.sap (ex. initS10.sap where S10 is the SID)

2.   init<SID>.dba 

Location of the parameter file

Unix:    $ORACLE_HOME/dbs

NT :   %ORACLE_HOME/database

How to check  ORACLE_HOME

Unix:   type  env   (  At command prompt  and look for the value of   ORACLE_HOME)

NT:  type  set    (at command prompt and look for the value of   ORACLE_HOME)

Unix:  ORACLE_HOME =  /oracle/<SID>/920_64/

Users responsible or authenticated to perform backup

1.   <SID>adm       (windows)

2.   ORA<SID>      (Unix)

Command used for backup of data

Unix:

brbackup –u / -c –q check –m all –t online –p $ORACLE_HOME/dbs/init<SID>.sap

brbackup –u / -c –m all –t online –p $ORACLE_HOME/dbs/init<SID>.sap

Here – u / indicates theat user is authenticated at OS level

Windows

 brbackup  -c –q check  –p %ORACLE_HOME%\database\init<SID>.sap

 brbackup -u /  -c –q check –p %ORACLE_HOME%\database\init<SID>.sap (in 640)

 brbackup  -c  –p %ORACLE_HOME%\database\init<SID>.sap

 brbackup  -u / -c  –p %ORACLE_HOME%\database\init<SID>.sap (in 640)

-c : indicates no operator confirmation required during backup

-t : type of backup e.g online or offline or some other type

-m : Backup mode All or Full or some other

-m and -t will not be required if you are using this option already in your backup parameter file.

for more brbackup option follow the below link.

http://help.sap.com/saphelp_erp2005/helpdata/en/79/5e0540a054e469e10000000a155106/frameset.htm

Backup Contents:

When we perform a backup using "BRBACKUP" command that means we are performing a databackup of SAP server.

The BRBACKUP backs up the following files during the backup

      a.  Datafiles   ( All datafiles of Tablespaces execpt the tablespaces  Temporary , ROLLBACK/SAPUNDO)

      b. Parameter files (init<SID>.sap, init<SID>.dba, init<SID>.ora , back<SID>.log , <file_name>. and or .afd files

      c. Control files

Backup Log: 

After running backup , you want to check the backup log whether it was successfully completed or not

a.  use tcode  DB12  at SAP level to check the backup log status

b.  at OS level open the file from  /oracle/<SID>/sapbackup/<filename>.and  and read the whole file or at the end of the file to see the backup log status

    There are two files updated at Os level in  /oracle/<SID>/sapbackup directory

        a.    back<SID>.log which shows  the only status of backup

        b.  *.and or *.afd file which gets created for each backup and contains the whole details about the backup

 DB12 reads    *.and or *.afd files from Os level when we check at SAP level.

Backup Return Codes

0000:  Backup Completed successfully

0001: Backup Completed successfully with Warnings

0003: Backup terminated  due to errors (check the log detail to find out detail of error)

0005:  Bakcup terminated due to errors  (check the log file detail to find out the detail of error)

9999:  Backup is currently running or in progress

BRARCHIVE

As we see in  brabackup , the whole datafiles , control files and parameter files are backed up to tape/disk

Similarly BRARCHIVE is used to perform the backup for offline archived logs  which are generated during database operation and gets stored in     /oracle/<SID>oraarch

During busy hours or high load on database , oracle creates lot of archive logs and stores in  ORAARCH .

If lot of archives gets generated during some time when we are not alert , then  /oracle/<SID>/oraarch folder gets filled and causes database to hang position

In order to avoid this hung situation, SAP recommeds to move these offile archived log files from  /oracle/<SID>/oraarch folder to  another location either locally or remotely  to save these offline redologfiles to backup into tape later when general backup runs for system.

Arhive backup should run every hour and has to put into a scheduler to run every hour

Use the following command to perform archive log backup using -sd option

brarchive –u / -c –q check -p $ORACLE_HOME/dbs/init<SID>.sap –sd

brarchive –u / -c –p $ORACLE_HOME/dbs/init<SID>.sap –sd

-sd  :  save and delete

When SAP moved the file from /oracle/<SID>/oraarch location to another location in disk using BRARCHIVE command, Brarchive makes sures that the file is saved in destination successfully and the performs the delete option.

What to do if Backup terminated in the middle or terminated manually?

If backup terminates due to some problem, the tablespaces which was in backup mode, doesn’t come back to end backup mode.

So when you rerun the backup or backup job is re-submitted it throws error stating that tablespaces are already in backup mode.

So to bring the tablespace from begin backup mode to end backup mode follow the following steps.

Before:

SQL> select* from v$backup where status= ‘ACTIVE’;

The above statement checks how many tablespaces are in backup mode and listed them out with STATUS = ACTIVE.

SQL> alter tablespace <tablespace_name> end backup;

After:

SQL> select* from v$backup where status=’ACTIVE’;

If nothing comes up, then all tablespaces are normal. If any tablespace name come up then issue the above command to bring that tablespace into end backup mode.