Oracle provides for a normal DBA user account to remotely connect to a database with SYSDBA privileges through the use of a password file. However, the Oracle password file is created with a set size which is not designed to expand to allow for additional information to be stored. Thus, the utility ORAPWD has a clause named ENTRIES which defines the maximum number of DBA user accounts with SYSDBA privileges you would every need. Once the entries clause is exceeded, you will receive the error ORA-01996. The only way to resolve this error is to recreate your password file with the utility ORAPWD. This article covers the recreation of a new password file. This procedure will work on any OS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Oct 25 11:21:31 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL>

3. You attempt to grant SYSDBA to a user and receive the following eerror; your password file is full.

SQL> grant SYSDBA to SYSTEM
*
ERROR at line 1:
ORA-01996: GRANT failed: password file ” is full

4. Perform the following to resolve this issue. Navigate to the following directory $ORACLE_HOME/dbs and list the file orapw* to check the current name of your password file.

mylinux :> cd $ORACLE_HOME/dbs
mylinux :> ls -lrt
total 272
-rw-r–r– 1 oracle dba 3979 Dec 13 07:57 initorcl.ora
-rw-r—– 1 oracle dba 5120 Mar 19 18:12 spfileorcl.ora
mylinux :>

3. In this example our SID is ORCL and we have located a file of the orapworcl. Rename the file with the following command:

mv orapworcl orapworcl_bak

4. Now we are ready to re-create our password file with the ORAPWD utility. Ensure that the entries clause is larger then you ever anticipate the number of DBAs to be.

mylinux :>orapwd file=./orapworacle password=password1 entries=100
mylinux :>

6. Execute the ls command to see the new password file.

mylinux :> ls orapw*
orapworcl
mylinux :>

7. Enter SQLPLUS with SYSDBA privileges.

mylinux :> sqlplus ‘/ as SYSDBA’

SQL*Plus: Release 10.2.0.4.0 – Production on Oct 25 11:39:25 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

8. Ensure that the init parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE through the use of the show command.

SQL> show parameter REMOTE_LOGIN_PASSWORDFILE

NAME TYPE VALUE
———————————— ———– ————
REMOTE_LOGIN_PASSWORDFILE string EXCLUSIVE
SQL>

9. If the parameter was something other then EXCLUSIVE, use the alter system command to change it to exclusive and restart the database.

10. Now you have to re-grant the SYSDBA privilege to all users which previously had the privilege and the new DBA user which caused the original error message. Use the command:

grant SYSDBA to {username};

Or see the article ‘Oracle determining users that are in the password file before recreating’

This completes recreation of Oracle password file due to error ORA-01996

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com