9i to 10g manual upgrade

9i TO 10g Manual Upgradation

Manual Upgrade

A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database 10g release.

Pre-Requisites

· Backup the 9i database

· Install oracle 10g software

Backup the database

After cleanly shutting down the instance following the analysis of the database, you should perform a full backup of the database.

Complete the following steps:

1. Sign on to RMAN:

rman "target / nocatalog"

2. Issue the following RMAN commands:

RUN {

ALLOCATE CHANNEL chan_name TYPE DISK;

BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;

BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';

}

Install oracle 10g software

· An Oracle Database release must be installed in a new Oracle home that is separate from previous releases of Oracle.

· The following are the note required to install the oracle 10g software

1. Release notes

2. Installation update notes

3. Installation guide

Upgrading to the New Oracle Database 10g Release

Run the Pre-Upgrade Information Tool:

· After you have installed the Oracle Database 10.2 software and any required patches, you should analyze your database before upgrading to the new Oracle Database 10g release. This is done by running the Pre-Upgrade Information Tool. This is a necessary step if you are upgrading manually.

· The Pre-Upgrade Information Tool is a SQL script that ships with the new Oracle Database 10g release, and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool.

1. Log in to the system as the owner of the Oracle home directory of the new Oracle Database 10g release.

2. Copy the following file from theORACLE_HOME/rdbms/admin directory of the new Oracle Database 10g release to a directory outside of the Oracle home, such as the temporary directory on your system

· Utlu102i.sql

· Utl2102s.sql

3. Log in to the system as the owner of the Oracle home directory of the database to be upgraded.(9i)

4. Copy the above files to $ORACLE_HOME/rdbms/admin directory.

5. Start SQL*Plus and Connect to the database instance as a user with SYSDBA privileges.

6. Set the system to spool results to a log file for later analysis:

SQL> SPOOL info.log

7. Run utlu102i.sql:

SQL> @/d01/ora9i/product/rdbms/admin/utlu102i.sql

8. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and examine the output of the upgrade information tool.

The following is the output generated by the Pre-Upgrade Information Tool:

Oracle Database 10.2 Upgrade Information Utility 08-06-2008 15:00:25

.

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

Database:

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

--> name: ORA9I

--> version: 9.2.0.8.0

--> compatible: 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: 572 MB

.... AUTOEXTEND additional space required: 162 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 18 MB

--> CWMLITE tablespace is adequate for the upgrade.

.... minimum required size: 16 MB

--> DRSYS tablespace is adequate for the upgrade.

.... minimum required size: 27 MB

.... AUTOEXTEND additional space required: 7 MB

--> EXAMPLE tablespace is adequate for the upgrade.

.... minimum required size: 150 MB

.... AUTOEXTEND additional space required: 1 MB

--> ODM tablespace is adequate for the upgrade.

.... minimum required size: 10 MB

--> XDB tablespace is adequate for the upgrade.

.... minimum required size: 48 MB

.... AUTOEXTEND additional space required: 3 MB

.

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

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

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

WARNING: --> "shared_pool_size" needs to be increased to at least 184572723

WARNING: --> "streams_pool_size" is not currently defined and needs a value of

at least 50331648

WARNING: --> "session_max_open_files" needs to be increased to at least 20

.

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

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

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

-- No deprecated parameters found. No changes are required.

.

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

