Home » RDBMS Server » Security » Who / what keeps locking one of database users? (Oracle 11.2.0.3, MS Windows)
| Who / what keeps locking one of database users? [message #654849] |
Tue, 16 August 2016 03:33  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Hello everyone!
I'm afraid I need assistance ... here's the story: our department runs a (small) Oracle database which contains several users. One of them is created for a colleague who is not an IT person, but he has self-educated himself, learnt SQL and Apex and created an application used by a few other colleagues.
Recently, he changed the password which - in turn - causes problems because something keeps locking the user. He doesn't know what might be doing that.
As I'm not a DBA (and there's none here; we have just installed the database and, as usual, watch Oracle administer itself), I don't know where to look for a culprit. Here's what I managed to do so far: querying DBA_AUDIT_TRAIL for RETURNCODE = 1017 (invalid username/password), by truncating timestamp to hours (to remove superfluous lines) I got the following result (all dates are DD.MM.YYYY HH24:MI):
SQL> SELECT DISTINCT os_username,
2 username,
3 userhost,
4 terminal,
5 TRUNC (timestamp, 'hh24') timestamp_trunc_to_hh24,
6 to_char(timestamp, 'Dy', 'nls_date_language = english') day,
7 action,
8 action_name
9 FROM dba_audit_trail
10 WHERE RETURNCODE = '1017'
11 AND username = 'MB_USER'
12 AND timestamp > DATE '2016-08-01'
13 ORDER BY TRUNC (timestamp, 'hh24') DESC;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP_TRUNC_ DAY ACTION ACTION_NAME
------------- -------- ------------ -------- ---------------- --- ------- -----------
Administrator MB_USER dbs1 unknown 15.08.2016 21:00 Mon 100 LOGON
Administrator MB_USER dbs1 unknown 12.08.2016 21:00 Fri 100 LOGON
Administrator MB_USER dbs1 unknown 11.08.2016 21:00 Thu 100 LOGON
Administrator MB_USER dbs1 unknown 10.08.2016 21:00 Wed 100 LOGON
MB_USER MB_USER MB_USER unknown 10.08.2016 07:00 Wed 100 LOGON
Administrator MB_USER dbs1 unknown 09.08.2016 21:00 Tue 100 LOGON
Administrator MB_USER dbs1 unknown 08.08.2016 21:00 Mon 100 LOGON
Administrator MB_USER dbs1 unknown 04.08.2016 21:00 Thu 100 LOGON
Administrator MB_USER dbs1 unknown 03.08.2016 21:00 Wed 100 LOGON
Administrator MB_USER dbs1 unknown 02.08.2016 21:00 Tue 100 LOGON
Administrator MB_USER dbs1 unknown 01.08.2016 21:00 Mon 100 LOGON
11 rows selected.
It appears that all those unsuccessful attempts begin at 21 o'clock. However, they don't appear EVERY day, but only on Croatian working days (Monday - Friday), Saturdays and Sundays excluded.
Furthermore, 05.08.2016 (Friday) was our national holiday (non-working day) and you don't see that date in the result list.
On the other hand, 15.08.2016 (Monday) was yet another holiday, but there was unsuccessful login attempt (?!?).
OS_USERNAME = MB_USER on 10.08.2016 07:00 was his fault (wrongly typed password).
All other rows, having USERHOST (dbs1) suggest that the "source" of the problem is on our database server (at least, that's what I think).
I checked DBA_JOBS for that user - there are only materialized views refresh jobs which successfully run at ~07:50:
SQL> SELECT log_user,
2 priv_user,
3 schema_user,
4 last_date,
5 failures,
6 SUBSTR (what, 1, 40) what
7 FROM dba_jobs
8 WHERE schema_user = 'MB_USER'
9 ORDER BY next_date;
LOG_USER PRIV_USE SCHEMA_USER LAST_DATE FAILURES WHAT
-------- -------- ----------- ---------------- ---------- ----------------------------------------
MB_USER MB_USER MB_USER 16.08.2016 07:48 0 dbms_refresh.refresh('"MB_USER"."MV_NKO_
MB_USER MB_USER MB_USER 16.08.2016 07:48 0 dbms_refresh.refresh('"MB_USER"."MV_NKO_
MB_USER MB_USER MB_USER 10.08.2016 07:48 0 dbms_refresh.refresh('"MB_USER"."MV_NKO_
MB_USER MB_USER MB_USER 16.08.2016 07:49 0 dbms_refresh.refresh('"MB_USER"."MV_NKO_
There's no job which runs at 21 o'clock:
SQL> SELECT log_user,
2 priv_user,
3 schema_user,
4 last_date,
5 failures,
6 SUBSTR (what, 1, 40) what
7 FROM dba_jobs
8 WHERE to_char(last_date, 'hh24') = '21'
9 ORDER BY next_date;
no rows selected
SQL>
Then I checked ALL_SOURCE and DBA_SOURCE, looking for his old password - didn't find anything.
SQL> SELECT *
2 FROM all_source
3 WHERE UPPER (text) LIKE '%OLD_PASSWORD%';
no rows selected
SQL>
I feel that it must be some kind of a job, materialized view refresh or something like that, but don't know how to find it. I'm out of ideas. What / where should I look next?
Regards,
LF
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654912 is a reply to message #654870] |
Tue, 16 August 2016 17:47  |
cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
I'm pretty sure it has to be something external to the DB. Dbms_job and the scheduler shouldn't need to ever use the password, they don't really log on. Only possible exception I can think of is a db link. I'm not sure how they behave if targets password changes and I'm not in a position to check at the moment.
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654914 is a reply to message #654849] |
Tue, 16 August 2016 19:35 |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This might be totally irrelevant, but you never know. What time does the last person go home? Is there a cleaning crew that works at night? I worked at one place where my overnight jobs kept aborting. I finally found out that the last person to leave, in order to save electricity, was going around to each desk and turning off all of the computers. In another case, the cleaning crew was unplugging computers in order to plug in their vacuum cleaner as they moved from one area to another, then plugging the computers back in. There was also somebody who brought their kids with them and they went from desk to desk eating candy and playing with things. The consistent late evening time makes you wonder. Maybe somebody is coming in at 2100 each evening and trying to logon 3 times and then something is locking the user. What does your profile do? Does it automatically lock after 3 attempts? Can you find out who is there at 2100 or if somebody is remotely accessing then? However, since the time is so precise, you have to suspect that it is something automated. Is the main power automatically shut off and the night lights turned on? Would any of these things cause something that was running under a user on your system to lock that account?
[Updated on: Tue, 16 August 2016 20:03] Report message to a moderator |
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654921 is a reply to message #654915] |
Wed, 17 August 2016 01:06 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Hello, CM & Barbara!
Thank you for your thoughts.
As of database links: there's only one DB link which was created by that user. There's no DB link which connects to it:
SQL> select * from dba_db_links
2 where owner = 'MB_USER'
3 or username = 'MB_USER';
OWNER DB_LINK USERNAME HOST CREATED
-------- ------------------------- ---------- ---------- --------------------
MB_USER DBL_DEUSER DEUSER ora10 20.08.2015
SQL>
DBA_PROFILES says the following:
SQL> select dba_profiles.*
2 from dba_users, dba_profiles
3 where dba_users.username = 'MB_USER'
4 and dba_users.profile = dba_profiles.profile;
PROFILE RESOURCE_NAME RESOURCE LIMIT
------- -------------------------------- -------- ----------
DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED
DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED
DEFAULT CPU_PER_SESSION KERNEL UNLIMITED
DEFAULT CPU_PER_CALL KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED
DEFAULT IDLE_TIME KERNEL UNLIMITED
DEFAULT CONNECT_TIME KERNEL UNLIMITED
DEFAULT PRIVATE_SGA KERNEL UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
16 rows selected.
FAILED_LOGIN_ATTEMPTS is set to 10; here's the newest output (yesterday evening) (of course, the user is locked again):SQL> SELECT os_username,
2 username,
3 userhost,
4 terminal,
5 timestamp,
6 action,
7 action_name
8 FROM dba_audit_trail
9 WHERE RETURNCODE = '1017'
10 AND username = 'MB_USER'
11 AND timestamp > DATE '2016-08-16'
12 ORDER BY timestamp;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP ACTION ACTION_NAME
------------- -------- ------------ -------- ------------------- ---------- -----------
Administrator MB_USER dbs1 unknown 16.08.2016 21:00:56 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:00:56 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:00:57 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:00:58 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:00 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:03 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:07 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:12 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:19 100 LOGON
Administrator MB_USER dbs1 unknown 16.08.2016 21:01:26 100 LOGON
10 rows selected.
SQL>
I don't think that the problem has anything to do with our cleaning ladies, children, electricity and similar.
There *must* be something scheduled (whether internal or external to the database), but I can't find it. So frustrating ...
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654928 is a reply to message #654870] |
Wed, 17 August 2016 02:16 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Littlefoot wrote on Tue, 16 August 2016 12:51
For 11g, https://server:5500/em opens a page which says that database is down (the one we're talking about is up) so I'm not sure that it is the correct address (but that's the one returned by Windows' "Start - Oracle 11g - Database Control").
OK, I fixed that (DBSNMP and SYSMAN users have been EXPIRED; I altered them using the USER$.SPARE4 column value). OEM on 11g is now running so I connected to it as SYS and checked "Jobs" and "Schedules" under the "Server" tab, "Oracle Scheduler" section. I ran the job related to MB_USER and it was successfully executed (i.e. didn't cause MB_USER to lock).
Should I check something else? I'm not sure what this is:John
... has OEM Database Control been configured for the database? It too has a job scheduler which can store credentials.
If you meant to say that MB_USER scheduled a job using OEM, I'm pretty much sure that he didn't as he does NOT have access to the database server, nor he has privileged credentials so that he could connect to the database (apart from using his own username/password).
[Updated on: Wed, 17 August 2016 02:19] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654966 is a reply to message #654921] |
Wed, 17 August 2016 14:12 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
From your query results:
Quote:
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10
DEFAULT PASSWORD_LOCK_TIME PASSWORD 1
From the documentation:
Quote:
FAILED_LOGIN_ATTEMPTS Specify the number of consecutive failed attempts to log in to the user account before the account is locked.
PASSWORD_LOCK_TIME Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.
So, after 10 failed login attempts, the account locks for one day, then does another 10 failed login attempts, then the account locks for another day, and so on. So, it looks like you have some automated process that is causing this to repeat every 24 hours at 9 p.m. I don't know if you just want to accept that or change the profile to see if it alters things. If you either accept it or test and confirm, then that still leaves you with trying to find that automated process. It sounds like you have already accepted that it is some automated process.
Presumably, the automated process that is trying to login is failing because it is using an outdated password. Is it possible to obtain the old password and reset it to that and see what the automated process runs?
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654970 is a reply to message #654968] |
Wed, 17 August 2016 15:58 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It is past 21 o'clock here, user is locked (of course it is!). Here's the output:
SQL> SELECT os_username,
2 username,
3 userhost,
4 terminal,
5 timestamp,
6 action,
7 action_name
8 FROM dba_audit_trail
9 WHERE RETURNCODE = '1017'
10 AND username = 'MB_USER'
11 AND timestamp > DATE '2016-08-17'
12 ORDER BY timestamp;
OS_USERNAME USERNAME USERHOST TERMINAL TIMESTAMP ACTION ACTION_NAME
------------- -------- ------------ -------- ------------------- ---------- -----------
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:00 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:00 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:01 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:02 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:04 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:07 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:11 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:16 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:23 100 LOGON
Administrator MB_USER dbs1 unknown 17.08.2016 21:01:30 100 LOGON
10 rows selected.
SQL> select lock_date from dba_users where username = 'MB_USER';
LOCK_DATE
-------------------
17.08.2016 21:01:30
SQL>
Listener.log contents that covers time period specified in the previous query results (IP address different from 127.0.0.1 is masked):
Wed Aug 17 21:00:54 2016
17-AUG-2016 21:00:54 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14967)) * establish * ora11 * 0
17-AUG-2016 21:00:56 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14968)) * establish * ora11 * 0
17-AUG-2016 21:00:57 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14969)) * establish * ora11 * 0
17-AUG-2016 21:00:58 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14970)) * establish * ora11 * 0
17-AUG-2016 21:01:00 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14973)) * establish * ora11 * 0
17-AUG-2016 21:01:00 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14974)) * establish * ora11 * 0
17-AUG-2016 21:01:01 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14975)) * establish * ora11 * 0
17-AUG-2016 21:01:01 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14976)) * establish * ora11 * 0
17-AUG-2016 21:01:02 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14977)) * establish * ora11 * 0
17-AUG-2016 21:01:02 * service_update * ora11 * 0
Wed Aug 17 21:01:04 2016
17-AUG-2016 21:01:04 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14979)) * establish * ora11 * 0
17-AUG-2016 21:01:05 * service_update * ora11 * 0
17-AUG-2016 21:01:07 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14980)) * establish * ora11 * 0
17-AUG-2016 21:01:11 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14981)) * establish * ora11 * 0
Wed Aug 17 21:01:17 2016
17-AUG-2016 21:01:17 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14987)) * establish * ora11 * 0
17-AUG-2016 21:01:23 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14988)) * establish * ora11 * 0
Wed Aug 17 21:01:30 2016
17-AUG-2016 21:01:30 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14991)) * establish * ora11 * 0
17-AUG-2016 21:01:30 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14992)) * establish * ora11 * 0
17-AUG-2016 21:01:32 * service_update * ora11 * 0
17-AUG-2016 21:01:34 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=D:\oracle11\product\11.2.0\dbhome_1\BIN\expdp.exe)(HOST=DPKC-DBS1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.2.6)(PORT=14993)) * establish * ora11 * 0
17-AUG-2016 21:01:35 * service_update * ora11 * 0
Wed Aug 17 21:01:44 2016
17-AUG-2016 21:01:44 * service_update * ora11 * 0
Wed Aug 17 21:02:20 2016
17-AUG-2016 21:02:20 * service_update * ora11 * 0
Wed Aug 17 21:02:50 2016
17-AUG-2016 21:02:50 * service_update * ora11 * 0
17-AUG-2016 21:02:55 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=D:\oracle11\product\11.2.0\dbhome_1\perl\bin\perl.exe)(HOST=DPKC-DBS1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.2.6)(PORT=15007)) * establish * ora11 * 0
17-AUG-2016 21:02:58 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=D:\oracle11\product\11.2.0\dbhome_1\perl\bin\perl.exe)(HOST=DPKC-DBS1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.2.6)(PORT=15010)) * establish * ora11 * 0
17-AUG-2016 21:02:59 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=D:\oracle11\product\11.2.0\dbhome_1\perl\bin\perl.exe)(HOST=DPKC-DBS1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.2.6)(PORT=15013)) * establish * ora11 * 0
17-AUG-2016 21:03:00 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=D:\oracle11\product\11.2.0\dbhome_1\perl\bin\perl.exe)(HOST=DPKC-DBS1)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=1.1.2.6)(PORT=15016)) * establish * ora11 * 0
I don't know how to interpret it (i.e. I don't see what Ed mentioned, "entry in the listener log that corresponds (within a couple of seconds) to the time the user got locked. The listener log should show you what machine and OS user is doing the deed."). Everything seems to be localhost (database server) ... Does someone of you see something useful here?
As of the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME: That might save me from altering the user every morning; I could set PASSWORD_LOCK_TIME to, say, 1/24 (one hour) which should be OK. Thank you for the suggestion!
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654984 is a reply to message #654982] |
Thu, 18 August 2016 01:20 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I see; there's another listener (10g's) so now I checked its log too, but it looks similar to 11g's (0 for connection attempts). As you said, it means that failing connections didn't come through any of these two listeners.
Saying "look to local scheduled jobs" means what? What is "local"? Database server or that user's PC?
 It is the database server.
