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

这里摘录一个案例，因为已经是半年多之前的事情了，有些细节已经回忆不起来，现在试图再现当时的处理过程。

远程数据库服务器版本为10.2.0.1.0  64位，运行在非归档模式下，操作系统为AIX系统 5，上面运行有tbm 和另外两个系统。项目组人员说系统磁盘空间已经很紧张,发现Oracle建立了62G的Undo表空间，想回缩但不释放空间，上来求助。如此简单的操作，那就开始进行吧。当时已经下班，没有什么用户连接，但是后来发生的事实出乎我的意料。   

    首先远程发送命令如下:

alter database datafile '/oradata/qyp/qyp/UNDOTBS1_2' offline drop;

    显示执行成功，但是似乎系统竟突然crash掉了，无法进行其它操作，我怀疑存储出了问题，询问项目人员说是之前没有做过什么操作!

      于是查询alert文件。之前先是连着两天凌晨启动多次，然后在上午发现之前的一个例行任务没有执行成功，所以中间9点多又反复启动了多次，看来数据库已经不大正常了。最后一次启动后还有7445错误出现：

Tue Mar 16 09:42:22 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 3594K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
Tue Mar 16 09:42:22 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 3567K exceeds notification threshold (2048K)
Details in trace file /oracle/app/admin/qyp/udump/qyp_ora_569750.trc
KGL object name :XDB.XD/HngR5i2kv7gM4IjoliS/g==

........

Tue Mar 16 13:39:19 2010
Thread 1 advanced to log sequence 576596
  Current log# 1 seq# 576596 mem# 0: /oradata/qyp/qyp/redo01.log
Tue Mar 16 13:39:20 2010
Errors in file /oracle/app/admin/qyp/udump/qyp_ora_901528.trc:
ORA-07445: exception encountered: core dump [] [] [] [] [] []

这个7445错误当时没有及时跟踪，接着往下看：

22点出现多次提示：

Tue Mar 16 22:00:36 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 2344K exceeds notification threshold (2048K)
KGL object name :select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring */ count(*),count(distinct "XSPRR"),sum(sys_op_opnsize("XSPRR")),substrb(dump(min("XSPRR"),16,0,32),1,120),substrb(dump(max("XSPRR"),16,0,32),1,120),count(distinct "XTERM"),sum(sys_op_opnsize("XTERM")),substrb(dump(min("XTERM"),16,0,32),1,120),substrb(dump(max("XTERM"),16,0,32),1,120),count(distinct "ZGR01"),sum(sys_op_opnsize("ZGR01")),substrb(dump(min("ZGR01"),16,0,32),1,120)

然后3点多又开始执行那个例行任务。该例行任务为从SAP系统中抽取数据，其字符集为WE8DEC，要强行改为简体中文ZHS16GBK，然后再切换回来。之前咨询过我，我告诉他们不能这样处理，你的系统本身已经存有一定的历史数据，这样做很危险。可以用两个应用，分别在客户端设置不同的字符集，这样就不会出现乱码了。可是项目组成员只剩维护的人员了，也不知道该怎么更改，就这样继续运行着。晕啊！

更改其专署索引空间：

Wed Mar 17 15:02:23 2010

ALTER DATABASE DATAFILE '/oradata/qyp/qyp/TBMINDEX.dbf' RESIZE 15000M
Wed Mar 17 15:04:51 2010
Completed: ALTER DATABASE DATAFILE '/oradata/qyp/qyp/TBMINDEX.dbf' RESIZE 15000M
Wed Mar 17 15:26:43 2010

不知道为什么此时数据库又重新启动了：

Stopping background process MMON
Wed Mar 17 15:26:46 2010
Shutting down instance (immediate)
License high water mark = 37
All dispatchers and shared servers shutdown
Wed Mar 17 15:26:58 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 15:27:10 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 15:27: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
Wed Mar 17 15:27:32 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 15:27:44 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 15:27:55 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 15:28:06 2010
PMON failed to acquire latch, see PMON dump
Wed Mar 17 15:28:09 2010
ALTER DATABASE CLOSE NORMAL

