Code by example: expdp from schema + scp to a backup server

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

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

KEEP_DAYS=30

expdp user/password@ora_inst DIRECTORY=IG_EXP_DIR DUMPFILE=${EXP_NAME_PREFIX}.dmp LOGFILE=${EXP_NAME_PREFIX}.log REUSE_DUMPFILES=YES

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

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