CTXSYS and PORTAL30 invalids after upgrade

Benette asked me about CTXSYS and PORTAL30 invalids which had resulted after upgrade. I asked him for a list and this is what he gave me:

APPS Package Body AST_OFL_TERR_MISS_ASGN_REPORT
APPS Package Body FND_OID_DIAG
CTXSYS Procedure WWSBR_CORNER_CTX_202
CTXSYS Procedure WWSBR_DOC_CTX_202
CTXSYS Procedure WWSBR_PERSP_CTX_202
CTXSYS Procedure WWSBR_THING_CTX_202
CTXSYS Procedure WWSBR_TOPIC_CTX_202
PORTAL30 Package WWV_MONITOR_WEBVIEW
PORTAL30 Package WWV_QBE_TABLE
PORTAL30 Package WWV_RENDER_QBE
PORTAL30 Package WWV_SYS_RENDER_QBE
PORTAL30 Package Body DBE_TABLE_ACTION
PORTAL30 Package Body WWV_AK
PORTAL30 Package Body WWV_COMPONENT_CONTROL
PORTAL30 Package Body WWV_MENU_BUILD
PORTAL30 Package Body WWV_MONITOR_DATABASE
PORTAL30 Package Body WWV_MONITOR_WEBVIEW
PORTAL30 Package Body WWV_QBE
PORTAL30 Package Body WWV_QBE_PROC
PORTAL30 Package Body WWV_QBE_TABLE
PORTAL30 Package Body WWV_QBE_UTILITY
PORTAL30 Package Body WWV_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_PAGINATE
PORTAL30 Package Body WWV_SYS_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_QBEFORM
PUBLIC Synonym DEBUG_CARTX

The simple answer to all the invalids above is: "Drop them, they are not needed." The Metalink Notes which advise this are:

444348.1 for Portal30 invalids
458370.1 for CTXSYS invalids which are calling the PORTAL30 invalids
352808.1 advises dropping AST_OFL_TERR_MISS_ASGN_REPORT as it is no longer used.
380480.1, 386746.1 and 444348.1 advise dropping DEBUG_CARTX
360285.1 advises dropping FND_OID_DIAG if you are not using OID/SSO.

ORA-7445 in alert log when you open Project Quick Entry form

In one of the 11.5.10.2 instances which was upgraded to 10.2.0.3 Database, the following behavior was observed:

Whenever Project Quick Entry form was accessed, it gave the error:

FRM-40735: ON-ERROR trigger raised unhandled exception ORA-01001.

APP-FND-01564: error 1000 in audfrm

Cause: audfm failed due to ORA-01000 maximum open cursors exceeded
The SQL statement being executed at the time of the error was &SQLSTMT and was executed from the file &ERRFILE.

In alert log the following ORA-7445 was logged:

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [drurnew()+184] [SIGSEGV] [Address not mapped to object] [0x000000040] [] []
Current SQL statement for this session:
INSERT INTO PA_PROJECT_CTX_SEARCH (PROJECT_ID, CTX_DESCRIPTION, PROGRAM_REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, CREATED
_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) VALUES
(:B2 , :B1 , NULL, NULL, NULL, NULL, FND_GLOBAL.USER_ID, SYSDATE, FND_GLOBAL.US
ER_ID, SYSDATE, FND_GLOBAL.LOGIN_ID)
----- PL/SQL Call Stack -----
object line object
handle number name
3ec258df8 5 CTXSYS.TEXTINDEXMETHODS
3ec258df8 625 CTXSYS.TEXTINDEXMETHODS
----- Call Stack Trace -----

We logged an SR for this issue and got this response:

CAUSE DETERMINATION
===================
This is unpublished Bug 5587976.

The error is due to a problem with the intermedia functionality.

CAUSE JUSTIFICATION
===================
BUG 5587976 : APPSST: ORA-07445: EXCEPTION ENCOUNTERED: CORE DUMP [DRURNEW()+129] [SIGSEGV]

PROPOSED SOLUTION(S)
====================
Download and install Patch 5587976

Bug has been fixed in 10.2.0.4 patchset

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
SURE 461767.1 : ORA-7445 [Drurnew()] Encountered During Applications Upgrade

SOLUTION / ACTION PLAN
======================
-- To implement the solution, please execute the following steps::
Download and install Patch 5587976

Even after applying this patch the issue was occuring. The DBA team ran gather schema statistics on PA schema from backend.

execute fnd_stats.gather_schema_stats('PA');

After this the issue was resolved. This issue was not occuring in dev/test instances which did not have this patch. It is possible that the error was coming solely due to the absence of statistics.