启动后紧接着又重新启动：

Starting ORACLE instance (normal)
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Wed Mar 17 15:33:04 2010
Starting ORACLE instance (normal)
Wed Mar 17 15:33:21 2010
Shutting down instance: further logons disabled
Wed Mar 17 15:33:21 2010
Stopping background process MMNL
Wed Mar 17 15:33:22 2010
Stopping background process MMON
Wed Mar 17 15:33:23 2010
Shutting down instance (immediate)
License high water mark = 2
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Wed Mar 17 15:33:29 2010
PMON failed to acquire latch, see PMON dump
Wed Mar 17 15:33:29 2010
ALTER DATABASE CLOSE NORMAL
Wed Mar 17 15:33:29 2010
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
Wed Mar 17 15:33:29 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes

重复启动了3次之后，项目组人员更改其专署临时表空间（真不明白，这种空间应用里是如何使用的），同时要缩小UNDO表空间的大小：

Wed Mar 17 15:37:43 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 3594K exceeds notification threshold (2048K)
KGL object name :XDB.XDbD/PLZ01TcHgNAgAIIegtw==
Wed Mar 17 15:37:44 2010
Memory Notification: Library Cache Object loaded into SGA
Heap size 3567K exceeds notification threshold (2048K)
Details in trace file /oracle/app/admin/qyp/udump/qyp_ora_8012046.trc
KGL object name :XDB.XD/HngR5i2kv7gM4IjoliS/g==
Wed Mar 17 15:38:46 2010
ALTER DATABASE TEMPFILE '/oradata/qyp/qyp/TBMTEMP.dbf' RESIZE 10240M
Wed Mar 17 15:39:39 2010
Completed: ALTER DATABASE TEMPFILE '/oradata/qyp/qyp/TBMTEMP.dbf' RESIZE 10240M
Wed Mar 17 15:40:56 2010
ALTER DATABASE DATAFILE '/oradata/qyp/qyp/undotbs01.dbf' RESIZE 100M
Wed Mar 17 15:40:56 2010
ORA-3297 signalled during: ALTER DATABASE DATAFILE '/oradata/qyp/qyp/undotbs01.dbf' RESIZE 100M...

数据库crash掉，重起了：

Wed Mar 17 16:09:47 2010
Starting background process EMN0
EMN0 started with pid=22, OS id=8052958
Wed Mar 17 16:09:47 2010
Shutting down instance: further logons disabled
Wed Mar 17 16:09:47 2010
Stopping background process MMNL
Wed Mar 17 16:09:47 2010
Stopping background process CJQ0
Wed Mar 17 16:09:50 2010
Stopping background process MMON
Wed Mar 17 16:09:51 2010
Shutting down instance (immediate)
License high water mark = 17
All dispatchers and shared servers shutdown
Wed Mar 17 16:10:02 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:10:14 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:10:25 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:10:37 2010
PMON failed to acquire latch, see PMON dump
Wed Mar 17 16:10:38 2010
ALTER DATABASE CLOSE NORMAL
Wed Mar 17 16:10:38 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Mar 17 16:10:38 2010
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 577921
Successful close of redo thread 1
Wed Mar 17 16:10:39 2010
Completed: ALTER DATABASE CLOSE NORMAL
Wed Mar 17 16:10:39 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Wed Mar 17 16:11:24 2010
Adjusting the default value of parameter parallel_max_servers
from 160 to 135 due to the value of parameter processes (150)
Wed Mar 17 16:11:24 2010
Starting ORACLE instance (normal)

还有3136 WARNING: inbound connection timed out，PMON failed to acquire latch, see PMON dump的信息，还有大量Thread 1 cannot allocate new log, sequenceXXX信息。

(未完待续)

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

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