Batch jobs report ORA error: io_z11 write in Aleph 23

Description

Several batch jobs report ORA error message  io_z11 in Aleph 23. 

Example:

$alephe_scratch/xxx01_p_manage_21.00100

Load: /exlibris/aleph/u23_1/usr00/tab/tab100

Oracle error: io_z11_write

ORA-00001: unique constraint (XXX01.Z11_ID) violated

Article “Oracle error: io_z11_write” describes that the problem may occur when the service is running and someone tries to access the z11 record (ue_01) – but that’s not the case here. ue_01 was stopped before the job was started.

Resolution

Because of an issue in Aleph Installation Kit (AIK) for Aleph 23 an Oracle parameter was set to wrong value. Ex Libris has fixed the issue in the AIK (as of May 2017).

Customers who have used AIK before the fix, may be impacted by the issue.

The solution is to change a hidden parameter in oracle by setting (as oracle user in oracle server):

_optimizer_batch_table_access_by_rowid = FALSE.

This is the  complete procedure to follow:

1. On the db server, su as oracle.

2. Connect to the DB:
   sqlplus /as sysdba

 c.  To find out if you are affected by the issue, check the value of the hidden parameter with the following SQL query:​​​        

  show parameter _optimizer
 

d. If the value is FALSE, you are not affected by this issue and there is nothing to do.

e. If the value is TRUE issue the command:

     alter system set "_optimizer_batch_table_access_by_rowid"=false scope=both;

     **Note that if this error is encountered:

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

then spfile should be first created with the following command, DB restarted, and the alter system command issued again

create spfile from pfile;

6. restart the DB

• Article last edited: 10-Dec-2018