How to perform Patch Impact Analysis in 12.1 and 12.2

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:

  • Copy files from the patch directory to the Oracle E-Business Suite file system
  • Archive object modules into the product libraries
  • Relink executables
  • Generate forms, reports, PL/SQL libraries, or menu files
  • Run SQL or EXEC commands (commands that change the database)
  • Instantiate new configuration files
  • Update the patch information files
  • Update patch information and release version in the database 

    Reference: Oracle E-Business Suite Maintenance Guide

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;

/

 

 

What is the new view ad_objects and how is it different from dba_objects?

  • Developers often use the user_objects or all_objects data dictionary view to confirm that there are no unexpected invalid objects.
  • Due to a database limitation these dictionary views only return correct object status after running a full compilation procedure (utl_recomp.recomp_parallel or ad_zd.compile).
  • As a workaround, you can check object status using the ad_objects view included with online patching.

select * from ad_objects where status=’INVALID’;

If we compare the total number of objects in ad_objects to dba_objects, we find that ad_objects has one more object than dba_objects:

SQL> select count(*) from ad_objects

COUNT(*)
———-
394774

SQL>  select count(*) from dba_objects

COUNT(*)
———-
394773

So, what’s the difference between ad_objects and dba_objects?
SQL> select object_name, object_type, owner from ad_objects
2  minus
3  select object_name, object_type, owner from dba_objects;
OBJECT_NAME
——————————————————————————–
OBJECT_TYPE        OWNER
——————- ——————————
_default_auditing_options_
TABLE            SYS
How are the columns different between ad_objects  and dba_objects? Compare the following table structures below:

SQL> desc ad_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                   NOT NULL VARCHAR2(30)
OBJECT_TYPE                        VARCHAR2(18)
ACTUAL                         VARCHAR2(1)
STATUS                         VARCHAR2(7)
CTIME                            DATE
MTIME                            DATE
STIME                            DATE

SQL> desc dba_objects
Name                       Null?    Type
—————————————– ——– —————————-
OWNER                            VARCHAR2(30)
OBJECT_NAME                        VARCHAR2(128)
SUBOBJECT_NAME                     VARCHAR2(30)
OBJECT_ID                        NUMBER
DATA_OBJECT_ID                     NUMBER
OBJECT_TYPE                        VARCHAR2(19)
CREATED                        DATE
LAST_DDL_TIME                        DATE
TIMESTAMP                        VARCHAR2(19)
STATUS                         VARCHAR2(7)
TEMPORARY                        VARCHAR2(1)
GENERATED                        VARCHAR2(1)
SECONDARY                        VARCHAR2(1)
NAMESPACE                        NUMBER
EDITION_NAME                        VARCHAR2(30)

How to Determine that all Tables and Materialized Views have an Editioning View

After applying 13543062, we ran ADZDSHOWDDLS.sql to see if all tables and materialized views had editioning views. Below, an excerpt of ADZDSHOWDDLS.out shows the editioning results for tables and materialized views:

Phase                               Count Status

UPGRADE_TABLE          18355 S – Successfully Executed

UPGRADE_MVIEW              1 N – Not Executed                            1 R – Running                          113 S – Successfully Executed                           11 E – Error

Run the following SQL statement to check the count of editioning views versus the count of tables and materialized views:

