ORA-01017 — Password Rotation Broke Database Links

ORA-01017: invalid username/password; logon denied is Oracle's authentication rejection error. It fires when the server cannot validate the supplied credentials against its user store — whether that store is the data dictionary, a password file, an LDAP directory, or an OS account mapping. The error is a hard stop: no session is created, no queries execute, and at the direct connection level, no ORA-06512 stack trace is generated because no PL/SQL was ever invoked.

However, ORA-01017 frequently appears inside ORA-06512 stack traces in production systems when stored procedures use database links with embedded credentials. A procedure that queries a remote database via CREATE DATABASE LINK ... CONNECT TO user IDENTIFIED BY pass will raise ORA-01017 at the remote authentication layer if the embedded credentials are wrong — typically because the remote password was rotated but the link definition still holds the old password. The error propagates through the PL/SQL call stack as ORA-06512 entries, and engineers see the stack trace without immediately recognizing that the root cause is an authentication failure on a database link, not a code defect.

The diagnostic path diverges depending on context. Direct connection failures (an application or tool cannot authenticate) require checking the username, password, password case sensitivity, account status, profile policies, and authentication method. Database link failures (a link query raises ORA-01017 inside a procedure) require checking the link's CONNECT TO clause, the remote database's account status, and whether the remote password was recently rotated. Confusing the two — or checking the wrong credential store — is the most common reason this error wastes hours.

Why ORA-01017 Is a Password-Rotation Landmine

ORA-01017 means the Oracle database rejected a login because the username or password is invalid. The core mechanic: Oracle compares the supplied credential hash against the stored hash in the data dictionary. If they don't match, the connection fails immediately — no retry, no fallback. This is a hard authentication failure, not a transient network error.

In practice, ORA-01017 surfaces most often in fixed-credential contexts: database links, connection pools, and scheduled jobs. A database link stores the remote username and password as encrypted text in the data dictionary. When the remote password changes, every link that uses those credentials becomes stale. The link doesn't refresh automatically — it keeps sending the old password until someone manually updates it. The same applies to JDBC connection pools with hardcoded credentials.

Use this knowledge to audit any system that stores credentials outside the application's runtime configuration. If your team rotates database passwords quarterly, every database link, every cron job, and every connection pool must be updated in lockstep. One missed link causes a production outage that looks like a network issue but is actually a credential mismatch. Treat ORA-01017 as a design smell: it often indicates credential duplication that should be replaced with a centralized secrets manager or token-based authentication.

Oracle Authentication: How ORA-01017 Is Raised

Oracle authentication happens at the session establishment layer, before any SQL statement executes. The client initiates a connection through the TNS listener, which redirects the client to a dedicated server process (or a shared server dispatcher). The server process challenges the client for credentials. The client sends the username and a cryptographic hash of the password. The server computes the same hash from its stored password verifier and compares. If the hashes match, a session is created and the client can begin executing SQL. If they do not match, ORA-01017 is raised and the connection is rejected.

Oracle maintains password verifiers (hashes) in the SYS.USER$ table within the data dictionary. The hashing algorithm depends on the Oracle version and the SQLNET.ALLOWED_LOGON_VERSION_SERVER and SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters: Oracle 10g used a DES-based hash, Oracle 11g added a SHA-1-based hash with case sensitivity, and Oracle 12c and later use SHA-2/SHA-512 with salt. The client and server negotiate which verifier to use during the authentication handshake — if the client is older and only supports the 10g verifier but the server has removed 10g verifiers (12c+ default behavior), the authentication fails with ORA-01017.

The password file (orapwSID) is a separate credential store used exclusively for SYSDBA and SYSOPER connections when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED. Normal connections never consult the password file — they authenticate against the data dictionary. This dual-store model is the root cause of a common ORA-01017 variant: ALTER USER changes the data dictionary hash but does not update the password file, so SYSDBA connections fail while normal connections succeed.

