This is the last part of the series, and as promised I’ll have a script to perform database export. If you wish to read the first posts, here they are: part1, part2.
The script is interactive and this is how it works:
- The script performs full database export
- It will ask you where to put the file and the file name
- It will check if the file exists and if it is if you wish to overwrite it
- There are defaults for everything
- I tried to perform all the necessary checks so it won’t get any strange errors
- I wrote many comments so you can understand what I did and why
Last note, I wrote this script for educational purposes. You are welcome to use it freely, but at your risk, it might contain errors or bugs.
The script:
#!/bin/bash | |
#------------------ | |
# variables | |
#------------------ | |
def_dirname=/tmp | |
def_filename=full_export.dmp | |
ora_dirname=temporarydirectoryforexport | |
overwrite_dumpfile=n | |
tmpfile=JustATemporaryFile | |
#------------------ | |
# functions | |
#------------------ | |
function readDBConn() { | |
ok=false | |
while ! $ok; do | |
# read username and password | |
read -p "Please enter a username with privileges to export the database [leave empty to use '/ as sysdba']: " dbuser | |
if [ -z "${dbuser}" ]; then | |
conn_string="/ as sysdba" | |
# the extra_sql is needed for cases when we connect with sysdba but the instance is not open | |
extra_sql="select 1 from dba_data_files where rownum=1;" | |
else | |
read -s -p "Please enter ${dbuser}'s password: " dbpwd | |
conn_string=${dbuser}/${dbpwd} | |
extra_sql="" | |
echo | |
fi | |
# try to connect to Oracle | |
$ORACLE_HOME/bin/sqlplus -s /nolog <<- EOF > /dev/null 2>&1 | |
set feedback off pages 0 | |
whenever sqlerror exit 1 | |
conn ${conn_string} | |
${extra_sql} | |
exit | |
EOF | |
if [ $? -ne 0 ]; then | |
# can't connect, try again | |
echo "Cannot connect to Oracle" | |
echo | |
else | |
# connected | |
ok=true | |
fi | |
done | |
} | |
function readInput() { | |
# read input from user | |
read -p "Please enter Oracle Home to use for export [leave empty to use '${ORACLE_HOME}']: " ORA_HOME | |
export ORACLE_HOME=${ORA_HOME:=${ORACLE_HOME}} | |
read -p "Please enter database SID [leave empty to use '${ORACLE_SID}']: " ORA_SID | |
export ORACLE_SID=${ORA_SID:=${ORACLE_SID}} | |
readDBConn | |
read -p "Please enter directory for the dump file [leave empty to use '${def_dirname}']: " dirname | |
dirname=${dirname:=${def_dirname}} | |
read -p "Please enter dump file name [leave empty to use '${def_filename}']: " filename | |
filename=${filename:=${def_filename}} | |
# append .dmp if there isn't | |
filename=${filename/%\.dmp/}.dmp | |
# generate log file name with the same name as the dump file but .log | |
logfilename=${filename/%\.dmp/\.log} | |
} | |
function confirm() { | |
# list all input and ask for confirmation | |
echo | |
echo "Export will use the following:" | |
echo "------------------------------" | |
echo "Oracle home: ${ORACLE_HOME}" | |
echo "Oracle SID: ${ORACLE_SID}" | |
echo "Export will be done by: ${dbuser:=sys as sysdba}" | |
echo "Export file: ${dirname}/${filename}" | |
echo "Log file: ${dirname}/${logfilename}" | |
echo | |
read -p "Is this ok? [y for yes, anything else to abort]: " v_confirm | |
if [ "${v_confirm}" != "y" ]; then | |
echo "Export aborted!" | |
exit | |
fi | |
echo | |
} | |
function checkDir() { | |
# check if the directory exists and if we can write to it | |
if [ -d ${dirname} ]; then | |
# directory exists | |
touch ${dirname}/${tmpfile} > /dev/null 2>&1 | |
if [ $? -eq 0 ]; then | |
# can write to the firectory | |
rm ${dirname}/${tmpfile} | |
else | |
# cannot write to the directory | |
echo "Cannot create a file in '${dirname}'" | |
exit 1 | |
fi | |
else | |
# direcroty does not exist | |
read -p "Directory '${dirname}' does not exist, create it? [y for yes, anything else to abort]: " v_confirm | |
if [ "${v_confirm}" != "y" ]; then | |
echo "Export aborted!" | |
exit | |
else | |
# create the directory | |
mkdir -p ${dirname} > /dev/null 2>&1 | |
if [ $? -ne 0 ]; then | |
echo "Cannot create directory '${dirname}'" | |
exit 1 | |
fi | |
fi | |
fi | |
} | |
function checkFile() { | |
# check if the file exists | |
if [ -f ${dirname}/${filename} ]; then | |
# file exists | |
read -p "File '${dirname}/${filename}' already exist, overwrite? [y for yes, anything else to abort]: " v_confirm | |
if [ "${v_confirm}" != "y" ]; then | |
echo "Export aborted!" | |
exit | |
else | |
overwrite_dumpfile=y | |
fi | |
fi | |
} | |
function createOracleDir() { | |
# creating directory in Oracle for the export | |
$ORACLE_HOME/bin/sqlplus -s /nolog <<- EOF > /dev/null 2>&1 | |
set feedback off | |
whenever sqlerror exit 1 | |
conn ${conn_string} | |
create directory ${ora_dirname} as '${dirname}'; | |
exit | |
EOF | |
if [ $? -ne 0 ]; then | |
echo "Cannot create Oracle directory" | |
exit 1 | |
fi | |
} | |
function runExport() { | |
# generate the overwrite clause for data pump | |
overwrite_clause="" | |
[[ "${overwrite_dumpfile}" == "y" ]] && overwrite_clause="reuse_dumpfiles=Y" | |
# run the export and save the exit code | |
echo | |
echo -n "Exporting the database... " | |
$ORACLE_HOME/bin/expdp \"${conn_string}\" full=y directory=${ora_dirname} dumpfile=${filename} logfile=${logfilename} ${overwrite_clause} > /dev/null 2>&1 | |
export_rc=$? | |
echo "Done" | |
} | |
function dropOracleDir() { | |
# delete the directory in Oracle | |
$ORACLE_HOME/bin/sqlplus -s /nolog <<- EOF > /dev/null 2>&1 | |
set feedback off | |
whenever sqlerror exit 1 | |
conn ${conn_string} | |
drop directory ${ora_dirname}; | |
exit | |
EOF | |
if [ $? -ne 0 ]; then | |
echo "Cannot drop Oracle directory" | |
exit 1 | |
fi | |
} | |
#------------------ | |
# main | |
#------------------ | |
echo | |
echo "Welcome to the export assistant" | |
echo "===============================" | |
echo | |
readInput | |
confirm | |
checkDir | |
checkFile | |
createOracleDir | |
runExport | |
dropOracleDir | |
echo | |
if [ ${export_rc} -ne 0 ]; then | |
echo "Export failed!" | |
exit 1 | |
else | |
echo "Export completed successfully!" | |
fi |
Run examples:
$ ./run_export.sh
Welcome to the export assistant
===============================
Please enter Oracle Home to use for export [leave empty to use '/opt/oracle/product/ora12c']:
Please enter database SID [leave empty to use 'ora12']:
Please enter a username with privileges to export the database [leave empty to use '/ as sysdba']:
Please enter directory for the dump file [leave empty to use '/tmp']:
Please enter dump file name [leave empty to use 'full_export.dmp']:
Export will use the following:
------------------------------
Oracle home: /opt/oracle/product/ora12c
Oracle SID: ora12
Export will be done by: sys as sysdba
Export file: /tmp/full_export.dmp
Log file: /tmp/full_export.log
Is this ok? [y for yes, anything else to abort]: y
File '/tmp/full_export.dmp' already exist, overwrite? [y for yes, anything else to abort]: y
Exporting the database... Done
Export completed successfully!
$
$
$ ./run_export.sh
Welcome to the export assistant
===============================
Please enter Oracle Home to use for export [leave empty to use '/opt/oracle/product/ora12c']:
Please enter database SID [leave empty to use 'ora12']:
Please enter a username with privileges to export the database [leave empty to use '/ as sysdba']: system
Please enter system's password:
Please enter directory for the dump file [leave empty to use '/tmp']: /tmp/export
Please enter dump file name [leave empty to use 'full_export.dmp']: my_export
Export will use the following:
------------------------------
Oracle home: /opt/oracle/product/ora12c
Oracle SID: ora12
Export will be done by: system
Export file: /tmp/export/my_export.dmp
Log file: /tmp/export/my_export.log
Is this ok? [y for yes, anything else to abort]: y
Exporting the database... Done
Export completed successfully!
Hi Amitzil,
Thanks a lot for this informative series. It’s also very usefull for WLSA’s (WebLogic Administrators).
Regards,
Martien