• Arun Kumar

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.

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;

449 views

Recent Posts

See All

Generate Table DDL Command

The DBMS_METADATA package allows you to generate DDL commands for any of the database objects inside Oracle database. Generate Table DDL Command Generate View DDL Command Generate Materialized View DD