expdp +impdp 迁移总结

1、创建用户

BOSQUERY_YF
BOSQUERY_YF3


SQL> grant dba to boswll;


SQL> select * From dba_directories;
 
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SYS                            DUMP_DIR                       /backup/


grant read, write on directory DUMP_DIR to boswll;

2、停止所有业务，锁定账户防止外界访问数据库，导致数据不一致问题

alter user DD_WORK account lock

3、网络权限的开通

从129网段数据迁移到127 网段，需要统计中间层的ip地址，开通访问数据库1521端口权限


1.1.127.85  1.1.127.87    1521 端口

1521 端口


4、导出数据
expdp boswll/"Wll_S[good123]_andTTpassw0rd"  directory=DUMP_DIR schemas=DD_WORK  COMPRESSION=DATA_ONLY filesize=7168m dumpfile=DD_WORK_%U_$today.dmp  logfile=DD_WORK_$today.log PARALLEL=8 




expdp boswll/"Wll_S[good123]_andTTpassw0rd"  directory=DUMP_DIR schemas=DD_WORK filesize=7168m dumpfile=DD_WORK_%U.dmp  logfile=DD_WORK.log PARALLEL=8 




aix_power_db:/data/expdp_data$ls -lrt
total 22453464
-rwxr-xr-x    1 oracle   dba              44 Mar 14 2014  start_exp.sh
-rw-r--r--    1 oracle   dba             147 May 20 2015  parfile.par
-rw-r--r--    1 oracle   dba             205 May 03 2016  metadata.sh
-rwxr-xr-x    1 oracle   dba             496 Sep 07 2016  expdp.sh
-rw-r--r--    1 oracle   dba      7343712109 Oct 23 11:40 DD_WORK_201710231100.tar.gz
-rw-r-----    1 oracle   dba       524500992 Oct 23 18:26 DD_WORK_08_.dmp
-rw-r-----    1 oracle   dba       322768896 Oct 23 18:28 DD_WORK_09_.dmp
-rw-r-----    1 oracle   dba       490909696 Oct 23 18:28 DD_WORK_07_.dmp
-rw-r-----    1 oracle   dba       653582336 Oct 23 18:28 DD_WORK_06_.dmp
-rw-r-----    1 oracle   dba       700010496 Oct 23 18:28 DD_WORK_05_.dmp
-rw-r-----    1 oracle   dba       428322816 Oct 23 18:28 DD_WORK_04_.dmp
-rw-r-----    1 oracle   dba       434515968 Oct 23 18:28 DD_WORK_03_.dmp
-rw-r-----    1 oracle   dba       478863360 Oct 23 18:28 DD_WORK_02_.dmp
-rw-r-----    1 oracle   dba       118685696 Oct 23 18:28 DD_WORK_01_.dmp
-rw-r--r--    1 oracle   dba            5450 Oct 23 18:28 DD_WORK_.log


4 传输数据
 scp   1.1.129.4:/data/expdp_data/DD_WORK_*   /backup/
 
 
5、 扩充表空间
 
 alter tablespace NNC_DATA01 add datafile '+ASMDATA/' size 100m;
 alter tablespace NNC_DATA01 add datafile '+ASMDATA/' size 100m;
 alter tablespace NNC_DATA01 add datafile '+ASMDATA/' size 100m;
 
alter database datafile '+ASMDATA/cwjs/datafile/nnc_data01.298.958156557' resize 10000m;
alter database datafile '+ASMDATA/cwjs/datafile/nnc_data01.299.958156563' resize 10000m;
alter database datafile '+ASMDATA/cwjs/datafile/nnc_data01.300.958156563' resize 10000m;


6、导入数据


impdp boswll/"boswll"  directory=my_dir schemas=DD_WORK  dumpfile=DD_WORK_%U_.dmp  logfile=DD_WORK_imp.log PARALLEL=8  REMAP_TABLESPACE=MONGO_DATA01:NNC_DATA01  REMAP_TABLESPACE=MONGO_INDEX01:NNC_INDEX01  REMAP_TABLESPACE=MONGO_INDEX02:NNC_INDEX01  TABLE_EXISTS_ACTION=REPLACE cluster=N