The error message is deliberately identical for all failure modes: wrong username, wrong password, expired password, locked account (in some configurations), and authentication method mismatch. Oracle does not want to confirm whether a username exists, because that information would allow an attacker to enumerate valid accounts. The diagnostic burden falls on the DBA to check each possible cause independently.

-- Step 1: Verify the username exists and check account status
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  profile,
  authentication_type
FROM dba_users
WHERE username = UPPER('app_user');
-- No rows: username does not exist in this database
-- account_status != 'OPEN': account is locked, expired, or both
-- authentication_type: PASSWORD, EXTERNAL, GLOBAL

-- Step 2: Check the profile's password policy
SELECT
  profile,
  resource_name,
  limit
FROM dba_profiles
WHERE profile = (
  SELECT profile FROM dba_users WHERE username = UPPER('app_user')
)
AND resource_type = 'PASSWORD'
ORDER BY resource_name;
-- Key parameters:
-- FAILED_LOGIN_ATTEMPTS: max consecutive failures before lockout
-- PASSWORD_LIFE_TIME: days before password expires
-- PASSWORD_LOCK_TIME: days the account stays locked after max failures
-- PASSWORD_GRACE_TIME: days after expiry before account is fully locked
-- PASSWORD_REUSE_TIME / PASSWORD_REUSE_MAX: reuse restrictions

-- Step 3: Check case-sensitive password setting
SELECT name, value
FROM v$parameter
WHERE name = 'sec_case_sensitive_logon';
-- TRUE  — passwords are case-sensitive (11g+ default)
-- FALSE — passwords are case-insensitive (pre-11g behavior)

-- Step 4: Check which password verifiers exist for the user
SELECT username, password_versions
FROM dba_users
WHERE username = UPPER('app_user');
-- 10G       — DES-based hash only (case-insensitive)
-- 11G       — SHA-1 hash (case-sensitive)
-- 12C       — SHA-2/SHA-512 hash (case-sensitive, salted)
-- 10G 11G   — both verifiers present
-- 11G 12C   — both verifiers present
-- If only 12C exists and the client is old, authentication may fail

-- Step 5: Check password file users
SELECT username, sysdba, sysoper, sysasm
FROM v$pwfile_users;
-- Lists users with privileged connection rights via the password file
-- If the user is listed: SYSDBA auth uses the password file
-- If not listed: SYSDBA auth falls back to OS auth or fails

-- Step 6: Check REMOTE_LOGIN_PASSWORDFILE
SELECT name, value
FROM v$parameter
WHERE name = 'remote_login_passwordfile';
-- NONE      — password file is not used; SYSDBA via OS auth only
-- EXCLUSIVE — one password file per instance (most common)
-- SHARED    — password file shared across instances (RAC with ASM)

-- Step 7: Check allowed logon versions (12c+)
SELECT name, value
FROM v$parameter
WHERE name LIKE '%allowed_logon_version%';
-- SQLNET.ALLOWED_LOGON_VERSION_SERVER: minimum client version accepted
-- SQLNET.ALLOWED_LOGON_VERSION_CLIENT: minimum server version accepted
-- If set to 12 or 12a, clients using 10g or 11g verifiers are rejected

-- Step 8: Check recent failed login attempts in the audit trail
SELECT
  os_username,
  username,
  userhost,
  terminal,
  returncode,
  timestamp#
FROM sys.aud$
WHERE username = UPPER('app_user')
  AND returncode IN (1017, 28000, 28001)
ORDER BY timestamp# DESC
FETCH FIRST 20 ROWS ONLY;
-- returncode 1017 = ORA-01017 (invalid credentials)
-- returncode 28000 = ORA-28000 (account locked)
-- returncode 28001 = ORA-28001 (password expired)
-- Shows which hosts and timestamps generated failures

Case-Sensitive Passwords: The 11g+ Gotcha

