Skip to Content
0

script pre_upgrade_checks_sap.sql reports invalid objects

Mar 23, 2017 at 07:22 PM

182

avatar image

Dear all,

I am currently upgrading an Oracle database from 11.2.0.4. to 12.1.0.2 on Windows.

Script pre_upgrade_checks_sap.sql reports invalid objects:

**********************************************************************
Invalid Objects
**********************************************************************
WARNING: ----> INVALID_OBJECTS
WARNING: ----> Invalid Objects in a schema (not SAP, not SYS)
.... USER PUBLIC has 1 INVALID object(s).
.... USER SYSTEM 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): 2

Number of invalid objects (SYS) : 0( from SYS.registry$sys_inv_objs)
Number of invalid objects (non-SYS): 0( from SYS.registry$nonsys_inv_obj
Note: a value of -1 indicates that the respective table does not exist.


List of invalid objects:
PUBLIC.STATS$PINGACTIVITY (SYNONYM)
SYSTEM.STATS$PINGACTIVITY (VIEW)
.
**********************************************************************
Generic Pre-Upgrade Warnings
**********************************************************************
WARNING: ----> INVALID_OBJECTS
There are invalid objects in a schema (not SAP, not SYS).
Check why these objects are invalid before upgrading
the database.

select * from dba_errors says that ORA-01775 (looping chain of synonyms) is the reason for the invalid view.

Have you already had the same problem too ?

What can I do to resolve this issue ?

Can I ignore this invalid objects as this is only a warning ?

Thanks and kind regards,

Arnd

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Y Sathishkumarreddy Mar 24, 2017 at 08:29 AM
-1

Hi,

i think your doing upgrade oracle.!

if right ?

you have to run like

SQL>@pre_upgrade_checks_sap.sql

also it has been come ignore it will cover while upgrade db.

Regards,

Share
10 |10000 characters needed characters left characters exceeded
James Zhang
Mar 24, 2017 at 12:25 AM
0

Hi Arnd,

Please check the invalid objects:

select * from dba_objects where status='INVALID'

You may manually 'compile' these objects.

Best regards,
James

Share
10 |10000 characters needed characters left characters exceeded
Arnd Kullik-Lindner Mar 24, 2017 at 07:42 AM
0

Dear James,

thank you for your answer.

I have already tried it but it did not help.

To my mind ORA-01775 is a logical error.

Any other idea ?

Do you think that I can ignore this invalid objects as this is only a warning ?

Kind regards,

Arnd

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Arnd,

Well looks like the object name is :

PUBLIC.STATS$PINGACTIVITY (SYNONYM)

SYSTEM.STATS$PINGACTIVITY (VIEW)

In this case, please check the definition of SYSTEM.STATS$PINGACTIVITY:

select dbms_metadata.get_ddl('VIEW', '/STATS$PINGACTIVITY', 'SYSTEM') from dual;


Best regards,
James

0
Harish Karra Mar 24, 2017 at 07:19 PM
0
WARNING: ----> INVALID_OBJECTS There are invalid objects in a schema (not SAP, not SYS). Check why these objects are invalid before upgrading the database.

If manual compile option is not working you can safely ignore this error as objects are not SAP and not SYS.

Regards,

Harish Karra

Share
10 |10000 characters needed characters left characters exceeded
Reagan Benjamin
Mar 24, 2017 at 10:57 PM
0
List of invalid objects:

PUBLIC.STATS$PINGACTIVITY (SYNONYM)

SYSTEM.STATS$PINGACTIVITY (VIEW)

Based on the name it appears to me that the SYNONYM PUBLIC.STATS$PINGACTIVITY is referencing to the VIEW SYSTEM.STATS$PINGACTIVITY. Have you checked the definition of the view and synonym? Most likely the referenced object or the base tables might have been changed or some columns used in the VIEW are no longer valid. This could be the reason why they remain invalid. If the table is no longer valid then you may drop the VIEW and SYNONYM or recreate the VIEW correctly.

Share
10 |10000 characters needed characters left characters exceeded