#!/bin/bash
#***********************************************************************
# This script automates steps when migrating from Oracle EE to Oracle SE
# Usage: called from Import_to_SE_main.sh
#***********************************************************************
#***********************************************************************
#------------------------
#functions Start
#------------------------
setStatus(){
echo $1 > ${PROCESS_STATUS_FILE}
}
#------------------------
#functions End
#------------------------
#----------------------------------------------------
#Begin execution
#----------------------------------------------------
#----------------------------------------------------
# Step 1 Create the schema
#----------------------------------------------------
echo " "
echo "Step 1. Create Schema(RunOnSite.sh)...............Starting"
echo " "
cd ${DB_FULL_INSTALL}
./setEnv.sh
echo " "
echo "Step 1. Create Schema(setEnv.sh)....................Done"
echo " "
#----------------------------------------------------
# Step 2 Prepare and execute SQL files - disable Constraints, Triggers, from Sequences
#----------------------------------------------------
echo " "
echo "Step 2. Disable Triggers, Disable Constraints, Drop sequences........Starting"
echo " "
cd ${EXP_IMP_WORKAREA}
sqlplus ${User}/${Pass}@${ConnectStr} <<EOF
set heading off
set feedback off
set linesize 300
set pagesize 2000
spool disable_triggers.sql
select 'ALTER TRIGGER '|| trigger_name || ' DISABLE' || ';' from user_triggers;
spool off
spool disable_constraints.sql
select 'ALTER TABLE '|| table_name || ' DISABLE CONSTRAINT ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off
spool drop_sequences.sql
select 'DROP SEQUENCE '|| sequence_name || ';' from user_sequences;
spool off
exit;
EOF
less disable_triggers.sql | grep -vi 'SQL>' > disable_triggers.sql_OK
mv -f disable_triggers.sql_OK disable_triggers.sql
less disable_constraints.sql | grep -vi 'SQL>' > disable_constraints.sql_OK
mv -f disable_constraints.sql_OK disable_constraints.sql
less drop_sequences.sql | grep -vi 'SQL>' > drop_sequences.sql_OK
mv -f drop_sequences.sql_OK drop_sequences.sql
sqlplus $User/$Pass@$ConnectStr <<EOF
@disable_triggers.sql
@disable_constraints.sql
@drop_sequences.sql
exit;
EOF
echo " "
echo "Step 2. Disable Triggers, Disable Constraints, Drop sequences........Done"
echo " "
#----------------------------------------------------
# Step 3 Import data with impdp
#----------------------------------------------------
echo " "
echo "Step 3. Import data with impdp....................................Starting"
echo " "
echo "Data Import...............................Starting at" `date +"%Y%m%d_%H%M%S"`
echo "migrate_schema: " $migrate_schema
echo "Current working directory: " `pwd`
echo "parameters file: "
cat ${ImpDataParamFile}
echo " "
if [[ $migrate_schema == false ]]; then
echo "Executing command: impdp "$User"/"$Pass"@"$ConnectStr "PARFILE="${ImpDataParamFile}
echo "Import to user: " $User"/******"@$ConnectStr
impdp $User/$Pass@$ConnectStr PARFILE=${ImpDataParamFile}
else
echo "Executing command: impdp "$SysUser"/"$SysPass"@"$ConnectStr "PARFILE="${ImpDataParamFile}
echo "Import to user: " $User"/******"@$ConnectStr " from user: " $ImpFromUser
impdp $SysUser/$SysPass@$ConnectStr PARFILE=${ImpDataParamFile}
fi
status=$?
setStatus $status
mv -f ${db_exp_dir}/${ImpDataLog} ${EXP_IMP_WORKAREA}/
if [[ $status -ne 0 ]]; then
echo " "
echo "***************************************************"
echo " ===> imdp data import FAILED!!! "
echo " ===> Check log " ${ImpDataLog}
echo "***************************************************"
exit $status
fi
echo "Data Import...................................Done at" `date +"%Y%m%d_%H%M%S"`
echo " "
echo "Sequences Import .........................Starting at" `date +"%Y%m%d_%H%M%S"`
if [[ $migrate_schema == false ]]; then
impdp $User/$Pass@$ConnectStr PARFILE=${ImpSeqParamFile}
else
impdp $SysUser/$SysPass@$ConnectStr PARFILE=${ImpSeqParamFile}
fi
status=$?
setStatus $status
mv -f ${db_exp_dir}/${ImpSeqLog} ${EXP_IMP_WORKAREA}/
if [[ $status -ne 0 ]]; then
echo " "
echo "***************************************************"
echo " ===> imdp sequences import FAILED!!! "
echo " ===> Check log " ${ImpSeqLog}
echo "***************************************************"
exit $status
fi
echo "Sequences Import .............................Done at" `date +"%Y%m%d_%H%M%S"`
echo " "
echo "Step 3. Import data with impdp....................................Done"
echo " "
#----------------------------------------------------
# Step 4 Prepare and execute SQL files - enable Constraints, Trigger,
#----------------------------------------------------
echo " "
echo "Step 4. Enable Triggers, Enable Constraints...........Starting"
echo " "
sqlplus $User/$Pass@$ConnectStr <<EOF
set heading off
set feedback off
set linesize 300
set pagesize 2000spool enable_triggers.sql
select 'ALTER TRIGGER '|| trigger_name || ' ENABLE ' || ';' from user_triggers;
spool off
spool enable_constraints.sql
select 'ALTER TABLE '|| table_name || ' ENABLE CONSTRAINT ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off
exit;
EOF
less select enable_triggers.sql | grep -vi 'SQL>' > enable_triggers.sql_OK
mv -f enable_triggers.sql_OK enable_triggers.sql
less select enable_constraints.sql | grep -vi 'SQL>' > enable_constraints.sql_OK
mv -f enable_constraints.sql_OK enable_constraints.sql
sqlplus $User/$Pass@$ConnectStr <<EOF
@enable_triggers.sql
@enable_constraints.sql
exit;
EOF
echo " "
echo "Step 4. Enable Triggers, Enable Constraints...........Done"
echo " "
#----------------------------------------------------
# Step 5 Compile invalid objects
#----------------------------------------------------
cd ${EXP_IMP_PATH}
echo " "
echo "Step 5. Compile Invalid Objects..............Starting"
echo " "
sqlplus $User/$Pass@$ConnectStr @make_compile_invalid_objects.sql
echo " "
echo "Step 5. Compile Invalid Objects..................Done"
echo " "
#----------------------------------------------------
# Stage 6 Clean up temp files
#----------------------------------------------------
rm -f ${EXP_IMP_WORKAREA}/disable_triggers.sql
rm -f ${EXP_IMP_WORKAREA}/disable_constraints.sql
rm -f ${EXP_IMP_WORKAREA}/drop_sequences.sql
rm -f ${EXP_IMP_WORKAREA}/enable_triggers.sql
rm -f ${EXP_IMP_WORKAREA}/enable_constraints.sql
echo " "
echo "***************************************************"
echo " FINISHED Import of DataBase SUCCESSFULLY!!! "
echo "***************************************************"
echo " "
exit 0;
