Oracle Database Discussions

Hi all,

one user is getting locked sporadically, without any reason that I can see in the Alertlog file.

SQL> host date
Sat Mar 7 07:32:25 WAT 2009

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST LOCKED(TIMED)

SQL> alter user INTEGRATION_TEST account unlock;

User altered.
SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST OPEN
.........................................................
SQL> host date
Sat Mar 7 07:41:20 WAT 2009

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST LOCKED(TIMED)

SQL> alter user INTEGRATION_TEST account unlock;

User altered.

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST OPEN
...........................................................
SQL> host date
Sat Mar 7 07:56:02 WAT 2009

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST LOCKED(TIMED)

SQL> alter user INTEGRATION_TEST account unlock

User altered.

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST OPEN
..........................................................
SQL> host date
Sat Mar 7 08:08:06 WAT 2009

SQL> /

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST LOCKED(TIMED)

SQL> alter user INTEGRATION_TEST account unlock;

User altered.

SQL> select username,account_Status from dba_users where username='INTEGRATION_TEST';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST OPEN
...............................................................

SQL> host date
Sat Mar 7 08:19:56 WAT 2009

SQL> /

USERNAME ACCOUNT_STATUS
------------------------------ -----------------
INTEGRATION_TEST LOCKED(TIMED)

SQL> alter user INTEGRATION_TEST account unlock;

User altered.
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
INTEGRATION_TEST OPEN
..................................................

5 days before I saw an error WARNING INBOUND CONNECTION TIMEOUT (ORA-3136)
in the Alert log file then I followed the metalink doc *465043.1* it suggest to increase the sqlnet.inbound_connect_timeout to a greater value than 60 sec, I made that 120 sec and still the user is getting locked, but since 4 days there is no Inbound connect timeout error being raised in the Alert log file.
This workaround didn't work in stopping the account from being locking. The metalink doc suggests checking the local connections and the network delay, but the local connections are fine and there is no network delay.

$ tnsping central

TNS Ping Utility for HPUX: Version 10.2.0.4.0 - Production on 04-MAR-2009 16:25:54

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:

/u01/app/oracle/db/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = hrdb03)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAM
E = central) (INSTANCE_NAME = central)) (HS = OK))
OK (0 msec)

PLEASE help!!!