Migrate data from Cassandra to Oracle

Encryption:

Data in transit - it is supported Oracle Native Network Encryption
- using properties for server:
  - SQLNET.ENCRYPTION_SERVER = REQUIRED
  - SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
- using properties for client:
  - SQLNET.ENCRYPTION_CLIENT = REQUIRED
  - SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)

Check at the end of the document, to see if encryption at transit is enabled.

Data at rest can be done by enabling in zookeeper the data protection. The following encryption mechanisms can be enforced:
- Using internal veridium protection by enable JCEKS
- Using HSM

Here is the needed documentation: https://docs.veridiumid.com/docs/v3.8/configuring-data-protection .

Overall process description:

Prepare oracle database schema by Client team - to be done before the intervention, at any time, regardless of the release - steps 1.a, 1.b, 1.c
- create schema
- test connection from veridium to oracle (network connection should be opened between webapp servers to oracle)
Actual migration - downtime 1h30 min
- stop veridium application - 5 min.
- migrate data from Cassandra to Oracle. - around one hour
- start veridium application - 5 min
Start testing.
in case that something goes wrong, there will be a rollback procedure - it takes around 15 minutes of downtime.
- stop veridium application
- commute veridium to connect back to cassandra
- start veridium application.
- bring back elasticsearch data to the point before the change

Technical steps:

This is the guide to migrate an environment from Cassandra to Oracle.

Create a user veridiumid with the necessary privileges (user will not have DBA privileges). Also it is recommended to have it’s own tablespace.

Create tenant table space (the space’s initial size and max size is to be discussed):

CODE

CREATE TABLESPACE veridium_ts DATAFILE 'veridium_ts01.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;
## if UNLIMITED is not allowed, please set 50Gb.

Create user cmd:

CODE

CREATE USER veridium IDENTIFIED BY <user-pwd> DEFAULT TABLESPACE veridium_ts QUOTA UNLIMITED ON veridium_ts;
ALTER USER veridium QUOTA UNLIMITED ON veridium_ts;

Grant privileges:

CODE

GRANT CREATE SESSION TO veridium;
GRANT CREATE TABLE TO veridium;
GRANT CREATE VIEW TO veridium;
GRANT CREATE SEQUENCE TO veridium;
GRANT CREATE PROCEDURE TO VERIDIUM;
GRANT CREATE TRIGGER TO VERIDIUM;
GRANT CREATE SYNONYM TO VERIDIUM;
GRANT CREATE TYPE TO VERIDIUM;

Please discuss with DBA to have a collect statistics job for VERIDIUM on a daily basis. Also DBA should monitor the Tablespace size.

Open Websecadmin and open Settings → Persistence → RDBMS and save the JDBC driver configuration. Jdbc configuration is stored in config.json node in ZK. Example:

CODE

    "jdbc": {
        "jdbcUrl": "jdbc:oracle:thin:@//oracledb.dev.local:1521/XEPDB1",
        "jdbcDriverClass": "oracle.jdbc.driver.OracleDriver",
        "username": "USERNAME",
        "password": "PASSWORD",
        .........
    }

To configure encryption, go to advanced settings, open config.json file and go to “jdbc“ config section. There should be a filed named additionalProperties (otherwise add it in jdbc section) and add following additional parameters with proper values (specific for your environment):

CODE

"additionalProperties": {
	"oracle.net.encryption_client": "REQUIRED",
	"oracle.net.encryption_types_client": "(AES256)",
	"oracle.net.crypto_checksum_client": "REQUIRED",
	"oracle.net.crypto_checksum_types_client": "(SHA256)"
}

Create tables and indexes in Oracle Database by running the following command:

CODE

bash /opt/veridiumid/migration/bin/rdbms_ops.sh --schema-update

On all webapp nodes stop the following services (run the command as root user):

CODE

service ver_tomcat stop
service ver_websecadmin stop
service ver_fido stop
service ver_selfservice stop

Check how many rows are going to be exported. Save the report so it can be compared with the imported oracle data.

