Wednesday, April 20, 2016

How to solve ORA-28017: The password file is in the legacy format


SQL> alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ;
alter user sys identified by iossS1Qwmk_kKfGHqs0UVu93xxswQ
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.
Cause:
#oerr ora 28017
28017, 00000, "The password file is in the legacy format."
// *Cause:    There are multiple possibilities for the cause of the error:
//
//              * An attempt was made to grant SYSBACKUP, SYSDG or SYSKM.
//              * These administrative privileges could not be granted unless
//                the password file used a newer format ("12" or higher).
//              * An attempt was made to grant a privilege to a user who
//                has a large password hash which cannot be stored in
//                the password file unless the password file uses a newer
//                format ("12" or higher).
//              * An attempt was made to grant or revoke a common administrative
//                privilege in a CDB
// *Action:   Regenerate the password file in the newer format ("12" or higher).
//            Use the newer password file format ("12" or higher) if you need to
//            grant a user the SYSBACKUP, SYSDG or SYSKM administrative
//            privileges, or if you need to grant a privilege to a user
//            who has a large password hash value.

Cause: the password file is of the wrong format. You typically see this error after a migration from 11g to 12c.

Solution: regenerate the password file.

First, check the users that are affected by the change:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0
In my case, two users are affected: SYS and DBAMON.


Regenerate the password file:
# orapwd file=$ORACLE_HOME/dbs/orapwproddb01 entries=5 force=y

Enter password for SYS:

Check the password file users again. SYS was added as a result of the recreation of the password file:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

To add DBAMON as a sysdba user, grant the SYSDBA privilege to the account:
SQL> grant sysdba to DBAMON;

Grant succeeded.
Check again, and DBAMON is now registered as a privileged user in the password file:
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
DBAMON                         TRUE  FALSE FALSE FALSE FALSE FALSE          0

1 comment:

  1. Great post, helped to fix similar cases in my environment. Thanks for sharing!

    ReplyDelete