Oracle 11g introduced case-sensitive passwords by default. The parameter SEC_CASE_SENSITIVE_LOGON controls this behavior. When set to TRUE (the default since 11g), the password verifier stored in the data dictionary includes case information: 'MyPassword' and 'mypassword' produce different hashes and are treated as different passwords.

This causes ORA-01017 in two common scenarios. First, applications or scripts that convert the password to uppercase before sending it to Oracle — a practice that was harmless in Oracle 10g — will fail if the actual password contains lowercase characters. Second, migrations from Oracle 10g to 11g or later can break all connections if the passwords were originally set without case awareness.

The mechanism is straightforward: the password hash stored in SYS.USER$ is computed from the exact character sequence including case. The client computes the same hash from the password the user supplies. If the user supplies 'Password123' but the stored hash was computed from 'PASSWORD123' (because ALTER USER was run without double quotes, which causes Oracle to uppercase the value), the hashes do not match and ORA-01017 is raised.

The subtlety that catches most engineers: ALTER USER app_user IDENTIFIED BY MyPassword (without double quotes) stores the password as MYPASSWORD in uppercase. This is because Oracle treats the unquoted identifier as a regular SQL identifier and uppercases it. ALTER USER app_user IDENTIFIED BY "MyPassword" (with double quotes) stores the password as MyPassword with the exact case preserved. The quoting behavior during password creation determines the case of the stored verifier — and the user must supply the same case when connecting.

Oracle 12c and later further complicated this by introducing the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter, which controls which password verifiers are accepted. If set to 12 or 12a, only SHA-2 verifiers are accepted, and clients that only support the older 10g DES-based verifier are rejected with ORA-01017. After an upgrade, if the user's password was never reset (so only the old verifier exists), authentication fails even though the password itself is correct.

-- Diagnostic 1: Check current case sensitivity setting
SELECT name, value
FROM v$parameter
WHERE name = 'sec_case_sensitive_logon';
-- TRUE  — case-sensitive (11g+ default)
-- FALSE — case-insensitive (pre-11g behavior)

-- Diagnostic 2: Check which password verifiers the user has
SELECT username, password_versions
FROM dba_users
WHERE username = UPPER('app_user');
-- 10G       — only the old DES hash exists (case-insensitive)
-- 11G       — SHA-1 hash with case sensitivity
-- 12C       — SHA-2/SHA-512 hash with case sensitivity and salt
-- 10G 11G   — both old and new verifiers present
-- 11G 12C   — both verifiers present
-- After upgrade, reset the password to generate the new verifier:
-- ALTER USER app_user IDENTIFIED BY "SamePassword";

-- Diagnostic 3: Check allowed logon versions
SELECT name, value
FROM v$parameter
WHERE name LIKE '%allowed_logon_version%';
-- If ALLOWED_LOGON_VERSION_SERVER = 12 and user only has 10G verifier:
-- ORA-01017 even with the correct password
-- Fix: reset the password to generate the 12C verifier

-- Diagnostic 4: Set password with exact case preserved
ALTER USER app_user IDENTIFIED BY "MyExactPassword123";
-- Double quotes: stores 'MyExactPassword123' with exact case
-- User must login with: sqlplus app_user/MyExactPassword123@DB

-- Diagnostic 5: Set password WITHOUT case preservation (uppercase)
ALTER USER app_user IDENTIFIED BY MyPassword;
-- No quotes: Oracle uppercases to 'MYPASSWORD'
-- User must login with: sqlplus app_user/MYPASSWORD@DB
-- Or any case if SEC_CASE_SENSITIVE_LOGON = FALSE

-- Diagnostic 6: Temporarily disable case sensitivity (emergency only)
ALTER SYSTEM SET sec_case_sensitive_logon = FALSE;
-- Both 'Password' and 'PASSWORD' will now work
-- Weakens security — use only as a temporary workaround during migration
-- Re-enable after fixing all client connection strings:
-- ALTER SYSTEM SET sec_case_sensitive_logon = TRUE;

