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