select * from dba_objects where (object_type in (‘VIEW’) and object_name like ‘%#’)

union

select * from dba_objects where object_type in (‘MATERIALIZED VIEW’, ‘TABLE’);

Of course, we can do it an easier way and compare VIEW_NAME and TABLE_NAME in DBA_EDITIONING_VIEWS and do a “count” to see if the result agrees with the results from above.

describe dba_editioning_views

OWNER                 NOT NULL VARCHAR(128)

VIEW_NAME         NOT NULL VARCHAR(128)

TABLE_NAME       NOT NULL VARCHAR(128)

Or, select table_name where the view_name doesn’t exist.

Or, use the Section 20 SQL from ADZDDBCC.sql to find tables that don’t have an editioning view. In order to patch custom tables using online patching, custom tables require an editioning view and APPS synonym. The following SQL will find tables that may not have an editioning view.

**********************************************************************

SECTION-20  [full]

**********************************************************************

“Table must have an editioning view.”

– P2: These tables may not be patched using online patching.

– Fix:  Execute the table upgrade procedure.

SQL> exec ad_zd_table.upgrade(table_owner, new_table_name)

Note: Tables that are dynamically created by application runtime can be ignored.

Note: Tables that end with “_A” are typically audit tables by the Audit Trail feature, and can be ignored.

Note: This check is only active after Online Patching Enablement.

#

select tab.owner owner, tab.table_name table_name

from dba_tables tab

where tab.owner in

( select oracle_username from system.fnd_oracle_userid

where  read_only_flag in (‘A’, ‘B’, ‘E’) )

and tab.temporary = ‘N’

and tab.secondary = ‘N’

and tab.iot_type is null

and tab.tablespace_name not in (‘APPS_TS_NOLOGGING’, ‘APPS_TS_QUEUES’)

and tab.table_name not like ‘%$’

and not regexp_like(tab.table_name, ‘^AQ\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^AW\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^MLOG\$’, ‘c’)

and not regexp_like(tab.table_name, ‘^BSC_DI_[0-9_]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^BSC_D_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_ADJUSTMENT_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_DETAIL_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^FA_ARCHIVE_SUMMARY_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_DAILY_POST_INT_.+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_INTERCO_BSV_INT_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_BAL_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^GL_MOVEMERGE_INTERIM_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^XLA_GLT_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ICX_POR_C[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ICX_POR_UPLOAD_[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^IGI_SLS_[0-9]+$’, ‘c’)

and not regexp_like(tab.table_name, ‘^JTF_TAE_[0-9]+.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^JTY_[0-9]+_.*$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ZPBDATA[0-9]+_EXCPT_T$’, ‘c’)

and not regexp_like(tab.table_name, ‘^ZX_DATA_UPLOAD_.*$’, ‘c’)

/* Note: Exclusion list for AD varies by release */

and ( ( tab.table_name not in

(‘TXK_TCC_RESULTS’,

‘AD_DEFERRED_JOBS’,

‘AD_TABLE_INDEX_INFO’,

‘FND_INSTALL_PROCESSES’,

‘AD_UTIL_PARAMS’,

‘AD_PATCHED_TABLES’,

‘AD_ZD_DDL_HANDLER’,

‘AD_OBSOLETE_OBJECTS’,

‘FND_PRODUCT_INSTALLATIONS’)

and

( select codelevel from ad_trackable_entities

where upper(abbreviation)=’AD’ ) < ‘C.1’ )

or

( tab.table_name not in

(‘TXK_TCC_RESULTS’,

‘AD_DEFERRED_JOBS’,

‘AD_TABLE_INDEX_INFO’,

‘FND_INSTALL_PROCESSES’,

‘AD_UTIL_PARAMS’ )

and

( select codelevel from ad_trackable_entities

where upper(abbreviation)=’AD’ ) >= ‘C.1′ )

)

and (tab.owner, tab.table_name) not in

( select qt.owner, qt.queue_table

from   dba_queue_tables qt )

and (tab.owner, tab.table_name) not in

( select mv.owner, mv.container_name

from   dba_mviews mv )

and (tab.owner, tab.table_name) in

( select syn.table_owner, syn.table_name

from   dba_synonyms syn

where  syn.owner  in

( select oracle_username from system.fnd_oracle_userid

where  read_only_flag =’U’ ) )

and not exists

( select ev.owner, ev.view_name

from dba_editioning_views ev

where ev.owner      = tab.owner

and ev.table_name = tab.table_name

and ev.view_name  = substrb(tab.table_name, 1, 29)||’#’ )

and exists

( select null

from fnd_oracle_userid au, dba_users du

where au.oracle_username = du.username

and au.read_only_flag  = ‘U’

and du.editions_enabled = ‘Y’ )

and not exists

( select 1

from system.fnd_oracle_userid fou

, fnd_product_installations fpi

, ad_obsolete_objects aoo

where fpi.application_id = aoo.application_id

and fou.oracle_id = fpi.oracle_id

and fou.oracle_username = tab.owner

and aoo.object_name = tab.table_name

and aoo.object_type = ‘TABLE’ )

order by tab.owner, tab.table_name

/

 

Finding the Patches inside a Merged Patch

SELECT applied_patch_id, rapid_installed_flag, patch_name, patch_type, maint_pack_level
FROM   applsys.ad_applied_patches
order by 1 desc;
select * from ad_patch_drivers
where applied_patch_id = 102974;
Notice the ORIG_PATCH_NAME is a surrogate number instead of the old convention of “merged”.
PATCH_DRIVER_ID     APPLIED_PATCH_ID     ORIG_PATCH_NAME

                       99582                            102974                     2123079401

SELECT ab.bug_number, acp.patch_abstract
FROM  ad_comprising_patches acp, ad_bugs ab
WHERE acp.bug_id = ab.bug_id AND acp.patch_driver_id = 99582;

BUG_NUMBER    PATCH_ABSTRACT

 21830810            R12.TXK.C.DELTA.8
 23569114            INVALID APPS DATABASE USER CREDENTIALS – TXKGENADOPWRAPPER_PL – 12.2.4
23705992             TNSNAMES.ORA GENERATED WITH SCAN NAME INSTEAD OF SCAN IPS FOR CUSTOM RACE DB CLONE
18525466             REVERT CHANGES MADE TO VALIDATE MANAGED SERVER PORTS OF ALL NODES

Tips From Our Latest Release 12.2.6 First Pass Upgrades

We’ve just finished two successful Release 12.2.6 First Pass Upgrades in the last six weeks. We always encounter and resolve a few unique issues with each client. Here are two examples.

The first issue occurred while running Patch 10124646 (the 12.2.0 upgrade patch), and the second occurred after running that patch.

1. During the 10124646 patch (the upgrade to 12.2.0), with about 22000 jobs completed and about 10000 jobs remaining, we saw this error:

ATTENTION: All workers either have failed or are waiting:

FAILED: file CustMigrationTool.class on worker 1.
FAILED: file CustMigrationTool.class on worker 2.
FAILED: file CustMigrationTool.class on worker 3.
FAILED: file CustMigrationTool.class on worker 4.
FAILED: file EDRXDOMigration.class on worker 5.
FAILED: file CustMigrationTool.class on worker 6.
FAILED: file CustMigrationTool.class on worker 7.
FAILED: file CustMigrationTool.class on worker 8.
FAILED: file CustMigrationTool.class on worker 9.

According to the ad worker log: adworker001.log
Exception in thread “main” java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-04063: package body “APPS.FND_TRACE” has errors
ORA-06508: PL/SQL: could not find program unit being called: “APPS.FND_TRACE”
ORA-06512: at line 5

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:397)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
at oracle.jdbc.driver.T4C7Ocommoncall.processError(T4C7Ocommoncall.java:100)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:64)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:583)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:5222)
at oracle.apps.edr.util.EDRXDOMigration.main(EDRXDOMigration.java:466)

