The password file stores a list of usernames and passwords that are allowed to remotely authenticate as SYSDBA over the network.
Oracle must use this file to authenticate them, not the normal list of passwords stored in the database.
And for this authentication to use password file, the value of REMOTE_LOGIN_PASSWORDFILE should be EXCLUSIVE or SHARED.
orapwd tool is used to create and manage password files.
DEFAULT LOCATION FOR PWD FILE – $ORACLE_HOME/dbs
orapwd syntax:
Usage: orapwd file= entries= force=<y/n> asm=<y/n> dbuniquename= format=<legacy/12> sysbackup=<y/n> sysdg=<y/n> syskm=<y/n> delete=<y/n> input_file= Usage: orapwd describe file= where file - name of password file (required), password - password for SYS will be prompted if not specified at command line. Ignored, if input_file is specified, entries - maximum number of distinct DBA (optional), force - whether to overwrite existing file (optional), asm - indicates that the password to be stored in Automatic Storage Management (ASM) disk group is an ASM password. (optional). dbuniquename - unique database name used to identify database password files residing in ASM diskgroup only. Ignored when asm option is specified (optional), format - use format=12 for new 12c features like SYSBACKUP, SYSDG and SYSKM support, longer identifiers, etc. If not specified, format=12 is default (optional), delete - drops a password file. Must specify 'asm', 'dbuniquename' or 'file'. If 'file' is specified, the file must be located on an ASM diskgroup (optional), sysbackup - create SYSBACKUP entry (optional and requires the 12 format). Ignored, if input_file is specified, sysdg - create SYSDG entry (optional and requires the 12 format), Ignored, if input_file is specified, syskm - create SYSKM entry (optional and requires the 12 format), Ignored, if input_file is specified, input_file - name of input password file, from where old user entries will be migrated (optional), describe - describes the properties of specified password file (required). There must be no spaces around the equal-to (=) character.
Create a password file for standalone database .
cd $ORACLE_HOME/dbs
orapwd file=orapwORCL password=oracle force=y
Create a password file in ASM diskgroup:
orapwd file='+DATA/orapwORCL' ENTRIES=10 DBUNIQUENAME='ORCL'
Create password file from asmcmd tool:(Oracle 12c onwards)
ASMCMD> pwcreate --dbuniquename ORCL +DATA/PWDFILE/pwdORCL oracle
We can view users authenticated through password file in v$pwfile_users table
SQL> select username,sysdba from v$pwfile_users; USERNAME SYSDB --------- ----- SYS TRUE
password Changes in oracle 12.2
From oracle 12.2 , complex password verification method is deployed.
Below are the criteria for password file in oracle 12.2
- The password contains no fewer than 8 characters and includes at least one numeric and one alphabetic character.
- The password is not the same as the user name or the user name reversed.
- The password is not the same as the database name.
- The password does not contain the word oracle (such as oracle123).
- The password differs from the previous password by at least 8 characters.
- The password contains at least 1 special character.
Not following the criteria will throw error as below
orapwd file=orapwORCL password=oracle OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters.
But we can bypass this password verification, by using format=12 as below:
orapwd file=orapw$ORACLE_SID password=oracle format=12