When oracle 11.2.0.4 is fully archived, the user limits the connection, stops generating awr snapshots (instances), and 11.2.0.4awr

When oracle 11.2.0.4 is fully archived, the user limits the connection, stops generating awr snapshots (instances), and 11.2.0.4awr

AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned (Doc ID 2023652.1) To BottomTo Bottom

Applies:

Oracle Database-Enterprise Edition-Version 11.2.0.4 to 11.2.0.4 [Release 11.2]

Information in this document applies to any platform.

SYMPTOMS

Due to other reasons, the MMON process died and subsequent AWR reports are therefore not getting generated.

The MMON process shoshould automatically be respawned but it is not.

CAUSE

MMON/MMNL not being respawned or recreated is due:

Unpublished bug: Bug 19565533-MMON/MMNL CAN NOT BE AUTOMATICALLY RESTARTED AFTER KILLED

The issue only occurs on 11.2.0.4 and is a regression of the fix for bugs 14213856

SOLUTION

To solve the issue:

Apply interim patch 19565533 for 11.2.0.4, if available for your platform.

To check for conflicting patches, please use the MOS Patch Planner Tool

Please refer

Note 1317012.1-How To Use MOS Patch Planner To Check And Request The Conflict Patches?

If no patch exists for your version, please contact Oracle Support for a backport request.

-OR-

As a workaround, you can respawn the MMON process by next methods:

Restart the database instance

-OR-

Set the instance to "restricted session" mode and then bring it back to normal mode using following commands as SYSDBA:

Alter system enable restricted session;

Alter system disable restricted session;

If the user is not a dba, logon is restricted. The dba account can

[Oracle @ host01 ~] $ Sqlplus clspuser/clsp

SQL * Plus: Release 11.2.0.3.0 Production on Fri Jan 19 23:29:09 2018

Copyright (c) 1982,201 1, Oracle. All rights reserved.

ERROR:

ORA-01035: ORACLE only available to users with restricted session privilege

Enter user-name: clspuser

[Oracle @ host01 ~] $

SQL> create user eviswang identified by evis;

User created.

SQL> gant dba to eviswang;

SP2-0734: unknown command beginning "gant dba t..."-rest of line ignored.

SQL> grant dba to eviswang;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

[Oracle @ host01 ~] $ Sqlplus eviswang

SQL * Plus: Release 11.2.0.3.0 Production on Fri Jan 19 23:30:31 2018

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Enter password:

Connected:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

Alter system disable restricted session; can be logged on later

[Oracle @ host01 ~] $ Sqlplus clspuser/clsp

SQL * Plus: Release 11.2.0.3.0 Production on Fri Jan 19 23:31:15 2018

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL>

Alter tracking is as follows:

Fri Jan 19 23:31:41 2018

Stopping background process MMNL

Stopping background process MMON

Starting background process MMON

Fri Jan 19 23:31:43 2018

MMON started with pid = 216, OS id = 54742

Starting background process MMNL

Fri Jan 19 23:31:43 2018

MMNL started with pid = 219, OS id = 54744

Alter system enable restricted session;

Fri Jan 19 23:32:02 2018

Alter system disable restricted session;

[Oracle @ adminm trace] $