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:
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/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