Password file and ASM users maintenance in RAC

Password file maintenance prior to 11gR2.

If you configure ASM and database using dbca, password file is automatically created and if you configure  without using DBCA, then you must manually create a password file using orapwd utility and is named orapw$ORACLE_SID. It is stored in the $ORACLE_HOME/dbs directory. Since $ORACLE_HOME is usually not shared in a clustered environment, this results in each instance having its own password file as shown below

orapwd file=$ORACLE_HOME/dbs/orapw+ASM1 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+ASM2 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+TEST1 password=**** entries=5
orapwd file=$ORACLE_HOME/dbs/orapw+ASM2 password=**** entries=5

This may lead to inconsistencies between the password files because password file will be updated on the cluster node at which the grant was executed(eg:grant sysdba to user).

So not sharing Oracle password files between the nodes in a cluster will result in inconsistencies between the password files if grant is made at only one of the instances. In order to avoid this you can either store the password file on a shared filesystem and create symbolic links from $ORACLE_HOME/dbs to this shared filesystem or execute the command on each instance to update password file at each instance

Password file maintenance in 11gR2

From 11gR2 onward  when password is changed for a privileged user on ASM instance of one node in a cluster, it is immediately propagated on all other nodes in the cluster using ASM CKPT process and hence there is no need to place theASM password files on a shared filesystem.

Another change that was made in Oracle 11g release 2 is a new password file name convention. In prior releases the password file name was based on $ORACLE_SID including the node suffix, since Oracle 11g release 2 the password file name is based on $ORACLE_SID without the node suffix. Thus orapw+ASM instead of orapw+ASM1 for an ASM instance.


[root@rsvmsb300 dbs]# ps -ef|grep pmon
root     13565 22639  0 02:23 pts/0    00:00:00 grep pmon
oracle   20527     1  0 Nov26 ?        00:00:32 ora_pmon_TEST1
grid     27380     1  0 Nov26 ?        00:00:13 asm_pmon_+ASM1



[root@rsvmsb300 dbs]# ls -lrt /u01/app/11.2.0/grid/dbs/orapw*
-rw-r----- 1 grid oinstall 1536 Dec 11 00:59 /u01/app/11.2.0/grid/dbs/orapw+ASM

[root@rsvmsb300 dbs]# ls -lrt /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapw*
-rw-r----- 1 oracle oinstall 1536 Nov 26 05:56 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTEST1
[root@rsvmsb300 dbs]#


Eg: When we grant privilege to user in ASM, then it is automatically propagated to other instances as shown below.

[grid@rsvmsb300 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 00:58:14 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user test identified by test;

User created.

SQL> grant sysasm to test;

Grant succeeded.

SQL> conn test/test as sysasm
Connected.

SQL> select * from gv$pwfile_users order by inst_id;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
         1 ASMSNMP                        TRUE  FALSE FALSE
         1 SYS                            TRUE  TRUE  TRUE
         1 TEST                           FALSE FALSE TRUE
         2 ASMSNMP                        TRUE  FALSE FALSE
         2 SYS                            TRUE  TRUE  TRUE
         2 TEST                           FALSE FALSE TRUE

6 rows selected.

SQL> exit


However, privileges are not propagated to another instances in case of database instances as show below.

$ . oraenv
ORACLE_SID = [TEST1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 02:34:30 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user test identified by test;

User created.

SQL> grant sysdba to test;

Grant succeeded.

SQL> select * from gv$pwfile_users order by inst_id;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS

---------- ------------------------------ ----- ----- -----

         1 SYS                            TRUE  TRUE  FALSE

         1 TEST                           TRUE  FALSE FALSE

         2 SYS                            TRUE  TRUE  FALSE

Note: Change in password file maintenance in a cluster is currently only implemented for clustered ASM and not for RAC database.For RAC database environments password files should either be placed on a shared filesystem (and symbolic linkfrom $ORACLE_HOME/dbs) or password file changes should be performed manually on all instances in order to keep the password file contents synchronized on all nodes. In example shown above, when I granted sysdba to user TEST on instance 1, it was updated to only in instance 1 (node1)password file. However as show in example above, when I granted sysasm privilege to ASM user , it propagated to all instances

ASM user maintenance in 11g

In Oracle 11g(R1 and R2),we can create user in +ASM instance just like in RDBMS instance and can view these users using gv$pwfile_users ,connecting to ASM instance.

You can't give sysasm privilege to database users. If you try to give, you will get error as

SQL> grant sysasm to test;
grant sysasm to test
*
ERROR at line 1:
ORA-15294: SYSASM privilege not allowed on database instance


You can view ASM users by connecting to ASM instance with SYSASM privilege.We can create ASM users and grant privileges as shown below

[grid@rsvmsb300 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/grid

[grid@rsvmsb300 ~]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:16:22 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> create user test identified by test123;

User created.

SQL> grant sysasm to test;

Grant succeeded.

SQL> grant sysdba to test;

Grant succeeded.

SQL> create user test2 identified by test2;

User created.

SQL> select * from gv$pwfile_users;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
         1 SYS                            TRUE  TRUE  TRUE
         1 ASMSNMP                        TRUE  FALSE FALSE
         1 TEST                           TRUE  FALSE TRUE
         1 TEST2                          FALSE FALSE FALSE
         2 SYS                            TRUE  TRUE  TRUE
         2 ASMSNMP                        TRUE  FALSE FALSE
         2 TEST                           TRUE  FALSE TRUE
         2 TEST2                          FALSE FALSE FALSE

8 rows selected.

SQL> exit


We can connect to ASM instance using privileged users as below

[grid@rsvmsb300 ~]$ . oraenv
ORACLE_SID = [+ASM1] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/grid.

[grid@rsvmsb300 ~]$ sqlplus test/test123 as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:22:36 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> exit

[grid@rsvmsb300 ~]$ sqlplus test as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:22:48 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

In ASM, users are authenticated using password file. there is no dictionary table to maintain users in ASM. So if you try to connect to ASM instance like database users, you will get error as below. To connect to ASM, you must be authenticated using password file.

[grid@rsvmsb300 ~]$ sqlplus test

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:29:17 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test
Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test /as sysasm
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL>

[grid@rsvmsb300 ~]$ sqlplus test2

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 11 04:32:05 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-15000: command disallowed by current instance type


Enter user-name: test2 /as sysasm
Enter password:
ERROR:
ORA-01031: insufficient privileges


Enter user-name:



[grid@rsvmsb300 ~]$