Solution to Oracle user password expiration and user lock

It is determined that ldquo; PASSWORD_LIFE_TIME180 days rdquo; is set in the default profile in Oracle11g by default.

It is determined that ldquo; PASSWORD_LIFE_TIME = 180 days rdquo; is set in the default profile in Oracle11g by default.

[Cause/trigger factor]

This is because "PASSWORD_LIFE_TIME = 180 days" is set in the default profile in Oracle11g by default.

[Impact and risk]

Impact

After the password expires, the business process has an exception connecting to the database, affecting service usage.

Problem occurrence frequency

After the database password expires, the connection will fail once the business process restarts.

[Solution]

Follow these steps:

1. Check the user's proifle, which is generally default:

SQL> SELECT username, PROFILE FROM dba_users;

2. view the password validity period settings of the specified profile (such as default:

SQL> SELECT * FROM dba_profiles s WHERE s. profile = 'default' AND resource_name = 'password _ LIFE_TIME ';

3. Change the password validity period from the default 180 days to "unlimited ":

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

After modification, the database does not need to be restarted and takes effect immediately.

4. After modification, the account has not been prompted ORA-28002 warning will not again encounter the same prompt;

The account that has been prompted must change the password again, for example:

$ Sqlplus/as sysdba

SQL> alter user smsc identified <原来的密码> ---- New password is not required

The Oracle11g startup parameter resource_limit takes effect regardless of whether it is set to false or true. Therefore, you must modify the password in the preceding method. The preceding account name must be changed based on the actual account name.

--------------------------------------------------------------------------------

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

Ii. [Problem description]

Recently H Bureau point feedback, using sqlplus login Short Message oracle Database prompt "ORA-28000: account is locked ".

[Cause/trigger factor]

It is confirmed that "FAILED_LOGIN_ATTEMPTS = 10 times" is set in the default profile in oracle11g by default. This problem occurs when the number of incorrect passwords reaches the set value.

[Impact and risk]

Impact

After the account is locked, the business process has an exception connecting to the database, affecting service usage.

Problem occurrence frequency

After the database account is locked, the connection fails once the business process is restarted.

[Solution]

Follow these steps:

1. Check the user's proifle, which is generally default:

SQL> SELECT username, PROFILE FROM dba_users;

2. view the password validity period settings of the specified profile (such as default:

SQL> SELECT * FROM dba_profiles s WHERE s. profile = 'default' AND resource_name = 'failed _ LOGIN_ATTEMPTS ';

3. Change the number of failed logon attempts from the default 10 to "unlimited ":

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

4. After the modification, the user who has not been prompted the ORA-28000 warning will not encounter the same prompt again;

The locked account must be unlocked, for example:

$ Sqlplus/as sysdba

SQL> alter user smsc identified by oracle account unlock;

5. After modification, the default profile should be as follows:

SQL> select * from dba_profiles WHERE dba_profiles.profile = 'default ';

PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT

-------------------------------------------------------------------------------------------------

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED

DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED

After modification, the database does not need to be restarted and takes effect immediately.

If the ORA-28000 the account is locked. error occurs

Alter user Username account unlock;