Oracle compile compiler Invalid object

Original blogger: http://blog.csdn.net/tianlesoftware/article/details/4843600

Applies To:
Oracle server-enterprise edition-version:10.1.0.5.0
This problem can occur on any platform.
Symptoms:
The issue is, the following error was raised:
Ora-00600:internal error code, arguments: [KesutlGetBindValue-2], [], [], [], [], [], [], []
The recent changes was the:
Migration from 10.1.0.5.0 database control to Grid control Agent v10.2.0.3.0
What is runing at this moment of the error occurance is:
The attempt to run SQL Tuning Advisor from the Grid Control


Cause:
Possibly invalid objects in the database.
As the issue here is to try to use these packages and then failed:
SYS. Dbms_sqltune_internal
Body SYS. Prvt_advisor
Body SYS. Dbms_advisor
Body SYS. Dbms_sqltune
And as mentioned with the recent changes is the migration.

Solution:
1. Fixup
1). Connect to the database as SYSDBA:
Sqlplus "/As SYSDBA"
2). Shutdown immediate
3). Startup migrate
4). @?/rdbms/admin/catalog.sql
5). @?/rdbms/admin/catproc.sql
6). @?/rdbms/admin/utlrp.sql
7). Shutdown Immediate
8). Startup

SELECT UNIQUE object_type from all_objects where status = ' INVALID ';
Select ' ALTER ' | | object_type | | "| | owner| | '. ' | | object_name | | ' COMPILE; '
From all_objects where status = ' INVALID '
and object_type in (' Package ', ' FUNCTION ', ' PROCEDURE ', ' TABLE ', ' VIEW ', ' SEQUENCE ', ' TRIGGER ');


SELECT * from Dba_users;
Select ' ALTER Package ' | | owner| | '. ' | | object_name | | ' COMPILE body; '
From all_objects where status = ' INVALID ' and object_type in (' Package BODY ');
SELECT UNIQUE object_type from All_objects;


2. Verify that the status of the Catproc:
sql> Col comp_id format A10
sql> Col comp_name format A30
Sql> Col version format A10
Sql> Select comp_id, Comp_name, status, version from Dba_registry;

And the status of the other objects:
sql> Col object_name format A30
Sql> Col owner Format A15
Sql> Select object_name, owner, object_type, status from Dba_objects
where status = ' INVALID ';

2-if They is invalid please do the following:
To validate the invalid objects
1)-please Run the Utlrp.sql script to try and validate the invalid packages, then check if they is valid or still invalid .
2)-if The objects is still invalid after running the Utlrp.sql script then run Catalog.sql and Catproc.sql and then run Utlrp.sql
A) startup restrict or startup migrate.
b) Run Catalog.sql
c) Run Catproc.sql
D) Run Utlrp.sql
3)-requery for the invalid objects again.
Select Owner,object_type,object_name from dba_objects where status= ' INVALID ';
4)-if They is still invalid try to validate them manually using the below:
Try Manual method of validation:
Alter procedure <owner>.<procedure_name> Compile;
Alter function <owner>.<function_name> compile;
Alter view <owner>.<view_name> compile;
Alter package <owner>.<package_name> compile;
Alter package <owner>.<package_name> compile body;
Alter materialized view <owner>.<Package_name> Compile;
In case you have lots of invalid objects,you can generate scripts that would generate the SQLS for compiling the invalid OB Jects:
In Sqlplus Connect as SYS:
Set Heading off
Spool Compileinvalid.sql
Select ' Alter ' | | object_type| | "| | Owner | | '. ' | | object_name | | ' Compile; ' from dba_objects
where status= ' INVALID ';
Spool off
Ora-06512:at "Ps_system. row_validator_t ", line 912
Ora-06512:at "Ps_system. row_validator_t ", line 924
Ora-06512:at "WorkOrder. Inventory_utl ", line 1260
Select Owner,object_name,object_type,status from all_objects where object_name= ' row_validator_t ' and owner= ' PS_SYSTEM ‘;
OWNER object_name object_type STATUS
------------------------------ ------------------------------ ------------------- -------
Ps_system row_validator_t TYPE VALID
Ps_system row_validator_t TYPE BODY VALID



Sql> Select Owner,object_name,object_type,status from all_objects where object_name= ' inventory_utl ' and owner= ' WorkOrder '
2;
OWNER object_name object_type STATUS
------------------------------ ------------------------------ -------------------      -------
WorkOrder Inventory_utl Package VALID
WorkOrder Inventory_utl Package BODY VALID


Then run Compileinvalid.sql in Sqlplus prompt as SYS user:
To compile invalid package body use:
Alter package <package_name> compile body;

5)-also could do the following Note 100419.1 script:validate. SQL to ANALYZE VALIDATE
STRUCTURE objects in a tablespace
Please Go through the above action plan and I'll be waiting for your update.
6)-please Make sure that these packages is valid:
SYS. Dbms_sqltune_internal
Body SYS. Prvt_advisor
Body SYS. Dbms_advisor
Body SYS. Dbms_sqltune

References
Note 100419.1-script:validate. SQL to ANALYZE. VALIDATE STRUCTURE objects in a tablespace

Alter package BODY SYS. Dbms_xplan body Compile;
Alter package BODY SYS. Dbms_sqltune_internal body Compile;
Alter synonym public. Hhlineor compile;
Alter synonym public. Hhneighbour compile;
Alter synonym public. Hhlineps compile;
Alter synonym public. Hhidrows compile;
Alter synonym public. Hhidlrows compile;
Alter synonym public. Hhbits compile;


---------Invalid index query ' INVALID INDEXES '

Select S2.owner, S2.tablespace_name, S2.segment_name,
I2.table_name, S2.extents, S2.bytes, S2.blocks, I2.status
From Dba_segments S2,
Dba_indexes I2
where S2.owner not in (' SYS ', ' SYSTEM ')
and S2.segment_type = ' INDEX '
and i2.status! = ' VALID '
and s2.segment_name = I2.index_name
and S2.owner = I2.owner
ORDER BY 1,5,4

Oracle Common Scripts
--Use SYS to run the This one
Select ' ALTER ' | | object_type | | "| | owner| | '. ' | | object_name | | ' COMPILE; '
From All_objects
where status = ' INVALID '
and object_type in (' Package ', ' FUNCTION ', ' PROCEDURE ', ' TABLE ', ' VIEW ', ' SEQUENCE ', ' TRIGGER ');

--Select
' ANALYZE ' | | object_type | | "| | object_name | | ' COMPUTE STATISTICS; '
From
User_objects
where
object_type = ' TABLE ';

Oracle compile compiler Invalid object