SQL Generating SQL to Remove EBS Online Patching Codelevel Checker Violations

The EBS Online Patching Database Compliance Checker, ADZDDBCC.sql, currently has 50 sections, section 1 through section 47, with sections 5.0 and 5.1, and sections 91 and 92. New sections are added as Oracle finds violations that can be checked reliably. There are some violations that are very difficult to check, such as user defined types, because there are almost unlimited variations of user defined types.

Oracle states in the ADZDDBCC.lst:

“Please do not attempt to correct violations in objects owned by Oracle.

P1 violations must be fixed before using the system or object.

P2 violations might not pose an immediate problem, but may cause later    issues if the affected objects are changed in a future online patch.

P3 violations are minor issues that can be deferred or ignored”

There are two different groups of violations, priority and compliance: P1, P2 and P3 priority violations and Minimal, Full and Internal compliance violations. You should fix all P1 and P2 compliance violations and all minimal priority violations. I usually fix both the minimal and full compliance violations, because in the future, as your development team better understands how to implement custom code, they may chose to create online patches for custom code.

“Minimal Compliance [minimal]

These checks represent the minimum requirement for correct operation of E-Business Suite Release 12.2.  Do not attempt to operate the system if there are P1 minimal compliance violations.  Custom code should pass the minimal compliance checks before being used in a Release 12.2 system.

Full Compliance [full]

These checks indicate whether an object can be patched using Online Patching.  Objects which do not meet full compliance may have limitations in how they can be patched, or may need to be patched using downtime patching.  Full compliance also requires that all minimal compliance checks are passed.  Custom code that will only be patched using downtime patching does not need to meet the full compliance level.”

“This report also implements a number of internal checks which verify that the upgrade and Online Patching Enablement process executed correctly. Violations of internal checks indicate upgrade failure and should be reviewed with support if the cause is not understood.”

The following is the header from section-15 from the ADZDDBCC.lst output from AADZDDBCC.sql. Even though this is a P3 priority violation, I usually fix this because of “clutter and confusion”.

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

DOC>  SECTION-15  [minimal]

DOC>  **********************************************************************

DOC>  “Synonym must point to an object.”

DOC>

DOC>   – P3: broken synonyms cause clutter and confusion.

DOC>   – Fix: Correct or drop these synonyms.

DOC>#

OWNER       SYNONYM_NAME           TABLE_OWNER      TABLE_NAME

However, there can be thousands of these broken synonyms. Most violations can be fixed with minor changes. However, because there are so many of these violations I chose to write SQL generating SQL that can be run to fix all the violations in one script. The following SQL script is one I created to fix this section:

select ‘drop synonym ‘||syn.owner||’.’||syn.synonym_name|| ‘;’

from  dba_synonyms syn

where syn.table_owner in

        ( select oracle_username from fnd_oracle_userid

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

  and not exists

        ( select obj.object_name

          from   dba_objects obj

          where  obj.owner       = syn.table_owner

            and  obj.object_name = syn.table_name )

  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 = syn.owner

            and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or

                 (aoo.object_name = syn.table_name   and aoo.object_type = ‘TABLE’)) )

order by 1, 2

I also create a script that will create all the synonyms, just in case:

select ‘create synonym ‘ ||syn.owner|| ‘.’ ||syn.synonym_name|| ‘ for  ‘ ||syn.table_owner|| ‘.’ ||syn.table_name || ‘;’

from  dba_synonyms syn

where syn.table_owner in

        ( select oracle_username from fnd_oracle_userid

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

  and not exists

        ( select obj.object_name

          from   dba_objects obj

          where  obj.owner       = syn.table_owner

            and  obj.object_name = syn.table_name )

  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 = syn.owner

            and ((aoo.object_name = syn.synonym_name and aoo.object_type = ‘SYNONYM’) or

                 (aoo.object_name = syn.table_name   and aoo.object_type = ‘TABLE’)) )

order by syn.owner, syn.synonym_name;

Section-26 contains objects that are mostly Oracle seeded  objects, so no action is necessary. However, some objects are owned by custom schemas and these typically need to be fixed by your development team.

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

SECTION-26  [minimal]

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

“Query/DML statements must access tables via the APPS table synonym.”

– P2: These objects may operate incorrectly after the referenced table has been patched.

Fix:  Change the object to reference tables via the APPS table synonym.

Purging Workflow – Updated version

Purging Workflow Item Type and Notifications

In the past, I’ve blogged about how to set workflow item_types so they can be purged using scripts in $FND_TOP. One such script is wf_purge.total. Below, I give an example of using wf_purge.total to item_types where the PARENT_ITEM_TYPE is null. I also write about two methods to purge notifications, wf_purge.notifications and manually updating the wf_notifications table and set status=’CLOSED’, mail_status=’SENT’, and END_DATE=sysdate-1.

