Export_Import_Scripts
expdp DUMPFILE=OWNER.dpdmp DIRECTORY=MYEXP SCHEMAS=OWNER parfile=1.par
cat 1.par -- INCLUDE=TABLE:"IN (select table_name from dba_tables where owner='OWNER' and table_name !='TAB_NAME')"
expdp DUMPFILE=TAB_NAME.dpdmp DIRECTORY=MYEXP TABLES=OWNER.TAB_NAME parfile=2.par
cat 2.par -- "query=D_TMESTP > '01-MAY-15' "
and
cat another.par --- query=owner.table_name:"where Col_name = 'ECLF'"
##########################################################################################################################################################
cat 1.par
INCLUDE=TABLE:"IN (select table_name from dba_tables where owner='ABC' and table_name !='ITM')"
cat expdp_full_test.sh
#!/bin/sh
###########################################################################
- Script Name - expdp_full.sh
- By Name - Deepak Kachole
- Desc- script to take full export database backup using Data Pump
- EXCEPT NW_DATA_WAREHOUSE
- Date - 29 July 2015
- Version - 0.1
#
###########################################################################
STARTTIME=`date`
export ORA_NAME=UKENTTST
export ORACLE_HOME=/opt/SP/pivotal/oracle/product/11.2.0/client_1
export EXPLOG=expdp_UKENTTST.log
export EXPDIR=LOG_DIR
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`
- Data Pump export of selected SCHEMAS
expdp EN/EDMIN_ex4d@UKENTTST.test.uk COMPRESSION=ALL DIRECTORY=LOG_DIR SCHEMAS=ENNT_ADMIN_DUP DUMPFILE=ennt_admin_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=ennt_admin_UKTST.log parallel=2
#SCHEMAS=ADMIN DUMPFILE=ennt_admin_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=ennt_admin_UKTST.log parallel=2
- Find old dump files and delete them
cd /opt/SP/data/data/log/ENNT_SP_LOG
find ./ -name "expdp_UKET_*.dpdmp" -mtime +1 -exec ls -lrt {} \;
- Send an email noitification to the ADMIN...
ENDTIME=`date`
SUBJECT=`hostname -s`:$ORA_NAME:`tail -5 /opt/SP/data/ENNT/data/log/ENNT_SP_LOG/expdp_UKENTTST.log`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" deepak.kachole@yahoo.com
===============
cat expdp_full.sh
#!/bin/sh
###########################################################################
- Script Name - expdp_full.sh
- By Name - Deepak Kachole
- Desc- script to take full export database backup using Data Pump
- EXCEPT NW_DATA_WAREHOUSE
- Date - 29 July 2015
- Version - 0.1
#
###########################################################################
STARTTIME=`date`
export ORA_NAME=UKENTTST
export ORACLE_HOME=/opt/SP/pivotal/oracle/product/11.2.0/client_1
export EXPLOG=expdp_UKENTTST.log
export EXPDIR=LOG_DIR
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`
- Data Pump export of selected SCHEMAS
expdp ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk parfile=/opt/SP/data/ENNT/scripts/expdp_full.par DUMPFILE=expdp_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=expdp_UKENTTST.log parallel=2
- Find old dump files and delete them
cd /opt/SP/data/ENNT/data/log/ENNT_SP_LOG
find ./ -name "expdp_UKENTTST_*.dpdmp" -mtime +1 -exec ls -lrt {} \;
- Send an email noitification to the ADMIN...
ENDTIME=`date`
SUBJECT=`hostname -s`:$ORA_NAME:`tail -5 /opt/SP/data/ENNT/data/log/ENNT_SP_LOG/expdp_UKENTTST.log`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" deepak.kachole@
ukrqtahr:oracle:/opt/SP/data/ENNT/scripts $ cat /opt/SP/data/ENNT/scripts/expdp_full.par
COMPRESSION=ALL full=y DIRECTORY=LOG_DIR EXCLUDE=SCHEMA:"IN ('OUTLN','SYSTEM','SYS','FLOWS_FILES','ADBM','ITO_DBSPI','DIP', 'ORACLE_OCM', 'DBSNMP','APPQOSSYS','NW_DATA_WAREHOUSE')"
===============================
cat impdp_full.sh
#!/bin/sh
###########################################################################
- Script Name - expdp_full.sh
- By Name - Deepak Kachole
- Desc- script to take full export database backup using Data Pump
- EXCEPT NW_DATA_WAREHOUSE
- Date - 29 July 2015
- Version - 0.1
#
###########################################################################
STARTTIME=`date`
export ORA_NAME=UKENTTST
export ORACLE_HOME=/opt/SP/pivotal/oracle/product/11.2.0/client_1
export EXPLOG=expdp_UKENTTST.log
export EXPDIR=LOG_DIR
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`
- Data Pump export of selected SCHEMAS
##impdp ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk DIRECTORY=LOG_DIR DUMPFILE=expdp_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=impdp_UKENTTST.log parallel=2
- Checked using REMAP function .....
impdp ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk DIRECTORY=LOG_DIR DUMPFILE=ennt_admin_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=impdp_UKENTTST.log parallel=2 REMAP_SCHEMA=NW_DATA_WAREHOUSE:ENNT_ADMIN_DUP
- Find old dump files and delete them
cd /opt/SP/data/ENNT/data/log/ENNT_SP_LOG
find ./ -name "expdp_UKENTTST_*.dpdmp" -mtime +1 -exec ls -lrt {} \;
- Send an email noitification to the ADMIN...
ENDTIME=`date`
SUBJECT=`hostname -s`:$ORA_NAME:`tail -5 /opt/SP/data/ENNT/data/log/ENNT_SP_LOG/expdp_UKENTTST.log`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" deepak.kachole@
============
cat impdp_full.sh
#!/bin/sh
###########################################################################
- Script Name - expdp_full.sh
- By Name - Deepak Kachole
- Desc- script to take full export database backup using Data Pump
- EXCEPT NW_DATA_WAREHOUSE
- Date - 29 July 2015
- Version - 0.1
#
###########################################################################
STARTTIME=`date`
export ORA_NAME=UKENTTST
export ORACLE_HOME=/opt/SP/pivotal/oracle/product/11.2.0/client_1
export EXPLOG=expdp_UKENTTST.log
export EXPDIR=LOG_DIR
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`
/opt/SP/data/ENNT/scripts/drop_schemas.sh
- Data Pump export of selected SCHEMAS
##impdp ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk DIRECTORY=LOG_DIR DUMPFILE=expdp_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=impdp_UKENTTST.log parallel=2
- Checked using REMAP function .....
impdp ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk DIRECTORY=LOG_DIR DUMPFILE=ennt_admin_`echo $ORA_NAME`_%U_`echo $DATEFORMAT`.dpdmp logfile=impdp_UKENTTST.log parallel=2 REMAP_SCHEMA=NW_DATA_WAREHOUSE:ENNT_ADMIN_DUP
- Find old dump files and delete them
cd /opt/SP/data/ENNT/data/log/ENNT_SP_LOG
find ./ -name "expdp_UKENTTST_*.dpdmp" -mtime +1 -exec ls -lrt {} \;
- Send an email noitification to the ADMIN...
ENDTIME=`date`
SUBJECT=`hostname -s`:$ORA_NAME:`tail -5 /opt/SP/data/ENNT/data/log/ENNT_SP_LOG/expdp_UKENTTST.log`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" deepak.kachole@
===
cat /opt/SP/data/ENNT/scripts/drop_schemas.sh
#!/bin/ksh
########################################
- Name - Script to drop users
- By - Deepak Kachole
########################################
date
sqlplus -s << EOF > /dev/null 2>&1
ENNT_ADMIN/ENNT_ADMIN_ex4d@UKENTTST.test.uk
spool /opt/SP/data/ENNT/scripts/drop_user.log append
drop user ENNT_ADMIN_DUP CASCADE;
--drop user ENNT_ADMIN CASCADE;
--drop user NW_DATA_WAREHOUSE CASCADE;
--drop user NW_ADMIN CASCADE;
--drop user NW_READ_ONLY CASCADE;
--drop user NW_USER CASCADE;
--drop user ARADMIN CASCADE;
--drop user ENNT_APP CASCADE;
--drop user ENNT_USER CASCADE;
--drop user ENNT_READ_ONLY CASCADE;
exit;
date
spool off
No comments:
Post a Comment