“oamDS”: ORA-28001: The Password Has Expired
Recently i come across an issue where R12 E-Business Users which was integrated with OAM 11g for SSO were not able to login.They were getting error message as shown below:
From first look of redirection URL in browser, it appears that it was crossing the OAM authentication process and it was the application which is throwing the error.
To verify, we check application.log and Apache. error log under $LOG_HOME/ora/10.1.3 but there were no error messages written.
We verified if AppsLocal Login was working and the login was working.
Next step was to check in E-Business Access gate log file, default location- /tmp/fndauth0.log on server where you deploy the E-Business Access gate. The log file shows below error
02-Jul-2012 11:04:02 oracle.apps.fnd.ext.common.server.FndSsoFilter doFilter
WARNING: FndSsoFilter::doFilter()– error encountered
Throwable occurred: oracle.apps.fnd.ext.common.server.FndSsoException: Unknown exception. Please contact your System Administrator.
Caused by: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool “VISR12” to allocate to applications, please increase the size of the pool and retry..
at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:252)
The error complains about no resource available in connection pool of Weblogic which means the problem was with JDBC datasources.
Next, check OAM Server output log file ,default location $DOMAIN_HOME/servers/oam_server1/logs/oam_server1.out . Log file show below error:
<02-Jul-2012 11:05:34 o’clock BST> <Warning> <oracle.oam.foundation.access> <OAMSSA-04006> <Cannot get connection from data source.
weblogic.jdbc.extensions.PoolDisabledSQLException: weblogic.common.resourcepool.ResourceDisabledException: Pool oamDS is Suspended, cannot allocate resources to applications..
at weblogic.jdbc.extensions.PoolDisabledSQLException.<init>(PoolDisabledSQLException.java:11)
Which reflect problem is with OAM datasource “OAMDS”
At this stage check DB which stores OAM schema is up and reachable from OAM node and check for errors in DB alert log file under $USER_DUMP_DEST location in database.
On restarting the datasource the oam server log file show below error:
Received exception while creating connection for pool “oamDS”: ORA-28001: the password has expired
02-Jul-2012 11:21:31 o’clock BST> <Warning> <oracle.oam.foundation.access> <OAMSSA-04006> <Cannot get connection from data source.
weblogic.jdbc.extensions.ConnectionDeadSQLException: weblogic.common.resourcepool.ResourceDeadException: 0:weblogic.common.ResourceException: Could not create pool connection. The DBMS driver exception was: ORA-28001: the password has expired
at weblogic.jdbc.common.internal.JDBCUtil.wrapAndThrowResourceException(JDBCUtil.java:250)
Login to DB which hold OAM schema and query for OAM schema as shown below:-
SQL> select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE from dba_users where USERNAME like ‘%OAM%’;
USERNAME ACCOUNT_STATUS EXPIRY_DATE
——————— ——————– ———
OAM_IAU_VIEWER OPEN
OAM_IAU OPEN
OAM_IAU_APPEND OPEN
OAM_OAM EXPIRED 10-JUN-12
Reason:- With 11g database , Password_life_time in DEFAULT profile is set to 180days and PASSWORD_GRACE_TIME is set to 7 days.
Use below query to see your default profile:-
SELECT * FROM dba_profiles WHERE profile = ‘DEFAULT’ AND resource_type = ‘PASSWORD’;
PASSWORD_GRACE_TIME parameter sets the number of days that a user has to change his or her password before it expires.
PASSWORD_LIFE_TIME parameter sets the number of days the user can use his or her current password.
Alter these parameter value to Unlimited in DEFAULT profile as:-
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;
Enable the user by resetting their passwords as
alter user <prefix>_oam identified by <password>;