This blog also  introduces using the WF_ITEM_ACTIVITY_STATUS.Create_Status procedure to set the status to ‘COMPLETE’ from ‘NOTIFIED’. This can be used to change the status.

— When STATUS=’NOTIFIED’ it can’t be purged. Use API to set the STATUS to ‘COMPLETE’

 

BEGIN

WF_ITEM_ACTIVITY_STATUS.Create_Status(itemtype  => ‘WFERROR’,

itemkey=>’10037187′,

actid=> 255020,

status=> ‘COMPLETE’,

result=>null,

beginning=> null,

ending=> sysdate,

suspended=> FALSE,

newStatus=> FALSE);

END;

 

Queries used to find WF_ITEMS that can’t be purged. It turns out the biggest offender was FAFLEXWF (41 million rows), because of a custom portion of the workflow for cash management had an error.

 

The next item_type with the most issues was WFERROR. This is very common. The problem with resolving WFERROR item_types, is you need to know the item_key, based on some criteria.

 

First, find the item_types that have issues, in this case the PARENT_ITEM_TYPE is null:

 

select count(*) as cnt, item_type from apps.wf_items where  PARENT_ITEM_TYPE is null group by item_type

 

Or, use the following to order by descending count:

select count(1) from apps.wf_items where item_type=’WFERROR’

group by PARENT_ITEM_TYPE order by 2 desc

 

Second, find the item_keys associated with the issue:

 

select item_type, item_key from apps.wf_items where item_type=’WFERROR’ and PARENT_ITEM_TYPE is null

 

Third, purge WFERROR item types where the parent_item_type is null using wf_purge.total, by looping through all the item_keys:

 

BEGIN

FOR c1_rec IN (select item_type, item_key from apps.wf_items where item_type=’WFERROR’and PARENT_ITEM_TYPE is null)

LOOP

apps.wf_purge.total(c1_rec.item_type, c1_rec.item_key)   ;

END LOOP;

END;

 

Helpful Views

select * from all_views where view_name like ‘%WF%ITEM%’

select * from apps.WF_ITEMS_V where item_type=’FAFLEXWF’

 

WF_ITEM_ATTRIBUTES_VL

select * from apps.WF_ITEM_TYPES_VL where

name=’WFERROR’

WF_ITEM_ACTIVITY_STATUSES_V

 

wf_purge.notifications

In this example, we will purge notifications, in this case, CREATEPO and passing it yesterday’s date as the END_DATE:

BEGIN

wf_purge.notifications(‘CREATEPO’, sysdate-1);

END;

For example, we had nine wf_notifications where the message_type=’CREATEPO’ and the status was not ‘CLOSED’ or the mail_status was not ‘SENT’ or there was no end_date.

select * from apps.wf_notifications where message_type=’CREATEPO’

update apps.wf_notifications set status=’CLOSED’, mail_status=’SENT’, END_DATE=sysdate-1 where message_type=’CREATEPO’

Major Oracle E-business suite upgrade coming in 2019. Or maybe 2020

If you can’t wait, Big Red says its new SaaS apps will behave just like software modules

Oracle is working on a big upgrade to its e-business suite in 2019. Or maybe 2020.

That’s what senior veep for application development Cliff Godwin told the Red Rock Oracle Leadership Forum* in Sydney, Australia, today.

The suite is currently on version 12.2.6 and Godwin said Oracle plans just-about-annual double-point releases for the next couple of years to bring it to to version 12.2.7 and 12.2.8. The release after that will be 12.3 and while Godwin couldn’t say what would be in it, he did say that Oracle has already guaranteed support for it until the year 2030 and beyond.

Big Red’s made that pledge to ensure users don’t feel their on-premises applications are in peril, or that they’re being herded to the cloud. Godwin said Oracle knows it has plenty of customers, many of them in military or other sensitive government fields, that are likely years or decades away from being comfortable in the public cloud.

The roadmap to version 12.3 and long support plan are designed to give them comfort that the suite has a future and that “if you are contemplating moving to cloud, do it for merits of the business not because you are afraid the suite is going to expire out from under you.”

Oracle’s double-point updates, however, don’t add a lot of functionality to the suite and the company’s current plans see most of its innovation focused on software-as-a-service (SaaS). Godwin said on-premises users will be able to access new functions by piping in those new SaaS offerings.

“They will work as if we built them as new modules,” he said.

