D
Upgrading an Oracle7 Database Using the MIG Utility

This appendix describes how to use the MIG utility to manually upgrade an Oracle7 database to the new Oracle9i release. This appendix covers the following topics:

Overview of the MIG Utility

The MIG utility converts the data dictionary and structures of an Oracle7 database into Oracle9i format. To upgrade the database, you first install the Oracle9i software and run the MIG utility on the Oracle7 database. Then, you execute a series of ALTER DATABASE statements on the new Oracle9i database and run the u0703040.sql upgrade script.

The completion of these procedures results in the conversion of the following Oracle7 structures into structures that can be used by Oracle9i:

Data files (file headers only)
Data dictionary
Control files
Rollback segments

Outline of the Upgrade Process Using the MIG Utility

The following sections provide an outline of the upgrade process using the MIG utility:

In the Oracle7 Environment

You run the Oracle9i MIG utility, which creates and populates a new data dictionary based on the data dictionary of the Oracle7 database, and also creates a binary file based on the control file of the Oracle7 database. This binary file is called the convert file.

Note:

You can run the Oracle9i MIG utility multiple times (without opening the database in Oracle9i) and still be able to return to the Oracle7 database. However, running the MIG utility automatically eliminates the Oracle7 database catalog views (see "Abandoning the Oracle7 Upgrade").

In the Oracle9i Environment

You execute an ALTER DATABASE CONVERT statement, which creates a new control file based on the convert file generated by the MIG utility, converts all online datafile headers to Oracle9i format, and mounts the Oracle9i database.

The file headers of offline datafiles and read-only tablespaces are not updated during the upgrade. The file headers of offline datafiles are converted later when they are brought online, and the file headers of read-only tablespaces are converted if and when they are made read-write sometime after the upgrade; however, they never have to be made read-write.
You execute an ALTER DATABASE OPEN RESETLOGS MIGRATE statement, which automatically converts all objects and users defined in the new dictionary to Oracle9i specifications, and converts all rollback segments to Oracle9i format.

If a database rollback segment is in a tablespace that is offline when the Oracle9i database is opened, then the rollback segment is not converted immediately to Oracle9i database format. Instead, the rollback segment is converted the first time the tablespace is brought online in Oracle9i.
You run the u0703040.sql upgrade script. This script creates and alters certain system tables and drops the MIGRATE user. It also runs the catalog.sql and catproc.sql scripts, which create the system catalog views and all the necessary packages for using PL/SQL.

System Considerations and Requirements for Using the MIG Utility

The following sections discuss additional system considerations and requirements for using the MIG utility. These requirements supplement the general upgrade requirements discussed in "System Considerations and Requirements".

Space Requirements

Oracle9i executables may require as much as three times the disk space required by Oracle7 executables. This requirement may cause you to run out of disk space during the upgrade. If you are installing Oracle9i onto a computer system that already has Oracle7 installed, then ensure that you have enough hard disk space and RAM for both databases. You need to add the system requirements for Oracle9i and Oracle7 to determine the total system requirements.

The MIG utility requires relatively little temporary space. It needs only enough extra room in the SYSTEM tablespace to hold the new Oracle9i data dictionary simultaneously with the existing Oracle7 data dictionary.

The space required to hold an Oracle data dictionary depends on how many objects are in the database. Typically, a new Oracle9i data dictionary requires double the space that its Oracle7 data dictionary required. If necessary, add space to the SYSTEM tablespace.

In addition, running scripts such as the u0703040.sql upgrade script may require more space in the SYSTEM tablespace and in the rollback segments. Insufficient space results in an "unable to extend" warning when you run a script. The exact amount of space required to run the scripts varies depending on the number of objects in the database. If you encounter "unable to extend" warnings when you run a conversion script, then try increasing the SYSTEM tablespace and the rollback segments; then, rerun the script.

See Also:

Your operating system-specific installation documentation for detailed information about system requirements

Block Size Considerations

The value of the DB_BLOCK_SIZE initialization parameter in both the Oracle7 database and in the upgraded Oracle9i database must be the same. Oracle9i requires a minimum block size of 2048 bytes (2 KB). Above this amount, integer multiples of your operating system's physical block size are acceptable. However, multiples of 2 KB, especially powers of 2--that is, 2 KB, 4 KB, 8 KB, 16 KB--provide for the most robust operation.

Make sure the Oracle9i block size setting meets the following criteria:

Matches the Oracle7 setting.
Is at least 2048 bytes (2 KB). The MIG utility displays an error message if the Oracle7 block size is less than 2 KB.
Is an integer-multiple of your operating system's physical block size, preferably a multiple of 2 KB.

Considerations for SQL*Net

There are many issues relating to SQL*Net that you must consider when you upgrade your database to the new Oracle9i release, not the least of which is deciding whether you will migrate to Oracle Net Services.

See Also:

Appendix B, "Upgrade Considerations for Oracle Net Services" for information about these issues and for instructions on migrating from SQL*Net to Oracle Net Services

Considerations for Replication Environments

You can upgrade an Oracle7 replication environment to Oracle9i. Oracle7 sites can coexist and run successfully with Oracle8, Oracle8i, and Oracle9i sites within the replication environment. However, take special care to accommodate the various replication features implemented on each system.

See Also:

Appendix E, "Database Migration and Compatibility for Replication Environments" for detailed instructions about upgrading systems using replication features

Considerations for Migrating from ConText to Oracle Text

See Oracle Text Application Developer's Guide for information about migrating from ConText to Oracle Text.

Distributed Database Considerations

When upgrading from Oracle7 in a distributed database configuration, make sure that no pending transactions are in the DBA_2PC_PENDING data dictionary view before upgrading the database. Otherwise, when you open the database after the upgrade using the ALTER DATABASE RESET LOGS statement and a transaction is pending, you will encounter an error.

If there are any pending transactions, then resolve them before you migrate using the SQL commands COMMIT FORCE or ROLLBACK FORCE.

Prepare the Oracle7 Database to be Upgraded

Additional preparatory steps are required before you upgrade your Oracle7 database to the new Oracle9i release. Complete the following steps:

Log in to the system as the owner of the Oracle home directory of the Oracle7 database being upgraded.
Start Server Manager.
Connect to the database instance as a user with SYSDBA privileges.
If the Procedural Option is not installed, then use your Oracle7 installation media to install it. See your operating system-specific Oracle documentation for instructions.

If you are not sure whether the Procedural Option is installed, then you can check by starting Server Manager.

The following is an example of the messages you will see when Server Manager starts:
```
Oracle Server Manager Release 2.3.3.0.0 - Production

Copyright (c) Oracle Corporation 1994, 1995. All rights reserved.

Oracle7 Server Release 7.3.4.0.0 - Production
With the distributed, replication, parallel query, Parallel Server
and Spatial Data options
PL/SQL Release 2.3.4.0.0 - Production
```
The messages you see may be slightly different, based on the options you have installed and their release numbers. If you see "PL/SQL" in the messages, as in the last line in the preceding example, then the Procedural Option is installed. Otherwise, it is not installed.
Make sure all datafiles and tablespaces are either online or offline normal.

To determine whether any datafiles require recovery, issue the following SQL statement:
```
SELECT * FROM v$recover_file;
```
You should see a "0 rows selected" message, which indicates that all datafiles are either online or offline normal. If any datafiles are listed, then you must restore the datafiles before you upgrade the database. You can use the V$DATAFILE dynamic performance view to find the datafile name based on the datafile number. The MIG utility will not proceed, and will display an error, if any datafiles require media recovery.

