Monday, 8 October 2018

Different Examples of expdp and impdp

1) Using Tables clause in expdp

If table name in upper case and db is case sensitive.
expdp otm/Password123@V6DB2 DIRECTORY=DATA_PUMP_DIR DUMPFILE=table1.dmp logfile=OTM_12_27NOV2013_2_imp.log NCLUDE=TABLE:\"IN \(\'TO_TestCase\'\)\"
expdp tccindife/tccindife@barc11r2 directory=DATA_DIR dumpfile=abcd.dmp logfile=tccindife_tables.log content=data_only tables='TCCINDIFE.ACTB_ACCBAL_MISMATCH','TCCINDIFE.ACTB_CONSOL_STMT_FILE'

2) Example Using Parameter Files

expdp system/manager parfile=exp.par

Parameter file:exp.par

DIRECTORY = my_dir

DUMPFILE = exp_tab.dmp

LOGFILE = exp_tab.log

SCHEMAS = scott

INCLUDE = TABLE:"IN ('EMP', 'DEPT')"

3) Examples using Parameter files for all expdp and impdp

exp.sh (Contents Following):
##Initialization of the Oracle Path
PATH=$PATH:$HOME/bin
export ORACLE_HOME=/oraint2/ora1122/product/11.2.0/dbhome_1
export ORACLE_SID=POC1
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
LD_LIBRARY_PATH=/oraint2/ora1122/product/11.2.0/dbhome_1/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
#Execution of the expdp command with parfile
expdp parfile=/oraint1/ora10gR2/tcdump/export.par

export.par (Contents Following as a parameter file):
USERID=user_name/password@Instance_name
schemas=tcbnk12
directory=tcbnkdumpdir
compression=all
dumpfile=tcbnk12.dmp
logfile=tcbnk113_21feb2012_FULL.log

imp.sh (Contents Following):
##Initialization of the Oracle Path
PATH=$PATH:$HOME/bin
export ORACLE_HOME=/scratch/db/db1123/product/11.2.0/dbhome_1
export ORACLE_SID=jpub2wek
PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
#Execution of the impdp command with parfile
impdp parfile=/scratch/work_area/DEV/dump/impdp.par

impdp.par (Contents Following as a parameter file):

USERID=user_name/password@Instance_name
directory= tcbnkdumpdir
dumpfile= tcbnk12.dmp
logfile= tcbnk12.log
REMAP_SCHEMA=old_schema_name:new_schema_name
REMAP_TABLESPACE=old_tablespace_name:new_tablespace_name
TRANSFORM=SEGMENT_ATTRIBUTES:n
TRANSFORM=OID:n
JOB_NAME= tcbnk12_job

4) Exclude/Include Clause with parameter file.

Impdp.par
userid=TCBNKAT121/TCBNKAT121@TCDEMO
DIRECTORY=DUMP_DIR
DUMPFILE=TCBNK_12.0.0_ITR2_FINAL.DMP
LOGFILE=TCBNK_12.0.0_ITR2_FINAL1.log
REMAP_SCHEMA=TCBNK12R2:TCBNKAT121
REMAP_TABLESPACE=TCBNK12R2:TCBNKAT121
TRANSFORM=SEGMENT_ATTRIBUTES:n
TRANSFORM=OID:n
COMPILE=N
EXCLUDE=SEQUENCE,PROCEDURE,PACKAGE,TRIGGER,INDEX,SYNONYM,VIEW,FUNCTION,TYPE,LIBRARY
IGNORE=Y
GRANTS=Y INDEXES=Y ROWS=Y CONSTRAINTS=Y
% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"

5) Example with Remap_Schema

impdp warbadev21/warbadev21@me11g22 directory=datadump dumpfile=WARBA.dmp remap_tablespace=WBTCSIM1:WARBADEV21 remap_schema=WBTCSIM1:WARBADEV21 logfile=warba_dev3.log

impdp USERID=TCS1131DEV/TCS1131DEV@server1 SCHEMAS=tcs1131dev DIRECTORY=DUMP DUMPFILE=expdp_tcs1131dev_full.dmp INCLUDE = PACKAGE_BODY LOGFILE=impdp_tcs1131dev_package_110120121.LOG
IMPDP using db link
impdp system/welcome1@server1 DIRECTORY=RESTORE remap_schema=PAND2BCOLL:PAND2BCOLL remap_tablespace=PAND2BCOLL:PAND2BCOLL NETWORK_LINK=DBLINK_pandgp56 logfile=imp_iut01_pandgp57_1.log

6) Example of Exporting a Full Database

To Export Full Database or Bigger size database or need to do parallel export and import, give the following command
$expdp scott/tiger DIRECTORY=data_pump_dir FULL=y DUMPFILE=partsize%U.dmp
FILESIZE=10G LOGFILE=mypartexp.log JOB_NAME=mypartJob
We get multiple dump files named as partsize01.dmp, partsize02.dmp and so on.  The FILESIZE parameter specifies max size of the pieces of the dump files.

7) Table level export

$expdp emp/emp DIRECTORY=dpump_dir_tab DUMPFILE=selectedtables.dmp TABLES=employees,jobs,departments

8) Exporting selective tablespaces

$expdp emp/emp DIRECTORY=dpump_dir_ts DUMPFILE=selected_tbs.dmp TABLESPACES=TBS_emp,TBS_dpt,TBS_Mgr
This will export all the objects from TBS_emp,TBS_dpt,TBS_Mgr

9) Excluding and Including Objects in expdp

$expdp scott/tigers DIRECTORY=data_pump_dir DUMPFILE=exclude.dmp
SCHEMAS=SCOTT EXCLUDE=TABLE:"like 'C%'"
Here all tables in Scott’s Schema whose name starts with “C “ will not be exported.
$expdp scott/tiger DIRECTORY=data_pump_dir DUMPFILE=scott_schema.dmp
SCHEMAS=SCOTT INCLUDE=TABLE:"like 'Z%'"
Here all tables in Scott’s Schema whose name starts with “Z “ will be exported.
Similarly you can also exclude INDEXES, CONSTRAINTS, GRANTS, USER, SCHEMA

10) Adding Query to Filter out Rows during Export

expdp emp/passw QUERY=emp:'"WHERE emp_id > 10 AND sal > 90000"' NOLOGFILE=y DIRECTORY=dpump_test DUMPFILE=query.dmp

11) Expdp/Impdp in different directories/different mountpoints in same server

expdp dumpfile=DATA_PUMP_DIR:TESTONE_datapumpdir_%U.dmp,EXPBKP2:TESTONE_expbkp2_%U.dmp logfile=DATA_PUMP_DIR:TESTONE_expdp.log full=y compression=all parallel=2
impdp dumpfile=DATA_PUMP_DIR:TESTONE_expdp_%U.dmp,EXPBKP2:TESTONE_expdp_%U.dmp logfile=TESTONE_impdp.log directory=DATA_PUMP_DIR parallel=2 schemas=SCOTT

12) Points for parallel EXPDP/IMPDP

As a general rule for processes you find the cpu_count parameter (v$parameter) and use cpu-count-1.  However. the Oracle Utilities documentation makes a slightly different recommendation for using a Data Pump export
select name,value from v$parameter where name = ‘cpu_count’;
1) Set the degree of parallelism to two times the number of CPUs, then tune from there.
2) For Data Pump Export, the PARALLEL parameter value should be less than or equal to the number of output dump files.
For Data Pump Import, the PARALLEL parameter value should not be much larger than the number of files in the dump file set.

EXAMPLE 1)

export.par
FILESIZE=10G
compression=all
parallel=32
logfile=expdp_t7.log
dumpfile=expd_t7_%u.dmp
schemas=fcubs1201
directory=expdir
COMPRESSION=ALL
JOB_NAME=fcubs1201_job

EXECUTE

expdp parfile=export.par
Username: sys as sysdba
Password:welcome1

EXAMPLE 2)

expdp vallep/password directory=datapump full=y dumpfile=data.dmp filesize=2G parallel=2 logfile=full.log

EXAMPLE 3):

impdp system/sysdec2012@TRAININGDB schemas=test parallel=2 DIRECTORY=DATA_DUMP DUMPFILE=test_%U.dmp LOGFILE=test.log JOB_NAME=testJob content=metadata_only

13) REUSE_DUMPFILE : Overwrite existing dumpfile)

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile. Oracle provides an option reuse_dumpfile=[Y/N] to avoid this error. You should mention the parameter value as Y to overwrite the existing dump file. By default the option considered as N.
$ expdp scott/tiger directory=exp_dir dumpfile = x.dmp table s= example reuse_dumpfiles = y14)Verification of status of IMPDP job

No comments:

Post a Comment