今天在Maclean Liu群里，一个网友查询表的时候遇到了 ORA-01110,ORA-15081错误。
环境是4节点的11G RAC环境。
详细错误描述如下：
数据库环境描述：
操作系统版本： Oracle Enterprise Linux 5.5 64bit
数据库版本 ： Oracle 11.2.0.1
数据库类型 ： RAC
节 点 数 : 4个
1、客户端连接数据库 现象如下：
2012 12:59:32
Copyright (c) 1997, 2010, Oracle. All rights reserved.
已使用的参数文件:
c:\app\swgsw\product\11.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (LOAD_BALANCE = on)(FAILOVER = on) (ADDRESS = (PROTOCOL
= TCP)(HOST = 10.65.99.14)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.6
5.99.15)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.65.99.16)(PORT = 15
21)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.65.99.17)(PORT = 1521)) (CONNECT_DATA
= (SERVER = DEDICATED) (SERVICE_NAME = bxrac) (FAILOVER_MODE = (TYPE = SELECT)(
METHOD = BASIC)(RETRIES = 180))))
OK (30 毫秒)
C:\Users\swgsw>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期五 4月 27 12:59:34 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn
已连接。
SQL> show sga
Total System Global Area 1603411968 bytes
Fixed Size 2213776 bytes
Variable Size 1040189552 bytes
Database Buffers 553648128 bytes
Redo Buffers 7360512 bytes
SQL> select * from fgrzrxx ; (为业务数据表)
select * from fgrzrxx
*
第 1 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01115: 从文件 读取块时出现 IO 错误 (块 # )
ORA-01110: 数据文件 1: '+DG1/bxrac/datafile/system.259.771948277'
ORA-15081: 无法将 I/O 操作提交到磁盘
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01115: 从文件 读取块时出现 IO 错误 (块 # )
ORA-01110: 数据文件 1: '+DG1/bxrac/datafile/system.259.771948277'
ORA-15081: 无法将 I/O 操作提交到磁盘
2、ASM alert ：详见附件 asm_alert.log
DB alert : 详见附件 db_alert.log
2.1 操作系统层面 ：
ASM 使用的是 裸设备 /dev/raw/raw
各个节点 裸设备的 权限分配如下：
[root@bxrac02 ~]# ssh bxrac01 ls -al /dev/raw/*
crw-rw---- 1 grid dba 162, 1 Apr 27 13:20 /dev/raw/raw1
crw-rw---- 1 grid dba 162, 2 Apr 27 13:18 /dev/raw/raw2
crw-rw---- 1 grid dba 162, 3 Apr 27 13:14 /dev/raw/raw3
crw-rw---- 1 grid dba 162, 4 Apr 27 13:20 /dev/raw/raw4
crw-rw---- 1 grid dba 162, 5 Apr 27 13:20 /dev/raw/raw5
crw-rw---- 1 grid asmadmin 162, 6 Apr 27 13:20 /dev/raw/raw6
crw-rw---- 1 grid asmadmin 162, 7 Apr 27 12:03 /dev/raw/raw7
crw-rw---- 1 grid dba 162, 8 Apr 27 13:20 /dev/raw/raw8
crw-rw---- 1 grid dba 162, 9 Apr 27 12:02 /dev/raw/raw9
[root@bxrac02 ~]# ssh bxrac02 ls -al /dev/raw/*
crw-rw---- 1 grid dba 162, 1 Apr 27 13:20 /dev/raw/raw1
crw-rw---- 1 grid dba 162, 2 Apr 27 13:17 /dev/raw/raw2
crw-rw---- 1 grid dba 162, 3 Apr 27 13:00 /dev/raw/raw3
crw-rw---- 1 grid dba 162, 4 Apr 27 13:20 /dev/raw/raw4
crw-rw---- 1 grid dba 162, 5 Apr 27 13:00 /dev/raw/raw5
crw-rw---- 1 grid asmadmin 162, 6 Apr 27 13:20 /dev/raw/raw6
crw-rw---- 1 grid asmadmin 162, 7 Apr 27 12:03 /dev/raw/raw7
crw-rw---- 1 grid dba 162, 8 Apr 27 13:17 /dev/raw/raw8
crw-rw---- 1 grid dba 162, 9 Apr 27 12:02 /dev/raw/raw9
[root@bxrac02 ~]# ssh bxrac03 ls -al /dev/raw/*
crw-rw---- 1 grid dba 162, 1 Apr 27 13:20 /dev/raw/raw1
crw-rw---- 1 grid dba 162, 2 Apr 27 13:20 /dev/raw/raw2
crw-rw---- 1 grid dba 162, 3 Apr 27 12:36 /dev/raw/raw3
crw-rw---- 1 grid dba 162, 4 Apr 27 13:20 /dev/raw/raw4
crw-rw---- 1 grid dba 162, 5 Apr 27 13:20 /dev/raw/raw5
crw-rw---- 1 grid asmadmin 162, 6 Apr 27 13:20 /dev/raw/raw6
crw-rw---- 1 grid asmadmin 162, 7 Apr 27 12:35 /dev/raw/raw7
crw-rw---- 1 grid dba 162, 8 Apr 27 13:18 /dev/raw/raw8
crw-rw---- 1 grid dba 162, 9 Apr 27 12:02 /dev/raw/raw9
[root@bxrac02 ~]# ssh bxrac04 ls -al /dev/raw/*
crw-rw---- 1 grid dba 162, 1 Apr 27 13:20 /dev/raw/raw1
crw-rw---- 1 grid dba 162, 2 Apr 27 13:19 /dev/raw/raw2
crw-rw---- 1 grid dba 162, 3 Apr 27 13:20 /dev/raw/raw3
crw-rw---- 1 grid dba 162, 4 Apr 27 13:20 /dev/raw/raw4
crw-rw---- 1 grid dba 162, 5 Apr 27 13:00 /dev/raw/raw5
crw-rw---- 1 grid asmadmin 162, 6 Apr 27 13:20 /dev/raw/raw6
crw-rw---- 1 grid asmadmin 162, 7 Apr 27 12:35 /dev/raw/raw7
crw-rw---- 1 grid dba 162, 8 Apr 27 13:20 /dev/raw/raw8
crw-rw---- 1 grid dba 162, 9 Apr 27 12:02 /dev/raw/raw9
[grid@bxrac02 ~]$ srvctl status diskgroup -g DG1 -a
Disk Group DG1 is running on bxrac01,bxrac02,bxrac03,bxrac04
Disk Group DG1 is enabled
ASM 相关信息：
SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,NAME,PATH from v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU NAME PATH
------------ ----------- ------- ------------ ------------------------------ ------------------------------
0 1 CLOSED MEMBER /dev/raw/raw9
3 1 CACHED MEMBER OCRVDG_0001 /dev/raw/raw7
3 0 CACHED MEMBER OCRVDG_0000 /dev/raw/raw6
2 2 CACHED MEMBER DG2_0002 /dev/raw/raw5
2 1 CACHED MEMBER DG2_0001 /dev/raw/raw4
2 0 CACHED MEMBER DG2_0000 /dev/raw/raw3
1 1 CACHED MEMBER DG1_0001 /dev/raw/raw2
1 0 CACHED MEMBER DG1_0000 /dev/raw/raw1
1 2 CACHED MEMBER DG1_0002 /dev/raw/raw8
SQL> select group_number, name, state, type, total_mb, free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME STATE TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ----------- ------ ---------- ----------
1 DG1 MOUNTED EXTERN 102398 1
2 DG2 MOUNTED EXTERN 153597 40303
3 OCRVDG MOUNTED EXTERN 2046 1564
alert日志如下有如下错误：
WARNING: kfk failed to open a disk[/dev/raw/raw2]
Errors in file /u01/app/oracle/diag/rdbms/bxrac/bxrac1/trace/bxrac1_ora_9215.trc:
ORA-15025: 鑴﹁劶璺?瘬楹撹矊椹撮檵楹撹劀鑴滆劋 '/dev/raw/raw2'
ORA-27041: 鑴﹁劶璺?瘬楹撹矊椹撮檵鑴﹁剾褰曢晛
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
WARNING: kfk failed to open a disk[/dev/raw/raw1]
Errors in file /u01/app/oracle/diag/rdbms/bxrac/bxrac1/trace/bxrac1_ora_9215.trc:
ORA-15025: 鑴﹁劶璺?瘬楹撹矊椹撮檵楹撹劀鑴滆劋 '/dev/raw/raw1'
ORA-27041: 鑴﹁劶璺?瘬楹撹矊椹撮檵鑴﹁剾褰曢晛
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
ORA-00604: 纰岃劵楣胯寘 SQL 褰曢湶鍗ゅ啋 1 椴佹灇鑴ц劙楹撻搯鑴﹁锤
ORA-01115: 楹撹劔鑴﹁剾褰曢晛 闇茶剷鑴犻殕椹磋寘鑴㈠崵椴佹灇鑴ц劙 IO 楹撻搯鑴﹁锤 (椹磋寘 # )
ORA-01110: 鑴㈠獟鎴?劵鑴﹁剾褰曢晛 1: '+DG1/bxrac/datafile/system.259.771948277'
ORA-15081: 鑴﹁劶璺?瘬闄嗚姦 I/O 铏忚劦鑴虫?鑴よ癌闄嗙?纰岄檰楹撹劀鑴滆劋
在经过了一番排查后，感觉和MOS [ID 1378747.1] 这篇文章描述的很像。
最后通过如下方法，问题得以解决：
1、 /raw/raw1-9 的权限应该均为 grid:asmadmin
2、 在四个节点上用 root 均运行：
[root@bxrac02 ~]# /u01/app/11.2.0/grid/bin/setasmgidwrap o=/u01/app/oracle/product/11.2.0/bxrac/bin/oracle
原文地址：
DB_alert.rar asm_alert.rar
阅读(5988) | 评论(0) | 转发(0) |
