Migrating from Version 6 to Version 7
This chapter describes the techniques that are available to migrate a Version 6 database to a Version 7 database. The specific topics covered in this chapter are
Migrating a Version 6 Database
This section contains the following topics:
Careful planning and proper tools can greatly reduce the complexity of migrating a Version 6 database. Oracle provides several tools, such as the Export/Import and Migration utilities, to make the migration process as simple as possible.
The Migration Utility can be used only to transform an earlier
version to a later version of the Oracle database. The Migration
Utility cannot be used for reverse migration. For example, the Migration Utility cannot be used to transform a Version 7 database to a
Version 6 database.
In addition, the following features of Oracle aid in the
Version 6 to Version 7 migration process:
- Version 6 can communicate with any of the Oracle7 releases using SQL*Net.
- The programming interface (for example, in the Oracle Precompilers and Oracle Call Interface) are the same for both Version 6 and Version 7. You can use either SQL*Net or relink applications designed for a Version 6 database to run them with any of the Oracle7 releases.
Although Oracle7 SERIALIZABLE mode is compatible with SQL92 and offers many benefits, compared with read-locking implementations, it does not provide semantics identical to such systems. Application developers should take into account the fact that reads in Oracle7 do not block writes as they do in other systems. For example, transactions that check for database consistency at the application level require coding techniques such as the use of SELECT FOR UPDATE. This issue should be considering when applications using SERIALIZABLE mode are ported to ORacle7 from other environments.
For more information about SERIALIZABLE isolation, see the Oracle7 Server Application Developer's Guide.
Preparing to Migrate from Version 6 to Version 7
Become Familiar with the Features of the Release 7.x Target Database
The features and functionality of Version 6 and the Release 7.x databases are significantly different. You should pay particular attention to the following topics when migrating a Version 6 database:
- padded vs. non-padded comparison semantics
- privilege and resource management
- multi-threaded server configuration
- character encoding scheme used for the database
Using the Migration Utility to Migrate from Version 6 to a Version 7 Release
This section contains the following topics:
General Considerations for Version 6 Migration
The Migration Utility is a program that converts some of the files
and structures in your Version 6 database to Oracle7, Release 7.x format. The primary advantages of the Migration Utility are its speed and ease of use. The Migration Utility takes significantly less time
than exporting/importing, and the migration process using the
utility consists of a few easy steps. However, you cannot selectively migrate datafiles. Thus, the Migration Utility is useful if you
want to migrate an entire Version 6 database to an Oracle7, Release 7.x database quickly.
When you use the Migration Utility, the entire database is converted, including database files, rollback segments, and the control file. At any point before actually migrating your source database (Step 5), you can still open and access data with Oracle Version 6. However, once you have performed Step 5 and migrated the Version 6 database to Oracle7, Release 7.x with the Migration Utility, you can only go back to Version 6 by restoring a full backup of the database. If you decide to use the Migration Utility as the method for migrating your database, see Chapter 4 "The Migration Utility" for detailed information about the Migration Utility and its use.
The following Version 6 structures must be converted before they can be used in any Oracle7 release:
- datafiles (file header only)
The Migration Utility creates a new data dictionary based on the Version 6 data dictionary. When the data dictionary is converted, all objects and users defined in the new dictionary automatically conform to all Oracle7 release specifications. Conversion of datafiles occurs later in the migration process. Only the file headers are changed; data blocks have the same format in Oracle7 releases as in Version 6.
- data dictionary information
The Migration Utility requires that the SYSTEM tablespace have enough free space to hold the Release 7.x data dictionary and the existing Version 6 data dictionary concurrently. If necessary, add space to the SYSTEM tablespace.
The space required to hold the Release 7.x data dictionary varies depending on how many objects are in the database. Typically, a Release 7.x data dictionary will be approximately one and a half times as large as your Version 6 data dictionary.
If you do not allocate enough space in the SYSTEM tablespace, the Migration Utility will not complete the migration. An error will be returned stating how much additional space needs to be allocated.
To determine how much space is needed, run the Migration Utility with the CHECK_ONLY option set to TRUE. This causes the utility to do the space check only, without actually building the Release 7.x data dictionary. The database must be opened for this option to work.
Data Dictionary Conversion when using the Migration Utility
The following changes are made to information stored in the
Warning: If any row in a Version 6 table is inconsistent with the constraints of Version 7, in other words, a Version 6 row violates one or more Version 7 integrity constraints, the integrity constraints remain disabled. Oracle returns an error message indicating that the integrity constraints are disabled.
- Integrity constraints defined in Version 6 are initially disabled in Oracle7 releases (except NOT NULL constraints, which are enabled after migration). After migration, you can manually enable these constraints as needed, using the ENABLE clause of the ALTER TABLE command.
For more information, see Oracle7 Server SQL Reference.
Migrating to Trusted Oracle7
The Migration Utility cannot migrate a Version 6 database to Trusted Oracle7. One of the methods of migrating a Version 6 database to Trusted Oracle is to migrate from Version 6 to Oracle7 using the Migration Utility, and then to migrate this database to Trusted Oracle. For information about migrating from Oracle7 to Trusted Oracle7, see Trusted Oracle7 Server Administrator's Guide.
Step 5: Migrate the Version 6 Database (using the Migration Utility)
Before proceeding, you should review the migration steps outlined in Chapter 1 "Migration Overview". The following steps expand on Step 5 of the migration procedure and explain how to migrate your source database using the Migration Utility.
1. Install and run the Migration Utility.
Additional Information: This task is operating system specific. Refer to your operating system-specific Oracle documentation.
There are several parameters that can be passed to the Migration Utility; these are outlined below. They are discussed in more detail in your operating system-specific Oracle documentation.
|CNVFILE specifies CONVERT.ORA filename (if not using the expected default).|
Warning: Do not attempt to open your database with
Version 6 of Oracle after running the Migration Utility. If you open the Version 6 database before proceeding with the remaining steps, the migration process (specifically, Step 6, described in Chapter 3 "The Migration Utility") will fail with an error.
|MIGFILE specifies MIGRATE.BSQ filename (if not using the expected default).|
The Migration Utility creates a binary file that creates the control file. The name of this file is operating system dependent. Do not alter this file in any way.
Additional Information: Installation of Release 7.x is operating system specific. Refer to your operating system-specific Oracle documentation. Read the README.DOC file included with your Release 7.x installation for any late additions or modifications to the product.
3. Issue the following command to connect to the Release 7.x instance:
SVRMGR> CONNECT INTERNAL
Note: You must be connected as INTERNAL to perform the rest of the migration. The connect procedure is different from Version 6. You must now issue CONNECT INTERNAL
4. Start a Release 7.x instance, but do not mount the database. Issue the following command:
SVRMGR> STARTUP NOMOUNT
5. Issue the following command:
SVRMGR> ALTER DATABASE CONVERT;
6. Open the Release 7.x database by issuing the following command:
SVRMGR> ALTER DATABASE OPEN RESETLOGS;
All datafiles and rollback segments that are online when the Release 7.x database is opened are converted to Release 7.x format. Refer to the section in this chapter "Preserving Your Version 6 Database" for more information on shutting down the source (original production) database and database file and rollback segment conversion.
DROP USER migrate CASCADE;
Objects in this user's schema are no longer needed once the conversion is complete. You can also delete the binary file that creates the Oracle7 data dictionary base tables.
Run the Release 7.x CATPROC.SQL script which will run all of the scripts required for, or used within, PL/SQL.
If you wish to create additional data dictionary structures, see Oracle7 Server Reference for a complete list and description of available scripts.
Using Export/Import to Migrate from Version 6 to a Version 7 Release
This section contains the following topics:
Basic Export/Import Steps
The Export/Import method of migrating a database involves the following two steps:
1. The first step is to export the data from the database that you wish to migrate.
2. The second step is to import the exported data into the database to which you wish to migrate.
You must create the target database (the Release 7.x database) before using the Export/Import method of migration.
The Export Utility copies the data in your source database to an export file, from which the Import utility can load the data into an Oracle7, Release 7.x database. An important distinction between Export/Import and the Migration Utility is that the physical data in your database is copied to a new location with Export/Import, while the Migration Utility changes only the file headers and the definitions of the data in the files where they currently reside.
The Export/Import method of migration provides some
- You can defragment the data. A full database import can compact the data and improve performance.
- You can restructure your database; that is, you can create new tablespaces, or modify existing tables or tablespaces.
Since the Export/Import method of migration does not change the Version 6 database, your Version 6 database is available at any point during the migration process. This allows you to keep a Version 6 database running in parallel with an Oracle7, Release 7.x database without requiring the restoration of a backup. (However, you should not change the Version 6 database unless you make exactly the same changes to the Oracle7, Release 7.x database.) Also, a full export can serve as an archive of your Version 6 database.
- You can migrate only certain database objects or users. You can selectively import only objects and users that need to be imported at a given time.
The Export/Import method has the following limitations:
- For a large database, a full database Export/Import can take a significant amount of time.
- For a large database, a full database Export/Import can require a substantial amount of temporary storage space for the data.
If you decide to use Export/Import as the method for migrating your database, see Chapter 5 "Migrating Using Export/Import" for detailed information about using Export/Import.
- Because you should not make changes to the Version 6 database after performing the export, your application is unavailable until the migration is completed.
When migrating a Version 6 database to a Version 7 database, you would first export the desired data from the Version 6 database. Then you would import the exported data into the Version 7 database.
To use the Export/Import method, the Oracle Corporation recommends that you use the version of the Export Utility shipped with the version or release of the source database, which, in this case, would be Version 6. Once you have exported the desired data, you must then use the version of the Import Utility shipped with the version or release of the destination database. You should use the Export and Import utilities for migration only after you have carefully read Part I of Oracle7 Server Utilities.
Data Definition Conversion when using Export/Import
When importing data from one version to another version, the Import Utility makes changes to data definitions as it reads in the export file. For example, when importing data from Version 6 to an Oracle7 release, the Import utility makes the following changes to data definition statements in the export file:
Space Requirements for Export/Import
The amount of space required for an export depends upon the amount of data you are exporting. Examine the views USER_SEGMENTS or DBA_SEGMENTS to determine the amount of space occupied by the data. These views give you the number of segments allocated, but keep in mind that some segments can be allocated but unused. Refer to the Oracle7 Server Administrator's Guide for more information on estimating space usage.
Time Requirements for Export/Import
The Export/Import method of migration may require several hours. Therefore, you may need to schedule your migration during non-peak, production hours. The time required to complete a migration will, of course, increase for databases that contain large amounts of data or a large number of indexes.
Note: Once an Export/Import migration has started, the database being migrated is unavailable for all production tasks.
Step 5: Migrate the Version 6 Database (using Export/Import)
The following Export/Import steps are completely general and can be applied to any Oracle version or release.
To migrate an Oracle database using Export/Import, perform the following steps:
1. Export all desired objects from the original database using the Export utility shipped with the original database. Refer to Oracle7 Server Utilities for a complete description of how to use the Export utility for Version 6.
Additional Information: Installation of an Oracle7 release is operating system specific. See your operating system-specific Oracle documentation. Read the README.DOC file included with your Release 7.x installation for any late additions or modifications to the product.
4. Open the Release 7.x database and start an instance. From the Server Manager prompt, issue the following commands:
SVRMGR> CONNECT INTERNAL
5. You should pre-create tables, tablespace, and users in Release 7.x as necessary, for example, to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus or Server Manager, you must either run in the original database compatibility mode or specifically make allowances for the data definition conversions outlined earlier in this chapter under "Data Definition Conversion when using Export/Import".
6. Import the data and tables from the original database export using the Import utility shipped with Release 7.x. Refer to Oracle7 Server Utilities for a complete description of how to use the Import utility.
7. After migrating, you may discover that your tables were not imported properly. Views and synonyms may not be created in the correct order when dependencies exist (for example, when a view is based on a synonym). You may have to perform one of the following procedures to finish importing correctly:
- If all of the rows for all tables were not successfully imported, repeat the import until it completes successfully. Be certain that IGNORE=Y and ROWS=N. IGNORE=Y causes Import to overlook "object already exists" errors and ROWS=N indicates that you do not want to import the rows of table data.
- If some tables were imported successfully, while others were not even created, repeat the import with IGNORE=N and ROWS=Y to ignore the "object already exists" errors and re-import the rows.
For more information on the use of the Export and Import utilities,
see Oracle7 Server Utilities.
- If the tables had some, but not all rows imported, drop the incomplete tables and repeat the previous step.
Preserving Your Version 6 Database
This section contains the following topics:
Shut Down the Source (original production) Database
The Version 6 database must be shut down normally so that there is no outstanding redo information, and no uncommitted transactions.
If you migrate using the Migration Utility, all source
datafiles that are online when the target database is opened are automatically converted to the target database file format. Files that are offline when the target database is opened remain in the source database file format.
You do not need to convert all of the database files to Oracle7, Release 7.x format immediately. The remaining files are converted when they are brought online in Oracle7, Release 7.x.
Rollback segments are converted as they are accessed by the Oracle7, Release 7.x database. Thus, all rollback segments that are in tablespaces that are online when the database is first opened in Oracle7, Release 7.x are converted. If a source database rollback segment is in a tablespace that is offline when the Oracle7, Release 7.x database is opened, it is converted the first time it is brought online in Oracle7, Release 7.x.
Back up the Source (original production) Database
After shutting down the database, you should make a full backup of the source database before proceeding with migration. Be certain to back up all datafiles, control files, parameter files, online redo log files, and any script files used to create objects in the source database.
Suggestion: You should make a backup of the online redo log files even though there should be no outstanding redo information. This allows you to easily restore your source database if necessary.
You can only migrate with offline tablespaces that were taken offline cleanly, using the NORMAL or TEMPORARY options; otherwise (for example, if you had performed an ALTER TABLESPACE OFFLINE IMMEDIATE), you receive an error. For 126.96.36.199, or later, databases you can simply bring these tablespaces online and then back offline with no other changes, and repeat the migration procedure.
Before release 188.8.131.52, offline tablespaces may appear to have outstanding save undo, even if they were taken offline cleanly. If you are certain that your offline tablespaces do not have outstanding save undo, you can set the ALLOW_OFFLINE migration parameter to TRUE to continue the migration.
Warning: When ALLOW_OFFLINE is TRUE, any offline tablespaces that were not cleanly taken offline will be lost after migration. You should not use this parameter with a release 184.108.40.206, or later, database.
Change Parameter Files (INIT.ORA Files)
Certain initialization parameters are obsolete in all Oracle7, releases. You must remove all obsolete parameters from any parameter file
that starts an Oracle7, Release 7.x instance. Obsolete parameters may cause errors if used with an Oracle7, Release 7.x database. You must also alter any parameter whose syntax has changed in Oracle7, Release 7.x. Refer to Appendices A, B, C, and D for lists of obsolete and changed parameters for Release 7.0, Release 7.1, Release 7.2, and Release 7.3, respectively.
Change SQL Scripts
All SQL scripts that you built that created objects in the source database should be changed to conform to Oracle7, Release 7.x syntax.
VARCHAR is synonymous with VARCHAR2 in all Oracle7 releases. However, VARCHAR may have different properties in a future version of Oracle. If any of your scripts use VARCHAR, you should update them to use VARCHAR2.
In addition, there are two new reserved words in the SQL language: ROWLABEL and VARCHAR2. If any of the objects in your database have names that match either of the new reserved words, you must take the following action, depending upon the object type:
- If the reserved word is used as a column heading, you must copy the data to a new table with a different column name.
If you want to use a reserved word as an object name, the reserved word must be enclosed in double quotes (for example, "ROWLABEL"). Refer to Oracle7 Server SQL Reference for more information on
- If the reserved word is used as the name of a table, sequence, synonym, or view, you must rename the object.
Attention: The Version 6 CHAR datatype is equivalent to the new Oracle7 VARCHAR2 datatype, except for a difference in maximum length. VARCHAR2 datatype values are variable-length character strings with a maximum length of 2000. Oracle compares VARCHAR2 values using non-padded comparison semantics. In all Oracle7 releases, values with the CHAR datatype are fixed-length character strings with a maximum length of 255 characters. Oracle7 compares CHAR values using blank-padded comparison semantics.
Update Database Administration Scripts
For database administration scripts, you should use the Version 7 CREATE USER and GRANT CREATE SESSION commands instead of the Version 6 GRANT commands for creating users and assigning resource quotas. The GRANT syntax continues to provide similar effects in all Oracle7 releases as in Version 6, but future versions of Oracle may not support this functionality.
Warning: You must assign resource quotas to each user using the QUOTA option of the CREATE USER or ALTER USER commands. There is no equivalent to granting a resource to PUBLIC. Resource quotas are not assigned in the GRANT command in Oracle7 releases. You should remove any resource quotas in GRANT statements.
Granting the RESOURCE role in an Oracle7 release permits you to have the UNLIMITED TABLESPACE privilege, so the following statement is equivalent in Version 6 and all Oracle7 releases:
GRANT RESOURCE TO user1;
Note: If you use any Server Manager or SQLDBA scripts to start instances or open the database, you must CONNECT AS SYSOPER or CONNECT AS SYSDBA (this command was CONNECT INTERNAL under Version 6) before issuing the STARTUP command in Oracle7 releases.
To comply with ANSI/ISO SQL standards, two dashes, "- -", are now recognized as starting a comment. Although it is unlikely that you are using two dashes outside a quoted string, you should be aware that all Oracle7 releases treat the text following "- -" as a comment.
Update Database Link Names
Oracle7 releases assume that any characters following the @ sign in a database link are part of the database name. For instance, in Version 6, to select the column ENAME from the EMP table at BOSTON, you could have typed the following statement:
SELECT firstname.lastname@example.org FROM emp@boston
With Oracle7 releases, you must change this SELECT statement to the following statement:
SELECT ename FROM emp@boston
Move Constraint Identifiers
The constraint clause for the CREATE TABLE command has new syntax. The optional constraint identifier (CONSTRAINT name) has moved from the end of the clause to the beginning of the clause. For example, the following example shows a constraint clause used in a Version 6 SQL statement:
CREATE TABLE emp
FOREIGN KEY (deptno) REFERENCES dept (deptno)
In Oracle7 releases, this same statement must be written as
CREATE TABLE emp
FOREIGN KEY (deptno) REFERENCES dept (deptno))
Version 6 scripts that do not contain a CONSTRAINT identifier require no modification and run with all Oracle7 releases. However, Version 6 scripts that do contain a CONSTRAINT identifier must be modified, as shown in the previous example, before running the scripts with an Oracle7 release. See Oracle7 Server SQL Reference for a description of the CONSTRAINT clause, Chapter 4 "The Migration Utility" for comments on integrity constraints, and
Chapter 9 "Upgrading and Downgrading between Oracle7 Releases" for a discussion of compatibility mode.
Change Unique Indexes to UNIQUE or PRIMARY KEY Integrity Constraints
Integrity constraints can be used in Oracle7 releases to enforce uniqueness among column values. Because unique indexes might
not be supported in future versions of Oracle, you should begin using UNIQUE or PRIMARY KEY integrity constraints instead of
Indexes can now be validated using the ANALYZE command. You should begin using this command, because the VALIDATE INDEX command might not be supported by future versions of Oracle.
Tune the New, Release 7.x Database
Most methods used to tune a Version 6 database and related applications have either the same effect or are unnecessary for Release 7.x. Any actions you took to tune your source database and applications should not impair the performance of Release 7.x.
Release 7.x includes several new features designed to improve performance of your database and related applications. Some of these features, such as the multi-threaded server configuration and shared SQL areas, require little or no action on your part to implement. Other features, such as hashed clusters, direct path loader, cost-based optimization, and even roles, have the potential to improve performance. For additional information on tuning the database, see Oracle7 Server Tuning.
Enabling and Disabling Release 7.x Features
After migrating to an Oracle7, Release 7.x database, certain steps must be followed to properly enable new Release 7.x features.
Once you have migrated to a Release 7.x database, you may wish to downgrade to your previous Version 6 database. Before you downgrade to your previous Version 6 database, you must disable some of the features that were enabled for operation with the Release 7.x database.
For more information on enabling and disabling Release 7.x features, see Chapter 9 "Upgrading and Downgrading Between Oracle7 Releases".
Add New Features as Appropriate
Appendices A, B, C and D of this manual describe many of the new features available in Oracle7 releases. You should determine which of these new features can benefit your application and develop a plan
for incorporating them. Release 7.x databases offer many new features that can affect not only your database design, but your application design as well. You do not, however, have to make any immediate changes to begin using your Release 7.x database. You may prefer to introduce these enhancements into your database and corresponding applications gradually.
Chapter 8 "Migrating Version 6 Applications" describes how you
can enhance your applications to begin taking advantage of these
new features. You should already have tested your applications
and successfully run them with Release 7.x in a special V6 compatibility mode.
Develop New Administrative Procedures as Needed
After familiarizing yourself with the new Release 7.x features, you should review your database administration scripts and procedures to determine if any changes are necessary. Some topics to consider are
- managing mirrored online redo log files
- administering the multi-threaded server
- distributing your database to improve performance
- determining optimal rollback segment size
- reclaiming unused space in datafiles using the Release 7.2 "Resizeable Datafiles" enhancement
Completion of the final migration steps described in this chapter will allow you to begin taking advantage of Release 7.x functionality. You need to coordinate your changes to the database with the changes that need to be made to each application. For example, by enabling integrity constraints in the database, you may be able to remove some of this data checking from your applications.
- secure connections for privileged users
Downgrading to a Previous, Version 6 Database
You might want to return (downgrade) to a Version 6 database after migrating to Release 7.x. If you have not entered any new data with Release 7.x, you can restore a complete backup of the Version 6 database and open it again. Be certain to use a complete backup that contains the original initialization parameters that were used in the Version 6 database. Once you have entered data with Release 7.x, you cannot reverse the methods of migration described in this manual.
Warning: You must disable certain features of the 7.x database before you return to a Version 6 database. For more information about setting the COMPATIBLE parameter, see Chapter 9, "Upgrading and Downgrading between Oracle7 Releases".
However, there are alternative methods of sending table data from a Release 7.x database to a Version 6 database. For more information about upgrading and downgrading between the Version 7 releases, see Chapter 9 "Upgrading and Downgrading between Oracle7 Releases".
- You can use the SQL*Plus COPY command to copy the data from the Release 7.x tables into the tables in the earlier version or release database.
- You can create the table again in the earlier version or release database (using CREATE TABLE AS SELECT) by selecting the data in the Release 7.x table through a distributed query from the source database to Release 7.x using a database link.
- You can run the CATEXP6.SQL file on your Release 7.x database. Then perform a Version 6 export of the Release 7.x data and import that data into the Version 6 database. To see which of your Release 7.x database objects will not be exported over SQL*Net when using a source database export, run UTLEXP6.SQL. Note that you cannot import a Release 7.x export into a Version 6 database.
For more information about performing a Version 6 export of Release 7.x files, see Oracle7 Server Utilities. For more information about the COPY command, see the SQL*Plus User's Guide and Reference. For more information about the AS clause of the CREATE TABLE command, see Oracle7 Server SQL Reference.
- You can use SQL*Plus to create non-Oracle text files from the Release 7.x database and then use SQL*Loader to load this data back into a Version 6 database.
Each of these methods of replacing data have some restrictions.
These restrictions, outlined below, hold only for replacing data in Version 6 databases. Not all restrictions apply to all methods.
For more information about restrictions, see Chapter 8 "Migrating Version 6 Applications".
- If a table has columns of character-type data (CHAR, which blank-pads data), any trailing spaces are retained when the data is copied to Version 6. Use an UPDATE statement, similar to the example shown below, if you want to remove these trailing spaces after copying the data to Version 6.
UPDATE emp SET ename = RTRIM(ename)
- If a table has columns of type VARCHAR2 in Release 7.x, the Version 6 table must have the same columns of type CHAR.
- If columns of type VARCHAR2 in Release 7.x contain any rows with data longer than 255 characters, you must either truncate this data to 255 characters or fewer or store the data in multiple fields before copying the data.
These methods of returning data to Version 6 are relatively simple if few tables have been updated using Release 7.x. However, copying an entire database of tables can be a long and complicated task, so you should decide whether you need to return to Version 6 before many tables are updated using one of the releases of Oracle7.
- Only tables can be copied. Synonyms, sequences, views, and any other database objects created or changed in Release 7.x must be re-created in the Version 6 database.