Linux/Unix shell automatically import 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 article describes how to use the shell script to automatically import Oracle databases in Linux.

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. Linux/Unix shell automatically imports Oracle Database scripts

# +------------------------------------------------+# |   Import database by schema                    |# |   file_name: impdp.sh                          |# |   Parameter: Oracle_SID                        |# |   Usage:                                       |# |         ./impdb.sh ${ORACLE_SID}               |# |   Author : Robinson                            |# |   Blog   : http://blog.csdn.net/robinson_0612  | # +------------------------------------------------+##!/bin/bash# --------------------# Define variable# --------------------if [ -f ~/.bash_profile ]; then. ~/.bash_profilefi# --------------------------#   Check SID# --------------------------if [ -z "${1}" ];then    echo "Usage: "    echo "      `basename $0` ORACLE_SID"    exit 1fiORACLE_SID=$1;                 export ORACLE_SIDDT=`date +%Y%m%d`;             export DTSRC_ORA_SID=SY5221A            export SRC_ORA_SIDTIMESTAMP=`date +%Y%m%d_%H%M`LOG_DIR=/u02/database/${ORACLE_SID}/BNR/dumpLOG_FILE=$LOG_DIR/impdb_${ORACLE_SID}_${TIMESTAMP}.logDUMP_DIR=/u02/database/${ORACLE_SID}/BNR/dumpTAR_FILE=EXP_${SRC_ORA_SID}_${DT}.tar.gzDUMP_FILE=EXP_${SRC_ORA_SID}_${DT}.dmpDUMP_LOG=IMP_${ORACLE_SID}_${DT}.logLAST_EXP_DUMP_LOG=${DUMP_DIR}/EXP_${SRC_ORA_SID}_${DT}.logRETENTION=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 import data  !!!"#    $MAIL_DIR/sendEmail -u $MAIL_SUB -f $MAIL_FM -t $MAIL_LIST -m $MAIL_SUB    echo ${MAIL_SUB} |mail -s " $ORACLE_SID is not available on `hostname` !!!" dba@trade.com    exit 1fi# ---------------------------------------------------#  Unzip the dump file# ---------------------------------------------------if [ -s "${DUMP_DIR}/$TAR_FILE" ] ; then    cd ${DUMP_DIR}    tar -xvf ${TAR_FILE}else  MAIL_SUB="No dumpfile was found for ${ORACLE_SID} before import."    echo "No dumpfile was found for ${ORACLE_SID} before import."|mail -s $MAIL_SUB dba@trade.com    exit 1fi# -----------------------------------------------------------------------------#  Check dumpfile and export log file are correct, if no send mail and exit# -----------------------------------------------------------------------------date >${LOG_FILE}echo "The hostname is :`hostname`">>$LOG_FILEecho "The source database is :${SRC_ORA_SID}" >>${LOG_FILE}echo "The target database is :${ORACLE_SID}">>$LOG_FILEecho " " >>${LOG_FILE}flag=`cat ${LAST_EXP_DUMP_LOG} | grep -i "successfully completed"`if  [ -n "${flag}" ] && [ -s "${DUMP_DIR}/${DUMP_FILE}" ] ; then    echo -e "The dumpfile exists and can be imported to ${ORACLE_SID} \n">>${LOG_FILE}else    echo "The dumpfile does not exist or exist with errors on `hostname` before try to import data !!!" >>${LOG_FILE}    mail -s "The dumpfile does not exists or exist with errors for ${ORACLE_SID}" dba@trade.com <${LOG_FILE}    exit 1fi# ------------------------------------------------------------------------------------------#  Remove all objects for specific schema before import data, if error send mail and exit# ------------------------------------------------------------------------------------------echo -e "Prepare plsql script to remove all objects for specific schema....\n" >>$LOG_FILEecho "DECLARE  VERIFICATION VARCHAR2(200);BEGIN  VERIFICATION := 'GOEX_ADMIN';  GOEX_ADMIN.GO_UTIL_DROP_SCHEMA_OBJECTS ( VERIFICATION );  COMMIT;END;/exit ">/tmp/remove_obj.sqlif [ -s /tmp/remove_obj.sql ]; then    echo -e "Running  pl/sql script to remove objects for specific schema... \n" >>${LOG_FILE}    $ORACLE_HOME/bin/sqlplus -s "/ as sysdba" @/tmp/remove_obj.sql >/tmp/remove_obj_result.logelse    echo -e "No any plsql script found to remvoe objects. please remove them before import..." >>$LOG_FILE    MAIL_SUB="Import data to ${ORACLE_SID} error. Please remove objects for specific schema firstly"    mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}    exit 1fires=`cat /tmp/remove_obj_result.log | grep ORA- | grep -v grep`if [ -n "${res}" ] ; then    echo -e "Some errors caught during remove object, ingore them. \n" >>${LOG_FILE}firm /tmp/remove_obj.sql >/dev/null# ----------------------------------------------------------#  Start import data to target database# ----------------------------------------------------------echo -e "Starting import data to target database ...\n" >>${LOG_FILE}impdp \'\/ as sysdba \' directory=db_dump_dir dumpfile=${DUMP_FILE} logfile=${DUMP_LOG} schemas=GOEX_ADMIN \table_exists_action=replace #parallel=3RC=$?cat ${DUMP_DIR}/${DUMP_LOG}>>$LOG_FILEif [ "${RC}" -ne 0 ]; then    echo -e " Some errors caught during import data. exit !!!! \n" >>$LOG_FILE    MAIL_SUB="Import data to ${ORACLE_SID} errors, exit, please check !!!"    mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}    exit 1fi# ----------------------------------------------------------#  Compile invalid objects# ----------------------------------------------------------echo "">>${LOG_FILE}echo -e "Starting compile invalid objects ....\n" >>$LOG_FILEecho "SET LINESIZE  145SET PAGESIZE  9999clear columnsclear breaksclear computescolumn owner           format a25         heading 'Owner'column object_name     format a30         heading 'Object Name'column object_type     format a20         heading 'Object Type'column status          format a10         heading 'Status'column object_name     format a30         heading 'Object Name'column object_type     format a20         heading 'Object Type'column status          format a10         heading 'Status'break on owner skip 2 on reportcompute count label ''               of object_name on ownercompute count label 'Grand Total: '  of object_name on reportspool /tmp/invalid_obj.logSELECT    owner  , object_name  , object_type  , statusFROM dba_objectsWHERE status <> 'VALID'ORDER BY owner, object_name/spool off;exit ">/tmp/list_invalid_obj.sqlsqlplus -silent "/ as sysdba" <<EOF@$ORACLE_HOME/rdbms/admin/utlrp.sql@/tmp/list_invalid_obj.sqlEOFecho -e "List all invalid objects \n" >>${LOG_FILE}echo "------------------------------------------------------------">>${LOG_FILE}cat /tmp/invalid_obj.log >>$LOG_FILEflag=`cat ${DUMP_DIR}/${DUMP_LOG} | grep "completed with [0-9][0-9] error"`if [ -z "${flag}" ] ; then    echo -e "Import data to ${ORACLE_SID} completed successful at `date`  ...\n" >>${LOG_FILE}    echo "--------------------------- End of the log file ---------------------------">>${LOG_FILE}    MAIL_SUB="Import data to ${ORACLE_SID} completed successful on `hostname`."    mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}else    echo -e "Import data to ${ORACLE_SID} completed with some errors at `date`...\n" >>${LOG_FILE}    MAIL_SUB="Import data to ${ORACLE_SID} completed with some errors on `hostname`"    echo "--------------------------- End of the log file ---------------------------">>${LOG_FILE}    mail -s $MAIL_SUB dba@trade.com <${LOG_FILE}fi# ------------------------------------------------# Removing files older than $RETENTION parameter# ------------------------------------------------find ${LOG_DIR} -name "impdb*.*" -mtime +$RETENTION -exec rm {} \;find ${DUMP_DIR} -name "IMP*.*" -mtime +$RETENTION -exec rm {} \;find ${DUMP_DIR} -name "EXP_${SRC_ORA_SID}*" -mtime +$RETENTION -exec rm {} \;exit

