backup.sh

A Backup Script for Oracle on Unix with NetApp Storage.

The backup.sh script has gone through many changes over the years and still contains remnants of RMAN, various Tivoli Storage Manager (TSM) clients, direct to disk backups and support for clones for tape among other items.  Today the backup.sh is literally a massive amount of checks, reports, and maintenance task that are wrapped around a hand full of NetApp command for creating and dropping snapshots, and updating snapmirrors.

Download: backup.sh

Features

• Cold or Hot backups and snapmirrors without archive log mode enabled
• Backup the supporting files including: initialization parameters, control files, archive logs, crontab entries, mount command, tnsname.ora entries for DB links, oratab entry, password file and any utility scripts associated with the database.
• Run a snapshot or mirror backup on a database while a backup to tape is still running.
• Disable all backups for a database, host, or the entire system with one flag file.
• Backup the database to a specific TSM management class with the desired retention period.
• Limit I/O load for rights to tape for the entire box, not just a single database backup.
• Optionally leave the database down after a cold backup.
• Optionally remove archive logs after a successful backup.
• Optionally backup offline tablespaces or skip them.
• Automatically excludes temporary files from tape backups.
• Define the retention time in days for the NetApp snapshot if one is used.
• Mount a snapshot on a remote system run the copy to TSM there in order to shift I/O load.
• Backup the database to disk space.
• Backup to a specific TSM archive management class.
• Backup to TSM without a NetApp snapshot.
• Set flags which will trigger application services to stop and start, before and after a backup.
• Configure parallel database backups, so that a backup of A triggers a backup of B and visa versa.
• Trigger statistics connections and index rebuild runs immediately following a backup.
• Verify the backups exists on the TSM tape system.
• NetApp Volume Snapshot Report details the source are target for all clones on all filers.
• Drop NetApp snapshots that have exceeded their retention period.
• Trigger a clone to another database immediately following a backup.
   

Usage

The usage output for the backup.sh script is overwhelming at first but much of the functional details can be ignored until possibly needed, and the important items will be discussed on below.

Hot Backups Without Archive Logs

The NetApp documentation states: "In order for Snapshot copies to be effectively used with Oracle Databases, they must be coordinated with the Oracle hot backup facility."  But after thousands of successful clones of  production databases from hot snapshots, without a single failure; I am convinced that any possible risk is acceptably small.
 

Backup Oracles Supporting Files

Supporting files include everything that will or even might be needed to reconstruct the database on a new host. In theory this information is also on tape due to TSM scheduled sweeps of the host, but these files take very little space so it is much more convenient to have them readily available. Some of the files are also just snips that pertain to a specific database.  The reason for parsing the file, such as crontab entries, is so the process of moving a database and all of it's supporting configuration can be automated.  For example we have a script named "dr.sh" originally written of a Disaster Recovery plan, that can mount, configure and start any database and any of it's related host side applications with one command: "dr.sh move {SID} to here"

The control, initialization, and password files will all be copied to tape when the database is being copied to tape.  This makes sure that we have a control file that is accurate to each tape backup.  For snapshot backup, the control file is rewritten in to a script associated to the snapshot by name.  Together the script and the snapshot are all that is needed to clone the database.

The primary destination for all of the supporting files is directory with the same name as the database.  These directories all reside on a single NetApp volume which is NFS mounted on all of the Oracle database host.  This volume scheduled to be copied to tape daily by TSM, but it is also mirrored to the companies off site NetApp storage using NetApp snapmirror.

The shared storage volume also has a directory for each Oracle database host. And these directories contain the host configuration files and information that would be necessary for recreating the Oracle environment. Below are a list of the files created for the database and a second list of the files gathered for the host. 

The process of collecting the supporting files is done by the backup_config.sh script which is called by the backup.sh script.  It is separate because it is also scheduled to routinely run from crontab.  This insures that the supporting files are current for any parameter changes or data files added, even if a backup has not been run.

Download:  backup_config.sh

Below are a list of the files created for the database and a second list of the files gathered for the host. 

Sending Tape Backups to a Specific TSM Management Class

