UNIX Shell Script Hot Database Backup

Updated : 13-Sep-2002
Version : 2.1

Description

Generates SQL script put the tablespace in backup mode and to do a unix copy to the backup directory. The datafiles can be copied to tape drive from the backup directory disk copy.

Parameters

$1 (MANDATORY) - ORACLE_SID of the database to hot backup
$2 (MANDATORY) - The tablespace name to back up
$3 (MANDATORY) - The directory to backup the database
$4 (MANDATORY) - The database connect string to export from.

Shell Source

#!/bin/ksh

function Usage
{
   echo ""
   echo " Usage : hot_backup.ksh   "
   echo ""
   echo "where ORACLE_SID (Mandatory) - SID of database to start"
   echo "       TABLESPACE NAME : Tablespace to backup "
   echo "       BACKUP DIRECTORY : target backup location "
   echo ""
   echo " e.g.  $ hot_backup.ksh PROD USERS" 
   echo ""
}

#########################################################
# START OF MAIN SCRIPT
#########################################################

DATABASE=$1
TABLESPACE=$2; export TABLESPACE
ORA_BACKUP=$3; export ORA_BACKUP

USERPASS="/ as sysdba"

# Check that we have a database
if [ -z "${DATABASE}" ]
then
   echo
   echo "No Database Specified !"
   echo
   Usage
   exit 1
fi
# Check that we have a tablespace name to back up
if [ -z "${TABLESPACE}" ]
then
   echo
   echo "No Tablespace Name Specified !"
   echo
   Usage
   exit 1
fi
# Check the backup directory exists
if [ ! -d "${ORA_BACKUP}" ]
then
  echo
  echo "$ORA_BACKUP does not exist !"
  echo
  exit 1
fi
# Check we have an Oracle username and password
if [ -z "${USERPASS}" ]
then
   echo
   echo "No Database connect string specified !"
   Usage
   exit 1
fi

ORACLE_SID=$DATABASE
export ORACLE_SID
export ORAENV_ASK=NO
. oraenv       

if [ ! -d "${ORACLE_HOME}" ]
then
  echo
  echo "$ORACLE_HOME does not exist !"
  echo
  exit 1
fi

# Generate the backup commands
#
 sqlplus -s "${USERPASS}" << EOF
whenever sqlerror exit 1 rollback
whenever oserror exit 2
set lines 10000 head off feedback off echo off pages 0  termout off
set trims on verify off
col x noprint
col y noprint
col z noprint

spool /tmp/hot1.sql

select t.name z,
       t.ts#  y,
       1 x,
      'alter tablespace '||t.name||' begin backup;'
from v\$tablespace t,
     dba_tablespaces tbs
where t.name=tbs.tablespace_name
and   tbs.tablespace_name = '${TABLESPACE}'
and   tbs.status = 'ONLINE'
and   (tbs.contents !='TEMPORARY' or tbs.extent_management != 'LOCAL')
union
select d.name z,
       d.ts# y,
       2 x,
       'host cp -p '||rtrim(d.NAME)||' ${ORA_BACKUP}'
from v\$datafile d, v\$tablespace t
where t.ts#=d.ts#
and t.name = '${TABLESPACE}'
union 
select t.name z,
       t.ts#  y,
       3 x,
       'alter tablespace '||t.name||' end backup;' 
from v\$tablespace t,
     dba_tablespaces tbs
where t.name=tbs.tablespace_name
and   tbs.tablespace_name = '${TABLESPACE}'
and   tbs.status = 'ONLINE'
and   (tbs.contents !='TEMPORARY' or tbs.extent_management != 'LOCAL')
order by 2,3
/

alter system switch logfile;

REM
REM Backup controlfiles
REM

select 'alter database backup controlfile to '''||'${ORA_BACKUP}\'||substr(name,instr(name,'\',-1)+1)||''' REUSE;' from v$controlfile; 

alter database backup controlfile to trace;

spool off

exit
EOF
if [ "${?}" != "0" ]
then
   echo
   echo  Hot Backup Generate for tablespace ${TABLESPACE} !
   echo
else

#
# Now start the Backup
# 
sqlplus -s "${USERPASS}" << EOF
whenever sqlerror exit 2 rollback
whenever oserror exit 1
spool /tmp/hot1.log
@/tmp/hot1.sql
spool off
exit
EOF
if [ "${?}" != "0" ]
then
   echo
   echo  Hot Backup Failed for tablespace ${TABLESPACE} !
   echo
fi

fi
exit 0

Previous Oracle Version Links

Hot Database Backup

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal