Processing EXP Export hang problem A case library cache lock

Forum for help

Last week, CD channel ZSL contact me, let me see him in the forum sent a help post "oracle11.2.1 entire library export can not use ", told me this is the actual work encountered problems, for this reason, the user's data has not been backed up for several days, The user's data security can not be guaranteed, I hope I can help to deal with, because the telephone guide more trouble, users have remote environment, so I propose to remote processing.

Recurring Problem

Remote Desktop is connected to the user, first of all, we have to look at the problem, do a full library of logical backup, as it is described, when the logical backup to perform to the location shown in the following figure, has been hang there, a few minutes did not move, resulting in a full library backup failed.

Through the phenomenon, we can analyze that this is a typical resource wait cause, so we need to first look up the cause of the problem from the Oracle wait event.

Thinking Analysis

The first thing is definitely to find the logical exported session, by querying the v$session view, specifying the program field for ' Exp.exe ' to perform the file lookup, the results are as follows:

Through the results of the query, found that there are 2 records, but the status is different, Sid 148 record status is Killed,sid 213 record status is active, ask the field engineer, that 148 is a few days before the logical backup, because there are other operations, The technician manually kills the session directly, but in the view it remains killed, and Sid 213 is the currently executing logical backup session. After observation, indeed, as it says, next we need to focus on the analysis of the SID 213 this session, why it will hang live, we need to check what he is currently waiting for what operation, this we can query v$session_wait session view, Query for the current wait event for a session with SID 213, the results are as follows:

Here we see a waiting event ' library cache Lock ', then under what circumstances will appear the library cache lock, here we first want to understand what is the library cache.

The Library cache, which is part of the shared pool in the Oracle SGA, is almost the most complex part of the Oracle memory structure, primarily hosting shared CUROSR (SQL) and Plsql objects (function,procedure , trigger) information, and the information that these objects depend on such objects as Table,index,view, the Library Cache mainly solves three problems of these objects: 1. Fast positioning, 2. Relationship dependency, 3 concurrency control, where concurrency control involves library cache lock, when a session accesses an object, it must first obtain lock and then the data pin that is accessed is in memory. The role of lock is to control concurrent access between processes, and the role of the PIN is to ensure data consistency, to prevent the data in the access to be exchanged out, to understand this situation, we need to query that the session is the logical backup of the session block, just find this blocking session, and then solve, Believe that the hang problem can be solved.

resolution Process

As mentioned above, first of all, find what session leads to the library cache lock, we need to use a Metalink article, "How to Found which",Holding a particular library Cache Lock [ID 122793.1], this article details how to find the session leading to holding, the effect is to use to Oracle's x$ internal table, interested students can see the article, I here directly cited.

The x$ table is Oracle's internal core table, and only the SYS user connects to the database to access it, and the following section of SQL identifies the blocking session.

Here I see a bright, SID 148 session, not the matter before we query out the state for killed session. It seems to have been determined that the session led to subsequent exp jams, so why is it blocked if the session that has been killed has been removed. Here we need to explain the operation of the next Oracle kill session.

In an Oracle database, you can terminate a process by using the kill session syntax:

Alter system kill session ' sid,serial# ', but alter system kill sessions only marks the conversation as recyclable and cuts off the mapping between the conversations and the server process. However, there is no resource release recycle, and once you try to connect, Pmon starts cleaning up the session that was killed, and Oracle rejects the connection operation, and the Server process is a client-side process that maintains two connections as long as the client is still starting. The Server process is not recycled. The server process does not release resources until the client actively stops communicating with the server process. How can we completely release resources? In fact, as long as we are at the operating system level, the end of the session of the process on the line, the first to find the process number, because the session is placed in the killed state, PADDR is modified, so the V$session and V$process Association to obtain the SPID, However, you can search by using the following SQL statement:

Once you know the SPID of the session, you can end the session directly in the operating system command mode by using the Orakill tool.

C:\>orakill ORCL 1639

Kill of thread ID 1639 in instance ORCL successfully signalled

C:\>

Then query v$session, you can see the killed state of the session has disappeared, blocking the session has been thoroughly cleared, Orakill is Oracle provided by the Windows platform to force the end of the Oracle process tool, This knowledge of the students interested in viewing Metalink documentsALTER SYSTEM KILL session does not release Locks killing a Thread on Windows NT [ID 100859.1] , and then try to try again exp export will hang live.

Validation Issues

Immediately perform the next exp export, verify that the problem has been resolved, under normal circumstances, in the export system objects and procedures will also pause for some time, this time, the query under the V$session_wait view, you can see the waiting event for sql*net message from The client follows the figure:

Sql*net message from the client belongs to idle wait, such as figure, wait type belongs to idle, no longer is the library Cache Lock, under normal circumstances can not ignore, belong to the regular waiting event. After about half a minute, began to see the data table export, the question of yours is satisfactorily resolved.

Follow-up summary

Through the introduction of this article, we not only know the solution to the problem in this case, but also basic understanding of the general hang to deal with Oracle's basic ideas:

1. First determine the waiting event for the hang session.

2. Then analyze the wait event to find out why the wait was caused by the lock, or because the resource was busy.

3. Finally, the problem can be solved by eliminating the problem according to its causes.

     hope the introduction of this example will help you in dealing with similar businesses in the future.