Creating an Oracle passwordfile

Step :1

Use Oracle utility 'orapwd' to create a password file as below.


syntax:

D:\oracle\product\10.2.0\db_1\BIN>orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n>

  where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA and    

    force - whether to overwrite existing file (opt),
OPERs (opt),
  There are no spaces around the equal-to (=) character.

Screen shot: as to how it looks.

Step 2:

Alter system set REMOTE_LOGIN_PASSWORD FILE =EXCLUSIVE  scope=’spfile’ 

Restart the system to avoid the below. As the parameter is a static one and requires a restart of the database.

Use the V$PWFILE_USERS  view the users who have been granted SYSDBA or SYSOPER system privileges for a database

Adding a user to password file

Step 1 : 

Set the password file to EXCLUSIVE mode. And restart the database.

Step 2 :

SQL>grant sysdba to oe;


user oe will get added to password file and with its  information on sysdba privileages

Note* : Mostly people come across an error
ORA 02095: specified intitialization parameter cannot be modified , 
it is adviced inorder to change the parameter user should use the below command.

Solution : Alter system set REMOTE_LOGIN_PASSWORD FILE =SHARED  scope=’spfile’

GRANT sysdba to oe // sometimes gives rise to error ORA-01944 GRANT failed.

Solution : is to restart the database after setting the REMOTE_LOGIN_PASSWORDFILE

Important * - Setting the parameter to the below values causes 

• NONE : Oracle database pretends to own no password file.
• EXCLUSIVE: only this option enables you to (Add, Edit and modify users) . And supports only one instance of the database.
• SHARED : Shared password file enable multiple databases to use the same file. Mostly Used in the case of multiple instances like RAC. The file cannot be modified in SHARED mode.