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" <
@/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