In 11i, R12.0 and R12.1, we can use adpatch apply=no to apply a patch in test mode.
Test Mode
In test mode, AutoPatch does not apply the patch. Instead, it lists each file it would have copied, relinked, executed, or generated and shows exactly what actions it would have performed had it applied the patch. It also runs AutoConfig in test mode to determine any impending changes to the configuration files. This allows you to see the effects of the patch on your production system before you apply it.
To run AutoPatch in test mode, include apply=no on the AutoPatch command line. This runs as if AutoPatch is applying the patch, except it does not.
In R12.2, we can use adop with apply=no, since most of the options and flags that are valid for adpatch are valid for adop.
To run adop in test mode, add the apply=no parameter to the adop command you would use if you were actually going to apply the patch. In test mode, adop will go through the process of applying the patch but will not perform any of the following actions:
Another way to do Patch Impact analysis is to apply the patch to the patch edition but don’t perform a cutover. Then, use the SQL provided by Oracle in $AD_TOP/sql,
ADZDCMPED.sql
This script is used to display the differences between RUN and PATCH edition
Displays the New/Changed objects
This script should only be run in PATCH edition
This script will compare editions and will report differences in all objects including tables, materialized views, indexes and cross-edition triggers. This can be very useful when trying to identify objects that may take a long time to regenerate during cutover, especially objects that are related to materialized views
++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET FEEDBACK OFF;
SET ECHO OFF;
exec ad_zd.set_edition(‘PATCH’);
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
REM
REM Spool results to adzdcmped.out file.
REM
spool adzdcmped.out
set pages 1000
set lines 200
prompt
prompt Database Information:
prompt =====================
prompt
column “Edition Name” format a15
column “Type” format a8
column “Status” format a8
column “Current?” format a8
REM Bug 15889193 – JWSMITH, Display same as ADZDSHOWED.sql
prompt =========================================================================
prompt = Editions
prompt =========================================================================
select
aed.edition_name “Edition Name”
, ad_zd.get_edition_type(aed.edition_name) “Type”
, decode(use.privilege, ‘USE’, ‘ACTIVE’, ‘RETIRED’) “Status”
, decode(aed.edition_name, sys_context(‘userenv’, ‘current_edition_name’),
‘CURRENT’, ”) “Current?”
from
all_editions aed
, database_properties prop
, dba_tab_privs use
where prop.property_name = ‘DEFAULT_EDITION’
and use.privilege(+) = ‘USE’
and use.owner(+) = ‘SYS’
and use.grantee(+) = ‘PUBLIC’
and use.table_name(+) = aed.edition_name
order by 1
/
variable patch_edition VARCHAR2(30);
variable run_edition VARCHAR2(30);
begin
:patch_edition := ad_zd.get_edition(‘PATCH’);
:run_edition := ad_zd.get_edition(‘RUN’);
end;
/
column “Owner” format a30
column “Object_Name” format a35
column “Object_Type” format a20
column “Status” format a7
column “InValid?” format a9
prompt ===========================================================================
prompt = Differences between RUN and PATCH edition (Editionable Objects)
prompt ===========================================================================
prompt
SELECT
eusr.user_name “Owner”,
dobjs.name “Object_Name”,
decode(dobjs.type#,
4, ‘VIEW’,
5, ‘SYNONYM’,
7, ‘PROCEDURE’,
8, ‘FUNCTION’,
9, ‘PACKAGE’,
11, ‘PACKAGE BODY’,
12, ‘TRIGGER’,
13, ‘TYPE’,
14, ‘TYPE BODY’,
‘ERROR’) “Object_Type”,
‘NEW’ “Status” ,
Decode(dobjs.status,’INVALID’,’INVALID’,NULL) “InValid?”
FROM sys.obj$ dobjs,
(
select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$
where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
and ed.name=Sys_Context(‘Userenv’, ‘Current_Edition_Name’)
union all
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type#=1 or user#=1) busr
, (select * from sys.obj$ where owner#=0 and type#=57) ed
where ed.name = ‘ORA$BASE’
and ed.name = Sys_Context(‘Userenv’, ‘Current_Edition_Name’)
) eusr
WHERE dobjs.owner#=eusr.user#
AND dobjs.type# in (4,5,7,8,9,11,12,13,14)
AND eusr.user_name in (select username from dba_users where
editions_enabled=’Y’)
AND dobjs.name NOT IN ( SELECT object_name
FROM dba_objects_ae
WHERE edition_name <> Sys_Context(‘Userenv’,
‘Current_Edition_Name’)
AND owner = eusr.user_name
AND object_type = decode(dobjs.type#,
4, ‘VIEW’,
5, ‘SYNONYM’,
7, ‘PROCEDURE’,
8, ‘FUNCTION’,
9, ‘PACKAGE’,
11, ‘PACKAGE BODY’,
12, ‘TRIGGER’,
13, ‘TYPE’,
14, ‘TYPE BODY’,
‘ERROR’))
UNION ALL
SELECT
eusr.user_name “Owner”,
dobjs.name “Object_Name”,
decode(dobjs.type#,
4, ‘VIEW’,
5, ‘SYNONYM’,
7, ‘PROCEDURE’,
8, ‘FUNCTION’,
9, ‘PACKAGE’,
11, ‘PACKAGE BODY’,
12, ‘TRIGGER’,
13, ‘TYPE’,
14, ‘TYPE BODY’,
‘ERROR’) “Object_Type”,
‘CHANGED’ “Status” ,
Decode(dobjs.status,’INVALID’,’INVALID’,NULL) “InValid?”
FROM sys.obj$ dobjs,
(
select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$
where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
and ed.name=Sys_Context(‘Userenv’, ‘Current_Edition_Name’)
union all
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type#=1 or user#=1) busr
, (select * from sys.obj$ where owner#=0 and type#=57) ed
where ed.name = ‘ORA$BASE’
and ed.name = Sys_Context(‘Userenv’, ‘Current_Edition_Name’)
) eusr
WHERE dobjs.owner#=eusr.user#
AND dobjs.type# in (4,5,7,8,9,11,12,13,14)
AND eusr.user_name in (select username from dba_users where
editions_enabled=’Y’)
AND dobjs.name IN ( SELECT object_name
FROM dba_objects_ae
WHERE edition_name <> Sys_Context(‘Userenv’,
‘Current_Edition_Name’)
AND owner = eusr.user_name
AND object_type = decode(dobjs.type#,
4, ‘VIEW’,
5, ‘SYNONYM’,
7, ‘PROCEDURE’,
8, ‘FUNCTION’,
9, ‘PACKAGE’,
11, ‘PACKAGE BODY’,
12, ‘TRIGGER’,
13, ‘TYPE’,
14, ‘TYPE BODY’,
‘ERROR’))
ORDER BY “Owner”, “Status”, “Object_Name”;
REM Merged in ADZDSHOWOBJS.sql with slight modifications
column edition_name format A15
prompt
prompt
prompt =========================================================================
prompt = Editioned Objects Per Edition
prompt =========================================================================
prompt
ttitle COL 27 “Actualized Referenced” skip 1 –
COL 17 “——————————– ——————————-”
select * from
(
select
eusr.edition_name edition_name
, count(decode(obj.type#,88,NULL,decode(obj.status,1,1,NULL))) a_valid
, count(decode(obj.type#,88,NULL,decode(obj.status,1,NULL,1))) a_invalid
, count(decode(obj.type#,88,NULL,1)) a_total
, count(decode(obj.type#,88,decode(obj.status,1,1,NULL),NULL)) s_valid
, count(decode(obj.type#,88,decode(obj.status,1,NULL,1),NULL)) s_invalid
, count(decode(obj.type#,88,1,NULL)) s_total
, count(1) total
from
sys.obj$ obj
, (
select
xusr.user#
, xusr.ext_username user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 2) xusr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where xusr.spare2 = ed.obj#
union
select
busr.user#
, busr.name user_name
, ed.name edition_name
from
(select * from sys.user$ where type# = 1 or user# = 1) busr
, (select * from sys.obj$ where owner# = 0 and type# = 57) ed
where ed.name = ‘ORA$BASE’
) eusr
where obj.owner# = eusr.user#
and obj.type# in (4,5,7,8,9,11,12,13,14,22,87,88)
and obj.remoteowner is null
and eusr.user_name in
( select username from dba_users where editions_enabled=’Y’ )
group by eusr.edition_name
) x
order by 1, 2;
prompt
prompt Note :
prompt (A_) – Actualized objects
prompt (S_) – Stubs
prompt
TTITLE OFF
column owner format a20
column table_name format a30
column sync format a7
prompt
prompt
prompt
prompt =========================================================================
prompt = Seed Data Tables Prepared in the Current Edition
prompt =========================================================================
select
col.owner owner
, col.table_name table_name
, trg.crossedition sync
from
dba_tab_columns col
, dba_objects obj
, dba_source src
, dba_triggers trg
where col.owner in
( select oracle_username
from fnd_oracle_userid
where read_only_flag in (‘A’,’E’) )
and col.table_name not like ‘%#’
and col.column_name = ‘ZD_EDITION_NAME’
and obj.owner = ad_zd.apps_schema
and obj.object_name = ad_zd_seed.eds_function(col.table_name)
and obj.object_type = ‘FUNCTION’
and obj.edition_name = sys_context(‘userenv’, ‘current_edition_name’)
and src.owner = obj.owner
and src.name = obj.object_name
and src.type = obj.object_type
and src.text like ‘%’||sys_context(‘userenv’,
‘current_edition_name’)||’%’
and trg.owner(+) = ad_zd.apps_schema
and trg.trigger_name(+) = ad_zd_seed.eds_fcet(col.table_name)
order by owner, table_name
/
prompt
prompt
prompt
prompt =========================================================================
prompt = New Tables
prompt =========================================================================
column Table_Name format a30
SELECT
substr(v.view_name,1,Length(v.view_name)-1) Table_Name
FROM dba_views_ae v
WHERE v.editioning_view = ‘Y’
AND v.edition_name = :patch_edition
AND v.view_name NOT IN (SELECT vi.view_name
FROM dba_views_ae vi
WHERE vi.editioning_view = ‘Y’
AND vi.view_name = v.view_name
AND vi.owner = v.owner
AND vi.edition_name = :run_edition)
/
prompt
prompt
prompt
prompt =========================================================================
prompt = New/Changed Columns
prompt =========================================================================
declare
procedure exec_force(stmt in varchar2) is
no_such_table exception;
pragma exception_init(no_such_table, -942);
begin
execute immediate stmt;
exception
when no_such_table then
null;
end;
begin
exec_force(‘drop table ad_zd_report_table_columns’);
end;
/
declare
procedure exec_force(stmt in varchar2) is
begin
execute immediate stmt;
end;
begin
exec_force(‘create ‘ || ‘table ‘ || ‘ad_zd_report_table_columns
(
p_owner varchar2(30) not null,
p_view_name varchar2(30) not null,
p_view_column_name varchar2(30) not null,
p_table_column_name varchar2(30) not null,
p_status varchar2(30) not null
)’);
end;
/
DECLARE
CURSOR c_changed_evs IS
SELECT
v.view_name,
v.owner
FROM dba_views_ae v
WHERE v.editioning_view = ‘Y’
AND v.edition_name = :patch_edition
AND v.view_name IN (SELECT vi.view_name
FROM dba_views_ae vi
WHERE vi.editioning_view = ‘Y’
AND vi.view_name = v.view_name
AND vi.owner = v.owner
AND vi.edition_name = :run_edition);
cursor new_columns(x_owner varchar2,x_view_name varchar2) is
select c.view_column_name,
c.table_column_name
from dba_editioning_view_cols_ae c
where c.view_name = x_view_name
and c.owner = x_owner
and c.edition_name = :patch_edition
and c.view_column_name not in (select ci.view_column_name
from dba_editioning_view_cols_ae ci
where ci.view_name = c.view_name
and ci.owner = c.owner
and ci.edition_name = :run_edition);
cursor revised_columns(x_owner varchar2,x_view_name varchar2) is
select c.view_column_name,
c.table_column_name
from dba_editioning_view_cols_ae c
where c.view_name = x_view_name
and c.owner = x_owner
and c.edition_name = :patch_edition
and exists (select ci.view_column_name
from dba_editioning_view_cols_ae ci
where ci.view_name = c.view_name
and ci.owner = c.owner
and ci.edition_name = :run_edition
and ci.view_column_name = c.view_column_name
and ci.table_column_name <> c.table_column_name);
l_view_name varchar2(30);
l_owner varchar2(30);
l_view_col varchar2(30);
l_table_col varchar2(30);
BEGIN
OPEN c_changed_evs;
FETCH c_changed_evs into l_view_name, l_owner;
while (c_changed_evs%found) loop
open new_columns(l_owner,l_view_name);
fetch new_columns into l_view_col,l_table_col;
while (new_columns%found) loop
insert into ad_zd_report_table_columns (p_owner,p_view_name,p_view_column_name,p_table_column_name,p_status)
values(l_owner, l_view_name, l_view_col, l_table_col,’NEW’);
fetch new_columns into l_view_col,l_table_col;
end loop;
close new_columns;
open revised_columns(l_owner,l_view_name);
fetch revised_columns into l_view_col,l_table_col;
while (revised_columns%found) loop
insert into ad_zd_report_table_columns (p_owner,p_view_name,p_view_column_name,p_table_column_name,p_status)
values(l_owner, l_view_name, l_view_col, l_table_col,’CHANGED’);
fetch revised_columns into l_view_col,l_table_col;
end loop;
close revised_columns;
fetch c_changed_evs into l_view_name, l_owner;
end loop;
close c_changed_evs;
commit;
END;
/
column TableName format a30
column ColumnName format a30
column status format a10
select substr(p_view_name,1,length(p_view_name)-1) TableName,
p_view_column_name ColumnName,
p_status STATUS
from ad_zd_report_table_columns
order by TableName,status desc,ColumnName
/
declare
procedure exec_force(stmt in varchar2) is
no_such_table exception;
pragma exception_init(no_such_table, -942);
begin
execute immediate stmt;
exception
when no_such_table then
null;
end;
begin
exec_force(‘drop table ad_zd_report_table_columns’);
end;
/
prompt
prompt
prompt
prompt =========================================================================
prompt = New Indexes
prompt =========================================================================
column owner format a30
column table_name format a30
column index_name format a30
select
idx.owner owner
, idx.table_name
, ad_zd_table.original_index_name(idx.index_name) index_name
from dba_indexes idx
where idx.owner in
( select oracle_username
from fnd_oracle_userid
where read_only_flag in (‘A’, ‘B’, ‘E’,’U’) )
and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, ‘c’)
and not exists ( SELECT inner.index_name
FROM dba_indexes inner
WHERE inner.index_name = ad_zd_table.original_index_name(idx.index_name)
AND inner.owner = idx.owner)
order by 1, 2, 3
/
prompt
prompt
prompt
prompt =========================================================================
prompt = Revised Indexes
prompt =========================================================================
column owner format a30
column table_name format a30
column index_name format a30
select
idx.owner owner
, idx.table_name
, idx.index_name patched_index
, oidx.index_name original_index
from dba_indexes idx, dba_indexes oidx
where idx.owner in
( select oracle_username
from fnd_oracle_userid
where read_only_flag in (‘A’, ‘B’, ‘E’,’U’) )
and regexp_like(idx.index_name, ad_zd_table.revised_index_regexp, ‘c’)
and oidx.owner(+) = idx.owner
and oidx.index_name = ad_zd_table.original_index_name(idx.index_name)
order by 1, 2, 3
/
prompt
prompt
prompt
prompt =========================================================================
prompt = Cross-edition Triggers
prompt =========================================================================
column owner format a30
column trigger_name format a30
column table_owner format a30
column table_name format a30
column crossedition format a10
column status format a10
select trg.owner,
trg.trigger_name,
trg.table_owner,
trg.table_name,
crossedition,
status
from dba_triggers trg
where crossedition in (‘FORWARD’, ‘REVERSE’)
order by 1
/
prompt
prompt
prompt
prompt =========================================================================
prompt = New/Revised Materialized views
prompt =========================================================================
column “Owner” format a30
column “MView Name” format a30
column “Status” format a7
SELECT mvq.owner “Owner”,
ad_zd_mview.get_mv_name(mvq.view_name) “MView Name”,
‘NEW’ “Status”
FROM dba_views_ae mvq,
fnd_oracle_userid fou
WHERE fou.read_only_flag in (‘A’,’B’, ‘E’, ‘U’)
AND mvq.owner = fou.oracle_username
AND mvq.editioning_view = ‘N’
AND mvq.view_name LIKE ‘%#’
AND mvq.edition_name = :patch_edition
AND NOT EXISTS (SELECT mv.mview_name
FROM dba_mviews mv
WHERE mv.owner = mvq.owner
AND mv.mview_name = ad_zd_mview.get_mv_name(mvq.view_name))
UNION
SELECT mvq.owner “Owner”,
ad_zd_mview.get_mv_name(mvq.view_name) “MView Name”,
‘CHANGED’ “Status”
FROM dba_views_ae mvq,
fnd_oracle_userid fou
WHERE fou.read_only_flag in (‘A’,’B’, ‘E’, ‘U’)
AND mvq.owner = fou.oracle_username
AND mvq.editioning_view = ‘N’
AND mvq.view_name LIKE ‘%#’
AND mvq.edition_name = :patch_edition
AND EXISTS (SELECT mv.mview_name
FROM dba_mviews mv
WHERE mv.owner = mvq.owner
AND mv.mview_name = ad_zd_mview.get_mv_name(mvq.view_name))
ORDER BY “Owner”, “Status”, “MView Name”
/
prompt
spool off;
exit;
/
