REMOTE_LOGIN_PASSWORDFILE - Configure Oracle Password File

REMOTE_LOGIN_PASSWORDFILE

Overview

REMOTE_LOGIN_PASSWORDFILE determines how Oracle uses a password file for authenticating privileged connections — specifically users connecting AS SYSDBA, AS SYSOPER, AS SYSBACKUP, AS SYSDG, or AS SYSKM. When set to EXCLUSIVE (the default), Oracle uses a database-specific password file that can contain multiple SYSDBA users in addition to SYS. When set to SHARED, the password file can be used by multiple databases but only contains the SYS user. When set to NONE, Oracle ignores the password file entirely and only permits operating system authentication for privileged connections.

This parameter is foundational for remote DBA access. Without a properly configured password file and REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE or SHARED, users cannot connect with AS SYSDBA over a network connection. Local OS authentication always works regardless of this setting, but remote SYSDBA authentication — including connections from RMAN clients, Data Guard standby configurations, and OEM — depends on the password file being present and correctly configured.

Parameter Type: Static (requires instance restart to change) Default Value: EXCLUSIVE Valid Values: NONE, SHARED, EXCLUSIVE Available Since: Oracle 7 Modifiable: No — SCOPE=SPFILE only; takes effect on next startup PDB Modifiable: No (CDB-level setting)

Configuration

Viewing Current Value

-- Check current REMOTE_LOGIN_PASSWORDFILE setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'remote_login_passwordfile';

-- Check SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'remote_login_passwordfile';

-- View all password file users (requires SYSDBA or SELECT on V$PWFILE_USERS)
SELECT username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm
FROM v$pwfile_users
ORDER BY username;

-- Check password file location
-- On Linux: $ORACLE_HOME/dbs/orapw<SID>
-- On Windows: %ORACLE_HOME%\database\PWD<SID>.ora
-- Verify from OS: ls -la $ORACLE_HOME/dbs/orapw*
SELECT value AS oracle_home
FROM v$parameter
WHERE name = 'oracle_home';

Setting the Parameter

-- Set to EXCLUSIVE (default; allows multiple SYSDBA users in password file)
ALTER SYSTEM SET remote_login_passwordfile = EXCLUSIVE SCOPE=SPFILE;

-- Set to SHARED (password file shared across multiple databases; SYS only)
ALTER SYSTEM SET remote_login_passwordfile = SHARED SCOPE=SPFILE;

-- Disable password file authentication (OS auth only)
ALTER SYSTEM SET remote_login_passwordfile = NONE SCOPE=SPFILE;

-- After changing, restart the instance
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Grant SYSDBA to a user (requires EXCLUSIVE mode and password file present)
GRANT SYSDBA TO dba_user;

-- Verify the user is now in the password file
SELECT username, sysdba
FROM v$pwfile_users
ORDER BY username;

Tuning Guidance

Recommended Values

Environment	Recommended Value
Standard production database	EXCLUSIVE
Data Guard primary and standby	EXCLUSIVE (password files must be synchronized)
RAC (all instances share a password file in ASM)	EXCLUSIVE
Database with multiple DBA users needing SYSDBA	EXCLUSIVE
High-security environment (no remote SYSDBA)	NONE
Multiple databases sharing one password file (legacy)	SHARED
Oracle 12c+ CDB (manages SYSDBA for all PDBs)	EXCLUSIVE

EXCLUSIVE is the correct choice for virtually all modern Oracle deployments. SHARED is a legacy option that predates per-database password files and has significant limitations. NONE is appropriate only in highly controlled environments where all DBA access is restricted to the local OS.

How to Size

There is no numeric sizing for this parameter. The key administrative tasks are creating the password file, granting the appropriate privileges, and ensuring the file is synchronized in Data Guard environments.

-- Create a new password file from the OS (run as oracle user)
-- orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password> entries=20

-- In Oracle 12c+, create password file with ASM option for RAC
-- orapwd file='+DATA/mydb/orapwmydb' password=<sys_password> entries=20 format=12

-- Check current password file format (12c+)
SELECT username, sysdba, sysoper
FROM v$pwfile_users
ORDER BY username;

-- Verify password file entries count
SELECT COUNT(*) AS total_sysdba_users
FROM v$pwfile_users
WHERE sysdba = 'TRUE';

-- Grant additional privileged roles
GRANT SYSBACKUP TO rman_user;
GRANT SYSDG TO dataguard_user;

Monitoring

-- Monitor privileged user changes in password file
SELECT username, sysdba, sysoper, sysasm, sysbackup, sysdg, syskm
FROM v$pwfile_users
ORDER BY username;

