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 have 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:

Oracle9i User-Managed 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 Oracle9i backup for a recovery of the newly migrated database.

Change the Passwords for Oracle-Supplied Accounts

Depending on the release from which you migrated or upgraded, there may be some new Oracle-supplied accounts. Oracle Corporation recommends that you lock all Oracle-supplied accounts except for SYS and SYSTEM, and expire their passwords, requiring new passwords to be specified if the accounts are unlocked.

You can view the status of all accounts by issuing the following SQL statement:

SQL> SELECT username, account_status
   FROM dba_users
      ORDER BY username;

To LOCK and EXPIRE passwords, issue the following SQL statement:

ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

Upgrade User NCHAR Columns

If you upgraded from a version 8 release and your database contains user tables with NCHAR columns, you must upgrade the NCHAR columns before they can be used in Oracle9i.

The following steps convert your NCHAR columns from the old format and character set to the new Oracle9i format. In addition, if your old National Character Set was UTF8, it will remain UTF8 in Oracle9i. However, your National Character Set will be converted to AL16UTF16 if it was not UTF8 in the old release.

You can override the default upgrade selection of the National Character Set. That is, a version 8 UTF8 National Character Set can be converted to an Oracle9i AL16UTF16 National Character Set or a version 8 non-UTF8 National Character Set can be converted to an Oracle9i UTF8 National Character Set.

You will encounter the following error when attempting to use the NCHAR columns in Oracle9i until you perform the steps in this section:

ORA-12714: invalid national character set specified

Note:

Once you upgrade your NCHAR columns, you will not be able to downgrade to a previous release of Oracle until all NCHAR columns have been dropped.

To upgrade user tables with NCHAR columns, perform the following steps:

Log in to the system as the owner of the Oracle home directory.
At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
If the instance is running, shut it down using SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE
```
Start up the instance in RESTRICT mode:
```
SQL> STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Run utlnchar.sql:
```
SQL> @utlnchar.sql
```
Alternatively, to override the default upgrade selection, run n_switch.sql:
```
SQL> @n_switch.sql
```
Shut down the instance:
```
SQL> SHUTDOWN IMMEDIATE
```
Exit SQL*Plus.

Downgrading SQL NCHAR Columns

Once you have upgraded your SQL NCHAR columns (NCHAR, NVARCHAR2, and NCLOB) to Oracle9i, you will not be able to downgrade to a previous release until all SQL NCHAR columns have been dropped. If you need to recover the version 8 SQL NCHAR data, you will need to reimport the data from a previous backup.

Migrate Your Initialization Parameter File to a Server Parameter File

If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:

If the initialization parameter file is located on a client machine, transfer the file from the client machine to the server machine.

Note:

If you are using Oracle9i Real Application Clusters, you must combine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for cluster databases, are discussed in:

Create a server parameter file using the CREATE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database does not have to be started to issue a CREATE SPFILE statement.
Start up the instance using the newly created server parameter file.
See Also:

Oracle9i Database Administrator's Guide for more information about creating server parameter files

Oracle9i SQL Reference for information about the CREATE SPFILE statement

Install Component Java Classes

The components listed in this section require the Java option. If you did not have Java installed in a previous release, then complete the following steps:

Log in to the system as the owner of the Oracle home directory of the new release.
At a system prompt, change to the ORACLE_HOME/javavm/install directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
If the instance is running, shut it down using SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE
```
Start up the instance in RESTRICT mode:
```
SQL> STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Run initjvm.sql:
```
SQL> @initjvm.sql
```
Shut down the instance:
```
SQL> SHUTDOWN IMMEDIATE
```
Exit SQL*Plus.

The following components require initialization that is separate from the procedures discussed in "Upgrading JServer":

Oracle Change Data Capture
Summary Advisor
ODCI and CartridgeServices
SQLJ

To install one or more of these components, complete the following steps:

