Remote_login_passwordfile Parameter and it's use

Remote_login_passwordfile   specifies whether Oracle checks for a password file . When we grant "sysdba" or  "sysoper" privileges to a user, that  user's name  and  privilege  information  are  added to the password file. A user's name remains in the password file only as long as that user has at least one of these two privileges. If we revoke both of these privileges, Oracle Database removes the user from the password file .

We have two options for Super User "SYS" password authentication .
1.) Operating System authentication

2.) Password file authentication

Password file authentication : When we use password file authentication then we have to create password file for  "SYS"  user or  the  user  having  "sysdba"  privileges . We can create the password file as below : 

C:\>orapwd  file=$ORACLE_HOME\database\orapwd.ora  password=sys_password  entries=n force=y

Note:

=> Entries= n  specifies the "maximum number of distinct DBAs and OPERs (opt)" can login into database .

=> Force=y    specifies if the password file exists then it overwrites the existing password file .

 There are three value for remote_login_passwordfile parameters : 

1.) EXCLUSIVE:  This is default value. The password file can be used by only one database and the password file can contain names other than sys.We can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an "exclusive" password file lets us allow to grant sysdba and sysoper system privileges to individual users and have them connect as themselves.

2.) NONE: If this parameter set "NONE" means passwordfile doesn't exists. That is, no privileged connections are allowed over non-secure connections. If remote_login_password is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting  remote_login_password  to NONE.

3.) SHARED: A SHARED password file can be used by multiple databases running on the same server. However, only user recognized by a SHARED password file is sys .We cannot add users to a SHARED password file. All users needing  sysdba  or sysoper system privileges must connect using the same name, SYS, and password. This option is useful if we have a single DBA administering multiple databases.A shared password file cannot be modified. This means that we cannot add users to a shared password file. Any attempt to do so or to change the password of SYS or other users with the sysdba or sysoper privileges generates an error.

All users needing  sysdba or sysoper  system privileges must be added to the password file when remote_login_passwordfile is set to  exclusive . After all users are added, we can change remote_login_passwordfile  to shared, and then share the file.This option is useful if we are administering multiple databases or a RAC database. If  remote_login_passwordfile is set to  exclusive  or shared and the password file is missing, this is equivalent to setting  remote_login_passwordfile  to None .

If we ever receive the file full error i.e, ORA-1996  while granting  "sysdba"  or  "sysoper" system privileges to a user, we must create a larger password file and regrant the privileges to the users. The below steps will replace the current passwordfile with new larger password file .

1.)  Identify the users who have sysdba or  sysoper  privileges by querying the  v$pwfile_users views .
2.)  Delete the existing password file.
3.)  Creating a new password file using the ORAPWD utility and ensure that the ENTRIES parameter is set to a number larger then the previous password file .
4.) Connect with "sys" and grant the priveleges to user as quired from v$pwfile_users view .

Enjoy     :-)