About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Friday, October 6, 2017

ORA-28017: The password file is in the legacy format.

Today I was trying to change password for one of my 12c database and got below error :-

SQL> alter user dbsnmp identified by "*************"
*
ERROR at line 1:
ORA-28017: The password file is in the legacy format.

Cause :- 

Looks like update to password file failed as the file format is wrong. 

Administrative privileges like sysbackup, syskm, sysdg cannot be granted unless the password file is in 12c Format

Solution :-

This error generally comes when you migrate your database from 11g to 12c

Regeneration of password will resolve this error.

PFB steps :-

1. Check which users have access to password file before regenerating it.

SQL> select * from v$pwfile_users;

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

2. Recreate your password file :-

==>orapwd file=$ORACLE_HOME/dbs/orapwamit entries=5 force=y

Enter password for SYS:
[Target_server][oracle][amit]

3. Now by default only sys have access to it

SQL> select * from v$pwfile_users;

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

4. Grant sysdba privilege to DBSNMP user to have access to password file

grant sysdba to dbsnmp;

Grant succeeded.

SQL> select * from v$pwfile_users;

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

5. Now again go for the password change, this time it will be successful.

SQL> alter user dbsnmp identified by "*************";

User altered.

I hope this article helped you.

Thanks
Amit Rath

2 comments: