UNIX Group Permissions when Installing 10g on 11g ASM

The following was a lesson learned with proper group permissions when interacting with the new and improved 11gR2 ASM. I thought perhaps, this might be useful for someone who is facing this scenario in the future. Hope Google, provides this as a possible link if you ever search this problem.

I tried to install Oracle 10g database on 11.2 ASM. I already have an 11.2 RAC DB running fine on this 11.2 ASM (also RAC) instance.

Upon completing the DB binary install (10.2.0.1), I tried to create the database using DBCA. At the point where it asks to select the type of the file, When I selected ASM: The following message pops up.

It erroed saying that the ASM instance was not running. Obviously, it was. So, It was time to look under the covers.

First place was to look at the trace.log in $OH/cfgtoollogs/dbca

trace.log (final few lines ) were...

[AWT-EventQueue-0] [22:31:45:302] [ASMInstance.discoverASMInstance:369] Found existing ASM -- Sid: +ASM2 OracleHome: /u01/app/11.2.0/grid

[AWT-EventQueue-0] [22:31:45:303] [ASMInstance.discoverASMInstance:374] ORATAB_FILE /etc/oratab

[AWT-EventQueue-0] [22:31:45:305] [ASMInstance.discoverASMInstance:376]discoverOSMInstance:bExists=true

[AWT-EventQueue-0] [22:31:45:310] [ASMInstance.initialize:339]initialize:m_bASMPresent=true

[AWT-EventQueue-0] [22:31:46:925] [ASMInstance.validateASM:475] Validating SI-ASM...

[AWT-EventQueue-0] [22:31:46:952] [SQLEngine.initialize:244] Execing SQLPLUS/SVRMGR process...

[AWT-EventQueue-0] [22:31:46:973] [SQLEngine.initialize:272] m_bReaderStarted: false

[AWT-EventQueue-0] [22:31:46:975] [SQLEngine.initialize:276] Starting Reader Thread...

[AWT-EventQueue-0] [22:31:47:33] [ASMInstance.detectASMInstance:775]detectOSMInstance:m_bASMPresent=true

[AWT-EventQueue-0] [22:31:47:34] [ASMInstance.detectASMInstance:776]detectOSMInstance:m_bASMRunning=false

[AWT-EventQueue-0] [22:31:47:147] [SQLEngine.done:1960] Done called

Here is the problem. It just stops abruptly instead of listing the ASM disk groups to proceed further with the install. So something is amiss.

[AWT-EventQueue-0] [22:31:47:148] [SQLEngine.reInitialize:605] Reinitializing SQLEngine...

[AWT-EventQueue-0] [22:31:47:151] [SQLEngine.initialize:244] Execing SQLPLUS/SVRMGR process...

Looking into metalink, notes: 948456.1

Point #1 was not the reason for my error, as the cluster in linux2 (my node) is pinned.

[root@linux2 ~]# olsnodes -t -n

linux1 1 Unpinned

linux2 2 Pinned

I looked at Point#2 and this was the exact error message I received. So I decided to take that route and did an upgrade to 10.2.0.5. I could not find out whether (8288940) is included in 10.2.0.5. It was a leap of faith.

After Upgrade, I started DBCA and came to the same error. So I decided to compare the o/s level permissions between the 11.2 oracle id (oracle) and 10.2 oracle id (oracle10).

I saw a difference.

[oracle@linux2 ~]$ id <-- This is the oracle db software owner id for 11gR2.

uid=1101(oracle) gid=1000(oinstall) groups=502(dba),503(oper),1000(oinstall),1201(asmdba)

[oracle10@linux2 ~]$ id <-- This is the Oracle s/w owner for 10g

uid=1104(oracle10) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1203(dba10)

As you can see, the asmdba group is missing for Oracle10.

Oracle 11.2 provides new security restrictions to manage and maintain ASM. Gone are the days when the "sys" user of the database can do everything on ASM. Specifically, oracle provides (asmadmin, asmdba, and asmoper). You can read more about the roles and privileges.

It states:

Members of the (OSASM group), a.k.a asmadmin can:

use SQL to connect to an Oracle ASM instance as SYSASM using operating system authentication. The SYSASM privileges permit mounting and dismounting disk groups, and other storage administration tasks.

Whereas,

Members of the ASM Database Administrator group (OSDBA for ASM) are granted read and write access to files managed by Oracle ASM. The grid infrastructure installation owner and all Oracle Database software owners must be a member of this group, and all users with OSDBA membership on databases that have access to the files managed by Oracle ASM must be members of the OSDBA group for ASM.

Translation:

