Upgrade database from 9i to 10g

Upgrade database from 9i to 10g


First you have to upgrade testing database from 9i to 10g. After that you will perform upgradtion activity in production database.


Example.
1.you create sample database in oracle 9i.2.create os user for oracle10g
su - root

groupadd dba # group of users to be granted SYSDBA system privilege

groupadd oinstall # group owner of Oracle files

useradd -c oracle10g -g oinstall -G dba oraclepasswd oracle


3.Download oracle 10g software from oracle web site then install oracle 10 in that server.

4.Then set environment variables for oracle10g.


For example 9i database name test.The database to be upgraded is called test.then copy the script from u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql to /tmp directory




Login to 9i database,


$sqlplus "/as sysdba"

Connected to:Oracle9i Enterprise Edition Release 9.2.0.6.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.6.0 - Production



Run utlu102i.sql

This utility script checks that the database is ready to be upgraded to 10g. It also identifies any actions that need to be taken. The script is located in the 10g oracle home, so you will need to specify the full path to it.
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql

Review the output and make any necessary alterations. Make a note of how many invalid objects there are.

Shut the database down with either normal or immediate
shutdown immediate

Copy the spfile (or pfile) and the password file from the existing home to the 10g one.

cp ${ORACLE_HOME}/dbs/*${ORACLE_SID}* /dbs/

Upgrade the database

sqlplus "/ as sysdba"

SQL> startup upgrade
ORACLE instance started.
Total System Global Area 398458880 bytes
Fixed Size 1261068 bytes
Variable Size 276824564 bytes
Database Buffers 117440512 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.
sql>

CREATE TABLESPACE sysaux
DATAFILE '/u01/ora9R2/oradata/sysaux01.dbf'
SIZE 1024M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

This next bit is the upgrade itself. It takes roughly half an hour to complete.
Spool the output to a file so that you can review it afterward.

@?/rdbms/admin/catupgrd.sql

Create a sysaux tablespace

Having a sysaux tablespace is a requirement in 10g. So, if you haven't already got one, create one now.

create tablespace sysaux
datafile '' size 512M
extent management local
segment space management auto
/

Recompile any invalid objects

SQL> select count(*) from dba_objects where status='INVALID';

@?/rdbms/admin/utlrp.sql

Then check the status of the upgrade

@?/rdbms/admin/utlu102s.sql