SAP workprocesses can not connect to the database any more.
SAP Note 1519872
Symptom
This note describes the configuration of an SAP installation with the SAP-pecific database user profile SAPUPROF.
Symptom:
SAP system does not start any more.
SAP workprocesses can not connect to the database any more.
SAP trace files contain errors similar to the following:
TRACE-INFO: 101: [ dbsloci.,00000] *** ERROR => CONNECT failed with sql error ’28001′
TRACE-INFO: 102: [ dev trc,00000] set_ocica() -> SQL error code 28001
TRACE-INFO: 103: [ dev trc,00000] >oci_get_errmsg (con_hdl=0,rc=28001)
TRACE-INFO: 104: [ dev trc,00000] OCIErrorGet -> SQL error code: 28001
TRACE-INFO: 105: [ dev trc,00000] ORA-28001: the password has expired
Other symptoms:
You get ORA-28000 or ORA-28001 or ORA-28002 errors when you try to connect to the database (i.e. as database user SYSTEM).
Reason:
- Oracle Database Release 11.2.0.X.
- Database user profile SAPUPROF is not configured. Check this with the following query (for user SAPSR3):
SQL> select profile from dba_users where username = ‘SAPSR3′;
- With 11.2, passwords of database users must be changed every 180 days for security reasons.
For full solution, Please go through this note 1519872.
Checking failed login attempts & change password of SAPSR3
In my previous post you find that how to unlock SAPSR3. if in some cases this user is locking again & again. it’s better to change the password of SAPSR3 & also change the default limit of failed login attempts.
Procedure to check.
1. go to CMD – “sqlplus / as sysdba”
2. execute statement to check limit of failed login attempts.
SELECT LIMIT FROM DBA_PROFILES
WHERE
PROFILE = ‘DEFAULT’ AND
RESOURCE_NAME = ‘FAILED_LOGIN_ATTEMPTS’;
default limit = 10.
Changing default limit = 10 to unlimited.
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
now it’s time to change the password of SAPSR3.
CMD>brconnect -u / -c -l E -f chpass -o SAPSR3 -password (whatever_you_want)
SAPSR3 user locked.
Recently we find an issue that while starting SAP via SAP MMC, work process stop in Ended status. We found in developer trace that it’s a database connectivity issue. In some cases connectivity from SAP to Database does not happen due to SAPSR3 user locked.
Steps to find the issue in Windows Environment.
1. Go to CMD – R3trans -d (it ended with return code 0012)
2. Read the trans.log & search if there any user lock issue found.
if YES then go to below procedure to resolved.
1. Stop the SAP process in SAP MMC.
1. go to sqlplus via “CMD – sqlplus / as sysdba”
2. execute statement to check which user(SAPSR3) is locked.
select username, account_status from dba_users;
find the list of dba users with their status. if SAPSR3 user is locked then execute this below statement to unlock.
alter user SAPSR3 account unlock;
again execute to check whether it’s successful or not.
select username, account_status from dba_users;
if successful log out from SQL & execute “R3trans -d“. it finish with RC=0000.
START SAP & it will start.
