昨天在参加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
[@more@]在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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18841027/viewspace-1052544/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18841027/viewspace-1052544/