Database Administrator’s Guide

Introduction

The present document offers a detailed description of migration-center requirements specifically related to the Oracle database management system it uses as the backend.

The Installation Guide contains a full description of the system requirements and installation steps for all migration-center components and should be consulted first. This document does not substitute the Installation Guide, but completes it with information that might be of importance for Oracle database administrators, such as the various packages, privileges and other Oracle specific features required and/or used by migration-center.

Targeted audience

This document is targeted specifically at Oracle database administrators about to deploy migration-center in corporate environments where databases need to adhere to strict internal guidelines and policies, and where the default database installation procedure used by migration-center may need to be reviewed first, or possibly adapted to meet specific requirements before deploying the solution.

The migration-center Database

The migration-center database stores all information generated during a migration project. This includes job configurations, object metadata, transformation rules, status and history information, etc.

One exception here is the actual document’s/file’s content, which is not stored in the database and is of no relevance to database deployment or configuration.

Requirements

Please consult the migration-center Installation Guide for the complete information regarding installation steps, recommended settings and configuration options for Oracle. Below is just an excerpt of the essential requirements, not a full overview of the installation process.

Hardware Requirements

  • 2-4GB of RAM should be assigned to the Oracle database instance (not the entire Oracle server) where the migration-center schema will be deployed. The Oracle specific memory allocation settings can be left at their defaults.

  • For storage requirements and sizing of Oracle datafiles see section Oracle Tablespaces below.

  • There is no differentiation between physical or virtual hardware in terms of requirements; faster physical hosts may be required to compensate for losses in I/O performance or latency if running the database server on virtual machines.

Software Requirements

  • Oracle RDBMS version: 10g R2 - 19c Architecture: both 32bit or 64bit are supported Edition: Developer or Enterprise Edition Oracle Express Edition (Oracle XE) is not supported in production use due to its limitations!

  • Ideally a separate Oracle database instance should be designated for use by migration-center alone, rather than an instance shared with other database applications. This would help performance and related troubleshooting if necessary as there wouldn’t be multiple applications and thus multiple potential problem sources to investigate. From a purely technical perspective sharing a database instance with other database applications poses no problems.

  • The operating system the Oracle Server is running on is of no relevance to migration-center, as all communication with the database happens at a higher level, independently of the underlying OS or hardware architecture. All operating systems officially supported by the Oracle versions mentioned above are also supported by migration-center.

  • Please consult the appropriate Oracle documents for more information on operating systems supported by Oracle databases.

Oracle Schema

The migration-center database schema can be deployed on any Oracle database instance meeting the requirements and setting described in the migration-center Installation Guide

  • The schema is named FMEMC. The schema name is fixed cannot be changed; as a consequence it is also not possible to have more than one schema installed on the same database instance.

  • The schema is home to all database objects created and used by migration-center, such as tables, stored procedures, Java code, etc. As part of the schema the user FMEMC is created and granted access to all necessary packages, objects and privileges (also see section Oracle Privileges). For storing data and indices two tablespaces are also created for use by FMEMC (also see section Oracle Tablespaces).

Oracle Tablespaces

  • The tablespace meant for storing user data is called FMEMC_DATA and will store information such as job configurations, object metadata, transformation rules, status and history information, etc.

  • A separate tablespace called FMEMC_INDEX is used for storing indices of indexed fields

Much like the schema name, the tablespace names are fixed and cannot be changed.

Oracle Datafiles

  • By default each of the two tablespaces mentioned above store information in 2 data files

  • The data files are set to autoextend by default

  • The data files can be customized in terms of count, size, storage location, and whether autoextend is allowed or not. With the autoextend option disabled the free space within the data files needs to be monitored and extended accordingly during the migration process to prevent the tables from filling up and stalling the migration process.

The above must be changed in the installation scripts and cannot be set through the regular setup program’s GUI.

  • The primary factor for sizing the Oracle data files is the number of objects planned to be migrated using the respective database instance, as well as the number of attributes stored per object, and the length of the respective attribute values; these factors can (and do) of course vary from case to case.

For more comprehensive information on typical migration project sizes (from small to very large) and the requirements in terms of storage space resulting from there, as well as general recommendations for sizing the database in preparation of an upcoming migration project please consult the Sizing Guide.

Oracle Privileges

This chapter details the privileges required by migration-center for accessing the Oracle packages and various functionalities it needs to work properly. By default these privileges are granted to the FMEMC user during installation. After database installation has completed successfully, it is possible to log in with the user FMEMC and connect to the migration-center database in order to start using the solution.

Privileges Required For Installation

The regular installation process using the setup program with GUI requires logging on as SYS. Since this might not be possible in some environments, there is the alternative of customizing the critical part of the setup and having an administrator execute the appropriate scripts.

This would generally involve having an administrator customize and runs the scripts for creating the user FMEMC, tablespaces, data files, etc., and then run the rest of the setup by running the setup program using the previously created user FMEMC for connecting to the database instead of SYS. In this case the below permissions must be granted to user FMEMC in order for the setup program to determine whether migration-center tablespaces and/or data files already exist on the instance where the database is supposed to be installed.

GRANT SELECT ON SYS.DBA_DATA_FILES TO FMEMC;

GRANT SELECT ON SYS.DBA_TABLESPACES TO FMEMC;

Privileges for Various Functionalities

  • Encrypting passwords saved by migration-center

  • Read current database configuration from view v$instance (host name and database instance name)

  • Execute core migration-center job-based functionalities such as transformation, validation, or scheduled migration as native Oracle Jobs

  • migration-center Transformation Engine (process migration-center data by applying transformation rules and altering object metadata)

GRANT CONNECT, RESOURCE TO FMEMC;

GRANT CREATE JOB TO FMEMC;

GRANT SELECT ON SYS.V_$INSTANCE TO FMEMC;

GRANT CREATE VIEW TO FMEMC;

GRANT SYS.DBMS_LOCK TO FMEMC;

GRANT SYS.UTL_RAW TO FMEMC;

GRANT SYS.UTL_ENCODE TO FMEMC;

GRANT SYS.DBMS_OBFUSCATION_TOOLKIT;

These privileges must be granted for migration-center to operate normally. It is not possible for the solution to offer even the core functionalities without having been granted the above privileges!

Privileges for migration-center Scheduler

  • Start scheduled migration jobs at the specified time using Oracle’s built-in scheduler GRANT SYS.DBMS_SCHEDULER TO FMEMC;

  • Establish network communication from scheduler to Job Server over TCP GRANT SYS.UTL_TCP TO FMEMC;

  • Allow the scheduler to send notification emails to configured users about outcome of scheduled migration jobs GRANT SYS.UTL_SMTP TO FMEMC;

Privileges on packages SYS.UTL_TCP and SYS.DBMS_SCHEDULER must be granted for the scheduler functionality to work. The SYS.UTL_SMTP package is required for sending notification emails to configured users summarizing the outcome of scheduled migration jobs. Since this can also be checked directly in the mc Client application at any time, sending notification emails is an optional feature, therefore access to the SYS.UTL_SMTP package is not mandatory.

Preparing the Database for Setup Using Scripts

As mentioned above, parts of the installation process can be controlled via installation scripts. These can be used for preparing the database manually by an administrator and finally running the regular setup program connected with the migration-center user FMEMC instead of SYS which may not be allowed to be used.

Database scripts

The script files used during setup are located in the Database\util folder of the migration-center installation package. These files can be used to adapt the setup procedure to some degree to specific requirements of the customer’s environment. The individual files and their role in the migration-center database installation process are described in the table below.

Script file name (located in Database\util)

Description

Executable

Editable

create_tablespaces_custom_location.sql

Tablespace and data file creation and configuration script

Y

Y

create_tablespaces_default_location.sql

Tablespace and data file creation and configuration script using database instance’s default location for data files

Y

Y

create_user_fmemc.sql

User creation script; this is where the user is created and privileges are granted to the user

WARNING! Do not change the user name!

Y

Y

drop_fmemc_schema.sql

Drop user/schema fmemc and all objects owned by fmemc WARNING! This will delete all migration center data from the database instance!

Y

N

Instructions for preparing the database manually and performing the installation

  • Decide whether to use a custom or the default location for the data files. Pick create_tablespaces_custom_location.sql or create_tablespaces_default_location.sql accordingly.

  • Review/adapt the selected tablespace creation script

  • Execute the tablespace creation script

  • Review/adapt the user creation script (do not change user name!)

  • Execute the user creation script

  • Run the migration-center database installation program (Database\InstallDataBase.exe) and proceed as described in the installation guide. Exception to the Installation Guide: log in as the previously created user FMEMC instead of user SYS!

  • Verify the database installation log after installation

  • Log on to migration-center using the migration-center Client and verify basic functionalities

  • Use the drop_fmemc_schema script to drop the entire schema in case of an unsuccessful installation, or if the schema needs to be removed for other reasons. WARNING! This will delete all migration center data from the database instance! Make sure the schema has been backed up before dropping a schema that already contains user data created by migration-center.

Last updated