Vigilant Technologies

An Oracle database provides you with better scalability, reliability, increased performance, and better security than other third-party databases. For this reason, organizations migrate from their current database, to an Oracle database. Because a database migration can be a complicated process, Oracle provides you with Migration Workbench. Migration Workbench is a tool that helps you to migrate from third-party databases to an Oracle database.

Migration Workbench captures information from the source database and displays it in the Source Model, which is a representation of the structure of the source database that is stored in the Workbench repository. Migration Workbench enables you to migrate the data, including schema objects, triggers, and stored procedures. Migration Workbench uses its own repository, called the Workbench repository, to store migration information that is persistent for the life of the migration. It allows you to modify the Source Model and generate an equivalent Oracle Model. The Oracle Model is a representation of the structure of the destination database and is stored in the Workbench repository. Migration Workbench populates the Oracle Model using the information from the Source Model. You can then use the information in the Source Model and the Oracle Model to compare database objects, identify conflicts with Oracle reserved words, and manage the migration progress.

Following are the components of the Migration Workbench Architecture:

• Migration Workbench
• Plug-In
• Workbench Repository

Migration Workbench

Migration Workbench provides a user interface, including wizards, that help you migrate third-party databases to an Oracle database. Migration Workbench includes the following wizards and scripts:

• Capture wizard to capture the source database
• Migration wizard to migrate the Source Model to the Oracle Model
• Scripts to create a set of files that describe the database structure and also to copy data from the source database to the destination database

Individual plug-ins are available to migrate different third-party databases to an Oracle database. You must install at least one plug-in with Migration Workbench.

Plug-In

Migration Workbench uses a plug-in to:

• Extract data from the data dictionary of the source database
• Create the Source Model
• Convert the Source Model to the Oracle Model

Individual plug-ins are available to migrate different third-party databases to an Oracle database.

Workbench Repository

The Workbench repository is a set of tables in an Oracle database that stores the Source Model and the Oracle Model and other information about the migration. By storing the Source Model and the Oracle Model in the Workbench repository, you can customize the migration without affecting the production environment. The Workbench repository saves dependency information about the database that you are migrating.
Oracle recommends that you use an Oracle Database 10g, Oracle9i, or Oracle8i database to store the Workbench repository. However, if you prefer, you can store the information in the default Workbench repository that is installed with Migration Workbench.

The stages of Migration Workbench process are as follows:

• Capturing the Source Database
• Accessing the Destination Database
• Creating and Customizing the Oracle Model
• Migrating from the Source Database to the Oracle Database

Capturing the Source Database
In this stage, you use Migration Workbench to extract the metadata from the data dictionary of the source database and populate the Source Model of the Workbench repository. There are two methods you can use during this stage, as follows:

• Use the Capture wizard to connect to the source database, extract the database structure, and populate the Source Model.
• Use Migration Workbench offline capture scripts to run against the source database and create a set of files that describe the database structure. You can then use Migration Workbench to populate the Source Model from these files.

Accessing the Destination Database

To access the destination database for migration purposes, you must have certain roles and privileges. You can create a user and give the following grants:

CONNECT, RESOURCE, CREATE PUBLIC SYNONYM, 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

Creating and Customizing the Oracle Model

In this stage, you use Migration Workbench to map the source database metadata, displayed in the Source Model, to similar Oracle schema objects in the Oracle Model. The Capture wizard automatically creates the Oracle Model by default. You can customize both the Source Model and the Oracle Model at a later stage.

Migrating from the Source Database to the Oracle Database
In this stage, you use Migration Workbench to migrate the source database to an Oracle database. Migration Workbench extracts schema object information from the Oracle Model and creates these objects in the destination database. After it has created the schema objects in the Oracle database, it populates these objects with the data from the source database.

Benefits of Migration Workbench

Benefits of using Migration Workbench to migrate a third-party database to an Oracle database are as follows:

