8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Upgrading to Oracle Database 10g
This article provides a brief overview of the areas involved in upgrading existing databases to Oracle 10g Release 1. The article provides the minumum information needed when preparing for the 10g Database Administation OCP upgrade exam, including:
- Supported Upgrade Paths
- Pre-Upgrade Validation Checks
- Database Upgrade Assistant (DBCA)
- STARTUP UPGRADE
The whole migration process is beyond the scope of this article so please refer to the Upgrading a Database to the New Oracle Database 10g Release document for further information.
Supported Upgrade Paths
Direct upgrades to 10g are possible from existing databases with versions listed in the table below. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
Original Version | Upgrade Script |
---|---|
8.0.6 | u0800060.sql |
8.1.7 | u0801070.sql |
9.0.1 | u0900010.sql |
9.2.0 | u0902000.sql |
The preferred upgrade method is to use the Database Upgrade Assistant (DBUA), a GUI tool that performs all necessary prerequisite checks and operations before upgrading the specified instances. The DBUA can be started directly from the Oracle Universal Installer (OUI) or separately after the software installation is complete.
Alternatively you may which to perform a manual upgrade which involves the following steps:
- Analyze the existing instance using the utlu101i.sql script, explained below.
- Backup the database.
- Start the original database in the new upgrade mode (see below) and proceed with the upgrade. The majority of the upgrade work is done by running the appropriate upgrade script for the current database version.
- Recompile invalid objects.
- Troubleshoot any issues or abort the upgrade.
Pre-Upgrade Validation Checks
Oracle 10g includes a script ($ORACLE_HOME/rdbms/admin/utlu101i.sql) which performs pre-update validation checks on an existing instance. The script checks a number of areas to make sure the instance is suitable for upgrade including:
- Database version.
- Log file sizes.
- Tablespace sizes.
- Server options.
- Initialization parameters (updated, depercated and obsolete).
- Database components.
- SYSAUX tablespace present.
- Cluster information.
The issues indicated by this script should be resolved before a manual upgrade is attempted. The type of output you can expect is displayed below.
SQL> conn / as sysdba Connected. SQL> spool /tmp/upgrade.txt SQL> @utlu101i.sql Oracle Database 10.1 Upgrade Information Tool 12-01-2004 17:54:55 . ************************************************************************* Database: ************************************************************************* --> name: OCS --> version: 9.2.0.4.0 --> compatibility: 9.2.0.0.0 . ************************************************************************* Logfiles: [make adjustments in the current environment] ************************************************************************* --> The existing log files are adequate. No changes are required. . ************************************************************************* Tablespaces: [make adjustments in the current environment] ************************************************************************* --> SYSTEM tablespace is adequate for the upgrade. .... minimum required size: 604 MB --> TEMP tablespace is adequate for the upgrade. .... minimum required size: 50 MB --> CWMLITE tablespace is adequate for the upgrade. .... minimum required size: 15 MB --> DRSYS tablespace is adequate for the upgrade. .... minimum required size: 54 MB --> ODM tablespace is adequate for the upgrade. .... minimum required size: 10 MB --> USERS tablespace is adequate for the upgrade. .... minimum required size: 17 MB --> XDB tablespace is adequate for the upgrade. .... minimum required size: 46 MB --> IFS tablespace is adequate for the upgrade. .... minimum required size: 94 MB . ************************************************************************* Options: [present in existing database] ************************************************************************* --> Partitioning --> Real Application Clusters --> Spatial --> OLAP --> Oracle Data Mining WARNING: Listed option(s) must be installed with Oracle Database 10.1 . ************************************************************************* Update Parameters: [Update Oracle Database 10.1 init.ora or spfile] ************************************************************************* WARNING: --> "shared_pool_size" needs to be increased to at least "285212672" --> "pga_aggregate_target" is already at "25165824" calculated new value is "25165824" --> "large_pool_size" is already at "33554432" calculated new value is "33554432" --> "java_pool_size" is already at "117440512" calculated new value is "117440512" . ************************************************************************* Deprecated Parameters: [Update Oracle Database 10.1 init.ora or spfile] ************************************************************************* -- No deprecated parameters found. No changes are required. . ************************************************************************* Obsolete Parameters: [Update Oracle Database 10.1 init.ora or spfile] ************************************************************************* --> "hash_join_enabled" --> "log_archive_start" . ************************************************************************* Components: [The following database components will be upgraded or installed] ************************************************************************* --> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID ...The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP) ...is required to be installed from the 10g Companion CD. ... --> Oracle XDK for Java [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Real Application Clusters [upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> Oracle Data Mining [upgrade] LOADED --> OLAP Analytic Workspace [upgrade] LOADED --> OLAP Catalog [upgrade] VALID --> Oracle OLAP API [upgrade] LOADED --> Oracle interMedia [upgrade] VALID ...The 'Oracle interMedia Image Accelerator' is ...required to be installed from the 10g Companion CD. ... --> Spatial [upgrade] LOADED --> Oracle Text [upgrade] VALID --> Oracle Ultra Search [upgrade] VALID . ************************************************************************* WARNING: --> The "cluster_database" parameter is currently "TRUE" and must be set to "FALSE" prior to running the upgrade. . ************************************************************************* SYSAUX Tablespace: [Create tablespace in Oracle Database 10.1 environment] ************************************************************************* --> New "SYSAUX" tablespace .... minimum required size for database upgrade: 500 MB Please create the new SYSAUX Tablespace AFTER the Oracle Database 10.1 server is started and BEFORE you invoke the upgrade script. . ************************************************************************* Oracle Database 10g: Changes in Default Behavior ------------------------------------------------ This page describes some of the changes in the behavior of Oracle Database 10g from that of previous releases. In some cases the default values of some parameters have changed. In other cases new behaviors/requirements have been introduced that may affect current scripts or applications. More detailed information is in the documentation. SQL OPTIMIZER The Cost Based Optimizer (CBO) is now enabled by default. * Rule-based optimization is not supported in 10g (setting OPTIMIZER_MODE to RULE or CHOOSE is not supported). See Chapter 12, "Introduction to the Optimizer," in Oracle Database Performance Tuning Guide. * Collection of optimizer statistics is now performed by default, automatically for all schemas (including SYS), for pre-existing databases upgraded to 10g, and for newly created 10g databases. Gathering optimizer statistics on stale objects is scheduled by default to occur daily during the maintenance window. See Chapter 15, "Managing Optimizer Statistics" in Oracle Performance Tuning Guide. * See the Oracle Database Upgrade Guide for changes in behavior for the COMPUTE STATISTICS clause of CREATE INDEX, and for behavior changes in SKIP_UNUSABLE_INDEXES. UPGRADE/DOWNGRADE * After upgrading to 10g, the minimum supported release to downgrade to is Oracle 9i R2 release 9.2.0.3 (or later), and the minimum value for COMPATIBLE is 9.2.0. The only supported downgrade path is for those users who have kept COMPATIBLE=9.2.0 and have an installed 9i R2 (release 9.2.0.3 or later) executable. Users upgrading to 10g from prior releases (such as Oracle 8, Oracle 8i or 9iR1) cannot downgrade to 9i R2 unless they first install 9i R2. When upgrading to 10g, by default the database will remain at 9i R2 file format compatibility, so the on disk structures that 10g writes are compatible with 9i R2 structures; this makes it possible to downgrade to 9i R2. Once file format compatibility has been explicitly advanced to 10g (using COMPATIBLE=10.x.x), it is no longer possible to downgrade. See the Oracle Database Upgrade Guide. * A SYSAUX tablespace is created upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain. MANAGEABILITY * Database performance statistics are now collected by the Automatic Workload Repository (AWR) database component, automatically upon upgrade to 10g and also for newly created 10g databases. This data is stored in the SYSAUX tablespace, and is used by the database for automatic generation of performance recommendations. See Chapter 5, "Automatic Performance Statistics" in the Oracle Database Performance Tuning Guide. * If you currently use Statspack for performance data gathering, see section 1. of the Statspack readme (spdoc.txt in the RDBMS ADMIN directory) for directions on using Statspack in 10g to avoid conflict with the AWR. MEMORY * Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET is defaulted to 20% of the SGA size, unless explicitly set. Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading. See Chapter 14 of the Oracle Database Performance Tuning Guide. * Previously, the number of SQL cursors cached by PL/SQL was determined by OPEN_CURSORS. In 10g, the number of cursors cached is determined by SESSION_CACHED_CURSORS. See the Oracle Database Reference manual. * SHARED_POOL_SIZE must increase to include the space needed for shared pool overhead. * The default value of DB_BLOCK_SIZE is operating system specific, but is typically 8KB (was typically 2KB in previous releases). TRANSACTION/SPACE * Dropped objects are now moved to the recycle bin, where the space is only reused when it is needed. This allows 'undropping' a table using the FLASHBACK DROP feature. See Chapter 14 of the Oracle Database Administrator's Guide. * Auto tuning undo retention is on by default. For more information, see Chapter 10, "Managing the Undo Tablespace," in the Oracle Database Administrator's Guide. CREATE DATABASE * In addition to the SYSTEM tablespace, a SYSAUX tablespace is always created at database creation, and upon upgrade to 10g. The SYSAUX tablespace serves as an auxiliary tablespace to the SYSTEM tablespace. Because it is the default tablespace for many Oracle features and products that previously required their own tablespaces, it reduces the number of tablespaces required by Oracle that you, as a DBA, must maintain. See Chapter 2, "Creating a Database," in the Oracle Database Administrator's Guide. * In 10g, by default all new databases are created with 10g file format compatibility. This means you can immediately use all the 10g features. Once a database uses 10g compatible file formats, it is not possible to downgrade this database to prior releases. Minimum and default logfile sizes are larger. Minimum is now 4 MB, default is 50MB, unless you are using Oracle Managed Files (OMF) when it is 100 MB. PL/SQL procedure successfully completed. SQL> spool off
Database Upgrade Assistant (DBCA)
The Database Upgrade Assistant (DBUA) is a GUI tool that guides the user through the whole upgrade process, including all the steps listed in the manual upgrade process. The assistant is started using the dbua
command in UNIX and Linux environments or from the Start menu (Start > Programs > Oracle - HOME_NAME > Configuration and Migration Tools > Database Upgrade Assistant) in Windows environments.
Once the assistant has started it leads the user through the several steps including:
- Selecting the instance to upgrade.
- Analyzing the existing database to make sure it is suitable for upgrade.
- Creating the SYSAUX tablespace which is required for 10g.
- Deciding whether to recompile all invalid objects when the upgrade is complete.
- Selecting a backup option for the database.
- Deciding how the database should be managed (OEM Console or Grid Control) and defining the appropriate authentication.
- Defining the flash recovery area.
- Performing any necessary network configuration.
- Performing the upgrade process.
- Checking the upgrade results.
- Listing the changes in default behaviour between the old and new versionsof the database.
- Completing the upgrade procedure.
The DBUA can also be started in silent mode provided all the necessary parameters are provided.
STARTUP UPGRADE
The is a new startup mode associated with the upgrade procedure in Oracle 10g.
SQL> STARTUP UPGRADE;
For more information see:
- Oracle Database Upgrade Guide 10g Release 1 (10.1)
- Oracle Database Upgrade Guide 10g Release 2 (10.2)
- Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]
- Different Upgrade Methods For Upgrading Your Database [ID 419550.1]
Hope this helps. Regards Tim...