Application Note 2008b: OpenLM database migration, from Firebird 1.7 to Oracle 1.8

General

OpenLM is a license management tool that provides application usage monitoring for a variety of Concurrent license servers. These include FlexLM / Flexnet, Sentinel RMS (Aspentech SLM), DSLS, IBM LUM, Sentinel HASP, LM-X (Altair), Reprise RLM, Beta-LM, Mathematica LM (MathLM), and Easycopy.

Scope

OpenLM provides an embedded Firebird database  as part of the software installation. It also supports different types of external databases, such as MS-SQL, Oracle and MySQL. This document is intended for system administrators who wish to perform an OpenLM database migration from the default Firebird to Oracle. It elaborates the process and tools required for such a migration. This document is closely related to the “Guide to OpenLM Data Migration Tool”, “OpenLM Database Configuration Form”  and “Backing Up Your OpenLM DB” documents, which are recommended for further reading.

Backup the current OpenLM database

It is good practice to begin with backing up the OpenLM database prior to migrating its content. In order to do so:

1. Navigate to the windows services (you can do that by searching for services.msc in the start menu’s search line).

2. Select the “OpenLM Server” service, and Click the Stop icon.

3. Backup the OpenLM database. Please refer to the “Backing Up Your OpenLM DB” document for a more detailed explanation.

Prerequisites

1. Download “ODAC 11.2 Release 5 (11.2.0.3.20) with Xcopy Deployment” for 64 or 32 bit from oracle.com.

32b: http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html

64b: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html)

2. Unzip the file downloaded in Step 1. The ODAC1120320Xcopy_x64\32 folder is created.

3. Copy the following Dll’s to two locations:

3a. The Openlm server installation folder \bin directory

(usually located in: “C:\Program Files (x86)\OpenLM\OpenLM Server\bin”), and

3b. The data migration folder:

(usually located in: “C:\Program Files (x86)\OpenLM\OpenLM Data Migration”).

  • ODAC1120320Xcopy_x64\instantclient_11_2\oci.dll

  • ODAC1120320Xcopy_x64\instantclient_11_2\ociw32.dll

  • ODAC1120320Xcopy_x64\instantclient_11_2\orannzsbb11.dll

  • ODAC1120320Xcopy_x64\instantclient_11_2\oraocci11.dll

  • ODAC1120320Xcopy_x64\instantclient_11_2\oraociei11.dll

  • ODAC1120320Xcopy_x64\instantclient_11_2\orasql11.dll

  • ODAC1120320Xcopy_x64\odp.net4\bin\OraOps11w.dll

Important note: Planning the workflow: Firebird 1.7 to Oracle 1.8

There are two routes for migrating Firebird 1.7 to Oracle 1.8:

Workflow 1: Migrate Firebird 1.7 to Oracle 1.7, and then upgrade Oracle 1.7 to Oracle 1.8

Workflow 2: Upgrade Firebird 1.7 to Firebird 1.8, and then migrate Firebird 1.8 to Oracle 1.8.

We will relate to these two routes throughout this document.

Workflow 2: Upgrade Firebird 1.7 to Firebird 1.8

Upgrading of the Firebird database OpenLM Server version 1.7 to Firebird database OpenLM Server version 1.8 is done by simply installing the later OpenLM Server version over the old one. It is important to keep a copy of the database prior to this upgrade. For more information, please refer to this document:

Application Note 1101: Upgrade guide of OpenLM Version 1.7 to Version 1.8

Workflows 1 & 2: Creating a new Oracle Database (Building the tables)

Create a new empty database with OpenLM tables in Oracle. In order to do so, OpenLM supplies dedicated DDL scripts (*.SQL files) to be applied in the Oracle Database control application.

1. For Workflow 1: The DDL scripts for Oracle 1.7 databases are available for download here. The Downloaded file contains two files for Oracle servers.

For Workflow 2: The DDL scripts for Oracle 1.8 databases are available for download on the OpenLM site. After filling in your name and email, you would be directed to the download section. Select the OpenLM Version 1.8 section, and choose the DDL scripts. The Downloaded file contains two files for Oracle servers.

2. Open the Oracle Database control application from the Windows start button. Use only Microsoft’s Internet Explorer.

3. Create a new OpenLM Schema in the Oracle database

3a. Create a new User:

3b. Select the Server tab, and click the “Users” link (see below).

3c. Create a new user. Add the following Roles and System Privileges to the user:

  • Roles: CONNECT

  • RESOURCE

  • System Privileges: add all System Privileges.

4. Open the Oracle SQL developer, and create a new connection. Log in to that connection with the previously defined Username and Password.

For workflow 1: Drag the “OpenLM_Oracle.sql” file from the downloaded directory over onto the Oracle SQL developer window. The Contents of “OpenLM_Oracle.sql” is displayed.

Run the SQL script. The OpenLM 1.7 schema is created in Oracle.

For workflow 2: Drag the “OpenLM_Oracle_1.8.0.sql” file from the downloaded directory over to the Oracle SQL developer window. The Contents of “OpenLM_Oracle_1.8.0.sql” is displayed. Run the SQL script. The OpenLM 1.8 schema is created in Oracle.

Workflows 1 & 2: Data Migration

Now we get to the actual migration of data from Firebird to Oracle:

1. Make sure that the original Firebird DB is backed up, according to this document.

2. Open the “OpenLM Data Migration” tool.

For workflow 1: Data migration is done from Firebird 1.7 → Oracle 1.7. Use the data migration 1.7 version.

For workflow 2: Data migration is done from Firebird 1.8 → Oracle 1.8. Use the data migration 1.8 version.

3. On the Source frame:

3a. Select “Firebird”. The “Database Credentials” window appears:

3b. Enter the Firebird database path in the appropriate text box.

3c. Select your database(*.FDB) and enter the username and password. By default, the username is set to ‘sysdba’ and the password is ‘masterkey’

4. Target frame actions:

4a. Type in the Oracle Server name.

4b. Select the ‘Oracle’ radio button. The “Database Credentials” window appears.

4c. In the “Oracle Service” text box type in the Oracle instance name.

4d. The Oracle default port is set to 1521

4e. Set the Username and Password, and click OK.

5. After configuring the Source and Target databases, click the ‘Transfer’ button to begin the migration process. At the end of the migration, a notifier window appears.

Please refer to the “Guide to OpenLM Data Migration Tool” document for further information.

Workflow 1: Upgrade Oracle 1.7 to Oracle 1.8

Upgrading the Oracle 1.7 database to 1.8 is done by applying the 2nd SQL script (‘OpenLM_Oracle_1.8.0_Upgrade.sql’) downloaded as part of the “Workflows 1 & 2: Creating a new Oracle Database (Building the tables)” section above. Drag it to the Oracle SQL developer window, and run it. The OpenLM Oracle schema is upgraded to version 1.8.

Configuring Openlm to work with the Oracle database

Now the OpenLM server must be made to refer to the new Oracle database.

1. Open the “OpenLM Database Configuration” tool:

(Start → All Programs → OpenLM → Server → OpenLM Database configuration).

2. Click the DB provider drop-down menu, and select ‘Oracle’.

3. Fill in the information in the blank text boxes. See example below.

4. Click “Check” to check the OpenLM server’s connection to the Oracle DB

5. Click “Apply” to finish the configuration.

Please refer to “OpenLM Database Configuration Form” for further information.

Revision

Rev 1.1, September 11 2013.

Please follow and like us: