Entry Topic
Oracle7 Server Migration Guide

ServerMigration Guide

Preface

CHAPTER 1. MIGRATION OVERVIEW

CHAPTER 2. BEFORE MIGRATION BEGINS

CHAPTER 3. THE MIGRATION UTILITY

CHAPTER 4. MIGRATING VIA EXPORT/IMPORT

CHAPTER 5. AFTER CONVERTING THE DATABASE

CHAPTER 6. MIGRATING YOUR APPLICATIONS

APPENDIX A. SUMMARY OF CHANGES IN ORACLE7

APPENDIX B. MIGRATION UTILITY ERRORS AND MESSAGES

APPENDIX C. OPERATING SYSTEM SPECIFIC INFORMATION


This help file created via:

 Version 1.0m

© 1994 Oracle Corporation Inc.

Authored by KM .


ServerMigration Guide

August,, 1993

Part No. A12520-1

Oracle7 Server Migration Guide

Part No. A12520-1

Copyright  Oracle Corporation 1993

Contributing Authors:  Eric Armstrong, John Frazzini, Maria Pratt

Contributors:  Karleen Aghevli, Cynthia Closkey, Stephen Faris, Michael Hartstein, Ken Jacobs, Andrew Laursen, Karen Masterson, Tuomas Pystynen, Gregory Pongracz, Kevin Wharton

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law.  Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend

Use, duplication, or disclosure by the Government is subject to restrictions

as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in

Technical Data and Computer Software (October 1988).

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice.   If you find any problems in the documentation, please report them to us in writing.   Oracle Corporation does not warrant that this document is error-free.

Oracle, Pro*Ada, Pro*Cobol, Pro*Fortran, Pro*PL/I, Pro*Pascal, SQL*DBA, SQL*Forms, SQL*Loader, SQL*Net, SQL*Plus and SQL*ReportWriter are registered trademarks of Oracle Corporation.  Oracle7, Oracle Parallel Server, PL/SQL, and Pro*C are trademarks of Oracle Corporation.

VMS is a registered trademark of Digital Equipment Corporation


Preface

This Guide describes the process of migrating a database and associated applications from ORACLE Version 6 to ORACLE7, to aid database administrators and system programmers in planning and implementing the migration process.

This Guide describes the procedures that are involved in migration, and explains the function and use of the migration utility. However, this Guide does not detail information that is documented in other sources.

This Guide does not describe certain details of migration, such as the command syntax for the Export and Import utilities. That information is described in the ORACLE7 Server Utilities User's Guide. Where more detail is required, this Guide refers the reader to the appropriate sources of information.

________________________________________

Audience

This Guide is written for people responsible for migrating a Version 6 database and its associated applications to ORACLE7. These people can include database administrators, application programmers, security administrators, and system operators.

Knowledge Assumed of the Reader

Readers of this guide are assumed to be familiar with Version 6 and with the operating system environment under which ORACLE is running. Readers should also be familiar with the new features provided in ORACLE7 as described in Appendix A of this document and the ORACLE7 README.DOC file.

________________________________________

How this Guide Is Organized

This guide is divided into the following chapters:

Chapter 1:  Migration Overview   This chapter describes the procedures involved with migration and the responsibilities of database administrators and application programmers.

Chapter 2:  Before Migration Begins   This chapter describes the steps to take before you begin migrating the database.

Chapter 3:  The Migration Utility   This chapter describes the process of migrating your database using the migration utility.

Chapter 4:  Migrating via Export/Import   This chapter describes the process of migrating a database using the Export and Import utilities.

Chapter 5:  After Converting the Database   This chapter describes the steps to take after you have converted the database using either the migration utility or Export/Import.

Chapter 6:  Migrating Your Applications   This chapter describes how to run your existing applications against an ORACLE7 database.

Appendix A:  Summary of Changes in ORACLE7   This appendix describes the differences between Version 6 and ORACLE7.

Appendix B:  Migration Utility Errors and Messages   This appendix lists the errors you may see when using the migration utility. For each error, the probable cause and corrective action are given.

Appendix C: Operating System Specific Information   This appendix lists references in this Guide to yourinstallation or user's guide.

________________________________________

Related Publications

In many sections of this document, the information is general because this document is written to apply to ORACLE working under any operating system and configuration. Therefore, you will also find many references in this book to important information in the related publications. To clearly indicate an area in this document that may be supported by information in a related book, a corresponding icon is placed in the margin of the page, adjacent to the text referring to the supporting book. The related books (and corresponding icons) referred to in this book are included below.

•	For information about ORACLE Version 6, see the ORACLE RDBMS Database Administrator's Guide, Part No. 3601-V6.0.

•	For conceptual  information about ORACLE7, see the ORACLE7 Server Concepts Manual, Part No. 6693-70.

•	For information about administering an ORACLE7 Server, see the ORACLE7 Server Administrator's Guide, Part No. 6694-70.

•	For  information about developing applications for an ORACLE7 Server, see the ORACLE7 Server Application Develepor's Guide, Part No. 6695-70.

•	For information about the utilities bundled with ORACLE , including Export, Import, and SQL*Loader, see the ORACLE7 Server Utilities User's Guide, Part No. 3602-70.

•	For information specific to administering the ORACLE Parallel Server, see the ORACLE7 Server Parallel Server Guide, Part No. 5990-70.

•	For more information about ORACLE  messages and codes, see the ORACLE7 Server Messages and Codes Manual, Part No. 3605-70.

•	For reference information on ORACLE's SQL commands and functions, see the ORACLE7 Server SQL Language Reference Manual,  Part No. 778-70.

•	For information about ORACLE's procedural language extension to SQL, PL/SQL, see the PL/SQL User's Guide and Reference, Part No. 800-20.

•	For information on the SQL*Plus utility see to the SQL*Plus User's Guide and Reference, Part No. 5412-V3.1.

•	For more information on the SQL*Forms utility see the SQL*Forms Designer's Reference, Part No. 3304-30.

•	For information specific to ORACLE working on your host operating system, see your installation or user's guide (specific book titles vary by operating system), and system release bulletins, if available.

________________________________________

Conventions Used in this Guide

The following conventions are observed in this Guide:

Syntax	Appears in the following font for commands:

STARTUP

UPPERCASE 	Indicates a word or phrase to be entered exactly as spelled, although your operating system may allow the words to be entered in lower or mixed case.

A "warning" icon placed in the left margin is used to call out very important information in the adjacent text to which you should pay special attention.

________________________________________

Your Comments Are Welcome

We value and appreciate your comments as an ORACLE user and reader of the manuals. As we write, revise, and evaluate, your opinions are the most important input we receive. At the back of this manual is a Reader's Comment Form which we encourage you to use to tell us what you like and dislike about this guide or other ORACLE manuals. If the form has been used or you would like to contact us, please use the following address or FAX number, or call us at (415) 506-7000.

ORACLE7 Server Documentation Manager

 Oracle Corporation

 500 Oracle Parkway

 Redwood City, CA   94065

 FAX:  (415) 506-7200


CHAPTER 1. MIGRATION OVERVIEW

ORACLE7 is upwardly compatible with ORACLE Version 6. The migration process consists of a set of procedures that transform an existing Version 6 database (including applications that access this database) to work in the same manner as in Version 6 and, optionally, to use new ORACLE7 functionality.

Several steps in the migration process occur before you begin to migrate the actual data in your database. Also, once you have migrated the database, there are several additional steps that deal with testing and adding ORACLE7 functionality.

This chapter gives you an overview of the migration process, and the responsibilities of database administrators and application programmers during the migration process. Depending upon the migration method that you select, you will be referred to other chapters of this document for additional information on the migration process.

If you are migrating to Trusted ORACLE, see the Trusted ORACLE7 Server Administrator's Guide for additional details.

________________________________________

Migrating a Database from One Version to Another

Careful planning and proper tools can greatly reduce the complexity of migrating a database. ORACLE provides several tools, such as the export, import and migration utilities, to make the migration process as simple as possible.

In addition, several features of ORACLE aid in the migration process:

•	Version 6 and ORACLE7 databases can communicate via SQL*Net.

•	The programming interface used (for example, by the ORACLE Precompilers and ORACLE Call Interfaces) has not changed. You simply need to relink applications designed using ORACLE Version 6 to run them with ORACLE7.

•	A backward compatibility mode is provided when there are small incompatibilities between Version 6 and ORACLE7 functionality.

•	Many ORACLE7 features are automatically available after migration. Several of these features, such as shared SQL areas, reduced instruction costs, and the multi-threaded server architecture, should result in improved performance.

•	Many ORACLE7 features require little work to implement (such as new security management schemes and multiplexed redo log files). Once again, several of these features, such as cost-based query optimization and hashed data access should result in improved performance.

This Guide is designed to aid in the planning and implementation of migrating your database from Version 6 to ORACLE7. It is designed to guide you through each step of the process and explain the tools and features involved with each step.

________________________________________

Overview of the Migration Process

Before attempting to migrate your database, you should understand the requirements and procedures involved with each step of the migration process. The following list is a step-by-step overview of the migration process. The details of each step are discussed in later chapters. This list is a guideline for approaching migration.

1.	Become familiar with ORACLE7 features and the changes from Version 6 to ORACLE7.

2.	Decide on the migration method you need to use (the migration utility, Export/Import, or copying data via database links). Refer to the section "step2." on page 2 - 2 for a description of which migration method is most appropriate for you.

3.	Develop a plan for testing the database after migrating to ORACLE7. Perform the tests on your Version 6 database and record the results to compare with the ORACLE7 results.

4.	Rehearse the migration of your database by proceeding with steps 6 through 11 using a subset of your Version 6 database. If you are using the migration utility, do not migrate your Version 6 database; create a test Version 6 database and migrate the test database.

5.	Test your applications with the sample ORACLE7 database and be certain that your applications run correctly with ORACLE7 before migrating your production database.

6.	Shut down the Version 6 database normally so that there are no uncommitted transactions and there is no outstanding redo information in the redo log files.

7.	Make a complete backup of the Version 6 database. Be certain to back up all data files, control files, and parameter (INIT.ORA) files, and any script files used to create objects in your Version 6 database. Although there should not be any outstanding redo information in the redo log files, you should back up these files as well, in case any Version 6 data files are lost or unreadable. You should keep this backup until you are using ORACLE7 as your production database.

8.	Delete or update any obsolete or changed parameters in your parameter (INIT.ORA) files.

9.	Update any SQL scripts that you currently use to create tables or clusters.

10.	Convert the database to ORACLE7 format using the migration method that you chose (such as export/import or the migration utility). Both methods require the installation of ORACLE7 during this step.

11.	Make a full, offline backup of the ORACLE7 database, including redo log files, after successfully opening the ORACLE7 database.

12.	After you have successfully migrated a test database, continue with step 5. After you are certain that your applications work with ORACLE7, you can repeat steps 6 through 11 using your production database.

13.	Test the ORACLE7 database using the testing plan developed in step 3. Compare the results with the results from Version 6.

14.	Make minor tuning adjustments as needed, to ensure that the ORACLE7 database performance is as good as or better than Version 6.

15.	Determine which new ORACLE7 features are appropriate to use with your database and update your applications accordingly.

16.	Develop new database administration procedures as needed.

After completing this process, your database should be completely converted to ORACLE7. Users can then begin using the new ORACLE7 database.

________________________________________

Role of the Database Administrator During Migration

Typically, the database administrator is responsible for ensuring the success of the migration process. Specific duties of the database administrator might include scheduling the migration process, making backups of the Version 6 and ORACLE7 databases, and performing the actual migration procedure. The database administrator should arrange to meet with everyone involved in the migration process to clearly define everyone's roles during migration.

The previous section is a guide for the database administrator for planning and implementing the migration process. The database administrator typically becomes involved in each step of the process, except for steps 5, 12, and 15, which are generally performed by application developers.

________________________________________

Role of the Application Developer During Migration

While the database administrator is responsible for migrating the database from Version 6 to ORACLE7, the application developer is responsible for ensuring that applications designed for Version 6 work in the same way using ORACLE7.

Before the migration process begins, the database administrator or application developer should install an ORACLE7 database for testing so that applications can be tested and modified, if necessary, to work with the same (or enhanced) functionality. Migration of production users to ORACLE7 should not begin until all applications have been tested and operate properly.

The application developer should note changes in ORACLE7 that may affect particular applications. Many of these changes are described in Appendix A of this Guide. Appendix A of the ORACLE7 Parallel Server Guide and the ORACLE7 Server SQL Language Reference Manual give additional descriptions of changes from Version 6 to ORACLE7. Appendix A of this Guide also lists changed data dictionary views upon which an application might depend. The application developer should use all of these sources to identify any modifications that need to be made in existing applications. In addition, Chapter 6 of this Guide describes the changes necessary to cause applications accessing a Version 6 database to access an ORACLE7 database in a backwards compatibility mode, as well as how to upgrade these applications to take advantage of new ORACLE7 functionality.

________________________________________

Returning to Version 6

You might want to return to using Version 6 after migrating to ORACLE7. If you have not entered any new data with ORACLE7, you can restore a backup of the Version 6 database and open it again. Once you have entered data with ORACLE7, you cannot reverse the methods of migration described in this Guide.

However, there are alternative methods of sending table data from an ORACLE7 database to a Version 6 database:

•	You can use the SQL*Plus COPY command to copy the data from the ORACLE7 tables into the tables in the Version 6 database.

•	You can create the table again in Version 6 (using CREATE TABLE AS SELECT) by selecting the data in the ORACLE7 table through a distributed query from Version 6 to ORACLE7 using a database link.

•	You can run the CATEXP6.SQL file on your ORACLE7 database. Then perform a Version 6 export of the ORACLE7 data and import that data into a Version 6 database. To see which of your ORACLE7 database objects will not be exported over SQL*Net when using an ORACLE Version 6 export, run UTLEXP6.SQL. Note that you cannot import an ORACLE7 export into a Version 6 database.

•	You can use SQL*Plus to create non-ORACLE text files from the ORACLE7 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 are outlined below. Not all restrictions apply to all methods.

•	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 ORACLE7, the Version 6 table must have the same columns of type CHAR.

•	If columns of type VARCHAR2 in ORACLE7 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.

•	Only tables can be copied. Synonyms, sequences, views, users, and any other database objects created or changed in ORACLE7 must be re-created in the Version 6 database.

These methods of returning data to Version 6 are relatively simple if few tables have been updated using ORACLE7. 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 ORACLE7.

Refer to the SQL*Plus User's Guide and Reference for more information on the COPY command, the ORACLE7 Server Utilities User's Guide for information on performing a Version 6 export of ORACLE7 files, and the ORACLE7 Server SQL Language Reference Manual for more information on the AS clause of the CREATE TABLE command.

________________________________________

Migrating from Version 5 of ORACLE

If you are migrating from Version 5 of ORACLE, it is suggested that you migrate to Version 6 before migrating to ORACLE7. Before migrating, you should become familiar with the changes in architecture of ORACLE from Version 5 to Version 6. All of these changes carry forward into ORACLE7.

A good source of information about the differences from Version 5 to Version 6 is Appendix A of the ORACLE RDBMS Database Administrator's Guide Version 6.0. Also, take an Oracle training class to become familiar with migrating from Version 5 to Version 6.

The migration utility for ORACLE7 can only migrate Version 6 of ORACLE. If you want to use the migration utility, you must first migrate to Version 6.

You can use the Export/Import utilities to transfer data from a Version 5 database to an ORACLE7 database. There are certain changes in functionality that require restrictions on importing from Version 5 to ORACLE7. The Version 6 README.DOC file details these restrictions.

The process for migrating a Version 5 database to ORACLE7 via Export/Import is similar to migrating from Version 6, and is described in Chapter 4, "Migrating Via Export/Import."


