Changing SYS Password in RAC Databases

The post title should really be “Changing SYS Password in RAC Databases when a password reuse limit is in place for SYS and how this has changed from 10.2 to 11.1″, but that would be a bit of a mouthful.

This is the post I was originally working on when I got side tracked by the change in behaviour of profiles assigned to SYS as detailed in “SYS and Password Resource Limits“. At the time I started this I wasn’t aware of the change and was just planning to use SYS passwords as an example of why you should listen to Tom Kyte when he says “… sys is magical, sys is special, sys is governed by a set of rules different from the rest of the world.” He’s right (of course) and the magical powers of SYS are subject to change!

The post is split into pre-11g and 11g or later. We can only assume that the behaviour won’t change back any time soon.

I’ll start with a disclaimer stating that I try to use the “password” command in SQL*Plus wherever possible to avoid displaying passwords in clear text on my screen and I would also need to have a very good reason to put passwords on the command line (OS or SQL*Plus), so the approach used in the examples is purely to make them clear.

Pre-11g

The example shows:

1. Change password on node 1
2. Connect to node 1 using new password successfully
3. Validate instance name
4. Connect to node 2 using new password unsuccessfully
5. Connect to node 2 using original password successfully
6. Attempt to change password on node 2 to new password fails as password reuse is restricted in profile assigned to SYS
7. Copy and rename password file from node 1 to node 2 (command not shown, but I use scp)
8. Connect to both nodes using same password successfully

SYS@orcl1> alter user sys identified by temp_pass1;

User altered.

SYS@orcl1> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> connect sys@orcl2/<original password> as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2> alter user sys identified by temp_pass1;
alter user sys identified by temp_pass1
*
ERROR at line 1:
ORA-28007: the password cannot be reused


SYS@orcl2>

Need to copy password file from node 1 and rename at this point (or change profile/resource limit)…

SYS@orcl2> connect sys@kn10st1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@kn10st2/temp_pass1 as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2>

11g or later

The example shows:

1. Change password on node 1
2. Connect to node 1 using new password successfully
3. Validate instance name
4. Connect to node 2 using new password unsuccessfully
5. Connect to node 2 using original password successfully
6. Change password on node 2 to new password successful even though password reuse is restricted in profile assigned to SYS
7. Connect to both nodes using same password successfully

SYS@orcl1> alter user sys identified by temp_pass1;

User altered.

SYS@orcl1> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
@> connect sys@orcl2/<original password> as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2> alter user sys identified by temp_pass1;

User altered.

SYS@orcl2> connect sys@orcl1/temp_pass1 as sysdba
Connected.
SYS@orcl1> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

SYS@orcl1> connect sys@orcl2/temp_pass1 as sysdba
Connected.
SYS@orcl2> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SYS@orcl2>

The key point is that the password for SYS is instance specific. I don’t really understand why Oracle have not implemented something that updates the SYS password file on all nodes of a clustered database, but maybe some reason why this would not be desirable behaviour. If there is then I can’t see it. A friend has suggested that one solution to this would be to use symbolic links for each instance to point to a common password file (on shared storage).

About these ads