Trigger to keep account unlock history

You can meet all your requirements with Standard Database Auditing or with Fine-Grained Auditing.

For the standard auditing to also capture the SQL statements, you would set AUDIT_TRAIL initialization parameter to DB,EXTENDED or XML,EXTENDED (See "Settings for the AUDIT_TRAIL Initialization Parameter" in Database Security Guide). The audit records would include timestamps and SQL text, and could be queried from SYS.AUD$ table if AUDIT_TRAIL value is set to DB,EXTENDED or DB, or read from OS files if AUDIT_TRAIL is set to XML,EXTENDED or XML. In the latter case you can determine where the audit files are located based on the value of the initialization parameter AUDIT_FILE_DEST:

SQL> show parameter audit

NAME                 TYPE        VALUE
-------------------- ----------- ------------------------------
audit_file_dest      string      /u01/app/oracle/admin/orcl/adump

As far as FGA is concerned, I have already described it in my other post here. You would just need to set the other value for the audit_trail parameter when creating the FGA with DBMS_FGA.ADD_POLICY procedure, e. g. DBMS_FGA.DB_EXTENDED.

Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".

This view provides valuable information such as the time the login failures occurred, the computer where the error originated from, as well as the OS user.

If you do not have audit_trail turned on, which you can verify with:

show parameter audit_trail;

then obviously, the above query will not help with past login failures, but you may want to consider turning it on to help with future failures. Also, keep in mind that this change requires a bounce of the database to go into effect.

You can refer to this document in order to set this environment variable to a value that is appropriate to your environment: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm

You would set this parameter as follows:

alter system set audit_trail='DB' scope=spfile;

Keep in mind that if you do turn on audit_trail, consider periodically clean out older records out of the audit table or the table can grow to be pretty unwieldy. There is an informative blog that talks about that here: http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/

As for another possible cause:

If you are running in a distributed environment (multiple databases connected by database links), then proxy database links (null user/password) could be your problem since such database links attempt to login to the destination database with the credentials that are passed from the source database.

If the credentials are different between the source and the destination, ORA-1017 is returned which counts against the login attempts on the destination DB (provided the usercode was the same, and just the passwords are different).

In addition, if the source DB user is logged in via a proxy user, then proxy DB links will ALWAYS fail with ORA-1017 regardless of whether or not the passwords match.