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 Go to next message
Littlefoot
Messages: 21268
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 #654853 is a reply to message #654849] Tue, 16 August 2016 03:42 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
Could it be Scheduler job, rather than a dbms_job? Take a look at dba_scheduler_jobs.
Re: Who / what keeps locking one of database users? [message #654860 is a reply to message #654853] Tue, 16 August 2016 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also could it be an OS scheduled task? Maybe using an external tool like Control-M?

Re: Who / what keeps locking one of database users? [message #654861 is a reply to message #654849] Tue, 16 August 2016 04:35 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
And another possibility: has OEM Database Control been configured for the database? It too has a job scheduler which can store credentials.
Re: Who / what keeps locking one of database users? [message #654865 is a reply to message #654860] Tue, 16 August 2016 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, both of you!

I don't see anything suspicious in DBA_SCHEDULER_JOBS. That user has created one job, but it runs
FREQ=WEEKLY;BYDAY=TUE;BYHOUR=15;BYMINUTE=15;BYSECOND=0
There are no jobs that run at 21:mi:ss o'clock (or anytime close to it) and, besides that, are owned by SYS or ORACLE_OCM or similar users and don't look like affecting that user.

As of the OS scheduled task: the last time we spoke (he's currently on vacation) he didn't mention it. I'll try to contact him and let you know the outcome.
Re: Who / what keeps locking one of database users? [message #654870 is a reply to message #654865] Tue, 16 August 2016 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
John

Has OEM Database Control been configured for the database?
I'm not sure. As I said, it is (more or less) a default database setup (Next - Next on OUI).

There are 2 databases on that server: Oracle 10g and 11g. I can access OEM for 10g on http://server:1158/em. 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").
Re: Who / what keeps locking one of database users? [message #654912 is a reply to message #654870] Tue, 16 August 2016 17:47 Go to previous messageGo to next message
cookiemonster
Messages: 12961
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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 #654915 is a reply to message #654849] Tue, 16 August 2016 19:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
What does the following show?

select dba_profiles.*
from   dba_users, dba_profiles
where  dba_users.username = 'MB_USER'
and    dba_users.profile = dba_profiles.profile;

[Updated on: Tue, 16 August 2016 20:01]

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 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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>
I don't know whether there are database links in other databases that connect to MB_USER. I did check 'ora10' ("HOST" in the previous output) as there are two databases on that server, 10g & 11g - the same query ran in 10g returned nothing.

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 #654922 is a reply to message #654870] Wed, 17 August 2016 01:20 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
To check OEM, set your Oracle Ho,e and path, and try
emctl stop dbcontrol
or stop the relevant Windows service. If you stop it, then it definitely won't be running any of its jobs (if it has any).
Re: Who / what keeps locking one of database users? [message #654928 is a reply to message #654870] Wed, 17 August 2016 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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 #654929 is a reply to message #654928] Wed, 17 August 2016 02:34 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
I've just created a dbconsole for a 11.2.0.4 DB, so now I can see what I meant Smile

You went to the Jobs link on the Server tab: that is the Scheduler. I meant the Jobs link in the Related Links area at the bottom of the database home page. That takes you to a Job Activity page where you can create jobs that will be run by the DB Console agent. If there are any jobs defined, they will have stored credentials and for several of the job types (SQL Script is the most obvious) those credentials will be a database login.
Re: Who / what keeps locking one of database users? [message #654934 is a reply to message #654929] Wed, 17 August 2016 03:50 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see; well, as I thought, No Jobs Found there (yes, I checked whether I'm displaying "All" or is there any criteria that might be hiding some info).
Re: Who / what keeps locking one of database users? [message #654936 is a reply to message #654934] Wed, 17 August 2016 04:12 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
Well, the only remaining possibility I can see is an OS scheduled job. I list Windows Scheduler jobs with
schtasks.exe /query
there are always zillions of them which rather disturbing. I don't know how to show jobs scheduled with the old at.exe mechanism.
Re: Who / what keeps locking one of database users? [message #654937 is a reply to message #654936] Wed, 17 August 2016 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't know how to show jobs scheduled with the old at.exe mechanism.
With just "at", default option is to list the tasks. Smile

Re: Who / what keeps locking one of database users? [message #654940 is a reply to message #654937] Wed, 17 August 2016 04:29 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lucky me, there are only ~20 jobs on the server and only 5 are currently active.

As I previously said, my colleague is on a vacation and won't be back until the end of August. His PC is powered on, but I don't know his password so I can't check his scheduled tasks right now.
Re: Who / what keeps locking one of database users? [message #654948 is a reply to message #654849] Wed, 17 August 2016 06:41 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Query DBA_USERS to see exactly when the account gets locked:

xelect username, 
      to_char(lock_date,'dd-mon-yyyy hh24:mi:ss)
from dba_users;

Then lock for an 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.
Re: Who / what keeps locking one of database users? [message #654953 is a reply to message #654948] Wed, 17 August 2016 07:28 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you, Ed. I'll do that tomorrow (or much later today) as the user is now unlocked (I'm doing it - unlocking - every morning because people, who use that Apex application, can't work otherwise). It gets locked ~21:00.
Re: Who / what keeps locking one of database users? [message #654966 is a reply to message #654921] Wed, 17 August 2016 14:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
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 #654968 is a reply to message #654966] Wed, 17 August 2016 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Removing FAILED_LOGIN_ATTEMPTS may be is a workaround waiting for the colleague to come back from his vacations.
Of course, this open the gate to a brute force attack on the password, so you need to watch the audit trail, hoping the failing job does not try to reconnect each time it fails as the audit trail you posted seems to indicate.

Re: Who / what keeps locking one of database users? [message #654970 is a reply to message #654968] Wed, 17 August 2016 15:58 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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 #654982 is a reply to message #654970] Thu, 18 August 2016 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The last figure of the CONNECT lines give the return code of the connection attempts.
All of them are 0 which means all these connections succeeded.
This means that your failing connections with 1017 were not remote (or did not come through this listener) but are local, so you just have a look to local scheduled jobs.

Re: Who / what keeps locking one of database users? [message #654984 is a reply to message #654982] Thu, 18 August 2016 01:20 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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?



/forum/fa/1964/0/ 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! /forum/fa/2115/0/
Re: Who / what keeps locking one of database users? [message #654985 is a reply to message #654984] Thu, 18 August 2016 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Good to know you manage it in the end. Note this in a procedure paper for the next password change. Wink

Re: Who / what keeps locking one of database users? [message #654986 is a reply to message #654984] Thu, 18 August 2016 02:03 Go to previous messageGo to next message
John Watson
Messages: 7188
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 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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 #654988 is a reply to message #654987] Thu, 18 August 2016 03:24 Go to previous messageGo to next message
John Watson
Messages: 7188
Registered: January 2010
Location: Global Village
Senior Member
Sussed!
Ta.
Re: Who / what keeps locking one of database users? [message #654995 is a reply to message #654982] Thu, 18 August 2016 07:00 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Michel Cadot wrote on Thu, 18 August 2016 00:39

The last figure of the CONNECT lines give the return code of the connection attempts.
All of them are 0 which means all these connections succeeded.
This means that your failing connections with 1017 were not remote (or did not come through this listener) but are local, so you just have a look to local scheduled jobs.

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. Smile
Re: Who / what keeps locking one of database users? [message #654996 is a reply to message #654970] Thu, 18 August 2016 07:09 Go to previous messageGo to next message
EdStevens
Messages: 863
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 #654997 is a reply to message #654984] Thu, 18 August 2016 07:14 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Littlefoot wrote on Thu, 18 August 2016 01:20
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?
Local - whatever was identified by the HOST= entry in the listener log. In this case it was 127.0.0.1 - so it is the db server itself.

The fact that you mention two listeners is disturbing.

============================================================================
Please stay after school and write the following sentence 100 times:

One single listener, using the default name of LISTENER and the default port of 1521, is quite capable of -- indeed, WAS DESIGNED TO -- service multiple databases of multiple versions running from multiple homes. Multiple listeners simply adds complications, and for no gain whatsoever.
============================================================================
If you are running multiple databases of multiple versions , you should run a single listener from the OHOME of the highest version installed.
Re: Who / what keeps locking one of database users? [message #655000 is a reply to message #654997] Thu, 18 August 2016 07:58 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank 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!
Re: Who / what keeps locking one of database users? [message #655003 is a reply to message #654995] Thu, 18 August 2016 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
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. Smile
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8779
Registered: November 2002
Location: California, USA
Senior Member
Littlefoot wrote on Thu, 18 August 2016 05:58
Thank 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 #655016 is a reply to message #655005] Fri, 19 August 2016 00:08 Go to previous messageGo to next message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Exactly! The same situation here (apart from the fact that both databases run well). Thank you, I understand what you are saying.

I'll do that during the weekend - shut down 10g, set its listener to "manual", restart 10g, check whether I can connect to it using 11g's listener. Although, it's a mystery to me - how is it going to work OK all by itself, automagically? Do I have to change some settings, somewhere?

I'll let you know the outcome.
Re: Who / what keeps locking one of database users? [message #655017 is a reply to message #655016] Fri, 19 August 2016 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Take care, the connection string contains (directly or not) the port of the listener.

Re: Who / what keeps locking one of database users? [message #655022 is a reply to message #655017] Fri, 19 August 2016 01:52 Go to previous messageGo to next message
Littlefoot
Messages: 21268
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" as
sqlplus scott/tiger@ora11
where "ora11" means the TNSNAMES.ORA entry which look like this (for our 10g and 11g databases):
# 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)
    )
  )
There are two ports here: 1521 and 1523.

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 Go to previous messageGo to next message
John Watson
Messages: 7188
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.
Re: Who / what keeps locking one of database users? [message #655025 is a reply to message #655022] Fri, 19 August 2016 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65256
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Littlefoot wrote on Fri, 19 August 2016 08:52
I'm not sure I understand what you are saying ... do you mean "connection string" as
sqlplus scott/tiger@ora11
"scott/tiger@ora11" is the connection string and you can see that "ora11" contains the listener port: 1523.

If you shutdown 10g listener and keep only 11g one then no one will listening on port 1523 and so no one will connect the client to the desired.
So you have to either add port 1523 in ADDRESS_LIST of your 11g listener or change the client tnsnames.ora to point to port 1521.


Re: Who / what keeps locking one of database users? [message #655028 is a reply to message #655025] Fri, 19 August 2016 02:49 Go to previous messageGo to previous message
Littlefoot
Messages: 21268
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@John:

I suppose I know how to shutdown 10g listener (in Windows Services or using lsnrctl stop).

This is excerpt of 11g LISTENER.ORA file:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1521))
    )
  )
If I understood you correctly,I should add this in there:
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbs1)(PORT = 1523))

Connected to 10g:
SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      LISTENER_ORA10
I should modify it to
alter system set local_listener = 'dbs1:1523';

Is that correct?



@Michel:
I know that "scott/tiger@ora11" is a connection string; I didn't know whether there's yet another "connection string" which is different from the previous one, related to listeners.

Michel

you can see that "ora11" contains the listener port: 1523.
Actually, it is vice versa: ora11 contains port 1521, while ora10 contains listener port 1523.

I suppose that it is easier (simpler) to modify 11g's LISTENER.ORA (i.e. adding 1523 to it, as described above) than to modify all TNSNAMES.ORA files, everywhere ...
Previous Topic: When Role Was Granted?
Next Topic: / as sysdba issue with AD membership
Goto Forum:
  


Current Time: Sun Dec 17 19:59:20 CST 2017

Total time taken to generate the page: 0.01261 seconds