Pages

Wednesday, February 12, 2014

Bash script by example. Bash scrips,impdp,sqlplus.

=============
The Flow
=============
The purpose of these scripts is to perform a migration from Oracle EE to Oracle SE.
It is assumed that a dmp from Oracle EE schema was taken, using expdb, and that the same schema was created on the target Oracle SE database.

 =============
The files
=============
import_to_se_main.sh
Is the start point for the process.
Checks that the script is executed from the correct place.
Checks that the database connection parameters were set.
Calls import_to_se_set_params.sh

 import_to_se_set_params.sh
Perform a set of checks by calling a generic call_sql_script.sh that handles access to Oracle DB via another sqlplus script. 
Reads from parameter file.
Builds the parameters file for impdb.
calls import_to_se.sh

 import_to_se.sh
The script that is actually calling impbp.
The logic for this step is described in details by another post:
Migrating from Oracle EE to Oracle SE

=====================
import_to_se_main.sh
=====================
#!/bin/bash
#***********************************************************************
# Wrapper script to import_to_se_set_params.sh
# Must be run from EXP_IMP_PATH=/dbinstall/xxx-xxx-xxx/x.x.x.xx/db/Export-Import;
# Usage: ./import_to_se_main.sh
#************************************************************************
# input files:
#------------------------------------------------------------------------
# 1. db/Sa/set_user.sql                (mandatory)
# define user=USER                     (mandatory)
# define pass=PASSWORD                 (mandatory)
# define connectstr=str                (mandatory)
# define ImpFromUser=import_from_user   (optional)
#                                        (By default import is done from same user to same user.
#                                        (Use ImpFromUser in case import is done from userA to userB)
# define ExpDmpFile=dump_file.dmp        (optional. Defaut dmp file name is Exp_$user.dmp)
#
# 2. db/Export-Import/import_exclude_list.txt optional
#                                           List here tables to be excluded during import phase
#                                           Each table should be in a sepparate line
#************************************************************************

 #------------------------
#functions Start
#------------------------
getStatus(){
  status=`cat ${PROCESS_STATUS_FILE}`
  return $status

 }
reportStatus(){
  status=$1
if [[ $status -eq 0 ]]; then
 echo " "
 echo "***************************************************"
 echo " Process Finished Successfully.     "
 echo "***************************************************"
else
 echo " "
 echo "***************************************************"
 echo " ===> Process FAILED!!!             "
 echo " ===> Check log " $PROCESS_LOG
 echo "***************************************************"
fi  
return $status
}
#------------------------
#functions End
#------------------------
clear 
echo " "
echo "***************************************************"  
echo "Starting Execution"
echo "***************************************************"  
echo " "

 export EXP_IMP_PATH=`pwd`
export EXP_IMP_FOLDER='Export-Import'
export TIMESTAMP=`date +"%Y%m%d_%H%M%S"`
export EXP_IMP_WORKAREA=${EXP_IMP_PATH}/exp_imp_workarea
export PROCESS_LOG=${EXP_IMP_WORKAREA}/import_to_SE_${TIMESTAMP}.log 
export PROCESS_STATUS_FILE=${EXP_IMP_WORKAREA}/import_proc_sts_${TIMESTAMP}.txt 
#-------------------------------------------
#Check current folder
#-------------------------------------------

 echo ${EXP_IMP_PATH}|awk -F "/" '{print $NF}'> ${EXP_IMP_PATH}/last_dir.txt
last_dir=`cat ${EXP_IMP_PATH}/last_dir.txt`
rm -f ${EXP_IMP_PATH}/last_dir.txts
if [[ ${last_dir} != ${EXP_IMP_FOLDER} ]]; then
  echo "***************************************************"  
  echo "Error!!! script must be run from /dbinstall/xxx-xxx-xxx/x.x.x.xx/db/Export-Import Folder"
  echo "Usage: ./Import_to_SE_main.sh"
  echo "***************************************************"  
  exit 1
fi

 ./Import_to_SE_set_params.sh | tee ${PROCESS_LOG}
getStatus;
status=$?
reportStatus $status

 #-----------------------------------
#Delete temp files
#-----------------------------------
rm -f ${PROCESS_STATUS_FILE}
rm -f ${EXP_IMP_WORKAREA}/import_data_param.prm
rm -f ${EXP_IMP_WORKAREA}/import_seq_param.prm

 exit $status

