The Password file is created during Oracle installation by default and normally, but sometimes you may need to create or delete this file.

ORAPWD Syntax and Command Line Argument Descriptions:

You can create or maintain the Password file through the ORAPWD command, and the command syntax is as follows:

[FORCE={y|n}]

[ASM={y|n}]

[DBUNIQUENAME=dbname]

[FORMAT={12.2|12}]

[SYS={y|n|password|external('sys-external-name')|global('sys-directory-DN')}]

[SYSBACKUP={y|n|password|external('sysbackup-external-name')|global('sysbackupdirectory-

DN')}]

[SYSDG={y|n|password|external('sysdg-external-name')|global('sysdg-directory-DN')}]

[SYSKM={y|n|password|external('syskm-external-name')|global('syskm-directory-DN')}]

[DELETE={y|n}]

[INPUT_FILE=input-fname]

FILE: Here the name of the Password file is specified and this variable is mandatory. You must enter the file name. If you are using Oracle Managed File (OMF), it is sufficient to write the file name only. However, if you are not using OMF, you must write the file name and the full path.

FORCE: If this variable takes the value y, this means that if there is a Password file with the same name, it will be rewritten. However, if it takes the value n, an error message will appear indicating that the task has failed.

ASM: In the default mode, this variable takes the value n, which means that you are creating a Password file for the database. However, if this variable takes the value y, this means that you are creating a Password file for Oracle ASM. In this case, the file must be stored in the Oracle ASM Disk Group.

DBUNIQUENAME: Here the unique name of the database is specified. If this variable is specified, this means that the password file is for the database. If this variable is not specified, it can be the password file for the database or for Oracle ASM.

FORMAT: This variable can take one of the following values:

12.2: This is the default value for creating the password file. This format supports granting administrative privileges to external users and also supports the SSL protocol and Kerberos verification for administrative users.

12: This format only supports the administrative privileges supported in Oracle 12c, which are (SYSBACKUP & SYSDG & SYSKM).

Legacy: This format only supports the administrative privileges found in versions before Oracle 12c, which are (SYSDBA & SYSOPER) and does not support other administrative privileges such as SYSBACKUP or SYSKM.

There are a number of other optional variables that you can view through the command orapwd help=y

Creating a Database Password File with ORAPWD:

You can create a Password file through the ORAPWD command, just open the command line and type the ORAPWD command.

Here are some examples of creating a Password file:

Example 1 Creating a Database Password File Located in a File System:

Here the Password File is created in (Format 12.2) format and the file name is orapworcl where the file is stored in the default path on the operating system.

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2

Example 2 Creating a Database Password File Located in an Oracle ASM Disk Group:

Here the Password File is created in (Format 12.2) and the file name is orapworcl but the file is stored on the Oracle ASM disk group

Note that the DBUNIQUENAME variable is required here because the Password file is stored on the Oracle ASM disk group.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2

Example 3 Creating a Database Password File with a SYSBACKUP Entry:

Create the Password File in the default Format 12.2. Note that we used the DBUNIQUENAME variable because the file is stored on the Oracle ASM disk group. The administrative user SYSBACKUP was also added to the file.

orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' SYSBACKUP=password FORMAT=12.2

Example 4 Migrating a Legacy Database Password File to Oracle Database 12c Format:

We will convert the Password File format from (Agency Format to 12.2 Format), the name of the Password File is orapworcl and it is stored on the operating system, the new file will replace the old file because we used the FORCE=y variable

orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

Resetting the Password for the SYS Administrative User:

To reset the password for the administrative user SYS, the new file will replace the old file because we used the FORCE=y variable.

orapwd FILE='/u01/oracle/dbs/orapworcl' SYS=Y INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y

The prompted will ask you to enter the new password for the user SYS.

Describing a Password File:

To view the data of the Password File.

orapwd DESCRIBE FILE='orapworcl'

In case the name of the Password File has been changed or its storage location has been changed, the following command must be executed for the update to take effect.

SQL> ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE;

After executing the command, you can confirm that the change has been made through V$PASSWORDFILE_INFO view

Sharing and Disabling the Database Password File:

The Password File is controlled by the variable REMOTE_LOGIN_PASSWORDFILE, this variable can take several values:

none: This value tells the database to ignore the Password File or act as if the Password File does not exist and thus does not allow connections with administrative privileges over unsecured connections.

Exclusive: This is the default mode where the Password File is allowed to be used for only one database.

Shared: This value allows sharing the Password File with more than one database, but it should be noted that the Password File here is in read-only mode (Read only) and therefore cannot be modified or added to a user or other, so if you want to change the Password File, you must change the variable REMOTE_LOGIN_PASSWORDFILE to the value Exclusive first and make the necessary modifications and then return the value of the variable to the Shared mode.

It should be noted that the Shared mode cannot be set for the Oracle ASM password file.

Keeping Administrator Passwords Synchronized with the Data Dictionary:

If you change the REMOTE_LOGIN_PASSWORDFILE variable from none to exclusive or shared, you must ensure that the passwords stored in the Data Dictionary are the same as the passwords stored in the Password File for all administrative users AS SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM except for the SYS user, as starting from Oracle Database 12c Release 2 (12.2), the SYS user is verified through the Password File only.

To ensure that the passwords for administrative users in the Data Dictionary and the Password File are synchronized, you must follow the following steps:

1- Find all users with the SYSDBA administrative privilege except for the SYS user.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';

2- Remove SYSDBA privilege from users and grant them back.

REVOKE SYSDBA FROM SYSDBA-user;

GRANT SYSDBA TO SYSDBA-user;

3- Find all users with SYSDBA privilege except SYS.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';

4- Remove SYSOPER privilege from users and grant them back.

REVOKE SYSOPER FROM SYSOPER-user;
GRANT SYSOPER TO SYSOPER-user;

5- Find all users with SYSBACKUP privilege except SYS.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSBACKUP='TRUE';

6- Revoke SYSBACKUP privilege from users and grant them back.

REVOKE SYSBACKUP FROM SYSBACKUP-user;
GRANT SYSBACKUP TO SYSBACKUP-user;

7- Find all users with SYSDG privilege except SYS.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDG='TRUE';

8- Revoke SYSBACKUP privilege from users and grant them back.

REVOKE SYSDG FROM SYSDG-user;
GRANT SYSDG TO SYSDG-user;

9- Find all users with SYSKM privilege except SYS.

SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSKM='TRUE';

10- Revoke SYSKM privilege from users and grant them back.

REVOKE SYSKM FROM SYSKM-user;
GRANT SYSDG TO SYSKM-user;

Adding Users to a Database Password File:

When you grant one of the administrative privileges SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM to a user, this user and the granted administrative privilege information are added to the Password File.

The user name remains in the Password File as long as he has one of these administrative privileges, and if all administrative privileges are withdrawn from the user, he is deleted from the Password File.

It should be noted that the Password File must be in one of the formats (FORMAT=12.2 or FORMAT=12) in order to support administrative privileges (SYSBACKUP, SYSDG, or SYSKM).

Granting and Revoking Administrative Privileges:

Administrative privileges are granted by the GRANT command and administrative privileges are withdrawn by the REVOKE command.

To grant SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privileges to a user:

GRANT SYSDBA TO mydba;

To revoke SYSDBA, SYSOPER, SYSBACKUP, SYSDG, or SYSKM administrative privileges from a user:

REVOKE SYSDBA FROM mydba;

The WITH ADMIN OPTION option is ignored if it is specified while granting the administrative privilege, but in general there are several rules:

A user who connects to the database with the AS SYSDBA administrative privilege can grant any administrative privileges to any user and can also revoke any administrative privileges from any user.

A user who connects to the database with the AS SYSOPER administrative privilege cannot grant any administrative privileges to any user and cannot revoke any administrative privileges from any user.

A user who connects to the database with the administrative privilege AS SYSBACKUP can grant and revoke the administrative privilege SYSBACKUP to any user and can also revoke the administrative privilege SYSBACKUP from any user.

A user who connects to the database with the administrative privilege AS SYSDG can grant and revoke the administrative privilege SYSDG to any user and can also revoke the administrative privilege SYSDG from any user.

A user who connects to the database with the administrative privilege AS SYSKM can grant and revoke the administrative privilege SYSKM to any user and can also revoke the administrative privilege SYSKM from any user.

Viewing Database Password File Members:

V$PWFILE_USERS