Oracle DataPump相对于传统的exp/imp做了很多的改善。比如通过链路NETWORK_LINK 导入方式,不需要导出和传送过程只需要一个IMPDP 命令就可以了。而且也增加了QUERY功能。
例子:
impdp system/password NETWORK_LINK=NEW_LINK DIRECTORY=DP_DIR logfile=jzdb_all_0106.log FULL=Y EXCLUDE=VIEW:"IN (select object_name from dba_objects where object_type='VIEW' and owner IN ('USER1','USER2','USER3'))" PARALLEL=2 JOB_NAME=TESTNOVIEWS;
这里是通过NEW_LINK把除了USER1、USER2、USER3的视图以为的对象及数据导入到当前用户下。
[@more@]前几天数据库由于存储非法关闭以后造成数据库无法启动,强制把数据库打开以后想通过数据泵备份出数据,但是执行数据泵导出时没有报任何的错误直接就断了,跳到操作系统$符。没有报任何的异常也没有办法,只好启动原始的exp操作,但是exp同样不行,好在exp抛出了一些异常:
$ exp system/password GRANTS=N ROWS=Y INDEXES=N FILE=/oracle/idata/dbdata/all_0106.dmp log=/oracle/idata/dbdata/all_0106.log TRIGGERS=N CONSTRAINTS=N FULL=Y COMPRESS=N
Export: Release 10.2.0.4.0 - Production on Thu Jan 6 17:59:39 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "WMSYS.LT_EXPORT_PKG", line 722
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling WMSYS.LT_EXPORT_PKG.system_info_exp
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 580
ORA-06512: at "SYS.DBMS_SCHED_WINDOW_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_WINDOW_EXPORT.create
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 580
ORA-06512: at "SYS.DBMS_SCHED_WINDOW_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_WINDOW_EXPORT.create
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 928
ORA-06512: at "SYS.DBMS_SCHED_CLASS_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_CLASS_EXPORT.create_
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 928
ORA-06512: at "SYS.DBMS_SCHED_CLASS_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_CLASS_EXPORT.create_
EXP-00008: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_SCHED_MAIN_EXPORT", line 868
ORA-06512: at "SYS.DBMS_SCHED_WINGRP_EXPORT", line 14
ORA-06512: at line 1
EXP-00085: The previous problem occurred when calling SYS.DBMS_SCHED_WINGRP_EXPORT.create
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully
有异常就好办了,检查一下异常,发现ORA-25153: Temporary Tablespace is Empty,到数据库里面一查,果然发现临时表空间都是空的。给临时表空间添加数据文件,再次尝试使用数据泵expdp果然可以了。还是传统的exp能暴露异常。
之前数据库导出的时候老是抛出以下异常:
$ expdp system/jzdb directory=DATA_PUMP_DIR dumpfile=gx_zsb1223%U.dp filesize=2g
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 December, 2010 23:
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_08": system/******** directory=DATA_PUMP_Dsb,jzpt_gx_glff_zsb
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.526 GB
Processing object type SCHEMA_EXPORT/USER
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-00600: internal error code, arguments: [qmtInit1], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 1397
ORA-06512: at line 2
Job "SYSTEM"."SYS_EXPORT_SCHEMA_08" stopped due to fatal error at 23:20:46
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-00600: internal error code, arguments: [qmtInit1], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 1397
ORA-06512: at line 2
Job "SYSTEM"."SYS_EXPORT_SCHEMA_05" stopped due to fatal error at 23:26:57
ORA-39014: One or more workers have prematurely exited.
老是会报ORA-00600内部错误,在网上查发现都是说由于环境变量设置错误引起的,64位的操作系统,却使用$ORACLE_HOME/lib32在环境变量的前面。而且需要重启数据库,由于数据库和小机都是由其他公司管理的,所以也不好办,就一直没管。
但是后来数据库崩溃后重建,环境变量还是这样设置,但是现在需要把之前备份出来的数据重新恢复回去。
$ impdp system/password DIRECTORY=DB_DP_DIR dumpfile=jcpt_dataonly_0106_%U.dp logfile=jcpt_dataonly_0107_imp.log JOB_NAME=impjcptdata
Import: Release 10.2.0.4.0 - 64bit Production on Friday, 07 January, 2011 18:44:03
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Master table "SYSTEM"."IMPJCPTDATA" successfully loaded/unloaded
Starting "SYSTEM"."IMPJCPTDATA": system/******** DIRECTORY=DB_DP_DIR dumpfile=jcpt_dataonly_0106_%U.dp logfile=jcpt_dataonly_0107_imp.log JOB_NAME=impjcptdata
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JZPT_GX_JCPT_ZSB"."DICTIONARY" 29.32 MB 396082 rows
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW01" prematurely terminated
ORA-31671: Worker process DW01 had an unhandled exception.
ORA-00600: internal error code, arguments: [unable to load XDB library], [], [], [], [], [], [], []
ORA-06512: at "SYS.KUPW$WORKER", line 1397
ORA-06512: at line 2
Job "SYSTEM"."IMPJCPTDATA" stopped due to fatal error at 18:44:38
$
没有办法,按照我们公司提供的安装文档,重新设置环境变量:
NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'
export NLS_LANG
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORA_CRS_HOME/lib:$ORA_CRS_HOME/lib32
export LD_LIBRARY_PATH
重启数据库,重启监听,重新导入,导入成功。
这里还要注意一下,从小机上把备份文件拿下到windows机器上最好使用专门的ftp工具,如果直接使用的是dos直接ftp上去拿的话要注意使用二进制传输,不然会报下面的错误:
C:>impdp system/gxjzywk dumpfile=glff_dataonly_0106_%U.dp logfile=glff_dataonly
_0106_in.log INCLUDE=TABLE:"= 'B_ZDRYGK_ZDRYXX_MID'" schemas=JZPT_GX_GLFF_ZSB
remap_schema=JZPT_GX_GLFF_ZSB:DZK_JK
Import: Release 10.2.0.4.0 - Production on 星期四, 06 1月, 2011 21:50:27
Copyright (c) 2003, 2007, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: 参数值无效
ORA-39000: 转储文件说明错误
ORA-31619: 转储文件 "D:oracleproduct10.2.0adminjzywkdpdumpglff_dataonly_0
106_01.dp" 无效
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24930246/viewspace-1044344/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24930246/viewspace-1044344/