Upgrading to Oracle Database 10g

 

Oracle has made the process of upgrading from Oracle8i or Oracle9idatabases simple by automating a large portion of the upgrade process.

Here are the upgrade paths that you can take to move up to Oracle Database 10gRelease 2 (10.2):

• If you are moving from Oracle 7.3.3 or lower, or Oracle 7.3.4, 8.0.3, 8.0.4, 8.0.5, 8.0.6, 8.1.5, or 8.1.6, you can’t upgrade directly to Oracle Database 10g.

• If you are using an Oracle 8.1.7, 9.0.1, 9.2, or 10.1 database, you can upgrade directly.

Steps For Upgrading

1.) Install 10g Binaries on a separate ORACLE_HOME

2.) Patch 10g Binaries to the latest Patchset (à 10.2.0.4)

3.) Set env of 9i ORACLE_HOME

4.) Connect to 9i DB

5.) Create a new tablespace called SYSAUX

1. Of size 500MB
2. Automatic segment space management
3. Locally managed

SQL> CREATE TABLESPACE sysaux DATAFILE ‘/oracle/oradata/prod/sysaux01.dbf’

SIZE 500M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

6.) Increase the size of system tablespace (Min 500MB à 1Gig)

7.) Shutdown DB & lsnr

8.) Take a cold backup of the database

9.) Set env to 10g ORACLE_HOME

1. Ensure PATH is pointing to 10g ORACLE_HOME
2. Export the SID to the above DB

10.) cp init<SID>.ora or spfile<SID>.ora from 9i ORACLE_HOME to 10g ORACLE_HOME

11.) run pre 10g verification steps

1. cd 10g ORACLE_HOME/rdbms/admin
2. sqlplus ‘/as sysdba’
3. startup migrate
4. Pre-Upgrade Information Tool will do a lot of the work for you. Just make sure that you implement the recommended changes and you will be ready to upgrade to Oracle Database 10g.

The Pre-Upgrade Information Tool is actually a SQL script, called utlu102i.sql (in $ORACLE_HOME/rdbms/admin). Here’s how you invoke the Pre-Upgrade Information Tool:

SQL> @ $ORACLE_HOME/rdbms/admin/utlu102i.sql

Oracle Database 10.2 Upgrade Information Tool

02-20-2005 16:45:48

Database:

———

–> name: FINANCE

–> version: 8.1.7.0.0

–> compatibility: 8.1.0

WARNING: Database compatibility must be set to 9.2.0 prior to upgrade.

. . .

PL/SQL procedure successfully completed.

1. Shutdown immediate

12.) Partial Output of the Pre-Upgrade Information Tool

*************************************************************************

Database:

–> name: FINANCE

–> version: 8.1.7.0.0

–> compatibility: 8.1.0

WARNING: Database compatibility must be set to 9.2.0 prior to upgrade.

***********************************************************************

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

WARNING: –> “shared_pool_size” needs to be increased to at least “150944944″

*************************************************************************

Obsolete Parameters: [Update Oracle Database 10.2 init.ora or spfile]

–> “job_queue_interval”

–> “max_enabled_roles”

*************************************************************************

Components: [The following database components will be upgraded or installed]

–> Oracle Catalog Views [upgrade]

–> Oracle Packages and Types [upgrade]

…

*************************************************************************

SYSAUX Tablespace: [Create tablespace in Oracle Database 10.2 environment]

–> New “SYSAUX” tablespace

…. minimum required size for database upgrade: 500 MB

Please create the new SYSAUX Tablespace AFTER the Oracle Database

10.2 server is started and BEFORE you invoke the upgrade script.

Make all the changes pointed out by the Pre-Upgrade Information Tool before proceeding with

the upgrade.

13.) Verify output and make necessary checks as recommended in the init file and/or tbs file sizes

14.) Perform dbupgrade

Sqlplus ‘/as sysdba’

Startup migrate

Run the upgrade script

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Oracle will create, drop, and alter some database objects as the upgrade process progresses, thus invalidating some internal Oracle packages and procedures. After the upgrade script has finished, you need to check for invalid objects:

SQL> SELECT count(*) FROM DBA_OBJECTS

2     WHERE status = ‘INVALID’;

You can recompile all invalidated Oracle database objects using the utlrp.sql script:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

15.)   Upgrade java

1. Create or replace java system

16.) The upgrade status of each database component by querying the DBA_SERVER_REGISTRY view.

Querying the DBA_SERVER_REGISTRY View for Post-Upgrade Information

SQL> SELECT comp_id, comp_name, version, status

FROM DBA_SERVER_REGISTRY;

COMP_ID      COMP_NAME-                                            VERSION      STATUS

—————-    —————————————————-       —————     ————

CATALOG     Oracle Database Catalog Views               10.2.0.0.0      VALID

CATPROC    Oracle Database Packages and Types   10.2.0.0.0      VALID

JAVAVM        JServer JAVA Virtual Machine                   10.2.0.0.0      VALID

CATJAVA      Oracle Database Java Packages              10.2.0.0.0      VALID

CONTEXT     Oracle Text                                                     10.2.0.0.0      VALID

17.) Now shut down and restart the instance so you’re ready for normal database operations.

18.) Create new listener and tns files

19.) Gather schema and dictionary stats

20.) Perform the cold backup of the database