The SQL2ORA Migrator migrates PANTHEON 5.5 databases for MS SQL Server to Oracle Database.

PANTHEON 5.5 for SQL Server must be upgraded to the latest build before the Migrator is run.

The migration process requires:

• PANTHEON 5.5 for SQL Server upgraded to the latest build,
• Oracle Database installed,
• SQL2ORA Migrator.

The migration is accomplished in steps:

• login to both database servers,
• create PANTHEON user and database on Oracle,
• copy data from SQL Server to Oracle database and create required Oracle objects,
• run PANTHEON for Oracle.

SQL2ORA Migrator has four tabs and a navigation bar. Each tab is for one of the abovementioned steps.

The navigation bar has the following buttons:

• Process - run the processes associated with the current tab
• Back - go to the previous tab
• Next- go to the next tab
• Exit - leave the Migrator

Connect Tab

The Connect tab is divided into the SQL and the ORA sections, each for connecting to the corresponding database server. Both have a checkbox that denotes whether you want to connect to that database server. Both checkboxes must be checked in order to complete the whole migration process, but if you want to, say, create indexes on Oracle you don't need the SQL connection.

• SQL
  • Server - the name of the computer running SQL Server and the SQL Server instance.
  • Database - the name of the PANTHEON 5.5 database.
  • Username - the user name for logging in to SQL Server. It can be a PANTHEON user or the SQL Server sa user.
  • Password - the password for logging in to SQL Server or PANTHEON.
• ORA
  • Host - the name of the computer running Oracle Database where you want to have the migrated PANTHEON database.
  • Port - the port that Oracle Database is listening to.
  • SID - the Oracle System ID of Oracle Database. By default, this is XE for Express Edition and ORCL for other editions.
  • Username - the Oracle Database user with sufficient privileges for creating users and adding schemes. This is usually sys or system.
  • Password - the password of the Oracle Database user.
  • Connection Type
    • Oracle Direct - connect using the Oracle client integrated into the migrator.
    • Oracle Client - connect using a separately installed Oracle client. Oracle Instant Client is usually sufficient.

Create Tab

The Create tab allows you to create an Oracle Database user and schema, each in its own section. Each section can be deactivated as needed, allowing you to create only a database with no users, or create multiple users by repeating the process (with schema creation deactivated).

• ORA Users
  • Username - the PANTHEON user name that should be created.
  • Password - the password for the PANTHEON user
  • Create all users from database - if checked, all users that exist in the SQL Server database will be created in the migrated database.
• ORA Schema
  • Name - the name of the schema that should be created on Oracle Database. In contrast to MS SQL Server, Oracle Database allows only one database on each instance, but a database can have many schemas. Schemas are represented as additional users on Oracle Database.
  • Tablespace - the name of the tablespace in which you want to create the schema. This is the USERS tablespace by default, but it is generally recommended to create a separate one only for PANTHEON.

Migrate tab

The Migrate tab migrates data tables and prepares programmability objects. Each of the options can be deactivated as needed.

• Tables
  • Create Tables - create Oracle data tables.
  • Copy Rows - copy table rows from SQL Server to Oracle.
  • Create Indexes - create indexes for tables.
  • Create Foreign Keys - create foreign keys for maintaining referential integrity.
• Programmability
  • Supporting Objects - create predefined temporary tables ad other auxilliary objects like sequences, temp table triggers, etc.
  • ORA Objects - create stored procedures, user-defined functions, views, etc.
  • ORA InterActives - create ARES procedures.
  • Validation - check created objects and check for existence of dependent objects.
• Settings
  • Pantheon.ini - if checked PANTHEON's .ini file will be altered to connect to the server specified in the Connect tab.

Finish Tab

The Finish tab allows you to specify where Pantheon55_ORA.exe (the PANTHEON 5.5 executable for Oracle) is located.

• Execute
  • Pantheon 5.5 ORA - the full path and file name to the PANTHEON 5.5 executable for Oracle.

Finishing the Migration

The Migrator creates a log of the migration process in the folder that it resides in. The log contains a list of performed operations and any encountered errors. The log is written to the disk when quitting the Migrator. It is recommended to review the log after every use of the migrator.

It is strongly recommended to upgrade PANTHEON to the latest Oracle version after the migration.