Lesson 3	Steps to creating a password file
Objective	Explain the process for creating a password file in Oracle 23ai

Creating an Oracle Password File (Five-Step Process Explained)

To create a password file and configure a database to use it, you must perform five tasks in sequence. The order matters - the password file must exist before the database starts, and REMOTE_LOGIN_PASSWORDFILE must be set before the database opens for the parameter change to take effect:

Shut down the database.
Determine the proper location and name for the password file.
Use the orapwd utility to create the password file.
Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE in the SPFILE or init.ora parameter file.
Restart the database.

You already know how to shut down and restart your database. This lesson covers the remaining three steps in detail: determining the correct file location, running orapwd, and setting REMOTE_LOGIN_PASSWORDFILE.

Step 1 and 2 - Shut Down and Locate the Password File

The database must be shut down before creating the password file because the orapwd utility creates the file at the operating system level - not through Oracle Net Services. Once the database is shut down, determine the correct location and filename for the password file. The filename convention is operating-system specific and Oracle must be able to find the file automatically when the instance starts.

Platform-specific naming conventions:

Linux and Unix: $ORACLE_HOME/dbs/orapw<SID> - for example, /u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwORCL for a database with SID ORCL. The filename must match the SID exactly - Oracle looks for this specific filename at startup.
Windows: %ORACLE_HOME%\database\PWD<SID>.ora - for example, C:\app\oracle\product\19.0.0\dbhome_1\database\PWDORCL.ora.
Oracle ASM (12.2+): The password file can be stored in an ASM disk group, for example +DATA/orapwORCL, enabling a shared password file across all Oracle RAC nodes.

You must supply the complete path when running orapwd. If you supply only a filename without a path, the file is created in the current working directory - which may not be the location Oracle looks for it at startup, resulting in authentication failures after restart.

Step 3 - Use orapwd to Create the Password File

The orapwd command-line utility creates the password file and sets the initial credentials for the SYS user. It must be run from the operating system prompt - not from within SQL*Plus - by an OS user with permission to write to the target directory.

orapwd Syntax for Oracle 19c and 23ai

orapwd FILE=$ORACLE_HOME/dbs/orapwORCL \
       ENTRIES=10 \
       FORMAT=12

The utility prompts for the SYS password interactively when PASSWORD is omitted - this is the recommended practice in Oracle 12.2 and later because it prevents the password from appearing in shell history files or process listings.

Key parameters for Oracle 19c and 23ai:

FILE - Required. The full path and filename for the password file. The contents are encrypted - the file cannot be read directly with a text editor.
ENTRIES - The maximum number of privileged user entries. In Oracle 12c and later the password file grows dynamically, but this sets the initial allocation. Set this higher than you expect to need - if you exceed the allocated entries you must recreate the password file. Entries are reused as users are added and removed.
FORMAT=12 - Required for Oracle 12c and later. Enables case-sensitive password storage and support for Oracle 12c+ administrative privileges (SYSBACKUP, SYSDG, SYSKM, SYSRAC). Without this parameter, the legacy format is used and these privileges cannot be stored in the password file.
FORCE=Y - Overwrites an existing password file. Required when recreating a password file after the SYS password has been lost, or when migrating from the legacy format to FORMAT=12. Use with caution in production - this resets SYS credentials.

The IGNORECASE parameter, which allowed case-insensitive password comparison, is deprecated in Oracle 12c and should not be used. Oracle 12c enforces case-sensitive password comparison by default through the SEC_CASE_SENSITIVE_LOGON parameter, and FORMAT=12 stores credentials accordingly.

Example - Creating the Password File

The following command creates a password file for a database with SID ORCL on Linux, with initial capacity for 10 privileged users:

orapwd FILE=$ORACLE_HOME/dbs/orapwORCL ENTRIES=10 FORMAT=12

After running this command, orapwd prompts for the SYS password:

Enter password for SYS:

The password file is created at the specified location. Verify it exists before proceeding:

ls -la $ORACLE_HOME/dbs/orapwORCL

On Windows:

orapwd FILE=%ORACLE_HOME%\database\PWDORCL.ora ENTRIES=10 FORMAT=12

Security Caution - Protecting the Password File

It is critically important to the security of your system that you protect the password file and the environment variables that identify its location. Any OS user with read access to the password file could potentially extract the encrypted credentials and mount an offline attack. Any OS user who can replace the password file could substitute their own credentials for SYS and gain unrestricted database access.

On Linux and Unix, restrict the password file permissions to the oracle OS user:

chmod 600 $ORACLE_HOME/dbs/orapwORCL
chown oracle:oinstall $ORACLE_HOME/dbs/orapwORCL

On Windows, restrict file permissions using the Security tab in file Properties so that only the Oracle service account and the local Administrators group have access.

Step 4 - Set REMOTE_LOGIN_PASSWORDFILE

Creating the password file is necessary but not sufficient. You must also set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to tell Oracle to use the password file. This parameter must be set in the SPFILE or the init.ora parameter file - it takes effect when the instance starts, not when it is altered at runtime.

To set it in the SPFILE while the database is shut down, edit the init.ora file directly, or use ALTER SYSTEM ... SCOPE=SPFILE while the database is open and then restart. The parameter accepts three values:

NONE

Setting REMOTE_LOGIN_PASSWORDFILE=NONE causes Oracle to ignore the password file entirely, even if the file exists on disk. No privileged remote connections are permitted - any attempt to connect as SYSDBA or SYSOPER over Oracle Net Services is rejected. This setting is appropriate for databases that should never be accessed remotely with administrative privileges. It is also the effective behavior when the parameter is set to EXCLUSIVE or SHARED but the password file is missing.

Oracle Cloud Infrastructure

EXCLUSIVE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is the standard setting for single-instance databases and for the primary database in a Data Guard configuration. An exclusive password file is owned by one database instance and can be modified - users can be added, removed, and have their passwords changed while this setting is active. This is the correct setting for most production databases.

With EXCLUSIVE, you can:

Add users to the password file by granting them SYSDBA, SYSOPER, or other administrative privileges
Change the SYS password using ALTER USER SYS IDENTIFIED BY <new_password>
Remove users from the password file by revoking their administrative privileges

EXCLUSIVE is the recommended value. Set it with:

-- In init.ora
REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

-- Or in SPFILE while database is open
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE SCOPE = SPFILE;

SHARED

REMOTE_LOGIN_PASSWORDFILE=SHARED allows multiple databases on the same server, or multiple instances in an Oracle RAC cluster, to share a single password file. However, a shared password file cannot be modified - you cannot add users, remove users, or change passwords while this setting is active. Any attempt to do so generates an error.

The correct workflow when using SHARED is:

Set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
Add all required users and grant all required privileges
Change REMOTE_LOGIN_PASSWORDFILE=SHARED
Restart the database

In Oracle 12.2 and later, the preferred approach for RAC environments is to store the password file in Oracle ASM rather than using the SHARED setting. An ASM-based password file is accessible to all RAC nodes while remaining modifiable - it does not have the restriction on adding users that the SHARED setting imposes.

Verifying the Configuration

After restarting the database, verify that REMOTE_LOGIN_PASSWORDFILE is set correctly and that the SYS user appears in the password file:

-- Check the parameter value
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

-- Verify SYS is in the password file
SELECT USERNAME, SYSDBA, SYSOPER
FROM   V$PWFILE_USERS
ORDER  BY USERNAME;

The output should show at minimum:

USERNAME   SYSDBA  SYSOPER
---------- ------- -------
SYS        TRUE    TRUE

If V$PWFILE_USERS returns no rows, the password file was not found at the expected location or REMOTE_LOGIN_PASSWORDFILE is not set to EXCLUSIVE.

Step 5 - Restart the Database

After creating the password file and setting REMOTE_LOGIN_PASSWORDFILE in the parameter file, restart the database to activate the new configuration:

-- Connect as SYSDBA locally using OS authentication
sqlplus / AS SYSDBA

-- Start the instance
STARTUP;

Once the database is open, test remote SYSDBA connectivity from a client workstation:

sqlplus sys/<password>@<service_name> AS SYSDBA

A successful connection confirms that the password file is in the correct location, the SYS credentials are stored correctly, and REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE is in effect. If the connection fails with ORA-01017 (invalid username/password), verify the SYS password matches what was entered during orapwd creation. If it fails with ORA-01994, the password file is missing or the parameter is set to NONE.

Complete Creation Process Summary

The complete password file creation process for Oracle 19c and 23ai:

-- Step 1: Connect locally and shut down the database
sqlplus / AS SYSDBA
SHUTDOWN IMMEDIATE;
EXIT;

-- Step 2 and 3: Create the password file (run from OS prompt, not SQL*Plus)
orapwd FILE=$ORACLE_HOME/dbs/orapwORCL ENTRIES=10 FORMAT=12
-- (prompts for SYS password interactively)

-- Secure the password file
chmod 600 $ORACLE_HOME/dbs/orapwORCL

-- Step 4: Set the parameter in init.ora or SPFILE
-- Edit $ORACLE_HOME/dbs/initORCL.ora and add:
-- REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

-- Step 5: Start the database
sqlplus / AS SYSDBA
STARTUP;

-- Verify
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;
SELECT USERNAME, SYSDBA FROM V$PWFILE_USERS;

In the next lesson, you will learn how to determine the proper location and naming conventions for the Oracle password file across different operating systems and Oracle deployment configurations.