Backup and Restore

This document describes backup and restore processes for JChem Choral.

• Application side
• Database side
  • Data Pump method (expdp / impdp)
  • Inspect the import log
  • Data Pump version compatibility
• Backend-specific notes
  • mapdb (default)
  • gcrdb
• Chemical search validation

Application side

See Archiving for directories to back up on the Choral server.

For mapdb (default backend), archive <choral_home>/data/store/ together with the database. mapdb search payload lives on the Choral server filesystem, not in Oracle. expdp restores domain-index metadata; the application archive restores the index files. A database-only restore is not sufficient if data/store is lost.

For gcrdb, index payload is stored in Oracle (see FAQ: store index in the database). Archive config/, data/types/, and license/ on the Choral server; include the JDBC user schema (which holds "engine_data" or your configured singleTableName) in expdp.

Database side

Choral uses standard Oracle schemas: a cartridge owner (e.g. CHORAL_OWNER) with PL/SQL types, domain indextypes, and CHORAL_SETTINGS; and table owner schema(s) holding application tables with structure columns.

Data Pump method (expdp / impdp)

Prerequisites:

1. CREATE DIRECTORY for dump files (e.g. CHORAL_DUMP).
2. User with DATAPUMP_EXP_FULL_DATABASE / DATAPUMP_IMP_FULL_DATABASE (or schema-level privileges).
3. Choral HTTP ACL configured via <choral_home>/sql/2_http.sql (generated into the sql/ folder by init-choral / update-choral) allows the restored Choral server host and port.

Create directory

CREATE DIRECTORY CHORAL_DUMP AS '/path/to/dumps';
GRANT READ, WRITE ON DIRECTORY CHORAL_DUMP TO <admin_user>;

Export

Export the table owner and cartridge owner schemas together:

expdp <admin_user>/<password>@<connect_string> \
  SCHEMAS=<table_owner>,<choral_owner> \
  DIRECTORY=CHORAL_DUMP \
  DUMPFILE=choral_backup.dmp \
  LOGFILE=choral_backup.exp.log \
  COMPRESSION=ALL

COMPRESSION=ALL requires the Oracle Advanced Compression option (or an edition that includes it). Omit it if your database does not support compressed dumps.

Include all schemas that hold Choral data. For gcrdb, also include the JDBC user schema if it is separate from the table owner and holds "engine_data" (or your configured singleTableName).

Import (same schema names — recommended)

Use when restoring to the same PDB/service with the same owner names (e.g. disaster recovery on a clone).

For a clean restore, drop and recreate empty schemas first, then import with TABLE_EXISTS_ACTION=SKIP (or omit the parameter). TABLE_EXISTS_ACTION=REPLACE overwrites existing tables and is risky on shared or production schemas.

impdp <admin_user>/<password>@<connect_string> \
  DIRECTORY=CHORAL_DUMP \
  DUMPFILE=choral_backup.dmp \
  LOGFILE=choral_backup.imp.log \
  TABLE_EXISTS_ACTION=SKIP

After import:

1. Inspect the import log for cartridge or domain-index errors.
2. Run backend-specific steps (see mapdb or gcrdb).
3. Re-apply HTTP ACL if the Choral server hostname or IP changed: edit and run <choral_home>/sql/2_http.sql as SYSTEM (or run update-choral to regenerate the sql/ folder, then run 2_http.sql). See Change the Choral Server machine or directory, IP address or port.
4. Update CHORAL_SETTINGS HOST/PORT if needed (set_choral_setting procedure).
5. Restore the Choral server archive from Archiving:
   • mapdb: config/, data/types/, license/, and data/store/ (restore data/store/ before starting Choral if the server was rebuilt).
   • gcrdb: config/, data/types/, license/ (update JDBC URL or credentials in choral.conf only if the database endpoint or passwords changed).
6. Restart the Choral server.
7. Run chemical search validation.

Import (remapped schemas)

REMAP_SCHEMA imports are possible on both backends, but Choral cartridge objects (domain indextypes, operators, statistics associations) often fail to recreate due to dependency ordering.

If import reports errors on INDEXTYPE, OPERATOR, or ASSOCIATION objects:

1. Run update-choral on the restored cartridge owner (not init-choral unless you are deliberately re-initialising from scratch).
2. Recreate domain indexes on each structure table in the table owner schema.
3. For mapdb, also restore <choral_home>/data/store/ from archive if available; otherwise reindex structure columns.

Inspect the import log

After impdp, read choral_backup.imp.log before declaring the restore successful. A job that completes with errors may leave cartridge objects or domain indexes missing.

Log pattern	Action
OPERATOR failed to create	Run update-choral on the cartridge owner
INDEXTYPE failed to create	Run update-choral on the cartridge owner
ASSOCIATION skipped (depends on indextype)	Fix cartridge first (update-choral), then recreate domain indexes
DOMAIN_INDEX / structure-table index failed	DROP INDEX + CREATE INDEX … INDEXTYPE IS … on the table owner
Job completed with 0 errors	Still run validation on production structure tables

Data Pump version compatibility

Data Pump behaviour and dump file format depend on the Oracle version running expdp and impdp. Do not assume a dump taken on one release imports unchanged on another. Consult Oracle documentation for the versions you use:

Oracle Data Pump overview: 19c, 21c, 23ai

expdp VERSION parameter: 19c, 21c, 23ai

Scenario	Guidance
Same release (e.g. 23ai → 23ai)	Default expdp / impdp; VERSION usually not required
Newer source → older target (e.g. 23ai → 19c)	On export, set VERSION to a value compatible with the target release so the older impdp can read the dump
Older source → newer target (e.g. 19c → 23ai)	Import with the target release's impdp; export VERSION usually not required
Any cross-release restore	Test on a non-production clone first; run chemical search validation after import

Example export when the target is Oracle 19c:

expdp ... VERSION=19.3.0

Backend-specific notes

mapdb (default)

Item	Backup	Restore
Oracle schema(s)	expdp of table owner + cartridge owner	impdp (same names preferred)
<choral_home>/data/store/	Required — index payload	Restore before starting Choral; reindex if missing
Domain index	Included in schema export	Metadata restored by impdp; must match data/store files

Reindexing after restore: Not required if same-schema impdp completes without cartridge errors and the data/store archive is restored. Required if data/store is empty, or remapped-schema import left cartridge objects invalid (run update-choral and recreate domain indexes). update-choral is not normally needed when both impdp and data/store restore succeed.

gcrdb

gcrdb stores index payload in Oracle ("engine_data" or your configured singleTableName), not in <choral_home>/data/store/. Archive config/, data/types/, and license/ on the Choral server — see Archiving.

Item	Backup	Restore
Cartridge owner schema	expdp	impdp; run update-choral if import errors on cartridge objects
JDBC user schema (holds "engine_data")	expdp	impdp
Table owner + domain indexes	expdp	impdp; recreate domain indexes if missing or invalid
"engine_data" rows	In JDBC user export	Restores with schema; verify row count after restore
<choral_home>/data/store/	Not used for index payload	N/A

Restore steps (after impdp):

1. If the import log reports INDEXTYPE, OPERATOR, or DOMAIN_INDEX errors (common even on same-schema restore):
   • Run update-choral on the restored cartridge owner.
   • Recreate domain indexes on each structure table (DROP INDEX + CREATE INDEX … INDEXTYPE IS <choral_owner>.sample_idxtype).
   • Re-gather statistics on indexed tables (DBMS_STATS.GATHER_TABLE_STATS).