-- Diagnostic 7: Verify the password works by testing the connection
-- $ sqlplus app_user/"MyExactPassword123"@DB
-- Connected.  — password and case are correct
-- ORA-01017   — password or case is wrong
-- Note: quote the password in the shell if it contains special characters

Account Lockout and Password Expiry

Oracle profiles enforce password policies that control login behavior: maximum failed login attempts before lockout, password lifetime before expiry, lockout duration, grace period after expiry, and password reuse restrictions. When any policy is violated, the account status changes from OPEN to a non-OPEN state, and all connection attempts receive ORA-01017 (or in some cases ORA-28000 for locked accounts, depending on the Oracle version and configuration).

The profile parameter FAILED_LOGIN_ATTEMPTS controls automatic lockout after consecutive failed login attempts. The DEFAULT profile ships with FAILED_LOGIN_ATTEMPTS set to 10 in most Oracle versions. After 10 consecutive wrong-password attempts, the account status becomes LOCKED(TIMED) for the duration specified by PASSWORD_LOCK_TIME (default: 1 day). During the lock period, even correct credentials are rejected.

The profile parameter PASSWORD_LIFE_TIME controls password expiry. After the specified number of days since the last password change, the password expires. The account status transitions to EXPIRED(GRACE) for PASSWORD_GRACE_TIME days, during which the user can still connect but Oracle prompts for a password change. After the grace period, the status becomes EXPIRED and all connections are rejected until the DBA resets the password.

The most dangerous production scenario is a shared service account with a low FAILED_LOGIN_ATTEMPTS value. If one misconfigured application instance sends the wrong password 10 times — perhaps because a configuration file has a stale password — the account locks for all consumers. Every other application, ETL job, and reporting query that uses the same account is blocked until the lockout expires or the DBA manually unlocks it. This cascading lockout is common in microservices environments and connection pool configurations where multiple processes share a single database account.

The fix has two parts: immediate (unlock the account) and permanent (prevent recurrence). For service accounts, create a dedicated profile with FAILED_LOGIN_ATTEMPTS set to UNLIMITED and PASSWORD_LIFE_TIME set to UNLIMITED. For human user accounts, keep the limits but ensure the lockout duration (PASSWORD_LOCK_TIME) is reasonable — 1/1440 (one minute) is often more appropriate than the default 1 day for environments with automated retry logic.

-- Diagnostic 1: Check account status and lock/expiry dates
SELECT
  username,
  account_status,
  lock_date,
  expiry_date,
  profile,
  created,
  last_login
FROM dba_users
WHERE username = UPPER('app_user');
-- account_status values:
-- OPEN               — active, connections allowed
-- LOCKED(TIMED)      — auto-locked after FAILED_LOGIN_ATTEMPTS failures
-- LOCKED             — manually locked by DBA (ALTER USER ACCOUNT LOCK)
-- EXPIRED            — PASSWORD_LIFE_TIME exceeded, grace period ended
-- EXPIRED(GRACE)     — password expired, within grace period
-- EXPIRED & LOCKED   — both conditions simultaneously

-- Diagnostic 2: Check the profile's password policy limits
SELECT
  resource_name,
  limit
FROM dba_profiles
WHERE profile = (
  SELECT profile FROM dba_users WHERE username = UPPER('app_user')
)
AND resource_type = 'PASSWORD'
ORDER BY resource_name;
-- FAILED_LOGIN_ATTEMPTS: consecutive failures before lockout (default: 10)
-- PASSWORD_LIFE_TIME: days before expiry (default: 180 in some versions)
-- PASSWORD_LOCK_TIME: days locked after max failures (default: 1)
-- PASSWORD_GRACE_TIME: days after expiry to change password (default: 7)
-- PASSWORD_REUSE_TIME: days before a password can be reused
-- PASSWORD_REUSE_MAX: number of changes before a password can be reused

-- Fix 1: Unlock a locked account
ALTER USER app_user ACCOUNT UNLOCK;
-- Takes effect immediately — no restart required

