昨天在参加Oracle 研讨会时同事打电话说数据库登陆不上了，赶紧回来登录服务器，执行

Sqlplus / as sysdba

命令出现等等，系统无法进去，alert log最近日志 中也没异常警告。因不是关键业务，重启了数据库服务器，再启动oracle，数据库恢复正常。

Alert log内容如下所示：

Current log# 3 seq# 894 mem# 0: /oradata/pcs/redo03.log

Fri Jan 28 09:18:52 2011

WARNING: inbound connection timed out (ORA-3136)

Fri Jan 28 09:33:02 2011

WARNING: inbound connection timed out (ORA-3136)

Fri Jan 28 09:33:09 2011

WARNING: inbound connection timed out (ORA-3136)

Fri Jan 28 12:46:00 2011

MMNL absent for 2925 secs; Foregrounds taking over

Fri Jan 28 17:59:43 2011

/* OracleOEM */ ALTER DATABASE DATAFILE '/oradata/pcs/undotbs01.dbf' RESIZE 512M

Fri Jan 28 17:59:47 2011

Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/oradata/pcs/undotbs01.dbf' RESIZE 512M

Fri Jan 28 17:59:47 2011

/* OracleOEM */ ALTER DATABASE DATAFILE '/oradata/pcs/undotbs01.dbf' AUTOEXTEND OFF

Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/oradata/pcs/undotbs01.dbf' AUTOEXTEND OFF

Fri Jan 28 18:00:24 2011

在Metalink上发现如下文章

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3

This problem can occur on any platform.

Symptoms

Alert log shows the following messages :

Wed Oct 10 10:59:06 2007

MMNL absent for 293161 secs; Foregrounds taking over

Wed Oct 10 11:00:06 2007

MMNL absent for 293223 secs; Foregrounds taking over

Wed Oct 10 11:01:07 2007

MMNL absent for 293283 secs; Foregrounds taking over

Wed Oct 10 11:02:07 2007

MMNL absent for 293343 secs; Foregrounds taking over

Wed Oct 10 11:02:07 2007

MMNL absent for 293343 secs; Foregrounds taking over

MMNL absent for 293343 secs; Foregrounds taking over

MMNL absent for 293343 secs; Foregrounds taking over

Cause

The maxbytes of the datafiles was reduced lower than the bytes value.

FILE_NAME

--------------------------------------------------------------------------------

BYTES/1024/1024 TABLESPACE_NAME AUT MAXBYTES/1024/1024

--------------- ------------------------------ --- ------------------

/db/oradata/pcis/SYSSPACE001/system.dbf

700 SYSTEM YES .0078125

/db/oradata/pcis/UNDOTSPACE001/undotbs.dbf

5000 UNDOTBS YES .0078125

/db/oradata/pcis/SYSSPACE001/sysaux.dbf

500 SYSAUX YES .0078125

Maxbytes should not be allowed to be lower than bytes. This is infact a bug which has already been logged.

Bug 5470031 has been raised for this issue on how the maxsize is allowed to be smaller than the actual filesize using the command ALTER DATABASE DATAFILE ...AUTOEXTEND MAXSIZE

As of now, at the time of writing this note ( Oct'07 ) , this bug is still under development/resolution phase.

Solution

Either Turn off the autoextend option for the datafile or

Increase the Maxbytes to be greater than the bytes value.

References

Bug 5470031 - INCORRECT MAXSIZE ON UNDO DATAFILE LEADS TO "MMNL ABSENT FOR XX SECS; FOREGROUND

源文档 <http://cuuzhang.blog.163.com/blog/static/608115292008722101714200/>

因此我执行了如下操作：

select file_name,bytes/1024/1024 from dba_data_files

select * from dba_data_files

ALTER DATABASE

DATAFILE '/oradata/pcs/users01.dbf' RESIZE 256M;

ALTER DATABASE DATAFILE '/oradata/pcs/undotbs01.dbf' AUTOEXTEND OFF

ALTER DATABASE DATAFILE '/oradata/pcs/system01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/oradata/pcs/sysaux01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/oradata/pcs/users01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/oradata/pcs/card_idx_01.dbf' AUTOEXTEND OFF;

经过两天的观察发现，系统恢复了

Fri Jan 28 18:10:25 2011

ALTER DATABASE DATAFILE '/oradata/pcs/card_idx_01.dbf' AUTOEXTEND OFF

Completed: ALTER DATABASE DATAFILE '/oradata/pcs/card_idx_01.dbf' AUTOEXTEND OFF

Fri Jan 28 22:00:04 2011

Thread 1 advanced to log sequence 895

Current log# 1 seq# 895 mem# 0: /oradata/pcs/redo01.log

Sat Jan 29 00:14:42 2011

Thread 1 advanced to log sequence 896

Current log# 2 seq# 896 mem# 0: /oradata/pcs/redo02.log

Sat Jan 29 17:00:57 2011

Thread 1 advanced to log sequence 897

Current log# 3 seq# 897 mem# 0: /oradata/pcs/redo03.log

Sun Jan 30 08:20:03 2011

Thread 1 advanced to log sequence 898