8
After Migrating or Upgrading the Database

This chapter guides you through the procedures to perform after you have completed a migration or upgrade of your database. If you migrated your database, then complete these tasks regardless of the method you used, including the following methods: the Migration utility, the Oracle Data Migration Assistant, Export/Import, or data copy. Similarly, if you upgraded your database, then complete these procedures regardless of the method you used, including the following methods: the Oracle Data Migration Assistant or manual upgrade.

This chapter covers the following topics:

Tasks to Complete After Migrating or Upgrading Your Database

Complete the following tasks after you migrated or upgraded your database.

Back Up the Database

Make sure you perform a complete backup of the production database. This backup must be complete, including all datafiles, control files, online redo log files, parameter files, and SQL scripts that create objects in the new database. To accomplish a complete backup, a full database export or a cold backup is required, because a hot backup cannot afford full recoverability. This backup can be used as a return point, if necessary, in case subsequent steps adversely affect the database.

See Also:
Oracle8i Backup and Recovery Guide for details about backing up the database.

Note:
Using the Migration utility transforms the source database. Therefore, after migration, the source database ceases to exist except for the backup you created under "Preserve the Oracle7 Source Database". This backup also can serve as the first Oracle8i backup for a recovery of the newly migrated database.

Change the Password for the OUTLN User

The OUTLN user is created automatically during installation of Oracle8i. This user has DBA privileges. Use the ALTER USER statement to change the password for this user. Oracle8i adds the OUTLN user schema to support Plan Stability. The OUTLN user acts as a place to centrally manage metadata associated with stored outlines.

Copy LONGs to LOBs

LOB datatypes (BFILE, BLOB, CLOB, and NCLOB) can provide many advantages over LONG datatypes. See Oracle8i Concepts for information about the differences between LOB and LONG datatypes.

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.

In the examples in the following procedure, the LONG column named LONG_COL in table LONG_TAB is copied to a LOB column named LOB_COL in table LOB_TAB. These tables include an ID column that contains identification numbers for each row in the table.

Complete the following steps to copy data from a LONG column to a LOB column:

Create a new table with the same definition as the table that contains the LONG column, but use a LOB datatype in place of the LONG datatype.

For example, suppose you have a table with the following definition:

CREATE TABLE long_tab (
    id NUMBER,
    long_col LONG);

Create a new table using the following SQL statement:

CREATE TABLE lob_tab (
    id NUMBER,
    clob_col CLOB);

Note:
When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB function only copies data; it does not preserve the table's schema.

Issue an INSERT statement using the TO_LOB function to insert the data from the table with the LONG datatype into the table with the LOB datatype.

For example, issue the following SQL statement:
```
INSERT INTO lob_tab
    SELECT id,
    TO_LOB(long_col)
    FROM long_tab;
```
When you are certain that the copy was successful, drop the table with the LONG column.

For example, issue the following SQL statement to drop the LONG_TAB table:
```
DROP TABLE long_tab;
```
Create a synonym for the new table using the name of the table with LONG data. The synonym ensures that your database and applications continue to function properly.

For example, issue the following SQL statement:
```
CREATE SYNONYM long_tab FOR lob_tab;
```

Once the copy is complete, any applications that use the table must be modified to use the LOB data.

See Also:
Oracle8i Application Developer's Guide - Large Objects (LOBs) for information about modifying applications to use LOB data.

Check for Bad Date Constraints

A bad date constraint involves invalid date manipulation, which is a date manipulation that implicitly assumes the century in the date, causing problems at the year 2000. The utlconst.sql script runs through all of the check constraints in the database and marks constraints as bad if they include any invalid date manipulation. This script selects all the bad constraints at the end. Oracle7 let you create constraints with a two-digit year date constant. However, version 8 returns an error if the check constraint date constant does not include a four-digit year.

To run the utlconst.sql script, complete the following steps:

At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
Start Server Manager. On most operating systems, enter svrmgrl at a command prompt to start Server Manager in Oracle8i.
Connect to the database instance:
```
SVRMGR> CONNECT INTERNAL
```

Enter the following:

SVRMGR> SPOOL utlresult.log
SVRMGR> @utlconst.sql
SVRMGR> SPOOL OFF

