Restarting MMON process

When I tried to do a simple change to a database parameter, I found an anormal or non-typical error, the ORA-00453 indicating that the MMON process was “dead”. Here is a small set of steps to restart it without problems for the database instance.

1. The antecedent, is that I tried to change the db_recovery_file_dest_size parameter to increase the size of the archived redo log files area, when the problem showed:

SQL> alter system set db_recovery_file_dest_size=400g scope=both;
alter system set db_recovery_file_dest_size=400g scope=both;
*
ERROR at line 1:
ORA-00453: backgroud process 'MMON' is dead

However, this error can appear when you want to change another parameter or do a special task that requires the MMON process.

2. When I do a little research, I observe that there is no MMON process for the database, just the one who belongs to ASM:

oracle|laboratory|$ ps -fe | grep mmon
grid 5160 1 0 2017 ? 01:24:38 asm_mmon_+ASM
oracle|laboratory|$

3. Then I reviewed the database log, and found that some days before because there was no space left in the SYSAUX tablespace, the process was stopped. Since this process is not so important for the database instance functionality, it was not affected:

 RECORD_ID TIME                 MESSAGE_TEXT
---------- -------------------- -------------------------------------------------------------------------------------------------------------
      6316 01/08/2018 07:00:29  MMON Flush encountered SYSAUX out of space error(1688).
      6317 01/08/2018 07:00:30  MMON (emergency) purge of WR snapshots (11472) and older
      7168 01/08/2018 10:36:04  Stopping background process MMON
      7169 01/08/2018 10:36:34  Background process MMON not dead after 30 seconds
      7170 01/08/2018 10:36:34  Killing background process MMON

4. Then I turned my review to My Oracle Support where I found the AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned (Doc ID 2023652.1) note, where there is a work around based on the following commands:

alter system enable restricted session;
alter system disable restricted session;

These just block the access to new connections to the database instance, unless the user has the RESTRICTED SESSION privilege or it is enabled. So I asked for a time’s window.

5. I executed the first command from the previous point and immediately, in the operating system appears the MMON process for the database:

SQL> alter system enable restricted session;

System altered.

oracle|laboratory|orcl$ ps -fe | grep mmon
grid      5160     1  0  2017 ?        01:24:38 asm_mmon_+ASM
oracle   28998     1  3 22:16 ?        00:00:00 ora_mmon_orcl

6. Then I disabled again the feature where only those users with RESTRICTED SESSION privilege can connect to the database instance:

SQL> alter system disable restricted session;

System altered.

7. Finally, without any additional movement, the pending parameter change can be made without any problem:

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +ARCH
db_recovery_file_dest_size           big integer 400G
recovery_parallelism                 integer     0

Please, leave a comment if this post was useful for you or if you have any doubt about the contents, I will OrAnswer you as soon as possible.