Upgrading OneDB Explore

OneDB Explore uses an embedded database called H2 to store custom configuration details.OneDB Explore 2.0.1.2 has upgraded its H2 version due to security vulnerabilities present in the older versions of H2. To upgrade OneDB Explore to 2.0.1.2 from the older versions, direct upgrade is not possible and the data needs to be migrated manually. For new users, who are using OneDB Explore 2.0.1.2 for the first time, there is no impact, and no upgrade is needed as OneDB Explore will create new H2db with latest version.

Before you begin

The following table lists the requirements for OneDB Explore 2.0.1.2 upgrade:


Table 1. Prerequisites
Software	Required Version
OneDB Explore Server (onedb-explore-server.jar) or H2 Database Engine (h2-1.4.192.jar)	2.0.1.1 or earlier 1.4.192
OneDB Explore Server (onedb-explore-server.jar)	2.0.1.2
Java	1.8

Procedure

Make a copy of the existing database:
Find the existing H2 database file path. For OneDB Explore, database can be found at <installation_Path>/explore/server/h2db.mv.db. Make a copy of the db file before starting the migration. This back up file will be useful if any issues are encountered during migration.
Export existing data using current version of OneDB Explore or H2:
1. Create a SQL source file migrate.sql . Store the file in the same folder where H2 database file is present.
  migrate.sql:
```
ALTER TABLE IF EXISTS USERS ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS USERS ADD PRIMARY KEY (ID);

ALTER TABLE IF EXISTS ONEDB_SERVERS ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS ONEDB_SERVERS ADD PRIMARY KEY (ID);

ALTER TABLE IF EXISTS ONEDB_SERVER_groups ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS ONEDB_SERVER_groups ADD PRIMARY KEY (ID);

ALTER TABLE IF EXISTS ALERTING_INCIDENTS ALTER COLUMN ID SET NOT NULL;
ALTER TABLE IF EXISTS ALERTING_INCIDENTS ADD PRIMARY KEY (ID);
```
  Run the java command from the path where H2 db is present. Replace <path> with the path where onedb-explore-server.jar (2.0.1.1) is present.
```
java -cp <path>/onedb-explore-server.jar org.h2.tools.RunScript -url jdbc:h2:./h2db -script migrate.sql
```
2. Export the database into a zip file.
  Run the java command from the path where h2 db is present. Replace <path> with the path where onedb-explore-server.jar (2.0.1.1) is present.
```
java -cp <path>/onedb-explore-server.jar org.h2.tools.Script -url jdbc:h2:./h2db -script h2db.zip -options compression zip
```
Note: h2-1.4.192.jar can be used if onedb-explore-server.jar (2.0.1.1) is not available. If you are using h2-1.4.192.jar, replace “<path>/onedb-explore-server.jar” with “<h2_path>/h2-1.4.192.jar” in the above commands.
Create new H2 database and import the data:
Rename the existing h2db.mv.db database:
```
rename h2db.mv.db h2db_old.mv.db
```
Run the java command from the path where H2 database file is present. Replace <path> with the path where onedb-explore-server.jar (2.0.1.2) is present.
```
java -cp <path>/onedb-explore-server.jar org.h2.tools.RunScript
          -url jdbc:h2:./h2db -script h2db.zip -options compression zip
```
Optional: Clean the migration files by deleting h2db.zip , h2db_old.mv.db and migrate.sql files.
Note: If h2.encrypt.enable & h2.encyrpt.password parameters are set in onedb-explore-server.properties, then all the URL needs to be modified to include those values.
Example 1: If h2.encrypt.enable=true and h2.encyrpt.password=password123, H2 database URLs should be modified as below:
(Add a space after the password, since the format is <file_password> space <user_password>)
```
java -cp <path>/onedb-explore-server.jar org.h2.tools.RunScript -url “
jdbc:h2:./h2db;CIPHER=AES” -password "password123 " -script migrate.sql
```
.
Example 2: If h2.encrypt.enable=true , h2.encyrpt.password=password123 and h2.encrypt.algorithm=XTEA, h2 database URLs should be modified as below:
```
java -cp <path>/onedb-explore-server.jar org.h2.tools.RunScript -url “
jdbc:h2:./h2db;CIPHER=XTEA” -password "password123 " -script migrate.sql
```

Results

Once all the above steps are completed without any error, data is successfully migrated to h2db.mv.db.