CREATE PROFILE

CREATE PROFILE developer LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 0
PASSWORD_REUSE_MAX 0
FAILED_LOGIN_ATTEMPTS 4
PASSWORD_LOCK_TIME 2
CPU_PER_CALL 3000
PRIVATE_SGA 500K
LOGICAL_READS_PER_CALL 1000;

ORA-28000: the account is locked

ORA-28000: the account is locked

Error Description:
-------------------------
Whenever you try connect to connect a user it failed with error message.
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

And immediately the current user is disconnected from oracle.

Cause of The Problem:
-------------------------------
The user account is locked. This may be explicitly issued by dba user Like,
SQL> ALTER USER INDIA ACCOUNT UNLOCK; or it may be locked internally based on the profile resource limit. In order to know more about profile and resource limit have a look at,
User Resource Limit
Profile in Oracle

For example if I set FAILED_LOGON_ATTEMPTS of the assigned profile to a user set as 10 (which is default in 10.2)then after failed logon attempt 10 times the user account will be automatically locked.

Solution of The problem:
-------------------------------
Try to unlock the user account as a dba user by ALTER USER username ACCOUNT UNLOCK;
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

SQL> CONN ARJU/A
Connected.

SQL> ALTER USER INDIA ACCOUNT UNLOCK;
User altered.

SQL> CONN INDIA/T
Connected.

There may be the reason that user is locked based on imposed resource limit. For example from application wrong password is set and for that the account is locked. You then have to know the assigned profile of the user by,
SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME=
2 'INDIA';

PROFILE
----------
DEFAULT

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED

16 rows selected.


Here FAILED_LOGIN_ATTEMPTS resource is set to 10. You can make it unlimited by

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

And then unlock the account.
SQL> ALTER USER INDIA ACCOUNT UNLOCK;

Why crossover cables are not supported in RAC

Why crossover cables are not supported in RAC


Many Oracle shops in this world use crossover cables, literally a network cable, between nodes for use as the interconnect between two rac nodes. Does this work, yep, you bet. Is it supported, no. Why? well it all has to do with how a node reacts when its sister fails in a two node cluster.

Each node in the cluster constantly checks on the other nodes in the cluster through both the network (interconnect) and storage (voting disks), if one or both are lost, the cluster node is instructed to commit suicide and reboot itself in hopes of rejoining the cluster healthy and happy.

If a crossover cable is used, and one of the nodes drops the remaining node will have to wait for the tcp timout, generally 60-300 seconds, before it realized that the lost node is gone. At which point, the cluster will remove the lost node from the cluster. What can happen during that time is two fold, the surviving node can lock up, litterally freeze during the wait for the timeout and/or the cluster can become very confused if the dead node restarts and attempts to join the cluster at a point when the cluster still thinks it is there. Strange things have been known to happen, many errors thrown and at times will cause both nodes to evict and restart.

Having a switch between the nodes allows a signal to be sent immediately if a node quits responding, at which time the surviving node will check for 60 seconds then evict the failing node, allowing it to rejoin (upon reboot) a clean cluster without any problems.

In short, crossover cables are fine in an emergency or development, any situation where failover is not critical, but for production, spend the money on a good switch, two in fact if you can bond your nics (that’s for another post), for the best senario to survive a failover with as few issues as possible.