Patch Impact Analysis

Hi,

As DBA, we are frequently asked to apply patch in the test environment. Sometimes the requestor may ask the file version which is very easy for a DBA.

But when the requested ask us to find the object impacted by the patch needs some attention. There may not be lot of links available in the net. Easy way to find the object impacted by a patch is to issue the query which will ask for patch number. Voila!!! you get the objects...

Patch Impact Analysis (or) Object Impact Analysis:-

set lines 300
column site_version format a30
column patch_version format a30
select f.bug_number "BUG_NBR",
a.filename "FILE_NAME",
substr(c.version,1,20) "PATCH_VERSION",
substr(d.version,1,20) "SITE_VERSION",
b.executed_flag,
b.creation_date "PATCH_DATE"
from
ad_files a,
ad_patch_run_bug_actions b,
ad_file_versions c,
ad_file_versions d,
ad_patch_run_bugs e,
ad_bugs f
where a.file_id=b.file_id
and c.file_version_id=b.patch_file_version_id
and d.file_version_id=b.onsite_file_version_id
and b.executed_flag = 'Y'
and e.bug_id=f.bug_id
and e.patch_run_bug_id=b.patch_run_bug_id
and f.bug_number='&Patch_number'
order by b.creation_date desc;

Sometimes, the requester may ask us which patch has affected a particular file. we can use the following query to find the answer:-

set lines 300
column site_version format a30
column patch_version format a30
select f.bug_number "BUG_NBR",
a.filename "FILE_NAME",
substr(c.version,1,20) "PATCH_VERSION",
substr(d.version,1,20) "SITE_VERSION",
b.executed_flag,
b.creation_date "PATCH_DATE"
from
ad_files a,
ad_patch_run_bug_actions b,
ad_file_versions c,
ad_file_versions d,
ad_patch_run_bugs e,
ad_bugs f
where a.file_id=b.file_id
and a.filename like '&FILE_NAME' -- use %
and c.file_version_id=b.patch_file_version_id
and d.file_version_id=b.onsite_file_version_id
and b.executed_flag = 'Y'
and e.bug_id=f.bug_id
and e.patch_run_bug_id=b.patch_run_bug_id
order by b.creation_date desc;

I hope this will be useful to many Apps DBAs

Thanks & Regards
Punith

Welcome To The Oracle Applications Blog

Hi Apps DBAians,

Welcome to world of Oracle Apps DBA. We would like to post all the deatils pertaining to everyday activities like Cloning, patching, hot & cold backup, performance issues, etc.,

If you wanted to share anything, please leave a mail to orcleappsdba@gmail.com and it will posted with your credits.

Thanks in advance.
Punithavel
Sampath

Find and resolve Blocking Session

Hi Everyone,

In the life of Oracle/Oracle Apps DBA, we usually fall in the pit of blocking session. First we need to find whether any blocking session is happening by the following query:-

select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;

If there is any blocking session, we must check the "second_in_wait" as well as the sid which is blocking. If the session wait is higher and the its inactive for long time, then we can get rid of that session by:-

alter system kill session 'sid,serial#' immediate;
for eg: alter system kill session '1140,188' immediate;

Other Useful Queries related with Locks & sessions:-

SELECT o.object_name,
v.session_id sid,
v.oracle_username,
decode(l.type,'TM', 'DML enqueue (TM)', 'TX', 'Transaction enqueue (TX)', 'UL', 'User supplied (UL)', l.type),
decode(l.lmode,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.lmode,'990')) holding,
decode(l.request,0,'None',1,'Null',2,'Row Share',3,'Row Excl.',4,'Share',5,'S/Row Excl.',6,'Exclusive',to_char(l.request,'990')) wanting,
l.block,
s.terminal,
s.machine
FROM sys.v_$locked_object v,
sys.v_$lock l,
sys.dba_objects o,
sys.v_$session s
WHERE l.sid = v.session_id
AND s.sid = v.session_id
AND v.object_id = o.object_id;


select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held, s.event
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
and l.sid = s.sid
and v.session_id = l.sid
and s.process = v.process
order by oracle_username
, session_id


select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));

select SESSION_ID,NAME,P1,P2,P3,WAIT_TIME,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event#=enm.event#
-- and SESSION_ID=213 and SAMPLE_TIME>=(sysdate-&minute/(24*60));

CAN ALSO USE THE BELOW

select do.object_name,sid,s.serial#,s.osuser,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, --s.session_id,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

Thanks
Punithavel