Password File Management in Oracle Database

What is the purpose of a password file in Oracle Database?

Password file stores the password for users having SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM and SYSDG privileges. By default, SYS is the only user to have SYSDBA+SYSOPER privileges. The password file is maintained on OS at locations, 

$ORACLE_HOME/database - for windows systems
$ORACLE_HOME/dbs - for linux/unix systems

The core purpose of password file is to let DBAs authenticate and connect to database without having the need to access the credentials from database. This functionality is essential because if all the user credentials are maintained within database, how would you connect when the database is down? It is password file who let's you connect to the database and perform the startup operation.

How to create a password file?

The Password file is created using ORAPWD utility. When you create a database, it is created by default and goes with the naming format of 

ORAPW<ORACLE_SID>.ora - On linux/unix | Example: ORAPWORCL.ora
PWD<ORACLE_SID>.ora - On Windows | Example: PWDORCL.ora

To create a password file, you must invoke ORAPWD utility using CMD/Terminal (make sure the environment is set correctly) along with mandatory parameters,

orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N]

Few Examples,

orapwd file=orapwSID password=sys_password force=y nosysdba=y
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=******
orapwd file=orapwprod entries=32 force=y
orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=***** entries=20
orapwd file=C:\app\oracle\product\11.2.0\db_1\database\pwdorcl.ora password=**** entries=10 force=y
orapwd file=orapworcl password=***** entries=12 ignorecase=n
orapwd file=orapworcl password=***** ignorecase=y
orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' SYSBACKUP=***** FORMAT=12.2 

FILE - Name to assign to the password file, which will hold the password information. You must supply complete path. If you supply only filename, the file is written to the current directory. The contents are encrypted and are unreadable. This argument is mandatory.

Note: If you are running multiple instances of Oracle Database using Oracle Real Application Clusters (RAC), the environment variable for each instance should point to the same password file.

PASSWORD - This is the password the privileged users should enter while connecting as SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP.

ENTRIES - Entries specify the maximum number of distinct SYSDBA, SYSOPER, SYSASM, SYSBACKUP, SYSKM and SYSDG users that can be stored in the password file.

Note: When you exceed the allocated number of password entries, you must create a new password file.

FORCE (Optional) - If Y, permits overwriting an existing password file. An error will be returned if password file of the same name already exists and this argument is omitted or set to N.

IGNORECASE (Optional) - If Y, passwords are treated as case-insensitive i.e. case is ignored when comparing the password that the user supplies during login with the password in the password file.

Note: Oracle Database 12c onwards, IGNORECASE parameter has been deprecated.

FORMAT (from 12c Oracle) - The administrative privileges SYSBACKUP, SYSDG, and SYSKM are not supported in the password file when the file is created with the FORMAT=LEGACY argument. Since 12.2, this argument is added as a default and mandatory to be defined.

NOSYSDBA (Optional) - For Oracle Data Vault installations.

How to check which users are stored in Password File?

To check this, you can simply query the view V$PWFILE_USERS.

SQL> select * from V$pwfile_users;

USERNAME   SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM ACCOUNT_STATUS       PASSWORD_P LAST_LOGIN      LOCK_DATE EXPIRY_DA EXTERNAL_NAME        AUTHENTI COM     CON_ID
---------- ----- ----- ----- ----- ----- ----- -------------------- ---------- --------------- --------- --------- -------------------- -------- --- ----------
SYS        TRUE  TRUE  FALSE FALSE FALSE FALSE OPEN                                                                                     PASSWORD YES          0

DB Parameter related for Password File Management

REMOTE_LOGIN_PASSWORDFILE is the db parameter that is relevant to password file usage control. This parameter can have 3 values,

NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non-secure connections.

EXCLUSIVE (default) - Password file is exclusively used by a single database instance. In this mode, users can be added to the password file.

SHARED - The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.

A SHARED password file cannot be modified i.e. you 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 or SYSASM (this is from Oracle 11g), SYSKM, SYSDG and SYSBACKUP (these 3 are from Oracle 12c R1) privileges generates an error. All users needing SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG and SYSBACKUP system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED.