impdp,expdp 可以使用network_link参数 在不用指定directory 也不用生成dump 文件的方式传送数据
今天群里有个同学遇到一个奇怪的问题
“impdp 用了network_link 但是schema下的其它表都可以导入,只有几张表导入时元数据可以导入就是无数据记录(0 row),而且没有任何错误”
下面我来还原那个问题
database version 11201 single instance, OS version OEL5.8,我是在一个实例的两个schema来模仿
1,TARGET DATABASE ,Configuration TNSNAMES.ora FILE
[oracle@rac1 ~]$ tnsping anbob
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 06-AUG-2012 20:35:45
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = anbob.com)))
OK (480 msec)
2,source database,create a test data table(hash partition table)
anbob@ANBOB>create table hash_obj(id int,name varchar2(20),ctime date)
2 partition by hash(id)
3 partitions 6;
Table created.
anbob@ANBOB>insert into hash_obj select rownum,'r'||rownum ,sysdate
2 from dual connect by rownum <10000;
9999 rows created.
anbob@ANBOB>commit;
3,TARGET DATABASE ,CREATE PUBLIC DATABASE LINK
sys@ANBOB>create public database link dl_pub_ab connect to anbob identified by anbob using 'anbob';
Database link created.
4,TARGET DATABASE ,VERIFY DBLINK ,make sure target database can use dblink access export schema tables
sys@ANBOB>select count(*) from anbob.obj@dl_pub_ab;
COUNT(*)
----------
10000
5,source database , privileges required
sys@ANBOB>grant IMP_FULL_DATABASE to anbob;
6,source database , create user for import
sys@ANBOB>create user weejar identified by weejar;
User created.
sys@ANBOB>grant connect,resource to weejar;
Grant succeeded.
7,target database, import to weejar schema from anbob schema
[oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Import: Release 11.2.0.1.0 - Production on Mon Aug 6 21:03:53 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 2520 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 2481 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 1283 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 1232 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 1188 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 1295 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:05:00
anbob@ANBOB>select owner,SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
2 from dba_objects where object_name='HASH_OBJ';
OWNER SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
ANBOB SYS_P21 80205 80205 TABLE PARTITION
ANBOB SYS_P22 80206 80206 TABLE PARTITION
ANBOB SYS_P23 80207 80207 TABLE PARTITION
ANBOB SYS_P24 80208 80208 TABLE PARTITION
ANBOB SYS_P25 80209 80209 TABLE PARTITION
ANBOB SYS_P26 80210 80210 TABLE PARTITION
ANBOB 80204 TABLE
WEEJAR SYS_P21 80426 80426 TABLE PARTITION
WEEJAR SYS_P22 80427 80427 TABLE PARTITION
WEEJAR SYS_P23 80428 80428 TABLE PARTITION
WEEJAR SYS_P24 80429 80429 TABLE PARTITION
WEEJAR SYS_P25 80430 80430 TABLE PARTITION
WEEJAR SYS_P26 80431 80431 TABLE PARTITION
WEEJAR 80425 TABLE
14 rows selected.
note:object_id=data_object_id
以上就是正常的现象,现在来做一些操作,展现问题
anbob@ANBOB>truncate table hash_obj;
Table truncated.
anbob@ANBOB>select owner,SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE
2 from dba_objects where object_name='HASH_OBJ';
OWNER SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ---------- -------------- -------------------
ANBOB 80204 TABLE
ANBOB SYS_P26 80210 80444 TABLE PARTITION
ANBOB SYS_P25 80209 80443 TABLE PARTITION
ANBOB SYS_P24 80208 80442 TABLE PARTITION
ANBOB SYS_P23 80207 80441 TABLE PARTITION
ANBOB SYS_P22 80206 80440 TABLE PARTITION
ANBOB SYS_P21 80205 80439 TABLE PARTITION
WEEJAR 80425 TABLE
WEEJAR SYS_P26 80431 80431 TABLE PARTITION
WEEJAR SYS_P25 80430 80430 TABLE PARTITION
WEEJAR SYS_P24 80429 80429 TABLE PARTITION
WEEJAR SYS_P23 80428 80428 TABLE PARTITION
WEEJAR SYS_P22 80427 80427 TABLE PARTITION
WEEJAR SYS_P21 80426 80426 TABLE PARTITION
note: note:object_id<>data_object_id
sys@ANBOB>conn weejar/weejar;
Connected.
weejar@ANBOB>drop table hash_obj purge;
Table dropped.
weejar@ANBOB>conn anbob/anbob
Connected.
anbob@ANBOB>insert into hash_obj select rownum,'r'||rownum ,sysdate
2 from dual connect by rownum <10000;
9999 rows created.
anbob@ANBOB>commit;
Commit complete.
anbob@ANBOB>select count(*) from hash_obj;
COUNT(*)
----------
9999
[oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Import: Release 11.2.0.1.0 - Production on Mon Aug 6 23:08:55 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 0 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 0 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 0 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 0 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 0 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 0 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 23:09:39
note:
明明有数据导入确是0 rows,对非分区表的truncate不影响
Cause:
expdp,impdp 使用network_link 传输分区表 object_id<>data_object_id时,bug 8692663
Solution:
打补丁或升到11202以上,也可以创建一个临时非分区表代替
下面打补丁,重试
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Patch 8692663 : applied on Mon Aug 06 23:42:54 PDT 2012
Unique Patch ID: 13818158
Created on 7 Jun 2011, 06:12:17 hrs PST8PDT
Bugs fixed:
8692663
这个补丁要关库更新一系列脚本,在我的配置超差的VM上跑了半个小时
[oracle@rac1 ~]$ impdp system/oracle remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Import: Release 11.2.0.1.0 - Production on Tue Aug 7 00:34:39 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** remap_schema=anbob:weejar schemas=anbob network_link=dl_pub_ab include=TABLE:"like 'HASH/_%' escape '/' "
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "WEEJAR"."HASH_OBJ":"SYS_P23" 2520 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P24" 2481 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P21" 1283 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P22" 1232 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P25" 1188 rows
. . imported "WEEJAR"."HASH_OBJ":"SYS_P26" 1295 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 00:36:23
ok,finished!