Home » RDBMS Server » Server Administration » How to find server process/scripts thats locking an account (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit)
How to find server process/scripts thats locking an account [message #690168] Sat, 09 November 2024 09:32 Go to next message
wtolentino
Messages: 420
Registered: March 2005
Senior Member
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Linux 3.10.0-1160.123.1.el7.x86_64

We have been chasing as to what server process/scripts that is locking an account. There are hundreds if not thousands of scripts in the server and it would be not feasible to manually search one-at-time each. We used every tool at our disposal that we know from using the views like dba_audit_trail, dba_audit_session, logon triggers to with the use of "after servererror on database" to capture the IS_SERVERERROR(1017) return code, among others. Yet we still have no success in finding the process that locks the account. Though we know what account is being locked but could not find the source of locking it.

Log table captured these data when the account is attempted to connect to the database with incorrect password.
/forum/fa/14825/0/


the dbms_utility.format_call_stack returns this:
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
70001032bf01d18         1  anonymous block
70001033ad5d090        23  anonymous block

Also we checked all of the database links that uses the account and it is working.

Please advise how we can locate the process/script that is locking the account.

Thank you.

Report message to a moderator

Re: How to find server process/scripts thats locking an account [message #690169 is a reply to message #690168] Sat, 09 November 2024 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

First it depends on which lock type it is.
See ACCOUNT_STATUS in DBA_USERS
Is it "LOCKED"? Then someone (maybe itself) locked the account (ALTER USER ACCOUNT LOCK).
Is it "LOCKED(TIMED)"? Then lock came from numerous wrong password at connection time.

Then you have DBA_USERS.LOCK_DATE which will give when lock happened and the audit trail will give you has attempted to connect at this time (OS_USERNAME, USERHOST, TERMINAL); COMMENT_TEXT may be of great help.

Report message to a moderator

Re: How to find server process/scripts thats locking an account [message #690173 is a reply to message #690169] Tue, 12 November 2024 14:05 Go to previous messageGo to next message
wtolentino
Messages: 420
Registered: March 2005
Senior Member
account_status is "LOCKED(TIMED)".
lock_date is daily in the morning.

Tested all the DB links that are using the account and appears to be fine.

Is it possible to capture the script/code/process (source code) that is locking the account?

Report message to a moderator

Re: How to find server process/scripts thats locking an account [message #690174 is a reply to message #690173] Tue, 12 November 2024 15:19 Go to previous message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

There are no general answers to your question.

First as I said:
Quote:
the audit trail will give you has attempted to connect at this time (OS_USERNAME, USERHOST, TERMINAL); COMMENT_TEXT may be of great help.
You also have OS_PROCESS.
If you have an OS audit trail, this will give you which one and which program it was at lock time.

If it is a remote connection, from an OS script, you probably cannot determine which script it was.

Post all these information and we can see what we can do next.

Report message to a moderator

Previous Topic: Attempt To Increase a Tablespace throws error ORA-01516
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 01:19:57 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.