CHAPTER 2. BEFORE MIGRATION BEGINS

This chapter describes all tasks that need to be performed before the actual migration of your database. This chapter discusses steps 1 through 9 of the migration process in greater detail, including the following topics:

•	becoming familiar with ORACLE7

•	installing a test database

•	choosing a method of migration

•	developing a testing plan

•	preparing to implement the migration process

________________________________________

step1.

Before you begin to plan the migration process, you should be familiar with the new features of ORACLE7. The sources listed in the section "Related Publications" of the Preface describe ORACLE7 functionality in detail. A good starting point for learning how ORACLE7 differs from Version 6 is Appendix A of this Guide. This appendix lists the changes in ORACLE7 and gives specific references for more information about each new feature. If you are using the Parallel Server option, you should also see the ORACLE7 Parallel Server Administrator's Guide for changes in the Parallel Server.

NIL.

You should pay particular attention to the following topics:

•	VARCHAR2 datatype

•	padded vs. non-padded comparison semantics

•	integrity constraints

•	privilege and resource management

•	redo log files

•	recovery strategies

•	multi-threaded server configuration

•	optimization

•	character encoding scheme used for the database

You should also take an Oracle training class to better understand how to best take advantage of the functionality available with ORACLE7.

________________________________________

step2.

There are several methods of migrating a database from Version 6 to ORACLE7:

•	You can use the migration utility.

•	You can perform a full or partial database import into ORACLE7 from a full or partial database export of a Version 6 database.

•	You can copy data from a Version 6 database to an ORACLE7 database via the COPY command or the AS clause of the CREATE TABLE command.

Each method is appropriate for different circumstances. The following sections describe each method, the amount of time and space required by each, and the situations where each method is appropriate.

Whichever method is chosen, you need to ensure that the character encoding scheme used for data in the ORACLE7 database is correctly specified. In ORACLE7, all character data in the database is assumed to be in the specific character encoding scheme specified when the database was created with the CREATE DATABASE command. Refer to the National Language Support appendix of the ORACLE7 Server Concepts Manual for more information on NLS.

The Migration Utility

The migration utility is a program that converts some of the files and structures in your Version 6 database to the ORACLE7 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 data files. Thus, the migration utility is useful if you want to quickly migrate an entire Version 6 database to ORACLE7.

The migration utility does not require a significant amount of temporary space. The utility requires only that you have enough extra room in the SYSTEM tablespace to hold the Version 6 and ORACLE7 data dictionaries simultaneously.

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 database (step 10), you can still open and access data with ORACLE Version 6. However, once you have performed step 10 and migrated the Version 6 database to ORACLE7 with the migration utility, you can only go back to ORACLE 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 3 for detailed information about the migration utility and its use.

Export/Import

The Export utility copies the data in your database to an export file, from which the Import utility can load the data into an ORACLE7 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 is advantageous under the following situations:

•	You want to defragment the data. A full database import can compact the data and improve performance.

•	You want to restructure your database; that is, you want to create new tablespaces, or modify existing tables or tablespaces.

•	You want to migrate only certain database objects or users. You can selectively import only objects and users that need to be imported at the time.

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 database without requiring the restoration of a backup. (Although you should not change the Version 6 database unless you make exactly the same changes to the ORACLE7 database.) Also, a full export can serve as an archive of your Version 6 database.

A database Export/Import has some limitations, however. For a large database, a full database Export/Import can take a significant amount of time and require a substantial amount of temporary storage space for the data. Because you should not make changes to the Version 6 database after performing the export, your application is unavailable until the migration completes.

If you decide to use Export/Import as the method for migrating your database, see Chapter 4 for detailed information about using Export/Import.

Copying Data

You can copy data from a Version 6 database to an ORACLE7 database via database links. You can either copy data from a Version 6 table to an ORACLE7 table with the SQL*Plus COPY command, or you can create new tables in an ORACLE7 database and fill the tables with data from the Version 6 database by using the INSERT INTO command or the AS clause of the CREATE TABLE command.

This method of migration is advantageous under the same circumstances as Export/Import:

•	You want to defragment your data files.

•	You want to restructure your database; that is, you want to create new tablespaces, or modify existing tables or tablespaces.

•	You want to migrate only certain database objects or users, or even a subset of rows.

One reason to use this method of migration and not Export/Import is if you want to select only certain rows of tables into your ORACLE7 database. The Export/Import utilities can only load entire tables and not selected rows. For example, if you wanted to create a new EMP table that contains a subset of the data in your existing EMP table (for example, employees in departments 10, 20 and 30), you would use the following SQL statement in ORACLE7:

CREATE TABLE NEW_EMP

   (EMPNO         NUMBER(4),

   ENAME          VARCHAR2(10),

   JOB            VARCHAR2(9),

   MGR            NUMBER(4),

   HIREDATE	DATE,

   SAL            NUMBER(7,2),

   COMM           NUMBER(7,2),

   DEPTNO         NUMBER(2)) AS SELECT EMPNO, ENAME, JOB, MGR,

                  HIREDATE, SAL, COMM, DEPTNO

   FROM EMP@V6DB  WHERE DEPTNO IN (10, 20, 30);

If you are only migrating a portion of your database, this method can also require less space than other methods. You don't need to allocate large amounts of extra space for temporary files or for export/import files. You simply need your Version 6 and ORACLE7 databases online.

For more information about copying data from one database to another, refer to the CREATE TABLE command in the ORACLE7 Server SQL Language Reference Manual and the COPY command in the SQL*Plus User's Guide and Reference.

________________________________________

step3.

Once the Version 6 database has been successfully migrated to ORACLE7, you should test the data to ensure that all data is accessible, that your applications function properly, and to determine if any database tuning is necessary. If possible, you should automate this testing procedure.

Your testing plan should be representative of the work performed at your site. You should test the functionality and performance of all of your applications on your Version 6 production databases. Gather performance statistics both under normal usage as well as peak usage.

The following tests can assist you in tuning your SQL statements after you migrate to ORACLE7:

1.	Gather performance statistics for each of your SQL statements. You should be able to write scripts to extract these statements from you applications and run them against your production database.

2.	Use EXPLAIN PLAN to determine the execution plan ORACLE follows to execute each SQL statement. Use the INTO parameter to save this information in a table.

After migrating, you can compare performance under ORACLE7 with your Version 6 test results by completing the following steps:

1.	Use the ANALYZE command to gather statistics for each of your tables. Use the CATSTAT.SQL script to collect statistics on all of the objects in a given schema, or if you have the procedural option, you can use the ANALYZE_SCHEMA procedure.

2.	Use EXPLAIN PLAN to determine the execution plan for each statement. Save this information in a new table.

3.	Compare the ORACLE7 execution plans to the Version 6 execution plans. If you notice a difference, you should execute the command in ORACLE7 and compare this performance to the Version 6 performance. In general, you should notice a performance improvement with ORACLE7. If not, you can use the information in the Version 6 execution plan to determine what hints to use to improve the performance of the cost-based optimization method used in ORACLE7.

________________________________________

step4.

You should perform a test migration before beginning the actual migration procedure, so that you can resolve any potential problems before actually migrating your production database. If you plan on using Export/Import to migrate, you can use your actual Version 6 database.

If you plan on using the migration utility, you need to create a test Version 6 database on which to perform the test migration. Refer to your installation or user's guide for information on how to configure another database so that no operating system variables defined for your production database are affected by the test database.

To rehearse the migration of the database, perform steps 6 through 11 of the migration process described in Chapter 1 using either the test database or a subset of the Version 6 database depending upon which migration method you choose.

________________________________________

step5.

After migrating a test database, you should use this test database to make sure that your existing applications operate properly with the ORACLE7 database. You can also begin enhancing Version 6 applications by adding ORACLE7 functionality to them, but it is suggested that you first make sure that the applications operate in the same manner as they did in Version 6. Information on using your applications with ORACLE7 is included in Chapter 6 of this document.

________________________________________

Steps 6-9: Prepare to Perform the Migration Process

There are several tasks to perform before starting the migration procedure. The following list summarizes the steps to take before implementing any of the migration procedures:

1.	Shut down the database normally.

2.	Make a complete backup of the Version 6 database.

3.	Update your parameter (INIT.ORA) files.

4.	Change any SQL scripts that you currently use to create objects.

Step 6: Shut Down the Version 6 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 Version 6 data files that are online in ORACLE7 when the database is opened are automatically converted to the ORACLE7 file format. Files that are offline when the ORACLE7 database is opened remain in the Version 6 file format.

You do not need to convert all of the database files to ORACLE7 format immediately. The remaining files are converted when they are brought online in ORACLE7.

Rollback segments are converted as they are accessed by the ORACLE7 database. Thus, all rollback segments that are in tablespaces that are online when the database is first opened in ORACLE7 are converted. If a Version 6 rollback segment is in a tablespace that is offline when the ORACLE7 database is opened, it is converted the first time it is brought online in ORACLE7.

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 6.0.34.3 databases, or later, you can simply bring these tablespaces online and then back offline with no other changes, and repeat the migration procedure.

Prior to release 6.0.34.3 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 this parameter 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 6.0.34.3, or later, database.

Step 7: Back up the Version 6 Database

After shutting down the database, you should make a full backup of the Version 6 database before proceeding with migration. Be certain to back up all data files, control files, parameter files, online redo log files and any script files used to create objects in Version 6.

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 Version 6 database if necessary.

Step 8: Change Parameter Files (INIT.ORA Files)

Certain initialization parameters have become obsolete with ORACLE7. You must remove all obsolete parameters from any parameter file that is used to start an ORACLE7 instance. Obsolete parameters may cause errors if used with ORACLE7 databases. You must also alter any parameter whose syntax has changed in ORACLE7. Refer to Appendix A for a list of obsolete and changed parameters.

Step 9: Change SQL Scripts

All SQL scripts which you built that create Version 6 objects should be changed to conform to ORACLE7 syntax.

 Update Datatypes

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 ORACLE7, 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.

Although in ORACLE7 VARCHAR is synonymous with VARCHAR2, 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 the reserved word is used as the name of a table, sequence, synonym or view, you must rename the object.

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 your ORACLE7 Server SQL Language Reference Manual for more information on object naming.

 Move Constraint Identifier

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

   (column definitions...

   FOREIGN KEY (deptno) REFERENCES dept (deptno)

      CONSTRAINT deptno_fk)

In ORACLE7, this same statement must be written as shown below.

CREATE TABLE emp

   (column definitions...

   CONSTRAINT deptno_fk

      FOREIGN KEY (deptno) REFERENCES dept (deptno))

If your Version 6 scripts do not contain a CONSTRAINT identifier they will run with ORACLE7. Otherwise, you must make this change before running your scripts with ORACLE7.See the ORACLE7 Server SQL Language Reference Manual for a description of the CONSTRAINT clause, and Chapter 6 for a discussion of compatibility mode.

 Update Database Administration Scripts

For database administration scripts, you should use the new CREATE USER and GRANT CREATE SESSION commands instead of the former GRANT commands for creating users and assigning resource quotas. The GRANT syntax continues to provide similar effects in ORACLE7 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. You should remove any resource quotas in GRANT statements.

Granting the RESOURCE role in ORACLE7 causes the user to have the UNLIMITED TABLESPACE privilege, so the following statement is equivalent in Version 6 and ORACLE7:

GRANT RESOURCE TO user1;

Also, if you use any SQL*DBA scripts to start instances or open the database, note that, unlike in Version 6, you must CONNECT INTERNAL before issuing the STARTUP command in ORACLE7.

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 ORACLE7 treats the text following "--" as a comment.

 Managing Indexes

Integrity constraints can now be used 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 unique indexes.

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.

 Updating Database Link Names

ORACLE7 assumes 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 emp@boston.ename FROM emp@boston

With ORACLE7 you must change this SELECT statement to the following statement:

SELECT ename FROM emp@boston


CHAPTER 3. THE MIGRATION UTILITY

This chapter guides you through the migration procedure using the migration utility with a step-by-step approach. Topics covered in this chapter include:

•	details of the conversions the migration utility performs

•	space requirements for the migration utility

•	migrating the Version 6 database to ORACLE7

•	database file and rollback segment conversion

•	errors encountered during conversion

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. See the Trusted ORACLE7 Server Administrator's Guide for information about migrating from ORACLE7 to Trusted ORACLE.

________________________________________

Overview

The migration utility primarily alters the file structure and data definition information of a Version 6 database so that the database conforms to the ORACLE7 format. Most of the conversion process is automatically completed by the migration utility.

The migration utility converts some structures, others are converted automatically by ORACLE7 itself, and other structures do not need to be converted at all. The following Version 6 structures need to be converted before they are used in ORACLE7:

•	data files (file header only)

•	data dictionary information

•	control files

•	rollback segments

The migration utility converts the data dictionary. When the data dictionary is converted, all objects and users defined in the new dictionary automatically conform to the ORACLE7 specifications. Conversion of data files and rollback segments occurs later in the migration process. Only the file headers are changed; data blocks have the same format in ORACLE7 as in Version 6.

The migration utility uses the character set specified by the LANGUAGE parameter in the INIT.ORA file of the Version 6 database as the character set for the ORACLE7 database. Since this cannot be changed after migration is complete, you must ensure that LANGUAGE specified the correct character set before running the migration utility.

To run the migration utility, you need DBA privileges. You do not interact with the migration utility. Once the utility is invoked, it performs the following migration activities automatically:

