Shulou(Shulou.com)06/01 Report--

Two sets of 10.2.0.4 databases produced last night have modified the parameters and need to be restarted. After waiting for about 10 minutes after issuing the shutdown immediate command, the database has not yet been down down. Check the background alert log and find that only a few logs are output from the beginning of shutdown to the end, and the last log is: SHUTDOWN: Active processes prevent shutdown operation.

The picture shows a screenshot of restoring the scene on a virtual machine.

Opening a new session connection shows that it is connected, but cannot view it and indicates that it is not connected. Execute the shutdown immediate command again and get the error as follows:

Baidu immediately took a look at this problem and casually clicked on it, saying that it was caused by the execution of the host command in the case of sqlplus / as sysdba connection. This is the case in the picture below.

Immediately think of the same situation on my side, quickly kill the corresponding session from the operating system. After the kill is dropped, the database shuts down normally (this is the case on the virtual machine).

In the actual production, however, we waited a few minutes after dropping the session kill, but there was still no response, so we had to force the shutdown abort. Fortunately, the business has stopped ahead of time, but there is still some apprehension in the implementation of this command, for fear that the database will not be able to get up.

When reading the post, the author mentioned that he switched logs, archives and checkpoint operations before stopping the database just in case. I think this is also a desirable place. In the future, when stopping the production database, we should not stop the database immediately. We should also do these operations to protect the data.

As the saying goes, it never rains but it pours. Just after the first database was successfully restarted, the second database of shutdown also lived in hang, but this error report is not the same as last time! My little heart. )

The output information of alert is as follows:

From the output information, we can see that the implementation has not been completed, but the business has been stopped, what will be carried out? Because there are other things, I didn't care what was going on, so I dropped kill directly, but the database still couldn't stop.

In the end, it has to be shutdown abort, still uneasy. In the end, nothing happened.

In the end, this problem was not simulated on the virtual machine, and I still don't know what it is.

To sum up:

1. It's really hard to dig a hole for yourself.

2. Do some operations to switch log files, archives and checkpoint before shutting down the database to ensure the security of the data

3. Be sure to check whether there are any outstanding transactions, JOB, or other processes before closing the database to avoid situations similar to the second.

4. Have a strong heart and believe in yourself.

Some relevant information was found on MOS afterwards:

MOS document

Troubleshooting Shutdown Immediate/Normal Hanging Issues (document ID 1906014.1)

List four cases of shutdown Hang.

The cause of the first situation and the solution 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 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'.

Current database sessions may show:

SQL > select SID, USERNAME, PROGRAM from v$session

SID USERNAME PROGRAM

-

243 SYSTEM SQL Developer

246 SYSMAN OMS

247 oracle@lgiora09 (Q001)

248 oracle@lgiora09 (Q000)

251 DBSNMP emagent@lgiora09 (TNS V1-V3)

252 SYSMAN OMS

253 SYSMAN OMS

254 DBSNMP emagent@lgiora09 (TNS V1-V3)

255 SYSTEM java.exe

256 SYSMAN OMS

Clearly OMS and OEM are connected (Oracle Enterprise Manager Grid Control or DBConsole) via users SYSMAN and DBSNMP.

These sessions should be de-activated (that is to log off any OEM, OMS, SYSMAN and DBSNMP) before the shutdown immediate is attempted.

Oracle Enterprise Manager, Grid Control, Dbconsole and agents keep doing internal processing.

This may 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 are active calls then it would wait for all the active calls to finish.

SOLUTION

To implement the 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.

If the problem persists, and no apparent user processes are active, you can set this event prior to issuing the shutdown command in order to see what is happening. This will 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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.