ORA-00020 reported in -MGMTDB alert log
SYMPTOMS
-MGMTDB is not releasing the database connection, causing it to run out of processes. -MGMTDB is the management database used for storing CHM (Cluster Health Monitor) data in 12c.
alert_-MGMTDB.log shows:
CJQ0 started with pid=33, OS id=36397
Completed: ALTER DATABASE OPEN /* db agent *//* {1:34217:2109} */
…
Sun Nov 09 17:03:59 2014
ORA-00020: maximum number of processes (300) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process m000 submission failed with error = 20
…
ps -ef | grep MGMTDB shows many processes:
oracle 29342 1 0 20:12 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29542 1 0 20:13 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29546 1 0 20:13 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29757 1 0 20:14 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29761 1 0 20:14 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29978 1 0 20:15 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 29984 1 0 20:15 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30272 1 0 20:16 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30276 1 0 20:16 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30494 1 0 20:17 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30498 1 0 20:17 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
oracle 30691 1 0 20:18 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
…
Shutdown and restart the -MGMTDB does not help, it soon reports ORA-20 again.
CAUSE
In MGMTDB, the user CHM’s password is expired.
select username, account_status, profile from dba_users where username=’CHM’;
USERNAME ACCOUNT_STATUS PROFILE
——————– ——————- ————
CHM EXPIRED(GRACE) DEFAULT
select * from dba_profiles where profile=’DEFAULT’ and resource_name=’PASSWORD_LIFE_TIME’;
PROFILE RESOURCE_NAME RESOURCE LIMIT COM
————– ———————– ————- ——— —–
DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 NO
The user CHM’s password is expired after 180 day from DB creation. This causes the user account is locked and connection can not be made through.
SOLUTION
Please use the following procedure to reset the CHM user password to its original value.
as grid user,
$ export ORACLE_SID=-MGMTDB
$ sqlplus / as sysdba
SQL> select name, password from user$ where name=’CHM’;
SQL> alter user CHM identified by values ‘<the password returned from the above query>’;
SQL> select username, account_status from dba_users where username=’CHM’;
It should show account_status as OPEN now and ORA-20 error should be stopped.
WikiDBA 