Friday, May 6, 2011

ORAPWD file- recreate, adding new entries, limits, ORA-01996,...

Last few days, i learned a lot about Oracle password file! :-)

What I wanted, is, to increase value of the ENTRIES parameter, from default to some greater value, to have possibility to add a few more users with sysdba privilege. So, before I start to do that exhibition on production databases,  I wanted to be sure that the whole process has been tested!
First, I was not sure, should I shutdown db after change orapw file or not ? Well, with a little help MOS and test DB, the anwer is, NOT !! More precisely, it is necessary to restart the DB only after you created the password for the first time.
Ok, first problem was solved, I already have created orapw files, so I just copied old original files to have backup if something goes wrong.

Then, I changed values 'entries', to test adding some new users.
First, you can see, how many and what users are in the password file:

So, for test, I added some users, and test increasing value of the ENTRIES parameter:

On entries 2, I can put 5 users with sysdba grants, entries 5 --> 5 users, entries 6 --> 9 users, entries 7 --> 9 users.. ??
Well, that was strange!
But, answer is in oracle doc :-)

Proof that a different value of the ENTRIES parameter is ok

"The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four."

Finally, I wondered whether you can see the current value of the parameter ENTRIES ??
Well, NO! You cannot see the value of the ENTRIES parameter in a view or somewhere else.(v$pwfile_users is only for number of users)
And another important thing at the end:
You can recreate the password file with a larger values for ENTRIES and will lose all the current SYSDBA users!!! You only set password for SYS user, and new users need to be granted from SYS:

Now is really the end!   :-)

1 comment:

  1. Nice! Didn't know that "entries" part with byte length.