-- Fix 2: Reset an expired password
ALTER USER app_user IDENTIFIED BY "NewSecurePassword";
-- Changes the password AND clears the EXPIRED status
-- Use double quotes to preserve exact case

-- Fix 3: Unlock AND reset simultaneously
ALTER USER app_user IDENTIFIED BY "NewSecurePassword" ACCOUNT UNLOCK;

-- Prevention 1: Create a profile for service accounts
CREATE PROFILE svc_account_profile LIMIT
  FAILED_LOGIN_ATTEMPTS UNLIMITED
  PASSWORD_LIFE_TIME    UNLIMITED
  PASSWORD_LOCK_TIME    UNLIMITED
  PASSWORD_GRACE_TIME   UNLIMITED
  PASSWORD_REUSE_TIME   UNLIMITED
  PASSWORD_REUSE_MAX    UNLIMITED;
-- No lockout, no expiry — appropriate for service accounts only
-- Human user accounts should retain policy limits

-- Prevention 2: Assign the profile to the service account
ALTER USER app_user PROFILE svc_account_profile;

-- Prevention 3: For human users, use a reasonable lock time instead of UNLIMITED
CREATE PROFILE human_user_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LOCK_TIME    1/1440  -- 1 minute (1/1440 of a day)
  PASSWORD_LIFE_TIME    90
  PASSWORD_GRACE_TIME   7;

