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)