-rw-r--r-- 1 oracle asmadmin        5450 Oct 23 18:30 DD_WORK_.log
-rw-r----- 1 oracle asmadmin   118685696 Oct 23 18:30 DD_WORK_01_.dmp
-rw-r----- 1 oracle asmadmin   478863360 Oct 23 18:30 DD_WORK_02_.dmp
-rw-r----- 1 oracle asmadmin   434515968 Oct 23 18:30 DD_WORK_03_.dmp
-rw-r----- 1 oracle asmadmin   428322816 Oct 23 18:30 DD_WORK_04_.dmp
-rw-r----- 1 oracle asmadmin   700010496 Oct 23 18:31 DD_WORK_05_.dmp
-rw-r----- 1 oracle asmadmin   653582336 Oct 23 18:31 DD_WORK_06_.dmp
-rw-r----- 1 oracle asmadmin   490909696 Oct 23 18:31 DD_WORK_07_.dmp
-rw-r----- 1 oracle asmadmin   524500992 Oct 23 18:31 DD_WORK_08_.dmp
-rw-r----- 1 oracle asmadmin   322768896 Oct 23 18:31 DD_WORK_09_.dmp
-rw-r--r-- 1 oracle asmadmin        6514 Oct 23 18:57 DD_WORK_imp.log



Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "DD_WORK"."HDS_SIGN_WORKER_TASK_OPER"  1.172 GB 29146483 rows
ORA-31693: Table data object "DD_WORK"."HDS_OUT_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_02_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_02_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "DD_WORK"."HDS_IN_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_08_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_08_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "DD_WORK"."HDS_JMS_COMMON_WORKER_TASK"  27.65 MB  602535 rows
. . imported "DD_WORK"."HDS_SIGN_WORKER_TASK_PROV"  1.172 GB 29146683 rows
ORA-31693: Table data object "DD_WORK"."HDS_SYNC_IA_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_07_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_07_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "DD_WORK"."HDS_EARN_WORKER_TASK"       8.743 MB  153641 rows
. . imported "DD_WORK"."HDS_COMMON_WORKER_TASK"     89.39 KB    2244 rows
. . imported "DD_WORK"."HDS_CLEAN_WORKER_DATA"      5.062 KB       9 rows
. . imported "DD_WORK"."HDS_EARN_COMMON_WORKER_TASK"  5.539 KB       1 rows
. . imported "DD_WORK"."HDS_NR_COMMON_WORKER_TASK"  42.41 KB     996 rows
. . imported "DD_WORK"."PAMIRS_SCHEDULE_QUEUE"      12.54 KB     338 rows
. . imported "DD_WORK"."PAMIRS_SCHEDULE_SERVER"     11.25 KB      68 rows
. . imported "DD_WORK"."PAMIRS_SCHEDULE_SERVER_HIS"  7.148 KB       9 rows
. . imported "DD_WORK"."PAMIRS_SCHEDULE_TASKTRUN"   7.085 KB      30 rows
. . imported "DD_WORK"."PAMIRS_SCHEDULE_TASKTYPE"   7.726 KB      30 rows
. . imported "DD_WORK"."SYS_FUNCTION"               8.929 KB     137 rows
. . imported "DD_WORK"."SYS_FUNC_ROLE"              6.367 KB     249 rows
. . imported "DD_WORK"."SYS_OPERATE"                5.195 KB       6 rows
. . imported "DD_WORK"."SYS_ROLE"                   5.281 KB       9 rows
. . imported "DD_WORK"."SYS_SYSTEM"                 4.984 KB       1 rows
. . imported "DD_WORK"."SYS_USER_COOKIE"            6.953 KB      79 rows
. . imported "DD_WORK"."SYS_USER_ICON"              12.64 KB     336 rows
. . imported "DD_WORK"."SYS_USER_ROLE"              8.945 KB     347 rows
. . imported "DD_WORK"."HDS_COMMON_WORKER_TASK_REPEAT"      0 KB       0 rows
. . imported "DD_WORK"."HDS_EXCP_ADJT_WORKER_TASK"      0 KB       0 rows
. . imported "DD_WORK"."HDS_IA_SIGN_WORKER_TASK_TMP"      0 KB       0 rows
. . imported "DD_WORK"."HDS_POD_COMMON_WORKER_TASK"      0 KB       0 rows
. . imported "DD_WORK"."HDS_POD_RETURN_WORKER_TASK"      0 KB       0 rows
. . imported "DD_WORK"."HDS_SIGN_WORKER_TASK"           0 KB       0 rows
. . imported "DD_WORK"."HDS_SIGN_WORKER_TASK_NR"        0 KB       0 rows
. . imported "DD_WORK"."HDS_SIGN_WORKER_TASK_POD"       0 KB       0 rows
. . imported "DD_WORK"."HDS_VERIFY_WORKER_TASK"         0 KB       0 rows
. . imported "DD_WORK"."HDS_WORKER_TASK_GATE_TMP"       0 KB       0 rows
. . imported "DD_WORK"."NR_WOCODE"                      0 KB       0 rows
. . imported "DD_WORK"."SYS_FUNC_ROLE_OPERATE"          0 KB       0 rows
ORA-31693: Table data object "DD_WORK"."HDS_IA_SIGN_ORIG_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_03_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_03_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "DD_WORK"."HDS_ADV_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_09_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_09_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "DD_WORK"."HDS_SIGN_WORKER_TASK_DELR" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_01_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_01_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-31693: Table data object "DD_WORK"."HDS_SUPER_WORKER_TASK" failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file "/backup/DD_WORK_07_.dmp" for read
ORA-19505: failed to identify file "/backup/DD_WORK_07_.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
. . imported "DD_WORK"."HDS_LONG_NSIGN_WORKER_TASK"  84.43 MB 3309894 rows

可以发现报错了，因为导入的是RAC数据库，impdp 的时候需要加cluster=N

7、 总结
此次迁移有些大意，总是以为一个expdp，一个impdp，用户提前建好，问题就不大了，最后在实施的时候，才发现有很多地方没有考虑到，下面总结一下
1、前期准备的时候忘记考虑网络限制的问题，我们在129网段库迁移到127网段，有防火墙限制网络不通。 这个在迁移前发现，需要考虑两点：
      (1) 从129传输到127网段开通22端口的权限，scp传输dmp文件的时候需要 
      (2) 还有中间层应用程序调用数据库1521端口，之前是调用127网段，现在要开通127 网段的1521端口，需要统计应用的ip地址开通网络权限
      
2、此次迁移，是一个小库的数据迁移到另外一个已经运行很久的一个库中，空间没有提前规划好，在impdp导入数据前发现此问题，扩充表空间给迁移任务增加了时间开销
3、在impdp导入的时候发现报错，导入数找不到数据问题，但是数据文件明明已经在directory 目录下面了，权限 属主正确没有问题，
   最后发现没有考虑此次迁移是单机往RAC库中迁，使用parallel参数后，没有加参数cluster=N，导致并行导入后，会去另外一个节点的相同目录下面去找数据文件，导致的报错