ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g expdb autoextend tablespace and create directory error;

oracle 11g expdb autoextend tablespace and create directory error;

原创 Linux操作系统 作者:DB_BLOCK 时间:2013-11-17 20:38:05 0 删除 编辑
今天,试了一把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的表空间。

点击(此处)折叠或打开

  1. SQL> select
  2.   2 f.tablespace_name,
  3.   3 a.total,
  4.   4 f.free,(a.total-f.free)/1024 \"used SIZE(G)\"
  5.   5 ,round((f.free/a.total)*100) \"% Free\"
  6.   6 from
  7.   7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by
  8.   8 tablespace_name) a,
  9.   9 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
  10.  10 group by tablespace_name) f
  11.  11 WHERE a.tablespace_name = f.tablespace_name(+)
  12.  12 order by \"% Free\";


  13. TABLESPACE_NAME TOTAL FREE used SIZE(G) % Free
  14. ------------------------------ ---------- ---------- ------------ ----------
  15. PANFENG 30 0 .029296875 0
  16. SYSTEM 750 9 .723632813 1
  17. SYSAUX 720 42 .662109375 6
  18. EXAMPLE 337.5 27 .303222656 8
  19. UNDO01 50 31 .018554688 62
  20. UNDOTBS1 165 137 .02734375 83
  21. USERS 66.25 62 .004150391 94
  22. PANFENG2 50 49 .000976563 98
  23. RMAN_TBS 1024 1017 .006835938 99


  24. 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数据库对象所指向的目录呢?我们可以查看下面视图:

点击(此处)折叠或打开

  1. SQL>select directory_name,directory_path from dba_directories
  2. DIRECTORY_NAME DIRECTORY_PATH
  3. ------------------------------ --------------------------------------------------
  4. DUMP_DIR1 /u01/dump_dir1
  5. SUBDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
  6. entry//2002/Sep
  7. SS_OE_XMLDIR /u01/app/oracle/products/11.2.0/demo/schema/order_
  8. entry/
  9. LOG_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/log/
  10. MEDIA_DIR /u01/app/oracle/products/11.2.0/demo/schema/produc
  11. t_media/
  12. DIRECTORY_NAME DIRECTORY_PATH
  13. ------------------------------ --------------------------------------------------
  14. XMLDIR /u01/app/oracle/products/11.2.0/rdbms/xml
  15. DATA_FILE_DIR /u01/app/oracle/products/11.2.0/demo/schema/sales_
  16. history/
  17. DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/
  18. 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-09-25

  • 博文量
    12
  • 访问量
    125963