Invalid Packages Making Reference to ods_process Table in OID 11g Installation Upgraded from 10g (Doc ID 1216597.1)

Last updated on MARCH 06, 2023

Applies to:

Symptoms

- SSO 10.1.2.3
- IM 10g Oracle Internet Directory (OID) 11.1.1.1

There are some packages under SYS schema that must belong to ODS schema database user, as documented on Note:

Note 579835.1 - Stored Procedures From 10.1.2.3 Patch Cannot be Compiled

Initial instance was AS10g 10.1.2.2 infra node, upgraded to 10.1.2.3 and then OID upgraded to 11g. So SSO is version 10.1.2.3 now.

Applying solution documented on tech Note 579835.1 removed incorrect packages for SYS schema user but the following three packages continue showing invalid under ODS schema:

ODS OIDSTATUSINFO PACKAGE BODY
ODS OIDREPLSTATUSINFO PACKAGE BODY
ODS ODIGETDIPINFO PACKAGE BODY

Trying to recompile those objects, an Oracle error of ODS_PROCESS table not exist is displayed. For example:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++
PACKAGE BODY ODS.OIDSTATUSINFO@<CONNECT_STRING>

get PL/SQL: ORA-00942: table or view does not exist (ODS_PROCESS) in source:

CURSOR cursInstStatusSetInfo (instNumber IN VARCHAR2, eId IN NUMBER) IS
SELECT
HOSTNAME,
DS1.ATTRVAL,
TO_CHAR(LASTWAKETIME, 'YYYY-MM-DD HH24:MI:SS'),
RETRYCOUNT,
DS2.ATTRVAL
FROM
ODS_PROCESS,
DS_ATTRSTORE DS1,
DS_ATTRSTORE DS2,
DS_ATTRSTORE DS3
WHERE
INSTANCE = TO_NUMBER(instNumber) AND
SERVERID =
(SELECT
SERVERID
FROM ODS_SERVERS
WHERE SERVERNAME='OIDLDAPD'
) AND
STATE = 2 AND
DS1.ENTRYID = eID AND
DS1.ENTRYID = DS2.ENTRYID AND
DS2.ENTRYID = DS3.ENTRYID AND
LOWER(DS1.ATTRNAME) = 'orclnonsslport' AND
LOWER(DS2.ATTRNAME) = 'orclconfigsetnumber' AND
LOWER(DS3.ATTRNAME) = 'orclhostname' AND
LOWER(HOSTNAME) = LOWER(DS3.ATTRVAL);

..
+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Changes

That is expected as in OID 11g ods_process table does not exist anymore. The new table name in 11g is ods_process_status.

Also ODS schema appears as INVALID status running:

SQL> select comp_id,version,status from app_registry;

OID process are up and running and SSO authentication works fine.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.