[oracle@som som]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 3 07:26:46 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter database open;
alter database open
*
日志信息如下：
SMON: enabling cache recovery
Mon Oct 3 05:52:34 2011
Errors in file /u01/oracle/app/admin/som/udump/som_ora_4751.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oracle/app/oradata/som/undotbs01.dbf'
Mon Oct 3 05:52:34 2011
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Mon Oct 3 05:52:35 2011
Errors in file /u01/oracle/app/admin/som/bdump/som_pmon_4725.trc:
ORA-00604: error occurred at recursive SQL level
Instance terminated by USER, pid = 4751
ORA-1092 signalled during: alter database open... |
从日志信息来看，smon在做cache recovery时，需要读取回滚段数据，出现SQL level 1异常，这一般是oracle内部的递归sql。既然是数据字典类更改操作，也就是说使用了system回滚段。
如果是数据库open状态下报此错误，我们可以使用：alter system set event='00604 trace name errorstack,level 12' 跟踪相关sql，显然此时不使用。
trace文件信息：
[oracle@som som]$ cat /u01/oracle/app/admin/som/udump/som_ora_4751.trc
/u01/oracle/app/admin/som/udump/som_ora_4751.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/oracle/app/product/10.2.0/db_1
System name: Linux
Node name: som
Release: 2.6.9-5.EL
Version: #1 Wed Jan 5 19:22:18 EST 2005
Machine: i686
Instance name: som
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 4751, image: oracle@som (TNS V1-V3)
*** 2011-10-03 05:52:33.752
*** SERVICE NAME:() 2011-10-03 05:52:33.750
*** SESSION ID:(159.3) 2011-10-03 05:52:33.750
Thread 1 checkpoint: logseq 21, block 2, scn 1324156
cache-low rba: logseq 14, block 3
on-disk rba: logseq 14, block 39, scn 1184004
start recovery at logseq 21, block 2, scn 1324156
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 10Kb in 0.24s => 0.04 Mb/sec
Total physical reads: 4096Kb
Longest record: 0Kb, moves: 0/22 (0%)
Longest LWN: 1Kb, moves: 0/11 (0%), moved: 0Mb
Last redo scn: 0x0000.00143492 (1324178)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 3/3 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 38/41 = 0.9
----------------------------------------------
*** 2011-10-03 05:52:34.006
KCRA: start recovery claims for 3 data blocks
*** 2011-10-03 05:52:34.008
KCRA: blocks processed = 3/3, claimed = 3, eliminated = 0
*** 2011-10-03 05:52:34.009
Recovery of Online Redo Log: Thread 1 Group 1 Seq 21 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 3/3 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 41/41 = 1.0
----------------------------------------------
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/oracle/app/oradata/som/undotbs01.dbf'
|
SQL> startup mount
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ---------------------- --------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/app/oradata/som/system01.dbf
/u01/oracle/app/oradata/som/undotbs01.dbf
/u01/oracle/app/oradata/som/sysaux01.dbf
/u01/oracle/app/oradata/som/users01.dbf
/u01/oracle/app/oradata/som/example01.dbf
/u01/oracle/app/oradata/som/test.dbf
6 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
TEST
7 rows selected.
由于此时数据库不能open，数据字典dba_rollback_segs当然也不能查询，这个时候需要查询系统回滚段可以使用以下方法：
|
$strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
_SYSSMU1
_SYSSMU1
_SYSSMU10
_SYSSMU11
_SYSSMU12
_SYSSMU13
_SYSSMU14
_SYSSMU15
_SYSSMU16
_SYSSMU17
_SYSSMU18
_SYSSMU19
_SYSSMU2
_SYSSMU2
_SYSSMU20
_SYSSMU3
_SYSSMU3
_SYSSMU4
_SYSSMU4
_SYSSMU5
_SYSSMU5
_SYSSMU6
_SYSSMU6
_SYSSMU7
_SYSSMU7
_SYSSMU8
_SYSSMU8
_SYSSMU8
_SYSSMU9
_SYSSMU9
SYSTEM _SYSSMU9
|
SQL> create pfile from spfile;
修改pfile文件，加入下面一行：
|
_corrupted_rollback_segments=(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$,_SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$,_SYSSMU11$, _SYSSMU12$, _SYSSMU13$, _SYSSMU14$, _SYSSMU15$, _SYSSMU16$,_SYSSMU17$, _SYSSMU18$, _SYSSMU19$, _SYSSMU20$) |
SQL> startup force
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
数据库已经OPEN了，下面是一些后续工作，重建undo空间，修改undo_tablespace参数。
SQL> create undo tablespace undotbs01 datafile '/u01/oracle/app/oradata/som/undo001.dbf' size 50m;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/oracle/app/oradata/som/system01.dbf
/u01/oracle/app/oradata/som/undotbs01.dbf
/u01/oracle/app/oradata/som/sysaux01.dbf
/u01/oracle/app/oradata/som/users01.dbf
/u01/oracle/app/oradata/som/example01.dbf
/u01/oracle/app/oradata/som/test.dbf
/u01/oracle/app/oradata/som/undo001.dbf
7 rows selected.
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
TEST
UNDOTBS01
8 rows selected.
SQL> create spfile from pfile;
SQL> alter system set undo_tablespace=undotbs01;
SQL> drop tablespace undotbs1 including contents and datafiles;
(责任编辑：OracleTT) |
