Skip to main content
ExLibris
  • Subscribe by RSS
  • ExLibris Dev

    Rosetta service pack 5.2 ends with warning related to the script rep_DPS_16142.sql

    • Product: Rosetta
    • Product Version: 5.2

     

    Problem Symptom

    Rosetta service pack 5.2 ends with warning related to the script rep_DPS_16142.sql.

     

    install_sp.log (under $dps_dev/local_sp_install/5.2.0.0/log):

     

    v249_rep00@DPS3> v249_rep00@DPS3> v249_rep00@DPS3> create unique constraint on collection name and parentId pair
    DECLARE
    *
    ERROR at line 1:
    ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
    ORA-06512: at line 11
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    SQL_FAILED: /exlibris1/operational_shared/service_pack/download/5.20/build_dir_5.2.0.0/install_sp_dir/sql/structure_sql/5_2_0/WARN/rep_DPS_16142.sql
    

     

    Cause

    In version 5.2 unique constraint has been added to the collection table to prevent the creation of duplicate collections under race conditions. In cases there are duplicates, rep_DPS_16142.sql will fail with warning status. 

    Resolution

    Remove any collection naming duplicates (from Collection Management in the Rosetta UI) and run the script manually afterwards .

     

    You may use the following script to identify the duplicates in HDECOLLECTION table:

    SELECT  id, name, parented, owner FROM hdecollection t1 WHERE EXISTS
    (SELECT 1 from hdecollection t2 Where t1.name = t2.name AND t1.parentid = t2.parentid AND t1.id <> t2.id);
    

     

    Run the rep_DPS_16142.sql script manually:

    1. ssh to one of Rosetta servers with dps user
    2. s+ rep00
    DECLARE
     v_is_exists   INTEGER;
    BEGIN
     SELECT COUNT ( * )
     INTO v_is_exists
     FROM user_indexes
     WHERE index_name = 'HDECOLLECTION_UK_03';
     IF (v_is_exists = 0) THEN
      DBMS_OUTPUT.PUT_LINE ('create unique constraint on collection name and parentId pair');
      EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX HDECOLLECTION_UK_03 ON HDECOLLECTION (NAME, PARENTID, OWNER)';
      EXECUTE IMMEDIATE 'ALTER TABLE HDECOLLECTION ADD CONSTRAINT HDECOLLECTION_UK_03 UNIQUE (NAME, PARENTID, OWNER) USING INDEX HDECOLLECTION_UK_03';
     END IF;
    END;
    /
    
    1. The following output indicates a successful run:
    create unique constraint on collection name and parentId pair
    PL/SQL procedure successfully completed.
    

     


    • Article last edited: 05-April-2017
    //doorbell.io feedback widged