我们的ORACLE经常在半夜宕机,几次宕机的错误日志都是一样:
alert 日志内容如下:
Tue Apr 13 22:44:02 2010
Errors in file /oradata/admin/topprod/bdump/topprod_ckpt_2736222.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
Tue Apr 13 22:44:02 2010
Errors in file /oradata/admin/topprod/bdump/topprod_ckpt_2736222.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
Tue Apr 13 22:44:02 2010
CKPT: terminating instance due to error 221
Termination issued to instance processes. Waiting for the processes to exit
Tue Apr 13 22:44:12 2010
Instance termination failed to kill one or more processes
Instance terminated by CKPT, pid = 2736222.trc日志内容如下:
*** 2010-03-10 22:33:39.975
*** SERVICE NAME:(SYS$BACKGROUND) 2010-03-10 22:33:39.673
*** SESSION ID:(1649.1) 2010-03-10 22:33:39.673
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
error 221 detected in background process
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
*** 2010-03-10 22:33:49.993
Instance termination failed to kill one or more processes
ksuitm_check: OS PID=3080572 is still alive
*** 2010-03-10 22:33:49.993
Dumping diagnostic information for oracle@hlerp01 (PSP0):
OS pid = 3080572
loadavg : 2.37 2.67 2.63
swap info: free_mem = 761.10M rsv = 33.00M
alloc = 6296.27M avail = 8448.00M swap_free = 2151.73M
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
250004 Z oracle 3080572 1 0 60 20 0:00 <defunct>
open: The file access permissions do not allow the specified action.
procstack: 3080572 is a kernel process
*** 2010-03-10 22:33:51.400
在网上查了很多相关内容都无果,咨询了很多高手也没有得到一个确切的回答。
把我这点分全献出去,希望大伙能给点帮助!!!
alert 日志内容如下:
Tue Apr 13 22:44:02 2010
Errors in file /oradata/admin/topprod/bdump/topprod_ckpt_2736222.trc:
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
Tue Apr 13 22:44:02 2010
Errors in file /oradata/admin/topprod/bdump/topprod_ckpt_2736222.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
Tue Apr 13 22:44:02 2010
CKPT: terminating instance due to error 221
Termination issued to instance processes. Waiting for the processes to exit
Tue Apr 13 22:44:12 2010
Instance termination failed to kill one or more processes
Instance terminated by CKPT, pid = 2736222.trc日志内容如下:
*** 2010-03-10 22:33:39.975
*** SERVICE NAME:(SYS$BACKGROUND) 2010-03-10 22:33:39.673
*** SESSION ID:(1649.1) 2010-03-10 22:33:39.673
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
error 221 detected in background process
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 8
*** 2010-03-10 22:33:49.993
Instance termination failed to kill one or more processes
ksuitm_check: OS PID=3080572 is still alive
*** 2010-03-10 22:33:49.993
Dumping diagnostic information for oracle@hlerp01 (PSP0):
OS pid = 3080572
loadavg : 2.37 2.67 2.63
swap info: free_mem = 761.10M rsv = 33.00M
alloc = 6296.27M avail = 8448.00M swap_free = 2151.73M
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
250004 Z oracle 3080572 1 0 60 20 0:00 <defunct>
open: The file access permissions do not allow the specified action.
procstack: 3080572 is a kernel process
*** 2010-03-10 22:33:51.400
在网上查了很多相关内容都无果,咨询了很多高手也没有得到一个确切的回答。
把我这点分全献出去,希望大伙能给点帮助!!!
error in writing (block string, # blocks string) of control file
Cause: A disk I/O failure was detected on writing the control file.
Action: Check if the disk is online, if it is not, bring it online and try a warm start again. If it is online, then you need to recover the disk.
ORA-00206: error in writing (block 3, # blocks 1) of control file
ORA-00202: control file: '/oradata/oradata/topprod/control03.ctl'
ORA-27072: File I/O error
突然就发生这个情况的吗?还是之前有其他操作?现在数据库能否启动?
数据库之前有备份吗? 启动归档模式没有? 看你的log 日志,是在写contol03.ctl 的时候出现问题。 这个可能是这个数据块有问题。可以试试如下操作:
删除control03.ctl, 然后复制control01.ctl并重命名为control03.ctl。 在启动数据库看看。 ------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
猜测是某个大的事务导致的,我们的系统有半夜同步数据(MS-SQL从ORACLE转载数据)的动作。难道是这个导致?
数据库启动正常。我们的数据库是EPR系统生产用的,不敢轻易修改,您说的“删除control03.ctl, 然后复制control01.ctl并重命名为control03.ctl” 不敢轻易做啊,呵呵。
Applies to: Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.1.0.4, 10.2.0.1
AIX5L Based Systems (64-bit)PurposeAlert users to possible database crashes with error 221 unable to write to controlfile on AIX 5L. The error stack that is generated is:ORA-00221: error on write to controlfile
ORA-00206: error in writing (block 3, # blocks 1) of controlfile
ORA-00202: controlfile: '/u03/oradata/paimg/control03.ctl'
ORA-27072: File I/O error
IBM AIX RISC System/6000 Error: 22: Invalid argument
Additional information: 7Scope and ApplicationThis problem is specific to Oracle 10g on AIX 5L. Database crashes with ORA-221, ORA-206, ORA-202, ORA-27072Impact:The database impact is severe since it causes the database to crash. In addition this error is usually the result of a resource
exhaustion. So once it is encountered it is more than likely that this problem will be encountered again on the production
system. Workaround: None. However the default server settings for AIX are:MINIMUM number of servers [1]
MAXIMUM number of servers per cpu [100]
Maximum number of REQUESTS [4096]
This problem is improved slightly by changing the above values to something like:MINIMUM number of servers (minservers) [64]
MAXIMUM number of servers per cpu(maxservers) [256]
Maximum number of REQUESTS (maxreqs) [8192]Solution:Apply patch for bug 4401119ReferencesBUG:4401119 - Database Down With Controlfile Corruption
刚才也上metalink 查了一下。 应该是bug了。 This problem is specific to Oracle 10g on AIX 5L. Versions confirmed as being affected
10.1.0.2
10.1.0.3
10.1.0.4
10.2.0.1你的版本也是10.2.0.1的, 楼主打补丁吧. 要不升级一下数据库?------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 群:62697716(满); DBA2 群:62697977
又是异步IO,这个问题曾经把我郁闷够呛。
这是我的经历:
http://topic.csdn.net/u/20091207/22/fdd4ba6e-0365-4b91-872a-8a2e22f63594.html
已解决。
还有一种方法,
smit aio
选择 Change / Show Characteristics of Asynchronous I/O
把 Maximum number of REQUESTS 这个值加大,40960,重启AIX,也可以。
分析1:
Oracle10.2.0.1在aix5301系统上异步I/O的bug。
从网络上查找资料,需要打操作系统补丁,官网说明:
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ03260
补丁过程出现异常:
Prerequisite Number: 1
Fileset: bos.rte.aio
Minimal Level: 5.3.0.61
Maximum Level: 5.3.0.61
Actual Level: 5.3.0.60
Type: PREREQ
Requisite Met: no
emgr: 0645-050 Prerequisite number 1 did not pass all checks. Please see details above.
oslevel –s 显示:5300-06-01-0000,不知道网上提供的方法为啥不能用。
致电IBM,称不知道原方法。建议下载完整的SP4包(584M)
地址http://www-01.ibm.com/support/docview.wss?uid=isg1fixinfo105422
smit installp
oslevel –s : 5300-06-04-0748
以上见2009-06-08 停机记录
分析2:
OS后,定位oracle bug,Bug 4401119 AIX: Crash possible on AIX (ORA-206)
分析见:http://blog.oracle.com.cn/?action/viewspace/itemid/30578.html
opatch apply p4401119_10201_AIX64-5L
以上见2009-06-16 停机记录
分析3:
问题依旧,决定将oracle 10.2.0.1 升级 到 10.2.0.4
以上参见2009-07-24 停机记录。
现状:
已解决。请问是使用哪一种方案解决的?
将OS 升级到 53064 (53061打SP4补丁)
升级oracle到10.2.0.4
还有一种方法,
smit aio
选择 Change / Show Characteristics of Asynchronous I/O
把 Maximum number of REQUESTS 这个值加大,40960,重启AIX,也可以。这样真的可以吗??