Oracle9i Database Migration Release 1 (9.0.1) Part Number A90191-02 |
|
This chapter guides you through the process of migrating an Oracle7 database to Oracle9i using the Oracle Data Migration Assistant. This chapter covers the following topics:
Figure 5-1 is a roadmap that specifies the documentation you should use to migrate your database to release 9.0.1 based on your current release of Oracle.
This section contains important considerations for using the Oracle Data Migration Assistant.
The following restrictions apply to the Oracle Data Migration Assistant:
In general, the Oracle Data Migration Assistant supports migrations of the last 7.3 release and higher databases on your operating system. The exact maintenance release number of the last 7.3 release varies from operating system to operating system.
For example, on some operating systems, the Oracle Data Migration Assistant can only migrate release 7.3.4 and higher databases, and cannot migrate a release lower than release 7.3.4 (such as release 7.0, 7.1, and 7.2). If your database release number is lower than the release supported by the Oracle Data Migration Assistant on your operating system, then upgrade or migrate the database to the required release.
Downgrading is the process of transforming an existing Oracle database into a previous version or release. The Oracle Data Migration Assistant cannot transform an Oracle9i database back into Oracle7. In some situations, you can use another facility to downgrade, such as using Export/Import, restoring from backups, and possibly using other functions.
The following sections discuss system considerations and requirements for using the Oracle Data Migration Assistant.
Oracle9i binaries may require as much as three times the disk space required by Oracle7 binaries. This requirement may cause you to run out of disk space during migration. 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 server and Oracle7 server to determine the total system requirements.
The Oracle Data Migration Assistant 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 source data dictionary required. If necessary, add space to the SYSTEM
tablespace. The Oracle Data Migration Assistant will not complete the migration unless sufficient space is allocated in the SYSTEM
tablespace.
If you need to add more space to the SYSTEM
tablespace, then issue a statement similar to the following, substituting the appropriate directory path and name for the new datafile and the amount of space you need to add:
ALTER TABLESPACE system ADD DATAFILE '/home/user1/mountpoint/oradata/db1/system02.dbf' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
Control files are considerably larger in Oracle9i than in Oracle7. For example, Oracle7 control files in the hundreds of kilobytes may expand into tens of megabytes in Oracle9i. The larger size in Oracle9i results from the storage of more information in the control file, such as backup and tablespace records. This size increase could be important if a control file is on a file system where its available disk space is restricted.
The value of DB_BLOCK_SIZE
(an initialization parameter in the initialization parameter file) in the Oracle7 database and in the migrated Oracle9i database must be the same. Oracle9i requires a minimum block size of 2048 bytes (2KB). Above this amount, integer multiples of your operating system's physical block size are acceptable. However, multiples of 2KB, especially powers of 2--that is, 2KB, 4KB, 8KB, 16KB--provide for the most robust operation.
Make sure the Oracle9i block size setting meets the following criteria:
There are many issues relating to SQL*Net that you must consider when you migrate your database to Oracle9i, not the least of which is deciding whether you will migrate to Oracle Net.
See Also:
Appendix F, "Migration and Compatibility for Oracle Net Services", for information about these issues and for instructions on migrating from SQL*Net to Oracle Net. |
You can migrate an Oracle7 replication environment to Oracle9i. Oracle7 sites can coexist and run successfully with version 8 and Oracle9i sites within the replication environment. However, take special care to accommodate the various replication features implemented on each system.
See Also:
Appendix G, "Migration and Compatibility for Replication Environments", for detailed instructions about migrating systems using replication features. |
See Oracle Text Application Developer's Guide for information about migrating from ConText to Oracle Text.
The Oracle Data Migration Assistant cannot migrate a database to a computer system that has a different operating system. For example, it cannot migrate a database from Oracle7 on Solaris to Oracle9i on Windows NT. However, you can normally use Export/Import to migrate a database to a different operating system.
Note: Starting with release 8.1, a change in word-size is supported during the migration process. A change in word size involves switching between 32-bit and 64-bit architecture within the same operating system. See "Changing Word-Size" for more information. |
In Oracle9i, the SQL NCHAR datatypes (NCHAR
, NVARCHAR
, and NCLOB
) will be limited to the Unicode character set encoding (UTF8 and AL16UTF16) only. When you migrate to Oracle9i, the value of the National Character Set of the migrated database is set to AL16UTF16.
The AL24UTFFSS Unicode character set has been desupported in Oracle9i. AL24UTFFSS was introduced in Oracle7 as the Unicode character set supporting the UTF-8 encoding scheme based on the Unicode 1.1 standard, which is now obsolete. In Oracle9i, The Unicode database character sets AL32UTF8 and UTF8, include the Unicode enhancements based on the Unicode 3.1 standard.
The migration path for existing AL24UTFFSS databases is to upgrade your database character set to UTF8 prior to upgrading to Oracle9i. As with all migrations to a new database character set, Oracle Corporation recommends you use the Character Set Scanner for data analysis before attempting to migrate your existing database character set to UTF8.
When migrating from Oracle7 in a distributed database configuration, make sure that no pending transactions are in the DBA_2PC_PENDING
data dictionary view before migrating the database. Otherwise, when you open the database after migration 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
.
Complete the following steps before you migrate your Oracle7 database to Oracle9i:
"Start with an Oracle7 Database Supported by the Oracle Data Migration Assistant" for more information.
See Also:
SPOOL v7files.log
SELECT member FROM v$logfile;
SPOOL OFF
SELECT name FROM v$datafile;
SELECT value FROM v$parameter WHERE name = 'control_files';
The v7files.log
spool file lists all of the files that you must back up.
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, 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.
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 migrate the database. You can use the V$DATAFILE
dynamic performance view to find the datafile name based on the datafile number. You will encounter an error during migration if any datafiles require media recovery.
Tablespaces that are not taken offline cleanly must be dropped or brought online before migration. Otherwise, these tablespaces will not be available under Oracle9i after the migration. Typically, tablespaces that are taken offline by using an ALTER TABLESPACE OFFLINE IMMEDIATE
or ALTER TABLESPACE OFFLINE TEMPORARY
statement require media recovery.
After migration, tablespaces that are offline when you open the Oracle9i database remain in Oracle7 database file format. The offline tablespaces can be brought online at any time after migration, 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 run another migration.
OUTLN
, because this schema is created automatically when you install Oracle9i. If you have a user or role named OUTLN
, then you must drop the user or role and recreate it with a different name.
To check for a user with the name OUTLN
, issue the following SQL statement:
SELECT USERNAME FROM dba_users WHERE USERNAME = 'OUTLN';
If you do not have a user named OUTLN
, then zero rows are selected.
To check for a role with the name OUTLN
, issue the following SQL statement:
SELECT ROLE FROM dba_roles WHERE ROLE = 'OUTLN';
If you do not have a role named OUTLN
, then zero rows are selected.
MIGRATE
, because the Oracle Data Migration Assistant 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 named 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 named 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.
SYSTEM
rollback segment does not have an OPTIMAL
setting. An OPTIMAL
setting may cause errors during migration.
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 migration is complete.
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 successfully complete the migration. If there is not enough space in your SYSTEM
rollback segment, then you may encounter an error when you run the Oracle Data Migration Assistant.
SYSTEM
tablespace has enough free space to hold the Oracle9i data dictionary and the existing Oracle7 data dictionary concurrently.
To check the free space in your SYSTEM
tablespace, issue the following SQL statement:
SELECT sum(bytes) FROM dba_free_space WHERE tablespace_name='SYSTEM';
This statement displays the number of free bytes in the system
tablespace.
See Also:
"Space Requirements" and "Assess System Requirements vs. Resources Available" for more information. |
Specifically, complete the following steps:
JOB_QUEUE_PROCESSES
initialization parameter, then set this parameter to 0 (zero) in the initialization parameter file. After migrating your database, you can change the setting for this parameter back to its normal setting.
OPTIMIZER_MODE
initialization parameter is set to choose
. After migrating your database, you can change the setting for this parameter back to its normal setting.
DB_DOMAIN
initialization parameter is set properly.
"The DB_DOMAIN Parameter" for more information about setting this initialization parameter.
See Also:
BACKGROUND_DUMP_DEST
and USER_DUMP_DEST
initialization parameters that point to RDBMS71, RDBMS72, or RDBMS73 to point to the following directories instead (optional):
Initialization Parameter | Change Setting To |
---|---|
|
|
|
|
In the settings, substitute the complete ORACLE_BASE path for ORACLE_BASE and substitute the database name for DB_NAME.
IFILE
(include file) entry, then edit the file specified in the IFILE
entry in the same way that you edited the initialization parameter file in sub-steps a to d.
Make sure you save the initialization parameter file and the file specified in the IFILE
entry, if one exists, after making these adjustments.
In addition to the steps described in the previous section, "Prepare the Oracle7 Source Database for Migration", complete the following steps if you are migrating your database on a Windows platform:
If the required release of SQL*Net is not installed, complete the following steps to install it:
If you are installing SQL*Net release 2.3.2.1.12, navigate to the following directory on the CD-ROM:
\patches\sqlnet\232112\nt_x86\install
If you are installing SQL*Net release 2.3.3.0.3, navigate to the following directory on the CD-ROM:
\patches\sqlnet\23303\nt_x86\install
nt.prd
file.
Complete the following steps to install the release 9.0.1 software and migrate the database:
If you need help at any screen or want to consult more documentation about the Oracle Universal Installer, then click the Help button to open the online help.
The Available Products screen appears.
After you make your selection, click Next.
If you chose Custom, the Available Product Components screen appears. Complete the following steps:
Make sure you install all of the options you installed with the Oracle7 database, assuming you do not want to discontinue use of a particular option. For example, if you installed Oracle Advanced Replication in Oracle7, then you should install it in Oracle9i.
When installation is complete, one or more assistants may be started. When the Oracle Data Migration Assistant is started, you are ready to proceed with the migration.
If you need help at any screen or want to consult more documentation about the Oracle Data Migration Assistant, then click the Help button to open the online help.
If you chose Custom, then respond to the screens that enable you to specify your custom migration settings until you reach the Backup Your Database screen. If you need help with any of the custom screens, click the Help button.
After you have made your choice, click Next.
When you click Next, the Status screen appears and the Oracle Data Migration Assistant begins to perform the migration. A status bar shows its progress. When the migration is complete, the Listener.ora Migration Confirmation screen appears.
listener.ora
file automatically, or click the No button if you do not want the assistant to modify the listener.ora
file.
Certain modifications are required to the listener.ora
file for your database to work properly with Oracle Enterprise Manager. If you plan to use Oracle Enterprise Manager, then you should click the Yes button to automatically modify the listener.ora
file. However, if you do not plan to use Oracle Enterprise Manager, then click the No button.
If you click the Yes button, then the Oracle Data Migration Assistant modifies the listener.ora
file in the following way:
listener.ora
in one of the following ways:
A simple case: Suppose the old listener.ora
has the following SID_DESC entry:
... (SID_DESC = (SID_NAME = ORCL) ) ...
If the database name is SAL, then the domain name is COM, and the Oracle home is /oracle/product/9.0
, the assistant adds the following entry:
... (SID_DESC = (GLOBAL_DBNAME = sal.com) (ORACLE_HOME = /oracle/product/9.0) (SID_NAME = SAL) ) ...
A more complicated case: Suppose the old listener.ora
has the following SID_DESC entry:
...
(SID_DESC =
(GLOBAL_DBNAME = an_entry
)
(SID_NAME = ORCL)
)
...
If an_entry does not match the GLOBAL_DBNAME of the migrated database, and if the database name is SAL, the domain name is COM, and the Oracle home is /oracle/product/9.0
, then the assistant adds the following entry:
... (SID_DESC = (GLOBAL_DBNAME = sal.com) (ORACLE_HOME = /oracle/product/9.0) (SID_NAME = SAL) ) ...
This entry is the same as the entry in the simple case, but the assistant also adds the entry an_entry to the SERVICE_NAMES
parameter. Therefore, the assistant changes the SERVICE_NAMES
parameter to the following:
SERVICE_NAMES = sal.com, an_entry
listener.ora
file.
listener.ora
file in both the Oracle7 and the Oracle9i environments.
When the Oracle Data Migration Assistant is complete, go to "Finish the Migration" for information about additional migration steps.
If you installed Oracle9i without specifying that you are migrating an existing database, then you can run the Oracle Data Migration Assistant independently after the Oracle9i installation is complete.
Complete the following steps to run the Oracle Data Migration Assistant independently:
On UNIX, enter the following command at a system prompt:
odma
On Windows platforms, choose:
Start > Programs > Oracle - HOME_NAME > Migration Utilities > Oracle Data Migration Assistant
When you start the Oracle Data Migration Assistant, its Before You Migrate or Upgrade screen appears (see Figure 5-2).
Step 9 to Step 15 in "Install the Release 9.0.1 Oracle Software and Migrate the Database" for more information about the windows that appear in the Oracle Data Migration Assistant.
See Also:
Complete the following steps after you have successfully run the Oracle Data Migration Assistant:
ORACLE_HOME
/rdbms/admin
directory in the Oracle9i Oracle home.
SYSDBA
privileges.
STARTUP RESTRICT
:
SQL> STARTUP RESTRICT
You may need to use the PFILE
option to specify the location of your initialization parameter file.
SQL> SPOOL catoutma.log
If you want to see the output of the scripts you will run on your screen, then you can also issue a SET ECHO ON
statement:
SQL> SET ECHO ON
utlrp.sql
if you did not run it using the Oracle Data Migration Assistant (optional):
SQL> @utlrp.sql
The utlrp.sql
script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on. These actions are optional; however, they ensure that the cost of recompilation is incurred during installation rather than in the future.
Oracle Corporation highly recommends performing this optional step.
If you encounter any problems when you run these scripts, or any of the scripts in the remaining steps, then correct the causes of the problems and rerun the scripts. You can rerun any of the scripts described in this chapter as many times as necessary.
SQL> SPOOL OFF
Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file in Step 5; the suggested name was catoutma.log
.
You should look for errors that alert you to insufficient space, and for errors that alert you that a script failed to run. If you see these types of errors, then your migration may not be completely successful. However, you typically can ignore errors about the failure to alter or drop an object that does not exist.
If you specified SET ECHO ON
, then you may want to SET ECHO OFF
now:
SQL> SET ECHO OFF
SHUTDOWN
on the Oracle9i database:
SQL> SHUTDOWN IMMEDIATE
Executing this clean shutdown flushes all caches, clears buffers, and performs other DBMS housekeeping activities. These measures are an important final step to ensure the integrity and consistency of the newly migrated Oracle9i database.
Alter any parameter whose syntax has changed in Oracle9i; refer to Appendix B, "Changes to Initialization Parameters", for lists of new, renamed, and obsolete parameters.
Also, learn about the new parameters listed in Appendix B, "Changes to Initialization Parameters", and decide which ones you want to use for your migrated database.
In addition, the Oracle Data Migration Assistant sets the COMPATIBLE
initialization parameter to 8.0.5. See Chapter 9, "Compatibility and Interoperability", for information about resetting the COMPATIBLE
initialization parameter.
If you retain the old Oracle7 software, then never start the migrated database with the old Oracle7 software. Only start the database with the executables in the new Oracle9i installation directory. Also, before you remove the old Oracle7 environment, make sure you relocate any datafiles in that environment to the Oracle9i environment. See the Oracle9i Database Administrator's Guide for information about relocating datafiles.
Caution:
Errors may be caused by the following actions or omissions:
Appendix A, "Troubleshooting Migration Problems" and Oracle9i Database Error Messages for information about errors during migration and about corrective action for each error.
See Also:
The easiest way to abandon a migration is to restore the backup of your Oracle7 database that you took before you ran the Oracle Data Migration Assistant.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|