Choral Upgrade without Downtime

This documentation describes the steps of upgrading Choral without stopping the operation of the formerly installed Choral. This upgrade method is available from version 22.16.0 as the version upgrading to.

1. Prerequisites

You have a running Choral setup with <choral_owner_old> as Choral owner. The users access all Choral methods they are privileged to use - <type>_idxtype, <type>_search, <type>_standardize, <type>_highlight, molconvert, molconvert_clob, chemterm, isvalidmolecule - without the schema prefix by having synonyms.

E.g.:

CREATE OR REPLACE SYNONYM <user>.sample_search FOR <choral_owner_old>.sample_search;

The query of the <user> doesn't set the <choral_owner_old> schema prefix:

SELECT count(*) FROM <table> WHERE sample_search(<column_name>,'C','SUBSTRUCTURE')=1;

The synonym defines that this call is the same as the following:

SELECT count(*) FROM <table> WHERE <choral_owner_old>.sample_search(<column_name>,'C','SUBSTRUCTURE')=1;

2. Install the new version of JChem Choral

Install the new Choral version with <choral_owner_new> as Choral owner. You may choose setting up a new Choral server or using a new install directory on the same server but on different port.

Create all necessary grants and privileges for <user>.
Don't change the synonyms yet and don't add the indexes yet. At the end of this step the old and the new Choral version are both working, only the indexes are missing for the new version.

3. Run the script

Execute this grant statement as SYSTEM:

grant execute on <choral_owner_new>.CREATE_INDEX_ONLINE to <user>;

Fill out the placeholders and execute as the following script for each index: as <user>

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('CHORAL_KEEP_INDEX_DATA', NULL);
  DBMS_OUTPUT.PUT_LINE('starting creating index call, waiting for all transactions on the index to finish');
  LOOP
    BEGIN
      EXECUTE IMMEDIATE 'CREATE INDEX <your create index script with a new index name and the indextype of choral_owner_new ...> INVISIBLE';
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE != -54 THEN
          RAISE;
        ELSE
          DBMS_SESSION.SLEEP(0.1);
        END IF;
      END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('finished creating index call');
  DBMS_OUTPUT.PUT_LINE('start filling index data with object id' || SYS_CONTEXT('USERENV', 'ACTION'));
  <choral_owner_new>.create_index_online(SYS_CONTEXT('USERENV', 'ACTION'));
  DBMS_OUTPUT.PUT_LINE('finished filling index data');
  EXECUTE IMMEDIATE 'ALTER INDEX <your new index name> VISIBLE';
  DBMS_OUTPUT.PUT_LINE('index is visible');
END;
/

The script waits until no uncommitted changes exist for the indexed table. Then creates an empty index. The index is invisible at first, so the query planner doesn't use it yet. Then the script fills the empty index with the index data. It takes a bit more time than a usual indexing. The current progress can be monitored in the v$session_longops view. When it is finished, the index is ready to use. The final step in the script is to make the index visible.

At the end of this step the old and the new Choral both contain the same index data and both are fully functioning.

E.g. these scripts must both work fine for the <user>:

SELECT count(*) FROM <table> WHERE <choral_owner_old>.sample_search(<column_name>,'C','SUBSTRUCTURE')=1;
SELECT count(*) FROM <table> WHERE <choral_owner_new>.sample_search(<column_name>,'C','SUBSTRUCTURE')=1;

4. Change the synonym definitions

Change the synonym definitions to use the new Choral version instead of the old Choral version. E.g.:

CREATE OR REPLACE SYNONYM <user>.sample_search FOR <choral_owner_new>.sample_search;

Test the synonym's work as <user>:

SELECT count(*) FROM <table> WHERE sample_search(<column_name>,'C','SUBSTRUCTURE')=1;

The synonym defines that this call is the same as the following:

SELECT count(*) FROM <table> WHERE <choral_owner_new>.sample_search(<column_name>,'C','SUBSTRUCTURE')=1;

5. Stop and remove old version

Stop running the old version of choral service and drop the user <choral_owner_old>.