The TSM management class determines how long the files will be maintained on tape, but unfortunately you can not pass the management class as an parameter to TSM's dsmc program which copies the file.  It tape storage was cheap we might just keep everything for 120 days and call it good.  Instead we want to store each database as long as the business unit think reasonable.  So we offer the business units the choices of 3, 14, 21, and 120 days.   If they want to keep something longer we have archive management classes configured for 3 and 10 years; archive_data_3y and archive_data_10y.  Ironically, you can pass archive management classes to the dsmc program. To work around the non-archive management class issue we use multiple dsm configuration directories: backup_dsm_03,  backup_dsm_14, backup_dsm_21, and backup_dsm_120. Each contains it's own copy of the  and each with it's own inclexcl.def file which configures the included files and the management class.  For example the inclexcl.def file in the backup_dsm_14 directory contains the line: "include * ORAAIX14d", where ORAAIX14d is the management class configured to retain file copies for 14 days.  To switch the from one dsm directory to another the backup.sh sets the DSM_DIR environment variable.  For example if the backup command is: "backup.sh cold PROD -tape -14dy", DSM_DIR will be set to point to the backup_dsm_14 directory.
 

Disabling Backups

If you are going to be doing a reorganization on a database you don't want the database to snapshots in the middle of the process so that you quickly use up all of the snapshot reserve and needlessly extend the volume or use up the available file space and crash the database.  Nor do you want the database to shutdown for it's nightly cold backup hours into your import.  

Or when the scheduled system outage time rolls around and every host is going to be shutdown or the Unix Admin is going to upgrade you host and restart it several times, it is great to be able to disable backups accordingly. 

The backup.sh script allows you to disable backups for a specific database, a specific host, or for every Oracle host by simply setting a flag file.  For example to disable backups during a full system outage the flag file is created on the shared storage volume mounted on every Oracle host and utilized by the backup_configs.sh script. For such an outage, setting and removing the flag file

     at 8:00 PM Friday
     touch /oracle_share/backup_configs/cancel_ALL_backups.flag
     mail -s "cancel_ALL_backups.flag set" djackson@oneok.com </dev/null
     job oracle.1298080800.a at Fri Feb 18 20:00:00 2011

     at 6:00 AM Saturday
     rm -f /oracle_share/backup_configs/cancel_ALL_backups.flag
     mail -s "cancel_ALL_backups.flag removed" djackson@oneok.com </dev/null
     job oracle.1298116800.a at Sat Feb 19 06:00:00 2011

To disable backups for a host the default flag file is: FLAG_DIR/cancel_ALL_backups.flag.  Backups for a specific host are canceled with FLAG_DIR/cancel_<SID>_backups.flag  or by a flag specified by MAINT_FLAG_DIR/maintenance_<SID>.flag. The maintenance flag can be set and removed using the "m" alias with is defined by running the up.sh script. The up.sh script runs as part of the oracle user's .profile and it will list all of the databases on the host as well as which databases are in maintenance mode. Maintenance mode will also prevent a host shutdown.
 

NetApp Snapshot Retention and Removal

The backup.sh can make backups with or without a snapshot, but as stated earlier using a snapshot allows for the use of a hot backup even when the database is not in archive log mode.  Using a snapshot in conjunction with a copy to tape also gives you two methods for restoring the database.  If the database's volume is intact and the snapshot still remains then the restore can be done in less than a couple of minutes no matter how larger the database.  However snapshots can often only be retained for a few days without have more than 20% of the volume space allocated to the snapshot reserve where new and modified data blocks are stored.  It all depends on the database, but generally the larger the database the more likely you can keep snapshots longer and smaller transactional database with no historical data need larger snapshot reserves in order to keep snapshots longer.

For example, this is a typical backup.sh call: "backup.sh hot TIPS -snap=7 -tape -21dy".   This will take a snapshot of the database and mark it to be retained for 3 days.  Once the snapshot is created it will copy all data, redo and undo files to the TSM tape system into a management class that will retain them for 21 days.  The name of the snapshot's name is the volume name, which by company standard is also the database name, followed by the date and time in the format "_YYMMDD_HH24" followed by "_R" and the number of days for which the snapshot is to be retained.  For example: "tips_110221_1203_R7" is a snapshot of the TIPS database on the "tips" volume, taken Feb 21, 2011 at 12:03p and it will be retained for 7 days.

Removal of expired snapshots is also done by the backup.sh script.  For example, "backup.sh  -snap_purge -filer=N05" will delete any snapshot from the N05 NetApp filer that has been retained longer that it's encoded retention days provided that the snapshot is not busy supporting a cloned database.  The backup.sh script actually runs the "NetApp Volume Snapshot Report" report first and uses that to determine if each expired snapshot is "busy" before it attempts to drop it.  The report is run again after expired snapshots are dropped so that the report file left on the host is accurate.  That reports name is "backup_netapp_vol_snapshot_report_n05.txt" if the filer is "N05". 

