Skip to content

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:

Sun Nov 09 22:55:03 2014
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 29338 1 0 20:12 ? 00:00:00 oracle-MGMTDB (LOCAL=NO)
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_HOME=<GRID_HOME>
$ 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.

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: