End Google Tag Manager (noscript) -->
USA +1 866 806 2068 | UK +44 203 1292 513 | JAPAN +81 3 45208991 担当:萩原 info@openlm.com

Database migration Firebird to Oracle 2.0 – AN3008b

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.

Back up the database

Backing-up the database is mandatory, since the database upgrade process is sensitive to hardware and software malfunctions. Upgrade the OpenLM Server without backing up the database file may compromise the accumulated data. Stop the “OpenLM Server” Windows’ Service, and backup the database file, typically located at “C:\Program Files (x86)\OpenLM\OpenLM Server\db”.

Prerequisites

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

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

3. Copy the following DLLs 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

Older versions

Migrating older versions of OpenLM (e.g. 1.8.x.x) Firebird Databases to the latest version on Oracle is done in 2 stages:

  1. Upgrading to the latest Firebird database.
  2. Migrating the updated Firebird database to an external database.

1. 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.1 Please contact OpenLM Support to obtain the DDL scripts package.

The Downloaded zip file contains files for MS-SQL, MySQL and Oracle servers. In this document we will make use of the Oracle files.

1.2 Open the Oracle Database Control Application from the Windows start button. Use only Microsoft’s Internet Explorer.

2

1.3 Create a new OpenLM Schema in the Oracle database

1.3a Create a new User:

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

 

3

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

  • Roles: CONNECT
  • RESOURCE
  • System Privileges: add all System Privileges.

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

Drag the “OpenLM_Oracle_2.0.0.0.sql” file from the downloaded directory over to the Oracle SQL developer window. The Contents of “OpenLM_Oracle_2.0.0.0.sql” is displayed. Run the SQL script. The OpenLM 2.0 schema is created in Oracle.

2. Data Migration

Now we get to the actual migration of data from Firebird to SQL Server.

2.1. Make sure that the Firebird database is backed up, according to the “Backup the current OpenLM database section” above.

2.2. Stop the OpenLM Server service.

2.3. Open the “OpenLM Data Migration” tool.

2.4. On the Source frame:

2.4a. Select “Firebird”. The “Database Credentials” window appears.

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

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

1

2.5. Target frame actions:

2.5.a. Type in the Oracle Server name.

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

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

2.5.d. The Oracle default port is set to 1521

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

4

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

3. 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).

  1. Click the DB provider drop-down menu, and select ‘Oracle’.
  2. Fill in the information in the blank text boxes. See example below.
  3. Click “Check” to check the OpenLM server’s connection to the Oracle DB
  4. Click “Apply” to finish the configuration.

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

5

Epilog

That’s it. The database has been migrated from Firebird to Oracle.

It is now recommended to

  • Stop and start the OpenLM Server service and
  • Perform a sanity – check to the OpenLM system’s functionality; Open the EasyAdmin web application, and review the License servers’ status and usage reporting windows.