RMAN Scripts

RMAN Scripts

These all scripts will be helpful for understanding the RMAN scripting

Note: Configure controlfile auto backup on by below command

configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u10/catdb/backup/auto_cntrl_%F';

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';
}

No comments:

Post a Comment