2. Restore the Choral server archive (config/, data/types/, license/). Update JDBC URL or credentials in choral.conf only if the database endpoint or passwords changed.
3. Re-apply HTTP ACL (<choral_home>/sql/2_http.sql — see changing server host or port) and CHORAL_SETTINGS HOST/PORT; restart Choral.
4. Run validation, including checks on production structure tables.

Do not run init-choral on a restored cartridge owner unless you are deliberately re-initialising from scratch.

Reindexing after restore: Often required after impdp, even with same schema names, when cartridge objects fail to import. Restore is complete only when production domain indexes exist, show status VALID, and indexed searches (with index hint) return expected counts. If impdp completes with no cartridge errors and production domain indexes are already VALID, recreating indexes is not required.

Chemical search validation

Run validation as table owner (the schema that holds your structure tables). Replace <choral_owner> with the cartridge owner schema.

1. Cartridge smoke test (throwaway table)

Create sample data and a domain index:

CREATE TABLE choral_restore_check (
    id  NUMBER PRIMARY KEY,
    mol VARCHAR2(250)
);

INSERT INTO choral_restore_check VALUES (1, 'c');
INSERT INTO choral_restore_check VALUES (2, 'cc');
INSERT INTO choral_restore_check VALUES (3, 'c1ccccc1');
INSERT INTO choral_restore_check VALUES (4, 'c1ccccc1CC(N)O');
INSERT INTO choral_restore_check VALUES (5, 'C1CCCCC1O');
INSERT INTO choral_restore_check VALUES (6, 'C1CCOCC1N');
COMMIT;

CREATE INDEX choral_restore_check_idx
    ON choral_restore_check(mol)
    INDEXTYPE IS <choral_owner>.sample_idxtype;

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CHORAL_RESTORE_CHECK');

Run searches (query: oxygen substructure O; expected hit count: 3):

-- Cartridge alive
SELECT COUNT(*) AS dual_hits
  FROM dual
 WHERE <choral_owner>.sample_search('CC', 'C', 'SUBSTRUCTURE') = 1;

-- Table search (no index hint)
SELECT COUNT(*) AS table_hits_no_hint
  FROM choral_restore_check
 WHERE <choral_owner>.sample_search(mol, 'O', 'SUBSTRUCTURE') = 1;

-- Table search (domain index hint)
SELECT /*+ INDEX(choral_restore_check choral_restore_check_idx) */ COUNT(*) AS table_hits_with_hint
  FROM choral_restore_check
 WHERE <choral_owner>.sample_search(mol, 'O', 'SUBSTRUCTURE') = 1;

-- Domain index status
SELECT index_name, status, index_type, ityp_owner, ityp_name
  FROM user_indexes
 WHERE table_name = 'CHORAL_RESTORE_CHECK'
   AND index_type LIKE 'DOMAIN%';

Unindexed and indexed searches should both return 3 hits. The domain index should show status VALID.

Warning: Table searches without an index hint can return correct counts even when the domain index on a production structure table is missing. Do not rely on no-hint queries alone to confirm a successful restore.

2. Production structure tables

Verify each restored structure table and its domain index (replace placeholders):

-- Domain index must exist and be VALID
SELECT index_name, status, index_type, ityp_owner, ityp_name
  FROM user_indexes
 WHERE table_name = '<structure_table>'
   AND index_type LIKE 'DOMAIN%';

-- Indexed search on the real table (required)
SELECT /*+ INDEX(<structure_table> <domain_index_name>) */ COUNT(*) AS table_hits_with_hint
  FROM <structure_table>
 WHERE <choral_owner>.sample_search(<mol_column>, '<query>', 'SUBSTRUCTURE') = 1;

Compare counts with your pre-backup baseline. The domain index should show status VALID with the correct ityp_owner and ityp_name.

3. gcrdb: verify index payload table

If using gcrdb single-table mode, confirm "engine_data" (or your configured singleTableName) has the expected row count.

SELECT COUNT(*) FROM "<singleTableName>";

Further reading

• Oracle-base: Data Pump