Linux/Unix shell auto export Oracle Database

Using shell scripts to monitor and manage Oracle databases will greatly simplify DBA workload, such as common instance monitoring, monitoring, and alarm log monitoring, and database backup, AWR report automatic mail, etc. This document describes how to use shell scripts in Linux to automatically export databases.

Linux Shell and Import and Export references:

Linux/Unix shell scripts call SQL and RMAN scripts

Passing variables between Linux/Unix shell SQL statements

Linux Unix shell calls PL/SQL

1. Automatically export the Oracle database shell script

# + ------------------------------------------------------- +
# + Export database by schema every day |
# + Author: Robinson |
# + Blog: http://blog.csdn.net/robinson-0612 |
# + Usage: |
# + Expdb. sh $ ORACLE_SID |
# + ------------------------------------------------------- +
#
#! /Bin/bash
#--------------------
# Define variable
#--------------------

If [-f ~ /. Bash_profile]; then
.~ /. Bash_profile
Fi

If [-z "$ {1}"]; then
Echo "Usage :"
Echo "'basename $0 'oracle_sid"
Exit 1
Fi

ORACLE_SID = $1; export ORACLE_SID
DT = 'date + % Y % m % d'; export DT
TIMESTAMP = 'date + % Y % m % d _ % H % m'
MAIL_LIST = "robinson.chen@12306.com"; export MAIL_LIST
MAIL_DIR =/users/robin/dba_scripts/sendEmail-v1.56
MAIL_FM = 'oracle @ szdb.com'
LOG_DIR =/users/robin/dba_scripts/custom/log
LOG_FILE = $ LOG_DIR/expdb _ $ {ORACLE_SID }_$ {TIMESTAMP}. log
DUMP_FILE = EXP _ $ {ORACLE_SID }_$ {DT}. dmp
DUMP_LOG = EXP _ $ {ORACLE_SID }_$ {DT}. log
DUMP_DIR =/u02/database/$ {ORACLE_SID}/BNR/dump
RETENTION = 1

#------------------------------------------------------------------------
# Check the target database status, if not available send mail and exit
#------------------------------------------------------------------------

Db_stat = 'ps-ef | grep pmon _ $ ORACLE_SID | grep-v grep | cut-f3-d _'
If [-z "$ {db_stat}"]; then
MAIL_SUB = "$ ORACLE_SID is not available on 'hostname' before try to export data !!! "
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-m $ MAIL_SUB
Exit 1
Fi;

#------------------------------------
# Check the dumpfile exist or not
#------------------------------------

If [-s "$ {DUMP_DIR}/$ {DUMP_FILE}"]; then
MAIL_SUB = "The dump file $ {DUMP_FILE} exists for $ {ORACLE_SID} on 'hostname', exit !!!! "
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-m $ MAIL_SUB
Exit 1
Fi

Echo "'date'"> $ LOG_FILE
Echo "The hostname is: 'hostname'"> $ LOG_FILE
Echo "The database is :$ {ORACLE_SID}" >>$ LOG_FILE
Echo "Start to export data..."> $ LOG_FILE

#--------------------------------------
# Start to export data
#--------------------------------------

Expdp \ '\/as sysdba \' directory = db_dump_dir dumpfile =$ {DUMP_FILE} logfile =$ {DUMP_LOG} schemas = goex_admin # parallel = 3
RC = $?

Echo ""> $ LOG_FILE
Cat $ {DUMP_DIR}/$ {DUMP_LOG}> $ LOG_FILE
Echo ""> $ LOG_FILE
Echo "------------------------- End of the log file ------------------------" >>$ LOG_FILE

Flag = 'cat $ {DUMP_DIR}/$ {DUMP_LOG} | grep-I "successfully completed "'

If ["$ {RC}"-eq 0] & [-n "$ {flag}"]; then
MAIL_SUB = "Export database $ {ORACLE_SID} finished successful on 'hostname '!!! "
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-o message-file = $ LOG_FILE
Else
MAIL_SUB = "Export database $ {ORACLE_SID} failed on 'hostname '!!! "
$ MAIL_DIR/sendEmail-u $ MAIL_SUB-f $ MAIL_FM-t $ MAIL_LIST-o message-file = $ LOG_FILE
Fi

#------------------------------------------------
# Removing files older than $ RETENTION parameter
#------------------------------------------------

Find $ {LOG_DIR}-name "expdb *. *"-mtime + $ RETENTION-exec rm {}\;
Find $ {DUMP_DIR}-name "EXP *"-mtime + $ RETENTION-exec rm {}\;

Exit

1 2 Next Page