ORA-00604,ORA-00376,ORA-01110

测试了一个回滚表空间意外损坏或者无法扩展的情况：

ORA-00604: error occurred at recursive SQL level string
Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).
Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support

ORA-00376: file string cannot be read at this time
Cause: attempting to read from a file that is not readable. Most likely the file is offline.
Action: Check the state of the file. Bring it online

ORA-01110: data file string: 'string'
Cause: Reporting file name for details of another error
Action: See associated error message

undo表空间不可用导致，测试一下先吧回滚表空间offline

SQL> conn / as sysdba
Connected.
SQL> alter database datafile '/tpdata/database/oradata/test/undotbs01.dbf' offline;
alter database datafile '/tpdata/database/oradata/test/undotbs01.dbf' offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error

SQL> conn test/test
Connected.
SQL> conn test/test
\Connected.
SQL> select * from v$log
SP2-0734: unknown command beginning "\select * ..." - rest of line ignored.
SQL> conn / as sysdba
Connected.
SQL> select file_name,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpdata/database/oradata/test/users01.dbf
AVAILABLE

/tpdata/database/oradata/test/sysaux01.dbf
AVAILABLE

/tpdata/database/oradata/test/undotbs01.dbf
AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpdata/database/oradata/test/system01.dbf
AVAILABLE

/tpdata/database/oradata/test/example01.dbf
AVAILABLE

/tpsys/oracle/product/10.2.0/db_1/test.ora
AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpsys/oracle/product/10.2.0/db_1/testbig.ora
AVAILABLE

/tpsys/oracle/product/10.2.0/db_1/test/test_resumable1.ora
AVAILABLE

8 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
         1          1        101   52428800          2 YES INACTIVE
      3276766 19-12?-09

         2          1        102   52428800          1 NO  CURRENT
      3307479 19-12?-09

         3          1        100   52428800          1 YES INACTIVE
      3251583 19-12?-09

状态还没有变，再另外一个session中执行一句insert，要使用undo表空间，
然后再看看：

SQL>
SQL> conn test/test
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/tpdata/database/oradata/test/undotbs01.dbf'

Warning: You are no longer connected to ORACLE.

SQL> conn / as sysdba
Connected.
SQL> select file_name,status from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpdata/database/oradata/test/users01.dbf
AVAILABLE

/tpdata/database/oradata/test/sysaux01.dbf
AVAILABLE

/tpdata/database/oradata/test/undotbs01.dbf
AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpdata/database/oradata/test/system01.dbf
AVAILABLE

/tpdata/database/oradata/test/example01.dbf
AVAILABLE

/tpsys/oracle/product/10.2.0/db_1/test.ora
AVAILABLE

FILE_NAME
--------------------------------------------------------------------------------
STATUS
---------
/tpsys/oracle/product/10.2.0/db_1/testbig.ora
AVAILABLE

/tpsys/oracle/product/10.2.0/db_1/test/test_resumable1.ora
AVAILABLE

8 rows selected.

这里有点问题，回滚表空间的状态应该是RECOVER，就这次发现是AVAILABLE，不解。

SQL> conn test/test
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/tpdata/database/oradata/test/undotbs01.dbf'

Warning: You are no longer connected to ORACLE.
SQL> alter database datafile '/tpdata/database/oradata/test/undotbs01.dbf' online;
SP2-0640: Not connected
SQL> conn /as sysdba
Connected.
SQL> alter database datafile '/tpdata/database/oradata/test/undotbs01.dbf' online;

Database altered.

SQL> conn test/test
Connected.
SQL>

online以后正常。

如果回滚表空间所在磁盘满了，或者回滚表空损坏，就有可能出现这个情况，如果是空间满了，可以扩大一点，
如果是损坏的话，可以先offline 然后再create一个回滚表空间，然后切过去就可以了。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-622775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-622775/