-- Audit SYSDBA connections (requires AUDIT_SYS_OPERATIONS=TRUE)
SELECT os_username, username, userhost, terminal,
       timestamp, action_name, returncode
FROM dba_audit_trail
WHERE priv_used IN ('SYSDBA', 'SYSOPER')
ORDER BY timestamp DESC
FETCH FIRST 50 ROWS ONLY;

-- In 12c+ with unified auditing
SELECT event_timestamp, db_username, userhost,
       system_privilege_used, action_name, return_code
FROM unified_audit_trail
WHERE system_privilege_used IN ('SYSDBA', 'SYSOPER', 'SYSBACKUP')
ORDER BY event_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

-- Check if password file is accessible at OS level
-- ls -la $ORACLE_HOME/dbs/orapw$ORACLE_SID (Linux)
-- On Windows: dir %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora

Common Issues

Issue 1: ORA-01031 When Connecting AS SYSDBA Remotely

Remote connections with AS SYSDBA fail with ORA-01031: insufficient privileges when the password file is missing, inaccessible, or the user has not been granted SYSDBA in the password file.

Resolution: Verify the password file exists at the expected location, confirm REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and grant SYSDBA to the connecting user.

-- Check if user is in the password file
SELECT username, sysdba
FROM v$pwfile_users
WHERE username = 'YOUR_USER';

-- If not present, grant SYSDBA
GRANT SYSDBA TO your_user;

-- If password file is missing, recreate it from OS:
-- orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<sys_password>
-- Then restart the instance

Issue 2: Data Guard Standby Password File Out of Sync

In a Data Guard configuration, the primary and standby must have identical SYS passwords in their respective password files. If the SYS password is changed on the primary without updating the standby password file, the redo apply and broker connections will fail.

Resolution: In Oracle 12c and later, copy the updated password file to the standby, or use the password file replication feature that automatically propagates password changes through redo.

-- In Oracle 12c+, verify that password file changes replicate automatically
-- When using ASM-based password files with Data Guard

-- Check Data Guard broker status to detect authentication failures
-- dgmgrl> show database verbose mydb_stby

-- Manually synchronize (copy from primary to standby)
-- From OS on standby:
-- scp primary:/oracle/product/19c/db_1/dbs/orapwprimary \
--    /oracle/product/19c/db_1/dbs/orapwstandby

-- Verify SYS login works on standby after sync
-- sqlplus sys/<password>@standby as sysdba

Issue 3: SHARED Mode Prevents Granting SYSDBA to Non-SYS Users

When REMOTE_LOGIN_PASSWORDFILE=SHARED, Oracle only allows the SYS user in the password file. Attempts to grant SYSDBA to other users succeed in the database but are silently ignored at authentication time.

Resolution: Change to EXCLUSIVE mode to support multiple SYSDBA users. Create individual password files per database and update each database’s SPFILE.

-- Switch from SHARED to EXCLUSIVE
ALTER SYSTEM SET remote_login_passwordfile = EXCLUSIVE SCOPE=SPFILE;
-- Restart instance, then re-grant SYSDBA
GRANT SYSDBA TO dba_user1;
GRANT SYSDBA TO dba_user2;

-- Verify both users appear in password file
SELECT username, sysdba
FROM v$pwfile_users
ORDER BY username;

Related Parameters

• AUDIT_TRAIL — Configures where privileged operation audit records are written; SYSDBA connections are audited to OS regardless
• SEC_CASE_SENSITIVE_LOGON — Password case sensitivity; SYS password in the password file is always case-sensitive in Oracle 11g+
• O7_DICTIONARY_ACCESSIBILITY — Controls access to the data dictionary; related to privileged user security configuration

Related Errors

• ORA-01031: Insufficient Privileges — Remote SYSDBA connection fails because user is not in the password file or the file is missing
• ORA-01017: Invalid Username/Password — Wrong password for a SYSDBA user listed in the password file
• ORA-01045: User Lacks CREATE SESSION Privilege — Related privilege issue; even SYSDBA users need the password file entry to connect remotely
• ORA-03113: End-of-file on Communication — Data Guard authentication failures caused by mismatched password files can surface as communication errors

Version Notes

Version	Notes
Oracle 7	Parameter introduced; password file (orapwd) created externally
Oracle 9i	EXCLUSIVE mode allows multiple SYSDBA users
Oracle 11g	SYS password in password file becomes case-sensitive
Oracle 12c	New privileged roles added: SYSBACKUP, SYSDG, SYSKM; these are also stored in the password file
Oracle 12.2	ASM-based password files supported for RAC; automatic propagation to standby via redo
Oracle 19c	No functional changes; EXCLUSIVE remains the recommended default
Oracle 21c / 23ai	Behavior unchanged; password file management improved for Autonomous Database configurations