Tablespaces that are not taken offline cleanly must be dropped or brought online before the upgrade. Otherwise, these tablespaces will not be available under Oracle9i after the upgrade. Typically, tablespaces that are taken offline by using an ALTER TABLESPACE OFFLINE IMMEDIATE or ALTER TABLESPACE OFFLINE TEMPORARY statement require media recovery.

After the upgrade, tablespaces that are offline when you open the new Oracle9i database remain in Oracle7 database file format. The offline tablespaces can be brought online at any time after the upgrade, and the file headers are converted to Oracle9i format at that time. In addition, if you want to avoid large restores in the event of a failure, then you can make all tablespaces except SYSTEM and ROLLBACK offline normal; then, you can restore only the datafiles for SYSTEM and ROLLBACK if you need to perform another upgrade.
Make sure no user or role has the name MIGRATE, because the MIG utility creates this schema and uses it to replace any pre-existing user or role with this name, and finally drops it from the system.

To check for a user with the name MIGRATE, issue the following SQL statement:
```
SELECT username FROM dba_users WHERE username = 'MIGRATE';
```
If you do not have a user named MIGRATE, then zero rows are selected.

To check for a role with the name MIGRATE, issue the following SQL statement:
```
SELECT role FROM dba_roles WHERE role = 'MIGRATE';
```
If you do not have a role named MIGRATE, then zero rows are selected.
Make sure the SYSTEM rollback segment does not have an OPTIMAL setting. An OPTIMAL setting may cause errors during the upgrade.

To check the OPTIMAL setting for the SYSTEM rollback segment, issue the following SQL statement:
```
SELECT a.usn, a.name, b.optsize
    FROM v$rollname a, v$rollstat b
    WHERE a.usn = b.usn AND name = 'SYSTEM';
```
Your output should be similar to the following:
```
USN        NAME                           OPTSIZE
---------- ------------------------------ ----------
         0 SYSTEM
1 row selected.
```
If there is a value in the OPTSIZE column, then issue the following SQL statement to set optimal to NULL:
```
ALTER ROLLBACK SEGMENT SYSTEM STORAGE (OPTIMAL NULL);
```
You can reset OPTIMAL when the upgrade is complete.

See Also:
The troubleshooting information in "OPTIMAL Setting for the SYSTEM Rollback Segment".
Increase the maximum number of extents for your SYSTEM rollback segment by altering the MAXEXTENTS parameter in the STORAGE clause of the ALTER ROLLBACK SEGMENT statement (optional).

The following is an example of the ALTER ROLLBACK SEGMENT statement:
```
ALTER ROLLBACK SEGMENT system
    STORAGE (NEXT 500K MAXEXTENTS 121);
```
You may need more space in the SYSTEM rollback segment to complete the upgrade successfully. If there is not enough space in your SYSTEM rollback segment, then you may encounter errors when you run the MIG utility.
Make sure the NLS_LANG environment variable is set to the character set you are using for your database.

To check your character set, issue the following SQL statement:
```
SELECT * FROM v$nls_parameters
    WHERE parameter = 'NLS_LANGUAGE'
        OR parameter = 'NLS_TERRITORY'
        OR parameter = 'NLS_CHARACTERSET';
```
You use all three values returned by this query to set NLS_LANG. For example, suppose your output for the query above is the following:
```
PARAMETER                   VALUE
---------------------  ---------------------------
NLS_LANGUAGE               AMERICAN
NLS_TERRITORY              AMERICA
NLS_CHARACTERSET          US7ASCII
```
In this case, set NLS_LANG to the following at a command prompt:
```
AMERICAN_AMERICA.US7ASCII
```
See Also:
Oracle9i Database Globalization Support Guide for information about setting NLS_LANG
Make sure you have DBA privileges, which are required to run the MIG utility.

To check if you have DBA privileges, query the DBA_ROLE_PRIVS static data dictionary view. For example, if you are connected as user SYSTEM, then enter the following SQL statement:
```
SELECT * FROM dba_role_privs WHERE grantee = 'SYSTEM';
```
You have DBA privileges if 'DBA' is listed in the GRANTED_ROLE column for the user. If you do not have DBA privileges, then connect as a user who does.
Make sure no other DBA with RESTRICTED SESSION privilege connects to the database while the MIG utility is running. Also, "Normal" users should not connect to the database during an upgrade.
Shut down the Oracle7 database cleanly using the SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE commands; do not use SHUTDOWN ABORT. The Oracle7 database must be shut down cleanly; therefore, no redo information or uncommitted transactions can remain.
```
SHUTDOWN IMMEDIATE
```
If you are using Oracle Parallel Server, then shut down all instances.

Note:
If you do not shut down the Oracle7 database before the upgrade, then the MIG utility will stop and display an error message.

Review MIG Utility Command-Line Options

The next task in the upgrade process is running the Oracle9i MIG utility. Before you begin that task, review the following MIG utility command-line options because you may need to specify some of them when you run the MIG utility. In addition, your operating system-specific Oracle documentation may contain more information about MIG utility command-line options.

`CHECK_ONLY`	When `true`, the MIG utility performs space use calculations without performing an upgrade. When `false`, the MIG utility performs both space usage calculations and the upgrade. This command-line option is mutually exclusive with `NO_SPACE_CHECK`.
DBNAME	Specifies the name of the database to upgrade (`DB_NAME` in the initialization parameter file).
`MULTIPLIER`	Specifies the initial size of the Oracle9i i_file#_block# index relative to the Oracle7 i_file#_block# index. For example, `MULTIPLIER=30` triples the initial size when the index is created. If no `MULTIPLIER` command-line option is specified, then the MIG utility uses the i_file#_block# value of 15, creating an index for Oracle9i that is 1.5 times larger than the Oracle7 i_file#_block# index.
`NEW_DBNAME`	Specifies a new name for the upgraded database. The default name "`DEFAULT`" should not be used; choose a more meaningful name.
`NO_SPACE_CHECK`	When `true`, the MIG utility does not perform a space usage check before the upgrade. When `false`, the MIG utility performs a space usage check before the upgrade. This command-line option is mutually exclusive with `CHECK_ONLY`.
`PFILE`	Specifies the name of the initialization parameter file. If no `PFILE` command-line option is specified, then the MIG utility uses the default initialization parameter file. Note: On UNIX, the pathname must be enclosed by double-quotes escaped by a backslash, for example: `mig PFILE=\"/tmp/mig/``pfile``\"`
`SPOOL`	Specifies the filename for the spool output. Note: On UNIX, the pathname must be enclosed by double-quotes escaped by a backslash, for example: `mig SPOOL=\"/tmp/mig/``spool``\"`

Run the MIG Utility

The steps required to run the MIG utility on UNIX operating systems are different than the steps required to run the MIG utility on Windows platforms. Complete the steps in the appropriate section:

Run the MIG Utility on UNIX Operating Systems

Complete the following steps to run the MIG utility on a UNIX operating system:

At a command prompt, change to the ORACLE_HOME/bin directory in your release 9.2 installation.
Run the MIGPREP utility.

The MIGPREP utility prepares the Oracle7 environment for upgrading by copying required files from the Oracle9i Oracle home to the Oracle7 Oracle home. Enter the following to run MIGPREP:
```
migprep new_oracle_home old_oracle_home
```
Where new_oracle_home is the complete path of the new Oracle9i Oracle home directory and old_oracle_home is the complete path of the old Oracle7 Oracle home directory.

