exp_schema.sh
#!/bin/bash
RUN_DATE=`date +"%Y%m%d"`
EXP_DIR=/starhome/iu/workarea/ora_exp
EXP_NAME_PREFIX=exp_schema
EXP_DIR_BACKUP=/starhome/schema/workarea/schema_exp_backup
EXP_NAME_PREFIX=exp_schema
EXP_DIR_BACKUP=/starhome/schema/workarea/schema_exp_backup
REMOTE_USER=rem_user
REMOTE_SERVER=10.20.30.40
REMOTE_PATH=/starhome/workarea/ora_exp
LOCAL_DMP_FILE=exp_schema_${RUN_DATE}.dmp
LOCAL_DIR=/starhome/schema/workarea/schema_exp_backup
REMOTE_SERVER=10.20.30.40
REMOTE_PATH=/starhome/workarea/ora_exp
LOCAL_DMP_FILE=exp_schema_${RUN_DATE}.dmp
LOCAL_DIR=/starhome/schema/workarea/schema_exp_backup
KEEP_DAYS=30
echo "scp -p ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${EXP_NAME_PREFIX}.dmp ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp"
scp -p ${REMOTE_USER}@${REMOTE_SERVER}:${REMOTE_PATH}/${EXP_NAME_PREFIX}.dmp ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp
gzip ${LOCAL_DIR}/${EXP_NAME_PREFIX}_${RUN_DATE}.dmp
find ${LOCAL_DIR} -type f -name "${EXP_NAME_PREFIX}*" -mtime ${KEEP_DAYS} -exec rm {} \;
export_schema.sh <SCHEMA_NAME>
#!/bin/bash
if [[ "$#" -ne 1 ]]; then
echo "usage: export_schema.sh SCHEMA_NAME"
exit 1
fi
EXP_SCHEMA=$1
VERSION=11.2
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
echo "expdp system/xen86pga@igt DIRECTORY=IG_EXP_DIR LOGFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.log DUMPFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.dmp SCHEMAS=${EXP_SCHEMA} version=${VERSION}"
expdp system/xen86pga@igt DIRECTORY=IG_EXP_DIR LOGFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.log DUMPFILE=expdp_${EXP_SCHEMA}_${RUN_DATE}.dmp SCHEMAS=${EXP_SCHEMA}
version=${VERSION}
imp_schema_data.sh <OLD_USER> <NEW_USER> <NEW_PASS> <DMP_FILE>
#!/bin/bash
if [[ "$#" -ne 4 ]]; then
echo "usage: import_schema_data.sh OLD_USER NEW_USER USER_PASSWORD DMP_FILE"
exit 1
fi
clear
echo "***** Importing Data... "
OLD_USER=$1
NEW_USER=$2
NEW_PASS=$3
DMP_FILE=$4
RUN_DATE=`date "+%Y%m%d"_"%H%M"`
CONNECTSTR=igt
DIRECTORY=IG_EXP_DIR
# Stage 1 Disable FK's
sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@disable_cons.sql
EOD
# Stage 2 Disable triggers
sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@disable_trigs.sql
EOD
# Stage 3 Import objects
impdp system/xxxxxxx@${CONNECTSTR} directory=${DIRECTORY} dumpfile=${DMP_FILE} logfile=impdp_${NEW_USER}_${RUN_DATE}.log table_exists_action=truncate streams_configuration=n content=data_only remap_schema=${OLD_USER}:${NEW_USER}
# Stage 4 Enable FK's
sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@enable_cons.sql
EOD
# Stage 5 Enable triggers
sqlplus -s $VIPUSER/$VIPPASS@$CONNECTSTR << EOD
@enable_trigs.sql
EOD
echo "***************************************************"
echo " FINISHED Import of DataBase "
echo "***************************************************"
Additional Files:
disable_cons.sql
disable_trigs.sql
enable_cons.sql
enable_trigs.sql
disable_cons.sql
disable_cons.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000 heading off feedback off
spool disable_cons.txt
prompt spool disable_cons.log
select 'alter table '|| table_name || ' disable constraint ' || constraint_name || ';' from user_constraints
where CONSTRAINT_TYPE='R' AND status = 'ENABLED';
/
prompt spool off
spool off
@disable_cons.txt
disable_trigs.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000 heading off feedback off
spool disable_trigs.txt
prompt spool disable_trigs.log
select 'alter trigger '|| trigger_name || ' disable ' || ';' from user_triggers
/
prompt spool off
spool off
@disable_trigs.txt
enable_cons.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000 heading off feedback off
spool enable_cons.txt
prompt spool enable_cons.log
select 'alter table '|| table_name || ' enable constraint ' || constraint_name || ';' from user_constraints
where CONSTRAINT_TYPE='R' AND status = 'ENABLED';
/
prompt spool off
spool off
@enable_cons.txt
enable_trigs.sql
set serverout on
exec dbms_output.enable(100000);
set heading off linesize 132 pagesize 1000 heading off feedback off
spool enable_trigs.txt
prompt spool enable_trigs.log
select 'alter trigger '|| trigger_name || ' enable ' || ';' from user_triggers where trigger_name!='REFRESH_VIEWS_TRG'
/
prompt spool off
spool off
@enable_trigs.txt
