Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    Batch jobs report ORA error: io_z11 write in Aleph 23

    • Product: Aleph
    • Product Version: 23
    • Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

     

    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.
    1. Connect to the DB:
      sqlplus /as sysdb

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


      select a.ksppinm name,
      b.ksppstdf default1,
      decode (a.ksppity, 1,'boolean', 2,'string', 3,'number', 4,'file', a.ksppity)   type,
      a.ksppdesc description
      from  sys.x$ksppi a,
      sys.x$ksppcv b
      where a.indx = b.indx
      and a.ksppinm like '\_%' escape '\'
      and a.ksppinm  like '%batch_table%'
      order by name;

     

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

    e. If the value in DEFAULT1 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;

    1. restart the DB

     

     


    • Article last edited: 10-Apr-2018