oracle数据泵的导入导出,Oracle数据泵导入导出作业

导出生产数据库上的数据到备份数据库中：

1. 建立导出目录：

mkdir /arch/dump1

chown -R oraees:dba /arch/dump1

create directory dump as '/arch/dump1';

2.建立导出参数文件

(1).EES_exp_par_01.txt(分区表)

directory=dump

dumpfile=EES_exp101.dmp,EES_exp102.dmp,EES_exp103.dmp

logfile=EES_exp101.log

parallel=3

tables =

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100701,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100702,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100703,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100704,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100705,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100706,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100707,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100708,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100709,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100710,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100711,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100712,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100713,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100714,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100715,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100716,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100717,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100718,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100719,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100720,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100721,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100722,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100723,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100724,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100725,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100726,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100727,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100728,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100729,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100730,

ORAEES.DATA_CHECK_TRX_SPC_SITE:P20100731,

ORAEES.REPORT_INDEX_D_SUM_MPA:P20100928,

ORAEES.REPORT_INDEX_D_SUM_MPA:P20100929,

ORAEES.REPORT_INDEX_D_SUM_MPA:P20100930,

exclude=STATISTICS

(2).EES_exp_par_02.txt(普通表)

dumpfile=EES_exp201.dmp

logfile=EES_exp201.log

directory=dump

tables=ORAEES.MET_DATA_PARAM_TRX_PP,ORAEES.MET_DATA_TRX_PP,ORAEES.OOC_TRX_SPC,ORAEES.MONITORING_HST_PP,ORAEES.DATA_TEMP_FDC

query=ORAEES.MET_DATA_PARAM_TRX_PP:" where CREATE_DTTS < TO_TIMESTAMP('2010-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"

query=ORAEES.MET_DATA_TRX_PP:" where CREATE_DTTS < TO_TIMESTAMP('2010-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"

query=ORAEES.OOC_TRX_SPC:" where CREATE_DTTS < TO_TIMESTAMP('2010-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"

query=ORAEES.MONITORING_HST_PP:" where CREATE_DTTS < TO_TIMESTAMP('2010-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"

query=ORAEES.DATA_TEMP_FDC:" where CREATE_DTTS < TO_TIMESTAMP('2010-10-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"

exclude=STATISTICS

parallel=1

3.导出表

$ expdp system/ parfile=EES_exp_par_01.txt

$ expdp system/ parfile=EES_exp_par_02.txt

4.建立导入目录

mkdir /oracle/dump/backup/ees

chown -R oracle:dba /oracle/dump/backup/ees

create directory Dump6 as '/oracle/dump/backup/ees';

5. 建立导入参数文件

(1).分区表

directory=dump6

dumpfile=EES_exp101.dmp,EES_exp102.dmp,EES_exp103.dmp

logfile=ees_imp01.log

REMAP_SCHEMA = ORAEES:PKG_EESPRD

parallel=3

REMAP_TABLESPACE=SPC_TRX_DAT:PKG_EES_DAT,

DCP_TRX_DAT:PKG_EES_DAT,

FDC_TRX_DAT:PKG_EES_DAT,

MPA_TRX_DAT:PKG_EES_DAT,

PPM_TRX_DAT:PKG_EES_DAT,

SPC_TRX_IDX:PKG_EES_IDX,

DCP_TRX_IDX:PKG_EES_IDX,

FDC_TRX_IDX:PKG_EES_IDX,

MPA_TRX_IDX:PKG_EES_IDX,

PPM_TRX_IDX:PKG_EES_IDX

TABLE_EXISTS_ACTION = APPEND

(2).普通表

directory=dump6

dumpfile=EES_exp201.dmp

logfile=ees_imp02.log

REMAP_SCHEMA = ORAEES:PKG_EESPRD

parallel=2

REMAP_TABLESPACE=SPC_TRX_DAT:PKG_EES_DAT,

DCP_TRX_DAT:PKG_EES_DAT,

FDC_TRX_DAT:PKG_EES_DAT,

MPA_TRX_DAT:PKG_EES_DAT,

PPM_TRX_DAT:PKG_EES_DAT,

SPC_TRX_IDX:PKG_EES_IDX,

DCP_TRX_IDX:PKG_EES_IDX,

FDC_TRX_IDX:PKG_EES_IDX,

MPA_TRX_IDX:PKG_EES_IDX,

PPM_TRX_IDX:PKG_EES_IDX

TABLE_EXISTS_ACTION = APPEND

6. 导入

$ impdp system/ parfile=EES_imp_par_01.txt

$ impdp system/ parfile=EES_imp_par_02.txt

7.收集统计信息

exec dbms_stats.gather_schema_stats(ownname => 'PKG_EESPRD',options => 'GATHER AUTO');

8. 删除数据&rebuild index

alter index oraees.IDX_EQP_TRACE_TRX_FDC_PK_NEW rebuild online;alter index oraees.IDX_EQP_ALARM_TRX_PP_UK rebuild online;alter index oraees.IDX_DATA_CHECK_TRX_SPC_SITE_UK rebuild online;alter index oraees.IDX_DATA_TRX_SPC_RAWID rebuild online;alter index oraees.IDX_LOSS_HST_CPPM_1 rebuild online;alter index oraees.IDX_LOSS_HST_CPPM_PK rebuild online;