十万火急抢导入 PRD oacle 数据库

今天应用部门给了个dump 文件，需要导入到另外一个系统中。

 

impdp vsop/vsop@sfepet directory=dump_dir dumpfile=dump.dmp full=y;

目的： 导入创建的schema = vsop 下数据。

 

报错：

Import: Release 10.2.0.3.0 - 64bit Production on Friday, 04 June, 2010 14:39:16

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

排错：
1：发现 dump_dir 下的路径根本就不存在：

在10g环境中即使在创建directory数据库对象的过程中即使所引用的目录不存在，该命令也是可以正常创建的，这就是容易误操作的根本原因。

sys@ora10g> create directory dpump_dir_test as '/sec/ool/er';

Directory created.

 

小心陷阱。

可以看建好的directory:

select * from dba_directories where DIRECTORY_NAME = 'DUMP_DIR';

2:发现问题后，处理方法就简单了许多，只需要重新创建directory数据库对象即可。

sys@sec> drop directory dpump_dir;

Directory dropped.

 

sys@sec> create directory dpump_dir as '/vsopshare/dump_dir';

Directory created.

 

sys@sec> grant read, write on directory dpump_dir to public;

Grant succeeded.

 

select * from dba_directory

 

------

 

下面继续 导入数据，发现还是保错，我又检查了一下文件夹：

ls -l vsopshare
drwxr-xr-x    2 221      usr             256 Jan 06 09:31 vsop_expdp

 

没有写权限，把它改成：
drwxrwxrwx    2 221      usr             256 Jan 06 09:31 vsop_expdp

 

再试一下，报错不一样，继续...

impdp vsop/vsop@sfeqas directory=dump_dir dumpfile=t_sell.dpdmp full=y logfile=....

 

结果报can not open log file,

好，把logfile 去掉，继续。。。

 

结果错误改变：can not find the tablespace : vsop

 

（结果，我去emconsole 控制台去看了一下，确实没有vsop tablespace, 只有： tbs_sfe_data）

 

好，把导入命令改成如下：

 

impdp vsop/vsop@sfeqas directory=dump_dir dumpfile=t_sell.dpdmp full=y REMAP_TABLESPACE=vsop:tbs_sfe_data;

 

成功，但是，刚才的操作建了一些表，可以通过以下来避免：

 

impdp vsop/vsop@sfeqas directory=dump_dir dumpfile=t_sell.dpdmp full=y REMAP_TABLESPACE=vsop:tbs_sfe_data TABLE_EXISTS_ACTION=TRUNCATE;

 

在imp 的过程中要停掉操作的话，可以ctrol + C

 

在另外开一个窗口，看 status:

select * from dba_datapump_jobs;

会发现：no running

 

当然可以开始不建 index:

impdp vsop/vsop@sfeqas directory=dump_dir dumpfile=t_sell.dpdmp full=y REMAP_TABLESPACE=vsop:tbs_sfe_data TABLE_EXISTS_ACTION=TRUNCATE indexes=no;

 

-----

 

数据导好后，可以在默认路径下看到log:

 

在刚才的directory : /vsopshare/dump_dir 下有log:

hgqsfe01:[/vsopshare/dump_dir]#ls -l

-rw-r--r--    1 oracle   dba            1587 Jan 06 10:08 import.log

 

hgqsfe01:[/vsopshare/vsop_expdp]#more import.log
;;;
Import: Release 11.2.0.1.0 - Production on Thu Jan 6 10:06:08 2011

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
;;; Legacy Mode Active due to the following parameters:
;;; Legacy Mode Parameter: "indexes=FALSE" Location: Command Line, Replaced with: "exclude=index"
Master table "VSOP"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "VSOP"."SYS_IMPORT_FULL_01":  vsop/********@sfeqas directory=dump_dir dumpfile=t_sell.dpdmp full=y REMAP_TABLESPACE=vsop:tb
s_sfe_data exclude=index TABLE_EXISTS_ACTION=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "VSOP"."T_SELL" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due t
o table_exists_action of truncate
ORA-39153: Table "VSOP"."T_SELL_OUT" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped d
ue to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VSOP"."T_SELL"                             8.117 GB 207507412 rows
. . imported "VSOP"."T_SELL_OUT"                         143.0 MB 1485248 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "VSOP"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 10:08:13

以上显示成功：可以check 一下line 来看是否ok。

注明： 学习impdp 的用法： impdp help=y 来看。