Home > oracle, RAC, SQL > Changing sys password in RAC databases

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.

About these ads
  1. jkeshwani
    August 14, 2011 at 8:56 am

    This information can be like an Oracle “Note of day”.
    Nice note.

  2. October 5, 2012 at 6:43 pm

    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..

  3. December 21, 2012 at 3:08 pm

    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.

  1. December 21, 2012 at 5:35 am
  2. January 10, 2013 at 5:43 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: