Parallel p_manage_102: Z01_ID4 index "unusable"

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
The p_manage_102 process is having some Oracle-level problems.

The first p_manage_102_c cycle to run loads successfully, except that it leaves the Z01_ID4 index in an "unusable" state. Last fall, you suggested a work-around for this problem - drop and then build the index after the load. This puts the index back in a normal state. This work-around has been incorporated into the p_manage_102_c procedure. With this work-around, the first cycle finishes okay, but the second cycle blows off with an obscure SQL loader error (below). I've run the job several times. The first cycle to complete has always been cycle 4, but the second cycle to complete and be processed has sometimes been 2 and sometimes 3.

The SQL Loader error messages are:
SQL*Loader-961: Error calling once/load finishing for table DUK02.Z01
ORA-03113: end-of-file on communication channel

SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
SQL*Loader-925: Error while uldlgs: OCIStmtExecute (ptc_hp)
ORA-03114: not connected to ORACLE

SQL*Loader-925: Error while uldlgs: OCIStmtFetch (ptc_hp)
ORA-24338: statement handle not executed

We believe that the indexing procedure would run if the SQLLDR was using the NONDIRECT option instead of the DIRECT. Does this sound reasonable to you?

Resolution:
The reason for the unusable index is a missing utility which should have been run when Oracle was installed.

Please follow Oracle instructions listed below.

======================
The workaround is to manually run the ?/rdbms/admin/catcio script as the SYSDBA user after the database has been upgraded to 9.2.0.5. This will allow ONLINE index rebuilds and stop the errors from SQLLDR direct load.

[From site:] We've run the utility (we were already at the correct Oracle level) on our development server and the p_manage_102 job has gotten past the point where it used to blow up.