Shell Script Cold Database Backup

Updated: 24-Nov-2004
Version: 3.0

Description

Retreives a list of all database files from the database whilst still running and generates a Shell script to do a unix copy to a directory and a shell script to run the dbverify utility. 

The datafiles can be copied to tape drive from the disk copy. Implementing the backup this way via a disk to disk copy reduces the database down time. 

This script now generates a equivalent restore command.

Parameters

$1 (MANDATORY) - ORACLE_SID of the database to backup
$2 (MANDATORY) - The directory to backup the database
$3 (MANDATORY) - The database connect string to export from.

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

#!/bin/ksh

function Usage 
{
   echo
   echo "Usage : cold_backup.ksh   "
   echo
   echo "where ORACLE_SID (Mandatory) - SID of database to start"
   echo "      BACKUP DIRECTORY - The location of the directory to backup the files to."
   echo " e.g.  $ cold_bu.ksh PROD /app/oracle/u1/backup" 
   echo
}

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

DATABASE=$1
ORA_BACKUP=$2; export ORA_BACKUP

USERPASS="/ as sysdba"

# Check that we have a database
if [ -z "${DATABASE}" ]
then
   echo
   echo "No Database Specified !"
   Usage
   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

# Check the backup directory exists
if [ ! -d "${ORA_BACKUP}" ]
then
  echo
  echo "Backup Directory $ORA_BACKUP does not exist !"
  echo
  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       

# GET A LIST OF ALL THE DATABASE DATAFILES
# 
 sqlplus -s "${USERPASS}" << EOF
set feed off pages 0 head off echo off line 250 trimspool on
col cr newline
spool /tmp/copy1.ksh
select ' echo "Backing Up All Datafiles ..."' from dual;
select ' cp -p '||file_name|| ' ${ORA_BACKUP}/.' cr
      ,' compress -f ' || '${ORA_BACKUP}/' || 
       subStr(file_name, instr(file_name, '/', -1)+1)
from sys.dba_data_files;
select ' cp -p '||name ||'  ${ORA_BACKUP}/.'
from v\$controlfile;
select ' cp -p '||member ||'  ${ORA_BACKUP}/.' cr
       ,' compress -f ' || '${ORA_BACKUP}/' || 
       substr(member, instr(member, '/', -1)+1)
from v\$logfile;
select ' cp ${ORACLE_HOME}/dbs/init${DATABASE}.ora ${ORA_BACKUP}/. '
from dual;
spool off
spool /tmp/restore1.ksh
select 'echo "Restore Script..."' from dual;
select ' cp ' || '${ORA_BACKUP}/' || 
       substr(file_name, instr(file_name, '/', - 1)+1) || '.Z ' || file_name ||'.Z' cr
       , ' uncompress ' || file_name || '.Z &'
from dba_data_files;
select ' cp ' || '${ORA_BACKUP}/' || 
       substr(member, instr(member, '/', -1)+1) || '.Z ' || Member || '.Z' cr
       ,' uncompress ' || member || '.Z &'
from v\$logFile;
select ' cp ' || '${ORA_BACKUP}/' || 
       substr(name, instr(name, '/', -1) +1) || ' ' || name 
From v\$controlfile;
select ' cp ' || '${ORA_BACKUP}/init${DATABASE}.ora $ORACLE_HOME/dbs'
from dual;
spool off
exit
EOF
#
# GENERATE SOME DBVERIFY COMMANDS
# 
 sqlplus -s "${USERPASS}" << EOF
set feed off pages 0 head off echo off line 250 trimspool on
spool /tmp/dbv1.ksh
select ' dbv file=${ORA_BACKUP}/'||
        '\`basename '||file_name||'\`'
       from sys.dba_data_files ;
spool off
exit
EOF
#
# SHUT THE DATABASE DOWN
# 
sqlplus "${USERPASS}" << EOF
shutdown immediate
exit
EOF

#
# DO THE COPY AND DBVERIFY IF IT EXISTS
# 
 chmod +x /tmp/copy1.ksh
 chmod +x /tmp/restore1.ksh
 chmod +x /tmp/dbv1.ksh

 /tmp/copy1.ksh

#
 if [ -x "${ORACLE_HOME}/bin/dbv" ]  
 then
     echo
     echo Performing DB Verify Check
     /tmp/dbv1.ksh
 fi
#
# STARTUP THE DATABASE 
# 
sqlplus "${USERPASS}" << EOF
startup
exit
EOF

exit 0

Related Links

Oracle 8: (Shell Script) Cold Database Backup

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links