Pressing CTRL-C during drop of function based index may result to a number of objects to became invalid.
For example, if you press CTRL-C while you drop a function based index on table applsys.fnd_loopup_values you have a chance to end up with invalid objects in the database.
Especially for E-Business Suite this will result in the following error while trying to log into Self Service Applications:
Error:Invalid argument.
URL:http://mysvr02.mysrv.com:8000/OA_HTML/RF.jsp?function_id=1283&resp_id=50583&resp_appl_id=401&security_group_id=0&lang_code=US&formsLink=yes
Click a "Web Based Form" eg iExpenses , then Expenses Home:
Oracle error 20001: java.sql.SQLException: ORA-20001: Oracle error -20001:
ORA-20001: APP-FND-02500: Error occurred during product initialization for PER when executing 'begin HR_SIGNON.INITIALIZE_HR_SECURITY; end;'. SQLCODE = -20001
SQLERROR = ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, HR_SIGNON.INITIALIZE_HR_SECURITY, N, ERRNO, -6508, N, REASON, ORA-06508:
PL/SQL: could not find program unit being called has been detected in FND_GLOBAL.INITIALIZE. ORA-06512: at "APPS.APP_EXCEPTION", line 70 ORA-06512:
at "APPS.FND_GLOBAL", line 64 ORA-06512: at "APPS.FND_GLOBAL", line 1028 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 573 ORA-06512: at
"APPS.FND_SESSION_MANAGEMENT", line 876 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 220 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.
Unable to authenticate session.Metalink offers a solution to follow:
1. Compile apps schema.
2. Recreate Grants and Synonyms.
But it won't solve the problem!
The problem is that canceling the drop index left inconsistencies to the dictionary. One possible workaround is:
A. Find the Orphaned IND$ and manually delete them from the dictionary.
B. Compile all objects in the database.
A. Find the Orphaned IND$ and manually delete them
To identify Dictionary Inconsistency you must run as sysdba the check.full procedure using the script hcheck8i.sql
Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hOut as described in Note 101468.1
3. Create package hcheck in SYS schema (Refer the attachment under SCRIPT to Create package hcheck)
4. spool outputfile
5. execute hcheck.full
6. It will create a trace file at the udump destination
An example output of the trace file:
SQL> set serveroutput on
SQL> execute hcheck.full
HCheck Version 8i/1.30
Problem: Duplicate DATAOBJ# (may be valid if using transported TS)
DATAOBJ#=0 OBJ#=662756 Name=JTF.DR$JTF_TASKS_TL_IM$K Type#=2
DATAOBJ#=0 OBJ#=662791 Name=JTF.DR$JTF_TASKS_TL_IM$N Type#=2
DATAOBJ#=0 OBJ#=798928 Name=JTF.DR$JTF_NOTES_TL_C1$K Type#=2
DATAOBJ#=0 OBJ#=798933 Name=JTF.DR$JTF_NOTES_TL_C1$N Type#=2
Problem: Orphaned IND$ (no SEG$) - See Note:65987.1 (Bug:624613/3655873)
ORPHAN IND$: OBJ=830178 DOBJ=830178 TS=0 RFILE/BLOCK=0 0 BO#=830174
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830176 DOBJ=830176 TS=0 RFILE/BLOCK=0 0 BO#=830174
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830193 DOBJ=830193 TS=0 RFILE/BLOCK=0 0 BO#=830191
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=830201 DOBJ=830201 TS=0 RFILE/BLOCK=0 0 BO#=830199
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=537290 DOBJ=537290 TS=0 RFILE/BLOCK=0 0 BO#=537288
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=342801 DOBJ=342801 TS=0 RFILE/BLOCK=0 0 BO#=342799
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=299283 DOBJ=299283 TS=0 RFILE/BLOCK=0 0 BO#=299281
SegType= ^- May be OK. Needs manual check
ORPHAN IND$: OBJ=537280 DOBJ=537280 TS=0 RFILE/BLOCK=0 0 BO#=537278
SegType= ^- May be OK. Needs manual check
SEG$ has no UET$ entry: TS#=3 RFILE#=4 BLK#=2383 TYPE#=9
SEG$ has no UET$ entry: TS#=5 RFILE#=46 BLK#=21520 TYPE#=9
SEG$ has no UET$ entry: TS#=6 RFILE#=45 BLK#=11189 TYPE#=9
SEG$ has no UET$ entry: TS#=7 RFILE#=22 BLK#=9677 TYPE#=9
NB: TYPE#=9 is special and may be OK
Problem: Table with Dropped Func Index - Bug:1805146 / Note:148740.1
Table=APPLSYS.FND_CONCURRENT_PROGRAMS
Table=APPLSYS.FND_DESCRIPTIVE_FLEXS
Table=APPLSYS.FND_DESCR_FLEX_COLUMN_USAGES
Table=APPLSYS.FND_DOCUMENT_CATEGORIES
Table=APPLSYS.FND_FLEX_VALIDATION_EVENTS
Table=APPLSYS.FND_FLEX_VALIDATION_QUALIFIERS
Table=APPLSYS.FND_FLEX_VALIDATION_RULES
Table=APPLSYS.FND_FLEX_VALIDATION_TABLES
Table=APPLSYS.FND_FLEX_VALUE_RULES
Table=APPLSYS.FND_FLEX_VALUE_SETS
Table=APPLSYS.FND_ID_FLEX_SEGMENTS
Table=APPLSYS.FND_LOOKUP_TYPES
Table=APPLSYS.WF_ACTIVITIES
Table=FA.FA_ADDITIONS_B
Table=FA.FA_CATEGORIES_B
Table=FA.FA_LOOKUPS_B
Table=FA.FA_LOOKUP_TYPES_B
Table=FA.FA_RX_REP_COLUMNS_B
Table=HR.PER_ASSIGN_PROPOSAL_ANSWERS
Table=HR.PER_PROPOSAL_CATEGORY_MEMBERS
Table=HR.PER_PROPOSAL_CATEGORY_TYPES
Table=HR.PER_PROPOSAL_OFFER_PARAGRAPHS
Table=HR.PER_PROPOSAL_QUESTIONS_ADV
Table=HR.PER_PROPOSAL_QUESTION_MEMBERS
Table=HR.PER_PROPOSAL_QUESTION_TYPES
Table=HR.PER_PROPOSAL_TEMPLATES
Table=INV.MTL_MATERIAL_TRANSACTIONS
Table=INV.MTL_MATERIAL_TRANSACTIONS_TEMP
Table=MSC.MSC_REGIONS
Table=MSC.MSC_ST_REGIONS
Table=OE.SO_AGREEMENTS_B
Table=OE.SO_PRICE_LISTS_B
Table=OE.SO_PRICING_RULES_B
Table=OE.SO_RESULTS_B
Table=WIP.WIP_COST_TXN_INTERFACE
Problem: SOURCE$ for OBJ# not in OBJ$ - Bug:3532977 ?
SOURCE$ has 197 rows for 11 OBJ# values not in OBJ$
Problem: Dependency$ p_timestamp mismatch for VALID objects
....
....
....Manual delete the Orphaned IND$
As you can see there are entries in the sys.OBJ$ and sys.IND$ but there not exist in the sys.dba_objects.
Record does not exist!
SELECT *
FROM SYS.DBA_OBJECTS
WHERE OBJECT_ID IN
(830178, 830176, 830193, 830201, 537290, 342801, 299283, 537280);Record exists
SELECT *
FROM SYS.OBJ$
WHERE OBJ# IN
(830178, 830176, 830193, 830201, 537290, 342801, 299283, 537280);
SELECT *
FROM SYS.IND$
WHERE OBJ# IN
(830178, 830176, 830193, 830201, 537290, 342801, 299283, 537280);Backup the rows before delete
INSERT INTO SYS.OBJ$_DEL
SELECT *
FROM SYS.OBJ$
WHERE OBJ# IN
(830178, 830176, 830193, 830201, 537290, 342801, 299283, 537280);
INSERT INTO SYS.IND$_DEL
SELECT *
FROM SYS.IND$
WHERE OBJ# IN
(830178, 830176, 830193, 830201, 537290, 342801, 299283, 537280);Delete from dictionary
DELETE FROM SYS.OBJ$
WHERE OBJ# IN (830176, 830193, 830201, 537290, 342801, 299283, 537280);
DELETE FROM SYS.IND$
WHERE OBJ# IN (830176, 830193, 830201, 537290, 342801, 299283, 537280);
COMMIT;B. Compile all objects in the database.
As you can see for further examination of the trace file after executing hcheck.full there are Dependency$ p_timestamp mismatch for VALID objects
and especially for object_id=17540 (which is HR_API package) and other HR packages!
The solution to the invalidation and timestamps mismatch is to run UTLIRP.SQL.
UTLIRP.SQL is a SQL script which first invalidates & then recompiles PL/SQL modules, procedures, functions, packages, types, triggers, views in a database.
For E-Business Suite Database it will take approximately 7hours to complete!
Other related issues
pls-00907: cannot load library unit APPS.HR_API (referenced by #####) HR_API is a valid object.This problem started occurring after they dropped a redundant custom index on the HR_ALL_ORGANIZATIONS_UNIT.
When they dropped this it seemed to take a while and then they had a bunch of invalid objects.
No comments:
Post a Comment