記錄一次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/