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:
- 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
- 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.