We found the partial solution in MOS Doc. ID: 1959572.1, CustMigrationTool.class Error Upgrading from EBS R12.1.3 to R12.2 as the Connection Is Not Being Made In the 60 Seconds Defined In sqlnet.ora, which says that the connection couldn’t be made in the 60 seconds timeout value defined in sqlnet.ora, so the value needs to be changed to 120 seconds. To resolve the issue:
1. Change sqlnet.ora, SQLNET.INBOUND_CONNECT_TIMEOUT=120
2. Restart the db server
3. Restart the patch

However, that was not the only solution that was required. It turns out that using Recommended Patch Collections (RPC) creates objects that have a higher version than objects delivered by the 12.2.0 upgrade. This means the 12.2.0 upgrade will not replace objects with a higher version number and the upgrade fails. We had to find the correct versions of the code that worked together and then drop a trigger in order to get the FND_TRACE package body and specification to compile. Very interesting.
_____________________________________________________________

2. When running the “Configure the Instance” step, we couldn’t run AutoConfig on the database tier. This step is from the “Finish the Upgrade” section of the 12.2 upgrade guide on page 3-8.

We found MOS Doc. ID: 2117174.1, 12.2 Adop Cleanup Fails with Error “ETCC not run in the Database Node” After Running ETCC on the Database Node.

