Monday, January 03, 2011

solve oracle error ORA-01999: password file cannot be updated in SHARED mode

sometimes under specific conditions you want to change the password of the Oracle database user sys. Their is however something special with the password for the sys user which might cause you to run into a ORA-01999 error. reason for this is that your database is using a REMOTE_LOGIN_PASSWORDFILE file and this file is set to SHARED. Shared means that one or more database can share the same password file which is for example used in a RAC setup. In my case it was NOT a RAC setup it was a standalone database. So if you want to change the sys password in that case you have to change the mode from SHARED to EXCLUSIVE.

Accoording to the Oracle documentation you have the following options:

# NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.

# EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.

# SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

The below example illustrates the error:

SQL> passw sys
Changing password for sys
New password:
Retype new password:
ORA-01999: password file cannot be updated in SHARED mode

Now we have 2 options to do solve this depending on the fact if your database uses a spfile or a pfile setup.

When using a spfile you can do the following:

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE' scope=spfile;

When using a pfile you will have to vi the content of your pfile.

After the settings are picked-up by the database you can change your sys password.

No comments: