Oracle RAC – granting SYSDBA or SYSOPER is instance specific

The default location for the password file is $ORACLE_HOME/dbs which in most cases (if you don’t have your ORACLE_HOME on a shared disk) is on a non-shared filesystem. Whenever you grant SYSDBA or SYSOPER to a database user Oracle will add the user to the password file. What the Oracle documentation doesn’t tell you is that the user will only be added to the password file for the instance where you are connected to, not to the password files for all other RAC instances running on other nodes.

So to make sure you can connect as a user (with for example AS SYSDBA) on all RAC database instances, you have todo the GRANT SYSDBA and/or GRANT SYSOPER on ALL RAC instances separately.

To make sure a user is added to the password file for all instances of a RAC database, use the following commend:

SQL> select * from gv$pwfile_users;

   INST_ID USERNAME                       SYSDB SYSOP SYSAS
---------- ------------------------------ ----- ----- -----
         2 SYS                            TRUE  TRUE  FALSE
         2 MARCEL                         TRUE  FALSE FALSE
         1 SYS                            TRUE  TRUE  FALSE
         1 MARCEL                         TRUE  FALSE FALSE