Log in to the system as the owner of the Oracle home directory of the new release.
At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.
Start SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.
If the instance is running, shut it down using SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE
```
Start up the instance in RESTRICT mode:
```
SQL> STARTUP RESTRICT
```
You may need to use the PFILE option to specify the location of your initialization parameter file.
Run one or more of the following scripts, depending on the components you are installing.

If you are installing Oracle Change Data Capture, run initcdc.sql:
```
SQL> @initcdc.sql
```
If you are installing Summary Advisor, run initqsma.sql:
```
SQL> @initqsma.sql
```
If you are installing ODCI and CartridgeServices, run initsoxx.sql:
```
SQL> @initsoxx.sql
```
If you are installing SQLJ, run initsjty.sql:
```
SQL> @initsjty.sql
```
Shut down the instance:
```
SQL> SHUTDOWN IMMEDIATE
```
Exit SQL*Plus.

Migrate Tables from LONGs to LOBs

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

Change LONGs to LOBs

In Oracle9i, the ALTER TABLE statement can be used to change the datatype of a LONG column to CLOB and that of a LONG RAW column to BLOB.

In the following example, the LONG column named long_col in table long_tab is changed to datatype CLOB:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

After using this method to change LONG columns to LOBs, all the existing constraints and triggers on the table will still be usable. However, all the indexes, including Domain indexes and Functional indexes, on all columns of the table will become unusable and will have to be rebuilt using an ALTER INDEX ... REBUILD statement. Also, the Domain indexes on the LONG column will have to be dropped before changing the LONG column to a LOB.

See Also:

Oracle9i Application Developer's Guide - Large Objects (LOBs) for information about modifying applications to use LOB data

Copy LONGs to LOBs

In release 8.1, the TO_LOB SQL function 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:

Oracle9i 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 allowed you to create constraints with a two-digit year date constant. However, release 8.0 and higher 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 SQL*Plus.
Connect to the database instance as a user with SYSDBA privileges.

Enter the following:

SQL> SPOOL utlresult.log
SQL> @utlconst.sql
SQL> 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.

Migrate Your Server Manager Line Mode Scripts to SQL*Plus

Oracle9i no longer supports the use of Server Manager. If you run SQL scripts using Server Manager line mode, you must modify these scripts so that they are compatible with SQL*Plus. Chapter 11, "Migrating from Server Manager to SQL*Plus" contains instructions for modifying your Server Manager line mode scripts to work with SQL*Plus.

Avoid Problems with Parallel Execution

Starting 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.

Modify Your listener.ora File

You need to modify your listener.ora file only if one of the following conditions is 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 is true, then skip this section. If one of these conditions is true, then you need to modify your listener.ora file.

See Also:

"listener.ora" for information about modifying your listener.ora file.

Migrate or Upgrade Your Standby Database

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 Oracle9i, or to upgrade the standby database from version 8 to Oracle9i, perform the following steps:

Apply all redo logs created under Oracle7 or version 8 to the standby database.
Make sure the primary database has been opened successfully under Oracle9i.
Install the release 9.0.1 Oracle software on the computer that is running the standby database.
- Install the new release of Oracle in a new Oracle home that is separate from the old release of Oracle.
- At the Installation Types screen of the Oracle Universal Installer, choose Custom. Do not choose Standard Edition or Enterprise Edition because either of these choices 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 of the Oracle Universal Installer, leave the Upgrade or Migrate an Existing Database check 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 a version 8 release.
1. Create a control file for your standby database by issuing an ALTER DATABASE CREATE STANDBY CONTROLFILE AS file_name statement, which creates a modified copy of the primary database's control file.
2. Transfer the control file you created in Step a 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:
  Oracle9i Data Guard Concepts and Administration for more information about standby database.

Add New Features as Appropriate

Oracle9i Database New Features describes many of the new features available in Oracle9i. 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 Oracle9i 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 Oracle9i features. However, before you implement new Oracle9i 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 Oracle9i 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 9.0.1, and Oracle9i Database 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 new database.

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 a user with SYSDBA privileges.
Shut down the database using SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE:
```
SQL> SHUTDOWN IMMEDIATE
```
Make an operating system backup of your control file.

See Also:
Oracle9i User-Managed 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 Oracle9i Recovery Manager User's Guide. 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 Oracle7 or version 6. These tasks are not required if you upgraded your database from a version 8 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:

Oracle9i Database Performance Guide and Reference and Oracle9i Database Concepts for more information about using bitmap indexes

Migrate Partition Views to Partition Tables

Partition views are not recommended for new applications in Oracle9i, 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:

Oracle9i Database Administrator's Guide for information about converting partitioned views to partitioned tables and Oracle9i Database Concepts for background information about partition views and partitioned tables.

Migrate or Upgrade to the New Release of Oracle Net (Optional)

Migrating or upgrading to the new release of Oracle Net is not required. However, Oracle Net provides significant advantages over SQL*Net V2, including simplified configuration and expanded functionality. The new release of Oracle Net also provides the following advantages over past releases of Oracle Net 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:

Oracle Net Services Administrator's Guide for more information about the advantages of Oracle Net, and see Appendix F, "Migration and Compatibility for Oracle Net Services" for detailed instructions on migrating or upgrading to the new release of Oracle Net.

Test the Database and Compare Results

Test the Oracle9i 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 Oracle9i database should be as good as, or better than, the performance of the source database. If you notice any decline in database performance with Oracle9i, 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, Oracle9i databases. Therefore, actions you used to tune your source database and applications should not impair the performance of the migrated Oracle9i database.

See Also:

Oracle9i Database Performance Guide and Reference for tuning information

Task to Complete Only After Upgrading Your Database

Complete the following task only if you upgraded your database from a version 8 release. This task is not required if you migrated your database from Oracle7 or version 6.

Rebuild Unusable Function-Based Indexes

During an 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

Tasks to Complete After Migrating or Upgrading Your Database

Back Up the Database

Change the Passwords for Oracle-Supplied Accounts

Upgrade User NCHAR Columns

Downgrading SQL NCHAR Columns

Migrate Your Initialization Parameter File to a Server Parameter File

Install Component Java Classes

Migrate Tables from LONGs to LOBs

Change LONGs to LOBs

Copy LONGs to LOBs

Check for Bad Date Constraints

Migrate Your Server Manager Line Mode Scripts to SQL*Plus

Avoid Problems with Parallel Execution

Modify Your listener.ora File

Migrate or Upgrade Your Standby Database

Add New Features as Appropriate

Develop New Administrative Procedures as Needed

Adjust Your Initialization Parameter File for the New Release

Normalize Filenames on Windows Platforms

Tasks to Complete Only After Migrating Your Database

Rebuild Unusable Bitmap Indexes

Migrate Partition Views to Partition Tables

Migrate or Upgrade to the New Release of Oracle Net (Optional)

Test the Database and Compare Results

Tune the Migrated Database

Task to Complete Only After Upgrading Your Database

Rebuild Unusable Function-Based Indexes

8
After Migrating or Upgrading the Database