When Oracle shuts down (shutdown immediate), hang

Two sets of 10.2.0.4 databases produced last night modified the parameters and needed to be restarted. After issuing the shutdown immediate command for about 10 minutes, the database has not been down yet. Check the background alert log, found from the beginning SHUTDOWN to the last output only a few logs, where the last log is: shutdown:active processes prevent SHUTDOWN operation.

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/92/8E/wKioL1kAjvrhU73jAABw-tRSRfA520.png "title=" 1.png "alt=" Wkiol1kajvrhu73jaabw-trsrfa520.png "/>

The figure is when you restore a scene on a virtual machine.

Open a new session connection displays connected, but cannot view and prompts not to connect. Execute shutdown immediate command again to get an error as follows:

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/92/8F/wKiom1kAj16gsXwWAABNplUV_ok888.png "title=" 1.png "alt=" Wkiom1kaj16gsxwwaabnpluv_ok888.png "/>

Immediately Baidu a bit of this problem, casually open a to see, said is because in the case of Sqlplus/as SYSDBA connection and the execution of the host command caused. If this is the case

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M02/92/8F/wKioL1kAmSTRvtX8AAAR2G7IuwQ315.png "title=" 1.png "alt=" Wkiol1kamstrvtx8aaar2g7iuwq315.png "/> immediately think of me this is also the case, hurriedly put the corresponding session from the operating system kill off. After kill, the database shuts down normally (this is the case on the virtual machine).

In the actual production is, the session kill and then wait a few, or did not return to, so had to force shutdown abort. Fortunately, the business is to stop early, but in the implementation of this command is still somewhat disturbed, it is feared that the database does not come.

While looking at the post, the author mentioned that he did the switch log, archive and checkpoint the operation to do in case, I think this is also a desirable place, in the future in the production depot should not stop the business immediately stop the database, you should do these operations to protect data.

As the saying goes, it is true that the first database successfully restarted, the second set of databases in shutdown also hang, but this error is not the same as the last time!!! (My little Heart.) ）

The alert output information is as follows:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/92/8F/wKioL1kAk1vjnbecAAARQrfm-FM569.png "title=" 1.png "alt=" Wkiol1kak1vjnbecaaarqrfm-fm569.png "/>

From the output information can be learned that has been carried out has not finished, but the business has stopped, what will be done. Because there are other things, do not take care of what is going on, directly kill off, but the database still can't stop AH.

Finally still had to shutdown abort, still is still disturbed. The end is safe.

This problem is not finally simulated on the virtual machine, always do not know what is.

To summarize:

1, oneself to dig a pit of taste is really not good to suffer ah.

2, to close the database to do some switching log files, archiving and checkpoint operations to ensure the security of the data

3, the database must be checked before shutting down, whether there are unresolved transactions, jobs, or other processes, to avoid the occurrence of a similar second situation

4, to have a strong heart, believe in themselves:)

A number of pertinent information was found on MOS after the incident:

MOS documentation

Troubleshooting Shutdown Immediate/normal Hanging issues (document ID 1906014.1)

List of four types of shutdown hang.

Reasons for the first case and solutions given

Shutdown Immediate hangs/active Processes Prevent Shutdown (document ID 416658.1)

Cause

This is not a bug.

If the DB Control repository is running on the database target against which shutdown immediate were attempted then an Inco Rrect order of events seems used.
Should stop DB control first to get rid of any connections between DB control and the repository database and then Shu Tdown the database with ' shutdown immediate '.

Current database sessions may show:

Sql> Select SID, USERNAME, program from V$session;
SID USERNAME Program
----- ---------------------- ----------------------------------
243 SYSTEM SQL Developer
246 Sysman OMS
247 [email protected] (q001)
248 [email protected] (q000)
251 DBSNMP [email protected] (TNS v1-v3)
252 Sysman OMS
253 Sysman OMS
254 Dbsnmp [email protected] (TNS v1-v3)
255 SYSTEM Java.exe
Sysman OMS

Clearly OMS and OEM is connected (Oracle Enterprise Manager Grid Control or Dbconsole) via users Sysman and DBSNMP.
These sessions should be de-activated (that's to log off any OEM, OMS, Sysman and DBSNMP) before the shutdown immediate I S attempted.

Oracle Enterprise Manager, Grid Control, dbconsole and agents keep doing internal processing.
This could include a few plsql notification procedures running on the database by database control like
BEGIN emd_notification. Queue_ready (: 1,: 2,: 3); END;

As per internal documentation of the shutdown immediate, if there is active calls then it would wait for all the active C Alls to finish.

Solution

To implement the solution:

1. Given OEM Connections is active (Sysman and DBSNMP), de-activate these sessions, i.e. by stopping the Agent/dbconsole

2. Then shutdown immediate as normal

-OR-

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


If This occurs, the only-to-shutdown the database is by doing:

Sql>
Shutdown abort
Startup restrict
Shutdown normal

The startup does any necessary recovery and cleanup, so this a valid cold backup can be taken afterward.

If this issue occurs frequently, it would is a good practice to see if there is any active user processes running in V$se Ssion or v$process before shutting down the instance.


If the problem persists, and no apparent user processes is active, you can set this event prior to issuing the shutdown C Ommand in order to see what is happening. This would dump a systemstate every 5 minutes while shutdown is hanging

Sql>
Connect/as SYSDBA
Alter session SET Events ' 10400 Trace name Context forever, Level 1 ';

Then issue the shutdown command.

3. You can kill the sessions preventing shutdown. Refer:alert Log:shutdown waiting for Active Calls to complete (Doc ID 1039389.6)

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1919778

When Oracle shuts down (shutdown immediate), hang