=====================
Import_to_SE_set_params.sh
=====================
#!/bin/bash
#!/bin/bash
#****************************************************************
# Get input parameters, perform checks, and call Import_to_SE.sh
# Must be run from EXP_IMP_PATH=/dbinstall/xxx-xxx-xxx/x.x.x.xx/db/Export-Import;
# Usage: called from Import_to_SE_main.sh
#****************************************************************

 #------------------------
#functions Start
#------------------------
setStatus(){
  echo $1 > ${PROCESS_STATUS_FILE}
}
checkStatus(){
  status=$1
  step=$2
  log=$3
  error_details=$4
  
  if [[ $status -ne 0 ]]; then
    echo " "  
    echo "***************************************************"
    echo " ===> Step "$step" FAILED!!!                       "
    echo " ===> For Details, check log " $log
echo "Process Error Details: "
echo $error_details
    echo "***************************************************"
    echo " "  
exit $status
  fi
}
reportAbortExecution(){
  echo "*****************************************************"
  echo " Abort Upon User Request. Exiting Script"
  echo "*****************************************************"
}
reportEndStep(){
  echo " "
  echo "***************************************************"
  echo " Step " $1 "Finished Successfully.                 "
  echo "***************************************************"
  echo " "  
}
reportBeginStep(){
  echo " "
  echo "***************************************************"
  echo "Starting Step " $1
  echo "***************************************************"
  echo " "  
}

 buildExcludeList(){

 echo "Building Exclude List"
if  [ ! -f ${ImpExcludeListFile} ]; then
  echo "***************************************************"  
  echo "No optional Exclude file detected.                 "
  echo "To Exclude tables from Import, create a file: ${ImpExcludeListFile}"
  echo "***************************************************"  
  echo " "
  return 0
fi  

 # "EXCLUDE=TABLE:\"IN('PLAN_TABLE','SOME_OTHER_TABLE')\"" >> $ImpDataParamFile

 lines=`wc -l ${ImpExcludeListFile} | awk '{print $1}'`
if [[ $lines -eq 0 ]];then
  echo "No Exception Tables list. Empty Exclude List File ${ImpExcludeListFile}"
  echo " "
  return 0
fi

   first_line=true
  exclude_list_str=""
  while read line
  do
    words=`echo $line|awk '{print NF}'`
if [[ $words -ne 1 ]];then
 continue
fi
 
    if [[ $first_line == true ]];then
 exclude_list_str="EXCLUDE=TABLE:\"IN("
 first_line=false
else
 exclude_list_str=${exclude_list_str}","
    fi
    exclude_list_str=${exclude_list_str}"'"${line}"'"
  done < ${ImpExcludeListFile}
  
  if [[ $first_line == false ]];then
    exclude_list_str=${exclude_list_str}")\""
echo "Tables to Exclude: " $exclude_list_str
echo $exclude_list_str>>$ImpDataParamFile
  else
    echo "No Exception Tables list. Emply Exclude List File ${ImpExcludeListFile}"
    echo " "
  fi 
}
#------------------------
#functions End
#------------------------

 clear 
reportBeginStep "Starting Execution"

 export ImpDataLog=impdp_data_${TIMESTAMP}.log
export ImpSeqLog=impdp_seq_${TIMESTAMP}.log
export ImpDataParamFile=${EXP_IMP_WORKAREA}/import_data_param.prm
export ImpSeqParamFile=${EXP_IMP_WORKAREA}/import_seq_param.prm
export ImpExcludeListFile=${EXP_IMP_PATH}/import_exclude_list.txt

 #-------------------------------------------
#Set folder variables
#-------------------------------------------
cd ${EXP_IMP_PATH}
cd ..
export DB_ROOT_PATH=`pwd`
export DB_WORKAREA=${DB_ROOT_PATH}/imp
export DB_FULL_INSTALL=${DB_WORKAREA}/Full_Install
cd ${EXP_IMP_PATH}

 if  [ ! -f ${DB_WORKAREA}/set_user.sql ]; then
  echo "***************************************************"  
  echo "Please create parameters file: " ${DB_SA_WORKAREA}/set_user.sql
  echo "***************************************************"  
  exit 1;
fi  

 if [ ! -d "$EXP_IMP_WORKAREA" ]; then
  mkdir ${EXP_IMP_WORKAREA}
fi

 #-------------------------------------------
#Read Parameters File
#------------------------------------------- 
echo "***************************************************"
echo " Starting Import DataBase Process                  "
echo " This process would clear and repopulate data.     "
echo "***************************************************"
echo " "

 echo "Reading from parameters file " ${DB_WORKAREA}/set_user.sql

 export User=`cat ${DB_WORKAREA}/set_user.sql | grep user | sed s/=/' '/| awk '{print $3}'`
