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

ORA-65114: space usage in container is too high

Yesterday I was doing an import for a pluggable database and it ran out of space so I tried to resize one of the datafile and got below error while doing it :-

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g;
ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/AMIT.786.919009661' RESIZE 100g
*
ERROR at line 1:
ORA-65114: space usage in container is too high

Cause :-

This error generally comes when total size of PDB after resize of the datafile is more than the size you have allocated to a PDB while creating.

Solution :-

Change the allocation storage for a PDB and then try to resize the datafile.

Note :- while changing the storage for a PDB , check if space is available in ASM disks.


Allocation storage of a PDB can be changed by logging in to the PDB :-

SQL> alter session set container=AMIT;

Session altered.

Either change the allocation storage to a required value or change it to unlimited.

SQL> ALTER PLUGGABLE DATABASE STORAGE UNLIMITED;

Pluggable database altered.

OR

SQL> alter pluggable database storage (MAXSIZE 120G);

Pluggable database altered.

Now try to resize the datafile

SQL> ALTER DATABASE DATAFILE '+DATA1/AMIT_CON/3933D120863A61B7E0531609900AE4EE/DATAFILE/amit.786.919009661' RESIZE 100556633088;

Database altered.

I hope this article helped you.

Thanks
Amit Rath