Oracle long running backup troubleshoot

Symptomes :-

• Backup is running longer than expected and not canceling as well .
• $SAPBACKUP%\.lock.brb no lock file at the location.( if there remove the lock.brb file and Run the backup)
• Other backup also failed due to last backup running

When we will check failed backup log below error will show

Action need to be taken:-

You can solve this problem in the following ways:

1. login to the System at OS level
2. Go to Start
3. Go to Run
4. Type cmd and enter

Start SQLPLUS:

sqlplus “/as sysdba“
SQL> select status from v$backup;
STATUS
——————
ACTIVE
ACTIVE
ACTIVE
ACTIVE
ACTIVE
No of rows selected.

Note:- STATUS=ACTIVE means All the tables spaces are on backup status

If all active put the below command on SQL;

SQL> alter database end backup;
Database altered.

SQL> select status from v$backup;
STATUS
——————
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
NOT ACTIVE
N No of rows selected.

SQL>
Note :- STATUS= NOT ACTIVE :- Means now tables paces are not in backup status
Run the backup again and monitor.

Steps to configure SAP Oracle Database backup using TSM for ERP (TDP SAP) and brtools backint utilitties

To take SAP database backup using IBM TSM for ERP SAP, you can use the backup and recovery tools which are provided with SAP. Backup and recovery tools are either an integrated component of a relational database system, such as Oracle Recovery Manager (RMAN) or may be installed as an independent software package developed by a third party such as SAP BR*Tools for Oracle. In this post we will see how to configure TDP for SAP for taking SAP database backup using BR*TOOLS

What are SAP BR*Tools ?

The SAP BRTools for Oracle is a package of utilities developed by SAP AG to protect and manage SAP data stored in Oracle databases. BR*Tools supports functions for online or offline, partial or full backups of database (BRBACKUP) and backup of archived redo logs (BRARCHIVE). It provides functions for database restore and recovery (BRRECOVER and BRRESTORE). BR*Tools can be used not only for database recoverability tasks, but it can also serve as a tool for creating homogenous database copies and can assist with database migration to different platforms or database versions. BR*Tools backup and restore functions can be accessed either from the user interface (BRGUI or BRTOOLS)  or directly from the command line or script by executing the commands BRBACKUP, BRARCHIVE, BRRESTORE, and BRRECOVER. The BR*Tools executables are located in the

/sap/<SID>/SYS/exe/run directory.

BRBACKUP

BRBACKUP is the BR*Tools utility that enables online or offline backup of database files such as data files, control files,online redo log files and also BR*Tools configuration profiles and logs that are required for the database disaster recovery. Before taking the offline backup, BRBACKUP automatically closes the database and opens it as soon as the backup is accomplished.

BRARCHIVE

BRARCHIVE provides functions for offline redo log files backup in Oracle databases running in archiving mode. The redo log is the most important database component for a recovery from a crash, media failure, or user failure. Therefore, at least the production databases should be configured in archiving mode. To prevent the archivelog directory from filling up, BRARCHIVE should be executed periodically to move the offline redo logs from the the archive directory to the backup media. The most common use of BRARCHIVE is to back up the offline redo log files to backup storage and delete the backed up log files from the archivelog directory.

In addition to data files and redo logs, BRBACKUP and BRARCHIVE can also back up the following objects

• BR*Tools profiles init<SID>.ora and init<SID>.dba

• The BRBACKUP and BRARCHIVE logs

• The summary BRSPACE log space<SID>.log, the log of database structure changes struc<SID>.log, and the log of database parameter changes param<SID>.log.

BRRESTORE

BRRESTORE is used to restore the whole database or parts of it (data files, control files, and archive redo log files). Before BRRESTORE starts placing the data files back into the file system, it determines whether there is the appropriate free space on the file system. In addition to restoring data files, BRRESTORE can also recovers the directories and links.

BRRECOVER

BRRECOVER  is a BR*Tools component used for database restore and recovery after media errors, user errors, or disasters occur and the entire database has to be restored, including backup profiles and logs. It can also be used  to recover the entire database or part of the database. To restore data files, BRRECOVER calls the utility BRRESTORE.

Use the restore and recovery functions for Complete database recovery, Database point-in-time recovery, Tablespace point-in-time recovery, Whole database reset and complete Disaster recovery. BR*Tools backup repository is backed up together with a database when using the BRBACKUP tool. The log files are located in the following directories

• The BR*Tools logs associated with data file backup functions in  <SAPDATA_HOME>\sapbackup directory.

• The BR*Tools logs associated with redo log backup tasks in <SAPDATA_HOME>\saparch directory.

All these SAP BRTools utilities uses the BACKINT interface to take the SAP database backup and restore functions. You can only take Full SAP database backup through BACKINT interface. You can start a full backup either in online mode or in offline mode. In case of the online type, the offline redo log files generated during the full backup are also backed up to the same media.

BRTOOLS uses below process to take the SAP database backup

• BR*Tools utility BRBACKUP informs Oracle what data has to be backed up and puts the database into the proper backup state, online or offline.

• BRBACKUP calls Tivoli Data Protection for SAP using the BACKINT interface with a list of all files to be backed up.

• Data Protection for SAP reads all the requested files from the database and reports back to BRBACKUP. BRBACKUP adds these files to the repository containing all processed backups.

• BACKINT saves the data to Tivoli Storage Manager.

• BR*Tools updates the file repository containing information about the status of the files.

The BR*Tools configuration is stored in the initialization profile file init<SID>.sap. The configuration file contains parameters that affect the performance of backup and restore functions. The default location of the file is

In UNIX                 <ORACLE_HOME>/dbs

In Windows          <ORACLE_HOME>\database 

How does the old SAP database & redologs backups get expired or deleted ?

• The old SAP database and archived redologs backups can be expired in two ways. You have to choose which method to use for your environment before going to configuration steps. The backup retention policy can configured either on the Tivoli Storage Manager server side by the archive copy group parameter RETVER  or on the side of Tivoli Storage Manager for ERP by specifying the parameter MAX_VERSIONS in the configuration profile (init<SID>.utl).

• The parameter MAX_VERSIONS defines the maximum number of database backup versions to be kept in backup storage. The default setting for this value is 0, meaning that backup version control is disabled.

• Every time a full backup completes successfully, the version count is incremented by 1 and stored in the TDP for SAP configuration file. This value is also assigned to the tablespace files and to all subsequent redo log backups. If the number of versions kept in backup storage is larger than the specified maximum number of backup versions (stored in the parameter MAX_VERSIONS), the oldest versions are deleted (together with the corresponding tablespace and redo log files) until only the specified maximum number of most recent versions remain.

• A Tivoli Storage Manager server uses the value of the parameter RETVER specified when defining an archive copy group to give files an expiration date. If you use Data Protection for SAP backup version control, you need to bypass this expiration function. If you use the Tivoli Storage Manager expiration function, you need to turn off Data Protection for SAP backup version control. Use only one of these methods to control how long you keep backups.

• If you use Data Protection for SAP backup version control, set the Tivoli Storage Manager parameter RETVER to RETVER=9999 so that the files are not considered expired and are not deleted by Tivoli Storage Manager. If you use Tivoli Storage Manager expiration, deactivate DP for SAP versioning by setting MAX_VERSIONS=0.

Steps to configure IBM TDP for SAP for taking SAP database backup using BR*Tools

You can perform the following steps to configure BR*Tools to use the Tivoli Storage Manager BACKINT adapter with Tivoli Storage Manager for ERP

Steps to be performed on the Tivoli Storage Manager server

• Define a policy domain with two management classes that will be used to transfer data and logs.

• Define an archive copy group for each of the management classes. If the retention control will be performed at the Tivoli Storage Manager server, specify RETVER=<days> for the number of days for each archive copy group.

• If retention control will be performed at the Tivoli Storage Manager for ERP level, specify RETVER=nolimit.

• Register the Tivoli Storage Manager node with the defined domain.

• Update the parameter MAXNUMMP for the Tivoli Storage Manager node to MAXNUMMP=<value>  (based on the number of drives available).

 Steps to be performed on the client node

• Update or create dsm.sys file for the Tivoli Storage Manager API client. The parameterPASSWORDACCESS=PROMPT must be set for this configuration.

• Configure the environment values DSMI_DIR and DSMI_LOG for the Oracle user. the Tivoli Storage Manager API client looks for the dsm.sys configuration file in the directory specified by DSMI_DIR.

• Install Tivoli Storage Manager for ERP – Oracle on the Oracle server, with SAP already installed. The installation wizard lets you specify the name of Tivoli Storage Manager server stanza in dsm.sys file, the Tivoli Storage Manager node name, and the management classes to be used for backup of data and archived logs.

• Configure the client resources for Oracle server in the Tivoli Storage Manager for ERP configuration file<ORACLE_HOME>\dbs\init<SID>.utl.

• In the initSID.utl file, check the defined Tivoli Storage Manager node name and Tivoli Storage Manager management classes to be used for the backup of offline redo log files and data files. Make sure that the SERVERparameter refers to an existing stanza in the dsm.sys file.  See the below example images, first image is parameters in initSID.utl file and second image is dsm.sys file parameters.

• Remember, if you specify ADSNODENAME in initSID.utl file then you must not be specify again in dsm,sys file.

• If the retention control will be driven by Tivoli Storage Manager for ERP, set up the MAX_VERSIONS parameter.

• Switch the user to the Oracle instance owner and update the password of the Tivoli Storage Manager node using the following command

            backint -p <full_path>\init<SID>.utl -f password

