Changing sys password in RAC databases
If you ever have worked with a RAC database, and you have changed a normal user password, you will notice that it has no difference between a single instance database. You just have to perform “ALTER USER xxx IDENTIFIED BY yyyy” and your password will be changed.
However, today I had a problem with a RAC database. I had to change the SYS password, and I did it the same way I would do in a single-instance database. But when I tried to connect with sys user, the error “ORA-01017: invalid username/password; logon denied” appeared. What I was doing wrong?
After a few thoughts, I found the solution. SYS password is instance specific in RAC databases, so you have to change it in every single instance. That’s all.
Categories: oracle, RAC, SQL
Oracle, Oracle RAC, Oracle Security, passwords
Oracle DBA tips 
This information can be like an Oracle “Note of day”.
Nice note.
This is due to the password files are not getting updated in all instances . so we need to change it in all instances or we can update the password files in case of any password file policy restriction..
Sorry, but this is, on its face, incorrect. The SYS password is stored in the Data Dictionary. The encrypted hash combination of the username/password is stored in SYS.USER$. As such, when you change the SYS password in one instance, it updates SYS.USER$ and the change is available in all other RAC instance. So on its face, your post is incorrect. To prove my point, lets look at this example:
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
resp1
SQL> alter user sys identified by MynewPass;
User altered.
On my instance RESP1, I changed my SYS password. Now let’s use that password to sign on to another instance in my RAC database:
SQL> connect sys/MynewPass as sysdba;
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
resp2
As you can see, I signed on to the RESP2 isntance with the new password yet I never changed the password for SYS in that instance.
Your problem is that when you connect as SYS, you must also connect as SYSDBA. When you connect as SYSDBA, no matter which SYSDBA user you connect with, Oracle needs to verify the password in the password file. I’ll bet that each instance has its own password file, which is why you had to change the SYS password in each instance. You noticed this behavioou had to update each password file. It is incorrect to say “SYS password is instance specific in RAC databases”. What is more correct would be that for your configuration, your password files are instance specific. As such, any SYSDBA user would appear that their password is instance specific.
To get around this, I move the password file to my shared storage. So on one instance, do something like this on node 1:
cp $ORACLE_HOME/dbs/orapworcl1 /u01/app/oracle/oradata/orcl/orapworcl
Now that the password file is in one shared location, let’s get all instances to use the same password file. On each node, do:
cd $ORACLE_HOME/dbs
rm orapworclX
ln -s /u01/app/oracle/oradata/orcl/orapworcl orapworclX
In the rm and ls commands, change X to your instance number on that node. Each instance will now have a softlink pointing to the same password file. Once this is complete, the SYS password will no longer be instance specific (it never was) and your password file will no longer be instance specific. When you change the password for any SYSDBA user, you will only have to change it on one instance.
Reminder: If this database is a primary for a standby database, don’t forget to copy the password file to the standby database.