Remotely export and import between two database

How to do export import data remotely between two Oracle DB.

This could be helpful when we want to do a reguar copy of production data into pre prod for certain table.

 

The script below can be useful for the same:

#!/bin/bash
. $HOME/.bash_profile
################################################################################
#

################################################################################
# Revision History
################################################################################
# ---> For debugging purposes only

################################################################################
# supplemental area for extra information

################################################################################
# Parameters
HOME_BASE=/spare/tmp
TODAY=`date +"%Y%m%d"`
SCRIPT_DIR=${HOME_BASE}/admin/bin
SQL_DIR=${HOME_BASE}/admin/sql
EXP_DIR=${HOME_BASE}/admin/dump
LOG_DIR=${HOME_BASE}/admin/log
LOADER=${ORACLE_HOME}/bin/sqlldr
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
EXP=${ORACLE_HOME}/bin/exp
EXPORT_OWNER=$1
EXPORT_PASSWD=$2
TNS_PPOD=PPRSADB_PPod
TNS_PROD=value_to_be_set
SCHEMA_OWNER=$3
SCHEMA_PASSWD=$4
LOG_FILE=$LOG_DIR/IIN_RUN.log
LOG_TEMP_FILE=/tmp/PIN_BIN_log.tmp
CRITICAL=NO
HOST=`hostname`
FTP_ERROR=0
EXP_ERR=0
FILES_HOOPED=0
FAILURE_CODE=1


################################################################################
if [[ ! -x $HOME_BASE/admin/bin/load_PIN_BIN.ksh ]]; then
    print "file load_PIN_BIN.ksh does not have execute permission."
    chmod +x  $HOME_BASE/bin/load_PIN_BIN.ksh
    return 1
fi


if [ -s ${LOG_TEMP_FILE} ] ; then
   rm ${LOG_TEMP_FILE}
fi

################################################################################
   echo "`date +"%D-%T"`: " >> ${LOG_FILE}

################################################################################
###                             MAIN                                         ###
################################################################################
# recreate log file for each run
if [ ! -f ${LOG_FILE} ]; then
   touch ${LOG_FILE}
else
   rm ${LOG_FILE}
   touch ${LOG_FILE}
fi

# LogMsg ${DASHED_LINE}

## Export the PTS_PIN_BIN from PPod.

exp $EXPORT_OWNER/$EXPORT_PASSWD@$TNS_PPOD  file=$EXP_DIR/PP_PIN_BIN$TODAY.dmp tables=PTSADB.PTS_PIN_BIN statistics=none buffer=2000000 log=$LOG_DIR/EXPPIN_BIN_PP$TODAY.log


## Take a backup of PTS_PIN_BIN table from production

exp $SCHEMA_OWNER/$SCHEMA_PASSWD  file=$EXP_DIR/PROD_PIN_BIN$TODAY.dmp tables=DTSPROD.PTS_PIN_BIN statistics=none buffer=2000000 log=$LOG_DIR/EXPPIN_BIN_PRO$TODAY.log

### Truncate the table PTS_PIN_BIN from production

${SQLPLUS} -silent << EOLINE
${SCHEMA_OWNER}/${SCHEMA_PASSWD}
set echo off
set heading on
set pagesize 0
set feedback off
set linesize 250
spool ${LOG_DIR}/PIN_BIN_CLEANUP.log
@${SQL_DIR}/PIN_BIN_CLEANUP.sql
spool off
exit;
EOLINE



### IMPORT THE TABLE

imp $SCHEMA_OWNER/$SCHEMA_PASSWD tables=PTS_PIN_BIN FROMUSER=PTSADB TOUSER=DTSPROD  rows=y ignore=y grants=n commit=y file=$EXP_DIR/PP_PIN_BIN$TODAY.dmp log=$LOG_DIR/IMP_PIN_BIN_PPT$TODAY.log




#CleanUp