I installed 64-bit Oracle 11gR2 on 64-bit Oracle Linux 5.6 using ASMLib interface (grid infrastructure and two users - grid and oracle). Installation was successful, but then I encountered very strange ORA errors trying to connect to database via sqlplus under any OS user account other than oracle. For each and every user, except oracle, it is not possible to connect to database and query it. This is what I get as root user (or every other user except oracle):
[root@localhost ~]# sqlplus sh/sh
SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 24 14:38:30 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA1/orcl/datafile/system.256.743941801'
ORA-15081: failed to submit an I/O operation to a disk
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA1/orcl/datafile/system.256.743941801'
ORA-15081: failed to submit an I/O operation to a disk
Error accessing package DBMS_APPLICATION_INFO
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options
SQL> select * from sales;
select * from sales
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA1/orcl/datafile/system.256.743941801'
ORA-15081: failed to submit an I/O operation to a disk
SQL>
As you see, even though it seems the connection is established, I cannot query the data at all (got the same errors). Note that this problem exists only when a user is trying to connect via sqlplus. There is no problem to connect and query the database via OEM or SQL Developer under any OS user account. Specially, under oracle user account the problem doesn't exist at all. It also doesn't exist if database uses filesystem instead of ASM (grid infrastructure). Also, the problem didn't exist in 11gR1 at all.
What is even more surprising for me is this: if I make a user a member of oinstall group (it must be just oinstall, not dba), the problem is solved. Every user who is a member of oinstall group can connect to and query database via sqlplus.
To resume:
1. any user other than oracle cannot connect to database and query it via sqlplus
2. every user can connect and query database via OEM, SQL Developer and, probably, other Oracle applications
3. every user must be a member of oinstall group to be able to connect to and query database via sqlplus
4. this problem only exists when database uses ASM (grid infrastructure), not with filesystem
5. the problem didn't exist in 11gR1 at all
Anyone has a clue how to explain this behaviour?
Here are additional informations about installation and what I exactly did.
# groupadd -g 501 oinstall
# groupadd -g 502 dba
# groupadd -g 503 oper
# groupadd -g 504 asmadmin
# groupadd -g 505 asmdba
# groupadd -g 506 asmoper
# useradd -u 501 -g oinstall -G dba,asmadmin,asmdba,asmoper grid
# passwd grid
# useradd -u 502 -g oinstall -G dba,oper,asmadmin,asmdba oracle
# passwd oracle
# mkdir -p /opt/app/grid/product/11.2.0/grid
# mkdir -p /opt/app/oracle/product/11.2.0/dbhome_1
# chown -R grid:oinstall /opt
# chown -R oracle:oinstall /opt/app/oracle
# chmod -R 775 /opt
So, the environments for the two users are:
1. for grid user (all owned by grid:oinstall)
ORACLE_BASE=/opt/app/grid
ORACLE_HOME=/opt/app/grid/product/11.2.0/grid
ORACLE_SID=+ASM
2. for oracle user (all owned by oracle:oinstall)
ORACLE_BASE=/opt/app/oracle
ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=orcl
I configured ASMLib interface on four 256 GB partitions on four physical disks (total 1 TB) with disk perms on boot: chown=grid:oinstall, chmod=660.
VOL1=/dev/sda4 (256 GB)
VOL2=/dev/sdb4 (256 GB)
VOL3=/dev/sdc4 (256 GB)
VOL4=/dev/sdd4 (256 GB)
[root@localhost disks]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 grid oinstall 8, 4 Feb 24 09:47 VOL1
brw-rw---- 1 grid oinstall 8, 20 Feb 24 09:47 VOL2
brw-rw---- 1 grid oinstall 8, 36 Feb 24 09:47 VOL3
brw-rw---- 1 grid oinstall 8, 52 Feb 24 09:47 VOL4
[root@localhost disks]# ls -l /dev/sd*4
brw-r----- 1 root disk 8, 4 Feb 24 10:46 /dev/sda4
brw-r----- 1 root disk 8, 20 Feb 24 10:46 /dev/sdb4
brw-r----- 1 root disk 8, 36 Feb 24 10:46 /dev/sdc4
brw-r----- 1 root disk 8, 52 Feb 24 10:46 /dev/sdd4
[root@localhost proc]# cat /proc/partitions | grep sd.4
8 4 268446150 sda4
8 20 268446150 sdb4
8 36 268446150 sdc4
8 52 268446150 sdd4
The grid infrastructure installation was performed from grid OS user account choosing the option "Install and Configure Grid Infrastructure for a Standalone Server". Installation was fast and smooth with resulting ASM instance and LISTENER running from grid home (processes owned by grid user). The database installation (single instance using ASM) was performed from oracle OS user account. This installation was also fast and smooth resulting in running database instance and OEM interface (processes owned by oracle user). You can see the processes snapshot on this [url http://www.jurinovic.com/priv/ora_processes.jpg]screenshot.
Thank you.
NJ