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

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_profile
Fi

#--------------------------
# Check SID
#--------------------------
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
SRC_ORA_SID = SY5221A export SRC_ORA_SID
TIMESTAMP = 'date + % Y % m % d _ % H % m'
LOG_DIR =/u02/database/$ {ORACLE_SID}/BNR/dump
LOG_FILE = $ LOG_DIR/impdb _ $ {ORACLE_SID }_$ {TIMESTAMP}. log
DUMP_DIR =/u02/database/$ {ORACLE_SID}/BNR/dump
Tar_file1_exp_1_src_ora_sid1__1_dt1_.tar.gz
DUMP_FILE = EXP _ $ {SRC_ORA_SID }_$ {DT}. dmp
DUMP_LOG = IMP _ $ {ORACLE_SID }_$ {DT}. log
LAST_EXP_DUMP_LOG =$ {DUMP_DIR}/EXP _ $ {SRC_ORA_SID }_$ {DT}. log
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 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 1
Fi

#---------------------------------------------------
# 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 1
Fi

#-----------------------------------------------------------------------------
# Check dumpfile and export log file are correct, if no send mail and exit
#-----------------------------------------------------------------------------

Date >$ {LOG_FILE}
Echo "The hostname is: 'hostname'"> $ LOG_FILE
Echo "The source database is :$ {SRC_ORA_SID}" >$ {LOG_FILE}
Echo "The target database is :$ {ORACLE_SID}" >>$ LOG_FILE
Echo "" >>$ {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 1
Fi

# Renewal #------------------------------------------------------------------------------------------
# Remove all objects for specific schema before import data, if error send mail and exit
# Renewal #------------------------------------------------------------------------------------------

Echo-e "Prepare plsql script to remove all objects for specific schema... \ n"> $ LOG_FILE

Echo"
DECLARE
VERIFICATION VARCHAR2 (200 );

BEGIN
VERIFICATION: = 'bo _ admin ';

BO_ADMIN.GO_UTIL_DROP_SCHEMA_OBJECTS (VERIFICATION );
COMMIT;
END;
/
Exit ">/tmp/remove_obj. SQL

If [-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.log
Else
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 1
Fi

Res = '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}
Fi

Rm/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 = BO_ADMIN \
Table_exists_action = replace # parallel = 3
RC = $?

Cat $ {DUMP_DIR}/$ {DUMP_LOG}> $ LOG_FILE
If ["$ {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 1
Fi

#----------------------------------------------------------
# Compile invalid objects
#----------------------------------------------------------

Echo "" >>$ {LOG_FILE}
Echo-e "Starting compile invalid objects... \ n"> $ LOG_FILE

Echo"
Set linesize 145
Set pagesize 9999

Clear columns
Clear breaks
Clear computes

Column 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 report
Compute count label ''of object_name on owner
Compute count label 'Grand Total: 'of object_name on report
Spool/tmp/invalid_obj.log
SELECT
Owner
, Object_name
, Object_type
, Status
FROM dba_objects
WHERE status <> 'valid'
Order by owner, object_name
/
Spool off;
Exit ">/tmp/list_invalid_obj. SQL

Sqlplus-silent "/as sysdba" <EOF
@ $ ORACLE_HOME/rdbms/admin/utlrp. SQL
@/Tmp/list_invalid_obj. SQL
EOF

Echo-e "List all invalid objects \ n" >$ {LOG_FILE}
Echo "------------------------------------------------------------" >$ {LOG_FILE}
Cat/tmp/invalid_obj.log> $ LOG_FILE

Flag = '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 "$ {DUMP_DIR}/EXP _ $ {SRC_ORA_SID} *. gz"-exec rm {}\;

Exit

1 2 Next Page