ANBOB™

We know that changed a normal user password has no diffrence between a single instance database and a RAC database,we just have to perform “alter user xxx identified by xxx” and the password will be cheanged.

however,if you want to change the sys password with a RAC database use the same way do in a single-instance database,you will be fail.SYS password is instance specific in RAC databases, so you have to change it in every single instance.Tom Kyte said ” do not do things as SYS, sys is
special, sys is magical, sys is special, sys is governed by a set of rules different from the rest of the world. Use ‘as sysdba’ only to perform maintenance operations like shutdown, startup and the such. ”

Martin Nash‘s a article very good,so I Reprinted here for china(can’t view wordprocess.com) ORACLE user.

Pre-11g

The example shows:

Change password on node 1
Connect to node 1 using new password successfully
Validate instance name
Connect to node 2 using new password unsuccessfully
Connect to node 2 using original password successfully
Attempt to change password on node 2 to new password fails as password reuse is restricted in profile assigned to SYS
Copy and rename password file from node 1 to node 2 (command not shown, but I use scp)
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/ 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:

Change password on node 1
Connect to node 1 using new password successfully
Validate instance name
Connect to node 2 using new password unsuccessfully
Connect to node 2 using original password successfully
Change password on node 2 to new password successful even though password reuse is restricted in profile assigned to SYS
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/ 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).

打赏

微信扫一扫，打赏作者吧～