• Instruct BR*Tools to use the BACKINT interface by setting the backup_dev_type parameter in the SAP initialization file (init<SID>.sap) as follows

             backup_dev_type = util_file

• Instruct BR*Tools to use the file init<SID>.utl by setting the util_par_file parameter in the SAP initialization file:

              util_par_file=<full_path>/init<SID>.utl

• You should see a prole process running in the background after all these configuration. The purpose of this entry is to start a daemon process for ProLE. This process listens on the Data Protection for SAP for Oracle port tdpr3ora64 for backint and RMAN connections and sends performance-related information to the Administration Assistant Server component.

• Now Finally after all these configuration steps, you can either manually call BRTOOLS manually or schedule it through DB13 from SAP to start taking the backup. To call BRTOOLS manually, log in as oracle user and runbrtools command as shown below.

• Use, options 4 & 5 and follow the steps after to take backup or restore of SAP Database and archive redo-logs using SAP BRTOOLS utility.

 

Tivoli Storage Manager Commands (TSM)

q sess   To see currently working sessions

can sess  ( sess no)  To cancel a session

q eve domainname schedulename    To see schedule time,nodename, scgedule name and progress of the scheeduled backup. ( q eve * * to see all scheduled backups)

q proc     To see currently running background processes

cancel process (process no)    To cancel a process

q node   To view assigned nodes in a Tivoli server

q filespace   To check backup space utilized for each file system of a server, filesystem type…
q actlog  To view activity log

q act begind=mm/dd/yyyy begint=hh:mm:ss  endd= mm/dd/yyyy endt=hh:mm:ss     To view activity log for a specified period by begining date and end date

q act begind=today-1 begint=00:00   To view activity log from yesterday 12:00 am

q act search=transferred begint=00:00   To see all completed backup size of servers from 12:00 am

q act search=nodename     To view activity log for a node

q drive f=d   To get detailed report of drive, it’s status …

q library f=d    To get detailed information about tape library

q libvol    To see volumes in the tape library and it’s status

q vol   To see volumes and it’s storage pool name, capacity, status 

q req     To see requests 

reply (request no)  To continue request

q sched        To show schedule name domain, action , start date & time

q sched f=d To show detailed report about schedules including schedule backup path

UPDATE LIBVOLUME TS3200 L41117L4 STATUS=SCRATCH To change a library volume  ( L41117L4  ) status to scratch from private

update node nodename “admin” domain=domainname forcepwreset=Yes       To change a tivoli login password of a node

define vol storagepoolname L41116L4       Add new tape L41116L4 to a storagepool 

delete volume L41116L4 discarddata=yes       To delete data inside a tape media L41116L4

To create a new schedule backup (incremental ) for a node

UPDATE SCHEDULE domainname nodename desc=”daily incremental backup for a server” action=Incremental options=-subdir=yes objects=””/fs1/*” “/fs2/*” “/home/user1/*”” priority=5startdate=mm/dd/yyyy starttime=hh:mm duration=1 duru=Houperiod=1 perunits=Day dayofweek=Any expiration=never
To start tivoli services in an AIX client

#cd /usr/tivoli/tsm/client/ba/bin

nohup dsmc sched &     To start scheduled backup service

nohup dsmcad &   To start BA client ( webmode backup i.e. http://ip.address.of.server>:1581)

#cd /usr/tivoli/tsm/StorageAgent/bin

nohup ./dsmsta & To start storage agent ( LANFREE BACKUP= backup directly from SAN to Tape library )

#ps -ef | grep dsm    To view currently running Tivoli services in server

To update drive status of a server to online

update path sourceservername TAPE0 srct=server destt=drive libr=ts3200 online=yes
To set serial number of a drive in tape library

UPDATE DRIVE TS3200 TAPE0 ELEMENT=autodetect ONLINE=Yes WWN=”2002000E11107B6C” SERIAL=autodetect

To detect label of a tape in tape library

label libvol t3200 checkin=scratch labelsource=barcode search=bulk waittime=0

To remove tape media from tape library 

checkout libvol ts3200 VOLLIST=A00015L3,A00019L3 CHECKLABEL=YES REMOVE=YES

To add and label new tapes into library
LABEL libvol TS3200 search=yes labelsource=barcode overwrite=NO checkin=SCRATCH WAITTIME=0

To take full backup of Tivoli database manually to a file in local disk
backup db dev=FILECLASS type=full

to assign a tape drive to a server
=======================

UPDATE PATH servername TAPE0 SRCTYPE=SERVER DESTTYPE=DRIVE LIBRARY=TS3200 DEVICE=\\.\Tape0 ONLINE=YES AUTODETECT=no

Important
If you are not able to take backup through any of Tivoli backup methods ( BA client, dsmc , schedule ) then check any NFS mounted filesystems are there in the server and that server is available or not. If that server is not powered on, either unmount the NFS filesyatem or make that server available…