今天,试了一把Data Pump和original Exopot and Import的实用性,在途中遇到的以下错误:
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 536
ORA-29283: invalid file operation
以下是关于上述错误的解释以及相关过程:
SQL> create directory dump_dir1 as '/u01/dump_dir1'; --创建默认目录
Directory created.
oracle@Ray:/u01>sqlplus / as sysdba
SQL> grant read,write on directory dump_dir1 to u1;--授予权限
Grant succeeded.
导出u1用户中一张名为u1TI的表。
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;--导出u1TI的一张表,默认目录为dump_dir1 dumpfile=u1TI.log
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 02:22:39 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658
上述报错貌似和表空间为panfeng的有关系,所以我们可以查看panfeng的表空间。
--根据上述数据,我们可以看到名为panfeng的表空闲空间为0%。所以我们需要打开默认增加表空间。
SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/panfeng01.dbf' autoextend on;
Database altered.
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:16:35 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 536
ORA-29283: invalid file operation
上述的“ORA-39070”提到的“Unable to open the log file.”初看非常的“诡异”,到底无法打开什么日志文件呢?难道是没有权限在这个目录下写文件?经过“touch”测试排除了这种可能性。
不管怎么说,这个问题与文件操作相关。顺着这个思路继续前行,终于发现原来数据库中的directory数据库对象所指向的目录为dump_dir1,而在该操作系统中根本没有这个目录,因目录不存在,日志文件也就理所当然的无处可写,所以,必须要在操作系统上建立相关目录。
oracle@Ray:/u01>mkdir dump_dir1;
那么怎么知道数据库中的directory数据库对象所指向的目录呢?我们可以查看下面视图:
导致该问题的潜在原因
在11g环境中即使在创建directory数据库对象的过程中即使所引用的目录不存在,该命令也是可以正常创建的,这就是容易误操作的根本原因。
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:22:07 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "U1"."SYS_EXPORT_TABLE_01": u1/******** dumpfile=u1TI.dmp directory=dump_dir6 tables=u1TI
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "U1"."U1TI" 5.421 KB 1 rows
Master table "U1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_TABLE_01 is:
/u01/u1TI.dmp
Job "U1"."SYS_EXPORT_TABLE_01" successfully completed at 03:24:07
如此,我们的表为u1TI就导出来了。
总结:在操作数据库时,一定要注意想清楚为什么?关于处理问题,首先要考虑为什么造成这样的错误,然后在分析定位。
欢迎大家批评指正:
QQ交流群:300392987
论 坛:http://www.oraclefreebase.com
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 536
ORA-29283: invalid file operation
SQL> create directory dump_dir1 as '/u01/dump_dir1'; --创建默认目录
Directory created.
oracle@Ray:/u01>sqlplus / as sysdba
SQL> grant read,write on directory dump_dir1 to u1;--授予权限
Grant succeeded.
导出u1用户中一张名为u1TI的表。
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;--导出u1TI的一张表,默认目录为dump_dir1 dumpfile=u1TI.log
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 02:22:39 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39006: internal error
ORA-39068: invalid master table data in row with PROCESS_ORDER=-3
ORA-01658: unable to create INITIAL extent for segment in tablespace PANFENG
ORA-39097: Data Pump job encountered unexpected error -1658
上述报错貌似和表空间为panfeng的有关系,所以我们可以查看panfeng的表空间。
点击(此处)折叠或打开
- SQL> select
- 2 f.tablespace_name,
- 3 a.total,
- 4 f.free,(a.total-f.free)/1024 \"used SIZE(G)\"
- 5 ,round((f.free/a.total)*100) \"% Free\"
- 6 from
- 7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
- 8 tablespace_name) a,
- 9 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
- 10 group by tablespace_name) f
- 11 WHERE a.tablespace_name = f.tablespace_name(+)
- 12 order by \"% Free\";
-
-
- TABLESPACE_NAME TOTAL FREE used SIZE(G) % Free
- ------------------------------ ---------- ---------- ------------ ----------
- PANFENG 30 0 .029296875 0
- SYSTEM 750 9 .723632813 1
- SYSAUX 720 42 .662109375 6
- EXAMPLE 337.5 27 .303222656 8
- UNDO01 50 31 .018554688 62
- UNDOTBS1 165 137 .02734375 83
- USERS 66.25 62 .004150391 94
- PANFENG2 50 49 .000976563 98
- RMAN_TBS 1024 1017 .006835938 99
-
-
- 9 rows selected.
--根据上述数据,我们可以看到名为panfeng的表空闲空间为0%。所以我们需要打开默认增加表空间。
SQL> alter database datafile '/u01/app/oracle/oradata/ora11g/panfeng01.dbf' autoextend on;
Database altered.
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:16:35 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing 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 536
ORA-29283: invalid file operation
上述的“ORA-39070”提到的“Unable to open the log file.”初看非常的“诡异”,到底无法打开什么日志文件呢?难道是没有权限在这个目录下写文件?经过“touch”测试排除了这种可能性。
不管怎么说,这个问题与文件操作相关。顺着这个思路继续前行,终于发现原来数据库中的directory数据库对象所指向的目录为dump_dir1,而在该操作系统中根本没有这个目录,因目录不存在,日志文件也就理所当然的无处可写,所以,必须要在操作系统上建立相关目录。
oracle@Ray:/u01>mkdir dump_dir1;
那么怎么知道数据库中的directory数据库对象所指向的目录呢?我们可以查看下面视图:
点击(此处)折叠或打开
- SQL>select directory_name,directory_path from dba_directories
- DIRECTORY_NAME DIRECTORY_PATH
- ------------------------------ --------------------------------------------------
- DUMP_DIR1 /u01/dump_dir1
- SUBDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
- entry//2002/Sep
- SS_OE_XMLDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
- entry/
- LOG_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/log/
- MEDIA_DIR /u01/app/oracle/products/11.2.0/demo/schema/produc
- t_media/
- DIRECTORY_NAME DIRECTORY_PATH
- ------------------------------ --------------------------------------------------
- XMLDIR /u01/app/oracle/products/11.2.0/rdbms/xml
- DATA_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/sales_
- history/
- DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/
- ORACLE_OCM_CONFIG_DIR /u01/app/oracle/products/11.2.0/ccr/state
在11g环境中即使在创建directory数据库对象的过程中即使所引用的目录不存在,该命令也是可以正常创建的,这就是容易误操作的根本原因。
oracle@Ray:/home/oracle>expdp u1/u1 directory=dump_dir1 dumpfile=u1TI.dmp logfile=u1TI.log tables=u1TI;
Export: Release 11.2.0.3.0 - Production on Sun Nov 17 03:22:07 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "U1"."SYS_EXPORT_TABLE_01": u1/******** dumpfile=u1TI.dmp directory=dump_dir6 tables=u1TI
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "U1"."U1TI" 5.421 KB 1 rows
Master table "U1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for U1.SYS_EXPORT_TABLE_01 is:
/u01/u1TI.dmp
Job "U1"."SYS_EXPORT_TABLE_01" successfully completed at 03:24:07
如此,我们的表为u1TI就导出来了。
总结:在操作数据库时,一定要注意想清楚为什么?关于处理问题,首先要考虑为什么造成这样的错误,然后在分析定位。
欢迎大家批评指正:
QQ交流群:300392987
论 坛:http://www.oraclefreebase.com
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29210156/viewspace-776797/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29210156/viewspace-776797/