-- check profile
col PROFILE format a10
col LIMIT format a15
select * from dba_profiles where resource_type='PASSWORD'
order by 1,2;
-- options for
trouble shooting
1. audit not successful connections
2. trace 1017 event
-- Steps for option 1
Reference: oracle doc # 352389.1
Reference: oracle
doc # 221944.1
-- setup audit
sqlplus / as sysdba
AUDIT CREATE SESSION BY
ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT CONNECT BY ACCESS
WHENEVER NOT SUCCESSFUL;
-- Get information related to failed connections
col USERID format a10
col USERHOST format a25
col TERMINAL format a10
select returncode,
action#, userid, userhost, terminal from aud$ where returncode='1017' and
action#=100;
SELECT TO_CHAR(NTIMESTAMP#
, 'DD-MON-YYYY HH24:MI:SSxFF'), SESSIONTIMEZONE from aud$ where returncode=1017;
NOTE : TIME is in GMT, so
subtract -5 hours
-- Steps for option 2
-- to trace what is locking the account
1. Login to
sqlplus as sysdba
2. enable trace,
using
alter system set events ‘1017 trace name errorstack level 10’;
3. Show parameter
dump
4. Goto the udump
location
5. grep ORA –
01017 *
6. get the
timestamp and related information from
trace generated
7. When trace is
no more needed , set it off using:
alter system set events ‘1017 trace name
errorstack off’
8. Goto the
listener.log file location
9. Get the
connectivity information for the selected time from listener log file.
Hope this help. Regards Rupam
