Lets login to SCOTT user to create session on Oracle database, as ~]$ sqlplus “scott/tiger” If the documentation from the media manager does not provide the needed information, contact technical support for the media manager.Requesting you to be more careful while killing session, in case of you will kill the wrong session, it will be very destructive.īy mistake, In case if you kill background process, it will cause an instance crash. In some media managers, the only solution is to shut down and restart the media manager. If any remain, the next backup or restore operation may hang again, due to the previous hang. Where sid identifies the database instance to target, and the thread_id is the SPID value from the query in step 1.Ĭheck that the media manager also clears its processes. From a command prompt, run the following command: orakill sid thread_id On Windows, there is a command-line utility called ORAKILL which lets you kill a specific thread in this situation. For example, on Solaris execute a kill -9 command: % kill -9 8642 8374 Using operating system-level tools appropriate to your platform, kill the hung sessions. For example, the output may be as follows: SPID EVENT SEC_WAIT STATE CLIENT_INFOĨ642 sbtwrite2 600 WAITING rman channel=ORA_SBT_TAPE_1Ĩ374 sbtwrite2 600 WAITING rman channel=ORA_SBT_TAPE_2 SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,įROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS pĮxamine the SQL output to determine which sbt functions are waiting. For example, execute the following query: COLUMN EVENT FORMAT a10 Query V$SESSION and V$SESSION_WAIT as described in "Monitoring RMAN Through V$ Views". To terminate an Oracle process that is hung in the media manager: The warning about the media manager resources still applies in this case.
#ORACLE KILL SESSION QUERY CODE#
Once the hung channels in the media manager code are killed, the RMAN process detects this termination and proceed to exit, removing all connections except target connections that are still operative in the media management layer. In this case, the connections to the hung channels remain active as described previously. Removing default channel and polling connections causes the RMAN process to detect that one of the channels has died and then proceed to exit. Terminating the catalog connection does not cause the RMAN process to terminate because RMAN is not performing catalog operations while the backup or restore is in progress. Consult your media manager documentation for details. Those which cannot may keep resources busy or continue processing. Not all media managers can detect the termination of the Oracle process. If either the target connection or any of the auxiliary connections are executing in the media management layer, they will not terminate until the processes are manually killed at the operating system level. If target and auxiliary connections are not hung in the media manager code, they also terminate. If you kill the RMAN process itself, then you also kill the catalog connection, the auxiliary connection, the default channel, and the polling connections. Polling connections seem to be in an infinite loop while polling the RPC under the control of the RMAN process. The catalog connection and the default channel appear to hang, because they are waiting for RMAN to tell them what to do. RMAN usually hangs because one of the channel connections is waiting in the media manager code for a tape resource. Note that this will not unhang the session if the session is hung in media manager code.
#ORACLE KILL SESSION QUERY SERIAL NUMBER#
Then, run the following statement, substituting the sid_in_rman_output and serial number obtained from the query: ALTER SYSTEM KILL SESSION ' sid_in_rman_output, serial#' For example, run the following statement, where sid_in_rman_output is the number from the RMAN message: SELECT SERIAL# FROM V$SESSION WHERE SID= sid_in_rman_output You can kill the session using a SQL ALTER SYSTEM KILL SESSION statement.ĪLTER SYSTEM KILL SESSION takes two arguments, the sid printed in the RMAN message and a serial number, both of which can be obtained by querying V$SESSION. Note that the Oracle sid is different from the operating system process ID. The sid and devtype are displayed for each allocated channel. You can identify the Oracle session ID for an RMAN channel by looking in the RMAN log for messages with the format shown in the following example: channel ch1: sid=15 devtype=SBT_TAPE Terminating the Session with ALTER SYSTEM KILL SESSION
0 kommentar(er)
