Now you're almost ready to start the migration. The last step is to decide on the approach you'll use for the migration. For a quick review or prototype or when migrating a small, straightforward database, the Quick Migrate option is ideal. Alternatively, you can use a more interactive approach and step through each phase of the migration. We'll review both approaches in the next section.
Quick Migrate is a wizard-driven dialog that follows seven steps:
- You can start Quick Migrate from two areas: either by navigating to Tools | Migration or from the context menu of the source database connection you are planning to migrate (as shown in the following screenshot). Either action invokes the same Quick Migrate dialog. If you start the wizard from the context menu of the source database connection, the first page of the wizard will already be populated with the source database connection.
- The next step is to select Target Connection. As you can see in the following screenshot, this step reminds you about the required privileges and offers you the opportunity to set up a new target Oracle database connection if you do not have a created one already:
- Create source and target connections before starting.
- Select the repository you plan to use during the migration. If you have only one repository, it will be in the drop-down list. If you have more than one repository, then the repository that you last associated your repository user with will appear here first. At this stage, if you have no repository, the dialog is a little different, looking more like the screenshot shown in step 2. You have the option of creating a repository here. This repository is created at the start of the migration, and you can have it removed on completion.
- This is the premigration step, and it gives you the opportunity to check whether all of the required privileges and connections are valid. If you are not working on a multischema migration and have not assigned additional privileges, you get a privilege warning, as shown in the following screenshot. This does not affect a single user migration.
The result of the checks should be a success status for all but the multischema privilege test, which fails for single schema settings, which is okay:
- In step 6, you can select the type of migration, as follows:
- Migrating tables only
- Migrating tables and data
- Migrating everything
These are self-explanatory, except the last option possibly, where everything includes the additional structures in the schema, including the procedural code, which is translated to PL/SQL in Oracle.
- This is a summary of the source and target connections, the repository you are using, and the type of migration you're about to use:
This follows the five phases mentioned earlier in the chapter:
Once the process is complete, you can review the steps in a tabbed window. The upcoming screenshots show three points of the migration.
The first screenshot of the migration is towards the end of the first phase. You can see the types and tallies of the objects being migrated. Note that you can elect to automatically close the dialog on completion. This is true for many of the migration dialogs. For the Quick Migration wizard, we recommend that you leave it deselected. This way, you can review all of the phases of the migration when the wizard completes them before the dialog closes.
The tail end of the Capture phase is shown in the preceding screenshot. Here, you see that the Capture icon has color, and there is also a Capture tab, indicating that the phase is now complete.
The next screenshot shows the final stage of the migration where the data is being moved to the target database. You can see that the all other phases are complete, and their tabs are now ready for review. What's also significant is that the data is not moved to the tables sequentially, but it's moved using threads instead so that much of the data is moved at the same time.
Once the migration is complete, all that remains for you to do is verify the data. You can expand your connection and review the objects and the data created by browsing them as you would in any other Oracle connection that you have created.
The captured and converted metadata is still in your repository, and so you can return to the metadata that resulted from Quick Migrate and make some fine-grained adjustments. In the next section, we'll review the individual steps of the migration. Therefore, you can return to the metadata that resulted from Quick Migrate and make some fine-grained adjustments.
When running a migration, the details of any issues or information are tracked in the Migration Log window. If you don't see a log window, go to View | Log to display it. Many entries are informative, while others show errors. The screenshot that follows shows the Microsoft SQL Server log and the Sybase migration log:
In the log displayed in the previous screenshot, we expanded the second item, which gathered a collection of changes with a similar theme. Here, the names of the converted objects have been modified. This might be due to duplicate names that would result from names being shortened during the conversion to comply with Oracle naming standards, or there may be other characters that do not match Oracle naming standards. In these cases, the name changes are for information purposes, and no error exists. You may still want to adjust some of the names to comply with your company's or project's standards.
This is a good opportunity to review the changed names and make additional modifications to the objects before the DDL scripts are prepared.
Once you have made the changes and updated the converted model, you are ready to generate the scripts that are executed against the target schema. You may have changed the name of the captured and converted models, but the DDL script creates the new target schema based on the details of the source schema. We recommend that you review this name before you generate the DDL.
In the example that follows, the default name created for the Northwind sample model is shown. To change this name, select the main folder in the converted model and invoke the context menu. Select Rename Schema and change it to a more appropriate name for your target database:
Once you're happy with all of the changes you have made, select Generate from the context menu. Close the dialog to reveal the complete script. In the example that follows, we have highlighted the Create User statement. This is the schema name that you may have changed:
Once you have completed your migration, you can review the output of the various phases by reviewing reports that are run against the repository owner:
Migration reports are a useful addition to the migration process. These reports have the following functions:
- Help you determine the size of the project by outlining the number of objects you have
- Show the number of objects that failed to migrate, and in doing so, they help determine what and how much needs to be handled manually
- Display the automatic changes made to names
SQL Developer provides three reports that detail the success and failure points of the migration. An example of one of these reports is displayed in the previous screenshot. The set of reports also includes Migration Estimation Report.