赞
踩
导出生产数据库上的数据到备份数据库中:
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;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。