Drop invalid FPA objects after 11.5.10 upgrade if you are not using Project Portfolio Analysis

After applying maintenance pack patch 3480000 to an 11.5.9 instance, we had several invalid FPA views. Since Project Portfolio Analysis(FPA) is a new product introduced in 11.5.10 and we are not using it currently, as advised in metalink note 400964.1, we dropped the invalid FPA views.

After applying PJ_PF.M RUP4 patch 5644830, the following FPA objects are invalid:

SQL> select object_type,object_name from dba_objects
where object_name like 'FPA%'
and owner='APPS'
and status='INVALID' 2 3 4
5 /

OBJECT_TYPE OBJECT_NAME
-----------------------------------------
PACKAGE BODY FPA_MAIN_PROCESS_PVT
PACKAGE BODY FPA_PLANNINGCYCLE_PVT
PACKAGE FPA_PORTFOLIO_PROJECT_SETS_PVT
PACKAGE BODY FPA_PORTFOLIO_PROJECT_SETS_PVT
PACKAGE BODY FPA_PORTFOLIO_PVT
PACKAGE BODY FPA_PROCESS_PVT
PACKAGE BODY FPA_PROJECT_PVT
PACKAGE BODY FPA_SCENARIO_PVT
PACKAGE BODY FPA_SCORECARDS_PVT
PACKAGE BODY FPA_UTILS_PVT
PACKAGE BODY FPA_VALIDATION_PROCESS_PVT

11 rows selected.

I logged an SR with Oracle to find out how to fix them. This is what they said:

ACTION PLAN
============
Hi Vikram,
Are you using Oracle Project Portfollio Analysis? If not, then you can ignore the or drop the invalid objects (see Note 436956.1).

Thanks,
Glade


Hi Glade,

We are not using Project Portfolio Analysis. We are only interested in getting the invalids fixed. If we can drop them, it would bring down the invalid count and meet our requirement of having
zero invalids after this upgrade. However I am unable to access metalink note 436956.1. Maybe this is not yet released to general public. Please paste the contents of note 436956.1 for our reference.

- Vikram


11-DEC-07 21:29:36 GMT


UPDATE
======
HI Vikram,
Here is the Note:

Article-ID: Note 436956.1
Title: FPA Invalid Objects After Applying Projects RUP4 Patch 5644830

Applies to:
Oracle Project Portfolio Analysis - Version: 11.5.10.2
Information in this document applies to any platform.

Goal

After applying Oracle Projects Family Pack M (11i.PJ_PF.M) Rollup 4 have three following FPA invalid objects :


Package Body FPA_MAIN_PROCESS_PVT
Package FPA_SCENARIO_PVT
Package Body FPA_SCENARIO_PVT FPA

These packages did not exist prior to applying these patches
Do not have Portfolio Analysis installed

Could these FPA invalid objects be dropped?

Solution
The FPA Invalid Objects can be Ignored or Dropped if Portfolio Analysis Product is not used.

Thanks,
Glade


11-DEC-07 21:29:47 GMT

Hi Glade,

The Note refers to only 3 objects:

Package Body FPA_MAIN_PROCESS_PVT
Package FPA_SCENARIO_PVT
Package Body FPA_SCENARIO_PVT FPA

But we have 11 invalids, is it ok to drop all 11 ?

- Vikram

ERROR: APPLYING PORDLVMR.XML TO THE WRONG RELEASE

While upgrading 11.5.9 to 11.5.10.2 in one of our environments, we got these errors:

Error: Applying PORDLVMR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVMR.xml,
v115.20
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVMR.xml, v1.1

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Error: Applying PORSPAMT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPAMT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPAMT.xml, v1.1

Error: Applying PORSPGDS.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPGDS.xml,
v115.16
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPGDS.xml, v1.1

Error: Applying PORSPRAT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPRAT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPRAT.xml, v1.1

### Steps to Reproduce ###
Apply patch 3480000 on an 11.5.9 CU2 instance, the following error results:

Error: Applying PORDLVMR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVMR.xml,
v115.20
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVMR.xml, v1.1

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Error: Applying PORSPAMT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPAMT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPAMT.xml, v1.1

Error: Applying PORSPGDS.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPGDS.xml,
v115.16
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPGDS.xml, v1.1

Error: Applying PORSPRAT.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORSPRAT.xml,
v115.15
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORSPRAT.xml, v1.1

I raised an SR with Oracle to find out what needs to be done. This is what they said:

The XML files referenced in the errors are obsoleted in 11.5.10 / Supply Chain Management Family Pack J and can be ignored.

PORDLVMR.xml
PORDLVSR.xml
PORSPAMT.xml
PORSPGDS.xml
PORSPRAT.xml

These files are related to iProcurement requisition creation process in 11.5.9 and lower; these have been replaced by OA Framework pages in 11.5.10 code level.

Thanks,
Anthony
Global Customer Services


Hi Anthony,

Does that mean that we are already on the most current version and don't need to copy the files manually from patch 3480000 ? The versions are strange (115.34 vs 1.1) but the dates with version is higher for on-site files:

Error: Applying PORDLVSR.xml to the wrong release:
Patch : /stage/jul_2008/gpshpc76/11510_upg/merge/target/icx/html/PORDLVSR.xml,
v115.34
On-Site: /gpshpc76/erpapp/appl/icx/11.5.0/html/PORDLVSR.xml, v1.1

Please clarify that the onsite files in $ICX_TOP/html are higher version 1.1 and we do not need to do anything with v115.34 files

- Vikram


12-DEC-07 00:55:48 GMT

.
UPDATE
=======
Hi Vikram,

The message "Applying PORDLVSR.xml to the wrong release" pretty much says it all.

Page definitions:

PORDLVMR.xml - Enter Delivery Information for Selected Line Item page
PORDLVSR.xml - Delivery Information page
PORSPAMT.xml - Non Catalog Request : Goods Or Services Billed by Amount
PORSPGDS.xml - Non Catalog Request : Goods Billed by Quantity
PORSPRAT.xml - Non Catalog Request : Rate

The above are obsolete with 11.5.10 code level iProcurement. You do not need to copy these.

Thanks,
Anthony

ORA-600 errors after upgrade from 9.2.0.8 to 10.2.0.3

1.ORA-00600: internal error code, arguments: [16203], [], [], [], [], [], [], []
2.ORA-00600: internal error code, arguments: [17281], [1001], [0x3AE64C028], [], [], [], [], []
3.ORA-00600: internal error code, arguments: [kcbzwb_4], [], [], [], [], [], [], []

1. Possibly bug 5108130:
Making a reference in a PL/SQL block to a synonym at a remote site R1 when
the referent of the synonym was on yet another site R2 could have resulted
in an ORA-600 [16203] error. The PL/SQL block must have been compiled by
a 9.2.0.X or earlier compiler.

2. Possibly bug 5482174:

Deinstantiation of a large number of PLSQL objects that have opened cursors
when session_cached_cursors is non-zero might hit an ORA-600 [17281] [36]
with a call stack of the form
  'ksupop->kgiCallStateCleanUp->kgiLRUResize'
repeated within it a number of times.

Workaround:
 Set session_cached_cursors to zero.

3. Possibly bug 5028099:

ORA-600 [kcbzwb_4] can occur when using in memory undo (IMU)
for private buffers.

Workaround:
 Set "_in_memory_undo"=FALSE

Invalid OLAPSYS and SYS objects after installing OLAP and Data Mining

We installed OLAP and Data Mining after upgrading the Apps Instance from to 11.5.9/9.2.0.8 to 11.5.10.2/10.2.0.3 by following point 19 in interop metalink note:362203.1 point 19:

To install Data Mining, use SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/rdbms/admin/dminst.sql SYSAUX TEMP

If the query does not return AMD, then you do not have OLAP installed. To install OLAP, use SQL*Plus to connect to the database as SYSDBA and run the following command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

We got the following invalids after running the above sqls:

SQL> /

OWNER OBJECT_TYPE OBJECT_NAME
---------- ------------------- ---------------------------
OLAPSYS PACKAGE BODY CWM2_OLAP_METADATA_REFRESH
OLAPSYS PACKAGE BODY CWM2_OLAP_OLAPAPI_ENABLE
OLAPSYS PACKAGE BODY DBMS_AWM
SYS PACKAGE DBMS_XSOQ_ODBO
SYS PACKAGE BODY DBMS_XSOQ_ODBO
PUBLIC SYNONYM DBMS_XSOQ_ODBO
SYS TYPE OLAPI_MDX_ROWSET_IMPL_T
SYS FUNCTION OLAPI_MDX_ROWSET_TABLE
PUBLIC SYNONYM OLAPI_MDX_ROWSET_IMPL_T
PUBLIC SYNONYM OLAPI_MDX_ROWSET_TABLE

10 rows selected.

I have logged an SR with Oracle for this. I'll update this post with the solution given by them.

Before Oracle could provide an answer, I reran this command:

SQL> @$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP

All the invalids are gone after re-running the command. It is possible that the DBA who ran the command originally, may have got some errors, but carelessly he did not spool the run.