Oracle password file Utility :ORAPWD

Last updated on May 11th, 2019 at 06:40 am

Introduction to ORAPWD

orapwd is a 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 password file need to be present in the remote database
You can create a password file using 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. Creating a password file, via orapwd, enables remote users to connect with administrative privileges.

$ 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 describe the orapwd command line arguments.

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.

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 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 password 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

Oracle password file Utility :ORAPWD

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.

Lets 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

  1. First Identify the users who have SYSDBA or SYSOPER privileges by querying the V$PWFILE_USERS view.
  2. Delete the existing password file. You can just remove the file from operating system
  3. 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.
  4. Now add all the users which were there earlier

Orapwd in 12c

With 12c Release, there are 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]

Oracle password file Utility :ORAPWD

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

 

Few other Important things on orapwd

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 public password file

 

How to change the SYS password in Oracle database

a. Change the SYS password

b. Recreate the password file if it exists

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

Hope you like detailed explanation on orapwd utility on 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 purpose

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

Leave a Reply