After you run the script, the utlresult.log log file includes all the constraints that have invalid date constraints.

Note:
The utlconst.sql script does not correct bad constraints, but instead it disables them. You should either drop the bad constraints or recreate them after you make the necessary changes.

Avoid Problems with Parallel Execution

Beginning with release 8.1, parallel execution message buffers can be allocated from the large pool. In past releases, this allocation was from the shared pool. To avoid problems resulting from this change, you may need to adjust the following initialization parameters in your initialization parameter file:

SHARED_POOL_SIZE
LARGE_POOL_SIZE

See Also:
"Parallel Execution Allocated from Large Pool" for information about adjusting these parameters.

Install ODCI.jar and CartridgeServices.jar Files

If you installed the Java option, then the you must install the ODCI.jar and CartridgeServices.jar files. You do not need to perform this task if you did not install the Java option.

To install the ODCI.jar and CartridgeServices.jar files, run the following commands from the command line:

loadjava -user sys/PASSWORD -resolve -synonym -grant public
-verbose ORACLE_HOME/vobs/jlib/ODCI.jar

loadjava -user sys/PASSWORD -resolve -synonym -grant public 
-verbose ORACLE_HOME/vobs/jlib/CartridgeServices.jar

Substitute the SYS password for PASSWORD, and substitute the Oracle home directory for ORACLE_HOME. These commands install the classes and create the synonyms in the SYS schema.

See Also:
These classes and synonyms must be created under SYS schema.

Modify Your listener.ora File

You need to modify your listener.ora file only if one of the following conditions are true:

You did not use the Oracle Data Migration Assistant to migrate or upgrade your database.
You used the Oracle Data Migration Assistant to migrate or upgrade your database but chose not to have the listener.ora file updated automatically.

If neither of these conditions are true, then skip this section. If one of these conditions is true, then you need to modify your listener.ora file.

See Also:
The section "listener.ora" for information about modifying your listener.ora file.

Migrate or Upgrade Your Standby Database to Oracle Release 8.1

If you are using a standby database, then the primary and standby databases must run the same maintenance release of Oracle, and both databases must have the same setting for the COMPATIBLE initialization parameter.

For example, if your primary database is running release 8.1.7 with the COMPATIBLE initialization parameter set to 8.1.0, then the standby database can run any production 8.1 release, such as release 8.1.5, 8.1.6, or 8.1.7, as long as it also has COMPATIBLE set to 8.1.0. However, in this case, the standby database cannot run Oracle7 or release 8.0, and COMPATIBLE cannot be set to any value other than 8.1.0 for the standby database.

To migrate the standby database from Oracle7 to release 8.1, or to upgrade the standby database from release 8.0 to release 8.1, perform the following steps:

Apply all redo logs created under Oracle7 or release 8.0 to the standby database.
Make sure the primary database has been opened successfully under Oracle8i.
Install the release 8.1 Oracle software on the computer that is running the standby database.
- Install the new Oracle software in an Oracle home directory separate from the old release of Oracle.
- At the Installation Types screen in the Oracle Universal Installer, choose Custom. Do not choose Typical unless because this choice will install a starter database along with your Oracle software. You can avoid installing a starter database if you select Custom.
- At the Upgrading or Migrating an Existing Database screen in the Oracle Universal Installer, leave the Upgrade or Migrate an Existing Database box unselected.
- If the Oracle Universal Installer asks you whether you want to create a starter database, instruct it not to create a starter database.
  
  See Also:
  The Oracle installation documentation for your operating system for information about installing Oracle.
Complete the following additional steps if you migrated from Oracle7. These steps are not required if you upgraded from release 8.0.
1. Create a control file for your standby database by issuing the ALTER DATABASE CREATE STANDBY CONTROLFILE AS file_name command, which creates a modified copy of the primary database's control file.
2. Transfer the control file you created in Step 4 to the standby database site.
3. Use your operating system to create a copy of the initial datafile at your primary database. If you are unsure about which datafile is the initial datafile, the following SQL statement provides that information:
```
SELECT file_name, file_id FROM dba_data_files
  WHERE file_id = 1;
```
4. Transfer the copy of the initial datafile you made in Step c from the primary database to the standby database.
5. Deinstall the old Oracle software on the standby database computer (optional).
  
  See Also:
  Oracle8i Standby Database Concepts and Administration for more information about standby database.

