Chapter 7
Migrating from Non-Oracle Databases

by Advanced Information Systems, Inc.

In This Chapter

•  System Migration—The Sum of All Our Fears

•  Common Migration Scenarios

•  Downsizing

•  Using Oracle’s Data Conversion Tool—DCT

System Migration—The Sum of All Our Fears

If you are considering the migration of data from a non-Oracle to an Oracle database, you may feel that you are on the edge of the precipice, about to fall over. This is a common perception of a very typical scenario for the owners of a new Oracle application. With the standardization of the Relational Database and the tendency to migrate from legacy systems, there are many newer Oracle applications using converted data from other databases and/or file systems. Whatever problems you may face, they have probably been solved before, so there is a great deal of opportunity to purchase tools and expertise. Yet it is important not to rely only on general knowledge; the migration of data into Oracle many times depends on specific domain and technical expertise within your company and the willingness of all parties involved to bring about a successful migration.

This chapter will look at the topic of migration from both a higher-level managerial standpoint and the more concrete level of a programmer analyst. We will define, in general terms, the key issues of migrating data from another source into Oracle, and will supply examples from today’s most common migration platforms and databases that tend to be evolving into new Oracle applications.

The Realities of System Migration

In most cases, you view the migration from another data store to Oracle as the replacement of one application or database with another; yet it is more of an evolution, because many times the domain expertise of the old application and maybe even portions of the applications code will not change throughout the migration. An example of this might be a migration of a Sybase database to Oracle, where the PowerBuilder front-end does not change; it is just modified to speak to Oracle instead of Sybase (see Figure 7.1).

Figure 7.1.  A Sybase to Oracle database migration with only portions of the front-end code changed.

On the other end of the spectrum, there are times where most of the previous system will be put out of existence once the new Oracle system is created to replace it. These systems are called sunset systems and have a limited life-span after which the new Oracle system will exclusively survive. Even with these systems, human resources who used the old system and management and technical structures which supported it will need to adapt to the new system (see Figure 7.2). For instance, if you are replacing an IBM Mainframe CICS/VSAM application with an Oracle/Oracle*Forms 4.5 application, you still do not want to lose all of the human domain knowledge from the older system. You also will need to rely on the experts of the older system for help in the migration effort.

Figure 7.2.  Even with the complete migration to a totally new physical system, business domain expertise must evolve from one system to another.

This discussion is to stress that along with technical tools, an organization needs the human experts of both the old and new systems to be willing to go forward with the migration. If there is resistance to any migration effort, whatever technical tools you may possess, the migration will suffer. For example, if a mainframe group is resisting a downsizing effort to an Oracle RDBMS, this will upset the needed communication and knowledge transfer that must exist for the downsizing to succeed.

The Myth of ANSI SQL

For those migration efforts where you are moving from one relational database to another, there is the myth of a standard SQL to be shattered. Although you were told when you bought our first relational database that it was compatible with any SQL database, the odds are that your application is not. This is due to the bells and whistles each vendor adds to its SQL implementation.