Thursday, December 31, 2020

Oracle Data Pump Import Shell Script

For how long you will keep googling each time you try to import data into an Oracle database?
In fact, I was doing the same too, because it's not easy to deal with a tool such as impdp, but the good news, is that a new script is now ready to make your life easier with import data tasks.

I know you are super busy! But before I take you through the process, please read the following disclaimer carefully:

THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS". I WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT.

In other words: You have to test this script "yourself" carefully on a test environment to get familiar with it before using it on production. I'm not intimidating you here. Yes, the script is handy and easy to use, but the common rule of thumb in IT world, is that nothing should go in production unless it's well tested.

Download the script:
https://www.dropbox.com/s/5qmfaeos9w4tj63/import_data.sh?dl=0


This script is also part of the "DBA Bundle", if you didn't hear about "DBA Bundle" term before then I recommend you to get out of the rock you are living under and explore it: 😀
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html

In the same bundle there is a script can help you with exporting data as well:
http://dba-tips.blogspot.com/2020/02/new-script-for-exporting-data.html

Now, let's go through a brief demo:
I will demonstrate only the Full import mode, the rest of import modes (schema & table) are similar. 

First, you will be asked to accept the Agreement: [Too much paper work I know 👀]

Next, you will be requested to provide the Complete path of the directory that hosts the export dump file/files, the script can accept shortcuts like "." and "~": [Do not include the dump file name along with the path, file name will be provided next] 👇

Next, provide the dump file name, if it's more than one file, just separate them by "," or if they are multiple files in sequence just enter it one time replacing the sequence number with "*" dump*.dmp, in the below screenshot I've two dump files (EXPORT_FULL_DB_configdb_30-Dec-2020_01.dmp and EXPORT_FULL_DB_configdb_30-Dec-2020_02.dmp) instead of providing them by comma "which is allowed" I just replaced the sequence number at the end of file name with *:👇

 If you have more than one active DB on the same machine, the script will ask you to provide the DB "number" you want to run the import against:👇

Next, provide your Email if you want to receive an Email notification after the completion of the import job, or leave it blank:👇

Next, provide the import mode:👇

Next, if you want to import Specific schemas, then provide them in this section separating schema names by comma "," otherwise leave it blank to import all the schemas available in the export dump file:👇

Next, In case you want to change the name of the imported schema to something else other than source, then provide it in this section following the right format, otherwise leave it blank:👇

for example, if you want to change the name of two schemas from HR to HR_NEW and from SCOTT to SCOTT_NEW then enter it like this: HR:HR_NEW, SCOTT:SCOTT_NEW



Next, Same thing you can do with renaming tables, but when you remap a table make sure to qualify it with its SCHEMA_OWNER in the source DB: 👇

for example, if you want to change the name of two tables from EMPLOYEES to EMPLOYEES_NEW and from DEPARTMENTS to DEPARTMENTS_NEW and both are owned by HR (on the source DB) then specify the remap as this: (Do NOT specify the owner for the target table)
HR.EMPLOYEES:EMPLOYEES_NEW, HR.DEPARTMENTS:DEPARTMENTS_NEW

 

Next, if the schemas in the target DB are having different default TABLESPACE names than their counterparties in the source DB, then you have to remap the tablespace names to reflect the already exist tablespace names in the target DB. If the tablespace names are same between source & target DBs then leave it blank: 👇

 Next, you have to decide what shall the import do if it finds tables already exist on Target DB: 👇

1. SKIP leave the already exist tables' structure and data intact and ignore them.
2. APPEND Append the imported data to the already exist tables and leave the current exist data intact.
3. REPLACE Drop and recreate the already exist table and import the new data which will delete the current exist data.
4. TRUNCATE Delete the current data on the already exist tables and import the new data


If you choose to REPLACE or TRUNCATE then you will get this cute warning message:👇

 Next, specify the data import mode Data & DDL, DDL ONLY or Data only:👇

The Next INCLUDE option will allow you to import ONLY objects types or wild carded tables/indexes like TABLES, INDEXES,... or even if you have a special requirement like:👇

for example: Import all tables starting with keyword "PROD", you can specify it in the input like this: TABLE:"LIKE 'PROD%'"

Please note that if you specify anything in this section this means you are telling the script to (import only the following object types, or wild carded object names and DO NOT IMPORT ANYTHING ELSE). If you leave this section blank this means to import all objects under the schemas/tables you already specified in earlier sections.

Next, If you leave the above section (INCLUDE) section blank, then the next option will ask you to exclude object types or wild carded tables/indexes from this import: [INCLUDE & EXCLUDE are mutually exclusive, they cannot be used together]

For example: to exclude all CONSTRAINTS (except those are mandatory for successful data import) simply input: CONSTRAINT

But in my demonstration, I wanted to exclude all table starting with the name "SYS_EXPORT_FULL*" from this import job, so I've done it like this in the screenshot below:👇

Next, You will be asked if you want to import Object Privileges: [Default is YES] 👇

Note: If the INCLUDE option was already used above, then this option will not appear.

Next, You will be asked if you want to import Statistics: [Default is YES] 👇

Note: If the INCLUDE option was already used above, then this option will not appear.

Next, If your database edition supports Parallelism, you will be asked to provide the PARALLEL DEGREE if you want to run the import process in parallel:👇

Note: On the right side it will display the current CPU core count on the system to help you decide the right degree of parallelism to use, but if you don't want to use Parallelism then leave it blank.

In below screenshot, I've set the degree of parallelism to 2 to match my CPU count, if you want a deep dive on the best practice of setting Parallel degree then read this post.

Next, if you are running the import on a RAC database, you will be offered to balance the import load on all RAC nodes:👇

Note: In order to make this feature works, the dump file location should be shared between all RAC nodes, otherwise don't enable this feature, otherwise the import will fail!

Next, if the database version is 12c and higher, you will be offered to execute the import in NOLOGGING mode [TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y] where REDO data generation will be at the minimum:👇

Note: Only import operation will run in NOLOGGING not the whole database!

Next, if the database is in FORCE LOGGING mode you will be offered to disable it temporarily during the import operation.👇

Note: After the completion of the import operation, the script will automatically ENABLE the FORCE LOGGING mode.

Warning: It's not recommended to disable FORCE LOGGING mode if the database is data guarded by a Standby DB!

Next, If DB_BLOCK_CHECKING parameter is set to TRUE, you will be prompted to disable it temporarily during the import operation. This is crucial to speed up the import process.👇

Note: After the completion of the import operation, the script will automatically set the DB_BLOCK_CHECKING parameter to its original setting.

