?
Solved

Password File

Posted on 2008-11-13
4
Medium Priority
?
1,228 Views
Last Modified: 2012-05-05
ORA-01996: GRANT failed: password file is full.

We have to create multiple user accounts with SYSDBA system privilege. All the existing users
and the new users must have the SYSDBA privilege.

I tried to create a user with sysdba system privilege. I got the following Oracle error:

ORA-01996: GRANT failed: password file is full

I queried the v$pwfile_users view and got the following output:

SQL> select count(*) from v$pwfile_users
2 /

COUNT(*)
----------
5

Please clarify the following:

1. How do I find out how many entries are there in the current password file?

2. How do I modify the password file and increase the number of entries to 30? What are
the steps to increase the number of entries to 30?

3. Should a current password file be deleted?

4. Will all the users with the sysdba sysdba privilege be dropped when the password file is deleted?
Or, will the users be there in the database and only the sysdba privilege revoked?

5. If the current password file has to be deleted, how do I save the user accounts
already created?

6. How do I drop the current password file?

7. How do I create a new password file? Please let me know the steps.

8. Do I have to re-create user accounts with sysdba system privilege when I create the new
password file or just grant the sysdba system privilege to the users?

9. Should the new password file have the same name as the old password file name?

10. What is location of the password file?

11. Should I shutdown and re-start the database after dropping and recreating the new password
file?

12. Should I restart the Oracle Service after re-creating the password file?

13. If the password file has to be deleted to increase the number of entries, will I able to login as SYS to do the DBA tasks when there is no password file?

14. How can I do the DBA tasks with a password file?

15. Do I have to keep a backup of the old password file in order to re-create the user in the new password file?

16. What will happen to the users during the time the current password file is deleted, the new password file is created, and the users recreated? Will they be able to use the database?

The ORA-01996: GRANT failed: password file is full has to be fixed in about forty database.

Please let me know how the above error can be quickly fixed in all the databases. I am concerned about the users when the password file is deleted.

This is a high priority task.

Thanks.



0
Comment
Question by:as93is
  • 2
4 Comments
 
LVL 6

Expert Comment

by:peter991
ID: 22957841
You will have to recreate the file..

Expanding the Number of Password File Users

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users.
To Replace a Password File

   1. Note the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
   2. Shut down the database.
   3. Delete the existing password file.
   4. Follow the instructions for creating a new password file using the ORAPWD utility in "Using ORAPWD". Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.
   5. Follow the instructions in "Adding Users to a Password File".


Read more:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/dba.htm#1227
0
 

Author Comment

by:as93is
ID: 22958627

We have an Oracle 10g database not Oracle 9i.

Can you give some steps to delete and re-create the password file?
What will happen to the users during the time when the password file is deleted and recreated?


Should the users be re-created after the new password file is created?

Please answer all my questions.   This is a time-bound task.

Thanks.
0
 
LVL 6

Accepted Solution

by:
peter991 earned 1500 total points
ID: 22958639
0
 
LVL 3

Expert Comment

by:johnsmith1962
ID: 22963768
Why do new users need the sysdba privileges?  This is really bad practice, it means everyone will have the ability to destroy the database.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question