DeveloperNote.com

In 2008 when I worked with some Oracle databases under Solaris and AIX, I spent some time to figure out how to make the database backup and restore and decided to use cold backup as the most straightforward method. As far as I remember, to backup the database I shutted down Oracle and then simply archive the database files using “zip” command. To restore the database I used the following shell script that extracts archived files and adjust some Oracle settings:

#!/bin/bash

#read -p "Enter the name of the database: " -e db_name
#export ORACLE_SID=$db_name
db_name=$ORACLE_SID

# variable value
# export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1

procCount=`ps -ef|grep -i $db_name$|wc -l`
if [ $procCount -ne 0 ] 
then
   read -p "Database with the given name is running. Do you want to shutdown instance: Y/N " -e ans
    if [ "$ans" = "Y" ]
        then 
        #ps -ef |grep -i $db_name$ | awk '{print $2}'|xargs kill -9
        $ORACLE_HOME/bin/sqlplus -S '/as sysdba' << EOF
shutdown abort;
EOF
        else 
        exit 0
    fi
fi

read -p "Enter name of the backup zip-file: " -e back

#read -p "Enter the absolute path for the location of DB files(without the last slash): " -e dir
dir=$ORACLE_ORADATA

fileCount=`ls -l $dir|wc -l`
if [ $fileCount -ne 0 ] 
    then
        rm -r $dir*
        unzip -j $back -d $dir
    else 
        unzip -j $back -d $dir
fi

mkdir $dir/dump
dumpDir=$dir/dump

mkdir -p $dumpDir/$db_name/adump
mkdir -p $dumpDir/$db_name/bdump
mkdir -p $dumpDir/$db_name/cdump
mkdir -p $dumpDir/$db_name/udump
pfl=`ls $dir/pf*`
control=(`ls $dir/*.ctl`)


sed -e '/file_dest/d;/files/d;/dump/d;/sga/d' $pfl > /tmp/pfl 
cat > $pfl < /tmp/pfl
rm /tmp/pfl
cat >> $pfl << EOF
*.control_files='${control[0]}','${control[1]}','${control[2]}'
*.audit_file_dest='$dumpDir/$db_name/adump'
*.background_dump_dest='$dumpDir/$db_name/bdump'
*.core_dump_dest='$dumpDir/$db_name/cdump'
*.user_dump_dest='$dumpDir/$db_name/udump'
*.sga_target=500M
*.sga_max_size=800M
EOF
echo -e "\n"
echo -e "===================================="
echo -e "Creating SPFILE \n"
$ORACLE_HOME/bin/sqlplus -S '/as sysdba' << EOF
create spfile from pfile='$pfl';
startup mount;
EOF

IFS='$'
FileNew=(`$ORACLE_HOME/bin/sqlplus -S '/as sysdba' << EOF
set heading off feedback off termout off trimspool on line 1000
select 'ALTER DATABASE RENAME FILE '''||member||''' TO ''$dir/'||regexp_substr(member,'[^/]*$')||''';$' from v\\$logfile;
select 'ALTER DATABASE RENAME FILE '''||name||''' TO ''$dir/'||regexp_substr(name,'[^/]*$')||''';$' from v\\$datafile;
EOF`)
echo -e "\n"
echo -e "===================================="
echo -e "Renaming datafiles and redo logs \n"
$ORACLE_HOME/bin/sqlplus -S '/as sysdba' << EOF
${FileNew[@]}
alter database open;
EOF
echo -e "Database is open \n"

This script requires the following environment variables to be set:

# .bashrc

export ORACLE_SID=MYDB
export ORACLE_HOME=/export/home/oracle/product/10.2.0/db_1
export ORACLE_ORADATA=/export/home/oracle/app/oracle/oradata/MYDB

export PATH=$PATH:/usr/bin:$ORACLE_HOME/bin

I do not remember exact versions, but as seen from the script above Oracle version was 10.2.0 or 10g.