For example, if your new Oracle9i Oracle home is /oracle/product/9.2 and your old Oracle7 Oracle home is /oracle/product/7.3, then enter the following:
```
migprep /oracle/product/9.2 /oracle/product/7.3
```
Change the following environment variables to point to the Oracle7 directories:
- ORACLE_HOME
- PATH
- LD_LIBRARY_PATH
  
  Make sure that LD_LIBRARY_PATH also points to the Oracle9i library path home directory.
- ORA_NLS32
  
  Note:
  For Oracle Parallel Server, perform this step on all nodes.
Set the ORA_NLS33 environment variable to the following directory in your Oracle7 environment:
```
$ORACLE_HOME/migrate/nls/admin/data
```
Make sure you have enough space in the SYSTEM tablespace (optional).

A common upgrade problem is running out of space in the SYSTEM tablespace during an upgrade. The MIG utility will not complete the upgrade unless sufficient space is allocated in the SYSTEM tablespace. To determine disk space requirements for a successful upgrade, run the MIG utility with the CHECK_ONLY command-line option set to true by entering the following at a system prompt:
```
mig CHECK_ONLY = true
```
The CHECK_ONLY command-line option causes the MIG utility to assess the amount of disk space required for the upgrade, check the amount of space available, and issue an informational message about the disk space requirements. When the CHECK_ONLY command-line option is set to true, the MIG utility does not build the Oracle9i data dictionary or perform any other upgrade processing.

If the CHECK_ONLY command-line option shows that you need to add more space to the SYSTEM tablespace, then you should add the amount specified by the CHECK_ONLY option plus an additional 25 megabytes. The additional 25 megabytes approximates the amount of space required by the upgrade script that you will run later in the upgrade process.

Enter the following to run the MIG utility:

mig

The command is mig unless stated otherwise in your operating system-specific Oracle documentation. Enter mig alone to run with a default set of options, or enter mig followed by one or more selected options.

See Also:

"Review MIG Utility Command-Line Options" for information about command-line options. Oracle Corporation recommends using the SPOOL option, because it makes it easier to check your results when the upgrade is complete.

Run the MIG Utility on Windows Platforms

Complete the following steps to run the MIG utility on a Windows platform:

In the new Oracle9i Oracle home, run the MIG utility by entering the MIG utility command at a command prompt with the PFILE option included:

C:\> mig PFILE=ORACLE7_HOME\DATABASE\INIT_PARAM_FILE

Replace the ORACLE7_HOME variable with the complete path to the Oracle7 Oracle home directory. Also, replace the INIT_PARAM_FILE variable with the full name of the initialization parameter file for the Oracle7 database.

For example, if ORACLE7_HOME is C:\ORANT and INIT_PARAM_FILE is INITORCL.ORA, then enter the following:

C:\> mig PFILE=C:\ORANT\DATABASE\INITORCL.ORA

You can enter mig with just the PFILE option to run with a default set of options, or you can enter mig followed by more selected options.

See Also:

If the Oracle7 Password appears when you run the MIG utility, then enter the password for the user logged in with SYSDBA privileges on the Oracle7 database. This prompt appears because the DBA_AUTHORIZATION registry parameter is set improperly or is not set at all.

Check the MIG Utility Results

Check the results after running the MIG utility. The MIG utility generates informational messages and echoes its progress as it runs the migrate.bsq script. If the MIG utility exits with an ORA- error, then check "Troubleshooting MIG Utility Errors" for information about the error and the actions to perform to resolve the problem.

The MIG utility creates a convert file that contains the information of the Oracle7 control file. Later in the upgrade process, the convert file is used by ALTER DATABASE CONVERT to create a new control file in Oracle9i.

The name and location of the convert file are operating system-specific. For example, on a UNIX operating system, the default location is ORACLE_HOME/dbs in the Oracle7 environment, and the default filename in this directory is convsid.dbf, where sid is your Oracle7 instance ID. On Windows platforms, the default location is ORACLE_HOME\rdbms in the Oracle9i environment, and the default filename in this directory is convert.ora.

Caution:

Do not open the Oracle7 database, which was shut down by the Oracle9i MIG utility. To ensure datafile version integrity, the SCNs in the dictionary, the convert file, and file header must all be consistent when the database is converted to Oracle9i. If the Oracle7 database is opened after running the MIG utility, then the SCN check will fail when the database is converted to Oracle9i, and the following error will be displayed:

ORA-01211: Oracle7 datafile is not from migration to Oracle8

Therefore, if the Oracle7 database is opened, then you must rerun the MIG utility.

Preserve the Oracle7 Database

After you successfully run the MIG utility, perform a cold backup of the Oracle7 database. This backup serves the following purposes:

If you wish to return to the Oracle7 database after executing the ALTER DATABASE CONVERT statement in Oracle9i, then you can restore the backup, start the Oracle7 database, and complete the procedure in "Abandoning the Oracle7 Upgrade".
It can be used as the first Oracle9i backup for an Oracle9i recovery.
If an error occurs at Oracle9i database convert time (ALTER DATABASE CONVERT or ALTER DATABASE OPEN RESETLOGS MIGRATE), then you can restore this backup, fix the problems, and continue the conversion process. However, if you restore a backup that was performed before you ran the MIG utility, then you must rerun the MIG utility.

See Also:
Oracle7 Server Administrator's Guide for information about performing backup and restore operations on your Oracle7 database

In addition, perform a backup of the entire Oracle7 software distribution, including the Oracle7 home directory. Make sure the backup includes the following:

All of the subdirectories
Control files
Datafiles and online redo log files (in case any datafiles in the Oracle7 database are lost or unreadable), although these files should not contain any outstanding redo information.
Parameter files
Convert file
Scripts that create objects in the Oracle7 database
Scripts that could restore the original database, if necessary

MIG Utility Messages

The MIG utility may return error messages and informational messages during the upgrade process. This section describes errors you may encounter when using the MIG utility. For each error, a description of its probable causes and instructions for corrective action are provided. Informational messages are also listed, but they require no corrective action.

If you are using the Database Upgrade Assistant, then the MIG utility messages are recorded in a log file. See the online help for the Database Upgrade Assistant for information about accessing its log files. Also, if you are using the Database Upgrade Assistant and the recommended action for a message includes rerunning the MIG utility, then you should rerun the Database Upgrade Assistant.

The following messages are listed in alphabetical order:

cannot reduce file number bits in DBA during migration: Cause: The MIG utility attempted to reduce the number of file-number bits used in a datablock address.; Action: Contact Oracle Support Services.
cannot create conversion file, records exceed number bytes: Cause: An internal error occurred. A valid convert file could not be created from the Oracle7 control file.; Action: Check the Oracle7 control file for corruption, fix any problems, and rerun the MIG utility.
CHECK_ONLY - estimate V8 catalog space requirement ONLY (default=FALSE): Cause: This is an informational message about the CHECK_ONLY command-line argument.; Action: No user action is required.
CHECK_ONLY and NO_SPACE_CHECK are mutually exclusive options: Cause: These two mutually exclusive command-line options were passed to the MIG utility.; Action: Rerun the MIG utility using only one of these options.
client nls_characterset does not match server nls_characterset - check that NLS_LANG environment variable is set: Cause: The NLS_LANG character set does not match the character set in PROPS$.; Action: Check the database character set in PROPS$ and set the NLS_LANG environment variable to match it.
command-line argument value must be TRUE or FALSE (string): Cause: A command-line argument was entered 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> (string): Cause: A command-line argument was used improperly.; Action: Check the syntax of the command-line argument, correct the statement, and retry the operation.
command-line arguments:: Cause: This informational message displays the command-line arguments.; Action: No user action is required.
command name not found (string): Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
command not of form CMD(ARG1, ARG2, ...): Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
copy long command must be of form COPYLONG(U1,T1,C1,U2,T2,C2,K1<,K2>): Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
could not find a single contiguous extent of number bytes for c_file#_block#: Cause: Your SYSTEM tablespace does not have enough contiguous space.; Action: Add free space to your SYSTEM tablespace and rerun the MIG utility.
could not find a single contiguous extent of number bytes for c_ts#: Cause: Your SYSTEM tablespace does not have enough contiguous space.; Action: Add free space to your SYSTEM tablespace and rerun the MIG utility.
could not find a single contiguous extent of number bytes for i_file#_block#: Cause: Your SYSTEM tablespace does not have enough contiguous space.; Action: Add free space to your SYSTEM tablespace and rerun the MIG utility.
could not find a single contiguous extent of number bytes for i_ts#: Cause: Your SYSTEM tablespace does not have enough contiguous space.; Action: Add free space to your SYSTEM tablespace and rerun the MIG utility.
could not translate logical name string: Cause: An internal error has occurred.; Action: Check that the logical name is defined correctly and rerun the MIG utility.
current version: string -- database must be release 7.1 or later: Cause: The current database is a release earlier than release 7.1.; Action: Migrate the current database to a release supported by the MIG utility on your operating system. Then, rerun the MIG utility. See your operating system-specific Oracle documentation for information about the releases supported by the MIG utility on your operating system.
data type must be long for column string: Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
datafile is found in inconsistent state (internal error) -- string: Cause: An internal error occurred; a datafile was found in an inconsistent state.; Action: Contact Oracle Support Services.
datafile is offline while tablespace is online - apply media recovery and bring datafile online before migration -- string: Cause: The datafile in a tablespace is offline while the tablespace is online. The MIG utility cannot proceed until the datafile and tablespace are both either online or offline normal.; Action: Apply media recovery and bring the datafile online before rerunning the MIG utility.
DBNAME - current database name (db_name in init.ora): Cause: This is an informational message about the DBNAME command-line argument.; Action: No user action is required.
dictionary constant not found - string: Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
entries found in system.def$_call, def$_calldest, or def$_error - push all deferred transactions before migration: Cause: Entries exist in SYSTEM.DEF$_CALL, DEF$_CALLDEST, or DEF$_ERROR.; Action: If entries are in SYSTEM.DEF$_CALL, then push all deferred transactions until SYSTEM.DEF$_CALL is empty. If entries are in SYSTEM.DEF$_ERROR, then resolve and re-execute any errors in the local queue until it is empty. Rerun the MIG utility.
error calling slgtd: Cause: Error in getting current time from slgtd, an internal error. The MIG utility may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
error closing file string: 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 rerun the MIG utility.
estimated space requirement for string is number blocks: Cause: In this informational message, the MIG utility displays the space required for the object.; Action: No user action is required.
file number is too large for DBA conversion: Cause: An internal error has occurred; the specified file is too large for DBA conversion.; Action: Contact Oracle Support Services.
file header does not fit in number bytes: Cause: An internal error has occurred.; Action: Check the control file for corruption, fix any problems, and rerun the MIG utility.
fixed portion of control file does not fit in number bytes: Cause: An internal error has occurred.; Action: Check the control file for corruption, fix any problems, and rerun the MIG utility.
found NULL SQL statement: Cause: An internal error has occurred; the migrate.bsq script may be corrupted.; Action: Check that the version of the MIG utility, of migrate.bsq, and of the Oracle9i software are compatible, and that no corruption exists in migrate.bsq. Fix any problems and rerun the MIG utility.
free space found in system tablespace is number blocks: Cause: This informational message shows the amount of free space in the SYSTEM tablespace.; Action: No user action is required.
free space found: number: Cause: This informational message shows the amount of free space in the SYSTEM tablespace.; Action: No user action is required.
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 rerun the MIG utility.
insufficient space for new dictionaries, number bytes needed, number 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 MIG utility.
invalid NLS_NCHAR value specified: Cause: The NLS_NCHAR value specified in the command line is invalid.; Action: Correct the NLS_NCHAR value specified in the command line and rerun the MIG utility.
migration can't proceed - database blocksize number is less than Oracle9i's minimum block size 2 KB: Cause: The existing database blocksize is less than 2 KB.; Action: Make sure the block size of the Oracle7 database is at least 2 KB. You may consider rebuilding the Oracle7 database. Then, rerun the MIG utility.
migration can't proceed with datafile online while tablespace offline -- string: Cause: The datafile in a tablespace is online while the tablespace is offline. Migration cannot proceed until the datafile and tablespace are both either online or offline normal.; Action: Make sure the online status of the datafile is the same as the online status of the tablespace. Then, rerun the MIG utility.
migration cannot proceed with active transactions or offline tablespaces with outstanding undo: Cause: One or more tablespaces were offline with outstanding save undo when the MIG utility attempted to upgrade the database.; Action: See Step 5 and make sure all offline tablespaces have been taken offline cleanly. Then, rerun the MIG utility.
mounting database ...: Cause: This is an informational message. The MIG utility is mounting the Oracle7 database.; Action: No user action is required.
MULTIPLIER - seg$/uet$ cluster index size increase factor (default=15): Cause: This is an informational message that the MIG utility displays about the MULTIPLIER command-line setting.; Action: No user action is required.
MULTIPLIER value must be at least 2: Cause: The MULTIPLIER value, which specifies the initial size of the Oracle9i i_file#_block# in the command line, is less than 2.; Action: Change the MULTIPLIER value to be greater than or equal to 2, and rerun the MIG utility.
NEW_DBNAME string too long - maximum length is 8 characters: Cause: The specified new database name is more than 8 characters long.; Action: Change the specified name for the new database to 8 or fewer characters, and rerun the MIG utility.
NEW_DBNAME - new name for the database (max. 8 characters): Cause: This informational message displays information about the NEW_DBNAME command-line argument.; Action: No user action is required.
NLS_NCHAR - specify the nchar characterset value: Cause: This informational message displays information about the NLS_NCHAR command-line argument.; Action: No user action is required.
NO_SPACE_CHECK - do not execute the space check (default=FALSE): Cause: This is an informational message about the NO_SPACE_CHECK command-line argument.; Action: No user action is required, but make sure there is adequate space before you run the MIG utility with this option.
string number being processed is incorrect during creating convert file: Cause: An internal error occurred while creating the convert file.; Action: Contact Oracle Support Services.
opening database ...: Cause: This is an informational message. The MIG utility is opening the Oracle7 database.; Action: No user action is required.
ORA_NLS33 environment variable is not set or incorrectly set: Cause: The ORA_NLS33 environment variable does not point to the NLS datafiles.; Action: Set the ORA_NLS33 environment variable to point to the correct files and rerun the MIG utility.
ORA-number:: Cause: The MIG utility has received an ORA- error and cannot retrieve the message text for the error.; Action: Take appropriate action based on the Oracle error number (see Oracle9i Database Error Messages).
parameter buffer overflow: Cause: The initialization parameter file is too large to fit in the buffer.; Action: Reduce the size of the initialization parameter file, possibly by removing any obsolete parameters. Then, rerun the MIG utility.
parameter file exceeds number bytes: Cause: The initialization parameter file for your Oracle7 database exceeds the maximum size.; Action: If possible, reduce the size of your initialization parameter file by removing obsolete parameters. Otherwise, contact Oracle Support Services.
PFILE - use alternate 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 string: Cause: An internal error has occurred reading the specified file.; Action: Make sure the file and disk are not corrupted. Fix any corruption before you rerun the MIG utility.
short read, number bytes requested, number bytes read: Cause: There was a problem reading the control file.; Action: Check the control file for corruption, fix any problems, and rerun the MIG utility.
shut down database (abort) ...: Cause: An internal error has occurred.; Action: Additional error messages should inform you of the cause of the shutdown. Follow the actions suggested for these additional messages.
shutting down database ...: Cause: This is an informational message. The MIG utility is shutting down the Oracle7 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 MIG utility is starting up an Oracle7 instance.; Action: No user action is required.
string argument too long, maximum length number: Cause: A string in the command-line argument passed to the MIG utility exceeds the maximum size.; Action: Shorten the string in the command-line argument and rerun the MIG utility.
Tablespace of datafile not taken offline normal. Bring tablespace online, offline normal, or drop before migration -- string: Cause: A tablespace was taken offline using IMMEDIATE or TEMPORARY.; Action: Either bring the tablespace online and then take it offline using NORMAL, or drop the tablespace. Then, rerun the MIG utility.
too many args in command (number max): Cause: Too many arguments were specified on the command line.; Action: Check the syntax of the command and specify fewer command-line options.
unable to allocate buffer space to copy longs: Cause: The MIG utility could not allocate memory to serve as a buffer for copying LONG columns in the database.; Action: Make sure enough computer resources are available and rerun the MIG utility.
unable to open file string: Cause: An internal error has occurred, or a file was not in the expected location, when you started the MIG utility.; Action: Check that the file exists and that its access permissions allow Oracle to open and read it. If possible, check that the file, and the disks on which the file reside, are not corrupt. Fix any problems and rerun the MIG utility.
unable to read file string: Cause: An internal error has occurred or a file was not in the expected location when you started the MIG utility.; Action: Check that the file exists and that its access permissions allow Oracle to open and read it. If possible, check that the file, and the disks on which the file reside, are not corrupt. Fix any problems and rerun the MIG utility.
unable to write file string: Cause: An internal error has occurred.; Action: Check the access permissions to make sure that Oracle can write to the file. Check that the disks to which the file is being written are not corrupt. Fix any corruption; then, rerun the MIG utility.
V8 catalog space requirement: number: Cause: This is an informational message that shows the amount of additional space required in your SYSTEM tablespace to successfully run the MIG utility.; Action: Make sure you have the specified amount of additional space before running the MIG utility.

