Online patching - table changes

[Sri]

Dear erman,

Pls help with our below concerns with adop R12.2

1)if a table is patched and the definition of an existing column changed, the original column is marked as unused on a subsequent full cleanup.But will the original column gets dropped?

2)Will full cleanup drops old editions?

Thank you for all the support

[Sri]

Our version R12.2.5. with ad,txk delta 7

[ErmanArslansOracleBlog]

1)During a full cleanup, adop sets those columns as and later, adop drops them. (Ref :Oracle Support)

Prior to AD-TXK Delta 8, if a table is patched and the definition of an existing column changed, the original column is marked as unused on a subsequent full cleanup. (Ref : EBS Administration Guide)

Additional info:
making a column unused means logically deleting it..
It is faster than a physical delete (drop).. Unused columns may be dropped later when required.. (using the
ALTER TABLE table_name DROP UNUSED COLUMNS command)

Why did you ask this question?

2)In a full cleanup, adop removes all obsolete code and data from earlier editions.
When you execute a full cleanup, the old editions get deleted.

[satish]

Thanks for the update erman.

1) In our environment,we could see the old columns still existing.so,adop will not drop unused columns? and if want to drop them,do we have to manually run the command

"ALTER TABLE table_name DROP UNUSED COLUMNS command"

In our environment

SQL> desc JA.JAI_TAX_DET_FACTORS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 
 LOCATION_ID                                        NUMBER
 SHIP_FROM_LOCATION_ID                              NUMBER
 SHIP_FROM_STATE                                    VARCHAR2(30)
 SHIP_FROM_COUNTRY                                  VARCHAR2(30)
 SHIP_TO_LOCATION_ID                                NUMBER
 SHIP_TO_STATE                                      VARCHAR2(30)
 SHIP_FROM_STATE#1                                  VARCHAR2(60)-------modified
 SHIP_FROM_COUNTRY#1                                VARCHAR2(60)
 SHIP_TO_STATE#1                                    VARCHAR2(60)
 SHIP_TO_COUNTRY#1                                  VARCHAR2(60)
 BILL_TO_STATE#1                                    VARCHAR2(60)
 BILL_TO_COUNTRY#1                                  VARCHAR2(60)

SQL> select count(SHIP_FROM_STATE) from JA.JAI_TAX_DET_FACTORS;--still exists

COUNT(SHIP_FROM_STATE)
----------------------
                     0

SQL> select count(SHIP_FROM_STATE#1) from JA.JAI_TAX_DET_FACTORS;

COUNT(SHIP_FROM_STATE#1)
------------------------
                      14

Thank you

[ErmanArslansOracleBlog]

1)Please show me the unused columns by querying the dba_unused_col_tabs and sending me the output.
2)Did you execute an adop full cleanup recently?

[satish]

Dear Erman,


We have executed full cleanup after that.
Please find the below output

select * from dba_unused_col_tabs;

OWNER      TABLE_NAME                          COUNT
---------- ------------------------------ ----------
INV        MTL_MATERIAL_TRANSACTIONS               6
JA         JAI_AP_TDS_INV_TAXES                    3
INV        MTL_MATERIAL_TRANSACTIONS_TEMP          2
BOM        BOM_DEPARTMENTS                         1
ONT        OE_AUDIT_ATTR_HISTORY                   4
JA         JAI_OM_LC_HDRS                          9
JA         JAI_PO_REQ_LINE_TAXES                   3
APPLSYS    FND_STATTAB                             5
JA         JAI_OM_OE_BOND_TRXS                     3
AP         AP_CHECKS_ALL                           3
INV        MTL_MVT_STATS_RULE_SETS_TL              2

OWNER      TABLE_NAME                          COUNT
---------- ------------------------------ ----------
JA         JAI_RGM_REGISTRATIONS                   3
JA         JAI_AP_TDS_YEARS                        5
CN         CN_POSTING_DETAILS_SUM_ALL              7
OKC        OKC_DEL_BUS_DOC_COMBXNS                 1
JA         JAI_CMN_ST_MATCH_DTLS                   9
JA         JAI_RCV_TRANSACTIONS                    3
MSC        MSC_BIS_INV_DETAIL                      1
INV        MTL_MOVEMENT_STATISTICS                 1
JA         JAI_AP_MATCH_ERS_T                      3
JA         JAI_OM_LC_AMENDMENTS                    3
CZ         CZ_LOCALIZED_TEXTS                      1

OWNER      TABLE_NAME                          COUNT
---------- ------------------------------ ----------
INV        MTL_ITEM_CATEGORIES                     1
INV        MTL_TXN_REQUEST_LINES                   8
AR         HZ_PARTIES                              8
OSM        AS_SALES_CREDITS_DENORM                 2
MSC        MSC_ITEM_SOURCING                       1
JTF        JTF_RS_ROLES_TL                         1
OSM        AS_LEADS_ALL                            4
ENG        ENG_CHANGE_ORDER_TYPES                  3
ENG        ENG_CHANGE_OBJECTS                      6
AR         AR_PAYMENT_SCHEDULES_ALL                1
JA         JAI_AR_SUP_TAXES                        3

OWNER      TABLE_NAME                          COUNT
---------- ------------------------------ ----------
JA         JAI_PO_QUOT_LINES_T                     3

34 rows selected.

SQL>

[ErmanArslansOracleBlog]

The use of unused columns is the speed.
EBS makes columns unused just for the speed, as the drop operation takes time.
You can't retrieve the unused columns back, so the only use of it is the speed.
Anyways, those columns should be dropped. By using drop unused statement, or  using CTAS to re-create the tables or alter table move to move them without unused columns.
However, we can't do these things directly. Because there is no documentation or record which supports these actions.
As our primary concern is being supported, we need to get the approval of Oracle Support.

