While developing Icinga, I also need to test the Oracle implementation in IDOUtils which then leads me to my fancy XE install (which is stripped down to low-mem and is only started on demand)
So, I wasn’t really expecting that I should change my password in 7 days.
sol /etc/icinga # rlwrap sqlplus icinga@XE SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:34:42 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: ERROR: ORA-28002: the password will expire within 7 days Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL>
Time for some sysdba magic
sol /etc/icinga # rlwrap sqlplus "sys@XE as sysdba" SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:44:46 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL
Get to know which profile the icinga user is using
SQL> SELECT profile FROM dba_users WHERE username='ICINGA'; PROFILE -------------------------------------------------------------------------------- DEFAULT
Now check the password lifetime of the default profile.
SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
Alter it to unlimited (note: it’s a local dev oracle xe install!).
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
Though, this doesn’t fix the icinga user being gracefully expired already.
SQL> SELECT account_status FROM dba_users WHERE username='ICINGA'; ACCOUNT_STATUS -------------------------------------------------------------------------------- EXPIRED(GRACE)
But updating this manually to the exact same password will resolve the issue.
SQL> ALTER USER ICINGA IDENTIFIED BY XXXXX; User altered. SQL> SELECT account_status FROM dba_users WHERE username='ICINGA'; ACCOUNT_STATUS -------------------------------------------------------------------------------- OPEN
Now quit the sysdba session and re-login as icinga user.
sol /etc/icinga # rlwrap sqlplus icinga@XE SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar 11 22:56:38 2013 Copyright (c) 1982, 2011, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL>
Voilà – Oracle beaten again 🙂
(Oh, and make sure to use rlwrap all the time. Native sqlplus just sucks on tab completion and so on).
thanks a lot! 🙂
Thanks for this great post, it helped me alot!
You sir, are a veritable genius. With most of my Oracle databases dragging ass in 9 or 10, I had not encountered this ‘undocumented feature’ of 11g (default ‘PASSWORD_LIFE_TIME is 180). With my DBA just having left on vacation for two weeks, your post saved my ass, thanks!
Glad that this old post still helps out 🙂
Thanks a lot, this really helped me!!!
if its not a default profile then what?
Oracle 11g sets a default time limit on passwords. Here is what to key in to SQL*Plus as Oracle user “system”:
• To prevent password expiration in the future (if you are using the default profile, which most people do):
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;
• To allow unlimited failed login attempts:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
• To unlock Oracle user whose password has already expired:
[Key in the commands above for preventing password expiration, then do the following.]
ALTER USER IDENTIFIED BY ;
ALTER USER ACCOUNT UNLOCK;
[The password reset is needed even if the password has not changed.]
• To check password expiry status:
SELECT USERNAME,ACCOUNT_STATUS,EXPIRY_DATE FROM DBA_USERS ORDER BY USERNAME ASC;