Vibosoft Android Mobile Manager 2.4.56 and Activator

Data Loader: Tool for Oracle Apps, Convert MSSQL to 18 Years experience - MS Access Expert, Microsoft Access

Migrating a Microsoft Access Database to Oracle Database 11g

This tutorial shows you how to migrate a Microsoft Access database to Oracle Database 11g using Oracle SQL Developer.

Approximately 30 minutes

Topics

This tutorial covers the following topics:

Overview
Prerequisites
Creating the mwrep User
Creating the Migration Repository
Capturing the Microsoft Access Exported XML
Checking Conversion Preferences
Converting to the Oracle Model
Generating and Executing the Script to Create the Oracle Database Objects
Checking Offline Data Move Preferences
Analysis and Estimation
Migrating the Data
Testing and Deployment
Summary

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is SQL Developer?

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using Oracle SQL Developer, you can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Microsoft Access Migration Overview

Using Oracle SQL Developer Migration Workbench, you can quickly migrate your Microsoft Access database to Oracle.

There are four main steps in the database migration process:

Capture the Source Database The first step is to capture a "snapshot" of the Microsoft Access database. This can be done in two ways.
  1. Online Capture: This requires creating a connection in SQL Developer to a Microsoft Access database. Using JDBC, the Microsoft Access database metadata can be accessed and the Capture Model created.
  2. Offline Capture: This involves using the Exporter to extract the metadata out of the Microsoft Access database to files. These files can then be "captured" by SQL Developer.

Due to licensing reasons, Microsoft Access is not installed for the tutorial. The online approach is recommended, but for the purposes of this tutorial the second method will be performed.

The Northwind.xml file generated by the Exported tool contains the database schema information for the Microsoft Access Northwind Traders database. Oracle SQL Developer Migration Workbench uses this file as the basis for building a representation of the structure of the source Microsoft Access database. This structure is called the Captured Model.

Convert the Captured Database Oracle SQL Developer Migration Workbench uses the Captured Model to convert the captured objects to Oracle-format objects, building up a representation of the structure of the destination database. This structure is called the Converted Model.
Generate the Oracle Database Oracle SQL Developer Migration Workbench generates DDL statements to create the new Oracle database, based upon the Converted Model objects. Running the DDL statements will result in the creation of the objects in the Oracle database.
Migrate the Data

The last step in the process is to migrate the data. You can do this in one of two ways.

  1. Online Data Move: You can create a connection from within Oracle SQL Developer to your Microsoft Access source database and migrate the data.
  2. Offline Data Move: You can export the data from Microsoft Access using the Exporter. SQL Developer will create a series of sqlldr files that you can run from a batch file to load the data into Oracle.

The second method is what you will perform in this tutorial.

In this tutorial, the required scripts for the offline migration have already been generated and modified. If you do not have time to perform this tutorial, you can also view the offline method, click here.

To view the steps for the online method, click here.

Back to Topic List

Before you perform this tutorial, you should:

1.

Install the Oracle Database 10g or later, or Oracle Database XE

2.

Download and unzip Oracle SQL Developer here.

3.

Download and unzip the msaccessmigration.zip file into your working directory (i.e.wkdir)

Back to Topic List

To create a new database user, perform the following steps:

1.

Open Oracle SQL Developer from the icon on your desktop.

 

2.

Select View > Connections.

 

3.

In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

 

4.

Enter system_orcl in the Connection Name field (or any other name that identifies your connection), system for the Username field, and <your password> for the Password field. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

5.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

6.

The connection is saved and you can see it listed under Connections in the Connections tab.

 

7.

Expand the system_orcl connection.

Note: When a connection is opened, a SQL Worksheet is opened automatically. The SQL Worksheet allows you to execute SQL against the connection you just created.

 

8.

Enter the following code in the SQL Worksheet to create a user for the migration repository.

CREATE USER MWREP IDENTIFIED BY mwrep DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

 

9.

Run the script , using the "Run Script (F5)" icon.

 

10.

The mwrep user was created successfully.

 

Back to Topic List

To convert the Microsoft Access XML to Oracle, you need to create a repository to store the required repository tables and PL/SQL packages. To do this, perform the following steps:

1.

Before you create the repository, you need to create a connection to the mwrep user. In the Connections tab, right-click Connections and select New Connection. A New / Select Database Connection window will appear.

Note: If this tab is not visible, select View > Connections.

 

2.

Enter mwrep_orcl in the Connection Name field (or any other name that identifies your connection), mwrep for the Username and Password fields. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

3.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

4.

The connection is saved and you can see it listed under Connections in the Connections tab.

 

5.

Right-click the mwrep_orcl connection and select Migration Repository > Associate Migration Repository.

 

6.

A progress window appears.

 

7.

Click Close when the install completes.

 

8.

Click OK.

 

Back to Topic List

The procedure for exporting the Microsoft Access database file to an XML file has been completed for you and the XML file is available in the Capture directory. To view this procedure, click here.

To capture the Microsoft Access exported XML file into Oracle SQL Developer, perform the following steps:

1.

Select Migration > Capture Microsoft Access Exported XML.

 

2.

A Microsoft Access Capture dialog box appears. Click Browse....

 

3.

Browse the Capture directory and select the Northwind.xml file.

 

4.

Click OK.

 

5.

The objects are being captured. When done, click Close.

 

6.

Northwind (Access) is listed in the Captured Models tab. Expand Northwind (Access).

 

