记录一次impdp的过程

记录一次impdp的过程

前几天有一个测试用的库数据不完全，于是从其他地方恢复了一个测试库，使用效率比较高的impdp导入数据的方式，从AIX导入到一台Linux系统，但晚上头晕眼花的，操作比较急，准备工作没做好，碰到了一堆错误，记录一下。

首先删除原来的schema：

SQL> drop user erp;
drop user erp
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

用户连接上来后是不让删除的，先杀死这些连接：

SQL> select sid,serial#,username,machine from v$session where username=’ERP’;

SID    SERIAL# USERNAME                       MACHINE
———- ———- —————————— —————————————————————-
116      61069 ERP                         erp-app
142       7619 ERP                         erp-app
155      51216 ERP                         erp-app

SQL> alter system kill session ’116,61069′;

System altered.

SQL> alter system kill session ’142,7619′;

System altered.

SQL> alter system kill session ’155,51216′;

System altered.

然后尝试删除用户：

SQL> drop user erp;
drop user erp
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop ‘ERP’

SQL> drop user erp cascade;

User dropped.

重新建立新的用户：

create user ERP
identified by banping
default tablespace ERP
temporary tablespace TEMP
profile DEFAULT;
– Grant/Revoke role privileges
grant connect to ERP;
grant dba to ERP;
grant resource to ERP;
– Grant/Revoke system privileges
grant unlimited tablespace to ERP;

开始利用已有的dblink和Directory导入数据：

[oracle@erpdevdb ~]$ impdp system/banping SCHEMAS=(erp) directory=exp_dir network_link=dbbak89 logfile=exp_dir:impdp.log;

Import: Release 10.2.0.1.0 – 64bit Production on Monday, 21 December, 2009 21:15:25

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYSTEM”.”SYS_IMPORT_SCHEMA_03″:  system/******** SCHEMAS=(erp) directory=exp_dir network_link=dbbak89 logfile=exp_dir:impdp.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 98.51 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”ERP” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported “ERP”.”CONTRACTTBL”                      809213 rows

. . imported “ERP”.”ITEMMDLAPP”                       653772 rows
ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table ERP.CONTCOMM by 128 in tablespace ERP_HT

导到这里的时候表空间不足了，于是扩展表空间：

SQL> alter database datafile 5 resize 32767M;

Database altered.

SQL> ALTER TABLESPACE erp_ht ADD datafile ‘+DGPAPER’ SIZE 20480M;

Tablespace altered.

扩展完成，顺便说一句，表空间操作比较常遇到的错误是以下2个：

ORA-1237 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE ‘/dev/rdb_erp_ht2′ RESIZE  14288M…

ORA-1144 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE ‘/dev/rdb_erp_ht’ RESIZE  32768M…

第一个是没有足够的可分配空间，第二个是达到了单个数据文件的上限。

扩展完表空间后，这边的导入会继续进行，不用任何干预：

ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table ERP.CONTCOMM by 128 in tablespace ERP_HT
. . imported “ERP”.”CONTCOMM”                        1341222 rows

然后又碰到了一个错误：

ORA-31679: Table data object “ERP”.”CONTCOMM_BACK” has long columns, and longs can not be loaded/unloaded using a network link

没仔细研究这个错误，看来是个备份表，测试数据没关系，就忽略了。以后有空再看看，表导完后又碰到了一个错误：

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [INDEX:"CNDERP"."INDEX_STORELIST_UPISLCODE"]
ORA-01555: snapshot too old: rollback segment number 8 with name “_SYSSMU8$” too small
ORA-06512: at “SYS.DBMS_METADATA”, line 1546
ORA-06512: at “SYS.DBMS_METADATA”, line 1583
ORA-06512: at “SYS.DBMS_METADATA”, line 1891
ORA-06512: at “SYS.DBMS_METADATA”, line 3956

ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 105
ORA-06512: at “SYS.KUPW$WORKER”, line 6241

—– PL/SQL Call Stack —–
object      line  object
handle    number  name
0xdd7542e0     14916  package body SYS.KUPW$WORKER
0xdd7542e0      6300  package body SYS.KUPW$WORKER
0xdd7542e0      2340  package body SYS.KUPW$WORKER
0xdd7542e0      6861  package body SYS.KUPW$WORKER
0xdd7542e0      1262  package body SYS.KUPW$WORKER
0xdde87480         2  anonymous block

Job “SYSTEM”.”SYS_IMPORT_SCHEMA_03″ stopped due to fatal error at 00:24:11

Job已经停止了，看来是索引方面的问题，于是加了2个参数（忽略不可用的索引、跳过已存在的表）重新导：

[oracle@erpdevdb ~]$ impdp system/sys SCHEMAS=(erp) directory=exp_dir network_link=dbbak89 logfile=exp_dir:impdp.log SKIP_UNUSABLE_INDEXES=Y TABLE_EXISTS_ACTION=SKIP

于是在一堆ORA-31684、ORA-39151、ORA-39082和ORA-39083错误之中完成了导入：

ORA-39151: Table “ERP”.”BCODE” exists. All dependent metadata and data will be skipped due to table_exists_action of skip

2009/12/24 18:12 - Oracle

原文自：http://www.banping.com/2009/12/24/impdp/