【MOS】Troubleshooting Shutdown Immediate Normal Hang Issues (Doc ID 1906014.1)

0    628    1

Tags:

👉 本文共约2452个字,系统预计阅读时间或需10分钟。

In this Document

APPLIES TO:

Oracle Database Cloud Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

PURPOSE

The goal of this document is diagnosing the different scenarios when shutdown normal/immediate hangs

DETAILS

A- SMON disabling TX Recovery

In the alert.log, you see only the following.. There are no ORA- errors or trace files generated.

Shutting down instance (immediate)
License high water mark = 12
Thu Dec 8 18:43:16 1994
alter database close normal
Thu Dec 8 18:43:17 1994
SMON: disabling tx recovery
SMON: disabling cache recovery
or
waiting for smon to disable tx recovery

During a SHUTDOWN IMMEDIATE and SHUTDOWN NORMAL, SMON is cleaning up extents which are no longer needed and marking them as freed.

Solution: Either wait for SMON to clean up the free extents in the database as it shuts down or perform a SHUTDOWN ABORT to shutdown the instance.
NOTE: A SHUTDOWN ABORT will not perform a clean shutdown.

For more information, please refer to Shutdown Normal or Shutdown Immediate Hangs. SMON disabling TX Recovery (1076161.6)

B- Active Processes Prevent Shutdown

The alert log lists messages like:

SHUTDOWN: waiting for active calls to complete.
ACTIVE PROCESSES PREVENT SHUTDOWN OPERATION

If the DB Control repository is running on the database target against which shutdown immediate was attempted then an incorrect order of events seems used.
You should stop DB Control first to get rid of all connections between DB Control and the repository database and then shutdown the database with 'shutdown immediate'.

Solution:

  1. Given OEM connections are active (SYSMAN and DBSNMP), de-activate these sessions, i.e. by stopping the agent/DBConsole

  2. Then shutdown immediate as normal
    - OR -

There may be processes still running and holding locks at the time a shutdown is issued.
Sometimes these are failed jobs or transactions, which are effectively 'zombies', which are not able to receive a signal from Oracle.

If this occurs, the only way to shutdown the database is by doing:
sql>
shutdown abort
startup restrict
shutdown normal

The startup does any necessary recovery and cleanup, so that a valid cold backup can be taken afterward.
If this issue occurs frequently, it would be a good practice to see if there are any active user processes running in v$session or v$process before shutting down the instance.

  1. You can kill the sessions preventing shutdown.

For more information, please refer to Shutdown Immediate Hangs / Active Processes Prevent Shutdown (Doc ID 416658.1)

C- Waiting for MMON process

The alert.log will show that the shutdown is waiting for MMON and its secondary processes to be terminated:

Thu Aug 05 13:28:48 2010
Background process MMNL not dead after 10 seconds
Killing background process MMNL
Stopping background process MMON
Thu Aug 05 13:29:20 2010
Background process MMON not dead after 30 seconds
Killing background process MMON
License high water mark = 1
Thu Aug 05 13:34:23 2010
Active process 12377 user 'oracle' program 'oracle@bsf14f (MMON)'
Active process 12377 user 'oracle' program 'oracle@bsf14f (MMON)'
Active process 22755 user 'oracle' program 'oracle@bsf14f (M000)'
Active process 22755 user 'oracle' program 'oracle@bsf14f (M000)'
SHUTDOWN: waiting for logins to complete.

This is caused by Bug 9132776 - AWR SNAPSHOT NOT GENERATED AFTER 11.2 UPGRADE

Solution: Please download and apply the one off Patch 9132776 if available for your platform. The fix for this bug is also included in patch set 11.2.0.2.

For more information, please refer to Shutdown Normal or Immediate Hang Waiting for MMON process ([Doc ID 1183213.1](https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1906014.1&id=Doc ID 1183213.1))

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

D- Waiting On ORA_J00# Process

The alert.log will look like:

Active process 1432 user 'oraj0' program 'oracle@mydb129 (J000)'
SHUTDOWN: waiting for logins to complete.

Shutdown normal is waiting for a job to finish. To check which job is currently running on the database use

SQL> select owner, job_name, session_id, secondary_os_process_id, running_instance from dba_scheduler_running_jobs;

Solution: First stop the active job, and then proceed to shutdown normal.

BEGIN
DBMS_SCHEDULER.stop_job (job_name => 'AUTO_SPACE_ADVISOR_JOB', force => TRUE);
END;
/

E- Shutdown is taking forever, can only do a SHUTDOWN ABORT

No error messages appear in the alert.log file. However, the resultant trace file within the udump directory from setting a trace event as follows:
Connect as SYS (/ as sysdba)
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> shutdown immediate;

Will Show:

PARSING IN CURSOR #n
delete from sys.col_usage$ c where not exists (select 1 from sys.obj$ o where o.obj# = c.obj# )

...followed by loads of.....

WAIT #2: nam='db file sequential read' ela= 23424 p1=1 p2=4073 p3=1
....
WAIT #2: nam='db file scattered read' ela= 1558 p1=1 p2=44161 p3=8
etc

Then eventually

WAIT #2: nam='log file sync' ela= 32535 p1=4111 p2=0 p3=0

...some other SQL....then back to

WAIT #2: nam='db file sequential read' ela= 205 p1=1 p2=107925 p3=1
WAIT #2: nam='db file sequential read' ela= 1212 p1=1 p2=107926 p3=1
WAIT #2: nam='db file sequential read' ela= 212 p1=1 p2=107927 p3=1
WAIT #2: nam='db file scattered read' ela= 1861 p1=1 p2=102625 p3=8
etc....

Solution: Periodically (eg 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 more information, please refer to Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort ([Doc ID 332177.1](https://support.oracle.com/epmos/faces/DocumentDisplay?parent=DOCUMENT&sourceId=1906014.1&id=Doc ID 332177.1))

REFERENCES

NOTE:332177.1 - Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort
NOTE:416658.1 - Shutdown Immediate Hangs / Active Processes Prevent Shutdown
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

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

16 + 10 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部