• Reduces the effort and risks involved in a migration project
• Allows you to make informed decisions based on the amount of automated migration you can achieve
• Simplifies the process of migrating data and applications from third-party databases to an Oracle database
• Allows you to migrate an entire third-party database, including triggers and stored procedures
• Provides an integrated, visual environment to help you manage the migration process
• Displays a representation of the Source Model and Oracle Model so that you can compare
• Provides feedback of the migration through reports
• Allows you to customize the Source Model and Oracle Model to maximize the automation of the migration process
• Allows you to collect metadata from a third-party database without having to connect to that database with Migration Workbench
• Eases the migration process by allowing you to perform the migration in stages using intuitive wizards

There are numerous new features in Oracle 11g R2. I have compiled a list to highlight the most important ones which will help boost performance and increase ROI for your implementation.

To help separate the features based on roles, I broke the features that pertain to the point of view of, Developers, and Database Administrators.

Developers

• This release includes an improved framework for advanced Oracle Application Express developers to build and leverage custom Web 2.0 capabilities, improving performance and enabling developers to create more dynamic application widgets. Oracle Application Express also includes the ability to suppress standard JavaScript and CSS files. All included JavaScript files are now compressed to improve page load time.
• The storing of binary large objects (BLOBs) within the database is growing in popularity due to the many advantages over storing content on disparate file systems. 11gR2 incorporates Declarative BLOB support, which greatly simplifies loading and manipulating of content.
• Oracle Application Express offers a number of security enhancements. Key enhancements include the ability to declaratively encrypt session state and specify session time outs for maximum idle time and maximum session duration as well as create new password item types that enable users to enter passwords without ever saving them to session state.
• For testing and production instances, Oracle Application Express now supports the ability to install a runtime version of Oracle Application Express. This minimizes the installed footprint and privileges. Scripts are also provided to remove or add the developer interface from an existing instance. The ability to implement a runtime-only environment improves application security as developers cannot inadvertently or maliciously update a production application.
• The JDBC driver is updated to conform to the new time zone upgrading scheme. This feature provides a simplified time zone patching process. As a result, Java applications using the TIMESTAMP WITH TIME ZONE data type are immune to Daylight Saving Time (DST) changes.

Database Administrators