•	creates the user MIGRATE as a temporary holding location for data dictionary tables (if you already have a user named MIGRATE in your database, you should export this user's objects before migration, then import the objects after migration)

•	runs the conversion scripts that create the ORACLE7 data dictionary base tables and views

•	changes the bootstrap segments to conform to ORACLE7

•	creates CONVERT.ORA, which is used to create the new control file for the ORACLE7 database (the name of this file may vary on some operating systems)

________________________________________

Data Dictionary Conversion

The migration utility converts the definitions of all objects and users in the data dictionary so that the database conforms to ORACLE7. The following changes are made to the information stored in the data dictionary:

•	Columns defined in Version 6 as type CHAR are defined as type VARCHAR2 in ORACLE7.

•	Users with CONNECT, RESOURCE, or DBA privileges in Version 6 have the respective CONNECT, RESOURCE, or DBA role in ORACLE7.

•	The passwords for SYSTEM and SYS are reset to MANAGER and CHANGE_ON_INSTALL respectively.

•	Integrity constraints defined in Version 6 are initially disabled in ORACLE7 (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.

________________________________________

Space Requirements

The migration utility requires that the SYSTEM tablespace have enough free space to hold the ORACLE7 data dictionary. You must store both the current Version 6 data dictionary and the new ORACLE7 data dictionary that is created during the conversion process. If necessary, add space to the SYSTEM tablespace.

The space required to hold the ORACLE7 data dictionary varies depending on how many objects are in the database, but the ORACLE7 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 does not complete the migration, and an error is 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 ORACLE7 catalog. The database must be started for this option to work.

________________________________________

Step 10a: Using the Migration Utility

Before proceeding, you should review the 16-step outline of the migration procedure that begins on page 1 - 2 of this Guide. The following steps expand upon step 10 of this migration procedure.

1.	Install and run the migration utility. This task is operating system specific; refer to your installation or user's guide.

There are several parameters that can be passed to the migration utility; these are outlined below. They are discussed in more detail in your installation or user's guide.

ALLOW_

OFFLINE	when TRUE to migrate release 6.0.34.3 databases and earlier with offline tablespaces. Be certain that these tablespace were taken offline cleanly before using this option; see page 2 - 8 for details.

ALLOW_

OFFLINE	when TRUE to migrate release 6.0.34.3 databases and earlier with offline tablespaces. Be certain that these tablespace were taken offline cleanly before using this option; see page 2 - 8 for details.

CFILE	specifies the filename of control file (if not using the expected default directory).

CHECK_

ONLY	when TRUE, performs space calculations without performing migration.

CNVFILE	specifies CONVERT.ORA filename (if not using the expected default directory).

DB_NAME	specifies the name of the database to migrate.

DUMPCF	is used as a diagnostic tool (primarily for Oracle Worldwide Customer Support).

ECHO	when TRUE, echoes the commands issued by the MIGRATE.BSQ script.

MIGFILE	specifies MIGRATE.BSQ filename (if not using the expected default directory).

NEW_

DATABASE	specifies a new name for your migrated database.

NO_SPACE_

CHECK	when TRUE, does not perform space check before migration.

PFILE	specifies the name of the parameter file (if not using the expected default, INIT.ORA).

SPOOL	specifies filename of file to which to spool output.

The migration utility creates a user, MIGRATE, to temporarily hold data dictionary information. If you have a user by that name you need to drop all objects owned by this user. You should first either copy the user's objects to another user or export the user's objects.

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 fails with an error. If this occurs, it may be necessary to restore your Version 6 database before you can rerun the migration utility and continue the migration procedure.

The migration utility drops the Version 5 and Version 6 views (such as those created by CATEXP6.SQL) and catalogs (such as those created by CATALOG.SQL). You can usually run the migration utility multiple times losing only these views and catalogs, as well as user-defined views and synonyms. It should only be necessary to restore your database if you issue the command ALTER DATABASE CONVERT. Refer to Appendix B for more information about errors encountered during the migration process.

The migration utility creates a binary file that is used to create the ORACLE7 data dictionary base tables. The name of this file is operating system dependent. Do not alter this file in any way.

2.	Install ORACLE7. Installation of ORACLE7 is operating system specific; refer to your installation or user's guide. Read the README.DOC file included with your ORACLE7 installation for any late additions or modifications to the product.

3.	Issue the following command to connect to the ORACLE7 instance:

SQLDBA> CONNECT INTERNAL

You must be connected as INTERNAL to perform the rest of the migration. Note that, unlike in Version 6, you must issue CONNECT INTERNAL before STARTUP.

4.	Start an ORACLE7 instance, but do not mount the database. Issue the following command:

SQLDBA> STARTUP NOMOUNT

5.	Issue the following command:

SQLDBA> ALTER DATABASE CONVERT;

This command completes the conversion of the data dictionary base tables. After this process is complete, the Version 6 data dictionary tables no longer exist in the ORACLE7 database.

6.	Open the ORACLE7 database by issuing the following command:

SQLDBA> ALTER DATABASE OPEN RESETLOGS

All data files and rollback segments that are online when the ORACLE7 database is opened are converted to ORACLE7 format. Refer to "Step 6: Shut Down the Version 6 Database" on page 2 - 7 for a description of database file and rollback segment conversion.

7.	Once you open the ORACLE7 database and start an instance, you can drop the MIGRATE user created temporarily to hold the data dictionary. Issue the following statement to drop this user and reclaim the space allocated to it:

DROP USER migrate CASCADE;

Objects in this user's schema are no longer needed once the conversion is complete. You can also drop the binary file used to create the ORACLE7 data dictionary base tables.

8.	Run the ORACLE7 CATALOG.SQL script to create the data dictionary views on the ORACLE7 base tables.

SQLDBA> @catalog

You might want to run the CATALOG6.SQL script, which contains Version 6 data dictionary views if you have not yet migrated your applications and the applications rely on obsolete or changed Version 6 data dictionary views. Version 6 views with the same name as ORACLE7 views have a "6" appended to the name to prevent naming conflicts. Refer to Appendix A for a list of changed and obsolete data dictionary views.

If you purchased any ORACLE options (such as the procedural option), you must also run the appropriate script to install that option. These scripts are described in the ORACLE7 Server Administrator's Guide.

Errors During Migration

During the migration procedure, you may encounter errors. Errors can be due to performing migration steps out of order, not performing certain migration steps, not fulfilling the prerequisites for migration, or encountering certain conversion irregularities.

Refer to Appendix B for more information about specific errors encountered during the migration process and the action to take for each error.


CHAPTER 4. MIGRATING VIA EXPORT/IMPORT

This chapter guides you through the migration procedure using the Export/Import method of migration. Although this chapter describes importing data exported from a Version 6 database, Version 5 data can be exported and imported in a similar manner. If you are exporting from Version 5, be certain to read the "Notes for Version 5" section at the end of this chapter.

Topics covered in this chapter include:

•	a description of the Export and Import utilities

•	the data definition conversion that occurs when using Export/Import

•	space requirements for Export/Import

•	how to use Export/Import to migrate

•	when to compress data using Export/Import

•	how to preserve the data's storage options

If you are exporting from, or importing to, a Trusted ORACLE database, there are additional features that you must be aware of. See the Trusted ORACLE7 Server Administrator's Guide for more information about export/import and migration.

________________________________________

Export/Import

The Export/Import method of migrating a database involves exporting the desired data from a Version 6 database then importing that data to an ORACLE7 database. This method of migration is useful if you wish to compress your data, or if you wish to selectively choose tables and users to migrate.

To use the Export/Import method, you must use the version of the Export utility shipped with Version 6 of ORACLE to export the data. You then need to use the version of the Import utility shipped with ORACLE7 to import the data. You should use the Export and Import utilities for migration only after you have carefully read Part I of the ORACLE7 Server Utilities User's Guide.

________________________________________

Data Definition Conversion

When importing data from Version 6 to ORACLE7, the Import utility makes the following changes to data definition statements in the export file:

•	Columns defined as CHAR from a Version 6 export file are changed to VARCHAR2.

•	Users with CONNECT, RESOURCE, or DBA privileges in Version 6 are granted the respective CONNECT, RESOURCE, or DBA roles in ORACLE7.

•	Constraints defined on tables in Version 6 are created in ORACLE7, but they are initially disabled (except NOT NULL constraints, which are enabled after import). After migration, you can manually enable these constraints as needed, using the ENABLE clause of the ALTER TABLE command. The SQL syntax for integrity constraints in Version 6 is different from the syntax used in ORACLE7. Import automatically adjusts the declaration so that integrity constraints are properly imported in ORACLE7 (unless V6 compatibility mode is in effect).

________________________________________

Space Requirements

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.

________________________________________

Step 10b: Using Export/Import

To migrate your database using Export/Import, perform the following steps:

1.	Export all desired objects from the Version 6 database using the Export utility shipped with Version 6 of ORACLE. Refer to Version 6 of the ORACLE RDBMS Utilities User's Guide for a complete description of how to use the Version 6 Export utility.

If you are using Version 5 of ORACLE to export a Version 5 database, see page 4 - 4 for additional notes about the export/import procedure.

2.	Install ORACLE7. A default database may be created in this step. If so, you can proceed to step 4; otherwise, continue with step 3. Installation of ORACLE7 is operating system specific; see your installation or user's guide. Read the README.DOC file included with your ORACLE7 installation for any late additions or modifications to the product.

3.	Create the ORACLE7 database. All of the issues involved in database creation are covered thoroughly in the ORACLE7 Server Administrator's Guide.

4.	Open the ORACLE7 database and start an instance. From the SQL*DBA prompt, issue the following commands:

SQLDBA> CONNECT INTERNAL

SQLDBA> STARTUP

5.	You should pre-create tables and users in ORACLE7 as necessary, for example, to improve space usage by changing storage parameters. When you pre-create tables using SQL*Plus or SQL*DBA, you must either run in V6 compatibility mode or specifically make allowances for the data definition conversions outlined on the previous page that occur during import.

6.	Import the data and tables from the Version 6 export using the Import utility shipped with ORACLE7. Use the CHARSET command line option to specify the character set of the Version 6 database. Refer to the ORACLE7 Server Utilities User's Guide for a complete description of how to use the Import utility.

Notes for Version 5

If you are currently using Version 5 of ORACLE, it is recommended that you upgrade first to Version 6. If you choose to export your Version 5 files and import them directly to ORACLE7, you should be aware of the following.

•	Column level grants are not imported and must be regranted after import.

•	The EXPVEW and EXPTAB views are exported from Version 5, even though you run DROPCAT5.SQL script prior to export. These views are not created when you perform the import to ORACLE7. Ignore the resulting error messages.

•	Views and synonyms do not get created in the correct order when dependencies exist (for example, when a view is based on a synonym). To finish importing complete one of the following procedures:

•	If all of the rows for all tables were 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.

•	If the tables had some, but not all rows, imported, drop the incomplete tables and repeat the import with ROWS=N, IGNORE=Y, INDEXES=N and CONSTRAINTS=N until all objects are created. Then re-import the tables with IGNORE=Y, ROWS=Y, INDEXES=Y and CONSTRAINTS=Y.


CHAPTER 5. AFTER CONVERTING THE DATABASE

This chapter discusses the steps to perform after you have completed converting your database from Version 6 to ORACLE7 using either the migration utility or the export/import method of migration. This chapter deals with steps 11 through 16 of the migration process discussed in Chapter 1. Topics in this chapter include:

•	backing up the ORACLE7 database

•	testing the ORACLE7 database

•	tuning the ORACLE7 database

________________________________________

Step 11: Back Up the ORACLE7 Database

After completing the conversion of your database, you should make a complete offline backup of the new ORACLE7 database. Make sure to back up all data files, control files, online redo log files, parameter files, and SQL scripts that create objects in the ORACLE7 database.

________________________________________

Step 12: Migrate Your Production Database

You should not migrate your production database until you have successfully migrated a sample database and tested your applications to ensure that they work properly with an ORACLE7 database. Information on upgrading your applications is included in Chapter 6 of this Guide.

________________________________________

Step 13: Test the Database and Compare Results

Test the ORACLE7 database using the testing plan you developed in step 3 of the migration procedure, described in Chapter 1 of this document. Compare the results of the test with the results obtained with the Version 6 database and make certain the same results are achieved.

Generally, performance under ORACLE7 should be as good as, or better than, Version 6 performance. If you notice any decline in database performance under ORACLE7, you should check the initialization parameters to ensure that they are set properly. For additional information on tuning the database, see the ORACLE7 Server Administrator's Guide. For information on tuning your database applications, see the ORACLE7 Server Application Developer's Guide.

You might want to run your Version 6 and ORACLE7 databases in parallel for a time, until you are certain that your ORACLE7 database is correctly configured and functioning properly. Any updates would need to be made to both the Version 6 and ORACLE7 databases. Depending upon how your updates are performed, you might be able to automate this process by editing your scripts to modify both versions of the database.

________________________________________

Step 14: Tune Your ORACLE7 Database

Most methods used to tune a Version 6 database and related applications have either the same effect or are unnecessary for ORACLE7. Any actions you took to tune your Version 6 database and applications should not impair the performance of ORACLE7.

ORACLE7 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 the ORACLE7 Server Administrator's Guide. For information on tuning your database applications, see the ORACLE7 Server Application Developer's Guide.

________________________________________

Step 15: Add New Features as Appropriate

Chapter 6 of this document describes many of the new features available in ORACLE7. You should determine which of these new features can benefit your application and develop a plan for incorporating them. ORACLE7 offers 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 ORACLE7 database. You may prefer to gradually introduce these enhancements into your database and corresponding applications.

Chapter 6 of this document 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 ORACLE7 in a special V6 compatibility mode.

Introducing the modifications described in Chapter 6 allows you to begin taking advantage of ORACLE7 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.

________________________________________

Step 16: Develop New Administrative Procedures as Needed

After familiarizing yourself with the new ORACLE7 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

•	using new SQL*DBA features, such as KILL SESSION and DISABLE RESTRICTED SESSION

•	distributing your database to improve performance

•	determining optimal rollback segment size


CHAPTER 6. MIGRATING YOUR APPLICATIONS

This chapter describes how you can use your Oracle tools and applications with ORACLE7. Usually, little or no change is required of your applications to achieve Version 6 functionality running on an ORACLE7 database. This chapter also briefly describes suggested changes that you can make to take advantage of new functionality introduced in ORACLE7. The tools included in this chapter are:

•	ORACLE Programmatic Interfaces

•	SQL*Forms

•	CASE*Dictionary

•	SQL*Report

•	SQL*ReportWriter

•	SQL*Plus

•	SQL*Net

If you are migrating applications to Trusted ORACLE, there are additional features that you need to be aware of. See the Trusted ORACLE7 Server Administrator's Guide for more information about migrating your applications to Trusted ORACLE.

________________________________________

Migrating Your ORACLE Programmatic Interface Applications

The ORACLE Programmatic Interface family consists of the ORACLE Precompilers, the ORACLE Call Interfaces, and the new SQL*Module. The tool that you select to create an application varies depending upon your needs. This section briefly discusses the changes to these tools resulting from the latest release of the database or the tool itself, and the implications of these changes for existing programs.

New Features of the ORACLE Precompilers

Version 1.5 of the ORACLE Precompilers is introduced with ORACLE7, and incorporates many new features designed to enhance precompiler functionality and application performance, as well as allow your applications to access new ORACLE7 capabilities. Most users will upgrade to Version 1.5 at the same time they upgrade to ORACLE7, and Version 1.5 is required for developing new applications.

The following changes have been introduced since Version 1.3 of the Precompilers. Some have appeared in Version 1.4 as well, and are also listed here for convenience.

Datatype Equivalencing  You can now selectively override the datatypes assigned to host variables in the Declare Section, and use those variables in static embedded SQL statements. In C and Pascal, you can also associate an ORACLE datatype with user-defined datatypes. This ability to equivalence datatypes adds flexibility to your applications.

Bundled Database Calls  Whenever possible, only a single bundled database call is made per embedded SQL statement, speeding up communication with ORACLE, especially in a networked environment.

Terser, Faster Generated Code  The interface to the ORACLE runtime library has been revised, reducing the amount of precompiler-generated code required to execute embedded SQL statements. This code is not only easier to read, but allows applications to run faster.

More Flexible Error Handling  An enhanced SQL WHENEVER statement allows you to embed host-language procedure calls in a precompiled program. If a SQL statement fails, you can have your program transfer control to a routine, and return when it ends.

Smart Resizing  Private SQL areas are automatically resized and the AREASIZE option is now obsolete.

Smart Rebinding  Host variables are rebound only when necessary, making the REBIND option obsolete.

Full Reentrance  Reentrant code is generated automatically for systems that require it and the REENTRANT option is now obsolete.

Separate Executables for Each Language  The precompilers are now distributed as separate executables, each designed for a specific host language. As a result, the HOST option is no longer needed for specifying the host language, but it remains for port-specific and COBOL-specific purposes.

Standards Compliance  The Version 1.5 precompilers have been verified as 100% compliant by the National Institute of Standards and Technology. Additionally, the new FIPS (Federal Information Processing Standard) flagger option issues warning messages whenever it detects the existence of an ORACLE SQL extension, or usage of a standards-mandated feature in a non-standard manner.

New Debugging Aid  Now the SQLCA stores more runtime information about the outcome of SQL operations. Specifically, SQLERRD(5) stores a parse error offset to help you debug SQL statements.

New Features of the ORACLE Call Interfaces

Upgrading to ORACLE7 automatically upgrades your ORACLE Call Interfaces libraries. Existing applications must be relinked with these new libraries. If desired, you can also recode existing applications or code new applications to take advantage of the following new features.

Deferred Database Calls  A new link option reduces the amount of messaging traffic required between OCI clients and ORACLE, by transparently bundling bind and define variable information on the client. Bundled (or deferred) information is only transmitted when necessary in conjunction with the describe, execute, and fetch calls.

New OCI Calls  ODESCR and OBNDRA supplement the existing ODSC and OBNDRV/OBNDRN calls, respectively, adding new functionality. OFLNG allows piecewise access to the data stored in ORACLE7 LONG and LONG RAW database column types. OPARSE and OEXFET can be used with deferred database linking to realize even greater performance improvements via network optimization.

SQL*Module

A new, separately licensed product, SQL*Module, provides the best of both the Precompiler and OCI development environments, while delivering 100% compliance with relevant ANSI and ISO standards.

Like the Precompilers, SQL*Module provides an easy capability to execute Static SQL Data Manipulation Language statements. However, like the OCIs, the mechanism by which the host program executes the statements is a simple host language procedure call. Because no Embedded SQL code is present in the application source code, you can continue to use your standard programming tools to edit and debug your code.

You might choose to take advantage of this new tool to create any new applications or even to recode your existing applications. SQL*Module incorporates the MODE and FIPS options of the Version 1.5 Precompilers as well, delivering the same functionality with respect to the SQL* Module Language.

Migrating Precompiler and OCI Applications

Before you migrate your ORACLE database from Version 6 to ORACLE7, migrate any OCI and Precompiler applications that you plan to use with your ORACLE7 databases. You can then test these applications on a sample ORACLE7 database, before migrating your production database.

The amount of effort involved in this migration process is dependent upon the degree to which you want to take advantage of the Programmatic Interfaces and ORACLE7. In order of increasing difficulty, you can choose to:

•	maintain your existing performance and functionality

•	boost the performance of your applications

•	take advantage of the new ORACLE7 database functionality

See the "Migrating Your Precompiler Applications" and "Migrating Your OCI Applications" sections later in this chapter for the specific steps required to migrate your Precompiler and OCI applications.

________________________________________

ORACLE7 Changes and the Programmatic Interfaces

The behavior of ORACLE7 has changed in some subtle ways from Version 6 to accommodate ANSI/ISO standards. While some of these changes can be averted by specifying option and procedure call settings that preserve Version 6 behavior, others cannot, and some applications may require slight recoding to function properly.

The following changes are always in effect and cannot be avoided. Some minor recoding of applications might be required to assure continued correct behavior.

•	In SQL statements, the special keyword USER is considered a literal and, if compared to other literals, uses padded comparison semantics.

•	All string literals have both the fixed-length and standard-compare attributes, and ANSI/ISO string comparison rules apply.

The following changes can be averted by retaining Version 6 behavior using specific option settings to the precompilers or, for OCI applications, using specific procedure call arguments and the selection of the procedure calls that are used. Most of these changes were introduced for standards compliance, but a few others were necessary for other reasons.

•	CHAR columns are fixed length (including those created by a CREATE TABLE or CREATE CLUSTER statement).

•	The syntax for specifying constraint names on table columns has changed, requiring the name at the beginning of the CONSTRAINT clause.

•	In Version 6, if a NULL value was fetched into a host variable, and no indicator variable was present, you received the error message "ORA-01405: fetched column value is NULL" only if you set MODE=ANSI. In ORACLE7, you always receive this message (unless you set DBMS=V6).

•	In Version 6, if a value fetched from the database had to be truncated to fit the host variable, and no indicator variable was present, you did not receive an ORACLE error message if you set MODE=ANSI. In ORACLE7, you never receive a message (unless you set DBMS=V6).

•	The DESCRIBE of a fixed-length string returns datatype 96 (CHAR) instead of datatype 1. Datatype 1 is now VARCHAR2. This results from the difference between the version 6 CHAR datatype and the version 7 CHAR datatype.

•	PCTINCREASE can no longer be specified in the CREATE ROLLBACK SEGMENT statement.

•	Illegal MAXEXTENTS storage parameters are no longer allowed.

Retaining Version 6.0 Behavior

In the Version 1.5 Precompilers, specifying DBMS=V6 causes SQL statement behavior to remain the same as when working against ORACLE Version 6, and applications need not be recoded to accommodate the changes listed above. Specifying DBMS=V7 to obtain ORACLE7 behavior might require slight recoding (refer to the Programmers Guide to the ORACLE Precompilers.

In the ORACLE7 OCIs, use of the existing OSQL3 parse call, or the new OPARSE call with the LNGFLG parameter set to Version 6, similarly guarantees Version 6-like program behavior. Substituting OPARSE for OSQL3, and specifying LNGFLG = Version 7 might require slight recoding to handle the changes listed above (refer to the Programmers Guide to the ORACLE Call Interfaces).

________________________________________

Migrating Your Precompiler Applications

In order to use your existing precompiler applications with a ORACLE7 database, you must complete a subset of the following steps. You may optionally begin at any step; however, once you begin you must complete any steps that follow. For instance, if you begin with step 3, you must complete steps 4 and 5. Step 5 is required of all applications.

1.	If you want your application to be fully ANSI/ISO compliant, you should recode your application according to the ANSI/ISO guidelines published in the Programmer's Guide to the ORACLE Precompilers.

2.	If you want to take advantage of the new features offered by ORACLE7, you must recode your applications to reflect the differences between Version 6 and ORACLE7 that were outlined previously in this chapter. You must also specify DBMS=V7 (the default) on the precompiler command line, to explicitly request ORACLE7 behavior.

3.	If you want to take advantage of new features offered by the Version 1.5 Precompiler, you must re-precompile your application using the appropriate option settings as explained below. For a complete explanation of each of the option settings, including those that are now obsolete, refer to your Programmer's Guide to the ORACLE Precompilers.

•	To simply maintain existing functionality, use the following option settings:

DBMS=V6

other existing options = no changes

•	To improve performance and take advantage of the new features available with ORACLE7, use the following settings:

DBMS=V7

MODE=ORACLE

other existing options = no changes

•	To be fully ANSI compliant, use the following settings:

DBMS=V7

MODE=ANSI

FIPS=YES (optionally, to receive FIPS warning messages)

other existing options = no changes

4.	Recompile your application with your compiler.

5.	(Required) Relink your application with Version 1.5 of the ORACLE Runtime Library (SQLLIB), which is included with the precompiler. You must complete at least this step to use your applications with ORACLE7.

________________________________________

Migrating Your OCI Applications

In order to use your existing OCI applications with a ORACLE7 database, you should complete the following steps. Steps 1 and 2 are required, while Step 3 is optional and allows you to take advantage of the new functionality offered with ORACLE7.

1.	Make certain that any applications that use "old" OCI [HLI] calls (as documented in the Programmer's Guide to the ORACLE Call Interfaces, Version 6.0, Appendix A) are either:

•	recoded using the "new" OCI equivalents

•	relinked with a user-written interface layer to map "old" calls to "new" calls. This may be necessary, for example, where the source code of the original application is no longer available.

2.	Relink your applications with ORACLE7 of the OCI libraries, using either:

•	deferred mode linking, to improve performance of applications

•	non-deferred mode, to maintain existing performance levels

The OCI libraries are shipped with ORACLE7.

One implication of deferred linking is that bind and define errors may not be reported to the application immediately after bind and define operations, but may instead be reported later at the time of a describe, execute, or fetch call. Some minor recoding of your application might be required to handle this eventuality.

3.	(Optional) Recode your applications to use new calls. The new OBNDRA, ODESCR and OFLNG calls allow you to take advantage of new features offered by ORACLE7, and do not require any further recoding. The new OPARSE and OEXFET calls allow you to improve the performance of your applications. If you use OPARSE with the LNGFLG parameter set to V7, you must also change your code to reflect the differences between Version 6 and ORACLE7, outlined previously.

________________________________________

Migrating Your SQL*Forms Applications

SQL*Forms 3.0 runs with either ORACLE Version 6 or ORACLE7. You are not required to regenerate your applications to use them with ORACLE7 of the database. After you have completed installation of ORACLE7, install SQL*Forms V3.0, if not already installed. Your SQL*Forms (Runform) applications run with no modification. You should, however, keep in mind the following issues:

•	Your SQL*Forms applications running on ORACLE7 are functionally identical to those applications running on Version 6. Guidelines for modifying your SQL*Forms applications to take advantage of ORACLE7 functionality are outlined later in this section.

•	SQL*Forms 3.0 continues to use PL/SQL Version 1, resulting in the following restrictions on your datatypes:

•	NUMBER, DATE BOOLEAN and VARCHAR2 fields are supported, but VARCHAR2 fields are limited to 2 Kbytes.

•	No other PL/SQL Version 2 datatypes can be used in SQL*Forms triggers.

Taking Advantage of ORACLE7 Functionality

SQL*Forms has been enhanced to allow support of stored procedures, functions and packages (local and remote). Because SQL*Forms creates the access routine for each stored subprogram that your trigger or SQL*Forms-level-procedure PL/SQL code references, it is not possible to write a form containing calls to stored procedures or functions that will generate against both ORACLE Version 6 and ORACLE7.

Once a form includes a reference to at least one stored subprogram, then it must be generated against an ORACLE7 database. Otherwise, compilation errors result when the names of stored subprograms cannot be resolved by the PL/SQL compiler.

You should review the new features described in Appendix A in order to determine if stored procedures, or any of the other new features would be beneficial to your SQL*Forms applications. A complete description of how these ORACLE7 features interact with SQL*Forms applications is provided in an Appendix C of the ORACLE7 Server Application Developer's Guide.

________________________________________

Migrating Your CASE Applications

The Data Definition Language (DDL) generator within CASE*Dictionary generates SQL DDL using ORACLE Version 6 syntax. To enable this DDL to be interpretable by ORACLE7, these scripts are automatically run from within SQL*Plus with the SET COMPATIBILITY V6 option. One of the translations performed during table creation is to convert all CHAR columns to their ORACLE7 equivalent, VARCHAR2. To generate ORACLE7 CHAR columns (fixed length, blank padded), the DDL scripts must be run using SQL*Plus outside the CASE environment.

________________________________________

Migrating Your SQL*Report (RPT/RPF) Reports

Support for reports created using SQL*Report is maintained in ORACLE7. Your reports should run against either Version 6 or ORACLE7 of the database with no modifications. SQL*Report has not been upgraded to take advantage of any new ORACLE7 functionality.

________________________________________

Migrating Your SQL*ReportWriter Reports

Support for SQL*ReportWriter 1.1 reports is maintained in ORACLE7. Your reports should run against either Version 6 or ORACLE7 of the database with no modifications. ORACLE7 functionality is supported in Version 2 of SQL*ReportWriter.

If you have user-owned SQL*ReportWriter tables and you want to take advantage of the row-level locking that is now available in standard ORACLE7, you must create system-owned tables. To install the system tables, use the SQL*Plus script SRW_ICEN.SQL, if you are using SQL*ReportWriter Version 1.1, or SRW_ICEN2.SQL, if you are using Version 2.0. Then load your existing reports into the new tables.

________________________________________

Migrating Your SQL*Plus Scripts

After you have migrated your database from ORACLE Version 6 to ORACLE7, you can run your SQL*Plus 3.0 scripts against ORACLE7 and achieve the same output as if you were running against Version 6 by adding the following line as the first line of your script:

SET COMPATIBILITY V6

Alternatively, you could add this line to your LOGIN.SQL file.

Taking Advantage of ORACLE7 Functionality

If you want SQL*Plus Version 3.1, ORACLE7, and PL/SQL Version 2 functionality, you must complete these additional steps:

•	You must make the following changes to your SQL*Plus 3.0 scripts to make them SQL*Plus 3.1 scripts:

•	If your scripts contain the line SET COMPATIBILITY V6, change it to SET COMPATIBILITY V7. Also check your LOGIN.SQL file and make this change as needed.

•	If your script relied on the DESCRIBE command's output, be aware that it now displays two new datatypes: ROWLABEL and VARCHAR2.

•	To learn about functionality new to SQL*Plus Version 3.1, refer to Appendix B of your SQL*Plus User's Guide and Reference.

•	Change all SQL scripts to conform to ORACLE7 syntax. Instructions for completing these modifications begin on page 2-8 of this document. For additional information on SQL, refer to your ORACLE7 Server SQL Language Reference manual.

•	No changes to PL/SQL procedures are required.

________________________________________

SQL*Net

ORACLE7 uses Version 2 of SQL*Net on the server side. Clients should use Version 2 if they want to connect to a dispatcher process via the multi-threaded server available with ORACLE7. Clients can continue to connect as a dedicated server using SQL*Net Version 1.

To upgrade from SQL*Net Version 1 to Version 2 perform these changes:

•	Re-create each Version 1 CONNECT string as a Version 2 CONNECT descriptor. SQL*Net Version 2 relies on the new syntax outlined in Appendix A of the SQL*Net V2.0 Administrator's Guide.

•	On a multi-user system, relink any Precompiler programs that you want to use with SQL*Net Version 2.

For complete instructions on upgrading SQL*Net from Version 1 to Version 2, refer to your SQL*Net V2.0 Administrator's Guide.


APPENDIX A. SUMMARY OF CHANGES IN ORACLE7

This appendix provides an overview of the changes in the ORACLE7 Server. It covers the following topics:

•	Changes in Terminology (next page)

•	Functionality Enhancements (page A - 3)

•	Backup and Recovery Enhancements (page A - 8)

•	Security Enhancements (page A - 9)

•	Performance Enhancements (page A - 13)

•	Administration Enhancements (page A - 16)

•	SQL*DBA Changes (page A - 18)

•	Changes to Utilities (page A - 19)

•	Changes to Views (page A - 20)

•	RDBMS Datatype Changes (page A - 21)

•	SQL Command Changes (page A - 22)

•	Initialization Parameter Changes (page A - 22)

•	Data Dictionary Changes (page A - 25)

•	Version 6 Compatibility (page A - 29)

•	Changes to SQL Scripts, Other Changes (pages A - 30, A - 31)

________________________________________

Terminology

Some new terms have been introduced with ORACLE7 that describe Version 6 features. The concepts behind these terms are not new; the new terms are used to better explain the concepts. The following list summarizes the new terms:

initialization parameters and parameter files	Parameters placed in the parameter file are used to specify configuration settings when starting an instance. In Version 6 documentation, initialization parameters were commonly referred to as INIT.ORA parameters.

In Version 6 documentation the file containing initialization parameters was referred to as the INIT.ORA file. In ORACLE7 documentation, it is known as the parameter file.

schema	A schema is a logical space in which a user can create objects. In Version 6, there was no distinction between a user and a collection of objects that the user owns. In ORACLE7, that distinction is introduced with the term schema.

Every user has one schema in which objects can be created. The name of that schema is the same as the user's ORACLE username. Objects are uniquely identified by the schema to which the object belongs and the object name. For example, the table EMP created in SCOTT's schema is identified as SCOTT.EMP.

schema objects	In Version 6, it was only necessary to talk about tables, views, and users. In ORACLE7, there are tables, views, snapshots, roles, profiles, users, procedures, packages, triggers, and integrity constraints, among other entities. These are known collectively as database objects.

server process	A process that handles requests (program interface calls) from user processes. A server process can be either dedicated to one user process, or shared among many user processes, depending on the configuration of your database system. In Version 6, these processes were known as "shadow processes."

session	A logical connection by a user to the database. One user can have several sessions. The Version 6 term "user process" has been replaced by "user session" in ORACLE7.

Shared SQL Areas	In Version 6, the parsed form of SQL statements were stored in user context areas. In ORACLE7, they are stored in shared SQL areas. See page A - 16 for more information.

________________________________________

Functionality Enhancements

Enforced Integrity Constraints

Data verification can now occur within ORACLE, as well as at the application level. By checking data at the application level, you can ensure that the users of a particular application receive immediate useful and appropriate help and error messages when they access a table. Checking at the database level ensures that no incorrect data can be entered in the table, regardless of which application or tool is used. Data checking at the database level is accomplished by using integrity constraints.

Integrity constraints can be defined to specify logical relationships of data in a database. Version 6 introduced syntactic support for data integrity constraints. ORACLE7 supports the enforcement of integrity constraints to ensure that a database contains consistent data. If a Data Manipulation Language statement violates an integrity constraint, the statement is rolled back and an error is returned.

 Enabling Constraints

Integrity constraints can be enabled or disabled. When constraints are enabled on existing data, an exception table can be generated to list all rows that violate constraints along with the constraints they violate.

 Unique Constraints

In Version 6, the fact that table indexes were unique was frequently used to enforce uniqueness constraints. This is no longer necessary in ORACLE7 since the UNIQUE constraint can now be used to guarantee uniqueness.

 Delete Cascade

When deleting a master row which is referenced by foreign keys in other tables, you can choose to cascade the delete, dropping all foreign key rows, as well as the master row.

 Standards Compliance

The integrity constraints implemented in the ORACLE7 Server fully comply with the SQL89 Level 2 standard set forth by ANSI X3.135-1989 and ISO 9075-1989.

Along with ORACLE7, the Version 1.5 precompiler is 100% compliant with the ANSI SQL89 standard, including integrity features.

In addition, the Version 1.5 precompiler passes 100% of the tests contained in the National Institute of Standards and Technology (NIST) SQL89 test suite. It also contains the FIPS Flagger (required by the Federal Information Processing Standard) to call attention to any non-ANSI-standard extensions that are used.

Extended National Language Support

National Language Support (NLS) in ORACLE7 supports the use of multi-byte character sets used in many Asian languages. It also provides greater flexibility in specifying language or territory, either for a session or for the entire database.

New NLS initialization parameters allow the specification of default date format, currency symbol, number-group separator, and decimal character. These format characteristics can also be specified explicitly in SQL functions to override the default values.

The values of all NLS parameters are defined with systemwide defaults, and can be overridden on a per-session basis. Different sessions connected to the same instance can use different values for NLS parameters. These values can be changed during a session.

For a detailed discussion of NLS features supported by the ORACLE7 Server, see Appendix A "National Language Support" in the ORACLE7 Server Concepts Manual. Also, review the NLS appendixes in the ORACLE7 Server Application Developer's Guide and the ORACLE7 Server Administrator's Guide.

Procedural Option

This option includes the PL/SQL programming language. With this option, a stored procedure or function can be defined and compiled once, saved in the database, and then executed by multiple users and applications. Stored procedures and functions consist of a set of SQL and PL/SQL statements that are stored in a compiled form in the database. In addition, the procedural option provides for stored packages and database triggers.

 Packages

Packages provide a method of encapsulating and storing related procedures, functions, cursors, variables, constants, and exception handlers as a unit. Global package variables and constants can be declared and used by any procedure in the package. Between calls, variables and cursors retain their state for each session. Only public data, procedures, and functions can be accessed from outside the package.

Chapter 7 "Using Procedures and Packages" of the ORACLE7 Server Application Developer's Guide contains a detailed description of stored procedures, functions, and packages.

 Triggers

Triggers are shared procedures that are automatically executed as a result of an insertion into, update of, or deletion from a table. A trigger consists of an event to signal the firing (execution) of the trigger, trigger restriction, and the action to take when the trigger fires. A trigger is implicitly fired by the ORACLE7 Server when the triggering event occurs, no matter which user is connected or which application is being used.

Chapter 8 "Using Triggers" of the ORACLE7 Server Application Developer's Guide contains a detailed description of triggers and their uses.

 Compilation of Procedural Objects

Procedures, functions, packages, and triggers are automatically recompiled, as needed, whenever they are referenced. All of these procedural objects can also be compiled manually. With either method, all dependent objects are marked for automatic recompilation, so that the most recent version of a routine is always used.

 PL/SQL Language Changes

PL/SQL in ORACLE7 supports remote procedure calls, if both the distributed option and the procedural option are licensed. Remote procedure calls include support for two-phase commit (described in the next section).

Several new commands, functions, and datatypes have been added to the PL/SQL language, and many changes have been made to existing commands. Refer to the changes appendix of the PL/SQL User's Guide and Reference for a detailed description of the changes.

 Pipes and Alerts

Several new packages supplied with the procedural option implement process-to-process communication pipes and procedural alerts. Pipes allow different processes to communicate by sending messages to one another. By registering an alert, a process can be informed when a specified event occurs in the database; for example, when a field reaches a particular value. For more information on pipes and alerts, see Appendix A "Supplied Packages" of the ORACLE7 Server Application Developer's Guide.

 Interactive Output from Stored Procedures

ORACLE7 stored procedures can display output interactively using the DBMS_OUTPUT package. Output can be enabled or disabled for debugging. Output lines are automatically displayed by SQL*DBA and SQL*Plus when output is enabled. You can also access debug output from within your procedures. For more information, see Appendix A, "Supplied Packages" and Chapter 7 "Using Procedures and Packages" in the ORACLE7 Server Application Developer's Guide.

Distributed Option

In Version 6, only queries were permitted to remote databases. ORACLE7 with the distributed option supports all Data Manipulation Language operations, including queries, insertions, updates, and deletions of remote table data. When both the distributed option and the procedural option are installed, calls to remote procedures are supported, as is the use of table snapshots.

You might want to distribute your database to improve performance or accommodate organizational structure. You should investigate and implement any changes that are necessary to the architecture of your applications to allow them to store data where it is used most often. See Chapter 21 "Distributed Databases" of the ORACLE7 Server Concepts Manual for a complete description of the distributed database features.

 Two-Phase Commit

The two-phase commit mechanism transparently maintains data consistency throughout a distributed database by guaranteeing that a distributed transaction either commits at all nodes or rolls back at all nodes, no matter what type of system or network failure occurs. Any inserts, updates, or deletes in a distributed transaction (whether from SQL statements or stored procedures) are protected by the two-phase commit mechanism.

In the event of a node failure, two-phase commit automatically coordinates the recovery of all nodes involved in a transaction.

 Deadlock Detection and Resolution

The ORACLE7 distributed option also detects and resolves distributed deadlock conditions.

 Multi-Node Read Consistency

For a single query that spans multiple nodes, read-consistency is guaranteed. For multiple queries, the ability to specify a read-only transaction guarantees read-consistency between distributed queries.

 Snapshot Capability

If you have both the distributed option and the procedural option, then you can make read-only copies of master tables (snapshots) at remote sites. Snapshots can reduce network traffic, improve access times, and allow remote sites to continue functioning in the event of network failure or central system failure.

Snapshots can contain a subset of a table's rows, or they can summarize the information in multiple rows. They can also join data from multiple master tables.

A master table can have multiple snapshots in different locations. Snapshots can be refreshed either manually or automatically, using either a complete copy of the master table or a log that contains master table changes.

 Global Naming

In ORACLE7, every object in a distributed database can be uniquely specified with global names. Following this convention paves the way for the future use of external name servers. With such servers, the work of resolving the path to an external object is done by the server, instead of within the database. To ensure that the current system is correctly configured, the GLOBAL_NAMES parameter can be set, as described in Appendix A "Parameter Files and Initialization Parameters" of the ORACLE7 Server Administrator's Guide. The global names capability is designed to work with external name servers, such as the OSF's Distributed Computing Environment (DCE).

If you rename a database with a global name and begin enforcing global naming, the change in database name must also be reflected in the following areas:

•	Database links in remote databases that point to the renamed database must be renamed.

•	Synonyms in remote databases that point to objects within the renamed database must be updated.

•	PL/SQL program units in remote databases that reference data in the renamed database must be recompiled.

You may choose to gradually enable global naming by enforcing it in selected databases in a distributed system. A database with global naming enabled can successfully query a database where global naming is not enabled as long as the links to the database are named properly. A database where global naming is not enabled can successfully query a database where global naming is enabled.

Global names will be required in future releases of ORACLE; however, in ORACLE7, you can disable global naming. Disabling global naming allows database link names to have no correspondence to global database names, as with earlier versions of ORACLE.

 DB_DOMAIN Parameter

The ORACLE7 initialization parameter DB_DOMAIN replaces the Version 6 parameter DB_DIRECTORY. DB_DOMAIN's default value is ".WORLD". You should change it to a unique value for every system in a distributed network, to uniquely identify objects in a distributed system.

 Closing Database Links

A database link can now be closed when it is no longer needed without terminating the session, by using the ALTER SESSION command. This can decrease your telecommunication charges, and reduce resource consumption at the site of the remote database.

 LONGs Supported

In ORACLE7, LONG data items can be referenced in distributed queries, updates, and deletes.

 Improvements in Distributed Query Processing

If all of the data referenced by a query is located on a remote node, then the ORACLE7 query processor evaluates the query at the remote node, returning only the result to the originating node. Multi-node queries are divided into parts, and each part is sent to the remote node for evaluation. This mechanism can dramatically reduce network traffic.

In addition, the optimizer can use information on indexes as well as statistics from remote tables to develop the execution plan. Using this information, queries are optimized in the same way that they would be if the tables were local. For example, ORACLE7 may be able to perform a nested-loop join, where Version 6 may have been constrained to a sort/merge join.

 Heterogeneous Distributed Database Systems

New coordination mechanisms make it possible for the ORACLE7 Server to operate in a distributed arrangement with non-ORACLE7 databases.

 Standards Compliance

To interface to TP monitors for transaction coordination, the ORACLE7 Server supports the XA interface, as defined in the X/OPEN specification.

Parallel Server Option

The Parallel Server option to the ORACLE7 Server supports simultaneous database access from two or more loosely coupled systems, to achieve high availability and performance. For a discussion of the new Parallel Server features, see the changes appendix in the ORACLE7 Parallel Server Administrator's Guide.

________________________________________

Backup and Recovery Enhancements

Recovery Capabilities

Many changes have been made to recovery features in ORACLE7:

•	The RECOVER command in SQL*DBA has new options for incomplete recovery.

•	Filename format can now be specified for archived redo log files with the initialization parameter LOG_ARCHIVE_FORMAT.

•	Each instance running in the parallel server has its own set of online redo log files.

•	Checkpoints can now be specified by time interval, and can also be forced on demand.

Refer to Chapter 23 "Recovery Structures" of the ORACLE7 Server Concepts Manual and Chapter 19 "Recovering a Database" of the ORACLE7 Server Administrator's Guide for more information about recovery and recovery structures. Refer to the SQL*DBA Commands section of the ORACLE7 Server Utilities User's Guide for more information about the RECOVER command.

Parallel Server Recovery Enhancements

In ORACLE7, it is possible to perform the same tablespace and datafile operations in parallel mode as when running in exclusive mode. For example, the database can remain running while a tablespaces is taken offline for backup, or brought back online as part of a recovery operation. For details, see the changes appendix of the ORACLE7 Parallel Server Administrator's Guide.

SCN-based Recovery

System Change Numbers (SCNs) can be used in ORACLE7 recovery operations, allowing you to recover up to a specific transaction. Details are given in Chapter 19 "Recovering a Database" of the ORACLE7 Server Administrator's Guide.

Mirrored Online Redo Log Files

The ORACLE7 Server provides the capability to maintain "mirror images" of the online redo logs. When mirrored online redo log files are configured, the LGWR background process concurrently writes the same redo log information to multiple active online redo log files, thereby eliminating a potential single point of redo log failure.

You should mirror your online redo log files to reduce the chance of losing a log file in the event of media failure. Since mirrored redo log files do not significantly affect performance, the only additional overhead is space to contain the additional log files. See Chapter 23 "Recovery Structures" of the ORACLE7 Server Concepts Manual for a detailed description of how to configure mirrored redo log files.

________________________________________

Security Enhancements

System and Object Privileges

Many new system privileges have been added to ORACLE7 to replace the three Version 6 system privileges:  CONNECT, RESOURCE, and DBA. For example, statements like the following give users the privilege to create tables in their schemas:

GRANT CREATE TABLE TO SCOTT;

The new system privileges in ORACLE7 allow for more specific control of system operations. The Version 6 system privileges have become predefined roles in ORACLE7 for backward compatibility (roles are discussed later in this section).

An object privilege gives a user the right to perform a selected operation on a specific database object. See Chapter 18 "Privileges and Roles" of the ORACLE7 Server Concepts Manual for more information about system and object privileges.

 Creating Users

In Version 6, users were created as a side-effect of the GRANT CONNECT command. For ORACLE7 it is recommended that you use the new CREATE USER command to create a user.

Users created in this manner have no privileges after creation. GRANT CREATE SESSION command can then be employed to give the user the CREATE SESSION system privilege. (This privilege is the equivalent of the Version 6 CONNECT system privilege.) For backward compatibility, GRANT CONNECT currently creates a user, but this behavior may not always be supported.

 RESTRICTED SESSION Privilege

The ORACLE7 privilege CREATE RESTRICTED SESSION limits database access to privileged users. In ORACLE7, you grant this privilege to create a special class of users who can use the database when it is not completely open, rather than assigning the DBA role, as was done in Version 6.

Roles

ORACLE7 simplifies privilege management with roles. Roles are groups of related privileges that are granted to users or other roles.

You can create roles with specific privileges for classes of users, such as computer operators and data entry clerks. When you assign a role to a user, that user is given all of the associated privileges. In addition, when you change the privileges granted to a role, the change affects all users with that role. Roles can be password-protected, and they can be dynamically enabled.

For more details on roles and privilege management, see Chapter 18 "Privileges and Roles" of the ORACLE7 Server Concepts Manual.

 Set Role Command

Issuing a SET ROLE command at the start of an application automatically enables the designated role for the user (provided the user has been granted that role) and disables any previously enabled roles. Users can have default roles, and passwords can limit which applications can use each role.

 Predefined Roles

Version 6 had three system privileges:  CONNECT, RESOURCE, and DBA. ORACLE7 defines roles with the same names, containing the equivalent ORACLE7 system privileges. These roles can be modified by the system administrator, and new roles can be created.

The roles IMP_FULL_DATABASE and EXP_FULL_DATABASE allow users to perform full and incremental imports and exports. These roles can be assigned to DBAs and non-DBAs.

The predefined roles OSOPER and OSDBA have also been added to ORACLE7. The OSOPER role is intended for operators and others who need some of a DBA's privileges to perform system maintenance, but who should not be granted full DBA privileges, as contained in OSDBA.

Table A - 1 lists the predefined roles that are provided for backward compatibility to Version 6.

Role Name	Privileges

CONNECT 1	ALTER SESSION, CREATE CLUSTER,

CREATE DATABASE LINK, CREATE

SEQUENCE, CREATE SESSION,

CREATE SYNONYM, CREATE TABLE,

CREATE VIEW

RESOURCE  1,2	CREATE CLUSTER, CREATE PROCE

DURE, CREATE SEQUENCE, CREATE

TABLE, CREATE TRIGGER

DBA  1,3	All system privileges WITH ADMIN

OPTION

Table A - 1.   Predefined Roles

1 created by SQL.BSQ

2 grantees of the RESOURCE role also receive the UNLIMITED TABLESPACE system privilege as an explicit grant (not part of the RESOURCE role)

3 grantees of the DBA role also receive the UNLIMITED TABLESPACE system privilege with the WITH ADMIN OPTION as an explicit grant (not as part of the DBA role)

During migration, Version 6 users with the CONNECT, RESOURCE, or DBA system privileges are granted the respective roles in the ORACLE7 database. After migration, you should consider how you want to control access to your database and create appropriate roles. You will probably want to have a variety of roles for users and developers of different applications, as well as the various people who administer your system. After creating and assigning these roles, you should revoke the CONNECT, RESOURCE, and DBA roles from users in your database. However, you should not alter or drop the predefined roles.

There are a few small differences between the Version 6 DBA privilege and the ORACLE7 DBA role. Views that could be created in Version 6 only because the view owner had DBA privilege cannot be created in ORACLE7. The migration utility migrates these views, but they are inaccessible to other users because ORACLE7 views cannot use privileges granted to the view owner via the DBA role. To access the view, the privileges required for the view's creation must be granted directly to the owner of the view.

Chapter 18 "Privileges and Roles" of the ORACLE7 Server Concepts Manual gives a more complete description of the new system privileges and strategies for creating roles, as well as how you can use your operating system for role management.

 Improved Operating System Security Interface

The operating system can be used to maintain a list of ORACLE7 roles, manage the grants and revokes of roles, and, if desired, manage the verification process for roles that require authorization. For details, see Chapter 18 "Privileges and Roles" of the ORACLE7 Server Concepts Manual.

As in Version 6, users can be authenticated by the operating system as well as by the ORACLE7 Server. However in ORACLE7 the prefix OPS$ (used to identify operating system authenticated roles) can be changed by adjusting the OS_AUTHENT_PREFIX initialization parameter.

Auditing Changes

Statement execution and the use of system-privileges can now be audited on a per-user basis. Table access can be audited, as can the execution of SQL statements. In addition, new ORACLE7 constructs such as triggers, procedures, and roles can be audited. To accommodate the new auditing functionality, changes have been made to most of the auditing views defined in CATAUDIT.SQL.

In Version 6, auditing of SQL statements occurred at parse time. In ORACLE7, auditing occurs at execution time, since the parsed form of SQL statements are frequently already stored in shared SQL areas. But if an application explicitly controls statement processing in order to execute a statement many times but only parse it once, then only a single audit record is generated.

In ORACLE7, table auditing and view auditing options are independent. If a table is accessed through a view, audit records may be generated for the table, for the view, or for both, depending on which auditing options are enabled. If a table is audited, an audit record is generated whenever that table is accessed, whether directly or through a view. An audit record is only generated on the view if auditing is enabled for the view, regardless of the audit-status of the table.

Auditing is discussed in Chapter 13 "Auditing Database Use" of the ORACLE7 Server Administrator's Guide.

PUBLIC Quotas

Although the UNLIMITED TABLESPACE system privilege can still be granted to PUBLIC, there is no ORACLE7 equivalent for the Version 6 action of granting a tablespace quota to PUBLIC. In ORACLE7, quotas are assigned as part of the CREATE USER or ALTER USER statements. See the ORACLE7 Server SQL Language Reference Manual for more details.

Standards Compliance and Trusted ORACLE7

The ORACLE7 Server is designed to comply with level C2 of the National Computer Security Center (NCSC) standard for computer security, as defined in the Orange Book. ORACLE7 provides discretionary access control.

Trusted ORACLE7 is designed to comply with level B1. Trusted ORACLE7 contains all features and functions of the ORACLE7 Server. In addition, it provides Mandatory Access Control (MAC) allowing for the definition of data sensitivity levels (for example secret, restricted, and unclassified) and user clearance up to a particular levels. For more information on the Trusted ORACLE7 Server, see the ORACLE7 Server Administrator's Guide.

________________________________________

Performance Enhancements

Multi-Threaded Server Architecture

The multi-threaded server architecture can reduce system overhead on multi-user systems, allowing you to increase the number of concurrent users. Chapter 9 "Memory Structures and Processes" of the ORACLE7 Server Concepts Manual contains a full description of the multi-threaded server architecture.

In Version 6, when using the "two task" architecture, a shadow process was associated with each user process to communicate with the database. In ORACLE7, dedicated server processes and shared server processes replace shadow processes. Dedicated server processes are used in a normal two-task configuration and have the same functionality as shadow processes. Shared server processes are used when you configure your database to run with dispatcher processes that route requests from user processes to the servers.

In Version 6, shadow processes often had a high percentage of idle time. They were either waiting for the user to enter a request, or for the application to process data. In ORACLE7, the shared server versions of those processes use that time to service other users. Thus, only a few shared server processes are needed to serve many users, with no loss in throughput. In ORACLE7, the system resources that were previously dedicated to shadow processes are available for additional users.

Checkpoint Process

In ORACLE7, the checkpoint process (CKPT) can be enabled. It helps on heavily loaded systems by taking over the work of checkpointing from LGWR. For more information, see Chapter 9 "Memory Structures and Processes" in the ORACLE7 Server Concepts Manual.

Optional Cost-Based Optimization

Every Data Manipulation Language statement processed by ORACLE7 must be examined by the optimizer. The optimizer chooses an execution plan for the statement based on the syntax of the statement, indexes and clusters associated with the tables in the statement, optional special instructions you give the optimizer, and at your discretion, statistics in the data dictionary.

The Version 6 optimizer used only the rule-based method to determine the execution plan for a SQL statement. The rule-based method considers only the syntax of the statement, table indexes, and table clusters when formulating a plan. The ORACLE7 optimizer can choose an execution plan with the lowest expected "cost" using statistics collected with the ANALYZE command. Hints can also be provided to manually control the optimization.

 ANALYZE Command

The ANALYZE command introduced in ORACLE7 computes or estimates statistics on tables, clusters, and indexes. Statistics are then stored in the data dictionary for performance tuning and query optimization.

ANALYZE can also identify chained rows and validate the structure of tables and indexes. In this capacity, it replaces the VALIDATE INDEX command. (Although VALIDATE INDEX is supported in ORACLE7, it may not be supported in future versions.)

You should familiarize yourself with the statistics that are collected by the ANALYZE command (see the ORACLE7 Server SQL Language Reference manual) and how these statistics are used by the cost-based optimization approach. You should then establish a plan for collecting these statistics at appropriate time intervals. Note that you must recollect statistics for data that has been exported and imported.

Rule-Based Optimization

If you do not collect statistics, or if the initialization parameter OPTIMIZER_MODE is set to RULE, ORACLE continues to use the rule-based optimization approach. There are some minor changes in this method between Version 6 and ORACLE7:

•	You may notice some performance improvements with joins of clustered tables where one table also has a unique index on the cluster keys.

•	Where possible, queries are executed entirely at one node.

•	Joins in distributed queries might be much faster because the local node is now aware of the indexes and statistics for the remote node and can choose to perform a sort/merge or nested loop join as appropriate.

•	Two types of semantically undefined outer-join queries now produce error messages.

If you have manually tuned your SQL statements to force a particular execution plan (for instance, by concatenating null or adding 0 to a numeric column), ORACLE7 continues to recognize these constructs, and you should generally notice no difference in the execution time for the statement.

Because future versions of ORACLE will not support the rule-based optimization approach, you should begin removing this manual tuning information. In general, you can simply remove this tuning information and use the cost-based optimization method with no new tuning being needed. The cost-based optimization method generally selects an execution path for the untuned SQL statement that is as good as or better than the path selected for the tuned statement.

However, there are certain rare cases, such as when you have a non-uniform distribution of data, when you might need to provide "hints" to the cost-based optimizer. Additionally, there are situations where it might be necessary to provide hints to the cost based optimizer for a query that did not need to be tuned when using the rule-based optimization approach. Once again, these usually involve non-uniform distributions of data.

Chapter 13 "The Optimizer" of the ORACLE7 Server Concepts Manual and Chapter 5 "Tuning SQL Statements" of the ORACLE7 Server Application Developer's Guide contain more information on ORACLE7's cost-based optimizer.

Refer to the ORACLE7 Server SQL Language Reference Manual for a complete description of the syntax and function of the ANALYZE command. See the ALL_TABLES, USER_TABLES, and DBA_TABLES views in the Data Dictionary appendixes of the ORACLE7 Server Administrator's Guide or the ORACLE7 Server Application Developer's Guide for a description of the statistics gathered by the ANALYZE command.

Hashing

Hash clusters permit more efficient retrieval of data stored in clusters. Hashing may be used instead of an index to more quickly locate a row, usually in one read operation. This is useful when you want to fetch one row. See Chapter 8 "Managing Schema Objects" of the ORACLE7 Server Administrator's Guide for details.

Shared SQL Areas

Shared SQL areas are the memory buffers that hold the parsed form of SQL statements. In Version 6 they were called context areas, and they were stored in each user process's private PGA. ORACLE7 stores the parsed form of SQL statements in the SGA and shares them among concurrent users.

When different applications issue identical SQL statements, the shared SQL area used to process the first occurrence of a statement is reused when processing subsequent occurrences of the same statement, regardless of the process that issues it. Shared SQL areas reduce memory usage and save parsing time, without any change to existing applications.

However, the SQL statements must be identical in order to take advantage of this feature. You should develop some guidelines for how SQL statements should be written. This includes spacing, capitalization, bind variables and object names. For instance, if two identical SQL statements are issued, but one uses a synonym to refer to the table, these statements are stored separately in the SGA.

TRUNCATE Command

The TRUNCATE command quickly deletes all rows in a table or cluster and optionally shrinks the corresponding segment by deallocating unnecessary extents. Removing rows with the TRUNCATE command is faster than removing them with the DELETE command, and TRUNCATE is more convenient when you want to empty a table without removing it. Refer to the ORACLE7 Server SQL Language Reference Manual for a complete description of the TRUNCATE command.

Additional Improvements

Many additional improvements have been made to the ORACLE7 architecture in order to improve performance.

•	Shortened critical code paths

•	Improved latching

•	Reduced logging

•	Ability to use standard Transaction Processing (TP) Monitors (on some platforms)

________________________________________

Administration Enhancements

Rollback Segments

Rollback segments are more efficient and are easier to manage in ORACLE7. Not only can rollback segments grow as needed, but they can dynamically shrink to a specifiable optimal size if space in the segment is not being used. Rollback segments can be taken offline and brought back online to facilitate rollback segment management. It is also possible to specify which rollback segment to use for a given transaction. See Chapter 9 "Managing Rollback Segments" of the ORACLE7 Server Administrator's Guide for a complete discussion of rollback segments.

 PCTINCREASE and MAXEXTENTS Not Applied

PCTINCREASE is no longer applied to the allocation of new rollback segment extents in ORACLE7. Because of this change, all extents within a rollback segment are of uniform size, which helps to reduce disk fragmentation. MAXEXTENTS is also no longer specifiable for rollback segments.

Resource Limits

In ORACLE7, limits can be set on the system resources available to a user. By explicitly setting resource limits, the database administrator can prevent uncontrolled consumption of valuable system resources.

Resource limits apply on a per call or per session basis. They can be set for:

•	CPU usage

•	logical I/O

•	idle time

•	connect time

•	number of open sessions

•	memory usage

Profiles

Resource limits are easily managed with user profiles. A profile is a named set of resource limits that can be assigned to users. Profiles are only needed if resource limits are a requirement of a database's security policy. The database administrator can universally enable or disable the enforcement of profile resource limits at the database level.

User Definitions

In Version 6, users could not be created without granting them connect access. In ORACLE7, user definitions can be created without automatically granting access to them, and a user's right to log on can be dropped while leaving the associated schema intact. For more information, refer to Chapter 11 "Controlling Access to the Database" of the ORACLE7 Server Administrator's Guide.

ALTER SYSTEM Command

The SQL command ALTER SYSTEM can be used to change ORACLEs configuration with respect to files, resource limits, multi-threaded server processes, and distributed recovery. For more information, see the ORACLE7 Server SQL Language Reference Manual.

________________________________________

SQL*DBA Changes

Interactive Menu Interface

Version 6 introduced SQL*DBA to facilitate common database administrator activities, such as monitoring database activity and database recovery. SQL*DBA in ORACLE7 is further enhanced with a menu-driven interface to make database administration easier. New monitors have also been introduced.

As in Version 6, any SQL statement can be executed from SQL*DBA, as well as stored procedures and functions. Refer to the ORACLE7 Server Utilities User's Guide for more information about SQL*DBA's menu interface, commands, and monitoring capabilities.

 CONNECT Required Before STARTUP or SHUTDOWN

In order to issue a SQL*DBA STARTUP or SHUTDOWN command, you must first CONNECT to the database. This is a new requirement in ORACLE7. It was not necessary in Version 6. Accordingly, scripts similar to the following no longer work:

SQLDBA COMMAND="STARTUP"

For ORACLE7, such commands should be embedded in scripts that include a CONNECT clause. The script can then be invoked with a command line similar to the following:

SQLDBA COMMAND="@START_SCRIPT.SQL"

Also, there are several new options to the STARTUP command; see your ORACLE7 Server Utilities User's Guide for more information.

 Monitors

The SQL*DBA monitor commands no longer take arguments. Instead, fields in the monitor screens take values to customize the monitor displays. Also, the monitor screens are no longer available when using the Version 6-compatible line mode interface. They are only available when using the ORACLE7 window and menu interface.

 Starting a Database in Restricted Mode

Rather than using the Version 6 command STARTUP DBA, you use the ORACLE7 command STARTUP RESTRICTED to limit database access to users who have been granted the RESTRICTED SESSION privilege.

 Controlling Restricted Sessions

Using the SQL command ALTER SYSTEM, restricted session mode can be enabled and disabled without having to restart the database. See the ORACLE7 Server SQL Language Reference Manual for details.

 KILL SESSION Command

The SQL*DBA KILL SESSION command allows the database administrator to terminate user sessions remotely.

 DESCRIBE Command

SQL*DBA supports the DESCRIBE command in ORACLE7. This command describes tables, views, procedures, functions, and packages.

 New Reserved Words

V6 and V7 are now reserved words in SQL*DBA.

 Debug Output Supported

Output lines generated in stored procedures and functions are automatically displayed by SQL*DBA when you enable the SERVEROUT function, either from the menu interface or with the SET command. The output routines are described in Appendix A "Supplied Packages" of the ORACLE7 Server Application Developer's Guide.

________________________________________

Changes to Utilities

Import/Export Changes

The ORACLE7 import/export utility handles all of the new ORACLE7 objects. Among other improvements, error-managing facilities have improved, and messages can be stored in a logfile. Major changes include the following:

•	You can create an export file containing a read-consistent image of tables and views.

•	In order to prevent accidental destruction, database files are no longer automatically reused on a full database import.

•	Index creation statements can be routed to a file during import, instead of being processed.

•	The default values for Export's GRANTS and CONSTRAINTS parameter and for Import's IGNORE parameter have changed.

•	A privileged user can import objects into another user's schema, even if that user does not have the CREATE privileges.

•	Single-byte character sets in an export file are automatically translated to the target database's single-byte character set.

For more information on these utilities, see the Changes appendix of the ORACLE7 Server Utilities User's Guide.

SQL*Loader Changes

SQL*Loaders direct path greatly reduces data loading times. This path bypasses SQL processing and loads data directly into the database. Other improvements to SQL*Loader include the following:

•	SQL functions can be applied to data as it is loaded.

•	New datatypes have been added.

•	Multi-byte character sets are supported.

•	White space and field delimiters can be handled with greater precision.

•	New performance features include the use of table truncation, OS-specific specification of file management, and the ability to make use of operating system sorts.

•	Parameters can be specified in a separate control file.

For more information, see the Changes appendix of the ORACLE7 Server Utilities User's Guide.

________________________________________

Changes to Views

Creating a View with Errors

In Version 6, it was not possible to create a view if the view could not be immediately queried. So, for example, it was impossible to create a view if an underlying table did not exist, or if its definition did not match that of the view.

In ORACLE7, such views can be created. The underlying table can then be created or modified later. Until the underlying tables are corrected, any attempt to use the view produces an error. This change relaxes the strict sequence of table-before-view creation, permitting greater flexibility in database definition.

When accessing a view that has been created with errors, the view is "compiled" to determine if it has become usable. A new SQL command, ALTER VIEW ... COMPILE lets you force the recompilation at any time, without waiting for the view to be accessed.

View Still Valid after Changing Table

In Version 6, a view failed if its underlying table was modified or if it was deleted and re-created. Because ORACLE7 implements object dependencies, such a view is marked for recompilation when the underlying table changes. The view is then recompiled the next time it is accessed, or after an ALTER VIEW ... COMPILE command, so it functions normally, as long as all of the columns it accesses still exist.

Replacing a View

An existing view can be dropped and re-created in one statement using the syntax

CREATE OR REPLACE VIEW

When a view is replaced in this manner, existing GRANTS and references by other views remain intact.

SELECT * In View Definitions

In Version 6, views created with "SELECT *" would operate only so long as new columns were not added to the underlying table, because the wildcard (*) was expanded dynamically during the select. As a result, the number of columns in the changed table no longer matched the view definition.

ORACLE7 adopts SQL's standard behavior of expanding such wildcards when the view is defined. The number of columns is then statically defined, because a statement like

SELECT *

is transformed into one like

SELECT column1, column2, column3

As a result, the view remains valid even when additional columns are added to the underlying table. (If there is an error when the view is created, the wildcard is expanded at the first successful compilation of the view.)

________________________________________

ORACLE Datatype Changes

Conversion of Datatypes

Refer to "Data Dictionary Conversion" on page 3 - 3 for more information on how your existing Version 6 datatypes will be converted to ORACLE7 datatypes using different migration options.

Character Datatypes

Version 6 supported one datatype, CHAR, for handling variable-length character strings, and one synonym for that datatype, VARCHAR. ORACLE7 uses two datatypes for character strings, as described in the following sections.

 CHAR

Values of this datatype are fixed-length character strings with a maximum length of 255. CHAR values are compared using padded comparison semantics. The ORACLE7 CHAR datatype is not equivalent to the Version 6 CHAR datatype.

 VARCHAR2

Values of this datatype are variable-length character strings of maximum length 2000. VARCHAR2 values are compared using non-padded comparison semantics. The ORACLE7 VARCHAR2 datatype is equivalent to the Version 6 CHAR datatype except for the difference in maximum lengths.

 VARCHAR

VARCHAR is a synonym for the VARCHAR2 datatype in ORACLE7, but future versions may use VARCHAR as a separate datatype, so use of VARCHAR is not recommended. Refer to the ORACLE7 SQL Language Reference Manual for a description of the different comparison semantics.

LONG and LONG RAW Datatypes

The LONG and LONG RAW datatypes can store a variable-length character string of up to 2 gigabytes (231-1 bytes) in length, as opposed to 64K bytes in Version 6. One piece at a time can be fetched with the new OCI call OFLNG.

MLSLABEL

The MLSLABEL type was added for compatibility with Trusted ORACLE. For more information, see the Trusted ORACLE7 Server Administrator's Guide.

Addition of ROWLABEL Column

ROWLABEL is a new virtual column in ORACLE tables. Like ROWID, it is automatically generated when a table is created. It has the datatype MLSLABEL. This column exists for compatibility with Trusted ORACLE, which uses it to store the security label of each row in a table. For more information, see the Trusted ORACLE7 Server Administrator's Guide.

Change in ROWID Format

In order to support more files per database, the number of bits in the ROWID used to identify file number and block id have been reallocated. This change has no effect in the externally visible ROWID.

________________________________________

SQL Command Changes

Many new commands and functions have been added to the SQL language in ORACLE7, and many changes have been made to existing commands. New format models and datatypes have also been added, along with new built-in trig and math functions, the UNION ALL operator, and a new comment facility. Two new keywords have been added: ROWLABEL and VARCHAR2. For additional information, refer to Appendix A "Summary of Changes to SQL in ORACLE7" of the ORACLE7 Server SQL Language Reference Manual for a detailed description of the changes.

________________________________________

Initialization Parameters

The following tables list the obsolete and renamed Version 6 initialization parameters and the new ORACLE7 parameters. Refer to Appendix A "Parameter Files and Initialization Parameters" of the ORACLE7 Server Administrator's Guide for a complete description of all initialization parameters.

Obsolete Parameters

BG_PRIORITY	FREE_LIST_PROC

CALLS	GC_SORT_LOCKS

CONTEXT_AREA	INSTANCES

CONTEXT_INCR	LANGUAGE

CPU_COUNT	LOG_ALLOCATION

DP_BLOCK_CACHE_	LOG_BLOCKS_DURING_

PROTECT	BACKUP

DP_BLOCK_COMPUTE_	LOG_BUFFERS_DEBUG

CHECKSUMS

DB_BLOCK_HASH_	LOG_DEBUG_MULTI_

BUCKETS	INSTANCE

DB_BLOCK_MAX_	LOG_ENTRY_PREBUILD_

CLEAN_PCT	THRESHOLD

DB_BLOCK_MAX_MOD_PCT	LOG_IO_SIZE

DB_BLOCK_MAX_SCAN_CNT	MESSAGES

DB_BLOCK_MAX_SCAN_PCT	ROW_CACHE_BUFFER_SIZE

DB_BLOCK_MULTIPLE_	ROW_CACHE_

HASHCHAIN_LATCHES	ENQUEUES

DB_BLOCK_TIMEOUT_	ROW_CACHE_

WRITE_PCT	INSTANCE_LOCKS

DB_BLOCK_WRITE_	ROW_CACHE_MULTI_

BATCH	INSTANCE

DB_HANDLES	ROW_CACHE_PCT_FREE

DB_HANDLES_CACHED	SAVEPOINTS

DC_*(All DC_parameters)	SCN_INCREMENT

DDL_LOCKS	SCN_THRESHOLD

ENQUEUE_DEBUG_	SORT_READ_FAC

MULTI_INSTANCE

ENQUEUE_HASH	USE_ROW_ENQUEUES

ENQUEUE_LOCKS	USER_SESSIONS

FREE_LIST_INST	WAIT_FOR_SYNC

If you use Version 6 National Language Support, you must alter your Oracle7 parameter file to include the appropriate initialization parameters. Additionally, the specification of the NLS_SORT parameter has changed. Refer to Appendix A "National Language Support" in the ORACLE7 Server Concepts Manual.

Renamed Parameter	New Name

MI_BG_PROCS	GC_LCK_PROCS

New Parameters

AUTO_MOUNTING	MTS_MAX_DISPATCHERS

CHECKPOINT_PROCESS	MTS_MAX_SERVERS

COMMIT_POINT_STRENGTH	MTS_SERVERS

CURSOR_SPACE_FOR_TIME	MTS_SERVICE

DB_BLOCK_CHECKPOINT_BATCH	NLS_CURRENCY

DB_DOMAIN	NLS_DATE_FORMAT

DB_MOUNT_MODE	NLS_DATE_LANGUAGE

DISCRETE_TRANSACTIONS_	NLS_ISO_CURRENCY

ENABLED

DISTRIBUTED_LOCK_TIMEOUT	NLS_LANGUAGE

DISTRIBUTED_RECOVERY_	NLS_NUMERIC_CHARACTERS

CONECTION_HOLD_TIME

DISTRIBUTED_TRANSACTIONS	NLS_TERRITORY

GC_LCK_PROCS	OPEN_COMPATIBLE

GLOBAL_NAMES	OPEN_MOUNTS

INSTANCE_NUMBER	OPTIMIZER_COMP_WEIGHT

LABEL_CACHE_SIZE	OPTIMIZER_OVERRIDE

LICENSE_MAX_SESSIONS	OS_AUTHENT_PREFIX

LICENSE_MAX_USERS	OS_ROLES

LICENSE_SESSIONS_WARNING	RECOVERY_COMPATIBLE

LOG_ARCHIVE_BUFFERS	REMOTE_OS_AUTHENT

LOG_ARCHIVE_BUFFER_SIZE	REMOTE_OS_ROLES

LOG_ARCHIVE_FORMAT	RESOURCE_LIMIT

LOG_CHECKPOINT_TIMEOUT	SHARED_POOL_SIZE

MAX_ENABLED_ROLES	SMALL_TABLE_THRESHOLD

MAX_ROLLBACK_SEGMENTS_MLS	SORT_AREA_RETAINED_SIZE

MLS_LABEL_FORMAT	TEMPORARY_TABLE_LOCKS

MTS_DISPATCHERS	THREAD

MTS_LISTENER_ADDRESS

________________________________________

Data Dictionary Changes

This section describes the changes to the ORACLE7 data dictionary. See the Data Dictionary appendix of the ORACLE7 Server Application Developer's Guide for descriptions of the standard tables and views that are available to developers. The Data Dictionary appendix in the ORACLE7 Server Administrator's Guide lists all tables and views.

Views

ORACLE7 introduces a new set of data dictionary views. The views are created by the database administrator under the schema of user SYS. Several data dictionary views from Version 6 have either changed or become obsolete. Others have been renamed. In addition, new views have been added in ORACLE7. The following tables summarize the changes.

Obsolete Views

ACCESSIBLE_COLUMNS	DBA_AUDIT_RESOURCE

ACCESSIBLE_TABLES	DBA_CROSS_REPS

CONSTRAINT_COLUMNS	MYPRIVS

CONSTRAINT_DEFS	USER_AUDIT_CONNECT

DBA_AUDIT_CONNECT	USER_AUDIT_RESOURCE

DBA_AUDIT_DBA	USER_CROSS_REFS

Changed Views

ALL_INDEXES	DBA_TAB_COLUMNS

ALL_OBJECTS	DBA_USERS

ALL_TAB_COLUMNS	TABLE_PRIVILEGES

ALL_TABLES	USER_AUDIT_TRAIL

COLUMN_PRIVILEGES	USER_CLUSTERS

DBA_AUDIT_EXISTS	USER_INDEXES

DBA_AUDIT_TRAIL	USER_OBJECTS

DBA_CLUSTERS	USER_TABLES

DBA_INDEXES	USER_TAB_COLUMNS

DBA_OBJECTS	USER_USERS

DBA_TABLES

Renamed Views

Old Name	New Name

ALL_COL_GRANTS	ALL_COL_PRIVS

ALL_COL_GRANTS_MADE	ALL_COL_PRIVS_MADE

ALL_COL_GRANTS_RECD	ALL_COL_PRIVS_RECD

ALL_TAB_GRANTS	ALL_TAB_PRIVS

ALL_TAB_GRANTS_MADE	ALL_TAB_PRIVS_MADE

ALL_TAB_GRANTS_RECD	ALL_TAB_PRIVS_RECD

AUDIT_OPTION_MAP	STMT_AUDIT_OPTION_MAP

DBA_COL_GRANTS	DBA_COL_PRIVS

DBA_TAB_GRANTS	DBA_TAB_PRIVS

DBA_SYS_AUDIT_OPTS	DBA_STMT_AUDIT_OPTS

DBA_TAB_AUDIT_OPS	DBA_OBJ_AUDIT_OPS

USER_COL_GRANTS	USER_COL_PRIVS

USER_COL_GRANTS_MADE	USER_COL_PRIVS_MADE

USER_COL_GRANTS_RECD	USER_COL_PRIVS_RECD

USER_TAB_AUDIT_OPTS	USER_OBJ_AUDIT_OPTS

USER_TAB_GRANTS	DBA_TAB_PRIVS

USER_TAB_GRANTS_MADE	USER_TAB_PRIVS_MADE

USER_TAB_GRANTS_RECD	USER_TAB_PRIVS_RECD

New Views

ALL_DEPENDENCIES	DBMS_ALERT_INFO

ALL_ERRORS	DBMS_LOCK_ALLOCATED

ALL_OBJECT_SIZE	DEPTREE

ALL_SNAPSHOTS	EXCEPTIONS

ALL_SOURCE	GLOBAL_NAME

ALL_TRIGGERS	IDEPTREE

CHAINED_ROWS	INDEX_HISTOGRAM

DBA_2PC_PENDING	INDEX_STATS

DBA_2PC_NEIGHBORS	PLAN_TABLE

DBA_AUDIT_OBJECT	PUBLIC_DEPENDENCY

DBA_AUDIT_SESSION	RESOURCE_COST

DBA_AUDIT_STATEMENT	ROLE_ROLE_PRIVS

DBA_BLOCKERS	ROLE_SYS_PRIVS

DBA_CONSTRAINTS	ROLE_TAB_TABS

DBA_DDL_LOCKS	SESSION_PRIVS

DBA_DEPENDENCIES	SESSION_ROLES

DBA_DML_LOCKS	USER_AUDIT_OBJECT

DBA_ERRORS	USERT_AUDIT_SESSION

DBA_LOCKS	USER_AUDIT_STATEMENT

DBA_OBJECT_SIZE	USER_DEPENDENCIES

DBA_PROFILES	USER_ERRORS

DBA_ROLE_PRIVS	USER_OBJECT_SIZE

DBA_ROLES	USER_RESOURCE_LIMITS

DBA_SNAPSHOTS	USER_ROLE_PRIVS

DBA_SNAPSHOT_LOGS	USER_SNAPSHOTS

DBA_SOURCE	USER_SNAPSHOT_LOGS

DBA_SYS_PRIVS	USER_SOURCE

DBA_TRIGGERS	USER_SYS_PRIVS

DBA_WAITERS	USER_TRIGGERS

Additional Views

These are new tables and views which have been created for use by the ORACLE server or ORACLE utility programs. They are mentioned for the sake of completeness, but are of little interest to the user or administrator.

CODE_PIECES	LOADER_TAB_INFO

CODE_SIZE	LOADER_TRIGGER_INFO

ERROR_SIZE	PARSED_PIECES

LOADER_COL_INFO	PARSED_SIZE

LOADER_CONSTRAINT_INFO	SOURCE_SIZE

LOADER_INDCOL_INFO	STMT_AUDIT_OPTION_MAP

LOADER_IND_INFO	SYSTEM_PRIVILEGE_MAP

LOADER_PARAM_INFO	TABLE_PRIVILEGE_MAP

Dynamic Performance Tables

This section lists the changes to the dynamic performance tables.

Changed V$ Views

V$ACCESS	V$PROCESS

V$FILESTAT	V$ROLLNAME

V$LATCH	V$ROLLSTAT

V$LOCK	V$SESSION

V$LOGFILE	V$WAITSTAT

V$PARAMETER

New V$ Views

V$ARCHIVE	V$OPEN_CURSOR

V$BACKUP	V$QUEUES

V$CIRCUITS	V$RECOVERY_LOG

V$DATABASE	V$RECOVER_FILE

V$DATAFILE	V$REQDIST

V$DB_OBJECT_CACHE	V$SESSION_WAIT

V$DISPATCHERS	V$SGASTAT

V$ENABLEDPRIVS	V$SHARED_SERVERS

V$LIBRARYCACHE	V$SQLAREA

V$LICENSE	V$SQLTEXT

V$LOADCSTAT	V$THREAD

V$LOADSTAT	V$TIMER

V$LOG	V$TYPE_SIZE

V$LOG_HISTORY	V$VERSION

V$LOGHIST	V$VERSION

V$NLS_PARAMETERS

Trusted ORACLE Views

The following views and dynamic tables are new in Trusted ORACLE7:

ALL_LABELS	V$SECONDARY

ALL_MOUNTED_DBS	V$SYSLABEL

________________________________________

Version 6 Compatibility

Version 6 to ORACLE7 Migration Tool

The ORACLE7 migration tool converts a Version 6 database into ORACLE7 form. The conversion happens "in place"; the database does not need to be reloaded from backup files. For details, see Chapter 3 "The Migration Utility."

Version 6 SQL Compatibility Mode

Each of the ORACLE7 utilities makes it possible to select Version 6 SQL command compatibility. In SQL*DBA, for example, the SET COMPATIBILITY command is used. For more information on the SET command, see the SQL*DBA Commands section of the ORACLE7 Server Utilities User's Guide. For more information on the effects of setting the compatibility mode, see the ORACLE7 Server SQL Language Reference Manual. The effects include the following:

•	CHAR datatypes create variable length columns in CREATE TABLE statements, instead of the fixed length columns they would ordinarily create in ORACLE7 mode.

•	Version 6 syntax for integrity constraints is recognized in CREATE TABLE statements, instead of the new ORACLE7 syntax.

•	PCTINCREASE cannot be specified for ORACLE7 rollback segments. (The specification is allowed in V6 compatibility mode, but is ignored.)

•	MAXEXTENTS cannot be specified for ORACLE7 rollback segments. (It is allowed in V6 compatibility mode.)

CATALOG6.SQL

This command file augments the ORACLE7 data dictionary by re-creating obsolete Version 6 views and views that were replaced in ORACLE7. The file DROPCAT6.SQL can be used later to undo these changes to the ORACLE7 data dictionary.

EXPVEW6.SQL

Running EXPVEW6.SQL allows remote Version 6 sites to do exports from ORACLE7 databases. See the ORACLE7 Server Utilities User's Guide for more details.

EXPEOB6.SQL

This script lists the objects that are excluded when Version 6 sites do exports from an ORACLE7 database. See the ORACLE7 Server Utilities User's Guide for more details.

________________________________________

New and Renamed SQL Scripts

The names of many SQL scripts were changed in order to make them easier to identify. In addition, many new scripts were added to support ORACLE7's extended functionality. The new and changed scripts are listed below.

By convention, scripts that begin with CAT are intended to be run by SYS. They create views and tables in the data dictionary. Scripts that begin with DBMS are also run by SYS. They create stored procedures, functions, and packages. Scripts that begin with UTL are intended to be run by individual users. For more information, see Chapter 2 "Creating a Database" and Appendix B "Data Dictionary Reference" in the ORACLE7 Server Administrator's Guide.

Renamed Scripts

Old Name

Renamed Scripts	New Name

Old Name

AUDIT.SQL	CATAUDIT.SQL

BLOCKING.SQL	CATBLOCK.SQL

BSTAT.SQL	CATBSTAT.SQL

DBA_SYN.SQL	CATDBSYN.SQL

EXPVEW.SQL	CATEXP.SQL

NOAUDIT.SQL	CATNOAUD.SQL

PSVIEWS.SQL	CATPARR.SQL

ULVIEW.SQL	CATLDR.SQL

DISPIDXS.SQL	UTILDXSD.SQL

ESTAT.SQL	UTLIDXSS.SQL

IDXSTAT.SQL	UTLESTAT.SQL

LOCKTREE.SQL	UTLLOCKT.SQL

MONITOR.SQL	UTLMONTR.SQL

ONEIDXS.SQL	UTILDXSO.SQL

SAMPLE1.SQL..SAMPLE4.SQL	UTLSAMPL.SQL (combined)

XPLAINPL.SQL	UTLXPLAN.SQL

New Scripts

CATEXP6.SQL	DBMSALRT.SQL	UTLCHAIN.SQL

CATNOPRC.SQL	DBMSLOCK.SQL	UTLDTREE.SQL

CATPROC.SQL	DBMSMAIL.SQL	UTLEXCPT.SQL

CATSTAT.SQL	DBMSPIPE.SQL	UTLMAIL.SQL

DBMSSNAP.SQL

DBMSTRST.SQL

DBMSUTIL.SQL

________________________________________

Other Changes

Larger Control Files

ORACLE7 control files can be larger than they were in Version 6, although the maximum size is still operating system dependent. On most systems, the maximum has been increased from 500 blocks to 2500 blocks. Consult your installation or user's guide for more information.

More Data Files

Although the maximum number of operating system files that can be in the database is operating system dependent, the maximum has been increased. In ORACLE Version 6, the maximum was typically 255 files. In ORACLE7, the maximum is typically 1022 or more. Consult your installation or user's guide for details.

Fewer Data Blocks

The number of data blocks per database may have shrunk for your system's implementation of the ORACLE7 Server. If it has, then the database blocksize may need to be increased in order to create a database of the same size.

MAXEXTENTS

The default value for MAXEXTENTS depends on the database block size in ORACLE7, as well as the maximum value. The details are operating system specific. Consult your installation or user's guide for details.

Change in Use of PCTINCREASE

When a new extent is allocated in ORACLE7, the size of the new extent is based on the last automatically allocated extent in that segment. As a result, changing PCTINCREASE produces an incremental change in extent size, regardless of how many extents have been previously allocated. (In Version 6, the size of the new extent depended on the total number of allocated extents, which magnified the effects of changes to PCTINCREASE.) For details, consult Chapter 8 "Managing Schema Objects of the ORACLE7 Server Administrator's Guide.

Assigning Tablespace Quotas

In Version 6, tablespace quotas were assigned with the GRANT command. In ORACLE7, tablespace quotas are assigned with the CREATE USER and ALTERUSER commands.

The Version 6 syntax made it appear to be legal to assign tablespace quotas to PUBLIC, although in fact it was not. The ORACLE7 syntax precludes that possibility.

Accessing Tables During Index Creation

In Version 6, it was not possible to select from a table while a CREATE INDEX statement was operating. In ORACLE7, rows can be selected from tables while indexes are being created.

Blocks in Rollback Segments Dedicated to Transactions

Blocks in ORACLE7 rollback segments are not shared by multiple transactions. Instead, each ORACLE7 rollback segment block is exclusively dedicated to a single transaction. As a result, you may need to increase the size of your rollback segments if your transactions are very small relative to the rollback segment block size.

Messages and Codes

Because of increased functionality, many new messages have been added to ORACLE7, and some Version 6 messages have become obsolete. If your programs use any hardcoded message numbers, please verify that the message number and message text have remained the same for any messages that your programs test for.

In addition, some message numbers have been reused. See the changes appendix of the ORACLE7 Server Messages and Codes Manual for a list of obsolete and reused message codes.

ALERT Filenames

Previously, ALERT filenames had the following format:

ALERT_instanceName.LOG

In ORACLE7, they usually have the following format:

nodename_instanceName_ALERT.LOG

Note: The format depends on your operating system. Consult your user or installation guide for details.

TRACE Filenames

Previously, TRACE filenames had the following format:

imageName_instanceID_process#.LOG

In ORACLE7, they usually have the following format:

nodename_instanceID_imageName_FG_process#.LOG

Note: The format depends on your operating system. Consult your user or installation guide for details.


APPENDIX B. MIGRATION UTILITY ERRORS AND MESSAGES

This appendix lists the errors and messages that may be encountered when using the migration utility. Probable cause and corrective action are given for each error.

________________________________________

Migration Utility Errors and Messages

The migration utility can return various error messages and informational messages. This section describes the errors that can be encountered when using the migration utility. For each error probable cause and corrective action are given. Informational messages are also listed, although no action is necessary.

Cannot create conversion file, records exceed num bytes

Cause:	An internal error has occurred. A valid control file could not be created from the Version 6 control file.

Action:	Check the Version 6 control file for corruption, fix any problems and rerun the migration utility.

CFILE	- use this init.ora file

Cause:	This is an informational message that displays information about the CFILE command line argument.

Action:	No user action is required.

CHECK_ONLY          - estimate V7 catalog space requirement only

Cause:	This is an informational message that displays information about the CHECK_ONLY command line argument.

Action:	No user action is required.

CHECK_ONLY and NO_SPACE_CHECK are mutually exclusive

Cause:	Two mutually exclusive command line options were passed to the migration utility.

Action:	Rerun the migration utility using only one of these options.

Command line argument value must be TRUE or FALSE (str)

Cause:	You entered a command line argument with a value other than TRUE or FALSE.

Action:	Check the syntax of the command line argument, correct the statement, and retry the operation.

Command line arguments must be of the form <keyword>=<value> (str)

Cause:	You used a command line argument improperly.

Action:	Check the syntax of the command line argument, correct the statement, and retry the operation.

Command line arguments:

Cause:	This is an informational message that displays the command line arguments.

Action:	No user action is required.

Command name not found (str)

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

Command not of form CMD(ARG1, ARG2, ...)

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

CNVFILE          - use this file for convert.ora

Cause:	This is an informational message that displays information about the CNVFILE command line argument.

Action:	No user action is required.

Copy long command must be of form

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

Could not find single contiguous extent of numbytes for c_file#_block#

Cause:	You do not have enough contiguous space in your SYSTEM tablespace.

Action:	Add additional free space to your SYSTEM tablespace and repeat the migration.

Could not find single contiguous extent of numbytes for c_ts#

Cause:	You do not have enough contiguous space in your SYSTEM tablespace.

Action:	Add additional free space to your SYSTEM tablespace and repeat the migration.

Could not translate logical name

Cause:	An internal error has occurred.

Action:	Check that the logical is defined correctly and rerun the migration utility.

Data type must be long for column name

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

DBNAME          - database name

Cause:	This is an informational message that displays information about the DBNAME command line argument.

Action:	No user action is required.

Dictionary constant not found - name

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

DUMPCF          - dump control file symbolically

Cause:	This is an informational message that displays information about the DUMPCF command line argument. You should generally only use this argument when asked to do so by your Customer Support representative.

Action:	No user action is required.

ECHO          - echo all SQL statements to standard out

Cause:	This is an informational message that displays information about the ECHO command line argument.

Action:	No user action is required.

Error calling slgtd

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

Error closing file name

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

File header does not fit in num bytes

Cause:	An internal error has occurred.

Action:	Check the control file for corruption, fix any problems and repeat the migration.

Fixed portion of control file does not fit in num bytes

Cause:	An internal error has occurred.

Action:	Check the control file for corruption, fix any problems and repeat the migration.

Found NULL SQL statement

Cause:	An internal error has occurred; possibly the MIGRATE.SQL script is corrupted.

Action:	Check that the version of the migration utility, MIGRATE.BSQ, and of the target ORACLE7 software are compatible, and that no corruption exists in migrate.bsq. Fix any such problems and rerun the migration.

Free space found: num

Cause:	This is an informational message that informs you how much free space you have in your SYSTEM tablespace.

Action:	Be certain that you have enough free space before running the migration utility.

Incomplete write

Cause:	An internal error has occurred. Data could not be written to disk.

Action:	Check that the file access permissions are correct, that you have enough space or quota to write this file, and that the disk is not corrupt. Fix any problems and repeat the migration.

Insufficient space for new dictionaries, num bytes needed, num found

Cause:	There is insufficient room in your SYSTEM tablespace for the new data dictionary information.

Action:	Allocate the additional space required in the SYSTEM tablespace, and rerun the migration utility.

MIGFILE          - use this file for migrate.bsq

Cause:	This is an informational message that displays information about the MIGFILE command line argument.

Action:	No user action is required.

Mounting database ...

Cause:	This is an informational message. The migration utility is mounting the Version 6 database.

Action:	No user action is required.

NEW_DBNAME          - new name for the database

Cause:	This is an informational message that displays information about the NEW_DBNAME command line argument.

Action:	No user action is required.

NO_SPACE_CHECK             - do not execute the space check

Cause:	This is an informational message that displays information about the NO_SPACE_CHECK command line argument.

Action:	No user action is required. You should be certain that you have adequate space before running the migration utility with this option.

Offline tablespaces with outstanding undo, cannot proceed

Cause:	One or more tablespaces were offline, with outstanding save undo, when you attempted to use the migration utility to migrate.

Action:	If you are using version 6.0.34.3, or later, complete the following steps:

•	Query X$KTTVS and determine which tablespaces are offline and have outstanding transaction information. The tablespace number is listed as the third column of the X$KTTVS table.

•	Bring each of these tablespaces online and take them offline without making any changes to the data in the tablespaces.

•	Rerun the migration utility.

If you are using a pre-6.0.34.3 version, first make certain that all offline tablespaces have been taken offline cleanly. Then rerun the migration utility with ALLOW_OFFLINE set TRUE. Use caution, however, since any tablespaces that were not taken offline cleanly cannot be recovered after migrating to ORACLE7.

Opening database ...

Cause:	This is an informational message. The migration utility is opening the Version 6 database.

Action:	No user action is required.

ORA-num: ???

Cause:	The migration utility has received an ORA error and cannot retrieve the message text for the error.

Action:	Take appropriate action for ORACLE error num.

Parameter file exceeds num bytes

Cause:	The parameter file for your Version 6 database exceeds the maximum size given.

Action:	If possible, reduce the size of your parameter file by removing obsolete parameters. Otherwise, contact your Customer Support representative.

Parameter buffer overflow

Cause:	The initialization parameter file is too large to fit in the buffer.

Action:	Reduce the size of the parameter file, possibly by removing any obsolete parameters, and repeat the migration.

PFILE          - use this init.ora file

Cause:	This is an informational message that displays information about the PFILE command line argument.

Action:	No user action is required.

Seek error in file name

Cause:	An internal error has occurred.

Action:	An error occurred reading file name. Check that the file and disk are not corrupted. Fix any such corruption before restarting the migration.

Short read, num bytes requested, num bytes read

Cause:	There is a problem reading the control file.

Action:	Check the CFILE parameter to make certain that the migration utility is using the correct control file.

Shutting down database (abort) ...

Cause:	An internal error has occurred.

Action:	Additional error messages should occur to inform you of the cause of the shutdown. Follow the actions suggested for these messages.

Shutting down database ...

Cause:	This is an informational message. The migration utility is shutting down the Version 6 database.

Action:	No user action is required.

SPOOL          - spool output to file

Cause:	This is an informational message that displays information about the SPOOL command line argument.

Action:	No user action is required.

Starting up database ...

Cause:	This is an informational message. The migration utility is starting up a Version 6 instance.

Action:	No user action is required.

Too many arguments in command (num max)

Cause:	You specified too many arguments on the command line.

Action:	Check the syntax of the command and specify fewer command line options.

Unable to open file name

Cause:	An internal error has occurred, or a database file was offline or not in the expected location when the migration utility was started.

Action:	Check to see that all database files are online before shutting down your Version 6 database. Check that the file exists and has access permissions that allow ORACLE to open and read the file. If possible, check that the file is not corrupt and the disks on which the file resides are not corrupt. Fix any problems and restart the migration utility.

Unable to allocate buffer space to copy longs

Cause:	The migration utility could not allocate memory to serve as a buffer for copying LONG columns in the database.

Action:	Make sure enough machine resources are available for the utility and rerun the migration.

Unable to read file name

Cause:	An internal error occurred, or a database file was offline or not in the expected location when the migration utility was started.

Action:	Check to see that all database files are online before shutting down your Version 6 database. Check that the file exists and has access permissions that allow ORACLE to open and read the file. If possible, check that the file is not corrupt and the disks on which the file resides are not corrupt. Fix any problems and restart the migration utility.

Unable to write file name

Cause:	An internal error occurred.

Action:	Check that the correct access permissions exist for ORACLE to write to the file. Check that the disks to which the file is being written are not corrupt. Fix any such corruption before restarting the migration.

V7 catalog space requirement: num

Cause:	This is an informational message that informs you how much additional space you need in your SYSTEM tablespace in order to successfully run the migration utility.

Action:	Be certain that you have the specified amount of additional space before running the migration utility.


APPENDIX C. OPERATING SYSTEM SPECIFIC INFORMATION

This Guide occasionally refers to other ORACLE manuals that contain detailed information for using ORACLE only on a specific operating system. These Oracle manuals are often called installation or user's guides, although the exact name may vary among operating systems.

This appendix lists all the references in this manual to operating system specific ORACLE manuals. If you are using ORACLE on multiple operating system, this appendix can help you ensure that your applications are portable across these operating systems.

The following table lists references to operating system specific information by topic:

Topic	Page

Creating a test Version 6 database to rehearse migration	2 - 6

The location of the CONVERT.ORA file	3 - 2

Running the migration utility	3 - 4

The migration utility parameters	3 - 4

Installing ORACLE7	3 - 5, 4 - 3

The format of ALERT filenames	A - 33

The format of TRACE filenames	A - 33