Oracle password file Utility :ORAPWD

In this post, we will discuss password file in oracle,oracle password file utility orapwd, how to create a password file in oracle , create password file in RAC

Table of Contents

Introduction to ORAPWD

orapwd is an oracle password file utility and it allows the remote connection to the database with administrative privilege i.e. if you want to connect a remote database with sysdba privilege, then a password file needs to be present in the remote database
You can create a password file using the orapwd utility.

Users are added to the password file when they are granted the SYSDBA or SYSOPER or SYSASM privilege. The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER, and SYSASM privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges.

how to create password file in oracle

Creating a password file, via orapwd, enables remote users to connect with administrative privileges. Here is the 11g Syntax

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

Examples:
cd $ORACLE_HOME/dbs
$ orapwd file=orapwTEST password=sys_password force=y nosysdba=y
$orapwd file=orapwPROD password=<secret> entries=12 ignorecase=n
$orapwd file=orapwPROD password=<secret> entries=30 ignorecase=Y

The following describes the orapwd command line arguments.

FILE
Name to assign to the password file, which will hold the password information. You must supply a 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.

The filenames allowed for the password file are OS-specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

PASSWORD
This is the password the privileged users should enter while connecting as SYSDBA or SYSOPER or SYSASM. This is the sys password

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

FORCE
(Optional) If Y permits overwriting an existing password file. An error will be returned if a 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. This becomes more important after 11g release as passwords become case sensitive in that release

NOSYSDBA
(Optional) For Oracle Data Vault installations.

Importance of parameter REMOTE_LOGIN_ PASSWORDFILE

Just creating the password file will not serve any purpose unless we specified the parameter REMOTE_LOGIN_ PASSWORDFILE in the oracle database initialization file

Important Note

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.

How to view password File members

V$PWFILE_USERS view can be used to see the users who have been granted SYSDBA or SYSOPER system privileges for a database

SQL> select * from v$pwfile_users;
USERNAME             SYSDBA        SYSOPER      SYSASM
--------             ------        -------        ------
SYS                   TRUE          TRUE          FALSE

USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.
SYSASM If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.

Let’s add a user

SQL> grant sysdba to TEST;

Now again see the view

SQL> select * from v$pwfile_users;
USERNAME             SYSDBA        SYSOPER      SYSASM
--------             ------        -------        ------
SYS                   TRUE          TRUE          FALSE
TEST                  TRUE          FALSE         FALSE

If you receive the file full error (ORA-1996) when you try to grant SYSDBA or SYSOPER system privileges to a user, you must create a larger password file and re-grant the privileges to the users. Check below how to expand the password file

How to Expand the Password file using orapwd

First Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
Delete the existing password file. You can just remove the file from operating system
Now again create the password file using orapwd utility .Ensure that the ENTRIES parameter is set to a number larger than you think you will ever need.

Now add all the users which were there earlier

create password file in oracle 12c

With 12c Release, there are a host of changes in the utility. The syntax is

orapwd FILE=filename [ENTRIES=numusers] [FORCE={y|n}] [ASM={y|n}]
[DBUNIQUENAME=dbname] [FORMAT={12|legacy}] [SYSBACKUP={y|n}] [SYSDG={y|n}]
[SYSKM={y|n}] [DELETE={y|n}] [INPUT_FILE=input-fname]

Examples

orapwd FILE='+DATA/TEST/orapwTEST' ENTRIES=10 DBUNIQUENAME='TEST' FORMAT=12
orapwd FILE='+DATA/TEST/orapwTEST' ENTRIES=10 DBUNIQUENAME='TEST' SYSBACKUP=y
orapwd FILE='orapwTEST' ENTRIES=30 FORMAT=12
We dont have ignorecase parameter .It is depcreated

ORA-01994 error

If orapwd has not yet been executed, attempting to grant SYSDBA or SYSOPER privileges will result in the following error:

SQL> grant sysdba to TEST;

ORA-01994: GRANT failed: cannot add users to the public password file

This can also happen if the database was started using srvctl and it did not get the path of the password file. We should check the configuration and fix

How to change the SYS password in the Oracle database

Prior to 12.2, you can change the sys using alter user system and this change will be reflected in the password file also if it exists

With 12.2, it is mandatory to have the password file existence before changing the SYS password else it will throw errors

Steps will be

Set remote_login_passwordfile=exclusive
then restart the database
generate a password file:
orapwd file=orapwSID password=<sys pass> entries=3

check in DB
SQL> select username,sysdba,sysoper from v$pwfile_users;
after that execute the following command to reset the SYS password:
SQL> alter user sys identified by ;
rm the password file.

change init.ora parameter back to remote_login_passwordfile=none and restart the database

Password file in RAC

password file in RAC should be stored in a common location i.e ASM Diskgroup. we can create the password file as

orapwd file='+DATA/orapwTEST' ENTRIES=10 DBUNIQUENAME='TEST'

From 12c onwards, we can create the password file in ASM using the below command also

ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path} {sys_password}
Example
ASMCMD>pwcreate –dbuniquename TEST '+DATA/PWDFILE/orapwTEST' {sys_password}

Hope you like the detailed explanation of the orapwd utility on the Oracle database and how to create, modify and expand it.

This is quite useful when setting up Data-guard, using active duplicate command for cloning and other purposes

Related Articles

ORA-01017: invalid username/password; logon denied Tips
How to Stop the Force Password Reset on Creation of User Account
Top 10 Oracle Database Security Tips for DBA
How to change the DBNAME /DBID using DBNEWID(nid) utility for Oracle database