CODE

## run on persistence
bash /opt/veridiumid/migration/bin/rdbms_ops.sh --summary-cassandra

Export the data from Cassandra by running the following command:

CODE

## run on webapp
bash /opt/veridiumid/migration/bin/rdbms_ops.sh --cassandra-export --dir=<PATH_TO_DIRECTORY>
# For example
# bash /opt/veridiumid/migration/bin/rdbms_ops.sh --cassandra-export --dir=/home/veridiumid/tmp_cass

Import the data to Oracle Database by running the following command:

CODE

## run on webapp
bash /opt/veridiumid/migration/bin/rdbms_ops.sh --rdbms-import --dir=<PATH_TO_DIRECTORY>
# For example
# bash /opt/veridiumid/migration/bin/rdbms_ops.sh --rdbms-import --dir=/home/veridiumid/tmp_cass

After importing the data, please collect statistics - this needs to be executed by DBA on oracle.

CODE

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => 'veridium',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE,
    degree           => DBMS_STATS.AUTO_DEGREE
  );
END;

Check the Cassandra number of entries, generated at step 6 and oracle imported data - this needs to be executed by DBA on oracle.

CODE

select table_name, num_rows from all_tables where owner = 'VERIDIUM' order by 1 asc;

Configure the new application profile on all webapp nodes by running the following command:

CODE

bash /opt/veridiumid/migration/bin/rdbms_ops.sh --change-profile-rdbms

Restart webapp services by running the following command as root user:

CODE

service ver_tomcat start
service ver_websecadmin start
service ver_fido start
service ver_selfservice start

I is highly recommended to get some AWR reports the second day, to see if there are any problems. Performance tests were executed still it needs to be monitored properly the first week.

Rollback procedure (with data lost - it will be lost what data was saved to oracle; the revert will consider the data before migration)

stop services

CODE

service ver_tomcat stop
service ver_websecadmin stop
service ver_fido stop
service ver_selfservice stop

change parameters from rdmbs to cassandra

CODE

bash /opt/veridiumid/migration/bin/rdbms_ops.sh --change-profile-cassandra

export data from Oracle DB

CODE

## run on webapp
bash /opt/veridiumid/migration/bin/rdbms_ops.sh --rdbms-export --dir=<PATH_TO_DIRECTORY>
# For example
# bash /opt/veridiumid/migration/bin/rdbms_ops.sh --rdbms-export --dir=/home/veridiumid/tmp_rdbms

import data to Cassandra

CODE

## run on webapp
bash /opt/veridiumid/migration/bin/rdbms_ops.sh --cassandra-import --dir=<PATH_TO_DIRECTORY>
# For example
# bash /opt/veridiumid/migration/bin/rdbms_ops.sh --cassandra-import --dir=/home/veridiumid/tmp_rdbms

start services

CODE

service ver_tomcat start
service ver_websecadmin start
service ver_fido start
service ver_selfservice start

delete some elastic indices and migrate data one more time from Cassandra to elastic.

CODE

eops -x=DELETE -p=/veridium.accounts-000001
eops -x=DELETE -p=/veridium.profiles-000001
eops -x=DELETE -p=/veridium.fido_devices-000001
eops -x=DELETE -p=/veridium.devices-000001
bash /opt/veridiumid/migration/bin/elk_ops.sh --update-settings
/opt/veridiumid/migration/bin/migrate_to_elk.sh -a
/opt/veridiumid/migration/bin/migrate_to_elk.sh -d
/opt/veridiumid/migration/bin/migrate_to_elk.sh -p
/opt/veridiumid/migration/bin/migrate_to_elk.sh -f

See if encryption is enabled by running the following query on oracle. The connection should have these parameters:

AES256 Encryption service adapter for 64-bit Windows: Version 21.0.1.0.0 - Production

SHA256 Crypto-checksumming service adapter for 64-bit Windows: Version 21.0.1.0.0 - Production

See below one connection that is encrypted and one is not encrypted.