You can update the "NetApp Volume Snapshot Report" report at anytime with: "backup.sh -snap_rpt -filer=N05"  adding "-v" for verbose will email a copy to the members of the backup email list specified in the backup.sh script's configuration section.
 

NetApp Volume Snapshot Report

The "NetApp Volume Snapshot Report" on occasion will be very helpful because there is no will NetApp will report that a specific volume snapshot is being used for a clone, it will not tell you what the clone volume name is that is using that is using that snapshot.  We sometimes have eight supporting volumes for a production database.  When snapshot reserve space is low you will need to choose between adding space or splitting a clone so you can drop and old snapshot.  Without the "NetApp Volume Snapshot Report" report you have to check the volume status of each possible target. If the report is current then you only need consult the report.

For example, a list of the snapshots for the "tips" volume shows four snapshots currently being used as clones for supporting database, of which there are are six:

The oldest snapshot and by nature, the one using the most snapshot reserve space is: "tips_101123_0220_R7".  To find the database volume's that are using this snapshot we can grep the latest NetApp Volume Snapshot Report" for the same filer.  To make it easier to read, it's combined with the reports column headers:

The results show that the snapshot is only being used by the "ttips" database volume. It's only one volume that is not frequently cloned so initiating a split would be the favorable choice.  Once the split is complete the tips_101123_0220_R7 snapshot will no longer be busy which means it will be dropped during the next time: "backup.sh  -snap_purge -filer=N05" is run.

It is possible and routinely done that a "non-split" clone has snapshots that are themselves being used for a clone. Note that the "ttips" clone of "tips" also has a snapshot but the report indicates that the "ttips" snapshot is not being used for a clone. While this will not be effected by the split, it would prevent you from destroying the "ttips" volume in order to refresh the clone from "tips".  In that case the above example would again show you the volume that needed to be split.

Concurrent Copies to TSM

Using the "resourceutilization" setting, TSM allows you to control the number of concurrent sessions used to copy files to the TSM storage. But it is limited to a specific set of files.  If you backup multiple databases and hence multiple sets of files at the same time, then each set would open multiple sessions and the impact on the host could be severe.  The backup.sh script allows you to control the total number of sessions for both the each set of backup files as as well as all backups running on the host.  The following parameter is used:

         -c=<Concurrent_Tape_Sessions>/<Total_Tape_Sessions_For_The_Host>

For example when the concurrent parameter "-c15/30" is passed then 15 data files will be sent to TSM at the same time as long as no more that 30 sessions are running on the host.  If 30 sessions are already in progress when the backup script is started then it will start only 1 session and continue to monitor the total number of sessions on the host until the number drops below 30 and then it will start additional sessions as long as the total number of sessions for the host is less than 30.  The default value for the concurrent settings is 10/20.  Once the last data file for a set is has been launched into the background; the script will wait for all of the spawned sessions to complete before moving on.  A file set for a cold backup can include all of the database data files, but for a hot backup it will only include the data files for the tablespace currently in backup mode. The concurrent backup feature only works with Hot and Cold backups to the normal TSM management classes.  It does not support backup to disk or an archive management class.

Remote TSM Backup

Download: backup_remote.sh

A snapshot backup has no performance impact.  But copying it to tape does utilized cpu and lots of bandwidth between the database host and the TSM host.  This can be avoided performing the copy to TSM from another host.  This is especially a good strategy when a backup to tape must run during business hours.

The remote backup to TSM is done by a companion script named "backup_remote.sh" the is scheduled to run periodically on the remote host.  To initiate a remote backup the "-rtape" parameter replaces the normal "-tape" parameter to the backup.sh script. For example: "backup.sh hot TIPS -snap=7 -rtape -21dy".   This will make a hot snapshot backup of the database and set a flag file in the REMOTE_TAPE_DIR as configured in the backup.sh script.  This location is on a volume which is mounted on both the database host as well as the host that runs the backup_remote.sh script.

The backup_remote.sh script will check for the existence of flag files, working from the oldest to the newest.  For each flag file it will mount the database volume using NFS, and then copy the files in the specific snapshot to TSM using a management class that was specified as a parameter to the backup.sh script.

The status of the copy to TSM is updated in the flag file, and any subsequent runs of the backup.sh script will check the status and update the Master log file on the database host as well as the bkup.bkup_log table.

