Cybersecurity
DevOps Cloud
IT Operations Cloud
How to change database servers in Octane (MSSQL to MSSQL or Oracle to Oracle)
Note: switching from Oracle to MSSQL or vise-versa is not supported with Octane CP10 or earlier and may not be possible for newer versions
Before proceeding stop the Octane service
1) Backup the "conf" folder in case something goes wrong...
Typical Windows path: <drive>\octane\conf
Typical Linux path: opt/octane/conf
2) Understand the name of the Octane site admin db/schema name. Open the file "setup.xml" in the Octane "conf" folder for editing...
Typical Windows path: <drive>\octane\conf\setup.xml
Typical Linux path: opt/octane/conf/setup.xml
3) Locate the value in the "SchemaName" element, for example...
<entry key="SchemaName">octanea_sa</entry>
4) Make note of the db/schema name as it will be used in Step 9
5) Locate the value in the "ConnectionString element, make note of the value as this will be used in Step 13.
For example...
MSSQL...
<entry key="ConnectionString">jdbc:mercury:sqlserver://tm-sql2014:1433</entry>
Oracle...
<entry key="ConnectionString">jdbc:mercury:oracle://tm-ora12b:1521;servicename=orcl.americas.corp.net</entry>
6) Make note of the existing value as this will be used in Step 13.
7) Change the hostname and port, if necessary, to specify the new database server, for example...
MSSQL...
<entry key="ConnectionString">jdbc:mercury:sqlserver://tm-sql2014:1433</entry>
Oracle...
<entry key="ConnectionString">jdbc:mercury:oracle://tm-ora12c:1521;servicename=orcl.americas.corp.net</entry>
Save the changes to the "setup.xml" file
9) Backup the Octane db/schema on the source database - refer to the db/schema name from Step 4
10) Restore the Octane db/schema backup from Step 9 on the target database
Note: when using MSSQL with SQL authentication issue the following queries to the site admin schema to assign schema ownership...
EXEC sp_change_users_login 'Report'
EXEC sp_change_users_login 'Update_One', 'octane', 'octane'
11) On the target database server where the Octane site admin db/schema is now restore, issue the following query...
select * from DB_SERVER
12) One record should be returned. If multiple records stop here and contact customer support.
13) Update the table using the following query. For the "old_value" use the value from Step 6. For the "new_value" use the value from Step 5
update DB_SERVER set DB_CONNECTION_STRING = '<new_value>' where DB_CONNECTION_STRING = '<old_value>'
For example...
MSSQL...
update DB_SERVER set DB_CONNECTION_STRING = 'jdbc:mercury:sqlserver://tm-sql2014:1433' where DB_CONNECTION_STRING = 'jdbc:mercury:sqlserver://localhost:1433'
Oracle...
update DB_SERVER set DB_CONNECTION_STRING = 'jdbc:mercury:oracle://tm-ora12c:1521;servicename=orcl.americas.corp.net' where DB_CONNECTION_STRING = 'jdbc:mercury:oracle://tm-ora12b:1521;servicename=orcl.americas.corp.net'
14) Start the Octane service
15) Validate that Octane is now pointing to the new database server
a) Open a browser and use the URL: http(s)://<octane_host>:<port>/ui?site
For example: http://myoctane:8080/ui?site
b) Select the "Servers" tab
c) Check the "Database Server" item and make sure this is the target database server
16) Finally, check each workspace and make sure it is accessible