Rebuilding the Oracle Password File

Rebuilding an Oracle password file is not a routine daily task, but it is an important DBA maintenance procedure. The password file supports privileged administrative authentication for users who connect with privileges such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM. If the file is missing, corrupted, too small, inconsistent, or in the wrong format, administrative access can fail at the exact moment when the DBA needs it most.

The theory should be understood before the example. A password file rebuild is a replacement operation. Before replacing the file, record the existing administrative users. After replacing the file, restore only the administrative grants that are still required and verify the result.

In the earlier lessons, you created a password file, configured REMOTE_LOGIN_PASSWORDFILE, granted administrative privileges, connected AS SYSDBA, and queried V$PWFILE_USERS. This lesson brings those pieces together by showing how a DBA safely rebuilds the password file in an Oracle 23ai environment.

Why Rebuild a Password File?

Before You Rebuild: Record Existing Users

The key to the rebuild process is the list you make before deleting, renaming, or replacing the password file. Query V$PWFILE_USERS and save the output.

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

This query tells you which users currently have password-file administrative privileges. After the password file is rebuilt, you will use this list to decide which grants should be restored.

Also record the current password file location and format:

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

This is important because the password file may not always be a simple file under $ORACLE_HOME/dbs. In ASM, RAC, Exadata, or managed environments, the password file may live in a different location and may require different tooling.

Safe Rebuild Workflow

Connect Locally as SYSDBA

The legacy rebuild procedure said to connect as an INTERNAL user. That instruction is obsolete. In a modern Oracle 23ai procedure, connect locally as SYSDBA, preferably through operating system authentication:

SQL> CONNECT / AS SYSDBA
Connected.

This form is especially important when remote password-file authentication is broken. If the password file is missing or corrupted, remote SYSDBA connections may fail. Local operating system authentication may be the recovery path that allows the DBA to rebuild administrative access.

Controlled Shutdown for a Training Database

Older lessons often stated that the database must be shut down and restarted. For a training database, that is the safest and clearest procedure:

SHUTDOWN IMMEDIATE;

In production, the exact procedure depends on the environment. Some platforms, storage methods, and high availability architectures require more nuanced handling. For an instructional database, however, a controlled shutdown reduces ambiguity and prevents the file from being replaced while active administrative connections are still depending on it.

Create the New Password File with ORAPWD

The orapwd utility creates the password file. For a file-system based Oracle 23ai training database, a modern example is:

cd $ORACLE_HOME/dbs

mv orapwCOIN orapwCOIN.bak

orapwd FILE=$ORACLE_HOME/dbs/orapwCOIN FORMAT=12.2 ENTRIES=100 FORCE=Y

In this example:

1. orapwCOIN is the password file for the training database.
2. FORMAT=12.2 uses a modern password file format when compatibility allows.
3. ENTRIES=100 allows room for future administrative users.
4. FORCE=Y permits replacement if a file already exists.

Avoid placing the SYS password directly on the command line when interactive password entry is available. A password on the command line may be exposed through shell history, terminal scrollback, process listings, scripts, or copied documentation.

If you rename the old file to a backup file, protect that backup. A file such as orapwCOIN.bak should not be left readable by unauthorized operating system users.

Restart the Database

If the database was shut down for the rebuild, restart it after the new password file has been created:

STARTUP;

If you changed REMOTE_LOGIN_PASSWORDFILE, remember that a restart may be required for the parameter change to take effect:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

For a standard named-user password file model, EXCLUSIVE is the expected setting:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

Restore Administrative Grants

After the password file is rebuilt, restore only the administrative grants that are still needed. Use the saved output from V$PWFILE_USERS as a reference, but review it before regranting privileges.

GRANT SYSDBA TO admin_user;
GRANT SYSOPER TO ops_user;
GRANT SYSBACKUP TO backup_admin;
GRANT SYSDG TO dg_admin;
GRANT SYSKM TO key_admin;

These grants represent different administrative responsibilities:

• SYSDBA: full database administrative authority.
• SYSOPER: operational startup, shutdown, mount, open, and recovery tasks.
• SYSBACKUP: backup and recovery administration.
• SYSDG: Data Guard administration.
• SYSKM: key management administration.

Regranting privileges is not just a mechanical restoration step. It is a security review. If an old administrative account is no longer needed, do not restore it.

Verify the Rebuilt Password File

After restoring the required grants, query V$PWFILE_USERS again:

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

Then verify the password file metadata:

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

Finally, test a prompted remote SYSDBA connection:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.

This confirms that the rebuilt password file supports the expected administrative authentication path.

ASM, RAC, and Data Guard Considerations

In simple file-system examples, the password file is often shown under $ORACLE_HOME/dbs. In ASM, RAC, Exadata, and some cloud-style deployments, the password file may not be managed as a simple local file.

Use V$PASSWORDFILE_INFO to identify the actual password file location. In ASM environments, use the appropriate ASM or Grid Infrastructure tools to create, copy, or delete password files. Do not assume that a single-node file-system command is correct for a clustered environment.

In Data Guard environments, password file consistency matters. The primary and standby databases must be compatible in password file format and administrative users. A mismatch can affect redo transport, broker operations, switchover, failover, or remote administration. Coordinate password file rebuilds with the standby architecture rather than treating the primary database in isolation.

Password Protection and Security

Common Rebuild Errors

Forgetting to Save Current Grants

If you do not save the output of V$PWFILE_USERS before replacing the file, you may not know which administrative grants need to be restored.

Using the Wrong File Name

The password file name must match what the database expects. For file-system examples, this often follows a pattern such as orapw<ORACLE_SID>. For ASM and RAC environments, verify the correct location with V$PASSWORDFILE_INFO.

Using the Wrong ORACLE_SID

If ORACLE_SID is wrong when you create the file, the new password file may be created for the wrong instance name or in the wrong location.

Using an Incompatible Format

A password file format mismatch can create administrative authentication problems. Use FORMAT=12.2 for Oracle 23ai when compatibility allows, but coordinate with Data Guard, older clients, and migration requirements.

Leaving REMOTE_LOGIN_PASSWORDFILE Disabled

If REMOTE_LOGIN_PASSWORDFILE is set to NONE, Oracle will not use the password file for remote privileged authentication.

Restoring Too Many Old Grants

A rebuild is an opportunity to reduce administrative exposure. Do not restore stale users simply because they were present in the old password file.

Example: Rebuilding the Password File for a Training Database

The general theory applies to any Oracle 23ai database. A training database such as COIN simply gives the procedure a concrete name.

SQL> CONNECT / AS SYSDBA
Connected.

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

SHUTDOWN IMMEDIATE;

At the operating system prompt, the Oracle software owner can rename the old file and create a replacement:

cd $ORACLE_HOME/dbs

mv orapwCOIN orapwCOIN.bak

orapwd FILE=$ORACLE_HOME/dbs/orapwCOIN FORMAT=12.2 ENTRIES=100 FORCE=Y

Then restart and restore the required grants:

SQL> CONNECT / AS SYSDBA
Connected.

STARTUP;

GRANT SYSDBA TO admin_user;
GRANT SYSOPER TO ops_user;
GRANT SYSBACKUP TO backup_admin;

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, CON_ID
FROM V$PWFILE_USERS
ORDER BY USERNAME;

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

Test the administrative connection with a password prompt:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.

Summary

Rebuilding an Oracle 23ai password file is a controlled maintenance operation. It may be needed when the file is missing, corrupted, too small, in the wrong format, or inconsistent across environments. The safest approach is to record current password-file users first, locate the password file, recreate it with orapwd, restore only necessary administrative grants, and verify the result.

The key rule is simple: do not delete or replace the password file until you have recorded the users and privileges needed to rebuild it correctly.