Thursday, November 14, 2013

How to map the operating system sessions with RMAN Channels


When only one RMAN session is active, the easiest method for determining the server session ID for an RMAN channel is to execute the following query on the target database while the RMAN job is executing:

COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
;
If you do not run the SET COMMAND ID command in the RMAN job, then the CLIENT_INFO column displays in the following format:

rman channel=channel_id

For example, in my case:

SID SPID         CLIENT_INFO
---- ------------ ------------------------------
  14 8374         rman channel=ORA_SBT_TAPE_1

As pointed out by the oracle documentation, you can monitor the sbt events:
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, 
       sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 's%bt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
;

In an example, the output from the above could look as below:
SPID EVENT             SEC_WAIT   STATE                CLIENT_INFO
---- ----------------- ---------- -------------------- ------------------------------
8642 Backup: sbtbackup 600        WAITING              rman channel=ORA_SBT_TAPE_1
indicating that RMAN has been waiting for the sbtbackup function to return for ten minutes.

No comments:

Post a Comment