Troubleshooting MIG Utility Errors

Errors may be caused by the following actions or omissions:

Performing an upgrade step out of order
Failing to fulfill the prerequisites for the upgrade
Encountering an occasional conversion irregularity

Problems Using the MIG Utility

General upgrade problems may occur when you run the MIG utility, but they are caused by your database system's configuration. While the MIG utility is performing the necessary actions to upgrade the Oracle7 database, an error is generated by your Oracle software. Typically, when such an error occurs, the MIG utility stops and displays one or more error messages.

If you encounter one of the following problems when you run the MIG utility, then perform the suggested actions, and then rerun the MIG utility.

Insufficient Space in the SYSTEM Tablespace

This problem may return an error message similar to the following:

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS by 473 in tablespace SYSTEM

You need to add a new datafile to the SYSTEM tablespace and allocate enough space to the new datafile to successfully complete the upgrade.

It is also possible to run out of space in the temporary tablespace during the upgrade. If you do, then add a new datafile to the temporary tablespace and allocate enough space to the new datafile to successfully complete the upgrade.

See Also:

"Space Requirements" and Step 5 for more information about the space requirements for the SYSTEM tablespace, and information about adding a new datafile to increase its available space.

Incorrect AUDIT_TRAIL Parameter Setting

This problem may return error messages similar to the following:

ORA-00604: error occurred at recursive SQL level string
ORA-01552: cannot use system rollback segment for non-system tablespace 
'string'
ORA-02002: error while writing to audit trail

You will encounter these errors only under the following conditions:

The AUDIT_TRAIL initialization parameter is set to either DB or to TRUE
The SYS.AUD$ table is located in a tablespace other than SYSTEM

To correct this problem, complete the following steps:

Shut down the database if it is running.
Set the AUDIT_TRAIL initialization parameter in the initialization parameter file in the following way:
```
AUDIT_TRAIL = NONE
```
Rerun the MIG utility.

OPTIMAL Setting for the SYSTEM Rollback Segment

This problem may return error messages similar to the following:

ORA-01562: failed to extend rollback segment number 0
ORA-01628: max # extents (n) reached for rollback segment SYSTEM

These messages indicate that the SYSTEM rollback segment is too small to complete the upgrade. You must ensure that the SYSTEM rollback segment is large enough for the upgrade to complete successfully.

Both the MIG utility and the Database Upgrade Assistant take all non-SYSTEM rollback segments offline and then freeze the size of the SYSTEM rollback segment by altering MAXEXTENTS to the number of extents currently allocated. This action prevents any space operations, such as an extent allocation, while the MIG utility or the Database Upgrade Assistant handles the space management tables.

If the SYSTEM rollback segment has an OPTIMAL setting, then extents are deallocated dynamically when their data is no longer needed for active transactions. The dynamic deallocation may cause the number of currently allocated extents to be small when the SYSTEM rollback segment is frozen. Therefore, the SYSTEM rollback segment may not be large enough to handle the transactions involving the space management tables during the upgrade.

The solution is to change the following settings:

Turn off the OPTIMAL setting for rollback segment.
Double the NEXT EXTENT of the System Rollback Segment.
Double the MULTIPLIER value.
Add space to the system tablespace to make sure there is enough free space to handle undo segment (at least 50 MB).

See Also:
If you are using the MIG utility, then see Step 7 for information on checking your OPTIMAL setting and resetting it if necessary.

Small MULTIPLIER Option Setting

This problem may return an error message similar to the following:

ORA-01632: max # extents (%s) reached in index %s.%s

The MIG utility is using the default value of 15 for the MULTIPLIER option, and this value is too low. To correct the problem, increase the value of the MULTIPLIER option.

If you are using the MIG utility, then, when you run it from the command line, enter the following to raise the MULTIPLIER option to 30:

mig MULTIPLIER=30

If, however, you are running the MIG utility in the background by using the Database Upgrade Assistant, then restore the backup of the database being upgraded and then rerun the Database Upgrade Assistant. Choose the Custom migration option in the Database Upgrade Assistant. When you are prompted for the MULTIPLIER value, enter a value greater than the default of 15.

See Also:

"Review MIG Utility Command-Line Options" for more information about the MULTIPLIER option

Problems at the ALTER DATABASE CONVERT Statement

You may encounter one of the problems described in this section when you issue the ALTER DATABASE CONVERT statement during the upgrade process after you run the MIG utility. Typically, the conversion will stop and one or more error messages will be displayed. If you encounter one of the following problems when you issue the ALTER DATABASE CONVERT statement, then perform the suggested actions to correct the problem.

Oracle7 Control Files Exist

This problem may return the following error messages:

ORA-00200: cannot create control file name
ORA-00202: controlfile: name
ORA-27038: skgfrcre: file exists

The old Oracle7 control files must be renamed or removed before you issue the ALTER DATABASE CONVERT statement.

See Also:

Step b

Database Started in Mode Other Than NOMOUNT

This problem may return the following error messages:

ORA-00227: corrupt block detected in controlfile: (block num, # blocks num)
ORA-00202: control file: 'name'

The old Oracle7 control files must be renamed or removed before you issue the ALTER DATABASE CONVERT statement. Also, the database must be started in NOMOUNT mode when you issue the ALTER DATABASE CONVERT statement. This error indicates that the database was started in a mode other than NOMOUNT.

See Also:

Step b and Step 12

Convert File Not Found

This problem may return the following error messages:

ORA-00404: convert file not found: name
ORA-27037: unable to obtain file status

The convert file (convsid.dbf on UNIX and convert.ora on Windows platforms) generated by the MIG utility was not found in the expected location. On UNIX, the expected location is the ORACLE_HOME/dbs directory in the Oracle9i environment; on Windows platforms, the expected location is the ORACLE_HOME\rdbms directory in the Oracle9i environment. The convert file must be moved to this location before you issue the ALTER DATABASE CONVERT statement.

See Also:

Step 3

REMOTE_LOGIN_PASSWORDFILE Initialization Parameter Set to EXCLUSIVE

This problem may return the following error message:

ORA-00600: internal error code, arguments: [kzsrsdn: 1], [32]

You will encounter this error under the following conditions:

Your database is using a password file, and the password file was not moved to the correct directory. On UNIX, the correct directory is ORACLE_HOME/dbs in the Oracle9i environment; on Windows platforms, the correct directory is ORACLE_HOME\database in the Oracle9i environment.
The REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to EXCLUSIVE in the initialization parameter file.

To continue with the upgrade, complete the following steps:

Shut down the database.
Set REMOTE_LOGIN_PASSWORDFILE to NONE in the initialization parameter file:
```
REMOTE_LOGIN_PASSWORDFILE = NONE
```
Startup mount the database by entering the following SQL statement:
```
SQL> STARTUP MOUNT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Issue the ALTER DATABASE OPEN RESETLOGS MIGRATE statement:
```
SQL> ALTER DATABASE OPEN RESETLOGS MIGRATE;
```
Continue with the upgrade process starting with Step 13 .

You cannot use the existing password file because it is no longer valid. If you want to use a password file with Oracle9i, then re-create the password file and repopulate it with users. Remember to set REMOTE_LOGIN_PASSWORDFILE correctly.

Database Name Mismatch

This problem may return the following error message:

ORA-01103: database name 'name' in controlfile is not 'name'

There is a mismatch in the database name. This mismatch is in one or more of the following places:

The database name specified by the DB_NAME initialization parameter in the initialization parameter file does not match the database name in the convsid.dbf filename.
The Oracle9i instance ID set by the ORACLE_SID environment variable does not match the database name in the convsid.dbf filename.

Note:
This problem only occurs on UNIX operating systems. It does not apply to Windows platforms.

To correct the problem, make sure the correct database name is specified in each of the following places:

The ORACLE_SID environment variable
The DB_NAME initialization parameter in the initialization parameter file
The sid part of the convsid.dbf filename

For example, if your ORACLE_SID environment variable and the DB_NAME initialization parameter in the initialization parameter file are both set to DB1, then the convsid.dbf filename should be the following:

convDB1.dbf

Rerunning the ALTER DATABASE CONVERT Statement

This problem may return the following error messages:

ORA-01122: datafile name - failed verification check 
ORA-01110: data file name: str
ORA-01202: wrong incarnation of this file - wrong creation time

These errors usually indicate that the ALTER DATABASE CONVERT statement was issued previously but failed. If you encounter these errors, then you can attempt to move on to the next step in the upgrade process by issuing the ALTER DATABASE OPEN RESETLOGS MIGRATE statement. However, if you encounter problems, then restore the backup you created before you started the upgrade process, and use it to start the upgrade again from the beginning. Start at the beginning of Chapter 3, but make sure you performed the pre-upgrade actions described in Chapter 2 and in this appendix.

Datafile Version Integrity Problem

This problem may return the following error messages:

ORA-01122: datafile name - failed verification check 
ORA-01110: data file name: str
ORA-01211: Oracle7 data file is not from migration to Oracle9i

The MIG utility must be the last utility to access the database in the Oracle7 environment. The datafile specified in the error messages is either a backup taken before you ran the MIG utility, or the database was opened by Oracle7 after you ran the MIG utility. Only the datafiles that were current when the MIG utility ran can be accessed by Oracle9i.

To ensure datafile version integrity, the system change numbers (SCNs) in the data dictionary, the convert file, and the file headers must all be consistent when the database is converted to Oracle9i. If the database is opened under Oracle7 after the MIG utility has run, then the SCN checking fails when you issue the ALTER DATABASE CONVERT statement.

To correct the problem, complete the following steps:

Shut down the database.
Rename the control files created by ALTER DATABASE CONVERT to different file names.
Restore the saved copy of Oracle7 control files from immediately before the issuing of the STARTUP NOMOUNT statement.

If you do not have the Oracle7 control files saved, then restore the backup you made prior to starting the migration process.
Start the migration process over from the beginning, ensuring the database is not opened in the Oracle7 environment after the MIG utility completes. Start from the beginning of Chapter 3.

Abandoning the Oracle7 Upgrade

If you performed a backup of your Oracle7 database before running the MIG utility, then the easiest way to abandon an upgrade is to restore that backup. However, if you do not have a backup, or if you made the backup after running the MIG utility, then you must complete the procedure described in this section to abandon the upgrade.

You can run the Oracle9i MIG utility multiple times and still return to the Oracle7 database. However, running the MIG utility automatically eliminates the Oracle7 database catalog views. Therefore, to return to the Oracle7 database after running the MIG utility, you must run the Oracle7 catalog.sql script to restore the Oracle7 database catalog views.

Note:

You cannot use the following procedure to abandon the upgrade if you have already executed the ALTER DATABASE CONVERT statement. If you have executed this statement and want to return to Oracle7, then complete the downgrade procedure in Chapter 8, "Database Migration Using Export/Import".

To abandon the upgrade, you generally must restore the Oracle7 database by completing the following steps in the Oracle7 environment:

Start the Oracle7 database using Server Manager.
Drop the MIGRATE user:
```
DROP USER MIGRATE CASCADE;
```
Rerun catalog.sql and catproc.sql:
```
@catalog.sql
@catproc.sql
```
Run catsvrmg.sql:
```
@catsvrmg.sql
```
If Oracle Parallel Server is installed, then run catparr.sql:
```
@catparr.sql
```
If Oracle Replication is installed, then run catrep.sql:
```
@catrep.sql
```

Migration Issues for Physical Rowids

Release 8.0 introduced new internal and external formats for physical rowids that enable you to use some new release 8.0 and higher features, including partitioning and global indexes.

This section includes the following topics:

Upgrading Applications and Migrating Data
The DBMS_ROWID Package
Snapshot Refresh
Oracle7 Client Compatibility Issues

ROWID Migration and Compatibility Issues

Note:

In the rest of this section, references to new rowids include rowid functionality that was introduced in release 8.0. Also, the word "rowid" means "physical rowid". This appendix does not discuss the UROWID (universal rowid) datatype. See Chapter 5, "Compatibility and Interoperability" for compatibility issues relating to the UROWID datatype.

Upgrading Applications and Migrating Data

Rowids can be stored in columns of ROWID datatype and in columns of character type. Stored Oracle7 rowids become invalid after an upgrade to Oracle9i. Therefore, stored Oracle7 rowids must be converted to the new format.

Applications that do not attempt to manually assemble and disassemble rowids do not need to be changed or recompiled because the new rowids fit the current storage requirements for host variables.

Applications that attempt to manufacture or analyze the contents of rowids must use the DBMS_ROWID package to deal with the format and contents of the new rowids. This package contains functions that extract the information that was available directly from an Oracle7 rowid (including file and block address), plus the data object number.

The columns that contain rowid values (in ROWID datatype format or in character format) must be migrated if they point to tables that were upgraded to Oracle9i. Otherwise, it will not be possible to retrieve any rows using their stored values. On the other hand, if the rowid values stored in the upgraded tables still point to Oracle7 tables, then you do not need to migrate the columns.

Columns are migrated in two stages: definition migration and data migration. The column definition is adjusted automatically during the upgrade to Oracle9i. The maximum size of rowid user columns is increased to the size of the extended disk rowids, changing the LENGTH column of COL$ for rowid columns from six to ten bytes.

The data migration can be performed only after the system has been opened in Oracle9i. You can upgrade different tables at different times or multiple tables in parallel. Make sure the upgrade is done before the Oracle7 database file limit is exceeded, thereby guarding against the creation of ambiguous block addresses.

You can use existing rowid refresh procedures that are available at your installation, or the DBMS_ROWID functionality, to migrate stored rowids from Oracle7 format to the new format.

Data migration by the MIG utility or the Database Upgrade Assistant applies only to rowids stored in a user-defined column. All system-stored rowids (such as in indexes) remain valid after the upgrade, and do not require specific actions to be migrated. Also, indexes are not invalidated because, during the upgrade to Oracle9i, indexes can continue to use the restricted ROWID datatype format.

Note:

Importing a column containing rowids should produce a message indicating that special attention might be required to re-establish the validity of the rowids. Special attention is necessary for all rowids being imported. Thus, database migration by Export/Import requires special attention for every column containing rowids (not just for user-defined columns).

The DBMS_ROWID Package

The DBMS_ROWID package contains the following functionality:

Creation and interpretation of rowids in both the Oracle7 format and in the new format
Conversion between Oracle7 rowids and new rowids

Migration of the stored rowids can be accomplished using conversion functions, as described in the following sections.

Rowid Conversion Types

You must specify the type of rowid being converted, because the rowid conversion functions perform the conversion differently depending on whether the rowid is stored in the user column of ROWID datatype, or in the user column of CHAR or VARCHAR2 datatype.

For a column of ROWID datatype, the caller of the conversion procedures must pass the following value as a procedure parameter:

rowid_convert_internal constant integer := 0;

For a column of CHAR or VARCHAR2 datatype, the caller of the conversion procedures must pass the following value as a procedure parameter:

rowid_convert_external constant integer := 1;

Rowid Conversion Functions

The following functions perform the rowid conversion:

ROWID_TO_EXTENDED converts a rowid from the Oracle7 (restricted) format to the new (extended) format.
ROWID_TO_RESTRICTED converts a rowid from the new (extended) format to the Oracle7 (restricted) format.
ROWID_VERIFY checks whether a given rowid can be converted from Oracle7 format to the new format.

The following sections contain detailed information about the ROWID_TO_EXTENDED and ROWID_VERIFY procedures.

The ROWID_TO_EXTENDED Conversion Procedure

ROWID_TO_EXTENDED uses the following parameters:

Rowid - specifies the rowid to be converted (in External Character format).
Schema Name - specifies the schema name of the table that contains a row whose rowid will be converted to the extended format.
Table Name - specifies the table name of the table that contains a row whose rowid will be converted to the extended format.
Conversion Type - specifies the type of rowid being converted.

See Also:
"Rowid Conversion Types" for more information

ROWID_TO_EXTENDED returns a new (extended) rowid in External Character format, and its parameters are interpreted in the following way:

If the schema name and table name for the target table are not specified (null), then ROWID_TO_EXTENDED attempts to fetch the page specified by the rowid to be converted. It will treat the file number stored in this rowid as the absolute file number, which can cause problems if the file has been dropped and its number has been reused prior to the migration. If the fetched page belongs to a valid table, then the rowid will be converted to an extended format using the Data Object ID of this table, but this conversion is very inefficient, and is only recommended as a last resort, when the target table is not known. You still must know the correct table name when using the converted value.
If the schema name and table name are given (a preferred approach), then ROWID_TO_EXTENDED will verify SELECT authority on the table and convert the rowid to an extended format using the Data Object Number of this table. There is no guarantee that the converted rowid actually references a real row in this table, neither at the time of conversion nor at the time when the rowid is used.
If a null value is supplied for the rowid, then the procedure ignores the table specification and returns a null value.
If a value of 0, or, more generally, <n>0.<m>0.<p>0 is supplied for rowid, then the table name is ignored and a restricted rowid of the form 00000000.0000.0000 is returned.
If a new rowid is supplied, then the data object in the rowid is verified against the actual data object number (which depends on the table name specification). If these two numbers do not match, then the INVALID ROWID error appears; otherwise, the original rowid is returned.

ROWID_VERIFY

A rowid verification procedure, ROWID_VERIFY, is provided. This procedure uses the same parameters as ROWID_TO_EXTENDED and returns 0 if the rowid can be converted successfully to extended format; otherwise, it returns 1.

However, ROWID_VERIFY returns security violation errors, or an "object not found" error, if the user does not have SELECT authority on the underlying table, or if the table does not exist. ROWID_VERIFY can be used to identify bad rowids prior to migration using the ROWID_TO_EXTENDED procedure.

Conversion Procedure Examples

The following are examples of conversion procedures for rowids:

Example 1

Assume a table scott.t contains a column c of ROWID datatype format. All these rowids reference a single table, scott.t1.

The values of column c can be converted to extended format using the following statement:

UPDATE scott.t SET c = DBMS_ROWID.ROWID_TO_EXTENDED(c, 'scott', 't1', 0);

Example 2

In a more general situation, rowids stored in column c may reference different tables, but the table name can be found based on the values of some other columns in the same row. For example, assume that the column tname of the table t contains a name of the table which is referenced by a rowid from column c.

In this case, the values in column c can be converted to extended format using the following statement:

UPDATE scott.t SET c = DBMS_ROWID.ROWID_TO_EXTENDED(c, 'scott', tname, 0);

Example 3

You can use the ROWID_TO_EXTENDED function in the CREATE ... AS SELECT statement. This use may be desirable in some cases because conversion can increase the size of the user column of ROWID datatype (typically from 6 bytes to 10 bytes, although this depends on a specific port) which may create indirect rows.

In this case, CREATE ... AS SELECT may be a better choice than UPDATE:

CREATE TABLE scott.tnew (a, b, c)
    AS SELECT a, b, DBMS_ROWID.ROWID_TO_EXTENDED(c, 'scott', 't1', 0) FROM 
scott.t;

Example 4

If the target table for rowids stored in column c is not known, then conversion can be accomplished using the following statement:

UPDATE scott.t SET c = DBMS_ROWID.ROWID_TO_EXTENDED(c, NULL, NULL, 0);

Example 5

The following SQL statement may be used to find bad rowids prior to conversion:

SELECT ROWID,c FROM scott.t WHERE DBMS_ROWID.ROWID_VERIFY(c, NULL, NULL, 0) = 
1;

Snapshot Refresh

The new ROWID datatype format forces all rowid snapshots to perform a complete refresh when both master and snapshot sites are upgraded to Oracle9i.

See Also:

Appendix E, "Database Migration and Compatibility for Replication Environments" for more information about replication compatibility

Oracle7 Client Compatibility Issues

Oracle7 clients can access a release 8.0 or higher database, and release 8.0 and higher clients can access an Oracle7 database. Binary and character values of the pseudo column ROWID and of columns of datatype ROWID that are returned by an Oracle7 database to a release 8.0 and higher database are always in restricted format, because Oracle7 cannot recognize the extended format ROWID.

The DBMS_ROWID package can be used for interpreting the contents of Oracle7 rowids and for creating the rowids in Oracle7 format.

An Oracle7 client accessing a release 8.0 and higher database receives the rowid in the new (extended) format. Therefore, the client cannot interpret the contents of the new rowids.

ROWID Migration and Compatibility Issues

For backward compatibility, the restricted form of the ROWID is still supported. These ROWIDs exist in massive amounts of Oracle7 data, and the extended form of the ROWID is required only in global indexes on partitioned tables. New tables always get extended ROWIDs.

It is possible for an Oracle7 client to access a release 8.0 and higher database. Similarly, a release 8.0 and higher client can access an Oracle7 Server. A client in this sense can include a remote database accessing a server using database links, as well as a client 3GL or 4GL application accessing a server.

There is more information on the ROWID_TO_EXTENDED function in the Oracle9i Supplied PL/SQL Packages and Types Reference.

Accessing an Oracle7 Database from a Release 8.0 and Higher Client

The ROWID values that are returned are always restricted ROWIDs. Also, ROWID values returned to an Oracle7 server are always restricted ROWIDs.

The following ROWID functionality works when accessing an Oracle7 Server:

Selecting a ROWID and using the obtained value in a WHERE clause
WHERE CURRENT OF cursor operations
Storing ROWIDs in user columns of ROWID or CHAR type
Interpreting ROWIDs using the hexadecimal encoding (not recommended, use the DBMS_ROWID functions)

Accessing a Release 8.0 or Higher Database from an Oracle7 Client

Release 8.0 and higher returns ROWIDs in the extended format. This means that you can only:

Select a ROWID and use it in a WHERE clause.
Use WHERE CURRENT OF cursor operations.
Store ROWIDs in user columns of CHAR(18) datatype.

Export/Import

It is not possible for an Oracle7 client to import a release 8.0 or higher table that has a ROWID column (not the ROWID pseudocolumn), if any row of the table contains an extended ROWID value.

Changes to Initialization Parameters and the Data Dictionary in Release 8.0

This section lists changes to initialization parameters and the data dictionary in release 8.0.

Initialization Parameter Changes in Release 8.0

The following sections list changes to initialization parameters in release 8.0.

Renamed Initialization Parameters in Release 8.0

The initialization parameters listed in Table D-1 were renamed in release 8.0:

Table D-1 Initialization Parameters Renamed in Release 8.0

Pre-Release 8.0 Name	Release 8.0 and Higher Name
`ASYNC_READ`	`DISK_ASYNCH_IO`
`ASYNC_WRITE`	`DISK_ASYNCH_IO`
`CCF_IO_SIZE` *	`DB_FILE_DIRECT_IO_COUNT` *
`DB_FILE_STANDBY_NAME_CONVERT`	`DB_FILE_NAME_CONVERT`
`DB_WRITERS`	`DBWR_IO_SLAVES`
`LOG_FILE_STANDBY_NAME_CONVERT`	`LOG_FILE_NAME_CONVERT`
`SNAPSHOT_REFRESH_INTERVAL`	`JOB_QUEUE_INTERVAL`

* The units are different for CCF_IO_SIZE (bytes) and DB_FILE_DIRECT_IO_COUNT (database blocks).

Initialization Parameters Obsolete in Release 8.0

The following initialization parameters were made obsolete in release 8.0:

`CHECKPOINT_PROCESS`	`FAST_CACHE_FLUSH`
`GC_DB_LOCKS`	`GC_FREELIST_GROUPS`
`GC_ROLLBACK_SEGMENTS`	`GC_SAVE_ROLLBACK_LOCKS`
`GC_SEGMENTS`	`GC_TABLESPACES`
`INIT_SQL_FILES`	`IO_TIMEOUT`
`IPQ_ADDRESS`	`IPQ_NET`
`LM_DOMAINS`	`LM_NON_FAULT_TOLERANT`
`MLS_LABEL_FORMAT`	`OPTIMIZER_PARALLEL_PASS`
`PARALLEL_DEFAULT_MAX_SCANS`	`PARALLEL_DEFAULT_SCAN_SIZE`
`POST_WAIT_DEVICE`	`SEQUENCE_CACHE_HASH_BUCKETS`
`UNLIMITED_ROLLBACK_SEGMENTS`	`USE_IPQ`
`USE_POST_WAIT_DRIVER`	`USE_READV`
`USE_SIGIO`	`V733_PLANS_ENABLED`

Note:

An attempt to start a release 9.2 database using one or more of these obsolete initialization parameters will result in an error, and the database will not start.

Static Data Dictionary View Changes in Release 8.0

The following sections list changes to static data dictionary views in release 8.0.

Static Data Dictionary Views Obsolete in Release 8.0

The following static data dictionary views were made obsolete in release 8.0:

`ALL_HISTOGRAMS`	`DBA_HISTOGRAMS`
`DEFCALL`	`USER_HISTOGRAMS`

D Upgrading an Oracle7 Database Using the MIG Utility

Overview of the MIG Utility

Outline of the Upgrade Process Using the MIG Utility

In the Oracle7 Environment

In the Oracle9i Environment

System Considerations and Requirements for Using the MIG Utility

Space Requirements

Block Size Considerations

Considerations for SQL*Net

Considerations for Replication Environments

Considerations for Migrating from ConText to Oracle Text

Distributed Database Considerations

Prepare the Oracle7 Database to be Upgraded

Review MIG Utility Command-Line Options

Run the MIG Utility

Run the MIG Utility on UNIX Operating Systems

Run the MIG Utility on Windows Platforms

Check the MIG Utility Results

Preserve the Oracle7 Database

MIG Utility Messages

Troubleshooting MIG Utility Errors

Problems Using the MIG Utility

Insufficient Space in the SYSTEM Tablespace

Incorrect AUDIT_TRAIL Parameter Setting

OPTIMAL Setting for the SYSTEM Rollback Segment

Small MULTIPLIER Option Setting

Problems at the ALTER DATABASE CONVERT Statement

Oracle7 Control Files Exist

Database Started in Mode Other Than NOMOUNT

Convert File Not Found

REMOTE_LOGIN_PASSWORDFILE Initialization Parameter Set to EXCLUSIVE

Database Name Mismatch

Rerunning the ALTER DATABASE CONVERT Statement

Datafile Version Integrity Problem

Abandoning the Oracle7 Upgrade

Migration Issues for Physical Rowids

Upgrading Applications and Migrating Data

The DBMS_ROWID Package

Rowid Conversion Types

Rowid Conversion Functions

The ROWID_TO_EXTENDED Conversion Procedure

ROWID_VERIFY

Conversion Procedure Examples

Example 1

Example 2

Example 3

Example 4

Example 5

Snapshot Refresh

Oracle7 Client Compatibility Issues

ROWID Migration and Compatibility Issues

Accessing an Oracle7 Database from a Release 8.0 and Higher Client

Accessing a Release 8.0 or Higher Database from an Oracle7 Client

Export/Import

Changes to Initialization Parameters and the Data Dictionary in Release 8.0

Initialization Parameter Changes in Release 8.0

Renamed Initialization Parameters in Release 8.0

Table D-1 Initialization Parameters Renamed in Release 8.0

Initialization Parameters Obsolete in Release 8.0

Static Data Dictionary View Changes in Release 8.0

Static Data Dictionary Views Obsolete in Release 8.0

D
Upgrading an Oracle7 Database Using the MIG Utility