2. process of removing all schema objects

CREATE OR REPLACE PROCEDURE GOEX_ADMIN."GO_UTIL_DROP_SCHEMA_OBJECTS" (verification VARCHAR2)IS   CURSOR c1   IS      SELECT   *          FROM (SELECT object_name, object_type, 1 AS grp                  FROM user_objects                 WHERE object_type IN                          ('PACKAGE',                           'PROCEDURE',                           'FUNCTION',                           'TABLE',                           'VIEW',                           'TYPE',                           'SEQUENCE'                          )                   AND object_name <> 'BO_SYS_DATAPUMP_PKG'                   AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'       AND object_name <> 'SYS_BACKUP_PARAM_TBL'                UNION ALL                SELECT object_name, object_type, 2 AS grp                  FROM user_objects                 WHERE object_type IN ('TYPE')                   AND object_name <> 'BO_SYS_DATAPUMP_PKG'                   AND object_name <> 'GO_UTIL_DROP_SCHEMA_OBJECTS'       AND object_name <> 'SYS_BACKUP_PARAM_TBL')      ORDER BY grp,               CASE object_type                  WHEN 'PACKAGE'                     THEN 1                  WHEN 'PROCEDURE'                     THEN 2                  WHEN 'FUNCTION'                     THEN 3                  WHEN 'VIEW'                     THEN 4                  ELSE 5               END;   objname                     VARCHAR2 (255);   objtype                     VARCHAR2 (255);   objgrp                      PLS_INTEGER;   verification_str   CONSTANT VARCHAR2 (10)  := 'GOEX_ADMIN';BEGIN   IF verification = verification_str   THEN      OPEN c1;      LOOP         FETCH c1          INTO objname, objtype, objgrp;         EXIT WHEN c1%NOTFOUND;         BEGIN            NULL;            IF objtype = 'TABLE'            THEN               EXECUTE IMMEDIATE    'drop '                                 || objtype                                 || ' '                                 || objname                                 || ' cascade constraints purge';            ELSE               EXECUTE IMMEDIATE 'drop ' || objtype || ' ' || objname;            END IF;            DBMS_OUTPUT.put_line (   objtype                                  || ' - '                                  || objname                                  || ' dropped successfully'                                 );         EXCEPTION            WHEN OTHERS            THEN               NULL;               DBMS_OUTPUT.put_line (   objtype                                     || ' - '                                     || objname                                     || ' dropped failed - '                                     || SQLERRM                                    );         END;      END LOOP;      CLOSE c1;   ELSE      DBMS_OUTPUT.put_line                ('Failed to verify the operation, please input verification.');   END IF;END;/

3. Additional instructions
A. The shell script implements Schema-based automatic import to the specified database. If the script is used to import the entire database, modify it accordingly.
B. For the format definition of the dump file used, see the article. Linux/Unix shell automatically exports the Oracle database. The format described in this article corresponds to the definition at the time of export.
C. Because the exported database is different, use src_ora_sid, and oracle_sid is the SID of the imported target database.
D. Check whether the database is in the available mode before trying to import the database, and decompress the tar file before importing the database.
E. Before importing a schema, call go_util_drop_schema_objects to remove all objects in the specified schema to reduce the number of errors in the import process.
F. Next, import the entire schema. After the import is complete, compile the invalid objects and list the invalid objects in the log file and send an email.
G. The code for removing the entire schema is provided.
H. You can integrate the automatic FTP dump file shell into this script. For details about the automatic FTP dump file, refer to the previous article. Linux/Unix shell automatic FTP backup file
I. Pay attention to the use of transfer characters in shell scripts and deploy them to crontab for automatic import.

 

4. 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)