Upgrading to Oracle Database 10g, concluded

Want to upgrade to Oracle Database 10g, but not sure how? Fortunately, there are a number of ways to do it. This article, the second of two parts, describes these methods and guides you as to which one to use, depending on your circumstances. It is excerpted from chapter two of the Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

Using Export and Import

Export and Import provide you with an indirect method for the upgrade. You can create an Oracle 10g database alongside your existing database and use Export and Import to move data from the old database to the new database. When the movement of the data is complete, you will need to point your applications to connect to the new database instead of the old database. You will also need to update any configuration files, version-specific scripts, and the networking configuration files (tnsnames.ora and listener.ora) to point to the new database.

Export and Import Versions to Use

When you create an Export dump file via the Export utility, that file can be imported into all later releases of Oracle. Export dump files are not backward compatible, so if you ever need to revert to an earlier version of Oracle, you will need to carefully select the version of Export and Import used. The following table shows the versions of the Export and Import executables you should use when going between versions of Oracle:

 

Export From:

Import To:

Use Export Utility For:

Use Import Utility For:

Release 9.2

Release 10.1

Release 9.2

Release 10.1

Release 8.1.7

Release 10.1

Release 8.1.7

Release 10.1

Release 8.0.6

Release 10.1

Release 8.0.6

Release 10.1

Release 7.3.4

Release 10.1

Release 7.3.4

Release 10.1

Release 10.1

Release 8.0.6

Release 8.0.6

Release 8.0.6

Release 10.1

Release 8.1.7

Release 8.1.7

Release 8.1.7

Release 10.1

Release 9.0.1

Release 9.0.1

Release 9.0.1

Release 10.1

Release 9.2

Release 9.2

Release 9.2

Release 10.1

Release 10.1

Release 10.1

Release 10.1

 

Note that when you are exporting in order to downgrade your database release, you should use the older version of the Export utility to minimize compatibility problems. You may still encounter compatibility problems if the newer version of the database uses new features (such as new datatypes) that the old version will not support.

{mospagebreak title=Performing the Upgrade}

Export the data from the source database using the version of the Export utility specified in the prior section. Perform a consistent export or perform the export when the database is not available for updates during and after the export.

NOTE

If you have little free space available, you may back up and delete the existing database at this point and then install Oracle Database 10g software and create a target database for the import. If at all possible, maintain the source and target databases concurrently during the upgrade. The only benefit of having only one database on the server at a time is that they can share the same database name.

Install the Oracle Database 10g software and create the target database. In the target database, pre-create the users and tablespaces needed to store the source data. If the source and target databases will coexist on the server, you need to be careful not to overwrite datafiles from one database with datafiles from the other. The Import utility will attempt to execute the create tablespace commands found in the Export dump file, and those commands will include the datafile names from the source database. By default, those commands will fail if the files already exist (although this can be overridden via Import’s DESTROY parameter). Pre-create the tablespaces with the proper datafile names to avoid this problem.

NOTE

You can export specific tablespaces, users, tables, and rows.

Once the database has been prepared, use Import or Data Pump Import to load the data from the Export dump file into the target database. Review the log file for information about objects that did not import successfully.

{mospagebreak title=Using the Data-Copying Method}

The data-copying method requires that the source database and target database coexist. This method is most appropriate when the tables to be migrated are fairly small and few in number. As with the Export/Import method, you must guard against transactions occurring in the source database during and after the extraction of the data. In this method, the data is extracted via queries across database links.

Create the target database using the Oracle Database 10g software and then pre-create the tablespaces, users, and tables to be populated with data from the source database. Create database links (see Chapter 16) in the target database that access accounts in the source database. Use the insert as select command to move data from the source database to the target.

The data-copying method allows you to bring over just the rows and columns you need; your queries limit the data migrated. You will need to be careful with the relationships between the tables in the source database so you can re-create them properly in the target database. If you have a long application outage available for performing the upgrade and you need to modify the data structures during the migration, the data-copying method may be appropriate for your needs. Note that this method requires that the data be stored in multiple places at once, thus impacting your storage needs.

To improve the performance of this method, you may consider the following options:

  • Disable all indexes and constraints until all the data has been loaded.
  • Run multiple data-copying jobs in parallel.
  • Use the parallel query option to enhance the performance of individual queries and inserts.
  • Use the APPEND hint to enhance the performance of inserts.

As of Oracle 10g, you can use cross-platform transportable tablespaces. When transporting tablespaces, you export and import only the metadata for the tablespace, while the datafiles are physically moved to the new platform. For very large databases, the time required to move the datafiles may be significantly shorter than the time required to reinsert the rows. See Chapter 17 for details on the use of transportable tablespaces.

See Chapter 8 for additional advice on performance tuning.

{mospagebreak title=After Upgrading}

Following the upgrade, you should double-check the configuration and parameter files related to the database, particularly if the instance name changed in the migration process. These files include

  1. The tnsnames.ora file
  2. The listener.ora file
  3. Programs that may have hard-coded instance names in them

NOTE

You will need to manually reload the modified listener.ora file if you are not using DBUA to perform the upgrade.

You should review your database initialization parameters to make sure deprecated and obsolete parameters have been removed; these should have been identified during the migration process. Be sure to recompile any programs you have written that rely on the database software libraries.

Once the upgrade has completed, perform the functional and performance tests identified before the upgrade began. If there are issues with the database functionality, attempt to identify any parameter settings or missing objects that may be impacting the test results. If the problem cannot be resolved, you may need to revert to the prior release.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye