As I was playing around with one of my 12c databases, I ran into the ORA-01996 error "GRANT failed: password file '/<>/orapwORCL' is full". The documentation and various other sources are clear about this: you will have to re-create the password file and add all existing entries manually afterwards.
Just when I was about to create a new password file and re-create the entries in there, it occurred to me that 12c has a new feature: the input_file parameter for orapwd. As far as the documentation is concerned, this is to migrate older versions of the password file to 12c.
But, what if we can use this to "migrate" our current 12c password file to a new 12c password file? What if we backup this file and use that as an input for our new password file? I created a quick test setup and found out that this seems to be exactly what I needed. I re-created the password file using the input_file parameter and I could add new entries, but at the same time I kept all the entries that were already there.
The testcase
First of all, I created a new passwordfile with entries=1. Actually, this will hold up to 4 entries for me, because of the fact that it is rounded up to the number of entries per block (in the same documentation I mentioned above).
[oracle@DBServer1 ~]$ cd $ORACLE_HOME/dbs
[oracle@DBServer1 dbs]$ orapwd file=orapwORCL entries=1 force=y
Enter password for SYS:
Using SQL*Plus, I verified that all I had was 1 entry:
SQL> select * from v$pwfile_users order by username, con_id;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
So, now we can start adding users to the passwordfile, until it is full:
SQL> grant sysdba to c##erik container=all;
Grant succeeded.
SQL> grant sysdg to sysdg;
Grant succeeded.
SQL> grant sysbackup to sysbackup;
grant sysbackup to sysbackup
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwORCL' is full
What do we have in the v$pwfile_users at this point?
SQL> select * from v$pwfile_users order by username, con_id;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
C##ERIK TRUE FALSE FALSE FALSE FALSE FALSE 0
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
Now, we will go back to $ORACLE_HOME/dbs and re-create the password file, using the input_file parameter. Make sure you backup the current password file, as you can never be too careful...
[oracle@DBServer1 dbs]$ cp orapwORCL orapwORCL_backup
[oracle@DBServer1 dbs]$ orapwd file=orapwORCL entries=10 input_file=orapwORCL_backup force=y
Immediately after that, check the contents of the password file with SQL*Plus:
SQL> select * from v$pwfile_users order by username, con_id;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
C##ERIK TRUE FALSE FALSE FALSE FALSE FALSE 0
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
And all entries are still there! Just one last step to prove we have successfully re-created the password file with more entries than we had:
SQL> grant sysbackup to sysbackup;
Grant succeeded.
SQL> select * from v$pwfile_users order by username, con_id;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
C##ERIK TRUE FALSE FALSE FALSE FALSE FALSE 0
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 0
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
And I didn't even have to reconnect to or restart the database :-)
Edited: Thanks to Pom for pointing out a small but essential typo
