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
Linux/Unix shell monitoring Oracle instance (Monitor instance)
Linux/Unix shell monitoring Oracle listener (Monitor listener)
Monitor alter Log File)
Use of Data Pump expdp export tool
Use of Data Pump impdp import tool
Import and Export Oracle Partition Table Data
Use of exclude/include in expdp impdp
Use the Oracle DataPump API to export data

 

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_profilefiif [ -z "${1}" ];then    echo "Usage: "    echo "      `basename $0` ORACLE_SID"    exit 1fiORACLE_SID=$1;                 export ORACLE_SID DT=`date +%Y%m%d`;             export DTTIMESTAMP=`date +%Y%m%d_%H%M`MAIL_LIST="robinson.chen@12306.com"; export MAIL_LISTMAIL_DIR=/users/robin/dba_scripts/sendEmail-v1.56MAIL_FM='oracle@szdb.com'LOG_DIR=/users/robin/dba_scripts/custom/logLOG_FILE=$LOG_DIR/expdb_${ORACLE_SID}_${TIMESTAMP}.logDUMP_FILE=EXP_${ORACLE_SID}_${DT}.dmpDUMP_LOG=EXP_${ORACLE_SID}_${DT}.logDUMP_DIR=/u02/database/${ORACLE_SID}/BNR/dumpRETENTION=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 1fi;# ------------------------------------#  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 1fiecho "`date`" >>$LOG_FILEecho "The hostname is :`hostname`">>$LOG_FILEecho "The database is :${ORACLE_SID}">>$LOG_FILEecho "  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=3RC=$?echo " ">>$LOG_FILEcat ${DUMP_DIR}/${DUMP_LOG} >>$LOG_FILEecho " ">>$LOG_FILEecho "------------------------- End of the log file ------------------------">>$LOG_FILEflag=`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_FILEelse    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_FILEfi# ------------------------------------------------# 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

2. Additional instructions
A. This script implements Schema-based database export. If you want to export the entire database, you need to modify the export code.
B. the exported dump file is in the format of exp _ $ oracle_sid_yyyymmdd.dmp, exp _ $ oracle_sid_yyyymmdd.log.
C. Since the file is exported only once a day, yyyymmdd is used for the above file name. If you export the file multiple times, consider modifying the code or file name format.
D. Use escape characters in the Export command
E. Use the sendemail mail program to send emails. See: Indispensable sendemail
F. This script is available in Oracle 10g testing, and Oracle 11g is tested
G. To implement automatic export, deploy it to crontab.

 

3. More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)