Though one might think (I certainly did) "asmadmin" is some sort of "admin" group that would have a superset of privileges, in fact, this is not true. It is more important to have the oracle software owners to be part of "asmdba" group than "asmadmin” group.

Upon adding the “asmdba” group to “oracle10” id, I restarted the install. This time the DBCA went past the problem area.

This time the trace.log has the following.

94 [AWT-EventQueue-0] [23:9:41:819] [ASMInstance.discoverASMInstance:369] Found existing ASM -- Sid: +ASM2 OracleHome: /u01 /app/11.2.0/grid

95 [AWT-EventQueue-0] [23:9:41:820] [ASMInstance.discoverASMInstance:374]ORATAB_FILE /etc/oratab

96 [AWT-EventQueue-0] [23:9:41:821] [ASMInstance.discoverASMInstance:376]discoverOSMInstance:bExists=true

97 [AWT-EventQueue-0] [23:9:41:825] [ASMInstance.initialize:339]initialize:m_bASMPresent=true

98 [AWT-EventQueue-0] [23:9:42:721] [ASMInstance.validateASM:475] Validating SI-ASM...

99 [AWT-EventQueue-0] [23:9:42:742] [SQLEngine.initialize:244] Execing SQLPLUS/SVRMGR process...

100 [AWT-EventQueue-0] [23:9:42:757] [SQLEngine.initialize:272] m_bReaderStarted: false

101 [AWT-EventQueue-0] [23:9:42:758] [SQLEngine.initialize:276] Starting Reader Thread...

102 [AWT-EventQueue-0] [23:9:42:809] [ASMInstance.detectASMInstance:775]detectOSMInstance:m_bASMPresent=true

103 [AWT-EventQueue-0] [23:9:42:810] [ASMInstance.detectASMInstance:776]detectOSMInstance:m_bASMRunning=false

104 [AWT-EventQueue-0] [23:9:42:919] [ASMInstance.detectASMInstance:803] OSM Instance is running

105 [AWT-EventQueue-0] [23:9:42:990] [ASMInstance.detectASMInstance:880] ASM version = 11.2.0.1.0

106 [AWT-EventQueue-0] [23:9:43:206] [InitParamAttributes.loadDBParams:4123]Checking if SPFILE is used

107 [AWT-EventQueue-0] [23:9:43:263] [InitParamAttributes.loadDBParams:4134]spParams = [Ljava.lang.String;@1e2a069

108 [AWT-EventQueue-0] [23:9:43:265] [ASMParameters.loadASMParameters:441]useSPFile=true

109 [AWT-EventQueue-0] [23:9:43:265] [StorageOptionsPage.validate:569] ASM is present and started.

110 [AWT-EventQueue-0] [23:9:43:347] [DiskGroupsPanel.initialize:262] Loading Data in the Table..

111 [AWT-EventQueue-0] [23:9:43:348] [DiskGroupDataSource.loadData:252] Load the data from the datasource

112 [AWT-EventQueue-0] [23:9:43:422] [ASMInstance.loadDiskGroups:1811] sql to be executed:=select NAME||'|'||round(TOTAL_MB)||'|'||round(USABLE_FILE_MB)||'|'||nvl(TYPE, 'DBCA_NULL')||'|'||STATE from v$asm_diskgroup order by NAME

113 [AWT-EventQueue-0] [23:9:43:611] [ASMInstance.loadDiskGroups:1815] Loading the diskgroup from schema....

114 [AWT-EventQueue-0] [23:9:43:612] [ASMInstance.loadDiskGroups:1823] diskgroup: CRS|3069|2673|EXTERN|MOUNTED

115 [AWT-EventQueue-0] [23:9:43:615] [ASMInstance.loadDiskGroups:1823] diskgroup: DATADG|30719|28615|EXTERN|MOUNTED

116 [AWT-EventQueue-0] [23:9:43:616] [ASMInstance.loadDiskGroups:1823] diskgroup: DATARAC10G|30719|30624|EXTERN|MOUNTED

117 [AWT-EventQueue-0] [23:9:43:617] [ASMInstance.loadDiskGroups:1823] diskgroup: FRADG|30719|30192|EXTERN|MOUNTED

118 [AWT-EventQueue-0] [23:9:43:688] [ASMInstance.setBestDiskGroup:1432] sql to be executed:=select name from v$asm_diskgrou p where free_mb= (select max(free_mb) from v$asm_diskgroup)

Just to ensure "asmdba" is the only critical group needed, I took out "asmadmin" and tried the install once again.

[oracle10@linux2 ~]$ id

uid=1104(oracle10) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1203(dba10)

It was able to recognize the disks.

So, please make sure that the oracle binary owners are part of the "asmdba" group when creating databases on 11.2 ASM.