export Pass=`cat ${DB_WORKAREA}/set_user.sql | grep pass | sed s/=/' '/| awk '{print $3}'`
export ConnectStr=`cat ${DB_WORKAREA}/set_user.sql | grep connectstr | sed s/=/' '/| awk '{print $3}'`
export ImpFromUser=`cat ${DB_WORKAREA}/set_user.sql | grep ImpFromUser | sed s/=/' '/| awk '{print $3}'`

 echo "Input Parameters:"
echo "User = " $User
echo "Pass = " $Pass
echo "ConnectStr = " $ConnectStr
echo "ImpFromUser = " $ImpFromUser

 migrate_schema=true;
if [[ -z $ImpFromUser ]];then
migrate_schema=false;
else
if [[ $ImpFromUser == $User ]];then
 migrate_schema=false;
fi
fi
export migrate_schema
   
#ExpDmpFile should be located in IG_EXP_DIR folder
#ExpDmpFile name is by default: Exp_${User}.dmp
#To override default name, set parameter ExpDmpFile in set_user.sql

 export ExpDmpFile=`cat ${DB_SA_WORKAREA}/set_user.sql | grep ExpDmpFile | sed s/=/' '/| awk '{print $3}'`
if [[ -z $ExpDmpFile ]];then
  echo " "
  echo "Optional parameter ExpDmpFile not defined in file set_user.sql. Using default name"
  export ExpDmpFile=Exp_${User}.dmp 
fi

 echo "Import will be done from file: " $ExpDmpFile
echo " "

 #--------------------------------
#set import data parameters file
#--------------------------------
rm -f $ImpDataParamFile
touch  $ImpDataParamFile
  
echo "DIRECTORY=IG_EXP_DIR" >> $ImpDataParamFile
echo "DUMPFILE="${ExpDmpFile} >> $ImpDataParamFile
echo "LOGFILE="${ImpDataLog} >> $ImpDataParamFile
echo "CONTENT=data_only" >> $ImpDataParamFile
echo "TABLE_EXISTS_ACTION=truncate" >> $ImpDataParamFile

 #To get a list of to be created Objects
#echo "SQLFILE"="impdb_sql_file.sql" >> $ImpDataParamFile

 buildExcludeList
if [[ $migrate_schema == true ]];then  
  echo "REMAP_SCHEMA="${ImpFromUser}:${User} >> $ImpDataParamFile
fi 

 #-------------------------------------
#set import sequences parameters file
#-------------------------------------
rm -f $ImpSeqParamFile
touch  $ImpSeqParamFile
  
echo "DIRECTORY=IMP_EXP_DIR" >> $ImpSeqParamFile
echo "DUMPFILE="${ExpDmpFile} >> $ImpSeqParamFile
echo "LOGFILE="${ImpSeqLog} >> $ImpSeqParamFile
echo "INCLUDE=sequence" >> $ImpSeqParamFile

 if [[ $migrate_schema == true ]];then
  echo "REMAP_SCHEMA="${ImpFromUser}:${User} >> $ImpSeqParamFile
fi 

 #-----------------------------------
# Validate Parameters 
#-----------------------------------
if [[ $migrate_schema == true ]];then
   echo "You are about to import data from schema: " $ImpFromUser " to schema: " $User " On Instance: " $ConnectStr 
   export SysUser=system
   echo "*****************************************************************"
   echo -n " Please enter password for ORACLE system user: "; read SysPass
   export SysPass
   echo "*****************************************************************"
else
   echo "You are about to import data from schema: " $User " to schema: " $User " On Instance: " $ConnectStr 
fi

 echo " "
echo " "
echo "Import Process Parameters:"
cat $ImpDataParamFile
echo " "

 #echo "Import Sequences Parameters:"
#cat $ImpSeqParamFile
#echo " "

 reportBeginStep " Please review parameters, and confirm."

 echo -n " ARE YOU SURE YOU WANT TO CONTINUE ? [Y,N] "; read TERM
if [[ "$TERM" == y || "$TERM" == Y || "$TERM" == Yes || "$TERM" == yes ]]; then
    setStatus 0
else
    reportAbortExecution
    setStatus 1
    exit 1
fi

 echo " "
echo " "
#-----------------------------------
#Start Actual Execution
#-----------------------------------

 #-----------------------------------
#Set QUERY_USER
#-----------------------------------
if [[ $migrate_schema == true ]];then
   export QUERY_USER=$ImpFromUser
   export SysUser=system
   echo -n " Please enter password for ORACLE"  $ImpFromUser " user: "; read QUERY_PSWD
   export QUERY_PSWD
