更改tbm 10G undo表空间出现ora-00604 ora-00376 ora-01110错误(三)

最终重新启动Oracle已不起作用，reboot系统之后方可以连接上oracle。这时候可以mount上，但是似乎打开的时候或者操作的时候，显示Ora-00376  ora-01110之类的错误，显示无法读取'/oradata/qyp/qyp/UNDOTBS1_2'文件。

Errors in file /oracle/app/admin/qyp/bdump/qyp_smon_397700.trc:
ORA-00604: Message 604 not found; No message file for product=RDBMS, facility=ORA; arguments: [1]
ORA-00376: Message 376 not found; No message file for product=RDBMS, facility=ORA; arguments: [13]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [13] [/oradata/qyp/qyp/UNDOTBS1_2]
License high water mark = 20
All dispatchers and shared servers shutdown
Wed Mar 17 16:49:41 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Wed Mar 17 16:49:52 2010
ALTER DATABASE CLOSE NORMAL

看ALERT文件，反复启动了2次，每次都提示上述message信息，最后数据库启动成功：

  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1  相关的undo参数

Completed: ALTER DATABASE   MOUNT
Wed Mar 17 17:32:51 2010
alter database datafile '/oradata/qyp/qyp/UNDOTBS1_2' offline drop
Wed Mar 17 17:32:51 2010
Completed: alter database datafile '/oradata/qyp/qyp/UNDOTBS1_2' offline drop
Wed Mar 17 17:33:48 2010

至此，drop 掉该数据文件的操作才算完成。

这时候查询v$datafile（select file_name,status from dba_data_files;）,还有这个文件，显示状态为available,但是使用图形管理工具，这个文件的大小已经为0，说是需要媒体恢复，甚至连create pfile from spfile这类的操作也无法执行。中间试图过恢复该文件，（recover datafile '/oradata/qyp/qyp/UNDOTBS1_2';）没有效果，仍然提示：  

     Wed Mar 17 18:13:18 2010
Errors in file /oracle/app/admin/qyp/bdump/qyp_smon_336250.trc:
ORA-00376: Message 376 not found; No message file for product=RDBMS, facility=ORA; arguments: [13]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [13] [/oradata/qyp/qyp/UNDOTBS1_2]

       此时数据库仍无法进行常规操作。这种情况应该重新建立一个回滚表空间，然后设置为默认的回滚表空间，然后再摘除原来的回滚表空间，但是一步一个坎。在样例机上面执行同样的操作，创建一个新的数据文件给原来的回滚表空间，然后摘除该文件，再重建一个完全成功，也没有修改undo_management的值为manual，默认为auto，5分钟就建立完毕了。郁闷！哈哈，只好加班了！

create undo tablespace UNDO_RBS3 datafile '/oradata/qyp/qyp/undorbs3.dbf' size 512m
Wed Mar 17 18:55:08 2010
ORA-604 signalled during: create undo tablespace UNDO_RBS3 datafile '/oradata/qyp/qyp/undorbs3.dbf' size 512m...没有反应

运行select segment_name,tablespace_name,status from dba_rollback_segs;察看回滚段情况，发现从_syssmu11$--_syssmu19$这些段都是处于OFFLINE状态，_syssmu1$--_syssmu10$没有问题，状态为online，分析认为是这些offline的回滚段可能是在摘除的文件里面，里面还有未竞的事务。这些回滚段不能摘除。

       alter database recover datafile '/oradata/qyp/qyp/UNDOTBS1_2'; 恢复完文件后，可以生成pfile了，于是修改参数undo_management=’MANUAL’,同时增加隐含参数将出错的回滚段屏蔽：*._corrupted_rollback_segments=syssmu11$,_syssmu12$,_syssmu13$,_syssmu14$,_syssmu15$,_syssmu16$,_syssmu17$,_syssmu18$,_syssmu19$

    此时Shutdown immediate已经不能进行，必须使用abort，有点不安,毕竟还有其他系统上该机上运行,备份情况未知:

    Shutdown abort；

   Create spfile from pfile;成功

  打开数据库之后，可以建立create undo tablespace UNDO_RBS3 datafile '/oradata/qyp/qyp/undorbs3.dbf' size 512m; 查看alert 文件，操作终于成功，但是关于原undo表空间的错误依然存在： 

Wed Mar 17 20:37:08 2010
create undo tablespace UNDO_RBS3 datafile '/oradata/qyp/qyp/undorbs3.dbf' size 512m
Wed Mar 17 20:37:23 2010
Completed: create undo tablespace UNDO_RBS3 datafile '/oradata/qyp/qyp/undorbs3.dbf' size 512m

打开数据库，设置新建的undo表空间为系统默认undo表空间，同时摘除原有出错的undo表空间undorbs1：