• 11gR2 includes a feature called Automatic Block Repair, which allows corrupt blocks on the primary database or physical standby database to be automatically repaired, as soon as they are detected, by transferring good blocks from the other destination. In addition, RECOVER BLOCK is enhanced to restore blocks from a physical standby database. This reduces block recovery time by using up-to-date good blocks from a real-time, synchronized physical standby database as opposed to disk or tape backups or flashback logs.
• Oracle now offers backup to Amazon S3, an internet-based storage service, with the Oracle Secure Backup (OSB) Cloud Module. This is part of the Oracle Cloud Computing offering. This feature provides easy-to-manage, low cost database backup to Web services storage, reducing or eliminating the cost and time to manage an in-house backup infrastructure.
• DUPLICATE can be performed without connecting to a target database. This is particularly useful for DUPLICATE to a destination database where connection to the target database may not be available at all times.
• Tablespace point-in-time recovery (TSPITR) has been enhanced to be repeated multiple times for the same tablespace. Previously, once a tablespace had been recovered to an earlier point-in-time, it could not be recovered to another earlier point-in-time.
• Large, mission critical applications are often unavailable for long periods of time while database objects are patched or upgraded. In such cases, Edition-based redefinition can be used. Edition-based redefinition allows an application’s database objects to be changed without interrupting the application’s availability by making the changes in the privacy of a new edition.
• The new STANDBY_MAX_DATA_DELAY session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.
• Applications connected to a primary database can transparently failover to the new primary database upon an Oracle Data Guard role transition. Integration with Fast Application Notification (FAN) provides fast failover for integrated clients. Flexibility and manageability of disaster recovery configurations using Oracle Data Guard is improved.
• Traditionally, parallel execution has enabled organizations to manage and access large amounts of data by taking full advantage of the I/O capacity of the system. In-memory parallel execution harnesses the aggregated memory in a system to enhance query performance by minimizing or even completely eliminating the physical I/O needed for a parallel operation. In-memory parallel query harnesses the aggregated memory in a system for parallel operations, enabling it to scale out with the available memory for data caching as the number of nodes in a cluster increases. This new functionality optimizes large parallel operations by minimizing or even completely eliminating the physical I/O needed because the parallel operation can now be satisfied in memory.
• Fast refresh of a materialized view is now significantly faster due to reducing the time spent on log handling. This provides significantly reduced maintenance time and more fast refreshes are possible.
• Additional functionality has been added to the Cluster Verification Utility (CVU), for Oracle RAC, in regard to checking certain storage types and configurations. Furthermore, it gives more consideration to user-specific settings. These enhancements provide easier implementation and configuration of cluster environments and improved problem diagnostics in a cluster environment.
• 11gR2 now supports up to 5 copies of the Oracle Cluster Registry (OCR) for improved availability of Oracle RAC. OCR has been improved to incorporate faster relocation of services on node failure.
• Grid Plug and Play (GPnP) eliminates per-node configuration data and the need for explicit add and delete nodes steps for Oracle RAC. Grid Plug and Play reduces the cost of installing, configuring, and managing database nodes by making their per-node state disposable. It allows nodes to be easily replaced with regenerated state.
• The patching of Oracle Clusterware and Oracle Real Application Clusters can now be completed without taking the entire cluster down. Patchsets are now installed as out-of-place upgrades to the Oracle Grid infrastructure for a cluster software (Oracle Clusterware and Automatic Storage Management) and Oracle Database.
• A new version of Oracle Clusterware is now installed into a separate home from the current installation. This reduces the downtime required to upgrade a node in the cluster and facilitate the provisioning of clusters within an enterprise. The benefit is a reduction in planned outage time required for cluster upgrades which assists in meeting availability service levels. This also makes it easier to provide a standard installation across the enterprise.
• This release includes a feature called Instance Caging. Instance Caging allows the DBA to limit the CPU usage of an Oracle instance by setting the CPU_COUNT initialization parameter and enabling CPU resource management. With Instance Caging, users can partition CPU resources among multiple instances running on a server to ensure predictable performance.
• Oracle Database 11g Release 2 (11.2) users can now get e-mail notifications on any job activity. This feature improves efficiency by enabling users to be notified of any job activity that is of interest to them without having to constantly monitor the job.
• File watcher is a new feature that enables jobs to be triggered when a file arrives on a given machine. This feature improves efficiency and ease-of-use. Jobs with file dependencies are automatically triggered when the specified file is received instead of constantly monitoring for the file.
• Enterprise Manager Support Workbench (Support Workbench) has been enhanced to help diagnose and package incidents to Oracle support for Automatic Storage Management (ASM) databases. This feature extends the benefit of Enterprise Manager Support Workbench to ASM by helping customers package all necessary diagnostic data for incidents.
• New in Oracle Database 11g Release 2 (11.2), the Database Smart Flash Cache feature is a transparent extension of the database buffer cache using solid state device (SSD) technology. The SSD acts as a Level 2 cache to the (Level 1) SGA. Database Smart Flash Cache can greatly improve the performance of Oracle databases by reducing the amount of disk I/O at a much lower cost than adding an equivalent amount of RAM.
• Today, disk drives have 512 byte sectors. Disk drive manufacturers are moving to 4 KB sector drives because it allows them to offer higher capacity with lower overhead. If customers use 4 KB sector drives as 512 byte sector drives, then there is likely to be a performance penalty (because they have to run in 512 byte emulation mode). 11gR2 allows Oracle to work with 4 KB (and 512 byte) sector drives without a performance penalty. There is also the capability in Automatic Storage Management (ASM) to allow migration of a disk group from 512 byte sector drives to 4 KB sector drives.
• 11gR2 includes the ASM Cluster File System (ACFS) which extends Automatic Storage Management (ASM) by providing a robust, modern, general purpose file system for files beyond the Oracle database files. ACFS provides support for files such as Oracle binaries, report files, trace files, alert logs, and other application data files. With the addition of the Oracle ASM Cluster File System, ASM becomes a complete storage management solution for both Oracle database and non-database files.
• Disk drives have higher transfer rates and bytes per track on the outer tracks. This makes it preferable to keep the hotter data closer to the edge of the disk; that is, the lower numbered blocks. 11gR2 includes a feature called ASM Intelligent Data Placement, which enables ASM to identify higher performance disk regions. Most frequently accessed ASM files can be marked to be moved into the hot region and take advantage of higher I/O performance (for example, hot tablespaces and indices) and able to better meet the application I/O demand.