Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | 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:

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:

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:

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:

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:

Hope this helps. Regards Tim...

Back to the Top.