Oracle® Database SQL Developer User's Guide Release 1.1.2 Part Number B31695-02 |
|
|
View PDF |
Beta Draft
Note:
The information about migration applies only if you are using a version of SQL Developer that supports migration of third-party databases to Oracle.Migration is the process of copying the schema objects and data from a source third-party (non-Oracle) database, such as MySQL, Microsoft SQL Server, or Microsoft Access, to an Oracle database. You can perform the migration in an efficient, largely automated way: the source schema objects are implemented as Oracle schema objects, and any related objects (such as sequences and triggers) that are required for the implementation are automatically created.
Thus, you have two options for working with third-party databases in SQL Developer:
Creating database connections so that you can view schema objects and data in these databases
Migrating these databases to Oracle, to take advantage of the full range of Oracle Database features and capabilities
This chapter contains the following major sections:
Section 2.1, "Migration Quick Start"
Section 2.2, "Overview of Migration"
Section 2.3, "Preparing a Migration Plan"
Section 2.4, "Before You Start Migrating: General Information"
Section 2.5, "Before You Start Migrating: Source-Specific Information"
Section 2.6, "Capturing the Source Database"
Section 2.7, "Creating and Customizing the Converted Model"
Section 2.8, "Generating the DDL for the Oracle Schema Objects"
Section 2.10, "Testing the Oracle Database"
Section 2.11, "Deploying the Oracle Database"
Section 2.12, "Using Migration Reports"
Section 2.13, "SQL Developer User Interface for Migration"
To migrate a third-party database to Oracle, the basic actions are: prepare for the migration, create or select a migration repository, capture the source database, convert the captured database, generate and run DDL for the new Oracle schema objects, and optionally move data from the source database to the new database. (Some steps after step 1 can be performed in a different order.)
Prepare for the migration by reading the appropriate related topics in Chapter 2, "Migrating Third-Party Databases".
Create a migration repository in a new or existing Oracle connection. You may find it simple and convenient to create separate Oracle database user and connection for migration work. Then, select the connection and create the repository. For example:
Create an Oracle user named MIGRATIONS with default tablespace USER and temporary tablespace TEMP; and grant it at least RESOURCE, CREATE SESSION, and CREATE VIEW privileges.
Create a database connection named Migration_Repository that connects to the MIGRATIONS user.
Right-click the Migration_Repository connection, select Manage Migration Repository, and create the repository.
Create an Oracle user whose schema is to be used as the destination for the objects to be migrated, or use an existing Oracle user and schema. Grant sufficient privileges to this user.
For example, if you plan to migrate a Microsoft Access database named sales.mdb, you might create an Oracle user named SALES, in whose schema the Oracle database objects will be generated.
Create and open an Oracle connection for the schema that you created or selected in the preceding step.
For example, create an Oracle connection named Sales_Migrated to the schema associated with user SALES, and connect to it.
Create and open a database connection for the third-party database.
For example, create a database connection named Sales_Access to the Microsoft Access database named sales.mdb, and connect to it.
Capture the source schema objects to be migrated. The specific steps depend on whether you are migrating a Microsoft Access database or another type of database:
For Microsoft Access migrations, run the exporter tool: click Migration, then Microsoft Access Exporter, then the item for your version of Microsoft Access. Follow the steps for the exporter tool, which has its own online help. Then, in SQL Developer click Migration, then Capture Exporter XML, and specify the XML file that you created using the exporter tool.
For other third-party database migrations, right-click the connection name in the Connections navigator, and select Capture product-name (for example, Capture MySQL or Capture Microsoft SQL Server).
After the capture, the Captured Objects navigator displays an expandable node for the captured objects (for example, sales (Access) for the captured sales.mdb objects, as shown in the figure in Section 2.13, "SQL Developer User Interface for Migration").
Convert the captured objects to Oracle-format objects: right-click the appropriate node in the Captured Objects navigator and select Convert to Oracle, and specify or accept the defaults for data mappings.
After the conversion, the Converted Models navigator displays an expandable node for the converted objects (for example, Converted sales (Access)).
Generate a SQL*Plus script that creates the DDL statements to create the Oracle database objects that correspond to the source database objects: right-click the appropriate node in the Captured Models navigator and select Generate. A SQL Worksheet window opens containing the SQL*Plus statements.
In the SQL Worksheet window that was just opened, select (in the drop-down list on the right) an Oracle database connection in which to run the script (next step).
Examine the generated SQL*Plus statements, and optionally make any changes. For example, if the database user to own the generated objects already exists (as it will if you are following these quick-step instructions), delete or modify the CREATE USER and related statements.
Click the Run Script button in the SQL Worksheet window to run the script.
Disconnect from the connection to the Oracle user for the generated objects (for example, Sales_Migrated), then reconnect to it. In the Connections navigator, you should now see the new database objects corresponding to the objects in the third-party database that you migrated.
If you want, you can migrate (move) any existing data from the source database to the Oracle database. You have two options for data migration: online (move the data now) or offline (move the data later).
Online data move: Click Migration, then Migrate Data. In the dialog box, specify the Source Connection and Captured Model, and the Target Connection and Converted Model.
Offline data move: Click Migration, then Generate Offline Data Move Scripts; specify the converted model and a directory into which to generate the files that you will use for unloading the data from the source database and for importing into Oracle using SQL*Loader.
An Oracle database provides you with better scalability, reliability, increased performance, and better security than third-party databases. For this reason, organizations migrate from their current database, such as Microsoft SQL Server and Microsoft Access, to an Oracle database. Although database migration can be complicated, SQL Developer enables you to simplify the process of migrating a third-party database to an Oracle database.
SQL Developer captures information from the source database and displays it in the captured model, which is a representation of the structure of the source database. This representation is stored in a migration repository, which is a collection of schema objects that SQL Developer uses to store migration information.
The information in the repository is used to generate the converted model, which is a representation of the structure of the destination database as it will be implemented in the Oracle database. You can then use the information in the captured model and the converted model to compare database objects, identify conflicts with Oracle reserved words, and manage the migration progress. When you are ready to migrate, you generate the Oracle schema objects, and then migrate the data.
SQL Developer contains logic to extract data from the data dictionary of the source database, create the captured model, and convert the captured model to the converted model.
Using SQL Developer to migrate a third-party database to an Oracle database provides the following benefits:
Reduces the effort and risks involved in a migration project
Enables you to migrate an entire third-party database, including triggers and stored procedures
Enables you to see and compare the captured model and converted model and to customize each if you wish, so that you can control how much automation there is in the migration process
Provides feedback about the migration through reports
The components of SQL Developer work together to migrate a third-party database to an Oracle database. Figure 2-1, "SQL Developer Migration Architecture" shows how SQL Developer reads the information from the source database and creates the Oracle database schema objects. SQL Developer uses the information stored in the migration repository to migrate to the Oracle schema. You can make changes to the captured model or the converted model, or both, before migrating. The information in the converted model is used to complete the migration, that is, to generate the database objects in the destination Oracle schema.
Migration support is implemented in SQL Developer as a set of extensions: one for Oracle Migration Workbench, and one "plugin" for each supported third-party database. If you want, you can disable migration support or support for individual plugins.
To view the installed extensions, and to enable or disable individual extensions, click Tools, then Preferences, then Extensions.
This topic describes the process of how to create a migration project plan. It identifies the sections to include in the migration plan, describes how to determine what to include for each section, and explains how to avoid the risks involved in a migration project. This information includes:
In this task, you identify which databases you want to migrate and applications that access that database. You also evaluate the business requirements and define testing criteria.
To determine the requirements of the migration project:
Define the scope of the project.
There are several choices you must make about the third-party database and applications that access that database in order to define the scope of the migration project. To obtain a list of migration issues and dependencies, you should consider the following
What third-party databases are you migrating?
What is the version of the third-party database?
What is the character set of the third-party database?
What source applications are affected by migrating the third-party database to an Oracle database?
What is the third-party application language?
What version of the application language are you using?
In the scope of the project, you should have identified the applications you must migrate. Ensure that you have included all the necessary applications that are affected by migrating the database
What types of connectivity issues are involved in migrating to an Oracle database?
Do you use connectivity software to connect the applications to the third-party database? Do you need to modify the connectivity software to connect the applications to the Oracle database?
What version of the connectivity software do you use? Can you use this same version to connect to the Oracle database?
Are you planning to rewrite the applications or modify the applications to work with an Oracle database?
Use Table 2-1 to determine whether you have a complex or simple source database environment. Identify the requirements based on the specific scenario.
If the migration project is a simple scenario, you may not have to complete all of the tasks listed in this guide. You make decisions based on your specific environment. For example, if you have a complex scenario, you may require extra testing based on the complexity of the application accessing the database.
Table 2-1 Complex and Simple Scenarios
Complex Scenario | Simple Scenario |
---|---|
More than one of the following:
|
Contains the following:
|
Determine whether the destination database requires additional hardware and rewriting of backup schedules.
Define testing and acceptance criteria.
Define tests to measure the accuracy of the migration. You then use the acceptance criteria to determine whether the migration was successful. The tests that you develop from the requirements should also measure stability, evaluate performance, and test the applications. You must decide how much testing is necessary before you can deploy the Oracle database and applications into a production environment.
Create a requirements document with a list of requirements for the migration project.
The requirements document should have clearly defined tasks and number each specific requirement, breaking these into sub-requirements where necessary.
In this task, you use SQL Developer to make calculated decisions on the amount of work that can be automated and how much is manual.
To estimate the workload:
Capture the captured model, create the converted model, and migrate to the destination database.
You can analyze the source database through the captured model and a preview of the destination database through the converted model. After you have captured the source database, analyze the captured data contained in the captured model and the converted model. Ensure the content and structure of the migration repository is correct and determine how much time the entire process takes.
Generate and use reports to evaluate the capture and migration process, categorize the total number of database objects, and identify the number of objects that can be converted and migrated automatically.
The Summary, Detailed, and Error and Warnings reports provide information about the actions that have occurred and record any warnings and errors. They identify the changes that have been made to the converted model so that you can evaluate if you should make changes to the applications that access the destination database.
Evaluate and categorize the issues that occurred. The reports can help by providing information about:
Tables that did not load when you captured the source database
Stored procedures, views, and triggers that did not parse when you created the converted model
Syntax that requires manual intervention
Database objects that were not created successfully when you migrated the destination database
Data that did not migrate successfully when you migrated the destination database
For each error or warning in reports, evaluate the following:
Number of times an issue occurred
Time required to fix the issues, in man hours
Number of resources required to fix the issue
After you have solved a complex problem, it should be easier and quicker to resolve the next time you have the same problem.
Use these reports to evaluate each error and determine how long it would take to rectify. The reports help identify the size of the database that was created.
In this task, you analyze the operational requirements, as follows:
Evaluate the operational considerations in migrating the source database to a destination database. Consider the following questions:
Note:
If the scope of the migration project is a complex scenario as defined in Table 2-1, Oracle recommends that you answer all of these questions. If you have a simple scenario, determine the answers to the most appropriate questions.What backup and recovery changes do you require?
What downtime is required during the migration?
Have you met the performance requirements?
Are you changing the operational time window?
What effect does the downtime have on the business?
What training requirements or additional staff considerations are required?
Is it necessary to have the third-party and the Oracle database running simultaneously?
For each task, determine the resources and time required to complete.
Create an initial project plan.
Use the information that you have gathered during the requirements and planning stage to develop an initial project plan.
In this task, you identify the users of the applications that run on the source database, what hardware it requires, what the application does, and how it interfaces with the source database. You also analyze the method the application uses to connect to the database and identify necessary modifications.
Note:
If the migration project is a complex scenario as defined in Table 2-1, Oracle recommends that you answer all of these questions. If you have a simple scenario, determine the answers to the most appropriate questions.To analyze the application:
Determine whether changes to the application are required to make them run effectively on the destination database.
If changes are required to the application, determine whether it is more efficient to rewrite or modify the applications.
If you are rewriting the application to use the Oracle database, consider the following:
Create the necessary project documentation to rewrite the application. For example, you need a design specification and requirements documentation.
Rewrite the application according to the specification.
Test the application works against the Oracle database.
If you are modifying the application to use the Oracle database, consider the following:
Identify the number of connections to the database that are in the application and modify these connections to use the Oracle database.
You may need to change the connection information to use an ODBC or JDBC connection.
Identify the embedded SQL statements that you need to change in the application before you can test it against the Oracle database.
Test the application using the Oracle database.
Allocate time and resource to address each issue associated with rewriting or modifying the application.
Update the general requirements document for the project that you created in Task 1.
In this task, you evaluate the unknown variables that the migration project may contain, such as the difference in the technologies of the source database and the destination database. During the planning stage, you:
Estimate the budget constraints of the project
Gather information to produce a migration plan
Estimate how much time the migration project should take
Calculate how many resources are required to complete and test the migration
To plan a migration project:
Define a list of tasks required to successfully complete the migration project requirements of Task One.
Categorize the list of tasks required to complete the migration project.
You should group these tasks according to your business. This allows you to schedule and assign resources more accurately.
Update and finalize the migration project plan based on the information that you have obtained from Task 3 and Task 4.
Make sure the migration project plan meets the requirements of the migration project.
The migration plan should include a project description, resources allocated, training requirements, migration deliverable, general requirements, environment analysis, risk analysis, application evaluation, and project schedule.
You may need to perform certain tasks before you start migrating a third-party database to an Oracle database. See the following for more information:
See also any information specific to the source database that you will be migrating, as explained in Section 2.5.
Note:
Oracle recommends that you make a complete backup of the source database before starting the migration. For more information about backing up the source database, see the documentation for that type of database.SQL Developer requires a migration repository to migrate a third-party database to an Oracle database. To use an Oracle database for the migration repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to create a user named MIGRATIONS, create a database connection to that user, and use that connection for the migration repository; and if you wish, you can later delete the MIGRATIONS user to remove all traces of the migration from the database.
When you create a user for migrations, specify the tablespace information as in the following example, instead of using the defaults for tablespaces:
CREATE USER migrations IDENTIFIED BY migrations DEFAULT TABLESAPACE users TEMPORARY TABLESPACE temp,
Do not use a standard account (for example, SYSTEM) for migration.
When SQL Developer creates a migration repository, it creates many schema objects that are intended only for its own use. For example, it creates tables, views, indexes, packages, and triggers, many with names starting with MD_
and MIGR
. You should not directly modify these objects or any data stored in them.
The user associated with the Oracle database connection used to perform the migration (that is, to run the script containing the generated DDL statements) must have the following roles and privileges:
Note:
You must grant these privileges directly to a user account. Granting the privileges to a role, which is subsequently granted to a user account, does not suffice. You cannot migrate a database as the userSYS
.Roles
CONNECT WITH ADMIN OPTION RESOURCE WITH ADMIN OPTION
Privileges
ALTER ANY ROLE ALTER ANY SEQUENCE ALTER ANY TABLE ALTER TABLESPACE ALTER ANY TRIGGER COMMENT ANY TABLE CREATE ANY SEQUENCE CREATE ANY TABLE CREATE ANY TRIGGER CREATE VIEW WITH ADMIN OPTION CREATE PUBLIC SYNONYM WITH ADMIN OPTION CREATE ROLE CREATE TABLESPACE CREATE USER DROP ANY SEQUENCE DROP ANY TABLE DROP ANY TRIGGER DROP TABLESPACE DROP USER DROP ANY ROLE GRANT ANY ROLE INSERT ANY TABLE SELECT ANY TABLE UPDATE ANY TABLE
For example, you can create a user called migrations
with the minimum required privileges required to migrate a database by using the following commands:
CREATE USER migrations IDENTIFIED BY migrations DEFAULT TABLESAPACE users TEMPORARY TABLESPACE temp; GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE PUBLIC SYNONYM TO migrations WITH ADMIN OPTION; GRANT ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE, ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO migrations;
If you need to configure SQL Developer to use nonstandard character encoding, such as for multibyte character sets, on Microsoft Windows systems, follow these steps:
Set the JRE file encoding property as follows:
Using a text editor, open the SQL Developer startup file by editing the omwb.bat file located in the OMWB_install_dir/Omwb/bin directory.
Add the following information to the start command, before -jar
:
-Dfile.encoding="file_encoding"
In the previous line, file_encoding is the required file encoding. For multibyte Microsoft Access databases, the file encoding should be set to UTF-8, and the omwb.bat file should look similar to the following:
start ..\jre\bin\javaw -ms30m -mx256m -Dfile.encoding="UTF-8" -jar ..\lib\boot.jar oracle.mtg.migrationUI.MigrationApp
If you are migrating a multibyte Microsoft Access database (for example Japanese or Chinese), the character set on the target Oracle database instance should be set to UTF8.
Save the file, then exit.
Install the appropriate font.properties file for the required file encoding as follows:
Make a backup copy of your existing font.properties file in the OMWB_install_dir/Omwb/jre/lib directory.
Download the required Java font.properties file from the following Web site:
http://www.sun.com/
Rename the downloaded file to font.properties. For example, rename the Japanese font.properties file from font.properties.ja to font.properties.
Copy the new font.properties file into the OMWB_install_dir/Omwb/jre/lib directory.
If you are performing an offline capture, you must specify delimiter characters in the offline capture scripts as follows:
Using a text editor, open the omwb.properties file located in the OMWB_install_dir/Omwb/bin directory.
Edit or add the following fields:
OFFLINE_CAPTURE_COLUMN_DELIMITER="delimiter_column" OFFLINE_CAPTURE_ROW_DELIMITER="delimiter_row"
In the previous lines, delimiter_column is your choice of column delimiter and delimiter_row is your choice of row delimiter.
Note:
These delimiter values must correspond to the delimiter values used in the offline capture scripts.Save the file, then exit.
SQL Developer is now enabled to handle the new character encoding.
Depending on the third-party database that you are migrating to an Oracle database, you may have to configure connection information and install drivers. For more information about specific third-party database requirements, see the following:
To configure a Microsoft SQL Server database for migration:
Ensure that the source database is accessible by the Microsoft SQL Server user that is used by SQL Developer. This user must have DBA access to the Microsoft SQL Server database.
Ensure that you can connect to the Microsoft SQL Server database from the system where you have installed SQL Developer.
Install the Microsoft SQL Server ODBC driver release 3.70.06.23 or higher is installed on the same system as SQL Developer, as follows:
The Microsoft SQL Server ODBC driver is shipped with the Microsoft SQL Server client software. Installation procedures depend on the version of Microsoft SQL Server. For more information about the Microsoft SQL Server ODBC driver, see the Microsoft SQL Server specific documentation.
Set the Oracle ODBC Data Source as follows:
The Oracle ODBC Data Source is used to set up Data Source Names (DSN). You can create a DSN at the user or system level.
From the Start option, select Settings, then select Control Panel.
The Control Panel window appears.
If you are using Microsoft Windows 2000, select the Administrative Tools icon.
Select the Data Source (ODBC) icon.
The ODBC Data Source Administrator dialog appears.
From the User DSN tab, click Add.
The Create New Data Source dialog appears.
Select SQL Server, then click Finish.
The Create a New Data Source to SQL Server dialog box appears.
Type a name for the data source in the Name field.
From the drop-down list, select the SQL Server database you want to connect to or type it in, then click Next.
Verify the authenticity of the login ID, then click Next.
Review the options, then click Next.
Click Next.
Click Finish.
The ODBC Microsoft SQL Server Setup page appears.
Click the Test Data Source option to ensure that you have set up the ODBC connection correctly, then click OK.
If the connection fails, return to Step d and modify the connection details.
Click OK.
The DSN connection is now created.
To configure a Microsoft Access database for migration:
Make backup copies of the database file or files.
Ensure that the necessary software (Microsoft Access, Microsoft ODBC driver, Microsoft Data Access Objects, perhaps other components) is installed on the same system as SQL Developer.
If security is enabled, you should turn it off by copying the contents of the secured database into a new database, as follows:
SQL Developer does not support the migration of Microsoft Access databases that have security enabled. By default, SQL Developer uses the name of the Microsoft Access MDB file as the user name for the destination Oracle user. If you create an Oracle user in this way, the password is ORACLE.
From the File menu in Microsoft Access, select New Database.
Select the Blank Database icon, then click OK.
In the File New Database option, type a name for the database, then click Create.
From the File menu within the new database, select Get External Data, then select Import.
Select the secured Microsoft Access database that you want to import, then click Import.
From the Import Objects dialog, click Options.
Select the Relationships and Definition and Data options.
From the Tables tab, choose Select All.
Click OK.
All Microsoft Access objects are copied over to the new Microsoft Access database, except for the security settings.
If the application contains linked tables to other Microsoft Access databases, refresh these links by opening the application in Microsoft Access and performing the following:
From the Tools menu in Microsoft Access 97, select Add Ins, then select Linked Table Manager.
From the Tools menu in Microsoft Access 2000, select Database Utilities, then select Linked Table Manager.
Note:
SQL Developer supports linked tables to other Microsoft Access databases. SQL Developer automatically creates a user schema within the Oracle database for each Microsoft Access database migrated. However, Oracle recommends that you move all tables into a single Microsoft Access database to ensure that a single user schema is created in the destination database.Ensure that the Microsoft Access database is not a replica database, but a master database.
When you use the Exporter for Microsoft Access to export, an error message is displayed if the database is a replica. SQL Developer does not support the migration of a replica database.
From the Tools menu within Microsoft Access, select Database, then select Compact Database to compact the Microsoft Access database files.
Ensure that the Microsoft Access MDB file is accessible from the system where you have installed SQL Developer.
Use the Oracle Universal Installer to verify that you have the Oracle ODBC driver installed. If you need to install the driver, it is available on the Oracle Database Server or Database Client CD. You can also download the Oracle ODBC driver from the Oracle Technology Network (OTN) Web site:
http://www.oracle.com/technology/software/tech/windows/odbc/
You install the Oracle ODBC driver to allow Microsoft Access forms and reports to work with the migrated data. Install the Oracle ODBC driver into an Oracle home directory that contains the Oracle Net Services. You can obtain the Oracle Net Services from the Oracle Client or Oracle Database CD. You install Oracle Net Services to obtain the Net Configuration Assistant and Net Manager. These allow you to create a net configuration in the tnsnames.ora
file.
Note:
For more information about installing the networking products needed to connect to an Oracle database, see the installation guide for your Oracle Database release.Set the Oracle ODBC Data Source in the ODBC Data Source Administrator, as follows:
From the Start option, select Settings, then select Control Panel.
The Control Panel window appears.
If you are using Microsoft Windows 2000, select the Administrative Tools icon.
Select the Data Source (ODBC) icon.
The ODBC Data Source Administrator dialog appears
From the User DSN tab, click Add.
The Create New Data Source dialog box appears.
Note:
If you want to use the data source name (DSN) for multiple users on the same system, you should create a System DSN instead of a User DSN.Select the Oracle ODBC Driver from the list of ODBC drivers installed on the system, then click Finish.
In the Data Source Name field, type a name to the ODBC data source.
This data source name is the ODBC Data Source that you must specify in the Modify Microsoft Access Database dialog box of SQL Developer.
To prepare for capturing a Microsoft Access database, you must use the Exporter for Microsoft Access tool. This tool is packaged as a Microsoft Access MDE file and it allows you to export the Microsoft Access MDB file to an XML file. If the Microsoft Access database you select uses linked tables, the schema information for the linked tables is also exported to the XML file.
To export the Microsoft Access database to an XML file:
Click Migration, then Microsoft Access Exporter, then the Run option for the Microsoft Access release that you are using.
The exporter tool interface is displayed.
Follow the instructions in the initial dialog box. For detailed information about using the exporter tool, click the Help button.
Note:
Do not modify any of the files created by the Exporter.Each Microsoft Access database that you selected is exported to an XML file. The exporter tool currently does not support creating XML files from secured or replica databases.
To configure a MySQL database for migration:
Install MySQLConnector/J release 3.0 on the system where you have installed SQL Developer, as follows:
Download the MySQLConnector/J API from the MySQL Web site at:
http://www.mysql.com/
Extract the mysql-connector-java-3.x.xx-stable.zip
file.
From the com
subdirectory zip the extracted files.
Note:
Ensure that the zip file contains the path information beginning with thecom
path name.Rename the zip file to the following:
mysql-connector-java.zip
Copy the mysql-connector-java.zip
file to the following directory:
On Windows:, copy to:
OMWB_install_dir\Omwb\drivers
On UNIX, copy to:
OMWB_install_dir/Omwb/drivers
Ensure that the MySQL database is accessible by the root
user. This user must have database administrative privileges.
Before migrating a third-party database, you must extract information from the database. This information is a representation of the structure of the source database, and it is called the captured model. The process of extracting the information from the database is called capturing the source database.
To capture the source database, do either of the following, depending on the source database type:
For Microsoft Access migrations, run the exporter tool: click Migration, then Microsoft Access Exporter, then the item for your version of Microsoft Access. Follow the steps for the exporter tool, which has its own online help. Then, in SQL Developer click Migration, then Capture Exporter XML, and specify the XML file that you created using the exporter tool.
For other third-party database migrations, right-click the connection name in the Connections navigator, and select Capture product-name (for example, Capture MySQL or Capture Microsoft SQL Server).
After capturing the source database, you can view the source database information in the captured model in SQL Developer. If necessary, you can modify the captured model and change data type mappings.
Note:
Oracle recommends that you do not change the default data type mappings unless you are an experienced Oracle database administrator.Before migrating a third-party database, you must create the converted model. The converted model is a representation of the structure of the destination database. SQL Developer creates the converted model using the information from the captured model.
To convert a captured model to a converted model, right-click the appropriate node in the Captured Objects navigator and select Convert to Oracle, and specify or accept the defaults for data mappings
To get the best results from the migrated database, you can customize the converted model to use specific features of Oracle databases, such as multiple tablespaces. However, if you are not an experienced Oracle database administrator, Oracle recommends that you proceed directly to migrating the database, as described in Section 2.8.
The following topics describe how to modify the converted model, if this becomes necessary:
If error messages with the prefix Parse Exception
are listed in the progress screen, the associated schema objects do not appear in the converted model. To complete the converted model:
Note the captured model schema object that caused the error.
Select that schema object in the captured model.
Inspect the properties on the schema object in the captured model for possible causes of the error.
Modify a property of the schema object in the captured model.
For example, you might comment out one line of a stored procedure.
From the Object menu, select Parse.
Note:
The Parse option is available only after creating the converted model.SQL Developer attempts to create the converted model schema object from the captured model schema object.
A progress screen appears.
If the error appears again, repeat steps 2 to 5.
If necessary, comment out all the schema object's editable properties. This ensures that a schema object is created in the converted model, and therefore in the destination database. The migrated schema object will not function as expected, but acts as a placeholder so that you can create an equivalent Oracle schema object with the correct functionality.
You can customize the converted model to take advantage of Oracle features identified during the planning phase. For example, you can make the following changes to the converted model:
To modify properties of a schema object in the converted model, expand the display of the converted model in the Converted Objects navigator, select the object, and perform the desired edits using the interface and techniques for that object type (table, procedure, and so on).
An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data. For more information about tablespaces, see the Oracle Concepts guide.
If you are not satisfied with the default tablespace settings in SQL Developer, you may want to create or rename a tablespace or reassign objects to existing tablespaces.
SQL Developer automatically creates a tablespace when it creates the converted model. To prevent this functionality, deselect the Automatically Create Tablespaces During converted model Creation option on the General page of the Options dialog box.
All users in the converted model are allocated the password oracle
because SQL Developer cannot decrypt passwords from source databases. If you do not change this password, the password for all users in the destination database will also be oracle
. If you want to change the password for a user, select that user in the converted model and change the password to the value you require, using the User property sheet.
To generate the DDL statements to create the Oracle schema objects, you must already have captured the captured model and created the converted model. After you generate the DDL, you run the DDL statements to cause the objects to be created in the Oracle database. At this point, the database schema is migrated to Oracle.
After you generate and run the DDL statements to migrate the schema objects, you can migrate the data from the original source database, as explained in Section 2.9.
After you have generated and run DDL statements to create the Oracle schema objects for the migrated database, you can migrate (move) any existing data from the source database to the Oracle database. You have two options for data migration: online (move the data now) or offline (move the data later).
Online data move: Click Migration, then Migrate Data. In the dialog box, specify the Source Connection and Captured Model, and the Target Connection and Converted Model.
Offline data move: Click Migration, then Generate Offline Data Move Scripts; specify the converted model and a directory into which to generate the files that you will use for unloading the data from the source database and for importing into Oracle using SQL*Loader.
To transfer the data offline, you generate and use scripts to copy data from the source database to the destination database. During this process you must:
Use SQL Developer to generate the data unload scripts for the source database and corresponding data load scripts for the destination database.
Run the data unload scripts to create data files from the source database using the appropriate procedure for your source database:
Run the data load scripts using SQL*Loader to populate the destination database with the data from these data files as described in Section 2.9.1.4.
To create data files from a Microsoft SQL Server or Sybase Adaptive Server database:
Note:
The default delimiters for Sybase Adaptive Server for Linux x86 do not work using the UTF-8 encoding file. Therefore, if your LANG variable is set to en_US.UTF-8, you must set the LANG variable to en_US to default to ISO-8859-1.Copy the contents of the directory where SQL Developer generated the data unload scripts onto the computer where the source database is installed.
Edit the BCP extract script to include the name of the source database server.
On Windows, edit the bcp_extract.bat
script to alter the isql lines to include the appropriate variables
On UNIX, edit the bcp_extract.sh
script to alter the isql lines to include the appropriate variables, then add the following command:
chmod 755 bcp_extract.sh
The following shows some lines from a sample bcp_extract.bat
script:
isql -Usa -P -S<SERVER NAME> -iCreate_View.sql bcp pubs.dbo].[authors out AUTHORS.dat -c -t "<ec>" -r "<er>" -Usa -P -S<SERVER NAME> isql -Usa -P -S<SERVER NAME> -iDrop_View.sql
On Windows and Unix, edit each line to include the correct value for SERVER NAME. Do not include the angle brackets in the edited version of this file.
Run the BCP extract script.
On Windows, enter:
prompt> bcp_extract.bat
On UNIX, enter:
./bcp_extract.sh
This script creates the data files in the current directory.
Copy the data files to the oracle
subdirectory of the directory where SQL Developer generated the data unload scripts.
To create data files from a Microsoft Access database, use the Exporter for Microsoft Access tool.
Note:
For information about how to create data files from a Microsoft Access database, see online help for the exporter tool.To create data files from a MySQL database:
Copy the contents of the directory where SQL Developer generated the data unload scripts onto the computer where the source database is installed.
Edit the Dump Extract script to include the correct host, user name, password, and destination directory for the data files.
On Windows, edit the dump_extract.bat
script
On UNIX, edit the dump_extract.sh
script
The following shows a line from a sample dump_extract.bat
script:
mysqldump -h <HOST> -u <USERNAME> -p<PASSWORD> -T <DESTINATION_PATH> --fields-terminated-by="<ec>" --lines-terminated-by="<er>" CarrierDb CarrierPlanTb
Edit this line to include the correct values for HOST, USERNAME, PASSWORD, and DESTINATION PATH. Do not include the angle brackets in the edited version of this file.
Run the Dump Extract script.
On Windows, enter:
prompt> dump_extract.bat
On UNIX, enter:
prompt> chmod 755 dump_extract.sh
This script creates the data files in the current directory.
Copy the data files to the oracle
subdirectory of the directory where SQL Developer generated the data unload scripts.
To populate the destination database using the data files, you run the data load scripts using SQL*Loader:
Navigate to the directory where you created the data unload scripts.
Copy the contents of the oracle
subdirectory to a directory on the computer where the destination database is installed.
Log on to the computer that runs the destination database.
Run the SQL Load script.
On Windows, enter:
prompt> sql_load_script.bat
On UNIX, enter:
prompt> ./sql_load_script.sh
During the testing phase, you test the application and Oracle database to make sure that the:
Migrated data is complete and accurate
Applications function in the same way as the source database
Oracle database producing the same results as the source database
Applications and Oracle database meet the operational and performance requirements
You may already have a collection of unit tests and system tests from the original application that you can use to test the Oracle database. You should run these tests in the same way that you ran tests against the source database. However, regardless of added features, you should ensure that the application connects to the Oracle database and that the SQL statements it issues produces the correct results.
Note:
The tests that you run against the application vary depending on the scope of the application. Oracle recommends that you thoroughly test each SQL statement that is changed in the application. You should also test the system to make sure that the application functions the same way as in the third-party database.See also the following related topics:
Many constraints shape the style and amount of testing that you perform on a database. Testing can contain one or all of the following:
Simple data validation
Full life cycle of testing addressing individual unit tests
System and acceptance testing
You should follow a strategy for testing that suits your organization and circumstances. Your strategy should define the process by which you test the migrated application and Oracle database. A typical test method is the V-model, which is a staged approach where each feature of the database creation is mirrored with a testing phase.
Figure 2-2, "V-model with a Database Migration" shows an example of the V-model with a database migration scenario:
Figure 2-2 V-model with a Database Migration
There are several types of tests that you use during the migration process. During the testing stage, you go through several cycles of testing to enhance the quality of the database. The test cases you use should make sure that any issues encountered in a previous version of the Oracle database are not introduced again.
For example, if you have to make changes to the migrated schema based on test results, you may need to create a new version of the Oracle database schema. In practice, you use SQL Developer to create a base-line Oracle schema at the start of testing, and then edit this schema as you progress with testing.
Note:
Oracle recommends that you track issues that you find during a testing cycle in an issue tracking system. Track these issues against the version of the database or application that you are testing.Use the test cases to verify that the Oracle database provides the same business logic results as the source database.
Note:
Oracle recommends that you define completion criteria so that you can determine the success of the migration.This procedure explains one way of testing the migrated database. Other methods are available and may be more appropriate to your business requirements.
To test the Oracle database:
Create a controlled version of the migrated database.
Oracle recommends that you keep the database migration scripts in a source control system.
Design a set of test cases that you can use to test the Oracle database from unit to system level. The test cases should:
Ensure the following:
All the users in the source database have migrated successfully
Privileges and grants for users are correct
Tables have the correct structure, defaults are functioning correctly, and errors did not occur during mapping or generation
Validate that the data migrated successfully by doing the following:
Comparing the number of rows in the Oracle database with those in the source database
Calculating the sum of numerical columns in the Oracle database and compare with those in the source database
Ensure that the following applies to constraints:
You cannot enter duplicate primary keys
Foreign keys prevent you from entering inconsistent data
Check constraints prevent you from entering invalid data
Check that indexes and sequences are created successfully.
Ensure that views migrated successfully by doing the following:
Comparing the number of rows in the Oracle database with those in the source database
Calculating the sum of numerical columns in the Oracle database and compare with those in the source database
Ensure that triggers, procedures, and functions are migrated successfully. Check that the correct values are returned for triggers and functions.
Run the test cases against the migrated database.
Create a report that evaluates the test case results.
These reports allow you to evaluate the data to qualify the errors, file problem reports, and provide a customer with a controlled version of the database.
If the tests pass, go to step 7.
If all tests in the test cases pass or contain acceptable errors, the test passes. If acceptable errors occur, document them in an error report that you can use for audit purposes.
If the test cases fail:
Identify the cause of the error.
Identify the test cases needed to check the errors.
Log an issue on the controlled version of the migrated database code in the problem report.
Add the test case and a description of the problem to the incident tracking system of your organization, which could be a spreadsheet or bug reporting system. Aside from the test case, the incident log should include the following:
Provide a clear, concise description of the incident encountered
Provide a complete description of the environment, such as platform and source control version
Attach the output of the test, if useful
Indicate the frequency and predictability of the incident
Provide a sequence of events leading to the incident
Describe the effect on the current test, diagnostic steps taken, and results noted
Describe the persistent after effect, if any
Attempt to fix the errors.
Return to step 1.
Identify acceptance tests that you can use to make sure the Oracle database is an acceptable quality level.
You may already have a collection of unit tests and system tests from the original application that you can use to test the Oracle database. However, if you do not have any unit or system tests, you need to create them. When creating test cases, use the following guidelines:
Plan, specify, and execute the test cases, recording the results of the tests.
The amount of testing you perform is proportional to the time and resources that are available for the migration project. Typically, the testing phase in a migration project can take anywhere from 40% to 60% of the effort for the entire project.
Identify the components that you are testing, the approach to the test design and the test completion criteria.
Define each test case so that it is reproducible.
A test that is not reproducible is not acceptable for issue tracking or for an audit process.
Divide the source database into functions and procedures and create a test case for each function or procedure. In the test case, state what you are going to test, define the testing criteria, and describe the expected results.
Record the expected result of each test case.
Verify that the actual results meet the expected results for each test.
Define test cases that produce negative results as well as those that you expect a positive result.
The following displays a sample unit test plan for Windows:
Name Jane Doe
Module Table Test Emp
Date test completed 20 August 2002
Coverage log file location mwb\database\TableTestEmp
Description This unit test tests that the emp table was migrated successfully.
Reviewed by John Smith
Task ID | Task Description | Expected Result | Verified (Yes/No) |
---|---|---|---|
1 | Run the following on the source database for each table:
select count(*) from emp Run the following on the destination database for each table: select count(*) from emp |
On the source database, the count(*) produces a number. In this case, the number is the number of rows in each table.
On the destination database, the count(*) number corresponds to the number of rows in the new Oracle table. |
Yes
The number of rows in each table is the same in the source and destination databases. |
2 | Run the following on the source database for each table:
select sum(salary) from emp Run the following on the destination database for each table: select sum(salary) from emp |
On the source database, sum(salary) produces a check sum for the sum of the data in each table.
On the destination database, sum(salary) corresponds to the sum of the salary in the emp table. |
Yes
The sum for each table is the same in the source and destination databases. |
Deploying the migrated and tested Oracle database within a business environment can be difficult. Therefore, you may need to consider different rollout strategies depending on your environment. Several rollout strategies are identified for you, but you may use another approach if that is recommended by your organization.
During the deployment phase, you move the destination database from a development to a production environment. A group separate from the migration and testing team, may perform the deployment phase, such as the in-house IT department.
Deployment involves the following:
The strategy that you use for migrating a third-party database to an Oracle database must take into consideration the users and the type of business that may be affected during the transition period. For example, you may use the Big Bang approach because you do not have enough systems to run the source database and Oracle database simultaneously. Otherwise, you may want to use the Phased approach to make sure that the system is operating in the user environment correctly before it is released to the general user population. You can use one of the following approaches.
Using the Phased approach, you migrate groups of users at different times. You may decide to migrate a department or a subset of the complete user-base. The users that you select should represent a cross-section of the complete user-base. This approach allows you to profile users as you introduce them to the Oracle database. You can reconfigure the system so that only selected users are affected by the migration and unscheduled outages only affect a small percentage of the user population. This approach may affect the work of the users you migrated. However, because the number of users is limited, support services are not overloaded with issues.
The Phased approach allows you to debug scalability issues as the number of migrated users increases. However, using this approach may mean that you must migrate data to and from legacy systems during the migration process. The application architecture must support a phased approach.
Using the Big Bang approach, you migrate all of the users at the same time. This approach may cause schedule outages during the time you are removing the old system, migrating the data, deploying the Oracle system, and testing that the system is operating correctly. This approach relies on you testing the database on the same scale as the original database. It has the advantage of minimal data conversion and synchronization with the original database because that database is switched off. The disadvantage is that this approach can be labor intensive and disruptive to business activities due to the switch over period needed to install the Oracle database and perform the other migration project tasks.
Using the Parallel approach, you maintain both the source database and destination Oracle database simultaneously. To ensure that the application behaves the same way in the production environment for the source database and destination database, you enter data in both databases and analyze the data results. The advantage of this approach is if problems occur in the destination database, users can continue using the source database. The disadvantage of the Parallel approach is that running and maintaining both the source and the destination database may require more resources and hardware than other approaches.
There are several ways to deploy the destination database. The following task is an example that you should use as a guideline for deploying the destination database.
Note:
If you have a complex scenario as defined in Table 2–1, Oracle recommends that you complete all of the deployment tasks. However, if you have a simple scenario, you should choose the deployment tasks appropriate to your organization.Configure the hardware, if necessary.
In a large scale or complex environment, you must design the disk layout to correspond with the database design. If you use redundant disks, align them in stripes that you can increase as the destination database evolves. You must install and configure the necessary disks, check the memory, and configure the system.
Make sure the operating system meets the parameters of the Oracle configuration.
Before installing any Oracle software, make sure that you have modified all system parameters. For more information about modifying system parameters, see the relevant installation guide for your platform, such as Solaris Operating System.
Install the Oracle software.
Aside from the Oracle software that allows you to create an Oracle database, you may need to install ancillary software to support the application, such as Extract Transformation and Load (ETL) Software for data warehousing.
Create the destination database from the source database and migrate the data to the Oracle database.
There are several ways of putting the destination database into production after testing it, such as:
Place the successfully tested database into production. The test system is now the production system.
Use Oracle Export to extract the destination database from the successfully tested database and use Oracle Import to create that database within the production environment.
Use the tested migration scripts to create the Oracle database and populate it with data using SQL*Loader.
Perform the final checks on the destination database and applications.
Place the destination database into production using one of the rollout strategies.
Perform a final audit by doing the following:
Audit the integrity of the data
Audit the validity of the processes, such as back-up and recovery
Obtain sign-off for the project, if necessary
Several SQL Developer reports provide information about ___TBS___.
These reports enable you to:
___TBS___
. . .
If you are performing database migration, you need to use some migration-specific features in addition to those described in Section 1.2, "SQL Developer User Interface". The user interface includes some additional navigator tabs and panes (Captured Objects and Converted Objects) and a Migration menu, and many smaller changes throughout the interface. Figure 2-3, "Main Window for a Database Migration" shows the SQL Developer main window with objects reflecting the migration of a Microsoft Access application named sales.mdb.
In this figure:
The Connections navigator shows three database connections: migration_repository
for a connection (to a user named MIGRATION) used for the migration repository, sales_access
connected to a Microsoft Access database named sales.mdb, and sales_oracle
connected to an Oracle user named SALES whose schema owns the migrated schema objects.
The Captured Objects navigator shows one captured model, which was created using an XML file created by the exporter tool for Access applications. (If the source database is a type other than Microsoft Access, the procedure for creating the captured model is different: you can generate it directly from the source database connection.)
The Converted Objects navigator shows one converted model, which is an Oracle representation of the source database. The converted model is created from the captured model, and the converted model is used to generate the schema objects that you can see using an Oracle database connection (sales_oracle in this figure).
The Migration menu contains options related to migrating third-party databases to Oracle.
Show Getting Started Guide: Displays steps for getting started quickly with a migration.
Repository Management: Enables you to create, delete, or truncate (remove all data from) a migration repository; select the current migration repository; and disconnect from the current migration repository (which deactivates the current repository but does not disconnect from the database).
Microsoft Access Exporter: Contains submenu items from which you specify the version of the exporter tool to use to create an XML file to be used for creating the captured model. You can also use the exporter tool to export table data. Specify the exporter tool version for the version of Access that is on your PC and that was used to create the .mdb file.
Migrate Data: Displays a dialog box for performing no online migration of table data from the source database to the Oracle schema.
Script Generation: Generate Oracle DDL displays DDL (data definition language) statements in a SQL Worksheet window, where you can then run the script to create the Oracle schema and schema objects; Generate Data Move Scripts displays a dialog box for specifying the location in which to create files for performing an offline migration of table data from the source database to the Oracle schema.
Capture Exporter XML (Microsoft Access migrations): Creates a captured model of a Microsoft Access database from the XML file created by the exporter tool.
Translation Scratch Editor: Displays the translation scratch editor, which is explained in Section 2.13.5.
The View menu has the following items related to database migration:
Captured Objects: Displays the Captured Objects navigator.
Converted Objects: Displays the Converted Objects navigator.
The SQL Developer user preferences window (displayed by clicking Tools, then Preferences) contains a Migration pane with several related subpanes, and a Translation pane with a Translation Preferences subpane.
For information about these preferences, click Help in the pane, or see Section 1.12.9, "Migration".
Migration Log: contains errors, warnings, and informational messages relating to migration operations.
Logging Page: contains an entry for each migrated-related operation.
Data Editor Log: contains ___TBS___.
You can use the translation scratch editor to enter SQL statements and have them translated to PL/SQL statements. You can specify translation from T-SQL to PL/SQL or from Microsoft Access SQL to PL/SQL.
You can display the scratch editor by clicking Migration, then Translation Scratch Editor. The scratch editor has the interface shown in the following figure:
Scratch Editor toolbar (under the Scratch Editor tab): Contains icons for the following operations:
Open lets you open a file with SQL statements to be placed in the Enter SQL Statement box.
Translate translates the SQL statements according to the type of translation specified in the drop-down menu, and displays the results in the translated statements box.
Translation Dif Viewer displays a read-only pane showing the original and translated statements. It provides color-coded comparison of the original and translated SQL, to display semantic similarities between the source and translated code.
Save Original SQL File lets you save the original file to a location and name of your choice.
Erase All erases the contents of the Enter SQL Statement box.
A drop-down menu lets you select the type of translation: T-SQL to PL/SQL for Transact-SQL, or Access to PL/SQL for Microsoft Access.
Enter SQL Statement: The statement or statements to be translated. For multiple statements, each non-PL/SQL statement must be terminated with either a semicolon or (on a new line) a slash (/), and each PL/SQL statement must be terminated with a slash (/) on a new line. SQL keywords are automatically highlighted.
Translated statements toolbar (under Enter SQL Statement): Contains icons for the following operations on the translated statement or statements:
Save Generated PL/SQL File lets you save the generated statements to a PL/SQL script file.
Erase All erases the generated statements.
Validate SQL validates the SQL statements.
Tabs display panes with the following information:
Result: Displays the results of the most recent Translate operation.
Source Tree: Displays the results in expandable tree format.