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

    Unique constraint errors in scan and find searches after v20 upgrade

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

    We've installed v.20 and imported our v.18 data via the data pump on to a new server and performed the UE 18 - 19 and 19 - 20. We are now running through the checklist after upgrade. I've noticed that newly created records aren't retrievable in scan and find searches in the GUI or OPAC. Checking ue_01 logs in ABC01 I see these types of errors:

    HANDLING DOC NO. - ABC01.002758143 10:36:17

    Oracle error: io_z97_write
    ORA-00001: unique constraint (ABC02.Z97_ID3) violated


    Oracle error: io_z01_write
    ORA-00001: unique constraint (ABC03.Z01_ID2) violated

    HANDLING DOC NO. - ABC01.002758143 10:40:54

    Our headings index is built in parallel ABC03 and our keyword in parallel ABC02. This problem seems similar to KB 16384-6289, but if the same problem with counters were causing it I would expect to see that in our current v18 production database where copied the data from and it is working fine there.

    I suppose I could just re-index our new v20 database, but I don't want to have to do that when we do our real cut over in a few months.

    As described in KB 16384-6289, these problems occur when the xxx01 util g/2 counter is lower than the actual value in the parallel library Oracle table.
    I'm no longer seeing the ABC02.Z97_ID3 error in the ue_01 log, so I assume that you have already increased the abc01 last-word-number. It has:

    last-word-number 33433806

    which agrees with the abc02 z97:
    abc02@LIBP> select max(z97_rec_number) from z97;

    The abc01 last-acc-number is 20114309, while the actual number is 020114319:

    abc03@LIBP> select max(Z01_ACC_SEQUENCE) from z01;

    Thus, you need to change the abc01 last-acc-number to 020114319 -- or whatever the SQL shows at the time you go to make the change.

    This happens because Upgrade Express extracts abc01 (including the z52 with the last-xxx counters) first. Then later it extracts abc02 and abc03. At the time it extracts these other libraries, if updates have occurred to the z97 or z98, then the abc01 counter will no longer be correct.

    If no updating (including no ue_01 activity) is occurring during the export, I believe you will not see this problem.

    In any case, the solution is to do the SQL shown above in abc02 and abc03 on the v20 server after Upgrade Express and then update the abc01 last-word-number and last-acc-number counters to the max's you find.


    • Article last edited: 10/8/2013