Why Did My Oracle User Keep Getting Locked? A Step‑by‑Step Investigation

After an application user password was changed on an Oracle 11g RAC cluster (version 11.2.0.4), the account became locked repeatedly within minutes, forcing manual unlocks.

Environment Overview

The database runs on two nodes (db1 and db2) with host entries such as 127.0.0.1 localhost, x.x.x.11 db1, x.x.x.12 db2, etc.

Profile Inspection

Querying the user’s profile revealed it uses the default profile:

SQL> select username, profile from dba_users where username='USER';
USERNAME   PROFILE
---------- ----------
USER       DEFAULT

The DEFAULT profile shows FAILED_LOGIN_ATTEMPTS = 10 and PASSWORD_LOCK_TIME = 1 day, explaining why the account locks after a few failed logins.

Listener Status and Log Location

Running lsnrctl status displayed the listener configuration and indicated the log file at /oracle/app/grid/diag/tnslsnr/db1/listener/alert/log.xml.

Analyzing Listener Logs

The log contained frequent entries like:

10-MAR-2016 13:11:17 * (CONNECT_DATA=(SID=db2)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=)))
* (ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.24)(PORT=43428)) * establish * db2 * 0

This showed a JDBC connection from IP x.x.x.24 to the second RAC node (db2) occurring almost every second.

Discovering the Culprit Application

On host x.x.x.24, a dataSync.jar file was found. Inside its jdbc.properties the connection string was:

jdbc.jdbcUrl=jdbc:oracle:thin:@x.x.x.14:1521:db2

A accompanying shell script launched the jar with a parameter 10, likely the polling interval:

java -Xms1024m -Xmx1024m -XX:MaxPermSize=256m -jar dataSync.jar xx 10 &

Log Evidence of Lockouts

The application’s own log repeatedly reported: ORA-28000: the account is locked confirming that the jar was still trying to connect with the old password.

Remediation Options

Terminate the offending process and remove or disable the shell script.

Update the password in jdbc.properties to match the new credentials.

Additional Listener Activity

Another frequent log entry was identified as coming from Oracle High Availability Services (OHASD), which periodically checks the local listener health.

Key Takeaways

Maintain proper documentation for all applications to avoid hidden dependencies.

Investigate every detail—profiles, listener logs, configuration files—to trace the root cause.

Oracle components such as listeners, profiles, and trace logs are tightly interlinked; thorough, systematic analysis is essential for reliable troubleshooting.