Automate RMAN Backups via Catalog Scripts

You can take advantage of RMAN catalog scripts to automate RMAN backups on target databases. However, the biggest disadvantage is when the catalog database is not available, the backups are not triggered. This is the reason why DBAs still prefer Linux scripts to automate RMAN backups.

• Create Catalog Stored Script

• Execute Stored Scripts

• Schedule Script via Crontab

• Updating Stored Scripts

• Delete Catalog Stored Scripts

• View Catalog Stored Scripts

We will connect to catalog database and first create a backup script. Next we will use the catalog script to schedule a backup on a target database.

Create Catalog Stored Script

Connect only to catalog database and create a backup script

create global script db_full_bkp
{
backup database plus archivelog;
}

Execute Stored Scripts

Within RMAN you can run below command to execute a stored catalog script

run { EXECUTE SCRIPT db_full_bkp; }

You can also pass the catalog script directly to rman utility

rman target / catalog rman_rc/rman_rc@rcat script 'db_full_bkp'

Schedule Script via Crontab

Let us create a shell script that calls the catalog stored script to take RMAN backup

vi /u02/rman/scripts/PRODDB_CATSCRIPT_rman_bkp.sh

#!/bin/bash
. /home/oracle/.bash_profile

export DATE=$(date +%m%d%y_%H%M%S)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=proddb

$ORACLE_HOME/bin/rman target / catalog rman_rc/rman_rc@rcat script 'db_full_bkp' log=/u01/rman/logs/${DATE}_proddb_cat_BACKUP.log

Give execute permission on backup script file

chmod 775 /u01/rman/scripts/PRODDB_CATSCRIPT_rman_bkp.sh

Schedule the script in crontab to run at 4pm daily

crontab -e

00 16 * * * /u01/rman/scripts/SID_rman_bkp.sh

Updating Stored Scripts

Connect to catalog database and use below to update stored script

REPLACE SCRIPT db_full_bkp
{
backup database;
backup archivelog all delete input;
}

Delete Catalog Stored Scripts

Simple delete command will delete stored script on catalog database

DELETE SCRIPT db_full_bkp;

View Catalog Stored Scripts

You list all the stored scripts via a LIST command OR view a specific script contents via PRINT command

LIST SCRIPT NAMES;
LIST GLOBAL SCRIPT NAMES;
LIST ALL SCRIPT NAMES;

PRINT SCRIPT full_bkp;