Successfully onlined Undo Tablespace 14.
Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Wed Mar 17 21:03:52 2010
ALTER SYSTEM SET undo_tablespace='UNDO_RBS3' SCOPE=BOTH;
Wed Mar 17 21:04:12 2010

drop tablespace undorbs1 including contents
Wed Mar 17 21:04:12 2010

Wed Mar 17 21:04:12 2010
ORA-959 signalled during: drop tablespace undorbs1 including contents...
Wed Mar 17 21:04:34 2010
drop tablespace undotbs1 including contents
Wed Mar 17 21:04:34 2010
ORA-30013 signalled during: drop tablespace undotbs1 including contents...

出现错误，根据提示查找了下发现syssmu10$也是offline，结果就在隐含参数里面增加此段，再次启动之后，可以执行drop tablespace undo_rbs1 including contents，实际应该加上and datafiles;直接删除数据文件。

Wed Mar 17 21:05:14 2010
alter database open
Wed Mar 17 21:05:14 2010
ORA-1531 signalled during: alter database open...
Wed Mar 17 21:05:51 2010
drop tablespace undotbs1 including contents
Wed Mar 17 21:05:51 2010
ORA-30013 signalled during: drop tablespace undotbs1 including contents...
Wed Mar 17 21:08:20 2010
Errors in file /oracle/app/admin/qyp/bdump/qyp_smon_414174.trc:
ORA-00376: Message 376 not found; No message file for product=RDBMS, facility=ORA; arguments: [13]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [13] [/oradata/qyp/qyp/UNDOTBS1_2]
Wed Mar 17 21:08:21 2010
Undo Tablespace 1 moved to Pending Switch-Out state.

 终于舒了一口气：

  undo_management      = AUTO
  undo_tablespace          = UNDO_RBS3

Wed Mar 17 21:10:09 2010
Successfully onlined Undo Tablespace 14.

Completed: alter database open
Wed Mar 17 21:10:10 2010
Errors in file /oracle/app/admin/qyp/bdump/qyp_smon_418138.trc:
ORA-00376: Message 376 not found; No message file for product=RDBMS, facility=ORA; arguments: [13]
ORA-01110: Message 1110 not found; No message file for product=RDBMS, facility=ORA; arguments: [13] [/oradata/qyp/qyp/UNDOTBS1_2]

现在就想办法把出错的undo表空间undorbs1摘掉就可以了：

Completed: ALTER DATABASE OPEN
Wed Mar 17 21:18:02 2010
Starting background process CJQ0
CJQ0 started with pid=18, OS id=507978
Wed Mar 17 21:19:43 2010
drop tablespace undotbs1 including contents
Wed Mar 17 21:19:44 2010
Completed: drop tablespace undotbs1 including contents

终于执行成功。接下来扩展下undo_rbs3的大小：

Wed Mar 17 21:22:23 2010
ALTER DATABASE DATAFILE '/oradata/qyp/qyp/undorbs3.dbf' RESIZE 5120M
Wed Mar 17 21:24:43 2010
Completed: ALTER DATABASE DATAFILE '/oradata/qyp/qyp/undorbs3.dbf' RESIZE 5120M

 将隐含参数去掉，确保undo_management      = AUTO,至此终于可以回家了！

后记：

这个系统还是有大量的：

PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump

Memory Notification: Library Cache Object loaded into SGA
Heap size 2710K exceeds notification threshold (2048K)

Memory Notification: Library Cache Object loaded into SGA
Heap size 3622K exceeds notification threshold (2048K) 
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
Wed Mar 17 22:42:54 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 4013K exceeds notification threshold (2048K)
Details in trace file /oracle/app/admin/qyp/udump/qyp_ora_839842.trc
KGL object name :XDB.XD/HngR5i2kv7gM4IjoliS/g==
Thu Mar 18 03:30:02 2010
Starting background process EMN0
EMN0 started with pid=19, OS id=847906
Thu Mar 18 03:30:02 2010
Shutting down instance: further logons disabled
Thu Mar 18 03:30:02 2010
Stopping background process CJQ0
Thu Mar 18 03:30:02 2010
Stopping background process MMNL
Thu Mar 18 03:30:04 2010
Stopping background process MMON
Thu Mar 18 03:30:07 2010
Shutting down instance (immediate)
License high water mark = 16
All dispatchers and shared servers shutdown
Thu Mar 18 03:30:21 2010
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
Thu Mar 18 03:30:32 2010
ALTER DATABASE CLOSE NORMAL
Thu Mar 18 03:30:33 2010
SMON: disabling tx recovery

但是不是我们公司为主的项目，我们也不便过多的参与。第二天查看了下操作系统层面下的文件情况，告诉项目组成员哪些文件本就无用的，可以去除以腾出不少的空间。

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

转载于:http://blog.itpub.net/7177735/viewspace-676987/