Who is locking your accounts in oracle 12c database

 First check which schema got locked.

SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME='SYSTEM';

USERNAME      ACCOUNT_STATUS                   EXPIRY_DA

-------------------------------- -----------------------------------------------

SYSTEM                  LOCKED

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='SYSTEM';

CAUSE

The situation can happen in case when someone (script or application) is trying to connect to a database with a wrong password 

thereby locking an account. By default, the DEFAULT profile allows 10 failed login attempts before an account will be locked. 

Because of multiple failed login attempts an account switches its status to ‘LOCKED(TIMED)‘. 

An account is locked due to FAILED_LOGIN_ATTEMPTS profile parameter.

=============================================================

Three main view: 

DBA_AUDIT_TRAIL – standard auditing only (from AUD$)

DBA_FGA_AUDIT_TRAIL-Fine grained auditing only (from FGA_LOG$)

DBA_COMMON_AUDIT_TRAIL – Both standard and fine-grained auditing 

FAILED_LOGIN_ATTEMPTS specify the number of consecutive failed attempts to log in to the user account before the account is locked. If you omit this clause, then the default is 10 times.

PASSWORD_LOCK_TIME specify the number of days an account will be locked after the specified number of consecutive failed login attempts. If you omit this clause, then the default is 1 day.

The DBA_AUDIT_SESSION view contains all failed logins attempts made by the script. 

The column RETURNCODE in the view corresponds to the ORA- error raised on the failed logins. 

It’s enough information to find out who or what caused locking of the account. 

==============================================================

Please find below All  AUDIT parameter and SQL statement.

show parameter audit;

NAME                         TYPE    VALUE                                   

---------------------------- ------- --------------------------------------- 

audit_file_dest              string  /u01/app/oracle/admin/oracle/adump 

audit_sys_operations         boolean TRUE                                    

audit_syslog_level           string                                          

audit_trail                  string  DB                                      

unified_audit_sga_queue_size integer 27535

======================================================

SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;

SELECT username,userhost,returncode FROM dba_audit_session; 

SELECT * FROM DBA_STMT_AUDIT_OPTS;

SELECT username,userhost,returncode FROM dba_audit_trail where USERNAME='SYSTEM';

select OS_USERNAME,USERNAME,USERHOST,TERMINAL,TIMESTAMP,OWNER,ACTION_NAME from dba_audit_trail;

SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;

SELECT * FROM dba_audit_session WHERE USERNAME='SYSTEM' ORDER BY sessionid DESC;

SELECT username,userhost,returncode FROM dba_audit_session WHERE RETURNCODE='1017' ORDER BY sessionid DESC;

SELECT username,userhost,returncode FROM dba_audit_session WHERE RETURNCODE='28000' ORDER BY sessionid DESC;

SELECT * FROM DBA_AUDIT_SESSION;

SELECT INSTANCE_NUMBER INUM,OS_USERNAME,USERNAME,USERHOST,TO_CHAR(EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,

ACTION_NAME,OS_PROCESS,RETURNCODE FROM DBA_AUDIT_SESSION WHERE RETURNCODE IN (1017, 28000) ORDER BY EXTENDED_TIMESTAMP;

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

=========================================================================

AUDIT DELETE ANY TABLE BY ACCESS;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT DELETE ANY TABLE BY ACCESS;

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY <schema_name> BY ACCESS;

AUDIT ALL BY <schema_name> BY ACCESS;

==========================================================

Enable auditing for failed logon attempts and then query DBA_AUDIT_SESSION view

AUDIT network BY ACCESS;

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

To disable auditing issue the following command

NOAUDIT SESSION WHENEVER NOT SUCCESSFUL;

===========================END=====================================