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.
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
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
User altered.
I hope this article helped you.
Thanks
Amit Rath