Shutdown is taking forever, can only do a SHUTDOWN ABORT

We recently performed the cloning of the staging Database from a production database, but unfortunately we faced an issue.

Once the restore is complete, we were not able to stop the database to rename it and perform the post checks. 

When initiating the shutdown command as below and in another session using the tail-20f command to view the output of the alert log, the results found that the database has been waiting for a long time did not shut down.

oraclehost@ourdb1$ conn / as Sysdba;

SQL> alter system switch logfile;

SQL> alter system checkpoint;

SQL> shutdown immediate;    //* DB stopped responding *//
 

-- Excerpt from Log

Thu Jul 15 22:15:19 2021

Shutting down instance (immediate)

Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process CJQ0
Stopping background process MMON
License high water mark = 4

 

While checking the process on OS level, the MMON process was still alive and as per alert log the database is waiting to stop the MMON process.

oraclehost@ourdb1$ ps -ef | grep mmon
grid      1928      1  0  2021  ?       01:24:38 asm_mmon_+ASM
oracle    48216     1  3  22:26 ?       00:00:00 ora_mmon_ourdb1

As a workaround, we tried to abruptly stop the database and start it up and perform a clean shutdown but no luck.

 

The cause of this error occurs:

As a conclusion based on the above finding and we know that..

• The MMON background process is responsible for the collection of statistics for the Automatic Workload Repository (AWR)

• The MMON process takes snapshots at regular intervals (default 60 minutes) and inserts that data into the AWR tables.

• The similar MMNL process is also a new process of AWR, the main role is to refresh the AWR data from the memory to the table.

• The session collecting snapshot is continually waiting for “enq: WF – contention”. So, the waiting was for this wait to flush the statistics to AWR.

 

Workaround :

With a function / procedure invoked from DBMS_WORKLOAD_REPOSITORY package, disabled the AWR snaps or purge the snaps for each DBID manually before shutdown.

SQL> select DBID, SNAP_INTERVAL, RETENTION from DBA_HIST_WR_CONTROL;

 DBID      SNAP_INTERVAL          RETENTION           
---------- -------------------- --------------------
4202305794 +00000 01:00:00.0    +00008 00:00:00.0   
1986475012 +00000 02:00:00.0    +00014 00:00:00.0   

 

SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 0, interval => 0);

PL/SQL procedure successfully completed. 


SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval => 0, dbid => 4202305794);

PL/SQL procedure successfully completed.

  

The memory monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

Another process is MMNL, is an acronym for Memory Monitor Light (MMNL) recorded in parts of the document for Manageability Monitor Light.The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

 

As an another alternative, Periodically (e,g. once a day) run exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO will clean out redundant col_usage$ entries, and when you come to shutdown the database you should not have a huge number of entries left to clean up.

For similar but in other scenarios , the Oracle's official documentation, explained below

The database is waiting for pmon to clean up processes, but pmon process is unable to clean them. The client connections to the server is causing the shutdown immediate or normal to hang. Killing them allows Pmon to clean up and release. The associated Oracle processes and resources.

What resources is we talking about here ?

1) Any non committed transactions must is rolled back

2) Any temporary space (sort segments/lobs/session temporary tables) must be freed

3) The session itself and any associated memory consumed by the session.

4) Internal locks/enqueues must is cleaned up

Often Oracle (Smon or Pmon depending on whether Shared Server is used) would wait for the OS to terminate the process associated with the session. If the OS never returns, or fails to terminate them, then the instance shutdown would hang with this message (Shutdown Waiting for Active Calls to complete)

 

MOS REFERENCES for related issues

NOTE:416658.1 - Shutdown Immediate Hangs / Active Processes Prevent Shutdown
NOTE:1039389.6 - Alert Log: Shutdown Waiting for Active Calls to Complete
NOTE:1183213.1 - Shutdown Normal or Immediate Hang Waiting for MMON process
NOTE:1197314.1 - Shutdown Normal Hung On ORA_J00# Process
NOTE:1076161.6 - Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery
NOTE:332177.1 - Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort

Found this post interesting? Subscribe us  😊😉