GotoDBA Infrastructure Bash for DBAs – Part 3

Bash for DBAs – Part 3

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!

1 thought on “Bash for DBAs – Part 3”

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post