|
Oracle® Migration Workbench User's Guide
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86 Part No. B15857-01 |
|
 Previous |
 Next |
|
Oracle® Migration Workbench User's Guide
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86 Part No. B15857-01 |
|
|
Previous |
Next |
This chapter describes how to migrate third-party databases to an Oracle database. It contains the following sections:
This chapter assumes that you have already created the Oracle Model using the procedure described in Chapter 5, "Creating and Customizing the Oracle Model".
To migrate the source database, you must follow the procedures described in this chapter. Before starting any of the procedures listed in this chapter, you must complete the procedures listed in Chapter 4 and Chapter 5 to capture the Source Model and create the Oracle Model.
Oracle recommends that you migrate the database in the following order:
Migrate tablespaces, users, and user tables.
Migrate data.
Migrate the remaining schema objects.
If you use the Migration wizard, Migration Workbench performs the tasks in this order automatically.
There are three methods you can use to complete the migration:
|
Note: To improve performance, Oracle recommends that you transfer the data offline. |
Creating the schema and transferring the data online.
This method requires that you use the Migration wizard to create the schema and transfer the data using the procedure described in "Using the Migration Wizard".
Use this method to gain maximum benefits from Migration Workbench reports.
Creating the schema online and transferring the data offline.
This method requires that you use the Migration wizard to create the schema using the procedure described in "Using the Migration Wizard". Transfer the data using the procedure described in "Transferring the Data Offline".
Use this method if:
You need to migrate different sections of the data at different times. For example, you may want to migrate static data before migrating time-critical data.
There is a large quantity of data.
Creating the schema offline and transferring the data offline.
This method requires that you create the schema using the procedure described in "Creating the Schema Offline" and transfer the data using the procedure described in "Transferring the Data Offline".
Use this method if the destination database is on a different network from the source database
You can choose different methods at different times during the migration project to complete different objectives. For example, you may want to create the schema online and transfer the data online to analyze the migration process and later transfer the data offline to fine tune the migration.
The following figure shows the process in the context of the overall migration process:
From the Action menu, select Migrate to Oracle to start the Migration wizard.
The Welcome page appears displaying the steps required to migrate the database.
Click Next.
The Destination Database Details page appears.
Enter the information required to connect to the destination database:
In the Username field, enter the name of a user with sufficient privileges for migrating. For information about privileges required for migrating, see "Accessing the Destination Database".
In the Password field, enter the password for the user.
Enter the appropriate values in the Host, Port, and SID fields. For information about determining the correct values for these fields, see "Determining Connection Information for an Oracle Database".
Click Next.
The Migrate Tablespaces, Users, and User Tables page appears.
If you have run the Migration wizard before, the tablespaces, users, and user tables already exist on the destination database and you are prompted to create them again. Click Yes to create these objects again.
|
Note: If you choose to create the tablespaces, users, and user tables again, the existing schema objects are overwritten and all existing data is lost. |
If you have not run the Migration wizard before, review the information about the Migrate Tablespaces, Users, and User Tables page, then click Next.
The Migrate Table Data page appears.
Specify whether you want to transfer the data during this Migration wizard session:
Choose Yes to transfer the data now.
Choose No if you want to transfer the data later.
Click Next.
The Select Schema Objects to Migrate page appears.
Select the schema objects to migrate, then click Next.
You may want to create some schema objects manually. For example you may want to create the indexes on the destination database manually. If so, deselect the indexes from the list of schema objects to migrate.
The Summary page appears.
Review the summary information:
If you need to change a selection, click Back to return to a previous page.
Click Finish to start the migration.
A progress screen appears, displaying information about the migration progress. The migration process may take a long time with complex databases.
If you are using the Microsoft Access plug-in, the Modify Microsoft Access Database screen appears.
If you are using the Microsoft Access plug-in, you can modify the Microsoft Access database file so that Microsoft Access Forms and Reports can operate with the destination database using an ODBC connection:
|
Note: Using the original Microsoft Access Forms and Reports with the destination database requires an ODBC connection to the Oracle database. |
Enter the user name for the ODBC connection. The ODBC connection user name is the name of the database you are migrating, for example, if you are migrating the northwind.mdb database, the user name is northwind.
|
Note: If you are migrating a database that is linked to other databases, each of the linked databases is migrated to a separate user. Make sure that the user you chose has sufficient privileges to access objects belonged to the other users. |
Enter the password for the user.
Enter the name of the ODBC connection to the destination database.
A progress screen appears, displaying information about the migration progress. The migration process may take a long time with complex databases.
Review the messages that appear on the progress screen, and take the following action:
If an error message appears, you must fix the error and return to step 1.
If a warning message appears, consider the problem outlined in the message and decide whether any action is required.
After the migration is complete, a screen appears displaying the total number of errors and warnings.
If you are using the Microsoft Access plug-in, and there are two or more Unique Indexes applied to different columns in the same table the primary keys do not appear in the appropriate columns after migration. You can perform the following tasks to ensure that the Primary Keys appear in appropriate columns:
Perform the post-migration tasks and create the ODBC links in the original Microsoft Access database.
As you have already run the Migration wizard before, the tablespaces, users, and user tables already exist on the destination database and you are prompted to create them again. Click No.
The Migrate Table Data page appears.
You are asked again to specify whether you want to transfer the data during this Migration wizard session. Click No.
Click Next.
The Select Schema Objects to Migrate page appears.
Only select Indexes to migrate, then click Next.
The Summary page appears.
Review the summary information:
If you need to change a selection, click Back to return to a previous page.
Click Finish to start the migration.
When you view the Tables in the original Microsoft Access database after the migration process is completed the Primary Keys will appear in the appropriate columns.
The destination database schema information is stored in the Oracle Model. Creating the schema is the process of creating the schema objects in the destination database.
Use the Migration wizard as described in "Using the Migration Wizard" to create the schema online. There are two methods:
Create all the schema objects in one session of the Migration wizard.
Create different schema objects in different sessions of the Migration wizard. For example, to create the tablespaces, users, and user tables before creating constraints and triggers:
Run the Migration wizard.
Choose to create the tablespaces, users, and user tables.
If you are transferring the data online, choose Yes on the Migrate Table Data page.
If you are transferring the data offline, choose No on the Migrate Table Data page.
Deselect all objects on the Select Schema Objects to Migrate page.
Click Finish on the Summary page.
If you are transferring the data offline, perform that transfer using the method described in "Transferring the Data Offline".
Run the Migration wizard again.
Choose not to create the tablespaces, users, and user tables.
Choose No on the Migrate Table Data page.
Select all objects on the Select Schema Objects to Migrate page.
Click Finish on the Summary page.
The destination database schema information is stored in the Oracle Model. Creating the schema is the process of creating the schema objects in the destination database.
The schema consists of all the schema objects, however you do not have to create all the objects in one session. Migration Workbench allows you to select which objects you want to create.
This feature allows you to transfer data without the destination database performing time consuming procedures, such as triggers or constraints, during each row insert.
From the Action menu, select Generate Migration Scripts.
The Generate Migration Scripts screen appears.
Choose the schema objects that you want to migrate.
Click OK.
Migration Workbench generates a set of scripts that you can use to create the schema.
A message box appears displaying the location where Migration Workbench saved the files.
Copy the create.sql file from that directory to the computer where the destination database is installed.
Log onto the computer with the destination database.
Change to the directory where the create.sql file is stored.
Enter the following command to start SQL*Plus, where user and password are the user name and password for the database user. Ensure that the user has sufficient privileges. For information about privileges for migrating, see "Accessing the Destination Database".
prompt> sqlplus user/password
A message similar to the following appears:
Connected to: Oracle9i Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production
Run the create.sql script using the following command:
SQL> @create.sql
As the script proceeds, messages similar to the following appear where object is a type of object, for example a table:
> object created.
The schema objects are created in the destination database.
Use the Migration wizard as described in "Using the Migration Wizard" to transfer the data online. Choose Yes on the Migrate Table Data page.
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 Migration Workbench to generate the data unload scripts for the source database and corresponding data load scripts for the destination database as described in "Generating Data Unload Scripts and SQL*Loader Control Files".
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 "Populating the Destination Database Using the Data Files".
Use Migration Workbench to generate data unload scripts for the source database and SQL*Loader control files for the destination database:
Select a user schema in the Oracle Model that you want to migrate.
Expand the node in the Oracle Model tree and select the associated Tables node.
From the Object menu, select Generate SQL*Loader Scripts.
A confirmation dialog box appears.
Click Yes to generate the scripts.
A message box appears displaying the location of the generated scripts. This directory name includes a timestamp.
Note the location of the data unload scripts.
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 Migration Workbench 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 Migration Workbench generated the data unload scripts.
To create data files from a Microsoft Access database, use Oracle Migration Workbench Exporter for Microsoft Access.
|
Note: For information about how to create data files from a Microsoft Access database, see Oracle Migration Workbench Exporter for Microsoft Access online help. |
To create data files from an Informix Dynamic Server database:
Log onto the computer where the source database is installed.
Enter the following command where dbname is the name of the source database:
prompt> dbaccess dbname unload.sql
This command extracts the data from the tables into a set of data files named tablename.dat where tablename is the name of the table.
Copy the data files to the oracle subdirectory of the directory where Migration Workbench generated the data unload scripts.
To create data files from a MySQL database:
Copy the contents of the directory where Migration Workbench 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 Migration Workbench generated the data unload scripts.
To create data files from an IBM DB2/400 database:
Copy the contents of the directory where Migration Workbench generated the data unload scripts onto the computer where the source database is installed.
If you have performed an offline capture as described in Chapter 4, you must install the extract_nn program on the computer where the source database is installed as described in the Oracle Migration Workbench Reference Guide for IBM DB2/400 V4R5 Migrations.
Run the extract_nn program from the omwb_lib library on the source AS/400 system.
This script creates the data files in the current directory.
Copy the data files to the oracle subdirectory of the directory where Migration Workbench generated the data unload scripts.
To create data files from an IBM DB2 UDB database:
Copy the contents of the directory where Migration Workbench generated the data unload scripts onto the computer where the source database is installed.
Open the IBM DB2 UDB Command Line Processor (CLP) Window. Enter the following command in a Microsoft DOS window command prompt:
db2cmd
Activate and connect to the appropriate database using the following commands in the CLP window:
db2 acitvate DB_NAME user USER_NAME using USER_PASSWORD db2 connect to DB_NAME
You must substitute the appropriate values for DB_NAME, USER_NAME and USER_PASSWORD.
Execute the export.bat file by entering the command in the CLP window:
> OMWB_install_dir\Omwb\sql_loader_scripts\UDBx\<timestamp>\Export
This command launches all of the command lines in the export.bat file in succession, resulting in a creation of a .DAT file that contains the specified table's data in the specified delimited form.
|
Note: You must have SYSADM or DBADM authority, or CONTROL or SELECT privileges, for each table you want to export. |
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 described in the procedure "Generating Data Unload Scripts and SQL*Loader Control Files".
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
If you are migrating an Informix Dynamic Server database, you can also migrate ESQL/C source files. To migrate the ESQL/C code that you captured using the procedure in "Capturing Informix ESQL/C Source Files":
From the Action menu, select Convert E/SQL to Pro*C.
A progress screen appears, displaying information about the conversion progress.
Review the messages on the progress screen, then click OK.
From the Action menu, select Generate PRO*C Source Files.
A directory selection dialog box appears.
Select the directory where you want to save the Pro*C files, then click OK.
Copy the Pro*C files to the computer where the destination database is installed.
For more information about migrating ESQL/C source files, see Oracle Migration Workbench Reference Guide for Informix Dynamic Server 7.3 Migrations.
