Invalid Objects in oracle upgrade 12.1.0.2

[bhargav_bhatt]

Dear All,

We have executed the @pre_upgrade_checks.sql before upgrading the database from 11.2.0.4.to 12.1.0.2. We have found around 80 invalid objects. Output of the pre_upgrade_checks.sql is as below

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

Invalid Objects

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

WARNING: ----> INVALID_OBJECTS

WARNING: ----> Invalid Objects in a schema (not SAP, not SYS)

.... USER SYSTEM has 54 INVALID object(s).

.... USER NELITO has 25 INVALID object(s).

.... USER TCLFI has 1 INVALID object(s).

Number of invalid objects based on SYS.DBA_OBJECTS

Number of invalid objects (SYS): 0

Number of invalid objects (SAP): 0

Number of invalid objects (all): 80

Number of invalid objects (SYS)    : 26( from SYS.registry$sys_inv_objs)

Number of invalid objects (non-SYS): 0( from SYS.registry$nonsys_inv_objs)

Note: a value of -1 indicates that the respective table does not exist.

We have also run the /oracle/cfgtoollogs/<SID>/preupgrade/preupgrade_fixups.sql and $ORACLE_HOME/rdbms/admin/utlrp.sql. But the number of invalid objects remained same. 

We also tried to compile objects manually with below command

SQL> ALTER VIEW NELITO.VW_OVERHEAD_MTHLY COMPILE;

Warning: View altered with compilation errors.

SQL>show error

no error

Please help on above issue.

Regards

Bhargav Bhatt

[ACE-SAP]

Hello

The DB should be dedicated to SAP usage... and obviously here you have 3rd party applications using it (user / schema for NETLITO & TCLFI)

Focus on the system objects, and warn you customer about this unsupported usage of the DB.

I hope they are note using an ASFU license for Oracle, because in that scenario hosting 3rd party application in the Oracle DB is a license violation.

Best regards

581312 - Oracle database: Licensing restrictions

If you use other software (for reporting, for example, or external tools to create automatic SAP system copies), then the following actions at the database level - among others - are not allowed:

• Creating database users

[bhargav_bhatt]

Hello,

Thanks you for your response !!

What can i do with " USER SYSTEM has 54 INVALID object(s) " ??

Regards

Bhargav Bhatt

[ACE-SAP]

Since you did already run utlrp.sql all the remaining invalid objects should not be fixed with recompilation... but you can still try that script to create the sql commands to recompile invalid objects for system user (and forget the ones for the other applications...  that might not survive to the upgrade )

Best regards

SELECT

'ALTER ' || RTRIM(object_type, ' BODY') || ' "' || owner || '"."' || object_name || '" COMPILE '|| DECODE (object_type, 'PACKAGE BODY','BODY;', 'PACKAGE', 'PACKAGE;', ';')

FROM

  dba_objects

WHERE

  owner = 'SYSTEM' AND object_type IN ('PROCEDURE', 'FUNCTION',  'TRIGGER', 'VIEW','PACKAGE','PACKAGE BODY')

  AND STATUS = 'INVALID';

[bhargav_bhatt]

Hi,

Thanks !!

We have run above query below is the output.

Regards

Bhargav Bhatt

[ACE-SAP]

These views does not seem to be part of the standard Oracle DDIC objects.

I've checked on different 11g DBs of some SAP systems (NW 7.x, ECC) and they do not exist

These views might have been wrongly created in the SYSTEM schema, or created by a 3rd party tool. You can check these views definition using the here under command.

Set lines 120 pages 500

SET LONG 2000

COLUMN VIEW_NAME FORMAT a20

COLUMN TEXT FORMAT a100 WORD_WRAPPED

select VIEW_NAME "View", TEXT "Code" from dba_views where view_name like 'VW%DAYS%';

[bhargav_bhatt]

Hi,

Thanks for the update.

Can we proceed with upgrade ignoring these invalid objects ??

Regards

Bhargav Bhatt

[Former Member]

Hi Bhargav,

To recompile invalid objects use SAP Transaction SE14 to reactive the views into database.

In addition consider SAP Note

648203 - Database objects with status INVALID & suggests to consider

1598594 - BR*Tools configuration for Oracle installation using user "oracle"

2320927 - ORA-04063 when upgrading to Oracle 12.1.0.2

Regards

Prithviraj

[ACE-SAP]

Hi

Check the view content, as this might not be related to Oracle DDIC.
Any way I think you could ignore these object especially if there are only views

Best regards