This note suggests that either the /etc/host file is not correctly formatted, or the nodes don’t match in the TXK_TCC_RESULTS table or the FND_NODES table. While this note is not a direct “hit”, we did find the domain was appended to the node_name. We updated the node_name in the TXK_TCC_RESULTS table to fix the issue:

update APPLSYS.TXK_TCC_RESULTS set node_name=’data2′ where node_name =’data2.ca.local’;

Email sherri@trutek.com to discuss pricing or schedule a time to have us help you with a First Pass Upgrade. We have completed the First Pass Upgrade in as few as three weeks, but it can take longer if we experience new issues. Please see our webpage for more information about our First Pass Upgrades consulting engagement. We also provide training to the rest of your managers, DBAs and development staff to help them understand the best practices of Online Patching.

Overview of Online Patching Phases

Prepare

Use adop phase=prepare sync_mode=delta. Sync_mode=delta is a new feature delivered in AD-TXK Delta 8. It is much faster than the previous sync_mode = patch. The new sync_mode=delta takes about 4-10 minutes on TruTek test servers. If the previous patch cycle was aborted, then FS_CLONE is run during the next prepare. The prepare phase can fail due to long running concurrent requests that are incompatible with the ADZDPATCH concurrent program.

FS_CLONE

Will copy all files including customizations and configurations – takes about 1 hour 40 minutes.Typiically, fs_clone only needs to be run after an abort. The usual sequence would be:

abort

cleanup (full)

fs_clone

Apply

We tested three apply modes: normal (default), hotpatch and downtime.

We found that downtime patching works, but the system must be down and the patch needs to be tested first in a test environment. Using hotpatch when the patch is not a hotpatch can result in a hung patch that can’t be aborted and can also cause an abandoned node. Neither downtime or hotpatch mode use an adop patch cycle and therefore cannot be aborted. Hotpatch mode requires the WebLogic Admin server to be running.

Finalize

Finalize prepares for the cutover by compiling invalid objects. This helps reduce the time cutover may need.

We found that if developers are applying hot custom code during finalize, that the compilation of objects can take a very long time (19 hours in one test)

Cutover

This is the most sensitive phase in ADOP for several reasons:

Users are not able to work during this phase, so the downtime needs to be very predictable

Any underlying code changes that affect tables, materialized views, and other objects may require the regeneration of logical views for tables, and regeneration of materialized views. The regeneration of materialized views from logical views is accomplished fairly quickly, however, repopulating the materialized view can take hours. I don’t think it is possible to prevent the regeneration of MVs, but we need to be able to predict the time it will take to regenerate MVs and other objects, so we can accurately advise the users/business of the downtime associated with each cutover.

The running concurrent requests need to be documented before we shutdown the concurrent managers. The ICM must also be shutdown. Otherwise, the cutover can take several hours to complete. This is because cutover waits for running concurrent requests to complete. It’s better to work with the business users and ask them to put long running requests on hold, before cutover starts.

We tested the cm_wait parameter (units are minutes). We found that no new requests were started, but the Internal Concurrent Manager is not shutdown/stopped. It took a long time for associated database sessions to be terminated.

Cleanup If a patch cycle has failed, it is possible to abort the patching cycle and return to normal runtime operation. Abort can be run for Prepare, Apply, Finalize, and Cutover phases. The abort command can only be used before successful completion of the cutover phase. After cutover, the system is running on the new edition, and abort is no longer possible for that patching cycle. An abort of prepare, apply or finalize, if complete on the master node and it fails on a slave node, will cause an abandoned node. There are two solutions: first, detach and re-attach the abandoned node. This can be very time consuming. If you have a “pairsfile” created ahead of time this can be done fairly quickly. There is also a  solution (undocumented on MOS) that has worked in all test cases, that requires shared disk on the apps tier. This solution is an update to the AD_ADOP_SESSIONS table, followed by an FS_CLONE.

Abort

This phase seems to be the easiest, and the patch session will determine if the cleanup needs to be standard or full. You can specify quick, but we’ve found it’s safest to let the cleanup determine what mode to use. If the proper mode is not used, then cleanup may be run during the prepare( followed by a fs_clone), extending the time for prepare.

Brief Comparison of R12.1.3 and R12.2.6 Upgrades