I recommend you to open a SR, and get the approval for these actions from Oracle.

Please update this thread once you get the approval.

[Sri]

sure erman,we will raise an SR and update the thread once we hear an update

Also our old editions are not getting dropped

SQL> @ADZDSHOWED.sql
=========================================================================
=                             Editions
=========================================================================

Edition Name    Type     Status   Current?
--------------- -------- -------- --------
ORA$BASE                 ACTIVE
V_20170104_1115          ACTIVE
V_20170105_1144          ACTIVE
V_20171206_1529          ACTIVE
V_20180515_1110          ACTIVE
V_20180530_2027          ACTIVE
V_20190730_1953          ACTIVE
V_20190731_1640 OLD      ACTIVE
V_20190813_1747 RUN      ACTIVE   CURRENT
V_20190814_1335 PATCH    ACTIVE

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[applsupp@erpsupport sql]$

can we connect to any of the above editions(apart from run and patch editions) and can we see if any objects are actual in those editions?

Thanks for all the support you give here

[ErmanArslansOracleBlog]

Those edition may be cleared after running actualize_all.

Consider executing the following action plan and get back to me;

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

[Sri]

Thanks for the update erman. We will plan this weekend.

Is the below concern possible?

can we connect to any of the above editions(apart from run and patch editions) and can we see if any objects are actual in those editions?

Thank you

[ErmanArslansOracleBlog]

I didn't try it before, but maybe..
This is not documented for EBS (so not supported), but you  may try using "alter session set edition=<edition_name>"  to connect to the old editions..

[Sri]

Thanks erman

I tried to connect but errored out with

SQL> alter session set edition=V_20190731_1640;
ERROR:
ORA-38814: ALTER SESSION SET EDITION must be first statement of transaction

Then i have granted use access to that edition and then connected using below steps

SQL> grant use on edition V_20190731_1640 to public;

Grant succeeded.

SQL> alter session set edition=V_20190731_1640;

Session altered.

SQL> select count(*) from apps.ad_objects where actual='Y';

  COUNT(*)
----------
    280545


I have many actual objects on that edition.Is this the reason that my old editions are not getting dropped?

Thank you

[ErmanArslansOracleBlog]

No. I don't think so.
When you drop an edition, all the actual editionable objects  in that edition are also dropped.
So there is no barrier.

Please implement the action plan that I provided you earlier, and get back to me.

Bytheway, do you have any stub objects in your current/run edition?
Check DBA_OBJECTS. AD_OBJECTS , check for "stub" objects (the object definition was inherited from a previous edition) and get back to me.

[Sri]

Thanks for the update

Will below query works for checking count of stub objects in current edition?

select count(*) from apps.ad_objects where actual='N'

Thank you for the suppport

[satish]

Dear Erman,

My run edition is V_20190813_1747 and please find the stub objects below

SQL> @$AD_TOP/sql/ADZDSHOWED.sql
=========================================================================
=                             Editions
=========================================================================

Edition Name    Type     Status   Current?
--------------- -------- -------- --------
ORA$BASE                 ACTIVE
V_20170104_1115          ACTIVE
V_20170105_1144          ACTIVE
V_20171206_1529          ACTIVE
V_20180515_1110          ACTIVE
V_20180530_2027          ACTIVE
V_20190730_1953          ACTIVE
V_20190731_1640 OLD      ACTIVE
V_20190813_1747 RUN      ACTIVE   CURRENT
V_20190814_1335 PATCH    ACTIVE


SQL> @$AD_TOP/sql/ADZDSHOWOBJS.sql

=                      Editioned Objects Per Edition
=========================================================================

EDITION_NAME       A_VALID  A_INVALID    A_TOTAL    S_VALID  S_INVALID    S_TOTAL      TOTAL
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORA$BASE            113397          0     113397          0          0          0     113397
V_20170104_1115          2          0          2     113397          0     113397     113399
V_20170105_1144       4767          0       4767     113399          0     113399     118166
V_20171206_1529      13589          0      13589     118166          0     118166     131755
V_20180515_1110       1013          0       1013     131755          0     131755     132768
V_20180530_2027      80275          8      80283     132766          0     132766     213049
V_20190730_1953       1983          2       1985     213039         10     213049     215034
V_20190731_1640        645          0        645     215022         12     215034     215679
V_20190813_1747         54          5         59     215546        111     215657     215716--->RUN
V_20190814_1335          1          0          1     215570        145     215715     215716

Note :
(A_) - Actual Objects
(S_) - Stub Objects



SQL> select count(*) from ad_objects where actual='N';

  COUNT(*)
----------
    216903

Thank you

[ErmanArslansOracleBlog]

Hello Sri,

It seems you have stub objects in your run edition.
So when you run actualize_all as I recommended earlier, these objects will be acutalized in the latest edition.
Normally, you should not be able to drop an EBR edition, which contains any actual editionable objects which are inherited by a child edition.
And probably that's things (by design) prevents your old edition to be dropped by an adop full cleanup.
So as I recommended, actualize them in the latest edition!

Execute the following action plan and get back to me;

$ adop phase=prepare
$ adop phase=actualize_all
$ adop phase=finalize finalize_mode=full
$ adop phase=cutover
$ adop phase=cleanup cleanup_mode=full

[Sri]

Got it erman.Thank you

[Sri]

https://www.slideshare.net/mobile/Berryclemens/con8435-parvate-con8435ebstechnicalupgradebestpractices-121-or-122

On Fri, Aug 23, 2019, 19:07 Sri [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

Got it erman.Thank you

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Online-patching-table-changes-tp7592p7612.html

To unsubscribe from Online patching - table changes, click here.
NAML