TSM management classes are defined in the backup_remote.sh script.  Unlike the backup.sh script only one backup to TSM will run at any given time on the remote host.  For that reason, there is only one subdirectory named backup_remote_dsm for the TSM configurations files.  The concurrent copy of files to TSM is control by "resourceutilization" setting in the dsm.sys configuration files in the backup_remote_dsm directory.  The inclexcl.def file is this directory is overwritten with the specified management class prior starting each volumes copy to TSM.

Once a copy to TSM completes the flag file is updated with the status and the volume is unmounted from the remote host.

Backups to TSM without Snapshots

If necessary you can use the backup.sh script without the NetApp snapshot feature. In these case the backup.sh script will temporarily rename the files with a data and time suffix before they are copied to TSM.  The format of the file name is: <file_name>_MonDD_YYYY_HHMM, for example: altra_data_04.dbf is renamed to: altra_data_04.dbf_Dec05_2002_2330. Once the tape copy is completed the file is renamed to its original name before the script proceeds to the next file.  By renaming the files it is easier to identify which files in TSM belong with each other as the will all have the same suffix name.

When a snapshot is used the files are not renamed because the copy to TSM includes the name of the snapshot in the path, and the name of the snapshot clearly identifies the data and time the snapshot was created. For example: /tips/.snapshot/tips_110112_1200_R2. 

Supporting Scripts Created for Restores and Clones

When a backup is successful it will create scripts that can be assist with restoring or cloning the database. This script is time stamped and the most recent scripts are maintained on the host in the directory with the backup.sh script. An example is RESTORE_PROD_SCRIPT_Jun29_2011_0230.sh. will restore the Prod database. You only need to shutdown the target database, change the rights on the script to 700, run it, then start the database when the script completes.  If a snapshot was used in the backup then a script to restore the backup from the snapshot is also created. For example the RESTORE_TIPS_SNAPSHOT_tips_110227_0221_R7.sh will restore the TIPS database from the tips_110227_0221_R7 snapshot.

The backup.sh script also creates a scripts that are used to clone the database from either TSM or a snapshot. The most recent backup creates a clone scripts without a time stamp such as CLONE_TIPS_SCRIPT.sh and CLONE_TIPS_SNAPSHOT.txt. These are the source scripts that will be used by the clone script on the target host. Each backup also creates a time stamped version of the clone scripts.  For example CLONE_TIPS_SNAPSHOT_tips_110226_0220_R7.txt is available if you don't want to clone from the most recent snapshot that is in CLONE_TIPS_SNAPSHOT.txt.   To clone from the a previous snapshot you can simply copy the appropriate text file over the current CLONE_TIPS_SNAPSHOT.txt.  The clone script will use ftp to retrieve the CLONE_TIPS_SNAPSHOT.txt file.

Backup Logs

There are multiple log files available for reviewing and troubleshooting backups.  The backup logs will be found in the same directory as the backup.sh script.  The backup_master.log contains a summary of all backup.sh backups, including mirror sync backups since the last time the file was deleted and it is delinated with a pipe, "|".  Below is an example of viewing the last backups completed, omitting the mirror syncs. Most of the columns are obvious, but the 3rd column is Normal indicating no errors, or it could be Error or Pending.  Pending indicates that the copy to tape is being preformed on a remote host.  When it is completed the log file will be updated.

A backup_daily.log file has the same data as the master log but it it fixed length and only includes backups that were completed since 7 am on the previous day. 

Backup data is also uploaded to the bkup.bkup_log table in database configured in the backup.sh script. The bkup.bkup_log table shown below contains additional information to the backup_master.log.  It is the data source for reporting backup status to the application administrators and developers as well as a process that verifies the backup files on the TSM system.

The most useful data for application administrators and developers is the bkup_available_snap_tape_vw view which list snapshots and backups on tape that are still available for restore.  You simply need to add a where clause to limit the results to a specific database.  For example the following select displays any backup of the TIPS database which are still available for restore.

For troubleshooting purposes a detail of each backup is found in a file named for the host and the date.  For example: backup_rsprod12_Dec12.log.  For example the detail includes the copy of each data file to TSM including start and stop time stamps.

Checking Backups Completed

Backups to TSM and run long sometimes due to load on the network, the TSM system, or growth of the database.  It is desirable that the backups complete copies to TSM prior to the daily user workload.  If a backup is running long the DBA can be notified by the backup_check_complete.sh which runs from crontab just after 7 AM each morning.