The table below shows the major comparable steps in the 11i to 12.1.3 upgrade and the 12.1.3 to 12.2.6. The highlighted sections in the 12.2.6 upgrade indicate the new steps relative to past upgrades. These new sections, while they don’t take very long to complete during the downtime window, require extensive effort to prepare the instance for the upgrade.

For example, the more customizations you have, the longer it will take to make sure your customizations are minimally compliant to the Release 12.2 development standards. The process of updating your customizations to match new table structures is still a major part of fixing your customizations, but now you must also make sure your customizations are at least minimally compliant.

The second new step highlighted below requires your database (DB) and middle tier (MT) to be patched to a level specified in the ETCC scripts, checkDBpatch.sh and checkMTpatch.sh. While these scripts are easy to run, it’s not always straightforward to resolve patch conflicts in the database or the middle tier. This step is complicated because the list of required patches is continuously changing.

It’s not so much that these new steps are difficult, but they are new and different from all previous upgrades and take some time to understand and implement.

Upgrade to 12.1.3                               Upgrade to 12.2.6

11i  -> 12.1.3                                       12.1.3  -> 12.2.6

Major Functional Changes                  Major Technical Changes

11i pre-upgrade patches                     12.1.3 pre-upgrade patches

Upgrade to 12.1.1 (16 hours)              Upgrade to 12.2.0 (8 hours)

1) Online Enablement Patch –

Requires minimal compliance

for customizations

2) DB and MT to have all patches

specified by ETCC

Upgrade to 12.1.3                                Upgrade to 12.2.6

 

Using ADOP in Release 12.2 – Prepare phase details

Prepare

When the prepare phase is run, files are copied/synchronized from the Run filesystem to the Patch filesystem and code stubs are created in the patch edition of the database. These stubs are instantiated when they are called the first time or the actualize_all parameter is used.

The second file system contains a copy of all the components that make up an application tier file system, including:

    • APPL_TOP – Oracle E-Business Suite code
    • INST_TOP- Instance Configuration Home
    • FMW_HOME – Oracle Weblogic Server Home and Oracle E-Business Suite Domain
    • ORACLE_HOME – Oracle Application Server Home, Forms, Reports
    • IAS_ORACLE_HOME – Oracle OHS Home
    • COMMON_TOP – Oracle E-Business Suite Java code, third-party libraries

During the prepare phase, adop performs the following steps:

  • Checks whether to perform a cleanup.
  • Validates the system configuration to ensure that the system is ready to start an online patching cycle.
  • Checks to see if the database is prepared for online patching:
  • Checks if the database user is edition-enabled.
  • Checks to see if the patch service has been created. adop requires that a special database service exists for the purpose of connecting to the patch edition. Its existence is validated when the prepare phase is run.
  • Checks to see if logon trigger exists and is enabled.
  • Checks the integrity of the database data dictionary, refer to MOS Note 1531121.1, Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2.
  • Checks that the E-Business Suite Technology Codelevel Checker (ETCC) has has been run, to verify that all required patches have been applied to the database.
  • Checks system configuration on each application tier node to make sure that each application tier node is correctly registered, configured, and ready for patching.
  • Checks for the existence of the “Online Patching In Progress” (ADZDPATCH) concurrent program. This program prevents certain predefined concurrent programs from being started, and as such needs to be active while a patching cycle is in progress (that is, while a database patch edition exists).

ADZDPATCH

If the ADZDPATCH program has not yet been requested to run, a request is submitted. If the request status is pending, it may be waiting for an incompatible program to finish. After incompatible programs finish, the ADZDPATCH status will change to running, and it will allow the prepare phase to proceed.

The next stage depends on whether the concurrent managers are running:

If the concurrent managers are all down, the prepare phase continues, with ADZDPATCH entering a status of pending (with the highest priority) until the managers are started.

If the concurrent managers are partially up, but there is no manager defined that can run ADZDPATCH, then the prepare phase will exit with an error.

If the concurrent managers are up, and there is one defined that can run ADZDPATCH, processing will loop until ADZDPATCH changes status from pending to running (that is to say, as noted in Step 2, no incompatible programs are found). The prepare phase then continues.

