Oracle DataPump之二

最新推荐文章于 2021-05-26 10:18:43 发布

cuchengjiao8603

最新推荐文章于 2021-05-26 10:18:43 发布

阅读量177

点赞数

文章标签：数据库操作系统

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

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:

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

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

连接到: 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/

cuchengjiao8603

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle DataPump之二

Oracle DataPump相对于传统的exp/imp做了很多的改善。比如通过链路NETWORK_LINK 导入方式，不需要导出和传送过程只需要一个IMPDP 命令就可以了。而且也增加了QUERY功能。例子：impdp sys...
复制链接

扫一扫