记录一次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导入数据：

[[email protected] ~]$ 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 ALTER DATABASE DATAFILE ‘/dev/rdb_erp_ht2′ RESIZE  14288M…

ORA-1144 signalled during: 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个参数（忽略不可用的索引、跳过已存在的表）重新导：

[[email protected] ~]$ 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/