Among the new SaaS offerings Oracle is working on is predictive analytics it will bake into its asset management modules. A new in-memory cost management cloud service replaces an on-premises product that Godwin said was “very computationally intensive” and required users to acquire an Oracle Engineered system. The new SaaS version does away with the need for the tin, but gives you access to an evolving piece of software.

Godwin said that ongoing work on the suite will include more emphasis on mobile, so that core functions can be poured into apps from Oracle or cooked up by customers. He added that the company wants to build more migration tools to help users of the suite move into the cloud, but not as a lift-and-shift. Instead he said Oracle hopes users can move test and development or disaster recovery rigs into the cloud, while retaining integration with on-premises production systems

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;

/

 

 

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.

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

TruTek’s Newest R12.2.6 Book is Here: the little r12.2.6 upgrade essentials for managers and team members

Putting together the right team to tackle the upgrade, and understanding the issues that the team needs to consider to be successful, can be quite a challenge. the little r12.2.6 upgrade essentials for managers and team members describes the big picture of what you need to consider before tackling the Release 12.2.6 E-Business Suite upgrade. Based on TruTek’s popular R12.1 to R12.2 Technical Upgrade classes, this book describes what managers, functional, and technical team members need to know to prepare to upgrade from Release 12.1 to Release 12.2 of Oracle’s E-Business Suite of Applications.

**You can order Mike Swing’s newest book, and his other Release 12.1.3 and 12.2 books now.**

Fast Backups and Restores are Critical during EBS Upgrades, Testing and Development

The process of upgrading an EBS system is one requiring dozens or hundreds of steps. Think of it like climbing a wall. Each handhold or foothold is analogous to one of the steps in the upgrade.

What happens if you miss a handhold or foothold?

If you are close to the ground, then the fall isn’t dangerous, and you can get restarted easily.

But what happens if you’re 25 or 35 feet off the ground?

A fall from that height can result in serious injury, unless you are strapped to a climbing rope to catch your fall.

And that is role that backups play in any lengthy process with many discrete steps, such as an EBS upgrade. Performing a backup of the systems you’re upgrading every half-dozen or dozen steps allows you to restore to the most recent backup and resume work while having only lost a relatively small amount of work.

But think of the costs of taking a full backup of the systems you’re upgrading. Let’s assume that the EBS database is 1 TB in size, the dbTier is 4 GB, and the appsTier is another 4 GB. All told each backup takes up over a TB of backup media and takes 2 hours to perform, start to finish. So, for an upgrade process of 100 steps, and performing a backup every dozen steps, means performing 8-9 backups, at 2 hours apiece, totaling 16-18 hours alone! Not only do the backups represent a significant portion of the total number of steps to perform, but those steps also consume a significant portion of the total elapsed time of the entire upgrade process.

So, in the event of a mistake or mishap, it will take about 2 hours to restore to the previous backup, and then the intervening steps have to be replayed, which might take another couple of painstaking hours.

Because of this high cost of failure, believe it that each step will be double-checked and triple-checked, perhaps by a second or third person, before it is committed or the ENTER key is pressed.

No wonder upgrading EBS is so exhausting and expensive!

The fastest operation is the one you never perform

What if you never had to perform all those backups, but you could still recover your entire EBS systems stack to any point-in-time as if you had?

This is what it is like to use data virtualization technology from Delphix. You start by linking a set of “source” EBS components into Delphix, and these sources are stored in compressed format within the Delphix appliance. Following that, these sources can produce virtual copies taking up very little storage in a matter of minutes. A Delphix administrator can provision virtual copies of the entire EBS systems stack into a single Delphix JetStream container for use by an individual or a team. The components of the container, which include the EBS database, the EBS dbTier, and the EBS appsTier, become the environment upon which the upgrade team is working.

Delphix captures all of the changes made to the EBS database, the EBS dbTier, and the EBS appsTier, which is called a timeflow. Additionally, Delphix JetStream containers allow users to create bookmarks, or named points-in-time, within the timeflow to speed up the location of a significant point-in-time, and easily restore to it.

So each developer or team can have their own private copy of the entire EBS systems stack. As the developer is stepping through each step of the upgrade, they can create a bookmark, which takes only seconds, after every couple steps. If they make a mistake or a mishap occurs, then they can rewind the entire JetStream container, consisting of the entire EBS systems stack, back to the desired bookmark in minutes, and then resume from there.

Let’s step through this, using the following example EBS system stack already linked and ready to use within a Delphix appliance…

An example EBS system stack in Delphix

So here we see a full E-Business Suites R12.1 system stack linked into Delphix. In the screenshot below, you can see three dSource objects in the left-hand navigation bar, under the Sources database group or folder. These three dSource objects are named…

  1. EBS R12.1 appsTier
  2. EBS R12.1 dbTechStack
  3. EBSDB

