Migrating Applications Between Oracle Databases

by Kelly C. Bourne

Migrating an application can be an extremely unsettling event for both you and your users. In this article, Kelly gives you practical tips and advice on how to plan for, undertake, and succeed in your migration process.

Moving applications from one database to another can be a tricky prospect at best. Certain situations, however, warrant this migration. Perhaps the existing server is being phased out in favor of a newer, more powerful one. Or maybe the goal is to balance the processing load more evenly between database servers. Or possibly an application needs to be used by another department, and that department wants to run the application on its own server.

No matter your reason for moving your application from database to database, this movement will affect the application and parts of your database. You will need to migrate certain database objects from their current environment to a new one. Fortunately, developers and DBAs don't perform this migration process every day. Unfortunately, this may be the first time that these developers and DBAs are confronted by this situation. In this article, I hope to give you some practical tips and advice on how to plan for, undertake, and succeed in this application-migration process.

In migrating an application from one database to another, two things need to be migrated to the new server: data and objects. Moving data, although it can be voluminous, is fairly straightforward. There are also many database objects that must accompany an application when it is moved. Although each of these objects is likely to be small, the diversity of objects can be surprising. The trick when dealing with this aspect of the migration effort is to avoid overlooking anything. If even a small object is left behind, the application may execute incorrectly, or it may not function at all.

Migrating Data

The first step you must perform when migrating your data is determining which tables the application accesses. You will need to export only these tables from the current database, and then import them into the new database. In a perfect world, the tables referenced by the affected application will be easy to identify. Typically, a single database schema organizes all of an application's data. In this case, you simply export all of the tables in the application schema. You can write the command to export all tables within a particular schema in a parameter file. The command to execute the parameter file and the contents of the file itself is:

exp parfile = export.ctl
owner/password 
file=export.exp 
compress=y 
full=n
buffer=256000
compress=y
indexes=y
constraints=y
owner=APP_OWNER
log=/db001/oracle/export.log

The meaning of the entries in the parameter file are as follows:

· file: file to which output is directed

· compress: compresses the output

· full: determines if a full export will be done; specify "n" if less than the entire database is to be exported

· buffer: size of the buffer used in transfer operation

· grants: any grants on the table(s) will be exported

· indexes: any indexes on the table(s) will be exported

· constraints: any existing constraints on the table(s) will be

exported

· owner: the owner of tables being exported

· log: the log file in which results are documented

When an application references tables in multiple database schemas, the process is more complicated. Documentation may be available that lists the database tables on which the application is dependent. If you have no readily available documentation, you must obtain this information the hard way by searching the source code for all SQL statements and the database tables they reference. It's likely that the majority of an application's tables are referenced via a SELECT statement. To identify all tables for an application, the search must also include other types of SQL queries. Therefore, search an application's source code for every type of table reference, including the following: CREATE TABLE, DROP TABLE, INSERT, DELETE, UPDATE, SELECT, and UPDATE STATISTICS.

By using either the available documentation or the results of the searches described previously, or by using both methods, you can compile a list of all tables that an application uses. After you identify all of these tables, you must export them. An export command can include a list of tables to export. You can change the owner line of the above parameter file to specify the tables to export. The exact format of this option is: tables=(table1, table2, table3).

Migrating Objects

Data may represent the bulk of the bytes you must migrate, but other database objects need to come along for the ride. The types of objects that might have to be isolated and moved are listed in Table 1 . You can break down these objects into three categories: table-related objects, user-oriented objects, and logic-type objects.

Application source code might not necessarily reference all of these object types. Examples of such object types are tablespaces, indexes, constraints, triggers, and clusters. The types of objects that an application will reference in its code are tables, views, database links, synonyms, sequences, roles, stored procedures, functions, and packaged objects (procedures, functions, variables, and so on).

Hopefully, your shop requires (and enforces) documentation that details which objects each application uses. If you don't have any documentation on the application, then you may spend a significant portion of your effort researching which objects the migration will affect. You can accomplish this best by examining the application's source code; read the code and identify objects that the application references.

Once you identify the objects to migrate, you must extract them from the current database. Unfortunately, these objects all reside in different system tables. You need to execute a different SELECT statement to extract each type of object. The following sections list the necessary SQL query statements for each object category.

Tablespaces. Information regarding tablespaces is in SYS.DBA_TABLESPACE. You can extract it with the statement:

SELECT
 tablespace_name,
 initial_extent,
 next_extent,
 min_extents,
 max_extents,
 pct_increase
FROM sys.dba_tablespaces;

The data files that you specify for the migrated tablespace will depend on the disks and space available on the destination server. The format of the statement to create a tablespace will be similar to:

CREATE TABLESPACE new_tablespace
 DATAFILE 'disk_file_name' SIZE 50M
 DEFAULT STORAGE (
  INITIAL 50K
  NEXT 50K
  MINEXTENTS 2
  MAXEXTENTS 50
  PCTINCREASE 0)
OFFLINE;

Data Clusters. A data cluster is a storage option for table data. For example, an indexed data cluster typically stores master and detail tables that applications frequently access together with joins. A hash data cluster stores a single table. In either case, the hoped-for result from this clustering is greater efficiency when accessing the tables in the cluster. To determine if clusters are in use and whether you need to migrate them, you need access to two tables: SYS.DBA_CLUSTERS and SYS.DBA_TABLES. To ascertain whether your application uses any tables that are in a cluster, execute the following SQL query:

SELECT
  t.table_name,
  t.owner,
  c.cluster_name,
  c.owner
 FROM
  sys.dba_clusters c,
  sys.dba_tables t
 WHERE c.cluster_name = t.cluster_name;

If you need any clusters, you can obtain further information about them from the SYS.DBA_CLUSTERS table. Use these details in the CREATE CLUSTER statement. You must create clusters before you can create tables within them. The SELECT statement to get cluster information is:

SELECT * 
 FROM sys.dba_clusters
 WHERE cluster_name = 'cluster_name';

The statement to create a cluster is:

CREATE CLUSTER [schema.]cluster_name
 (column1_name datatype, column2_name datatype, )
 [PCTUSED integer}
 [PCTFREE integer]
 [INITRANS integer]
 [MAXTRANS integer]
 [SIZE integer]
 [TABLESPACE tablespace_name]
 [STORAGE storage_clause]

Views. Many applications utilize views to restrict access to data, enforce security, or make the SQL statements simpler. Views are also used to reduce the dependency on base tables. If the name or location of a table is changed, a view can insulate applications from the change. Updating the view to reflect changes in name or location will enable the application to continue running without any modifications. You can determine if there are any views in the database by referencing the SYS.DBA_VIEWS table. The "text" column contains the statement that actually created the view. This information can be used to re-create the view in the new database. The command to obtain details on views is: SELECT * FROM sys.dba_views.

The column "text" in the result set contains the SQL statement that you must execute to re-create the view in the destination database. The "text" column is a LONG datatype; therefore, in order to see all of the data in this column, you will probably need to widen this column. Enter the following two SET commands in SQL*Plus to display all of the field. (Information in the column text_length reveals how many characters are in the text column.)

SET LONG 1500
SET LONGCHUNKSIZE 1500.

Indexes. Indexes are used to enforce uniqueness in a table as well as to speed access to data. Indexes are automatically re-created on a table if the export command includes the "indexes=y" option.

Constraints. Constraints are employed to control entity, domain, and referential integrity within a database. Constraints are automatically re-created for a table if the export command includes the "constraints=y" option.

Database Links. A database link is used to enable an application to reference objects in remote Oracle databases. You can retrieve the details about a particular link from the database via the command: SELECT * FROM sys.dba_db_links. You can use the information you obtain via this statement to re-create the link in the destination database. The command to create a database link is:

CREATE [PUBLIC] DATABASE LINK link_name
 CONNECT TO  user IDENTIFIED BY password
 USING 'connect_string'

Synonyms. A synonym is an alias for a database object. You can create aliases only for certain types of objects, including tables, sequences, procedures, stored functions, packages, snapshots, and other synonyms. To identify the synonyms that exist in the database, execute the statement: SELECT * FROM sys.dba_synonyms. Once you know which synonyms exist in the original database, you can reproduce them in the destination database. The SQL statement to create synonyms is:

CREATE [PUBLIC] SYNONYM
 [schema.]synonym_name
 FOR [schema.]object[@dblink]

Sequences. Most applications require that you create numerical primary keys for tables, such as order numbers, employee numbers, and invoice numbers. Rather than creating routines within an application that perform this function, applications can employ an Oracle sequence. To gather information about any sequences in the database, use the SQL command: SELECT * FROM sys.dba_sequences.Perhaps the most important piece of information to glean from this query's results is the last_number column, which indicates the most recently generated value for a sequence. The sequence generates its next value using the INCREMENT BY value (ascending or descending). To avoid duplicating sequence values in the new database, make sure to properly increment (or decrement) the START WITH parameter of the corresponding CREATE SEQUENCE statement. The command to create a sequence in the destination database is:

CREATE SEQUENCE [schema.]sequence 
 [INCREMENT BY integer]
 [START WITH integer]
 [MAXVALUE integer |
 NOMAXVALUE]
 [CYCLE | NOCYCLE]
 [CACHE integer | NOCACHE]
 [ORDER | NOORDER]

Users. Each application user must be defined in the new database. You can obtain details about users with the DML statement: SELECT * FROM sys.dba_users. The result set will contain a column called "password." Unfortunately, the password column isn't as useful as you might expect, because passwords are encrypted within the database. Therefore, when you create new user accounts, it isn't practical to assign them their current passwords. Instead, first assign users a default or initial password, which they can modify after their first login to the new database. The statement to create users is:

CREATE USER user_name
 {IDENTIFIED BY password | IDENTIFIED EXTERNALLY } 
 DEFAULT TABLESPACE tablespace_name
 TEMPORARY TABLESPACE tablespace_name
 QUOTA integer ON tablespace_name [, ...]
 PROFILE profile_name

Roles. A role is a collection of related privileges that you can use to exercise system type functions or access database objects. Applications typically employ one or more roles. You can grant roles to users or to other roles. By utilizing roles, you can significantly reduce the amount of overhead required when implementing database security. Assigning roles to users also provides an easy method of identifying the way in which the user interacts with the application and database. A user assigned to a VIEWER role is obviously going to simply view the data; a user assigned to an EDITOR role is more likely to modify the data. You can view the roles defined in the database by executing the statement: SELECT * FROM sys.dba_roles. The command to create roles is:

CREATE ROLE role_name
 {IDENTIFIED BY password | IDENTIFIED EXTERNALLY}

Grants. GRANT statements are used to grant privileges to roles and users. Two different types of grants are available. The first type grants the ability to exercise a system privilege, such as CREATE TABLE, ALTER ANY TABLE, and CREATE ANY TABLE. The second type grants the ability to access a database object that belongs to another user. Execute the following query to obtain the list of the privileges granted to each role or user: SELECT * FROM sys.dba_role_privs. The SQL used to grant a system privilege or role to a user is:

GRANT {system_privilege | role} 
 TO {user | role | PUBLIC}
 [WITH ADMIN OPTION]

The statement format that grants access to user objects is:

GRANT object_priv 
 ON [schema.]object 
 TO {user | role | PUBLIC}
 [WITH GRANT OPTION]

where object_priv can be ALTER(indexes, sequences, tables), DELETE

(tables, views), EXECUTE(functions, procedures, packages), INDEX(tables), INSERT(tables, views), REFERENCES(tables), SELECT(sequences, snapshots, tables, views), or UPDATE(tables, views). If a privilege or role is granted to PUBLIC, all users, regardless of their other security grants, will have corresponding access to the object.

Grants of both types can give the receiver the ability to grant the same privilege to other users. In the first type of grant, the receiver can grant the same privilege to other users using the WITH ADMIN OPTION. In the second type of grant, the receiver accomplishes this with the WITH GRANT OPTION. The column admin_option indicates whether this privilege has been specified on each grant in the SYS.DBA_ROLE_PRIVS table.

Profiles. A profile limits the system resources that a user can consume. It may be necessary to limit system resources in order to prevent a small number of users from consuming excessive system resources. When you define a new user, you assign him or her a profile. You can alter an existing user to a new or different profile, and the limits defined for that profile then become the resource limits for that user. DEFAULT defines limits for users that don't have a specific profile assigned to them. The query to list any existing profiles is:

SELECT * FROM sys.dba_profiles. You can use the information obtained to duplicate profile information in the destination database. The statement to create profiles in the target database is:

CREATE PROFILE profile_name LIMIT
 [SESSIONS_PER_USER value]
 [CPU_PER_SESSION value]
 [CPU_PER_CALL value]
 [CONNECT_TIME value]
 [IDLE_TIME value]
 [LOGICAL_READS_PER_SESSION value]
 [LOGICAL_READS_PER_CALL value]
 [COMPOSITE_LIMIT value]
 [PRIVATE_SGA value]

where "value" can be an integer, UNLIMITED, or DEFAULT. The units of measurement for the above settings differ depending upon the parameter specified. A summary of parameters and related unit of measurement is shown in Table 2 .

Stored Procedures. A stored procedure is a block of SQL statements that resides and executes in the database server. You can obtain the list of stored procedures with the following SQL statement. Use the SET LONG and SET LONGCHUNKSIZE commands to control the length of the column "text"; the contents of this field will be fairly lengthy - you will probably need to direct the output to a file instead of directly to the terminal. (The column "text" in the result set returned by this statement contains the SQL statement required to re-create the stored procedure on your target database.)

SELECT *
 FROM sys.dba_source
 WHERE type = 'PROCEDURE'

Functions. A function is similar to a stored procedure, except that it returns a value to the caller. You can extract a list of functions with the following SQL statement. (The column "text" in the result set returned by this statement contains the SQL statement required to re-create the function on the destination database.)

SELECT *
 FROM sys.dba_source
 WHERE type = 'FUNCTION'

Triggers. A trigger is a specialized form of stored procedure. It is logically attached to a table and executes (or fires) when a specified activity occurs on that table. You can obtain a list of triggers in a database using the query: SELECT * FROM sys.dba_triggers. The column "trigger_body " in the result set returned by this statement contains the SQL statement required to re-create the trigger on the destination database.

Packages. A package is an encapsulated collection of PL/SQL programs (procedures, functions, cursors, constants, and variables). Packages provide a number of advantages over standalone procedures and functions. Using packages, Oracle can read multiple package objects into memory simultaneously. Their use enables all procedures and functions in the package to reference global variables and cursors. You can produce a list of packages in the database with the query:

SELECT *
 FROM sys.dba_source
 WHERE type = 'PACKAGE'

Packages consist of two distinct parts: specifications and body. The specifications are the collection of public objects as they are referenced from either outside or inside the package. The package body defines the objects - that is, the SQL statements that constitute each object. The statement to create a package specification is:

CREATE [OR REPLACE] PACKAGE [schema.]package_name
 [AS |[ IS] pl/sql _package_specs

where pl/sql_package_specs are the SQL commands that declare the objects' names, calling parameters, and the values they return. The package body is created with the following SQL statement:

CREATE [OR REPLACE] PACKAGE BODY  
 [schema.]package
 [AS OR IS] pl/sql_package_body

where pl/sql_package_body are PL/SQL commands that define the functions the objects perform.

Migrating Other Application Components

Until now I've only discussed handling database objects, but there are other, nondatabase, objects that might also be affected, including application source code, application executables, application libraries (DLLs), database and application initialization files, help files, bitmaps, and other tables.

If you are deploying an application to new users or workstations, you may need to copy some objects to the local drives of new users or a new network server. DLLs, help files, and bitmaps are examples of objects that you can probably just copy from the old location to the new. You will likely need to modify other nondatabase objects to reference the new database and/or server.

Initialization files (*.ini) are prime candidates for change, because they frequently include parameters that control with which database and server an application will attempt to connect. You must modify this file to make an application connect to the new location of the database.

Alternatively, some applications haven't been designed to utilize initialization files. In these cases, you might need to update the source code of the application. Once you change the source code to reference the new database, you need to compile the source code and build a new executable. You can then deploy the new executable.

The development tool used to build an application might have created a few of its own database tables. One example of a tool that does this is PowerBuilder, which creates six tables (pbcatcol, pbcatedt, pbcatfmt, pbcattbl, and pbcatvld) in the target database. These tables are used to hold information related to column headers, data validation, masking, and formatting. If the development tool creates tables of this nature, you must migrate the tables as well. Failure to do so will result in problems when the application is executed.

What to Leave Behind

What remains in the original database after you have migrated an application? The answer to this question is complicated if some objects in the database are used by multiple applications. Users, for example, might still need to log into the original database. In this case, you must duplicate the user entries in the new database without deleting them from the original database. Some tables might be referenced by many applications. If any of these applications will continue to connect to the original database, you must duplicate the tables, which can lead to very complicated problems relating to duplication of data.

Performing the Actual Migration

A number of steps lead up to the actual migration. The first step is to perform a test migration. Complete all of the steps required to migrate tables and other objects, export the tables from the source database and import them into the destination database, and execute the SQL statements necessary to extract details about users, triggers, roles, and so on. Use the information elicited to create SQL scripts that duplicate these objects in the destination database. Modify executables and other nondatabase objects as necessary.

Once you migrate everything, begin testing. Logon to the application and verify that everything functions correctly. Test all of the nooks and crannies of the application. If any of these tests fail, determine the cause of the failure.

When you reach the point at which all of the testing worked and everything looks good, you can perform the final migration. You must perform this last migration in order to copy over changes that have occurred since the first migration was done, although many of the objects originally migrated probably won't have been modified. In fact, it's very likely that no changes have been made to your triggers, stored procedures, roles, grants, synonyms, constraints, tablespaces, or views. On the other hand, it's almost certain that changes have been made to tables, indexes, sequences, and, possibly, users. Data will have been inserted into the system, and tables and indexes will have been affected by this new data. The creation of new records will almost certainly have updated sequence values. The final migration must include all of the objects that were changed. Failure to completely capture these changes will result in an out-of-date database and seriously perturbed users.

Ensure that the timing and duration of the final migration is well coordinated with the user community. Make everyone aware, well in advance, of when the migration will occur and how long it will take to complete. Many systems cannot be out of service for extended periods of time. If minimizing downtime is critical, you should run through a few additional practice runs of this process. If your environment isn't under strict time constraints, migrate the application between the close of business one night and when users come into the office the next morning. The exact sequence of migration events is: 1. Users stop making changes to the original database; 2. Data is exported from the original database; 3. Data is imported into the destination database; 4. Application executables and related objects are modified to connect to the new database; 5. Final testing is performed; and 6. Users are allowed to log into the application.

Migrating an application can be an extremely unsettling event for both you and your users. You can accomplish the task successfully, though, with a certain amount of knowledge, diligence, thought, and planning.

Note: The SQL statements shown herein to create objects are not meant to be an exhaustive or complete explanation of those statements. To fully understand the statements and their full range of parameters, please reference the appropriate Oracle documentation.

Kelly C. Bourne is a principal of Bourne Solutions in Omaha, Nebraska. He specializes in client/server database design and application development. You can reach Kelly via telephone at 402.697.8328 or email at [email protected].

User Oriented Objects
users
roles
grants
profiles

Logic Objects
stored procedures
functions
database triggers
packages

Back to text