Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 6 August 2013

RMAN Backup & Recovery Scripts for Linux System

This is some basic RMAN script to backup the entire database and archived redo logs for a non-RAC environments. In this example we will save the script in a file called “dbbackup_??.scp”. The script contains disk location which can be changed according to your specific environment. The resulting shell script can be run manually from the command line or schedule using CRON. For RMAN place the following command into cmdfile or create a batch file. The batch file must include a catalog=entry if a recovery catalog is used.
There is several RMAN Backup & Recovery script i already uploaded you can search them and update it as per your environment. Below is some basic shell script i found. Hope it will be useful. While creating the RMAN shell script some of the point you must keep in mind. Click on the below link: Point to be considered with RMAN Backup Scripts
Consider you have already configure RMAN controlfile autobackup parameter ON
configure controlfile autobackup format for device type disk to '/u10/catdb/backup/auto_cntrl_%F';
configure controlfile autobackup on;
RMAN Script: Database Full Backup
To take rman offline backup mount the database and run the below script. Do not forget to startup the database again. It is better to include shutdown/startup as a part of the script. For more details please check our other rman offline backup script.
run {
allocate channel c1 type disk;
backup tag weekly_orcl3_full
format '/u07/orcl3/backup/full_%d_%s_%p_%t'
(database);
release channel c1;
allocate channel c2 type disk;
backup format '/u07/orcl3/backup/archive_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 2 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 Catalog Catalog/Catalog@rman cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm_level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 2 cumulative tag orcl3_cm2
        format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
        (database);
release channel c1;

#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm2_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 1 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm level1 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm1.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 1 cumulative tag orcl3_cm1
        format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
        (database);
release channel c1;

#backup up archivelog files
allocate channel c2 type disk;
backup format '/u10/catdb/backup/cm1_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Cumulative level 0 backup
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman backup cm_level0 for CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/dbbkup_cm0.scp'
echo rman backup cm level0 for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup incremental level 0 cumulative tag orcl3_cm0
        format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
        (database);
release channel c1;

#backup up archivelog files
allocate channel c2 type disk;
backup
format '/u10/catdb/backup/cm0_%d_%s_%p_%t'
(archivelog all);
release channel c2;
}
RMAN Script: Deleting archivelog when catalog exists.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/orcl3_archflush.scp'
exit
# RMAN SCRIPT: DELETING ARCHIVE LOGS
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
#or  RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;
release channel c1;
}
RMAN Script: Deleting the old archives when no catalog exists
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="catdb"
PATH=$PATH:$ORACLE_HOME/bin
rman target sys/oracle@catdb cmdfile='/u04/rman_archflush.scp'
exit              
run
{
allocate channel c1 type disk;
delete archivelog until time 'SYSDATE-8';
# OR delete archivelog until sequence=;
release channel c1;
RMAN Script: Backing up all the archivelog files
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
PATH=$PATH:$ORACLE_HOME/bin
echo rman ARCHIVE backup for  CATDB started `date` >> /u07/catdb/rmanbkup.log
rman target sys/oracle@orcl3 catalog catalog/catalog@catdb cmdfile='/u04/catdb/arch_bkup.scp'
echo rman ARCHIVE backup for CATDB ended `date` >> /u07/catdb/rmanbkup.log
exit
run
{
allocate channel c1 type disk;
backup
format '/u10/catdb/backup/arch_%d_%s_%p_%t'
(archivelog all);
release channel c1;
# deleting archive logs older than 8 days
allocate channel c2 type disk;
delete archivelog until time 'SYSDATE-5';
release channel c2;
}
Database Backup Script (OFFLINE / LOGICAL BKUP)
Script1 : Offline backup
This scripts first take the logical export using datapump, then it shutdown the database, Copy the files to tape drive using
TAR(it coppies all oracle db related file like spfile, pwdfile, listener,tnsnames files aswell) , then start the database.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo orcl3 database export started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (TAKING DATAPUMP EXP)
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:offexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:offexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo ORCL3 database export ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database shutdown begin at `date` >> /u05/orcl3/export/offbkup_orcl3.log (SHUTTING DOWN THE DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/shutdown_orcl3.sql
echo ORCL3 database shutdown at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo TAPE archiving started at `date` >> /u05/orcl3/export/offbkup_orcl3.log (COPYING FILES TO TAPEDRIVE USING TAR)
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u01/app/oracle/product/10.2.0/dbs/initorcl3.ora /u01/app/oracle/product/10.2.0/dbs/spfileorcl3.ora /u01/app/oracle/product/10.2.0/network/admin/listener.ora /u01/app/oracle/oradata/orcl3 /u02/orcl3/oradata /u03/orcl3/oradata /u04/orcl3/oradata /u05/orcl3/oradata /u06/orcl3/orcl3/scripts /u06/orcl3/arch
echo TAPE archiving ended at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 database getting started at `date` >> /u05/orcl3/export/offbkup_orcl3.log(STARTING DATABASE)
$ORACLE_HOME/bin/sqlplus /nolog @/u06/orcl3/orcl3/scripts/startup_orcl3.sql
echo ORCL3 database started at `date` >> /u05/orcl3/export/offbkup_orcl3.log
echo ORCL3 offline backup finished at `date` >> /u05/orcl3/export/offbkup_orcl3.log
$ more startup_orcl3.sql
connect /as sysdba;
startup;
exit;
$ more shutdown_orcl3.sql
connect / as sysdba;
shutdown immediate;
exit;
Script2:  Datapump export backup.
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo export started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
$ORACLE_HOME/bin/expdp system/oracle dumpfile=datapump_dir:dailyexpdp-`date '+%Y%m%d'`.dmp logfile=datapump_log:dailyexpdp-`date '+%Y%m%d'`.log schemas=orafin
echo export stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
echo tape archiving started at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
tar Ecvf /dev/rmt/0 /u05/orcl3/export /u06/orcl3/arch
echo tape archiving stopped at `date` >> /u05/orcl3/export/dailyexpdp_orcl3.log
Script3: Backup all archive files generated during working hrs
$ more morning_arch.sh
echo morning tape archiving started
tar cvf /dev/rmt/0 /u06/orcl3/arch
echo morning tape archiving ended
Scripts to Stop/Start the Enterprise Manager while performing Offline backup
$ more emctl_start.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STARTING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl start dbconsole
$ more emctl_stop.sh
#!/bin/ksh
export ORACLE_HOME="/u01/app/oracle/product/10.2.0"
export ORACLE_SID="orcl3"
echo STOPPING ENTERPRISE MANAGER DATABASE CONSOLE
$ORACLE_HOME/bin/emctl stop dbconsole
RMAN SCRIPT : DISASTER RECOVERY
# The commands below assume that all initialization parameters files are in place and the complete directory structure for the datafiles is recreated and you already set LS_LANG environment variable
setenv NLS_LANG amarican_america.we8dec
# Start RMAN without the target option, and use the following commands to RESTORE and RECOVER the database
# SET DBID 63198018;
# not required if using recovery catalog. You can find DBID from RMAN Controlfile Autobackup.
        connect target sys/oracle@orcl3
        startup nomount;
        run
        {
# you need to allocate channels if not using recovery catalog.
        allocate channel c1 type disk;
# optionally you can set newname and switch commands to restore datafiles to a new location
        restore controlfile from autobackup;
        alter database mount;
        restore database;
        reocver database;
        alter database open resetlogs;
Note: You must take  a new whole database backup after resetlogs, since backups of previous incarnation are not easily usable.
RMAN Script: POINT IN TIME RECOVERY
# This scenario assumes that all initializaiton filesa and the current controlfile are in place and you want to recover to a point in time '2012-05-22"10:30:00'.
# Ensure you set your NLS_LANG enviroment variable
        STARTUP MOUNT FORCE;
        RUN
        {
        SET UNTIL TIME "TO_DATE('2012-05-22"10:30:00','yyyy-dd-mm:hh24:mi:ss')";
        RESTORE DATABASE;
        RECOVER DATABASE;
        ALTER DATABASE OPEN RESETLOGS;
        }
Note: You must take a new whole database backup after resetlogs,since backups of previous incarnation are not easily usable
RMAN SCRIPT : CONTROLFILE RECOVERY
# Oracle strongly recommends that you specify multiple controlfiles, on separate  physical disks and controllers, in the CONTROL_FILE initialization parameter.
# - If one copy is lost due to media failure, copy one of the others over the lost controlfile and restart the instance.
# - If you lose all copies of the controlfile, you must re-create it using the create controlfile sql command
# You should use RMAN to recover a backup controlfile only if you have lost all copies of the current controlfile, because after restoring a backup controlfile, you will have to open RESETLOGS and take a new whole database backup.
# This section assumes that all copies of the current controlfile have been lost, and than all initialization parameter files, datafiles and online logs are intact.
# Ensure you set your NLS_LANG environment variable e.g. in unix (csh):
#  >setenv NLS_LANG american_america.we8dec
# Start RMAN without the TARGET option, and use the following commands to restore and recover the database;
# SET DBID 63198018;
        connect target sys/oracle@orcl3
        startup nomount;
        run
        {
# you need to allocate channels if not using recovery catalog.
        allocate channel c1 type disk;
        restore controlfile from autobackup # or directly provide the controlfile backup location
        alter database mount;
        recover database;
        alter database open resetlogs;
        }
# you must take a new whole database backup after reerlogs, since backups of previous incarnation are not easily usable
RMAN Script: DATAFILE RECOVERY
# This section assumes that datafile 5 has been damaged and needs to be restored and recovered, and that the current controlfile and all other datafiles are intact. The database is mounted during the restore and recovery.
# - offlie the datafile that needs recovery
# - restore the datafile from backups
# - apply incrementals and archivelogs as necessary to recover.
# - make online recovered datafile
        run
        {
        sql 'alter database datafile 5 offline';
#if you want to restore to a different location,uncomment the following command
# Set newname for datafile 5 to '/newdirectory/new_filename.dbf';
        restore datafile 5;
# if you restored to a different locatin, uncomment the command below to switch the controlfile to point to the file in the new location
# SWITCH DATAFILE ALL;
        recover datafile 5;
        sql 'alter database datafile 5 online';
        }
Below is the link for some of the related RMAN backup shell script:

7 comments:

  1. Thanks for sharing!!

    Keep up the good work!

    ReplyDelete
  2. Thanks for the scripts and sharing the knowledge. For the RMAN SCRIPT : CONTROLFILE RECOVERY, If you want to automate to find the latest control file from the backup location for restore, here is the script to find the controlfile backupset

    ReplyDelete
    Replies
    1. Hi All the above link('here is the script to find the controlfile backupset')spreads malware to your PC. Beware!!!

      Delete
  3. ASK,


    Good Scripts.

    ReplyDelete
  4. I LIKE THIS ARTICLE SO GOOD. THANK YOU .I LIKE IT.

    ReplyDelete