Obsolete Parameters: [Update Oracle Database 10.2 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 Text [upgrade] VALID

--> Oracle XML Database [upgrade] VALID

--> Oracle Workspace Manager [upgrade] VALID

--> Oracle Data Mining [upgrade] VALID

--> OLAP Analytic Workspace [upgrade] UPGRADED

--> OLAP Catalog [upgrade] VALID

--> Oracle OLAP API [upgrade] UPGRADED

--> Oracle interMedia [upgrade] VALID

...The 'Oracle interMedia Image Accelerator' is

...required to be installed from the 10g Companion CD.

--> Spatial [upgrade] VALID

--> Oracle Ultra Search [upgrade] VALID

... To successfully upgrade Ultra Search, install it from

... the 10g Companion CD.

.

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

Miscellaneous Warnings

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

WARNING: --> Deprecated CONNECT role granted to some user/roles.

.... CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

.... XDB

.... WMSYS

.... ODM

.... OLAPSYS

.... MDSYS

.... WKSYS

.

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

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

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

--> New "SYSAUX" tablespace

.... minimum required size for database upgrade: 500 MB

9. Then make necessary changes as mentioned in the above report.

10. Copy the parameter file from 9i database to 10g database and make necessary changes. The parameters we have to add are

· “shared_pool_size” needs to be increased to at least 184572723

· “streams_pool_size" is not currently defined and needs a value of at least 50331648

· “session_max_open_files" needs to be increased to at least 20

· Compatible parameter should be changed to 10.2.0.1

11. Shut down the 9i database.

Upgrade the Database

1. make sure that your ORACLE SID is set correctly and that the following environment variables point to the new release 10.2 directories:

· ORACLE_HOME

· PATH

· ORA_NLS10

· LD_LIBRARY_PATH

2. Log in to the system as the owner of the Oracle home directory of the new Oracle Database 10g release.

3. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory.

4. Start sql*plus and connect to the database instance as a user with SYSDBA privileges.

5. Start up the instance by issuing the following command:

SQL> STARTUP UPGRADE

6. In Oracle Database 10g, the SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.

The SYSAUX tablespace must be created with the following mandatory attributes:

· ONLINE

· PERMANENT

· READ WRITE

· EXTENT MANAGEMENT LOCAL

· SEGMENT SPACE MANAGEMENT AUTO

7. The following SQL statement would create a 500 MB SYSAUX tablespace for the database:

SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'

SIZE 500M REUSE

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO

ONLINE;

8. Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log

9. Run catupgrd.sql:

SQL> @catupgrd.sql

10. The catupgrd.sql script determines which upgrade scripts need to be run and then runs each necessary script. You must run the script in the new release 10.2 environment.

The upgrade script creates and alters certain data dictionary tables.

11. Run utlu102s.sql to display the results of the upgrade:

SQL> @utlu102s.sql

The Post-upgrade Status Tool displays the status of the database components in the upgraded database and the time required to complete each component upgrade. The Upgrade Status Tool displays output

SQL> @/d01/mt/10g/10.2.0.1/rdbms/admin/utlu102s.sql

Oracle Database 10.2 Upgrade Status Utility 08-07-2008 15:24:11

Component status version HH:MM:SS

Oracle Database Server VALID 10.2.0.1.0 00:22:32

JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:07:57

Oracle XDK VALID 10.2.0.1.0 00:01:35

Oracle Database Java Packages VALID 10.2.0.1.0 00:00:41

Oracle Text VALID 10.2.0.1.0 00:01:06

Oracle XML Database VALID 10.2.0.1.0 00:01:52

Oracle Workspace Manager VALID 10.2.0.1.0 00:01:06

Oracle Data Mining VALID 10.2.0.1.0 00:00:19

OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:23

OLAP Catalog VALID 10.2.0.1.0 00:01:16

Oracle OLAP API VALID 10.2.0.1.0 00:00:58

Oracle interMedia VALID 10.2.0.1.0 00:05:51

Spatial VALID 10.2.0.1.0 00:04:16

Oracle Ultra Search NO SCRIPT 9.2.0.8.0 00:00:00

.

Total Upgrade Time: 00:53:00

14. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and verify that the packages and procedures compiled successfully.

15. Shut down and restart the instance to reinitialize the system parameters for normal operation.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

16. Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies. This step is only necessary if Oracle Label Security is in your database.

17. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.

SQL> @utlrp.sql

18.Verify that all expected packages and classes are valid.

19. Exit SQL*Plus.

Your database is now upgraded to the new Oracle Database 10g release.

Starting the dbconsole:

· Configure the listener. Ora file using netmgr.

· Configure the tnsnames.ora file using netca.(The database should be up and running).

· Inorder to start the dbconsole, first we have to start the listener and database and execute following command:

Emca –config dbcontrol db –repos create