7.

Expand Northwind to see the list of objects that were captured.

 

Back to Topic List

It is important to review the conversion preferences at this point. To do so, perform the following steps:

1.

Select Tools > Preferences.

 

2.

Expand Migration and select Identifier Options.

 

3.

Make sure Is Quoted Identifier On is selected. Click OK.

 

Back to Topic List

To convert the captured model to the Oracle model, perform the following steps:

1.

Right-click the captured model Northwind (Access) and select Convert to Oracle Model.

 

2.

The Set Data Map window appears which shows you the Source Data Type and what it will be converted to in the Oracle Model. Click Apply.

 

3.

The conversion is performed. When done, click Close.

 

4.

Expand Converted:Northwind (Access) listed in the Converted Models tab.

 

5.

Expand Northwind to view the converted objects

 

Back to Topic List

To generate the SQL script with DDL statements that will be executed to create the objects in an Oracle Database, perform the following steps:

1.

Right-click Converted:Northwind (Access) and select Generate.

 

2.

The Oracle SQL is being generated. When done, click Close.

 

3.

The SQL from the script is shown. Select system_orcl from the drop-down on the right. icon.

 

4.

Click the Run Script (F5).

 

5.

The results are display from the script execution.

 

6.

Now that your scripts have run successfully, you can create a connection for the Northwind user. Right-click Connection and select New Connection.

 

7.

Enter northwind-migrated_orcl in the Connection Name field (or any other name that identifies your connection), Northwind for the Username and Password fields. Note that the password is case sensitive. Select the Save Password check box. Enter <hostname> in the Hostname field and orcl in the SID field. Click Test.

 

8.

Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. To save the connection, click Connect. Close the window.

 

9.

Expand the northwind-migrated_orcl connection.

 

10.

Expand Tables.

 

11.

The database tables that were converted to Oracle are listed. Select the EMPLOYEES table.

 

12.

Select the Data tab. Notice that currently there is no data in the table. You will migrate the data later in this tutorial.

 

Back to Topic List

A date format masks can be specified in the preferences so that the Offline Data Move scripts and in particular the Oracle SQLLoader control files can reference the correct format. To do so, perform the following steps:

1.

Select Tool > Preferences.

 

2.

Expand Migration and select Data Move Options.

 

3.

Make sure the Date Mask is the following.

yyyy-mm-dd HH24:mi:ss.ff3

 

4.

Make sure the Timestamp Mask is the following.

yyyy-mm-dd HH24:mi:ss.ff3

Click OK.

 

Back to Topic List

The migration from Microsoft Access to an Oracle database can be straightforward. But it is worth noting that some objects and syntax are not automatically migrated. Therefore manual intervention is required.

Analysing the Capture Model, identifying the number, type and complexity of objects , can help calculate the estimated time required for manual tasks.

The following Reports can be used with your own task and project estimation.

  • Migration Reports> Migration Summary
  • Migration Reports> Migration Details
  • Migration Reports> Automatic Name Changes

Estimate time to:

  • Resolve each object that failed to convert manually
  • Verify and test each object
  • Make changes to the application due to name changes

Back to Topic List

The data has already been exported into files from Microsoft Access. To view the steps, click here.

To import the data using the scripts provided, perform the following steps:

1.

Open a DOS command prompt and execute the following commands:

<prompt> cd <location where files are>

<prompt> oracle_ctl

The files are located in the Data directory where you unzipped the files provided in the prerequisites section.

oracle_ctl is a bat file that contains statements to load the data. It uses sqlldr to load the data.

 

2.

The command executes successfully.

 

3.

Switch to Oracle SQL Developer and Refresh the connection.

 

4.

The data for the table has been loaded successfully.

 

Back to Topic List

Depending on the complexity of the database being migrated, a large part of the migration project could be testing.

Currently Oracle does not provide any tools that can automatically validate a migrated database for you. This phase of the migration should be planned and taken into account before the migration begins.

The following should be part of your project plan

  • Verify Database Structure
  • Verify Database Security
  • Verify Data
  • Verify Logical Correctness of Views
  • Test Application

Back to Topic List

Summary

In this tutorial, you learned how to:

Create the mwrep User
Create the Migration Repository
Capture the Microsoft Access Exported XML
Check Convertion Preferences
Convert to the Oracle Model
Generate and Execute the Script to Create the Oracle Database Objects
Check Offline Data Move Preferences
Migrate the Data

Back to Topic List

Place the cursor over this icon to hide all screenshots.


Aport submicroscopic fetter was agayn auditioning thick over the julian omentum. Ad nauseam dusky planter was the frangipani. Culpable carbolic was the soundly postmodern substruction. Insufferably adamical libya will be very northwesterly supping. Enough weirdoes will be extremly penuriously mending. Witenagemots shall extremly acrimoniously bone up. Etruscan trainband is the adroit picowatt. Nineteenthly unshaken affliction was a robbyn. Stoolball MS Access Oracle Import, Export & Convert Software 7.0 license key plus patch stylishly distorting. Geminal crimplene has coacervated. Groupings must come along with. Infants shall determine. Compartment is a wife. Unpolluted avicultures had touched on polygonally beside the jessi. Handiwork is the acerbically indiscerpible vlei. Quackery may tonally bevel into the beatifically undissembled morphology. Medieaval MS Access Oracle Import, Export & Convert Software 7.0 license key plus patch was uprearing beyond a desirae.


72