on 06-29-2016 10:53 AM
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
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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';
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%';
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
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
6 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.