Yesterday, as John suggested, I had a look at server's scheduled tasks (and said that there are only 5 of them active). Sorry, guys, I should have been more careful. It appears that I was, somehow, subconsciously convinced that it MUST be something scheduled, but related explicitly to that user - materialized view refresh or SQL*Loader batch job (as he loads data he needs for calculations he has to do in one/some of his application(s)) or something like that.
I also connected to that user's PC as "Administrator" (as I don't know his password, but I do have administrator's one) and didn't see any suspicious job (some Google updates and similar stuff), but I guessed that I can't see tasks he scheduled when logged under his own account.
Today, after reading your (Michel) message, I re-checked database server's jobs and found the culprit - it is a job that creates backup versions of Apex applications. It is a .BAT file which contains bunch of lines like this:
java oracle.apex.APEXExport -db localhost:1521:ora11 -user mb_user -password his_old_password -applicationid 101
After I modified the script (so that it contains his new password) and started the job, it completed successfully. Well, it didn't generally fail previously either - it just didn't backup his applications (and I didn't notice that in backup log file; it is rather large and APEXExport doesn't report an error when it fails - it just keeps quiet and does nothing). Now I carefully reviewed the backup log file and saw that his applications are backed up as well, there's nothing in the DBA_AUDIT_TRAIL and user isn't locked.
Whoa, what a relief!
Thank you very much, everyone, who helped me solving this! 
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654986 is a reply to message #654984] |
Thu, 18 August 2016 02:03 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
LF, that looks like a neat way to export an APEX application. I could use it! But I can't find the oracle.apex.APEXExport file, is it something standard or something your people wrote? I've looked for it in an APEX 4.0.2 installation, also in APEX 5.0.3.
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654987 is a reply to message #654986] |
Thu, 18 August 2016 02:46 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You should read README.TXT which is located in (for example) C:\Apex_2.1_install\apex\utilities directory. I believe that you'll understand what it says. However, there is (or might be) a "trick" which isn't described anywhere (I found it in my old "Apex 3.2" Arie Geller's book), which says that you have to add ".\" directory into CLASSPATH, so it (finally) looks like this:.\;C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar
Also, case matters - you have to use APEXExport (not apexexport nor APEXEXPORT ...).
Furthermore, -user and -password parameters are Oracle DATABASE un/pw, not Apex (workspace) un/pw.
I guess that's it, more or less ... if you want, I can share my own backup script which adds date information to exported file (so that new backup doesn't overwrite the previous one), ZIPs them all into a single ZIP file and mails the log file (all that on MS Windows).
[Updated on: Thu, 18 August 2016 02:47] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #654996 is a reply to message #654970] |
Thu, 18 August 2016 07:09 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Littlefoot wrote on Wed, 17 August 2016 15:58
Listener.log contents that covers time period specified in the previous query results (IP address different from 127.0.0.1 is masked):
Wed Aug 17 21:00:54 2016
17-AUG-2016 21:00:54 * (CONNECT_DATA=(SID=ora11)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=14967)) * establish * ora11 * 0
I don't know how to interpret it (i.e. I don't see what Ed mentioned, "entry in the listener log that corresponds (within a couple of seconds) to the time the user got locked. The listener log should show you what machine and OS user is doing the deed."). Everything seems to be localhost (database server) ... Does someone of you see something useful here?
As of the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME: That might save me from altering the user every morning; I could set PASSWORD_LOCK_TIME to, say, 1/24 (one hour) which should be OK. Thank you for the suggestion!
Yes, those connections that have HOST=127.0.0.1 (LOCALHOST) are originating from the database server. Nothing particularly unusual about that. There's nothing that says a client process can't be on the same machine as the server process. As Michael said, the "establish" means the request was to "establish a connection", the "ora11" indicates the database to which the connection is to be made, and the final "0" indicates success. But see my response to Michael. That "success" is only that the listener was able to do it's job in establishing a connection. The rejection of the credentials comes from the database after the listener has done its job. But now you know the culprit is a client process running on the database server. So you would look at the OS scheduler on the database server.
[Updated on: Thu, 18 August 2016 07:10] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #655003 is a reply to message #654995] |
Thu, 18 August 2016 08:08 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
EdStevens wrote on Thu, 18 August 2016 14:00
...
MIchael - When the listener reports "success", that simply means the listener was able to spawn the connection. The check of password (and potential rejection) comes AFTER that connection is made, as a direct conversation between the db and the client. Thus, the listener will report success even if the db itself ends up rejecting the connection. Test it for yourself. 
Gee! You are right, only ORA-12... connection errors are recorded, why I ever had this in mind?
[Updated on: Thu, 18 August 2016 08:08] Report message to a moderator |
|
|
|
| Re: Who / what keeps locking one of database users? [message #655005 is a reply to message #655000] |
Thu, 18 August 2016 14:09 |
|
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Littlefoot wrote on Thu, 18 August 2016 05:58Thank you, Ed!
As I said - there's no real DBA here who would take care about these things. First there was 10g (with its own listener). After 11g was installed, OUI (I suppose so) created a new listener by default. As everything works well, nobody complained about two listeners so we have them.
I trust every word you say, however - I have no idea how to actually run a single listener from the OHOME of the highest version installed. I'm far too dangerous to do ANYTHING about it; thinking that I'm fixing things, I'd probably crash the server and that would be a serious problem. Two listeners? No problem at all!
I see you are using Windows, as I am. I have had the same situation where I have installed one version, then later a newer version in another home. In another case, I messed up a first installation, leaving pieces behind, then did a second installation correctly. The result, in both cases, was two listeners.
In Windows, on the server, go to your control panel -> System and Security -> Administrative Tools -> Services. The services are listed alphabetically under the name column, so scroll down until you see a bunch of things starting with "Oracle". You will see for example, "OracleOraDB12Home1TNSListener". As you can see the version and home and that it is the listener are clearly indicated. Keep the one for the highest version. I have mine set to automatic, so that it automatically starts every time I start my computer. I am kind of like you in that I hesitate to do anything permanent, especially on a production database that is running O.K., so I don't know that I would delete the other listener, especially if it is running. However, if it is not running, just set it to manual, so that it does not run unless you manually start it, so that Oracle will automatically use the other listener. If it is running, then you will want to find a time, when you can stop the database, stop the listener, set it to manual, re-start the database, and make sure that it is using the newer listener. If there is anything wrong, then you can always re-start the older listener.
If the database with the newer version attempts to use the listener from the older version, then you may find that there are some things that don't run right. It is kind of like accessing your database via an outdated version of SQL*Plus.
|
|
|
|
|
|
|
|
| Re: Who / what keeps locking one of database users? [message #655022 is a reply to message #655017] |
Fri, 19 August 2016 01:52 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I'm not sure I understand what you are saying ... do you mean "connection string" assqlplus scott/tiger@ora11
# Oracle 10g
ORA10=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=dbs1)
(PORT=1523)
)
(CONNECT_DATA=
(SERVICE_NAME=ora10)
)
)
# Oracle 11g
ORA11 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=dbs1)
(PORT=1521)
)
(CONNECT_DATA=
(SID=ora11)
)
)
Does it (your sentence) mean that I should modify ORA10 PORT value to 1521 (if I turn off 10g's listener)? If so, I'm not sure I want to do that because everyone (who uses it) would have to make that modification in his TNSNAMES.ORA.
You know, "don't fix it if it ain't broken" ...
[EDIT: typo]
[Updated on: Fri, 19 August 2016 03:26] Report message to a moderator |
|
|
|
| Re: Who / what keeps locking one of database users? [message #655023 is a reply to message #655022] |
Fri, 19 August 2016 01:58 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Unlike Ed (whose opinion on most matters I greatly respect) I have never seen any problem with running multiple listeners. As you have found, it is working perfectly for you. However, as a learning exercise, this is what you could do:
Shutdown the 10.x listener.
Edit the 11.x listener.ora file to add 1523 as a second listening address
Adjust the 10.x instance parameter local_listener='dbs1:1523'
Your users will not be aware of any difference.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jan 02 16:09:29 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