-- Diagnostic 3: Check the audit trail for failed login patterns
SELECT
  os_username,
  username,
  userhost,
  terminal,
  returncode,
  TO_CHAR(timestamp#, 'YYYY-MM-DD HH24:MI:SS') AS failure_time
FROM sys.aud$
WHERE username = UPPER('app_user')
  AND returncode IN (1017, 28000, 28001)
ORDER BY timestamp# DESC
FETCH FIRST 30 ROWS ONLY;
-- returncode 1017 = ORA-01017 (wrong credentials)
-- returncode 28000 = ORA-28000 (account locked)
-- returncode 28001 = ORA-28001 (password expired)
-- userhost shows which machine is sending the wrong password

Database Link ORA-01017: Stale Credentials After Password Rotation

Database links store credentials statically in the CONNECT TO ... IDENTIFIED BY clause at creation time. The password embedded in the link definition is a frozen snapshot — it is never automatically updated when the remote database password changes. When the remote password is rotated, the link still authenticates with the old password, and every query through the link raises ORA-01017.

This is the most common cause of ORA-01017 in production environments with password rotation policies. The pattern repeats every rotation cycle: the security team rotates the remote password, the database links still hold the old password, the nightly ETL or reporting pipeline fails with ORA-01017 inside ORA-06512 stacks, and the team spends hours checking the remote account status (which is OPEN and healthy) before realizing the link credentials are stale.

There is no ALTER DATABASE LINK command to update the password. The link must be dropped and recreated with the new password. This means the password rotation process must include an explicit step to enumerate all database links that reference the rotated account, drop each link, and recreate it with the new password. Failing to include this step guarantees an ORA-01017 incident on the next execution of any procedure that uses the link.

For environments that rotate passwords frequently, Oracle Wallet (External Password Store) can centralize credentials outside the link definition. The link is created with CONNECT TO user IDENTIFIED BY VALUES ':wallet_entry' (or simply without an IDENTIFIED BY clause if the wallet is configured for the connect string). Password updates are made to the wallet, and the link inherits the new password without DDL changes. However, wallet configuration adds operational complexity and must be managed carefully in RAC and Data Guard environments.

The immediate diagnostic for database link ORA-01017 is to query USER_DB_LINKS or DBA_DB_LINKS, identify the link's username and host (TNS alias), and test the credentials directly against the remote database. If direct authentication with the same username and password succeeds, the link definition has a different (older) password.

-- Diagnostic 1: List all database links and their embedded credentials
SELECT
  db_link,
  username AS remote_user,
  host     AS tns_alias,
  created
FROM user_db_links
ORDER BY db_link;
-- USERNAME shows the remote account the link authenticates as
-- If this account's password was rotated, the link will fail

-- Diagnostic 2: List all links across every schema (run as DBA)
SELECT
  owner,
  db_link,
  username AS remote_user,
  host     AS tns_alias,
  created
FROM dba_db_links
ORDER BY owner, db_link;
-- Shows ALL links in ALL schemas that might reference the rotated account

-- Diagnostic 3: Find all links that authenticate as a specific user
SELECT
  owner,
  db_link,
  host AS tns_alias
FROM dba_db_links
WHERE username = UPPER('app_user')
ORDER BY owner, db_link;
-- These are the links that will break when app_user's password is rotated

-- Diagnostic 4: Test a specific database link
SELECT 1 FROM dual@remote_prod;
-- Returns 1: link credentials are valid
-- ORA-01017: link credentials are stale (wrong password)
-- ORA-12154: link's TNS alias cannot be resolved (different problem)
-- ORA-28000: remote account is locked (different problem)

-- Diagnostic 5: Test ALL links in the current schema
SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
  FOR rec IN (
    SELECT db_link, username FROM user_db_links ORDER BY db_link
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'SELECT 1 FROM dual@' || rec.db_link;
      DBMS_OUTPUT.PUT_LINE('OK:      ' || rec.db_link || ' (user: ' || rec.username || ')');
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAIL:    ' || rec.db_link || ' (user: ' || rec.username || ') — ' || SQLERRM);
    END;
  END LOOP;
END;
/

-- Fix: Drop and recreate the database link with the new password
DROP DATABASE LINK remote_prod;

CREATE DATABASE LINK remote_prod
  CONNECT TO app_user IDENTIFIED BY "NewRotatedPassword"
  USING 'REMOTE_PROD';
-- Use double quotes around the password to preserve exact case
-- Replace REMOTE_PROD with the TNS alias or inline descriptor

-- Verify the recreated link works
SELECT 1 FROM dual@remote_prod;

-- Alternative: Inline TNS descriptor (no tnsnames.ora dependency)
CREATE DATABASE LINK remote_prod
  CONNECT TO app_user IDENTIFIED BY "NewRotatedPassword"
  USING '(DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=remote.example.com)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=REMOTE_PROD))
  )';

Password File and SYSDBA Authentication

SYSDBA and SYSOPER connections use the password file (orapwSID) as their credential store, not the data dictionary. When REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED, the password file is consulted for privileged connections. If the password file contains a different hash than the one computed from the supplied password, ORA-01017 is raised — even though the data dictionary password (used for normal connections) is correct.

The password file is a binary file located at $ORACLE_HOME/dbs/orapwSID on Linux or %ORACLE_HOME%\database\PWDsid.ora on Windows. It stores the username and password hash for users who have been granted SYSDBA or SYSOPER privileges. The critical detail is that ALTER USER does not automatically update the password file. When a DBA runs ALTER USER sys IDENTIFIED BY newpass, the data dictionary hash in SYS.USER$ is updated, but the password file retains the old hash. SYSDBA connections using the password file still authenticate against the old hash and fail.

The fix is to regenerate the password file using the orapwd utility after any password change for a SYSDBA-privileged user. In Oracle 12.2 and later, ALTER USER for the SYS user does update the password file automatically when REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE and the password file is in the default location. However, this automatic update does not work for non-SYS users with SYSDBA grants, for SHARED password files, or when the password file is stored in a non-default location.

In RAC environments, the password file must be identical on every node. After regenerating the file on one node, it must be copied to all other nodes — or the password file must be stored in ASM (Oracle 12c+), which provides automatic sharing across all nodes. A common production failure is: the DBA regenerates the password file on node 1, SYSDBA works on node 1, but SYSDBA fails on nodes 2 and 3 because they still have the old file.

In Data Guard environments, the password file must be synchronized between the primary and standby databases. If the primary's password file is updated but the standby's is not, a switchover or failover to the standby will result in ORA-01017 for all SYSDBA connections.

-- Diagnostic 1: Check REMOTE_LOGIN_PASSWORDFILE setting
SELECT name, value
FROM v$parameter
WHERE name = 'remote_login_passwordfile';
-- NONE      — password file not used; SYSDBA only via OS auth
-- EXCLUSIVE — one password file per instance (most common)
-- SHARED    — one password file shared across instances

-- Diagnostic 2: List all users in the password file
SELECT username, sysdba, sysoper, sysasm
FROM v$pwfile_users;
-- Shows which users have privileged access via the password file
-- If a user is listed here, their SYSDBA auth uses this file
-- If not listed, SYSDBA attempts will use OS auth or fail

-- Diagnostic 3: Find the password file location
-- Linux:
-- $ ls -la $ORACLE_HOME/dbs/orapw*
-- -rw-r----- 1 oracle oinstall 3584 Apr 14 01:00 orapwPROD
--
-- Windows:
-- > dir %ORACLE_HOME%\database\PWD*
-- PWDprod.ora    3584  04/14/2026  01:00 AM
--
-- ASM (12c+):
-- SQL> SELECT name FROM v$passwordfile_info;
-- +DATA/PROD/PASSWORD/pwdprod.256.1234567890

-- Diagnostic 4: Regenerate the password file (Linux)
-- $ orapwd file=$ORACLE_HOME/dbs/orapwPROD \
--         password=new_sys_password \
--         entries=30 \
--         force=y \
--         format=12.2
-- file: path to the password file
-- password: the SYS password
-- entries: maximum number of SYSDBA/SYSOPER users
-- force=y: overwrite the existing file
-- format=12.2: use the 12c format (supports longer passwords)

-- Diagnostic 5: Regenerate the password file (Windows)
-- > orapwd file=%ORACLE_HOME%\database\PWDprod.ora \
--          password=new_sys_password \
--          entries=30 \
--          force=y

-- Diagnostic 6: Verify SYSDBA connection after regeneration
-- $ sqlplus sys/new_sys_password@PROD as sysdba
-- Connected.
-- If still ORA-01017: check the file path matches what Oracle expects

-- Diagnostic 7: Copy password file to all RAC nodes
-- $ scp $ORACLE_HOME/dbs/orapwPROD node2:$ORACLE_HOME/dbs/orapwPROD
-- $ scp $ORACLE_HOME/dbs/orapwPROD node3:$ORACLE_HOME/dbs/orapwPROD
-- Verify on each node:
-- $ sqlplus sys/new_sys_password@PROD_NODE2 as sysdba

-- Diagnostic 8: Store password file in ASM (12c+ — eliminates RAC sync)
-- $ srvctl modify database -db PROD -pwfile +DATA/PROD/PASSWORD/pwdprod
-- All RAC nodes automatically share the same file

-- Diagnostic 9: Copy password file to Data Guard standby
-- $ scp $ORACLE_HOME/dbs/orapwPROD standby_host:$ORACLE_HOME/dbs/orapwSTBY
-- Required for switchover/failover to work with SYSDBA auth

External Authentication Fallback Failures

Oracle supports multiple external authentication methods: OS authentication (ops$ accounts on Linux, NTS on Windows), Kerberos, LDAP, and RADIUS. When external authentication is configured as the primary method but the external credential check fails, Oracle may silently fall back to password authentication. If the password is also wrong — or the user does not have a database password at all — ORA-01017 is raised. The error message gives no indication that an external authentication attempt was made and failed before the password fallback.

The sqlnet.ora parameter SQLNET.AUTHENTICATION_SERVICES controls which authentication methods are active. On Windows, the default value is (NTS), which enables Windows domain authentication. On Linux, the default is typically (NONE) or unset, which means password authentication only. If the parameter includes multiple methods, Oracle tries each in order.

The most confusing scenario is: a DBA connects with sqlplus / as sysdba on the database server and succeeds (OS authentication works because the OS user matches the oracle account). They assume the password is therefore correct. But a remote application connecting with sqlplus user/pass@DB fails with ORA-01017 because it uses password authentication, and the password in the connection string does not match the data dictionary hash. OS authentication and password authentication are completely independent paths — success in one does not prove the other will work.

Another common scenario is ops$ accounts. On Linux, if SQLNET.AUTHENTICATION_SERVICES includes BEQ, Oracle maps the OS username to a database user named OPS$<osuser>. If the OS user is 'oracle', Oracle looks for a database user named OPS$ORACLE. If this user does not exist in the database, the OS authentication fails silently and Oracle falls back to password authentication — which may also fail, producing ORA-01017 with no indication that the real problem is a missing ops$ account.

The diagnostic approach is to determine exactly which authentication method the failing connection is using. Check SQLNET.AUTHENTICATION_SERVICES in the sqlnet.ora that the failing process reads (not the DBA's sqlnet.ora). If external authentication is configured, verify the external credential source (OS account, Kerberos ticket, LDAP bind). If password authentication is the intended method, set SQLNET.AUTHENTICATION_SERVICES = (NONE) to disable external methods and force password-only authentication.

-- Diagnostic 1: Check which authentication methods are configured
-- File: $TNS_ADMIN/sqlnet.ora
-- $ grep -i AUTHENTICATION_SERVICES $TNS_ADMIN/sqlnet.ora
-- Common values:
-- SQLNET.AUTHENTICATION_SERVICES = (NONE)       -- password only
-- SQLNET.AUTHENTICATION_SERVICES = (BEQ, NONE)  -- OS auth first, password fallback
-- SQLNET.AUTHENTICATION_SERVICES = (NTS)         -- Windows domain auth
-- SQLNET.AUTHENTICATION_SERVICES = (KERBEROS5)   -- Kerberos only
-- SQLNET.AUTHENTICATION_SERVICES = (ALL)          -- try everything

-- Diagnostic 2: Check for ops$ accounts (OS authentication)
SELECT username, account_status, authentication_type
FROM dba_users
WHERE username LIKE 'OPS$%'
ORDER BY username;
-- If the OS user is 'oracle', Oracle looks for OPS$ORACLE
-- If no matching ops$ account exists: OS auth fails silently

-- Diagnostic 3: Check if the user is configured for external auth
SELECT username, authentication_type
FROM dba_users
WHERE username = UPPER('app_user');
-- PASSWORD: uses data dictionary password
-- EXTERNAL: uses OS / external authentication
-- GLOBAL:   uses LDAP / directory authentication

-- Diagnostic 4: Test OS authentication explicitly
-- On the database server, as the oracle OS user:
-- $ sqlplus /
-- Connected.  — OS auth works (ops$ account exists and matches)
-- ORA-01017   — OS auth failed (ops$ account missing or mismatch)

-- Diagnostic 5: Test password authentication explicitly
-- From any machine:
-- $ sqlplus app_user/"MyPassword"@DBNAME
-- Connected.  — password auth works
-- ORA-01017   — password is wrong, case mismatch, or account locked

-- Diagnostic 6: Force password-only authentication
-- File: $TNS_ADMIN/sqlnet.ora
-- SQLNET.AUTHENTICATION_SERVICES = (NONE)
-- This disables all external auth methods
-- Restart the application after changing sqlnet.ora

-- Diagnostic 7: Check if Kerberos ticket is valid
-- $ klist
-- If no valid ticket: Kerberos auth fails, may fall back to password
-- Renew ticket: $ kinit username@REALM

-- Diagnostic 8: Create an ops$ account for OS authentication
CREATE USER "OPS$ORACLE" IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO "OPS$ORACLE";
-- The double quotes preserve the exact case of the username
-- The OS username must match exactly (including case on Linux)