ITPub博客

首页 > 数据库 > Oracle > Troubleshooting Shutdown Immediate/Normal Hanging Issues

Troubleshooting Shutdown Immediate/Normal Hanging Issues

Oracle 作者:Curry_0316 时间:2015-12-14 11:38:04 0 删除 编辑
文章转自MOS:
Troubleshooting Shutdown Immediate/Normal Hanging Issues (文档 ID 1906014.1)

In this Document

Purpose
Details
   A- SMON disabling TX Recovery
  B- Active Processes Prevent Shutdown
  C- Waiting for MMON process
  D- Waiting On ORA_J00# Process
  E- Shutdown is taking forever, can only do a SHUTDOWN ABORT
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.1.0.1 [Release 9.2 to 12.1]
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.

3. You can kill the sessions preventing shutdown. Refer: Alert Log: Shutdown Waiting for Active Calls to Complete (Doc ID 1039389.6)

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 slave 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)

 

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, slave_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)

REFERENCES

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29953799/viewspace-1871819/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-10-11

  • 博文量
    86
  • 访问量
    244279