Oracle9i Database Migration Release 1 (9.0.1) Part Number A90191-02 |
|
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:
Complete the following tasks after you have migrated or upgraded your 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.
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. |
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;
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
To upgrade user tables with NCHAR
columns, perform the following steps:
ORACLE_HOME
/rdbms/admin
directory.
SYSDBA
privileges.
SHUTDOWN IMMEDIATE
:
SQL> SHUTDOWN IMMEDIATE
RESTRICT
mode:
SQL> STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
utlnchar.sql
:
SQL> @utlnchar.sql
Alternatively, to override the default upgrade selection, run n_switch.sql
:
SQL> @n_switch.sql
SQL> SHUTDOWN IMMEDIATE
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.
If you are currently using a traditional initialization parameter file, perform the following steps to migrate to a server parameter file:
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:
Note:
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.
See Also:
CREATE SPFILE
statement
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:
ORACLE_HOME
/javavm/install
directory.
SYSDBA
privileges.
SHUTDOWN IMMEDIATE
:
SQL> SHUTDOWN IMMEDIATE
RESTRICT
mode:
SQL> STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
initjvm.sql
:
SQL> @initjvm.sql
SQL> SHUTDOWN IMMEDIATE
The following components require initialization that is separate from the procedures discussed in "Upgrading JServer":
To install one or more of these components, complete the following steps:
ORACLE_HOME
/rdbms/admin
directory.
SYSDBA
privileges.
SHUTDOWN IMMEDIATE
:
SQL> SHUTDOWN IMMEDIATE
RESTRICT
mode:
SQL> STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
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
SQL> SHUTDOWN IMMEDIATE
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.
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 |
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:
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);
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;
LONG
column.
For example, issue the following SQL statement to drop the long_tab
table:
DROP TABLE long_tab;
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 |
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:
ORACLE_HOME
/rdbms/admin
directory.
SYSDBA
privileges.
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.
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.
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
"Parallel Execution Allocated from Large Pool" for information about adjusting these parameters.
See Also:
You need to modify your listener.ora
file only if one of the following conditions is true:
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.
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:
ALTER DATABASE CREATE STANDBY CONTROLFILE AS
file_name
statement, which creates a modified copy of the primary database's control file.
SELECT file_name, file_id FROM dba_data_files WHERE file_id = 1;
Oracle9i Data Guard Concepts and Administration for more information about standby database.
See Also:
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.
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.
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.
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:
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.
To normalize these filenames, complete the following steps:
SYSDBA
privileges.
SHUTDOWN NORMAL
or SHUTDOWN IMMEDIATE
:
SQL> SHUTDOWN IMMEDIATE
Oracle9i User-Managed Backup and Recovery Guide for more information about operating system backups.
See Also:
STARTUP MOUNT
to mount the database without opening it:
SQL> STARTUP MOUNT
DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES
procedure to normalize the filenames in your control file:
SQL> EXECUTE DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES;
DBMS_BACKUP_RESTORE.RENORMALIZEALLFILENAMES
procedure has completed successfully, open the database:
SQL> ALTER DATABASE OPEN;
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
RENORMALIZE CATALOG
command to normalize the filenames in the recovery catalog for this target database:
RMAN> renormalize catalog;
Note:
The |
Your filenames are now normalized.
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.
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 |
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. |
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:
listener.ora
file.
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.
See Also:
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.
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.
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.
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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|