Oracle provides the use of a password file and initialization parameters in order to control who can access the database with SYSDBA privileges. The error ORA-28046 is a result of using a single password file and the initialization parameters to access multiple databases with only one file. When the password file is used in this fashion, the ability to dynamically change the SYS password with the command “alter user” is disabled. In this procedure we will review how to change the SYS password after receiving this error.

1. Logon to your Oracle database server as the Oracle software owner.

2. Enter SQLPLUS as SYSDBA.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Oct 04 11:39:34 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Attempt to change the SYS user password.

SQL> alter user sys identified by password1;
alter user sys identified by password1
*
ERROR at line 1:
ORA-28046: Password change for SYS disallowed

SQL>

NOTE: The alter user command failed.

4. Show the current definition of the initialization parameter
REMOTE_LOGIN_PASSWORDFILE with the SQLPLUS command show.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE
———————————— ———– —————-
REMOTE_LOGIN_PASSWORDFILE string SHARED
SQL>

NOTE: The current REMOTE_LOGIN_PASSWORDFILE is set to SHARED which disallows changing of the SYS password.

4. The initialization parameter REMOTE_LOGIN_PASSWORDFILE is not dynamic and thus we need to change its value and restart the database.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=exclusive scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>

5. Now, show the current value of REMOTE_LOGIN_PASSWORDFILE.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE
———————————— ———– ————
REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE
SQL>

6. Re-execute the “ALTER USER” command to change SYS’s password and you will see that the command was successful.

SQL> alter user SYS identified by password1;

User altered.

SQL>

7. Change the REMOTE_LOGIN_PASSWORDFILE init parameter back to SHARED and restart the database.

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=shared scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>

8. Now, show the current value of REMOTE_LOGIN_PASSWORDFILE.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE
———————————— ———– ———–
REMOTE_LOGIN_PASSWORDFILE string SHARED
SQL>

This completes changing of SYS password when the init parameter
REMOTE_LOGIN_PASSWORDFILE is set to SHARED.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com