Note: ADZDPATCH is cancelled when the cutover phase is complete.

  • Invokes the TXK script $AD_TOP/patch/115/bin/txkADOPPreparePhaseSynchronize.pl to synchronize the patches which have been applied to the run APPL_TOP, but not the patch APPL_TOP. The script depends on the adop repository for patches that have been applied on the run APPL_TOP but not the patch APPL_TOP.
  • Checks the database for the existence of a patch edition, and creates one if it does not find one. An example from the log file that shows the prepare checking for the patch edition:

EVENT      Prepare System

 700202704 09:00:10 00:00:00 ad.plsql.ad_zd.create_edition

STATEMENT  SQL: create edition V_20170316_0900

    EVENT      Patch edition not yet created by master node.

    EVENT      Will wait for another minute and retry.

  • Calls the $AD_TOP/patch/115/bin/txkADOPPreparePhaseSanityCheck.pl script again to confirm that the database connection to the patch edition is working.
  • Prepare does not run FS_CLONE. Prepare does run:

                    FsCloneStage

                    FsCloneApply

  • Prepare has the old default sync_mode of “patch”. This can be thought of as the file sync mode, because it synchronizes the files of the Run edition to the Patch edition. The new value for sync_mode that is introduced with AD-TXK Delta 8, is “delta”. Delta mode can use rsync to significantly speed up the prepare phase.

There are two values for sync_mode:

sync_mode = patch, and

sync_mode = delta, (uses rsync) – this is new in AD-TXK delta 8

The benefit besides being much faster, is that sync_mode=deltat also copies all customizations and ignores the adop_sync.drv.

Much faster than the other two methods, this delta synchronization method uses your choice of third-party utility to synchronize the file systems by copying files as applicable from the source directory to the destination directory, optionally ignoring any files and directories you may decide to specify in an exclusion file.

 To use this method, specify the parameter/value pair sync_mode=delta on the adop command line:

$ adop phase=prepare sync_mode=delta

The delta_sync_drv.txt file includes examples for setting up synchronization using rsync on UNIX.

How to Tell If a Patch Has been Applied in R12.2

You can check to see if a patch is really applied by using the AD_PATCH.IS_PATCH_APPLIED pl/sql function.

Usage:
select AD_PATCH.IS_PATCH_APPLIED(\’$release\’,\’$appltop_id\’,\’$patch_no\’,\’$language\’) from dual;

example sql:
SELECT adb.bug_number,ad_patch.is_patch_applied(‘122’, 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (20034256);

or for single app tier installations:
select ad_patch.is_patch_applied(‘R12’,-1,20034256) from dual;

expected results:
EXPLICIT = applied
NOT APPLIED = not applied / aborted

Find TruTek at Collaborate 17!

If you’re considering upgrading to Oracle E-Business Suite Release 12.2, then take a look at all these ways to learn from TruTek about the latest information about the Applications while you’re at Collaborate 17:

I’m Teaching, Presenting, and Paneling at Collaborate 17

R12.2.6 One Day Workshop at Collaborate 17 SUNDAY

If you’re planning to attend Collaborate 17, don’t miss out on my E-Business Suite Release 12.2 Upgrade Workshop: The Whole Nine Yards Workshop. This all day Pre-Conference Workshop on Sunday, April 2nd, will include a free copy of my newest book, the little r12.2.6 upgrade essentials for managers and team members. The workshop is filling up fast, so be sure to register early.

OAUG Customization & Alternatives SIG Panel MONDAY

I’ll be participating on the OAUG Customization & Alternatives SIG panel from 2:45-3:45pm on Monday, April 3rd in Jasmine F.

The Release 12.2.6 Upgrade: Online Patching and Customizations TUESDAY

I’ll be presenting The Release 12.2.6 Upgrade: Online Patching and Customizations on Tuesday at 2:45pm.

Want to score a free copy of my latest book, the little 12.2.6 upgrade essentials for managers and team members? All you have to do is find me (while supplies last)! I’ll bring some copies to Collaborate 17, and my friends at eprentise will have copies at their Booth 1523 in the Exhibit Hall.

Not attending Collaborate 17? You can buy a copy of my new book at: http://www.lulu.com/content/paperback-book/the-little-r1226-upgrade-essentials/20707296