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

/

 

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.

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

 

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.**

Attending Collaborate and Have an Oracle EBS Upgrade or Customization Project?

Delphix_Primary_CMYK

Check out Delphix at Booth #1613 and see how you can provision full-stake EBS environments in just minutes, automatically secure sensitive information, and lower TCO with space-efficient virtual data. You are invited to join Delphix for the following special events:

April 12th, Tuesday (at the Delphix Booth #1613)

  • 1:00 – 1:15        Delphix Demo
  • 1:15 – 2:00        How many instances do you need for an EBS Upgrade?Q&A with Mike Swing
  • 6:00 – 7:30        Happy Hour

April 13th, Wednesday (at the Delphix Booth #1613)

  • 2:00 – 2:45        Book signing with Mike Swing

Sessions you may be interested in:

Sunday, April 10:

  • Mike Swing’s Pre-Conference (added cost) 1 Day Workshop: E-Business Suite Release 12.2 Upgrade Workshop: The Whole Nine Yards, 9am-4pm, Breakers B
  • OAUG Archive and Purge SIG Meeting at 3:30pm

Monday, April 11:

  • Tim Gorman’s Linux/UNIX Tools for the Oracle DBA on April 11th at 9:15am, Palm A
  • Tim Gorman’s Accelerating DevOps Using Data Virtualization at 2:00pm, Jasmine B

Wednesday, April 13:

  • 11:45 –12:45     Oak Table World at Collaborate 16. Mandalay Bay Ballroom I
    • 11:45 – 11:55am – Internet of Things 101 – Alex Gorbachev, Pythian
    • 12:00 – 12:10pm – How Oracle Data Recovery Mechanisms Complicate Data Security, and What To Do About It, Tim Gorman, Delphix
    • 12:15: – 12:25pm – Some Useful Oracle Diagnostic Tricks – Tanel Poder, Gluent, Inc.
    • 12:30 – 12:40pm – Challenges and Solutions Masking Data – Kyle Hailey, Delphix
    • 12:45 – 12:55pm – Everything Exadata – Dan Norris, Oracle Corporation

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

littler1225upgradeessentialsguideFRONTcover 03-29-16wBorderPutting 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.5 upgrade essentials for managers and team members_ describes the big picture of what you need to consider before tackling the Release 12.2.5 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](http://www.lulu.com/spotlight/trutek) now.**

High Level Upgrade Plan to 12.2.5 with Database Migration to Linux

Aside

High Level Upgrade Plan to 12.2.5 with a Database Migration to Linux

        TASK NAME                                                             Duration     

  1. Procure Linux Hardware                                                     12d
  2. Install and configure OEL for EBS for DB                            2w
  3. Gather custom code                                                             1w
  4. Migrate Solaris database to Linux                                       1mo
  5. Develop Test Scripts                                                            1w
  6. Test Linux 64 bit DB server with Solaris Apps Tier              1w
  7. Freeze Patching of PROD                                                    0d
  8. Production Cutover to Linux Database servers                    2d
  9. Configure Linux Upgrade DB server – Install OEL                1w
  10. Clone Linux DB servers to Linux Upgrade server                 1w
  11. Configure Linux Apps Upgrade server- Install OEL              1w
  12. Install 12.2.0 on Linux 64 bit apps tier                                  1w
  13. Upgrade DB to 12.1.0.2 on Upgrade Server                        1w
  14. Test 12.1.0.2 DB with Solaris Apps Tier                               1w
  15. Upgrade PROD DB to 12.1.0.2                                             2d
  16. Clone PROD DB to Upgrade DB                                          1w
  17. Upgrade to 12.2.5                                                                 2w
  18. Develop and Apply Customizations to 12.2.5                       3w
  19. Test 12.2.5                                                                            3w
  20. Upgrade Pass #2 to 12.2.5                                                   2w
  21. Apply Customizations                                                           1w
  22. Test 12.2.5                                                                            1w
  23. Performance Upgrade to 12.2.5                                           1w
  24. Apply Customizations                                                           1w
  25. Test 12.2.5                                                                            1w
  26. Go Live                                                                                  4d

 

Installing startCD51 and Staging Release 12.2.5, Including 12.1.0.2 of the Database

From Steven Chan’s blog, https://blogs.oracle.com/stevenChan/entry/ebs_12_2_5_available:

“Instructions for downloading and applying this release update pack (RUP) for the EBS 12.2 codeline can be found here:

After going to edelivery.oracle.com:

edelivery-download3

1225 download files1

Notice the Oracle Database 12.1.0.2 files with most of the ETCC patches already applied:

1225 download files2

And, the Oracle Web Tier 11.1.1.9, software is already included. This makes updating to the latest Oracle Fusion Middleware much easier:

1225 download files3

After downloading all the files, running a md5sum to make sure they all downloaded correctly, I staged the files according to: R12.2: How To Create the Stage In Preparation For Installation (Doc ID 1596433.1)

I followed the notes that are included below, but had some additional fixes, in order for the install to work correctly:
1) build the stage according to note 1596433.1
2) chmod -R 755 /home/stage
3) xhost +
4) Clean out /etc/oraInventory/ContentsXML/inventory.xml
5) fix /etc/oraInst.loc follow note 1292347.1
6) to fix the database installer, rapidwiz didn’t show the actual error.
I went to /home/stage/TechInstallMedia/databas/database and ran
runInstaller to get the errors and to run the fixup script. Then
rapidwiz would work.

Notes:
R12.2: How To Create the Stage In Preparation For Installation (Doc ID
1596433.1)
MD5 Checksums for R12.2 Rapid Install Media (Doc ID 1505510.1)
[INS-10002] Inventory Initialization Failed or [INS-10102] Installer
Initialization Failed during Installation (Doc ID 1292347.1)

Notes:
R12.2: How To Create the Stage In Preparation For Installation (Doc
ID 1596433.1)
MD5 Checksums for R12.2 Rapid Install Media (Doc ID 1505510.1)
[INS-10002] Inventory Initialization Failed or [INS-10102] Installer
Initialization Failed during Installation (Doc ID 1292347.1)