Query to see the RMAN sessions
With the article “Query to see the RMAN sessions”, we have the possibility to verify if the RMAN backup is running or not.
In case potential backup issues are reported, we can check if the backup schedule time matches the access time of the RMAN sessions on the database. If the times are not aligned, we can report that the backup is stuck.
The fields that we will select are:
LAST_CALL_ET – The time when the session changed state from ACTIVE to INACTIVE and vice versa. STATUS – The status of the session whether ACTIVE or INACTIVE. PROCESS – The operating system process. PROGRAM – The client software from which the Oracle session was started. SCHEMANAME – The user with which the Oracle session was started. SID – The Oracle session Serial ID. SERIAL# – The unique serial code of an Oracle session. SPID – The operating system process associated with the Oracle session. OSUSER – The operating system user with which the Oracle session was started. MACHINE – The name of the client machine from which the Oracle session was started. TERMINAL – The name of the operating system from which the Oracle session was started. LOGON_TIME – Indicates the time when the session started.
To extract this information from the Oracle database, we have linked three tables:
GV$SESSION GV$PROCESS DBA_USERS
In the WHERE condition, we first linked the views V$PROCESS and V$SESSION through the join P.ADDR=S.PADDR, and linked the view V$SESSION with the view DBA_USERS through the join S.USER#=U.USER_ID. Furthermore, after the relationship between the V$PROCESS, V$SESSION, and DBA_USERS tables, to check the RMAN sessions, we pass the condition S.PROGRAM LIKE ‘%rman%’.
Query to see the RMAN sessions
Set lines 200
Set pages 60
select to_char(sysdate -(LAST_CALL_ET/86400),'DD-MON-YY hh24:mi:ss') LAST_CALL, s.status, s.process,s.program, s.schemaname,
s.sid, s.serial#, p.spid, s.osuser, S.machine, S.terminal, to_char(S.logon_time,'DD-MM-YYYY hh24.mi.ss') LOGON_TIME
from gv$session S,
dba_users U,
gv$process P
where P.ADDR = S.PADDR
and S.user# = U.user_id
and s.type ='USER'
and s.username is not null
and s.program like '%rman%';To view my guides on CRS commands click here!
At this LINK, however, you will find SQL queries
Stay updated on our activities by joining our community:
Facebook
Youtube
Linkedin
For any doubt, do not hesitate to comment on the article.
0 Comments