The first two items are vFiles, containing the file system sub-directories representing the E-Business Suites R12.1 applications tier and database tier, respectively. The last item is a virtual database or VDB, containing the Oracle database for the E-Business Suites R12.1 system. Each are indicated as dSources by the circular black dSource icon with the letters “dS” within (indicated below by the red arrows)…

Delphix1

Registering EBS dSources as JetStream templates

The three dSources have been encapsulated within the Delphix JetStream user-interface within a templates, as shown by the JetStream icons showing a tiny hand holding out a tiny cylinder representing a datasource (indicated below by the red arrows) just to the right of the dSource icons…

Delphix2

Within the JetStream user-interface itself, we see that all three dSources are encapsulated within one JetStream template, with the three dSources comprising the three datasources for the template…

Delphix3 

Encapsulating EBS VDBs as JetStream containers

Once dSources are encapsulated as a JetStream template, then we can encapsulate VDBs and vFiles created from those dSources as JetStream containers. Here, we see the database group or folder called Targets circled in the left-hand navigation bar, and the blue icons for VDBs and vFiles with the letter “V” within are indicated by red arrow, while the darker icons for JetStream Containers are indicated by the green arrow…

Delphix4

Working with JetStream containers for EBS

With an entire EBS system stack under the control of Delphix JetStream, we can begin the lengthy process of upgrading EBS. Before we begin, we should create a JetStream bookmark called Upgrade Step 0 to indicate the starting point of the upgrade…

Delphix5

Then, after performing the first five steps of the upgrade in the EBS system stack, come back to the JetStream user-interface and create a JetStream bookmark named Upgrade Step 5, as shown below…

Delphix6

After every couple steps of the upgrade, or prior to any lengthy step, come back to the Delphix JetStream user-interface and create a bookmark named appropriate for the step in the upgrade process, as shown below…

Delphix7

Performing a rewind in a JetStream container for EBS

So we’re cruising along, performing productive steps in the EBS upgrade process, and not worrying about making backups.

WHOOPS! At upgrade step 59, we made a serious mistake!

If we had been making backups every 12 steps or so, then the most recent backup may have been Upgrade Step 48, which was 11 steps ago. Besides the multiple hours it may take to restore the backup in the database, the dbTier, and the appsTier, we are going to have to perform 11 steps over again.

In contrast, using Delphix JetStream, we have better choices. From the Delphix JetStream user-interface, we can restore to any point-in-time on the timeflow, whether it is one of the bookmarks we previously created at a known step in the upgrade process, or to any point-in-time location on the timeflow line, which may or may be located in the middle of one the upgrade steps, and thus not a viable point from which to restart.

So, because we want to restart the upgrade from a known and viable step in the process, we’re going to restore the entire EBS systems stack consistently back to the JetStream bookmark Upgrade Step 55

Delphix8

So, first let’s click on the JetStream bookmark named Upgrade Step 55 and then click on the icon for the Restore operation to start the rewind…

Delphix9

After being prompted Are You Sure and then confirming Yes I Am, then the Restore process starts to create a brand new timeflow for all three datasources in the container…

Delphix10

Please notice that the old timeflow is still visible and accessible to the left in the JetStream user-interface, even with the new timeflow being restored to the right.

Finally, the restore operation completes, and now all three parts of the EBS systems stack (i.e. appsTier, dbTier, and database) have been restored to Upgrade Step 55 in about 15 minutes elapsed time, a fraction of the 2 hours it would have taken to restore from backups.

Delphix11

Now we can resume the EBS upgrade process with barely any time lost!

Summary of benefits

So a few things to bear in mind from this example when using Delphix Jetstream…

  1. Once a JetStream container has been provisioned by the Delphix administrators (usually a role occupied by database administrators or DBAs), all the operations shown above are performed by JetStream data users, who are usually the developers performing the upgrade themselves
    • No need to coordinate backups or restores between teams == less time wasted
  2. Using Delphix removed the need to perform backups, because all changes across the timeflow of all three components of the EBS systems stack (i.e. appsTier, dbTechStack, and database) are recorded automatically within Delphix
    • No backups == less storage consumed == less time wasted
  3. Using Delphix removed the need to restore from backups, because the timeflow can be restored to any previous point-in-time in a fraction of the time it takes to perform a restore from backup
    • No restores == less time wasted

It is difficult to imagine not performing EBS upgrades using the methods we’ve used for decades, but the that time is here. Data virtualization, like server virtualization, is fast becoming the new norm, and it is changing everything for the better.