Add New Features as Appropriate

Getting to Know Oracle8i describes many of the new features available in Oracle8i. Determine which of these new features can benefit the database and applications; then, develop a plan for using these features.

It is not necessary to make any immediate changes to begin using your Oracle8i database. You may prefer to introduce these enhancements into your database and corresponding applications gradually.

Chapter 10, "Upgrading Your Applications", describes ways to enhance your applications so that you can take advantage of the new Oracle8i features. However, before you implement new Oracle8i features, test your applications and successfully run them with the database you migrated or upgraded.

Develop New Administrative Procedures as Needed

After familiarizing yourself with the Oracle8i features, review your database administration scripts and procedures to determine whether any changes are necessary.

Coordinate your changes to the database with the changes that are necessary for each application. For example, by enabling integrity constraints in the database, you may be able to remove some data checking from your applications.

Adjust Your Initialization Parameter File for the New Release

Each new release of Oracle introduces new initialization parameters, changes some parameters, and obsoletes some parameters. You should adjust your initialization parameter file to account for these changes and to take advantage of new initialization parameters that may be beneficial to your system.

See Also:
Appendix B, "Changes to Initialization Parameters" for lists of the new, changed, and obsoleted initialization parameters in release 8.1, and see Oracle8i Reference for detailed information about each parameter.

The COMPATIBLE initialization parameter controls the compatibility level of your database. Set the COMPATIBLE initialization parameter in your initialization parameter file based on the compatibility level you want for your upgraded database.

See Also:
"Setting the COMPATIBLE Initialization Parameter" for information.

Normalize Filenames on Windows Platforms

You only need to normalize filenames if you are running Oracle on a Windows platform. You do not need to perform these steps on UNIX operating systems.

The control file and the recovery catalog both store filenames so that they can access files that are required by the database, such as:

datafiles
control files
online and archived redo logs used by Oracle
datafile copies and on-disk backup pieces used by Recovery Manager

In releases prior to release 8.1.6 on Windows platforms, a flawed filename normalization mechanism allowed two different filenames to refer to the same physical file. For example, because of this flaw, Oracle may not record the fully specified pathname for a file in the control file. That is, Oracle may record only dbfile1.dbf instead of c:\oracle\oradata\dbfile1.dbf. If this happens, then, in subsequent statements that modify c:\oracle\oradata\dbfile1.dbf, Oracle might conclude that this file is different than dbfile1.dbf.

Also, because of this behavior, SQL statements and Recovery Manager commands that refer to existing files must be specified exactly as they were originally entered or they are not recognized. An example of a SQL statement that refers to existing files is the ALTER DATABASE RENAME FILE statement.

In release 8.1.6 and higher, the flawed filename normalization mechanism is corrected. However, existing filenames in the control file and recovery catalog must be normalized with the new filename normalization mechanism.

Note:
Do not perform the following procedure on Oracle releases prior to release 8.1.6.

To normalize these filenames, complete the following steps:

Using SQL*Plus, connect to the database as SYSDBA:
```
SQL> connect sys/password as sysdba
```
Shut down the database using SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE;
```
Make an operating system backup of your control file.

See Also:
Oracle8i Backup and Recovery Guide for more information about operating system backups.
Run STARTUP MOUNT to mount the database without opening it:
```
SQL> STARTUP MOUNT
```
Run the DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES procedure to normalize the filenames in your control file:
```
SQL> EXECUTE DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES;
```
When the DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES procedure has completed successfully, open the database:
```
SQL> ALTER DATABASE OPEN;
```
Exit SQL*Plus.
Log in to Recovery Manager and connect to a target database and recovery catalog.

For example, if the network service name for the target database is TGT_DB and the network service name for the recovery catalog database is CAT_DB, then you can enter the following, substituting the appropriate schema names and passwords:
```
rman target sys/password@tgt_db catalog rcat_schema/rcat_password@cat_db 
```

Issue the renormalize catalog command to normalize the filenames in the recovery catalog for this target database:

RMAN> renormalize catalog;

Note:
The renormalize catalog command is not considered part of the Recovery Manager syntax and is not documented in the Oracle8i Recovery Manager User's Guide and Reference. The command is only intended for use on databases migrated or upgraded from a release prior to release 8.1.6 on Windows platforms.

Repeat Steps 8 and 9 for each release 8.1.6 or higher target database registered in this recovery catalog.

Your filenames are now normalized.

Note:
If you need to restore a control file for a point-in-time recovery from a backup that was taken before you completed the filename normalization procedure described above, then first restore the backup control file, then perform Steps 1 to 7, and finally perform the recovery.

Tasks to Complete Only After Migrating Your Database

Complete the following tasks only if you migrated your database from version 7 or version 6. These tasks are not required if you upgraded your database from a prior 8.0 or 8.1 release.

Rebuild Unusable Bitmap Indexes

During migration, some bitmap indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT index_name, index_type, table_owner, status  
   FROM dba_indexes 
      WHERE index_type = 'BITMAP' 
      AND status = 'UNUSABLE';

Rebuild the unusable bitmap indexes listed.

See Also:
Oracle8i Designing and Tuning for Performance and Oracle8i Concepts for more information about using bitmap indexes.

Migrate Partition Views to Partition Tables

Partition views are not recommended for new applications in Oracle8i, and existing partition views should be converted to partitioned tables. You can convert partition views created for Oracle7 databases to partitioned tables by using the EXCHANGE PARTITION option of the ALTER TABLE statement.

See Also:
Oracle8i Administrator's Guide for information about converting partitioned views to partitioned tables and Oracle8i Concepts for background information about partition views and partitioned tables.

Migrate or Upgrade to the New Release of Net8 (Optional)

Migrating or upgrading to the new release of Net8 is not required. However, Net8 provides significant advantages over SQL*Net V2, including simplified configuration and expanded functionality. The new release of Net8 also provides the following advantages over past releases of Net8 and SQL*Net:

Service naming enables clients to access a service as a whole, using the service name, rather than a specific database instance. Service naming logically separates the service name from any particular instance name and replaces the SID parameter, enabling one instance to serve multiple services. Individual instances also can be part of multiple services.
Instance registration is automatic. Instances register themselves with the listener when they are started. In past releases, information about the instance was configured manually in the listener.ora file.
Failover is automatic. If an instance is down, a client connect request is sent to a different listener automatically.

Load balancing distributes connections over the available listeners.

See Also:
Net8 Administrator's Guide for more information about the advantages of Net8, and see Appendix F, "SQL*Net and Net8 Considerations for Migrations and Upgrades" for detailed instructions on migrating or upgrading to the new release of Net8.

Test the Database and Compare Results

Test the Oracle8i database using the testing plan you developed in "Develop a Testing Plan". Compare the results of the test with the results obtained with the original database and make certain the same, or better, results are achieved.

Generally, the performance of the migrated Oracle8i database should be as good as, or better than, the performance of the source database. If you notice any decline in database performance with Oracle8i, then make sure the initialization parameters are set properly, because improperly set initialization parameters can hurt performance.

Tune the Migrated Database

If you want to improve the performance of the migrated database, then tune the database. Most of the actions normally used to tune Oracle7 databases and related applications either have the same effect on or are unnecessary for Oracle8i databases. Therefore, actions you used to tune your source database and applications should not impair the performance of the migrated Oracle8i database.

See Also:
Oracle8i Designing and Tuning for Performance for tuning information.

Task to Complete Only After Upgrading Your Database

Complete the following task only if you upgraded your database from release 8.0 or release 8.1. This task is not required if you migrated your database from version 7 or version 6.

Rebuild Unusable Function-Based Indexes

During upgrade, some function-based indexes may become unusable. To find these indexes, issue the following SQL statement:

SELECT owner, index_name, funcidx_status 
   FROM dba_indexes WHERE funcidx_status = 'DISABLED';

Rebuild the unusable function-based indexes listed.

8 After Migrating or Upgrading the Database