Next, If DB_BLOCK_CHECKSUM parameter is set to TRUE, you will be prompted to disable it temporarily during the import operation. This is crucial to speed up the import process.👇

Note: After the completion of the import operation, the script will automatically set the DB_BLOCK_CHECKSUM parameter to its original setting.

Next, you reach to the final stage, where you will confirm the import settings before the start of the data pump import:👇

Then provide your confirmation if you want to proceed:👇

Done.

You can make use of the following shown guidelines anytime during the import operation to have control over it while it's running, i.e. you can change the parallel degree of the import job or kill it:👇


The import operation will run in the background where you can disconnect from the current session anytime, also the log records will be displayed if you keep connecting to the same session.

Note: If you want to kill the import operation, kill it from impdp console as shown above, not by killing the import_data.sh script, in case you mistakenly killed import_data.sh script, then revert the database setting manually by executing all the lines below the following line in IMPORTSCRIPT.sh script.

Let me know your feedback.

More Reading: Speed up Data Pump import in 19c


GitHub Version:
# #########################################################################################################################################
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
# THE AUTHOR WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT.
#
# Ver: 1.3
# This script help with importing data using impdp.
#
# # # #
# Author: Mahmmoud ADEL # # # # ###
# # # # # #
# Created: 22-12-2020
# Modified: 31-12-2020 Allow turning OFF the FLASHBACK mode during the import.
# 31-12-2020 Delay the drop of the importer user DBA_BUNDLE7 for 5 sec as some slow workers may not yet cleaned up.
# 27-05-2022 Add the RESUMABLE option, for impdp it can be only set system wide, unlike imp which have the option built-in.
# 01-09-2022 Forcefully drop the exporter user DBA_BUNDLEEXP7
#
#
# #########################################################################################################################################
# ###########
# Description:
# ###########
export SRV_NAME="`uname -n`"
echo
echo "=================================================================="
echo "THIS SCRIPT HELP WITH IMPORTING DATA ON DATABASE | SCHEMA | TABLE."
echo "=================================================================="
echo
sleep 1
echo ""
echo "DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED \"AS IS\"."
echo "THE AUTHOR WILL NOT BE HELD RESPONSIBLE TO ANY PERSON OR ENTITY WITH RESPECT TO ANY DATA LOSS OR DAMAGES CAUSED BY THIS SCRIPT."
echo ""
echo "Do you agree to continue? [YES | NO]"
echo "========================="
while read DISCLAIMER
do
case ${DISCLAIMER} in
Y|y|yes|YES|Yes) echo; break;;
N|n|no|NO|No|NE) echo; echo "Nothing wrong with being on the safe side :-)";echo "Please test this script on a test environment first to be more confident with it.";echo "SCRIPT TERMINATED! "; echo; exit;;
*) echo "Please Enter a valid answer: [YES|NO]";;
esac
done
echo "Provide the DIRECTORY FULL LOCATION PATH where the export file located: [e.g. /backup/export]"
echo "======================================================================="
while read DUMPDIRPATH
do
case ${DUMPDIRPATH} in
'') export DUMPDIRPATH=`pwd`; echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
'.') export DUMPDIRPATH=`pwd`; echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
'~') export DUMPDIRPATH=${HOME}; echo "DIRECTORY TRANSLATED TO: ${DUMPDIRPATH}";;
esac
if [[ -d "${DUMPDIRPATH}" ]] && [[ -r "${DUMPDIRPATH}" ]] && [[ -w "${DUMPDIRPATH}" ]]
then
export DUMPDIR="${DUMPDIRPATH}"; break
else
echo; printf "`echo "Please make sure that oracle user has"` `echo -e "\033[33;5mREAD/WRITE\033[0m"` `echo "permissions on the provided directory."`\n"; echo; echo "Enter the complete PATH where the dump file is located: [e.g. /backup/export]"
fi
done
echo ""
echo "Provide the EXPORT FILE name: [The dump file name to import from]"
echo "============================="
echo "For multiple dump files separate them by comma: e.g. dump01.dmp,dump02.dmp"
echo "For multiple dump files in sequence, replace the sequence number with *: e.g. dump*.dmp"
echo
while read DUMPFILE
do
case ${DUMPFILE} in
"") echo "You MISSED to Enter the export file name!"; echo; echo "Provide the EXPORT FILE name: [e.g. dump01.dmp]";;
*/*) echo; echo "Enter the FILE NAME only NOT the full path!"; echo; echo "Provide the EXPORT FILE name: [e.g. dump01.dmp]";;
*) export DUMPFILENAME="$(sed s/*/%U/g <<<${DUMPFILE})"; echo; echo "File Name is: ${DUMPFILE}"; break;;
esac
done
#if [ -f ${DUMPFILE} ]; then
#export VALID_DUMP=`strings {${DUMPFILEPATH}} | head -1 | grep 'EXPORT' | wc -l`
#export LEGACY_MODE=`strings {${DUMPFILEPATH}} | head -1 | grep 'EXPORT:' | wc -l`
#export TABLE_EXPORT_MODE=`strings {${DUMPFILEPATH}} | head -1 | grep TABLE | wc -l`
#export DUMPDIR="${DUMPFILEPATH%/*}"
#export DUMPFILENAME="${DUMPFILEPATH##*/}"
# break;;
#else
# echo "File not found or not accessible!"
# echo
# echo "Please Enter a valid path, or make sure it's readable by Oracle user:"
# echo "====================================================================="
# ;;
#fi
# Warning if the file is not a valid Import file:
# if [ ${VALID_DUMP} -ne 1 ]
# then
# echo
# echo "WARNING: The provided file doesn't sound like a valid EXPORT file! "
# echo
# fi
# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:
EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
# ###########################
# Listing Available Databases:
# ###########################
# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo "No Database is Running !"
echo
return
fi
# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo "Select the ORACLE_SID:[Enter the number]"
echo "---------------------"
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
do
integ='^[0-9]+$'
if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ] || [ ${REPLY} -eq 0 ]
then
echo
echo "Error: Not a valid number!"
echo
echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
echo "----------------------------------------------"
else
export ORACLE_SID=$DB_ID
echo
printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
echo
break
fi
done
fi
# Exit if the user selected a Non Listed Number:
if [ -z "${ORACLE_SID}" ]
then
echo "You've Entered An INVALID ORACLE_SID"
exit
fi
# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|grep -v "\-MGMTDB"|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep -i "^${ORA_USER}:" /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
export PGREP=`which pgrep`
export PWDX=`which pwdx`
if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
then
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID} 2>/dev/null|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
fi
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
fi
# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`dbhome "${ORACLE_SID}"`
export ORACLE_HOME
fi
# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
fi
# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
fi
# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
if [ -x /usr/bin/locate ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
fi
fi
# TERMINATE: If all above attempts failed to get ORACLE_HOME location, EXIT the script:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
echo "Please export ORACLE_HOME variable in your .bash_profile file under oracle user home directory in order to get this script to run properly"
echo "e.g."
echo "export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1"
exit
fi
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo ""
echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
echo "Script Terminated! "
exit
fi
# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user's profile if not set:
if [ -z "${ORACLE_BASE}" ]
then
ORACLE_BASE=`grep -h 'ORACLE_BASE=\/' ${USR_ORA_HOME}/.bash* ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_BASE
fi
# ###########################
# INSTANCE OPEN MODE CHECKING:
# ###########################
INSTANCE_STATUS_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off;
SELECT STATUS FROM V\$INSTANCE;
EOF
)
INSTANCE_STATUS=`echo ${INSTANCE_STATUS_RAW}| awk '{print $NF}'`
case ${INSTANCE_STATUS} in
"OPEN") echo ;;
*) echo;echo "ERROR: INSTANCE [${ORACLE_SID}] IS IN STATUS: ${INSTANCE_STATUS} !"
echo;echo "PLEASE FIRST OPEN INSTANCE [${ORACLE_SID}] THEN RE-RUN THIS SCRIPT.";echo; exit ;;
esac
# ###################
# Checking DB Version:
# ###################
DB_VER_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select version from v\$instance;
exit;
EOF
)
DB_VER=`echo ${DB_VER_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
DB_RELEASE=`echo ${DB_VER_RAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f2 -d '.'`
# ###########################
# Checking FORCE LOGGING mode:
# ###########################
FORCE_LOGGING_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
select force_logging from v\$database;
EOF
)
FORCE_LOGGING=`echo ${FORCE_LOGGING_RAW}| awk '{print $NF}'`
case ${FORCE_LOGGING} in
YES) export FORCE_LOGGING=1; echo; echo -e "\033[33;5mFORCE LOGGING MODE IS ENABLED!\033[0m"; echo;;
*) export FORCE_LOGGING=0;;
esac
# ############################
# Checking PARALLELISM Feature:
# ############################
CHK_PARALLELISM_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from v\$option where parameter='Parallel execution' and value='TRUE';
exit;
EOF
)
CHK_PARALLELISM_OPTION=`echo ${CHK_PARALLELISM_OPTION_RAW} | awk '{print $NF}'`
if [ "${CHK_PARALLELISM_OPTION}" -eq 1 ]
then
export PARALLELISM=1
else
export PARALLELISM=0
fi
# ####################
# Checking RAC Feature:
# ####################
CHK_RAC_ACTIVE_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select count(*) from gv\$instance;
EOF
)
CHK_RAC_ACTIVE=`echo ${CHK_RAC_ACTIVE_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
if [ "${CHK_RAC_ACTIVE}" -gt 1 ]
then
export RAC_ACTIVE=1
else
export RAC_ACTIVE=0
fi
# ###########################
# Checking FLASHBACK Feature:
# ###########################
CHK_FLASHBACK_ACTIVE_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select count(*) from v\$database where FLASHBACK_ON='YES';
EOF
)
CHK_FLASHBACK_ACTIVE=`echo ${CHK_FLASHBACK_ACTIVE_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
if [ "${CHK_FLASHBACK_ACTIVE}" -eq 1 ]
then
export FLASHBACK_ACTIVE=1
else
export FLASHBACK_ACTIVE=0
fi
# ##########################################
# db_block_checking parameter value:
# ##########################################
DB_BLOCK_CHECKING_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='db_block_checking';
EOF
)
DB_BLOCK_CHECKING_VAL=`echo ${DB_BLOCK_CHECKING_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
# ##########################################
# db_block_checksum parameter value:
# ##########################################
DB_BLOCK_CHECKSUM_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='db_block_checksum';
EOF
)
DB_BLOCK_CHECKSUM_VAL=`echo ${DB_BLOCK_CHECKSUM_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
# ##########################################
# resumable_timeout parameter value:
# ##########################################
RESUMABLE_TIMEOUT_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set heading off echo off feedback off termout off
select value from v\$parameter where name='resumable_timeout';
EOF
)
RESUMABLE_TIMEOUT_VAL=`echo ${RESUMABLE_TIMEOUT_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
# #########################################################
# Check the existance of the export DB user DBA_BUNDLEEXP7:
# #########################################################
USER_OBJECTS_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off;
SELECT COUNT(*) FROM DBA_OBJECTS WHERE OWNER='DBA_BUNDLEEXP7';
EOF
)
USER_OBJECTS_COUNT=`echo ${USER_OBJECTS_COUNT_RAW}| awk '{print $NF}'`
if [ ${USER_OBJECTS_COUNT} -gt 0 ]
then
echo
printf "`echo "The Exporter User [DBA_BUNDLEEXP7] is already EXIST in the database and has [${USER_OBJECTS_COUNT}] objects and "` `echo -e "\033[33;5mwill be DROPPED\033[0m"` `echo " by this script."`\n"
echo
fi
# ##########################################
# _dlm_stats_collect parameter value:
# ##########################################
#STATS_COLLECT_VAL_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
#set heading off echo off feedback off termout off
#select y.ksppstvl from x\$ksppi x, x\$ksppcv y where x.indx = y.indx and x.ksppinm like '\_%' escape '\' and x.ksppinm='_dlm_stats_collect';
#EOF
#)
#STATS_COLLECT_VAL=`echo ${STATS_COLLECT_VAL_RAW} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
# #########################
# IMPORT Section:
# #########################
# VARIABLES:
# #########
# Date Stamp:
DUMPDATE=`date +%d-%b-%Y`
DUMPDATEFULL=`date +'%d-%b-%Y %T'`
#PASSHALF=`echo $((RANDOM % 999+7000))`
PASSHALF=`date '+%s'`
# ##############################
# Prompt for EMAIL Confirmation:
# ##############################
echo "Enter your EMAIL to receive a notification upon the completion of the Import job: [Leave it BLANK or Enter N to Skip the notification]"
echo "================================================================================"
while read EMAILANS
do
case ${EMAILANS} in
""|"N"|"n"|"NO"|"No"|"no")export EMAILANS=""; export SENDEMAIL=""; echo; break;;
*@*.*) export SENDEMAIL="mail -s \"\${JOBSTATUS} on Server ${SRV_NAME}\" \${EMAILID} < \${LOGFILE}"; echo; break;;
*)echo ""
echo -e "\033[32;5mThis doesn't sound like a valid Email? ${EMAILANS}\033[0m"
echo ""
echo "Please Enter your Email: [Leave it BLANK or Enter N to Skip this!]"
echo "------------------------"
echo "i.e. john.smith@xyzcompany.com"
echo "";;
esac
done
# ######################
# IMPORT MODES:
# ######################
#if [ "${LEGACY_MODE}" -eq 1 ]
#then
# ######################
# LEGACY IMPORT MODE:
# ######################
#else
# ######################
# DATA PUMP IMPORT MODE:
# ######################
# Specify the IMPORT MODE:
echo "What do you want to IMPORT?"
echo "==========================="
echo "1. FULL_DATABASE"
echo "2. SCHEMAS"
echo "3. TABLES"
while read IMPORTMODEVAR
do
case ${IMPORTMODEVAR} in
1|FULL|full|FULL_DATABASE|DB|db) echo; echo "Entering FULL import mode ..."; echo; export TABLE_IMPORT_MODE=0; export IMPORT_MODE="FULL"; break;;
2|SCHEMAS|SCHEMA|schema|schemas) echo; echo "Entering SCHEMA import mode ..."; echo; export TABLE_IMPORT_MODE=0; export IMPORT_MODE="SCHEMA"; break;;
3) echo; echo "Entering TABLE import mode ..."; echo; export TABLE_IMPORT_MODE=1; export IMPORT_MODE="TABLE"; break;;
*) echo "Please Enter a number [1, 2, 3]:";;
esac
done
# PARFILE Setup:
if [ -w ${DUMPDIR} ]
then
export STGDIR=${DUMPDIR}
else
export STGDIR=~
fi
PARFILE_NAME=import_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.par
export PARFILE=${STGDIR}/${PARFILE_NAME}
echo "# This PAR file is for ${IMPORT_MODE} IMPORT task that ran against [${ORACLE_SID}] On [${DUMPDATEFULL}]:" > ${PARFILE}
echo "directory=EXPORT_FILES_DBA_BUNDLE" >> ${PARFILE}
echo "dumpfile=${DUMPFILENAME}" >> ${PARFILE}
echo "logfile=import_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.log" >> ${PARFILE}
if [ "${TABLE_IMPORT_MODE}" -eq 1 ]
then
# ENTER TABLE IMPORT MODE:
echo "Enter the TABLE NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import ALL TABLES in the Export file]"
echo "========================================"
echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
echo ""
while read LISTOFTABLESVAR
do
case ${LISTOFTABLESVAR} in
"") echo; export LISTOFTABLES=""; break ;;
*) echo; export LISTOFTABLES="TABLES=${LISTOFTABLESVAR}"; echo ${LISTOFTABLES} >> ${PARFILE}; break ;;
esac
done
if [ -z ${LISTOFTABLESVAR} ]
then
echo "Enter the TABLE NAMES you want to EXCLUDE from this import job: [Leave it BLANK and hit ENTER to import ALL TABLES]"
echo "=============================================================="
echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
echo ""
while read EXCLUDETABLEVAR
do
case ${EXCLUDETABLEVAR} in
"") echo; export EXCLUDETABLE=""; break ;;
*) echo; export EXCLUDETABLE="EXCLUDE=TABLE:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDETABLEVAR}| tr '[:lower:]' '[:upper:]')')\""
echo ${EXCLUDETABLE} >> ${PARFILE}; break ;;
esac
done
fi
else
echo "Enter the SCHEMA NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import FULL DATABASE or ALL SCHEMAS in the Export file]"
echo "========================================="
echo "i.e. SCOTT,HR"
echo ""
while read LISTOFSCHEMASVAR
do
case ${LISTOFSCHEMASVAR} in
"") echo; export LISTOFSCHEMAS=""; break ;;
*) echo; export LISTOFSCHEMAS="SCHEMAS=${LISTOFSCHEMASVAR}"; echo ${LISTOFSCHEMAS} >> ${PARFILE}; break ;;
esac
done
if [ -z ${LISTOFSCHEMASVAR} ]
then
echo "Enter the SCHEMA NAMES you want to EXCLUDE from this import job: [Leave it BLANK and hit ENTER to import ALL SCHEMAS]"
echo "==============================================================="
echo "i.e. ANONYMOUS,APPQOSSYS,AUDSYS,BI,CTXSYS,DBSNMP,DIP,DMSYS,DVF,DVSYS,EXDSYS,EXFSYS,GSMADMIN_INTERNAL,GSMCATUSER,GSMUSER,LBACSYS,MDSYS,MGMT_VIEW,MDDATA,MTSSYS,ODM,ODM_MTR,OJVMSYS,OLAPSYS,ORACLE_OCM,ORDDATA,ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SPATIAL_CSW_ADMIN,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN,SPATIAL_WFS_ADMIN_USR,SYS,SYSBACKUP,SYSDG,SYSKM,SYSMAN,SYSTEM,TSMSYS,WKPROXY,WKSYS,WK_TEST,WMSYS,XDB,XTISYS,DSSYS,PERFSTAT,REPADMIN,OEM_ADVISOR,OEM_MONITOR,OLAP_DBA,OLAP_USER,OWBSYS,OWBSYS_AUDIT,APEX_030200"
echo ""
while read EXCLUDESCHEMAVAR
do
case ${EXCLUDESCHEMAVAR} in
"") echo; export EXCLUDESCHEMA=""; break ;;
*) echo; export EXCLUDESCHEMA="EXCLUDE=SCHEMA:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDESCHEMAVAR}| tr '[:lower:]' '[:upper:]')')\""
echo ${EXCLUDESCHEMA} >> ${PARFILE}; break ;;
esac
done
fi
# Enter TABLE MODE if the user specifies no SCHEMA:
if [ -z ${LISTOFSCHEMASVAR} ] && [ -z ${EXCLUDESCHEMAVAR} ]
then
echo "Enter the TABLE NAMES you want to IMPORT: [Leave it BLANK and hit ENTER to import ALL TABLES in the Export file]"
echo "========================================"
echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
echo ""
while read LISTOFTABLESVAR
do
case ${LISTOFTABLESVAR} in
"") echo; export LISTOFTABLES=""; break ;;
*) echo; export LISTOFTABLES="TABLES=${LISTOFTABLESVAR}"; echo ${LISTOFTABLES} >> ${PARFILE}; break ;;
esac
done
if [ -z ${LISTOFTABLESVAR} ]
then
echo "Enter the TABLE NAMES you want to EXCLUDE from this import: [Leave it BLANK and hit ENTER to import ALL TABLES]"
echo "=========================================================="
echo "i.e. SCOTT.EMP,HR.DEPARTMENTS"
echo ""
while read EXCLUDETABLEVAR
do
case ${EXCLUDETABLEVAR} in
"") echo; export EXCLUDETABLE=""; break ;;
*) echo; export EXCLUDETABLE="EXCLUDE=TABLE:\"IN('$(sed s/,/\',\'/g <<<${EXCLUDETABLEVAR}| tr '[:lower:]' '[:upper:]')')\""
echo ${EXCLUDETABLE} >> ${PARFILE}; break ;;
esac
done
fi
fi
fi
# REMAP SCHEMA section:
echo "REMAP SCHEMAS or TABLES OWNER: [Leave it BLANK to keep SCHEMA NAMES | TABLES OWNER same as source]"
echo "============================="
echo "SOURCE_SCHEMA:TARGET_SCHEMA i.e. If you want to import objects owned by HR to BLAKE schema:"
echo "HR:BLAKE"
echo ""
while read REMAPSCHEMAVAR
do
case ${REMAPSCHEMAVAR} in
"") echo; export REMAPSCHEMA=""; break ;;
*:*) echo; export REMAPSCHEMA="REMAP_SCHEMA=${REMAPSCHEMAVAR}";echo ${REMAPSCHEMA} >> ${PARFILE}; break ;;
*) echo; echo "Please REMAP the SCHEMA in a right format: i.e. SOURCE_SCHEMA_NAME:TARGET_SCHEMA_NAME";;
esac
done
# REMAP TABLE section:
echo "REMAP TABLE NAMES: [Leave it BLANK to keep TABLE NAMES same as source]"
echo "================="
echo "SOURCE_SCHEMA.SOURCE_TABLE:TARGET_TABLE i.e. If you want to import table HR.EMPLOYEES as EMPX then enter it as follows:"
echo "HR.EMPLOYEES:EMPX"
echo ""
while read REMAPTABLEVAR
do
case ${REMAPTABLEVAR} in
"") echo; export REMAPTABLE=""; break ;;
*:*) echo; export REMAPTABLE="REMAP_TABLE=${REMAPTABLEVAR}"; echo ${REMAPTABLE} >> ${PARFILE}; break ;;
*) echo; echo "Please REMAP the TABLE in a right format: i.e. SOURCE_TABLE:TARGET_TABLE";;
esac
done
# REMAP TABLESPACE section:
echo "REMAP TABLESPACE: [Leave it BLANK to import the objects on same TABLESPACE NAME as source]"
echo "================="
echo "SOURCE_TABLESPACE:TARGET_TABLESPACE"
echo "i.e. If you want the imported objects to be created on EXAMPLE tablespace instead of USERS tablespace, then enter it as follows:"
echo "USERS:EXAMPLE"
echo ""
while read REMAPTABLESPACEVAR
do
case ${REMAPTABLESPACEVAR} in
"") echo; export REMAPTABLESPACE=""; break ;;
*:*) echo; export REMAPTABLESPACE="REMAP_TABLESPACE=${REMAPTABLESPACEVAR}"; echo ${REMAPTABLESPACE} >> ${PARFILE}; break ;;
*) echo; echo "Please REMAP the TABLESPACE in a right format: i.e. SOURCE_TABLESPACE:TARGET_TABLESPACE";;
esac
done
# TABLE_EXISTS_ACTION SECTION:
echo "TABLE_EXISTS_ACTION: Specify an action to be taken if the table is ALREADY EXIST: [[SKIP] | APPEND | REPLACE | TRUNCATE]"
echo "================================================================================"
echo "1. SKIP [IF TABLE IS ALREADY EXIST, SKIP ITS IMPORT AND LEAVE IT INTACT [DEFAULT]]"
echo "2. APPEND [IF TABLE IS ALREADY EXIST, APPEND THE IMPORTED DATA TO THE TABLE AND LEAVE THE CURRENT DATA INTACT]"
echo "3. REPLACE [IF TABLE IS ALREADY EXIST, DROP, RE-CREATE IT AND IMPORT NEW DATA, WHICH WILL DESTROY ITS CURRENT EXISTING DATA]"
echo "4. TRUNCATE [IF TABLE IS ALREADY EXIST, TRUNCATE IT AND IMPORT NEW DATA, WHICH WILL DESTROY ITS CURRENT EXISTING DATA]"
echo ""
while read TABLEEXISTSACTIONVAR
do
case ${TABLEEXISTSACTIONVAR} in
""|1|SKIP|skip|Skip) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=SKIP"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
echo "SKIP MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE [PRESERVED]"; echo; break;;
2|APPEND|append|Append) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=APPEND"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
echo "APPEND MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE [PRESERVED]"; echo; break;;
3|REPLACE|replace|Replace) export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=REPLACE"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
printf "`echo "REPLACE MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE ["` `echo -e "\033[33;5mDESTROYED\033[0m"` `echo "]"`\n"; echo; break;;
4|TRUNCATE|truncate|Truncate) echo; export TABLEEXISTSACTION="TABLE_EXISTS_ACTION=TRUNCATE"; echo ${TABLEEXISTSACTION} >> ${PARFILE}
printf "`echo "TRUNCATE MODE SELECTED | EXISTING DATA ON 'TABLES TO BE IMPORTED' WILL BE ["` `echo -e "\033[33;5mDESTROYED\033[0m"` `echo "]"`\n"; echo; break;;
*) echo; echo "Please Enter a valid NUMBER between 1 to 4";;
esac
done
# CONTENT SECTION:
echo "Specify if you want to import DATA or METADATA(DDLs) or both: [[DATA+METADATA] | METADATA_ONLY | DATA_ONLY]"
echo "============================================================"
echo "1. DATA+METADATA (ALL) [DEFAULT]"
echo "2. METADATA_ONLY"
echo "3. DATA_ONLY"
echo ""
while read CONTENTVAR
do
case ${CONTENTVAR} in
""|1|DATA+METADATA|data+metadata|DATAMETADATA|all|ALL) export CONTENT="CONTENT=ALL"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
2|METADATA|METADATA_ONLY|metadata|metadata_only) export CONTENT="CONTENT=METADATA_ONLY"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
3|DATA|data|DATA_ONLY|data_only|dataonly|DATAONLY) export CONTENT="CONTENT=DATA_ONLY"; echo ${CONTENT} >> ${PARFILE}; echo ${CONTENT}; echo; break;;
*) echo; echo "Please Enter a valid NUMBER between 1 to 3";;
esac
done
# INCLUDE ONLY SPECIFIC OBJECTS AND DENY THE OTHERS:
echo "Specify if you want to import ONLY specific objects and IGNORE the rest in the dump file: [Leave it BLANK to include all objects in the dump file]"
echo "========================================================================================"
echo "Note: TABLE option is valid ONLY in FULL & SCHEMA import modes."
echo "e.g. To import ONLY specific object types, separate them by comma: TABLE,INDEX,CONSTRAINT,REF_CONSTRAINT,TRIGGER,PROCEDURE,PACKAGE,FUNCTION,..."
echo "e.g. You can IMPORT ONLY the INDEXES starting with name PROD by typing: INDEX:\"LIKE 'PROD%'\""
echo ""
while read INCLUDEOBJECTSVAR
do
case ${INCLUDEOBJECTSVAR} in
"") echo; export INCLUDEOBJECTS=""; break;;
*) echo; export INCLUDEOBJECTS="INCLUDE=${INCLUDEOBJECTSVAR}"; echo ${INCLUDEOBJECTS} >> ${PARFILE}; echo; echo "EXCLUDE option will not be shown because INCLUDE option is used, whereas both options are mutually exclusive.";echo;break;;
esac
done
# Hide the following EXCLUDE options if INCLUDE is used, because INCLUDE & EXCLUDE are mutually exclusive:
if [ -z "${INCLUDEOBJECTSVAR}" ]
then
# EXCLUDE OTHER OBJECT TYPES:
echo "Specify if you want to EXCLUDE specific object types from this import: [Leave it BLANK to include all objects in the dump file]"
echo "====================================================================="
echo "Note: TABLE option is valid ONLY in FULL & SCHEMA import modes."
echo "e.g. you can exclude one or more object type separating with comma: TABLE,INDEX,CONSTRAINT,REF_CONSTRAINT,TRIGGER,PROCEDURE,PACKAGE,FUNCTION,..."
echo "e.g. you can exclude all tables start with name TEMP: TABLE:\"LIKE 'TEMP%'\""
echo ""
while read EXCLUDEOTHEROBJECTSVAR
do
case ${EXCLUDEOTHEROBJECTSVAR} in
"") echo; export EXCLUDEOTHEROBJECTS=""; break;;
*) echo; export EXCLUDEOTHEROBJECTS="EXCLUDE=${EXCLUDEOTHEROBJECTSVAR}"; echo ${EXCLUDEOTHEROBJECTS} >> ${PARFILE}; break;;
esac
done
# EXCLUDE GRANTS SECTION:
echo "Do you want to import the OBJECT PRIVILEGES? [[YES] | NO]"
echo "============================================"
echo ""
while read GRANTSVAR
do
case ${GRANTSVAR} in
""|YES|Yes|yes|y|Y) echo; export GRANTS=""; break;;
NO|No|no|n|N) echo; export GRANTS="EXCLUDE=OBJECT_GRANT"; echo ${GRANTS} >> ${PARFILE}; break;;
*) echo; echo "Please Enter a valid ANSWER YES or NO:";;
esac
done
# EXCLUDE STATISTICS SECTION:
echo "Do you want to import the STATISTICS? [[YES] | NO]"
echo "====================================="
echo ""
while read STATISTICSVAR
do
case ${STATISTICSVAR} in
""|YES|Yes|yes|Y|y) echo; export STATISTICS=""; break;;
NO|No|no|N|n) echo; export STATISTICS="EXCLUDE=STATISTICS"; echo ${STATISTICS} >> ${PARFILE}; break;;
*) echo; echo "Please Enter a valid ANSWER YES or NO:";;
esac
done
fi
echo -e "\033[33;5m[IMPDP PERFORMANCE SETTINGS]\033[0m";echo
# PARALLEL SECTION:
# If database edition support the PARALLEL option:
if [ "${CHK_PARALLELISM_OPTION}" -eq 1 ]
then
# Get the CPU Count on the machine:
case `uname` in
Linux ) export CPUCOUNT=`cat /proc/cpuinfo| grep processor|wc -l`;;
AIX ) export CPUCOUNT=`lsdev -C|grep Process|wc -l`;;
SunOS ) export CPUCOUNT=`kstat cpu_info|grep core_id|sort -u|wc -l`;;
HP-UX) export CPUCOUNT=`lsdev -C|grep Process|wc -l`;;
esac
echo "Enter the PARALLELISM DEGREE if you want to run the import in PARALLEL: [Leave it BLANK for NO PARALLEL | CPU COUNT is: ${CPUCOUNT}]"
echo "======================================================================"
echo ""
while read PARALLELDEGREEVAR
do
case ${PARALLELDEGREEVAR} in
"") echo; export PARALLELDEGREE=""; break;;
*[!0-9]*) echo "Please enter a valid NUMBER:";;
*) echo; export PARALLELDEGREE="PARALLEL=${PARALLELDEGREEVAR}"; echo ${PARALLELDEGREE} >> ${PARFILE}; break;;
esac
done
fi
# CLUSTER SECTION:
# If cluster detected and other instances are open:
if [ "${RAC_ACTIVE}" -eq 1 ]
then
echo "Do you want to BALANCE the import load between RAC nodes? [YES | [NO]] [Note: The dumpfile should be located on a SHARED location]"
echo "========================================================="
echo ""
while read CLUSTERVAR
do
case ${CLUSTERVAR} in
""|N|n|NO|no) echo; export CLUSTER=""; break;;
Y|y|YES|yes) echo; export CLUSTER="CLUSTER=YES"; echo ${CLUSTER} >> ${PARFILE}; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
fi
# If DB Version is 12c+:
if [ ${DB_VER} -gt 11 ]
then
# Disable ARCHIVE LOGGING for the import:
echo "Perform the import in NOLOGGING mode? [[YES] | NO] [NOT RECOMMENDED to use NOLOGGING if this DB is DataGuarded by a STANDBY DB]"
echo "====================================="
echo "Note: Only the import operation will run in NOLOGGING mode by setting TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y"
echo ""
while read ARCHIVELOGGINGVAR
do
case ${ARCHIVELOGGINGVAR} in
N|n|NO|no) echo; export ARCHIVELOGGING=""; break;;
""|Y|y|YES|yes) export ARCHIVELOGGING="TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y"; echo ${ARCHIVELOGGING} >> ${PARFILE};echo "TRANSFORM=DISABLE_ARCHIVE_LOGGING mode activated.";echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
# Enable ADDITIONAL PARAMETERS for 12c+:
# Enable LOGTIME: [Timed Log Records]
echo "LOGTIME=all" >> ${PARFILE}
# Enable METRICS: [Detailed log]
echo "METRICS=YES" >> ${PARFILE}
# Enable STATUS update every 30sec:
#echo "STATUS=30" >> ${PARFILE}
fi
echo -e "\033[33;5m[DATABASE PERFORMANCE SETTINGS]\033[0m";echo
# Disable FORCE LOGGING mode:
# If FORCE LOGGING is ENABLED:
if [ "${FORCE_LOGGING}" -eq 1 ]
then
echo "FORCE LOGGING mode is ENABLED | Do you want to DISABLE it during the import? [YES | [NO]] [NOT RECOMMENDED to disable if this DB is DataGuarded by a STANDBY DB]"
echo "============================================================================"
echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
echo ""
while read FORCELOGGINGVAR
do
case ${FORCELOGGINGVAR} in
""|N|n|NO|no) echo; export FORCELOGGING=""; break;;
Y|y|YES|yes) export FORCELOGGING="ALTER DATABASE NO FORCE LOGGING;";PRESERVEFORCELOGGING="ALTER DATABASE FORCE LOGGING;"; echo "FORCE LOGGING mode will be disabled during the import and will be enabled back after the import."; echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
fi
# Disable FLASHBACK mode:
# If FLASHBACK mode is ENABLED:
if [ "${FLASHBACK_ACTIVE}" -eq 1 ]
then
echo "FLASHBACK mode is ENABLED | Do you want to DISABLE it during the import? [[YES] | NO]"
echo "========================================================================"
echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
echo ""
while read FLASHBACKVAR
do
case ${FLASHBACKVAR} in
N|n|NO|no) echo; export FLASHBACK=""; break;;
""|Y|y|YES|yes) export FLASHBACK="ALTER DATABASE FLASHBACK OFF;";PRESERVEFLASHBACK="ALTER DATABASE FLASHBACK ON;"; echo "FLASHBACK mode will be disabled during the import and will be enabled back after the import."; echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
fi
# Disable of DB_BLOCK_CHECKING:
# IF DB_BLOCK_CHECKING_VAL=TRUE
if [ "${DB_BLOCK_CHECKING_VAL}" = "TRUE" ]
then
echo "DB_BLOCK_CHECKING is set to TRUE, Do you want to DISABLE it to speed up the import? [[YES] | NO]"
echo "==================================================================================="
echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
echo ""
while read DBBLOCKCHECKINGVAR
do
case ${DBBLOCKCHECKINGVAR} in
N|n|NO|no) echo; export DBBLOCKCHECKING=""; break;;
""|Y|y|YES|yes) export DBBLOCKCHECKING="ALTER SYSTEM SET db_block_checking=FALSE SCOPE=MEMORY;"; export PRESERVEDBBLOCKCHECKING="ALTER SYSTEM SET db_block_checking=TRUE;";echo "DB_BLOCK_CHECKING will be disabled during the import and will be enabled back after the import."; echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
fi
# Disable of DB_BLOCK_CHECKSUM:
# IF DB_BLOCK_CHECKSUM_VAL=TRUE
if [ "${DB_BLOCK_CHECKSUM_VAL}" = "TYPICAL" ] || [ "${DB_BLOCK_CHECKSUM_VAL}" = "FULL" ]
then
echo "DB_BLOCK_CHECKSUM is set to TRUE, Do you want to DISABLE it to speed up the import? [[YES] | NO]"
echo "==================================================================================="
echo "NOTE: If you choose to disable it, the script will automatically reset it to its original setting and enable it back after the import."
echo ""
while read DBBLOCKCHECKSUMVAR
do
case ${DBBLOCKCHECKSUMVAR} in
N|n|NO|no) echo; export DBBLOCKCHECKSUM=""; break;;
""|Y|y|YES|yes) export DBBLOCKCHECKSUM="ALTER SYSTEM SET db_block_checksum=OFF SCOPE=MEMORY;"
export PRESERVEDBBLOCKCHECKSUM="ALTER SYSTEM SET db_block_checksum=${DB_BLOCK_CHECKSUM_VAL};"
echo "DB_BLOCK_CHECKSUM will be disabled during the import and will be enabled back after the import."; echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
fi
# Enable RESUMABLE TIMEOUT:
echo "Enter the number of HOURs this import can RESUME within if it gets hung due to SPACE limitation: [Leave it Blank to Skip RESUMABLE_TIMEOUT]"
echo "================================================================================================"
echo "Note: This will set the parameter resumable_timeout system wide and will set it back to its original value after the import."
echo ""
while read RESUMABLEVAR
do
case ${RESUMABLEVAR} in
"") export RESUMABLESTMT=""; break;;
*[!0-9]*) echo "Please enter a valid NUMBER:"
echo "----------------------------";;
*) RESUMABLEVARSEC=$((${RESUMABLEVAR} * 3600))
export RESUMABLESTMT="ALTER SYSTEM SET resumable_timeout=${RESUMABLEVARSEC} SCOPE=MEMORY;"
export PRESERVERESUMABLESTMT="ALTER SYSTEM SET resumable_timeout=${RESUMABLE_TIMEOUT_VAL} SCOPE=BOTH;"
echo ""; echo "RESUMABLE_TIMEOUT will be set to: ${RESUMABLEVAR} Hours"; echo ""; break;;
esac
done
echo
echo "You are almost done!"; echo
echo "Hint: Make sure that TEMPORARY & UNDO Tablespaces along with UNDO_RETENTION Parameter are big enough to support this import operation."
echo ""
sleep 1
echo "Please verify the import settings summary:"
echo "------------------------------------------"
cat ${PARFILE}
echo
sleep 2
echo "Shall we start the IMPORT now? [[YES] | NO]"
echo "=============================="
while read STARTNOW
do
case ${STARTNOW} in
N|n|NO|no) echo; echo "SCRIPT TERMINATED! "; echo; exit;;
""|Y|y|YES|yes) echo; echo "STARTING THE IMPORT ..."; echo; break;;
*) echo "Please enter a valid answer: [YES|NO]";;
esac
done
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SPOOL ${STGDIR}/user_creation.log
PROMPT CREATE USER DBA_BUNDLEEXP7 [IMPORTER USER] (WILL BE DROPPED AFTER THE IMPORT) ...
CREATE USER DBA_BUNDLEEXP7 IDENTIFIED BY "BUNdle_#-^${PASSHALF}" ACCOUNT UNLOCK;
ALTER USER DBA_BUNDLEEXP7 IDENTIFIED BY "BUNdle_#-^${PASSHALF}" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO DBA_BUNDLEEXP7;
GRANT DBA TO DBA_BUNDLEEXP7;
-- The following privileges to workaround Bug 6392040:
GRANT EXECUTE ON SYS.DBMS_DEFER_IMPORT_INTERNAL TO DBA_BUNDLEEXP7;
GRANT EXECUTE ON SYS.DBMS_EXPORT_EXTENSION TO DBA_BUNDLEEXP7;
PROMPT
PROMPT CREATING DIRECTORY EXPORT_FILES_DBA_BUNDLE POINTING TO ${DUMPDIR} ...
CREATE OR REPLACE DIRECTORY EXPORT_FILES_DBA_BUNDLE AS '${DUMPDIR}';
PROMPT
PROMPT DISABLING DB FEATURES TO SPEED UP THE IMPORT: [will be enabled back after the import]
${FORCELOGGING}
${FLASHBACK}
${DBBLOCKCHECKING}
${DBBLOCKCHECKSUM}
${RESUMABLESTMT}
PROMPT
SPOOL OFF
EOF
)
echo
# Creation of the Import Script:
export LOGFILE="${STGDIR}/IMPORT_${IMPORT_MODE}_on_${ORACLE_SID}_${DUMPDATE}.log"
export IMPORTSCRIPT=${STGDIR}/IMPORTSCRIPT.sh
export IMPORTSCRIPTRUNNER=${STGDIR}/IMPORTSCRIPTRUNNER.sh
echo "# IMPORT Script: [Created By DBA_BUNDLE]" > ${IMPORTSCRIPT}
echo "echo ''" >>${IMPORTSCRIPT}
echo "echo \"While the Import job is running, you can check the STATUS using:\"" >>${IMPORTSCRIPT}
echo "echo \"--------------------------------------------------------------- \"" >>${IMPORTSCRIPT}
echo "echo \"SELECT job_name, operation, job_mode, DEGREE, state FROM dba_datapump_jobs where OPERATION='IMPORT' and state='EXECUTING' and owner_name='DBA_BUNDLEEXP7';\"" >>${IMPORTSCRIPT}
echo "echo ''" >>${IMPORTSCRIPT}
echo "echo \"Then you can ATTACH to the import job and control it using:\"" >>${IMPORTSCRIPT}
echo "echo \"---------------------------------------------------------- \"" >>${IMPORTSCRIPT}
echo "echo \"impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" ATTACH=<JOB_NAME_FROM_ABOVE_COMMAND>\"" >>${IMPORTSCRIPT}
echo "echo \"i.e.\"" >>${IMPORTSCRIPT}
echo "echo \"impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" ATTACH=SYS_IMPORT_${IMPORT_MODE}_01\"" >>${IMPORTSCRIPT}
echo "echo \"To Show the STATUS:....... STATUS\"" >>${IMPORTSCRIPT}
echo "echo \"To KILL the import:....... KILL_JOB\"" >>${IMPORTSCRIPT}
echo "echo \"To PAUSE the import:...... STOP_JOB\"" >>${IMPORTSCRIPT}
echo "echo \"To RESUME a paused import: START_JOB\"" >>${IMPORTSCRIPT}
echo "export ORACLE_SID=${ORACLE_SID}" >>${IMPORTSCRIPT}
echo "echo ''" >>${IMPORTSCRIPT}
echo "echo 'Running The IMPORT Job Now ...'" >>${IMPORTSCRIPT}
echo "${ORACLE_HOME}/bin/impdp DBA_BUNDLEEXP7/\"BUNdle_#-^${PASSHALF}\" PARFILE=${PARFILE}" >>${IMPORTSCRIPT}
echo "echo ''" >>${IMPORTSCRIPT}
echo "echo 'Running Post IMPORT Steps ...'" >>${IMPORTSCRIPT}
echo "echo ''" >>${IMPORTSCRIPT}
echo "sleep 5" >>${IMPORTSCRIPT}
echo "${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF" >>${IMPORTSCRIPT}
echo "PROMPT" >>${IMPORTSCRIPT}
echo "PROMPT DROPPING THE IMPORTER USER DBA_BUNDLEEXP7 ..." >>${IMPORTSCRIPT}
echo "DROP USER DBA_BUNDLEEXP7 CASCADE;" >>${IMPORTSCRIPT}
echo "PROMPT PRESERVE ORIGINAL DB SETTINGS ..." >>${IMPORTSCRIPT}
echo "${PRESERVEFORCELOGGING}" >>${IMPORTSCRIPT}
echo "${PRESERVEFLASHBACK}" >>${IMPORTSCRIPT}
echo "${PRESERVEDBBLOCKCHECKING}" >>${IMPORTSCRIPT}
echo "${PRESERVEDBBLOCKCHECKSUM}" >>${IMPORTSCRIPT}
echo "${PRESERVERESUMABLESTMT}" >>${IMPORTSCRIPT}
echo "EOF" >>${IMPORTSCRIPT}
echo "export JOBSTATUS=\`grep \"successfully\\|stopped\\|completed\" ${LOGFILE}|tail -1\`" >>${IMPORTSCRIPT}
echo "export LOGFILE=${LOGFILE}" >>${IMPORTSCRIPT}
echo "export EMAILID=\"${EMAILANS}\"" >>${IMPORTSCRIPT}
echo "${SENDEMAIL}" >>${IMPORTSCRIPT}
echo "echo; echo -e '\033[32;5mIMPORT IS DONE.\033[0m'" >>${IMPORTSCRIPT}
echo "echo" >>${IMPORTSCRIPT}
chmod 740 ${IMPORTSCRIPT}
echo
echo "#!/bin/bash" > ${IMPORTSCRIPTRUNNER}
echo "nohup sh ${IMPORTSCRIPT}| tee ${LOGFILE} 2>&1 &" >>${IMPORTSCRIPTRUNNER}
chmod 740 ${IMPORTSCRIPTRUNNER}
echo -e "\033[32;5mFeel free to EXIT from this session as the IMPORT SCRIPT is running in the BACKGROUND.\033[0m";echo
source ${IMPORTSCRIPTRUNNER}
# #############
# END OF SCRIPT
# #############
# Your feedback is most welcome, e-mail me at: mahmmoudadel@hotmail.com
# Every month a new release of DBA BUNDLE will get published, make sure to download it from:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
view raw import_data.sh hosted with ❤ by GitHub
Posted by 9 comments:
Subscribe to: Posts (Atom)