else
  export QUERY_USER=$User
  export QUERY_PSWD=$Pass
fi 
#-----------------------------------
#Check if user got open sessions
#-----------------------------------

 reportBeginStep "Check User $User Exists"
if [[ $migrate_schema == true ]];then
  sql_result=$(./Call_Sql_script.sh -u $SysUser -p $SysPass -s $ConnectStr -q Check_User_Exists.sql -a $User )
else
  sql_result=$(./Call_Sql_script.sh -u $User -p $Pass -s $ConnectStr -q Check_User_Exists.sql -a $User )
fi

 status=$?

 if [[ $status -eq 0 ]]; then  
  if [[ $sql_result -eq 1 ]]; then  
    user_exists_ind=1
  else
    user_exists_ind=0
  fi
else
  echo "===> ERROR From sqlplus: $sql_output"
  invalid_login_ind=$(echo $sql_result | grep "ORA-01017: invalid username" | wc -l)
  
  if [[ $invalid_login_ind -eq 0 ]];then
user_exists_ind=0
  else
status=0
  fi
fi

 if [[ $user_exists_ind -eq 0 ]];then
  echo "User $User does not exist."
fi

 setStatus $status
checkStatus $status "Check User Exists" $PROCESS_LOG "$user_exists_ind"
reportEndStep "Check User Exists"

 if [[ $user_exists_ind -eq 1 ]];then 
  reportBeginStep "Check User Open Connections"
  sql_result=$(./Call_Sql_script.sh -u $QUERY_USER -p $QUERY_PSWD -s $ConnectStr -q Check_User_Connections.sql -a $User )
  status=$?
  setStatus $status
  checkStatus $status "Check User Open Connections" $PROCESS_LOG "$sql_output"
  
  if [[ "x$sql_output" != "x" ]]; then 
    echo "User ${User} has Open Sessions. Close sessions listed below, before running the script!!!"
    for record in $sql_output
    do
 echo " "
      echo $record | awk 'BEGIN { FS = "~" } ; { print "Process: " $7 " running on server: " $4 " OS user: " $6 " .\nProcess Details: V_PROCESS.SPID: " $1" V_SESSION.SID " $2 " V_SESSION.SERIAL# " $3 }'
 echo $record | awk 'BEGIN { FS = "~" } ; { print "The command: ALTER SYSTEM KILL SESSION '\''" $2 "," $3 "'\'';"}'
    done
status=555
setStatus $status
checkStatus $status "Check User Open Connections" $PROCESS_LOG $sql_output
  else
    echo "No Open Sessions for user: ${User}"
  fi
  reportEndStep "Check User Open Connections"
fi
#--------------------------------------------------
#Compare x.x.x.xx path from /dbinstall/xxx-xxx-xxx/x.x.x.xx/db/Export-Import to DB_INSTALLED_VERSIONS
#--------------------------------------------------
reportBeginStep "Check Product Version"

 if [[ $user_exists_ind -ne 1 ]];then 
  echo "User $User does not exist. Skipping Check Product Version Step."
else
    echo "Call_Sql_script.sh -u ${QUERY_USER} -p ${QUERY_PSWD} -s ${ConnectStr} -q Check_Product_Version.sql"
db_product_version=$(./Call_Sql_script.sh -u ${QUERY_USER} -p ${QUERY_PSWD} -s ${ConnectStr} -q Check_Product_Version.sql )
status=$?
setStatus $status
checkStatus $status "Check Product Version" $PROCESS_LOG "$db_product_version"
os_product_version=$(echo $EXP_IMP_PATH | sed -e 's/Export-Import//' -e 's/\/db\/$//' -e 's/\// /g'  | awk '{ print $NF }')

 if [[ $os_product_version != $db_product_version ]];then
 echo "WARNING!!!! Product version in DB is not same as Product version on Server"
 echo "DB Product Version: $db_product_version  ; Server Product Version: $os_product_version"
 echo "Continue at YOUR OWN RISK!!!"
 echo " "
 echo -n "Continue?......[Y,N] "; read TERM
 if [[ "$TERM" == y || "$TERM" == Y || "$TERM" == Yes || "$TERM" == yes ]]; then
setStatus 0
 else
 reportAbortExecution
