Troubleshooting Guide - invalid objects in the E-Business Suite Environment 11i and 12 [ID 1325394.1]
Oracle Application Install - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 30-Nov-2012***
Purpose
This troubleshooting guide is created to help identify invalid objects (PL/SQL Packages or Java Classes) in your E-Business Suite Instance, identify the cause and to compile objects to be valid.
The scripts and solutions offered here are valid for any Release of the E-Business Suite from Release 10.7, 11.0.x, 11.5.x, 12.0.x and 12.1.x.
Troubleshooting Steps
Quick Checks
Invalid objects will appear for several reasons and it is not very easy to identify invalid objects as the root cause of a failure. In many cases invalid objects can be identified
for failures reported from the functional area or for any mis-behaviour identified. In any case you can expect invalid objects, when you have imported a Database or when you have
applied a Patch bringing new PL/SQL Packages. If you are using adpatch with the Standard options, a compilation of invalid objects for the APPS Schema is included by default -
many Customers disable this compilation via "adpatch options=nocompile", to save time. In any case, it is a good idea to check the E-Business Suite Instance on a regular basis for
invalid objects.
For regular checks for invalid objects, you can use following selects :
1. How many invalid objects are in the Database ?
select owner,object_type,COUNT(*)
from dba_objects
where status='INVALID'
group by owner, object_type;
2. Check for details about the invalid objects :
column owner format A9
column object_name format A31
column object_type format A15
column last_ddl_time format A10
spool invalids.lst
select OWNER, OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME
from dba_objects where status='INVALID'
order by owner;
The shown select statements will show you any invalid object - including Triggers, Java-Classes in the DB, Views and PL/SQL Packages.
You can also identify the invalid PL/SQL Packages via the RDA output available for Release 11i and Release 12 :
Document 183274.1 - '11i : Applications DBA RDA Data Collection Test'
Document 732091.1 - 'R12.0.6+ : All RDA Data Collection Test'
Document 420427.1 - 'R12.0.[3-4] : All RDA Data Collection Test'
Attention : The Reports produced by the RDA Utilities are only including the inavlid PL/SQL Packages - but they are also showing the Release Information of the Packages !
Validating invalid objects
There are several possibilities to validate the invalid objects, which depends on the summary of invalids. If you have identified only some invalid objects, it will be the best to compile
them manually. Please be aware, that you need first to compile the objects, which are belonging to Database Schemas like SYS, SYSTEM, CTXSYS. Objects in the APPS Schema
are often dependent on the objects listed and need to be valid before you are compiling the objects owned by APPS or APPLSYS.
1. Manual compilation of invalid objects :
If you have identified a maximum of 10 invalid objects, it will be the best to try to compile the manually. Please use following commands for the manual compilation, where the command for a package, a package body, a View and a Java Class is listed :
alter package
. compile;
alter package
. compile body;
alter view
. compile;
alter alter java class "
." resolve;
alter procedure
. compile;
alter function
. compile;
alter materialized view
. compile;
2. Scripts to compile invalid objects :
You can create your own script(s) for your invalid objects or you can use scripts, which are coming with your Database- and the E-Business Suite- Installation. There are several scripts available after a Fresh-Install of the E-Business Suite .
2.1 The script 'adcompsc.pls' under the $AD_TOP/sql :
The script 'adcompsc.pls' is installed under the $AD_TOP/sql - this script is executed when you run the AD_Utility 'adadmin' and select to compile the invalid objects.
The script can be executed manually with following options :
1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3
NOTE : The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schemas. In case of an ORA-1555 error while running adcompsc.pls, restart the script.
The script can be run as follows :
cd $AD_TOP/sql
sqlplus @adcompsc.pls
%
Example :
sqlplus @adcompsc.pls apps
%
2.2 The script 'utlrp.sql' under the DATABASE $ORACLE_HOME/rdbms/admin/
You need to be logged on as the OS User, who owns the DATABASE $ORACLE_HOME and you need to source the Database Environment.
NOTE:
* This script must be run using SQL*PLUS.
* You must be connected AS SYSDBA to run this script.
* There should be no other DDL on the database while running the script.
Not following this recommendation may lead to deadlocks.
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
connect / as sysdba
NOTE: The number of invalid objects and spool the invalid objects list before and after running these scripts.
2.3 Creating your own compilation script
With the following script, you will create a new sql-script to compile the invalid objects, which can be used to compile your individual invalid objects.
set pagesize 0 head off feedb off echo off
spool validate_all.sql
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects where object_type in ('PROCEDURE','FUNCTION','VIEW','TRIGGER','MATERIALIZED VIEW')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile package;'
from dba_objects where object_type in ('PACKAGE')
and status='INVALID' order by owner
/
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects where object_type in ('PACKAGE BODY')
and status='INVALID' order by owner
/
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects where object_type = 'JAVA SOURCE' and status = 'INVALID';
/
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects where object_type = 'JAVA CLASS' and status = 'INVALID';
/
spool off
exit
The script 'validate_all.sql' should have been created in the directory, where you executed the select. You need to add the execution right on the created script, to start the compilation script.
chmod +x validate_all.sql
Then execute the script 'validate_all.sql' within a sqlplus session :
sqlplus apps/
If any error occurs during the compilation, you can identify them by checking the next section in this document.
Compilation errors
If the compilation statement returns a warning like:
Warning: Procedure created with compilation errors.
Warning: View created with compilation errors.
Warning: Function created with compilation errors.
Warning: Package created with compilation errors.
then you need to check for the error message. It depends on the compilation method you used, to identify the error message for the failure.
1. Using built-in functionality in sqlplus
If you are compiling the invalids manually within sqlplus, please use following command after the failure occurred :
show errors
This should show the cause of compilation error. Please note the error number, the description and the name of the object you tried to compile. It could happen, that the
command above shows no error or if you used your own script for compilation. Then please see step 2.
2. Checking for an error via a select on the DBA_ERRORS
It is possible to check for the failure on an object via following select on the DBA_ERRORS View :
select text from DBA_ERRORS
where name ='
'
and owner ='
';
The above select would present as output a list of error messages that led to compilation error.
3. Checking for errors using the 'aderrchk.sql'
Under the $AD_TOP/sql is the script 'aderrchk.sql' available, which can be used to check for the compilation errors occurred.
It accepts following ARGUMENTS :
1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3
4 - File to spool to. When running from adaimgr, this would be something like errchk
.lst
5 - Error behavior
FAILINV - Fail if any invalid objects found at all
FAILERR - Fail if any invalid objects found outside user_errors
NOFAIL - Do not fail, period.
NOTE: The schema that this runs against must have select access on some tables owned by SYS.
The Oracle Applications APPS schemas have the required privileges.
Example for the usage of the 'aderrchk.sql' script :
It is expected, that a new Shell has been opened and the APPS User Environment has been sourced.
cd $AD_TOP/sql
sqlplus apps
@aderrchk.sql APPS % aderrchk.log NOFAIL
You will be able to check for all objectnames and errors in the logfile 'aderrchk.log'.
Solving the compilation errors
1. Checking for dependencies
If you see a generic error message like 'table or view does not exist' or 'no columns in table', it is needed to identify the dependent objects. The dependencies can be identified
using following query :
select referenced_owner, referenced_name,referenced_type
from DBA_DEPENDENCIES
where name='
';
With the output you can verify if the dependent objects are existing in the Database and you can check the Status of these dependencies by querying the DBA_OBJECTS View.
2. Verifying the Release Information of Packages
In the E-Business Suite you can verify, if the package in the Database is available in the same Release as it is available in the filesystem. To get the Release information of the package in the Database, please use following select :
select text from user_source where line=2
and name =
;
Example :
select text from user_source where line=2
and name ='ICX_UTIL';
Output :
TEXT
--------------------------------------------------------------------------------
/* $Header: ICXUTILS.pls 115.11 2002/12/04 20:25:45 nlbarlow ship $ */
/* $Header: ICXUTILB.pls 115.30 2003/04/23 15:52:19 nlbarlow ship $ */
The output shows 2 lines for the package name, as we have a package body which reflects the
B.pls and the package (specification) which is reflected by the
S.pls. Additionally we can see the Release information for both files. Now we can check in the filesystem for the filenames and for the Release information
via following commands :
cd $ICX_TOP/patch/115/sql
ls -l ICXUTIL*
grep '$Header' ICXUTILB.pls
OR
adident Header ICXUTILB.pls
If the Release information for the package body and the specification mismatch with the Release information from the Database, it is the best to recreate the package via sqlplus :
sqlplus apps/
The Product TOP to check for the file in the filesystem can be identified via the object name - in generic the object name starts with the Product name, like ICX or FND and then the name is added with the specific name. The latest files coming with a Patch are stored under the $
/patch/115/sql, which are used to re-create a package in the Database.
So the related files for the object with the name 'FND_OAM_MET_GRPS_PKG' will be found under the "$FND_TOP/patch/115/sql" and they are named
'AFOAMMGB.pls' and 'AFOAMMGS.pls'.
3. Recreate DB objects owned by SYS or SYSTEM
If it is needed to recreate any package/procedure/view or function, it is possible to identify the script, which should have been used for the creation of these objects under the
Database $ORACLE_HOME/rdbms/admin directory.
To identify the script, which is creating for example the shared you need to execute following steps :
Open a new Shell and source the Database Environment
cd $ORACLE_HOME/rdbms/admin
grep -i 'create or replace package dbms_shared_pool' *.sql
For the example, the following line will be shown as result :
dbmspool.sql:create or replace package dbms_shared_pool is
In this example you have to execute the script 'dbmspool.sql' as User SYS.
4. Identify problems with the global DB_name and DB links
If you see invalid DB links, which are failing for the compilation, it is in most cases a problem in your global DB-Name, which is constructed on your selected domain name.
Very often a hyphen is used for a domain, which is in many DB Releases an illegal Character.
To identify the global DB-Name use following query :
SELECT * FROM global_name;
If there is a hyphen used somewhere in the Domain name, it is identified as an illegal character.
If the global DB name is correct, try to fix the issue by dropping the Database links and re-create them.
If the manual compilation fails and it is not possible to identify the root cause, then it will be the best to open a SR with the E-Business Suite Technology area and provide the receiving engineer with additional details for the issue, by providing a trace file.
5. Enable 10046 tracing and upload the trace file :
To enable the tracing and create a trace file please use following commands :
Alter session set events '10046 trace name context forever,level 12'
Alter procedure
compile;
Exit session.
Check in the user_dump_dest for the trace file generated. Upload this to Oracle Support for further analysis.
6. Dealing with invalid Java Classes
Here is a script to identify the invalid Java Classes, create a compilation script, execute the compilation script and create a list with invalid Java Classes, which are still invalid -
after the first compilation.
comp_java.sql
set echo off
set feedback off
set pagesize 0
spool alter_java.sql
select 'ALTER JAVA SOURCE "' || object_name || '" COMPILE;'
from user_objects
where object_type = 'JAVA SOURCE'
and status = 'INVALID';
select 'ALTER JAVA CLASS "' || object_name || '" RESOLVE;'
from user_objects
where object_type = 'JAVA CLASS'
and status = 'INVALID';
spool off
set feedback on
set pagesize 120
set echo on
Please save the script and execute it until there are no more invalid objects or the number of invalid java objects is the same after the execution as before. If this happens please upload the list of invalid objects, which is stored in the file 'java_invalid.lst'.
You can also identify the Release information of a Java Class stored in the Database by using following commands :
1. exec dbms_java.set_output(200000);
2. set serveroutput on
3. exec fnd_aolj_util.getclassversionfromdbgetclassversionfromdb('oracle/apps/fnd/common/AppsContext');
For example :
SQL> exec dbms_java.set_output(200000);
PL/SQL procedure successfully completed.
SQL> SET serveroutput ON
SQL> exec fnd_aolj_util.getclassversionfromdb('oracle/apps/fnd/common/AppsContext');
Class: oracle.apps.fnd.common.AppsContext
$Header: AppsContext.java 115.158 2004/08/04 13:38:07 rnix ship $
PL/SQL procedure successfully completed.
Another example :
SQL> set serveroutput on
SQL> exec fnd_aolj_util.getclassversionfromdb('oracle/xml/parser/v2/SAXAttrList');
Class: oracle.xml.parser.v2.SAXAttrList
Class oracle.xml.parser.v2.SAXAttrList does not have an RCS_ID field
PL/SQL procedure successfully completed.
NOTE: The message "Class xxxx does not have an RCS_ID field" means that this Java Class is not EBS specific Java Code and therefore no version control on these Java Classes is available in the Database - so it is not an error !
Questions & Answers
1. Why do I have invalid objects ? What causes them ?
Invalid objects can and will occur for many reasons. You will usually find invalid objects after running (or failing to run) database preparation scripts, doing an export/import,
upgrading, or applying patches. Invalid objects are usually caused by missing grants, synonyms, views, tables or packages, but can also be caused by corrupted packages.
2. Why does Oracle Support always tell me to recompile my invalid objects ?
Compiling invalid objects on your database is almost the equivalent of running scandisk on a PC hard drive. This should be one of the first things you check if you start experiencing problems with your Oracle database. It is also a good idea to schedule regular checks for invalid objects.
When you call in to Oracle Support with a database or installation issue, one of the first questions they will probably ask is whether you have checked for and resolved any invalid objects.
3. Are invalid objects ever acceptable? How many is too many ?
If the invalid objects exist for a Product that is not installed, it may be OK to have some, but it is preferable to have no invalid objects existing at all. If invalid objects exist
for a Product that you do have installed and are using, then it should be considered unacceptable and any existing invalid objects should be resolved before further issues can occur.
There is no set number of invalid objects that could be considered 'acceptable' as each situation will vary widely from one Installation to the next. You could just have a few invalid objects or they could number in the hundreds or even thousands, but every effort should be made to resolve them.
4. If we do not use the Product with the invalid objects, can we delete them ?
In an E-Business Suite Installation are several dependencies between Products and therefore you should never delete any invalid object, if not advised by Support, that these invalid objects can be deleted.
5. How can I use adadmin to recompile my invalid objects ?
If you don't want to use the methods shown above, you can use adadmin in an E-Business Suite Environment to compile the invalid objects. Please execute following steps to initiate the compilation via 'adadmin':
1. Open a new Shell and source the APPS User Environment
2. Start 'adadmin' and pass the values requested for
3. Select the menu '3. Compile/Reload Applications Database Entities menu'
4. Select the menus '1. Compile APPS schema'