setStatus 1
exit 1
fi  
fi
fi
reportEndStep "Check Product Version"
#--------------------------------------------------
#Get the IMP_EXP_DIR Directory value
#--------------------------------------------------
reportBeginStep "Get Export Directory"  
#export db_exp_dir=$(./Get_Export_Directory.sh)
export db_exp_dir=$(./Call_Sql_script.sh -u ${QUERY_USER} -p ${QUERY_PSWD} -s ${ConnectStr} -q Get_Export_Directory.sql )
status=$?
setStatus $status
echo "Import done from directory: " $db_exp_dir
checkStatus $status "Get Export Directory" $PROCESS_LOG "$db_exp_dir"
reportEndStep "Get Export Directory"

 #-----------------------------------
#perform the import to DB
#-----------------------------------
step_message="Import Schema"
reportBeginStep "$step_message"
./Import_to_SE.sh 
status=$?

 if [[ $status -ne 0 ]];then 
  echo " "
  echo "IMPORTANT!!! - Manually Clean up SYS_IMPORT_FULL_XX Segments"
  echo "Step 1 - sqlplus system/********@{ConnectStr}"
  echo "Step 2 - SELECT 'DROP TABLE '||SEGMENT_NAME||';' FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE '%SYS_IMPORT_FULL_%'"
  echo "Step 3 - Execute the generated DROP TABLE Statements"
  echo " "  
fi
setStatus $status
checkStatus $status "Import Schema" $PROCESS_LOG
reportEndStep "$step_message"

 exit $status

=====================
Import_to_SE.sh
=====================
#!/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;

=====================
call_sql_script.sh
=====================
#!/bin/bash

#echo "*****************************************************************"
#echo " Generic execute SQL statement script
#echo "*****************************************************************"

while getopts u:p:s:q:a:b:c:d:e:f:g:h:j:k option
do
        case "${option}"
        in
                u) user=${OPTARG};;
                p) paswd=${OPTARG};;
                s) connect_str=${OPTARG};;
                q) sql_file=${OPTARG};;
a) prm1=${OPTARG};;
b) prm2=${OPTARG};;
c) prm3=${OPTARG};;
d) prm4=${OPTARG};;
e) prm5=${OPTARG};;
f) prm6=${OPTARG};;
g) prm7=${OPTARG};;
h) prm8=${OPTARG};;
j) prm9=${OPTARG};;
k) prm10=${OPTARG};;
        esac
done
#echo "sqlplus -S" ${user}/${paswd}@${connect_str} $prm1 $prm2 $prm3 $prm4 $prm5 $prm6 $prm7 $prm8 $prm9 $prm10 

sql_output=`sqlplus -S ${user}/${paswd}@${connect_str} <<EOF
  @${sql_file} $prm1 $prm2 $prm3 $prm4 $prm5 $prm6 $prm7 $prm8 $prm9 $prm10 
EOF
`
status=$?
if [[ $status -ne 0 ]]; then
  echo " ===> ERROR From sqlplus: $sql_output"
  exit $status
fi

for record in "$sql_output"
do
  echo "$record" 
done

exit 0
=====================
check_product_version.sql
=====================
set pages 0  
set verify off 
set feedback off


SELECT NVL(MAX(version_id||'.'||upgrade_id),'NULL')  
  FROM DB_VERSION;

EXIT; 

=====================
get_export_directory.sql
=====================
set pages 0  
set verify off 
set feedback off

SELECT directory_path 
FROM dba_directories
WHERE directory_name = 'IMP_EXP_DIR';

EXIT; 

 =====================
check_user_connections.sql
=====================
set pages 0 
set verify off 
set feedback off
set linesize 200
COL PID FOR 9999999999
COL SID FOR 9999999999
COL SER# FOR 9999999999
COL SERVER FOR A20
COL USERNAME FOR A30
COL OS_USER FOR A30
COL PROGRAM FOR A60

 SELECT REPLACE(v_process.spid ||'~'||
       v_session.sid ||'~'||
       v_session.serial# ||'~'||
       v_session.machine ||'~'||
       v_session.username ||'~'||
       v_session.osuser ||'~'||
       v_session.program,' ','_') AS user_sessions
from v$session v_session, v$process v_process
where v_session.paddr = v_process.addr
  and v_session.type='USER'
  and  v_session.username = UPPER('&&1')
  AND v_session.SID <>( SELECT SID 
                          FROM v$session 
                         WHERE audsid=sys_context('userenv','SESSIONID'));  
EXIT;

=====================
check_user_exists.sql
=====================
set pages 0 
set verify off 
set feedback off

SELECT count(1) from ALL_USERS 
 WHERE USERNAME = UPPER('&&1');
EXIT;
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)