upgrade

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 oracle

passwd 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 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production



sql>spool /tmp/upgrade.txt

sql>@/tmp/utlu102i

Oracle Database 10.2 Upgrade Information Utility    06-20-2006 16:39:48

.

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

Database:

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

--> name:       test

--> version:    9.2.0.6.0

--> compatible: 9.2.0.0.0

--> blocksize:  8192

.

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

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: 439 MB

.... AUTOEXTEND additional space required: 109 MB

--> TEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

.... AUTOEXTEND additional space required: 18 MB

--> DRSYS tablespace is adequate for the upgrade.

.... minimum required size: 5 MB

--> XDB tablespace is adequate for the upgrade.

.... minimum required size: 49 MB

.... AUTOEXTEND additional space required: 4 MB

.

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

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

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

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

WARNING: --> "java_pool_size" needs to be increased to at least   67108864

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

.

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

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

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

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

.

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

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora 

                                                           or spfile]

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

--> "hash_join_enabled"

.

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

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 interMedia            [upgrade]  VALID

...The 'Oracle interMedia Image Accelerator' is

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

--> Spatial                      [upgrade]  VALID

.

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

Miscellaneous Warnings

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

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

....   CTXSYS

....   XDB

....   WMSYS

....   ORDSYS

....   MDSYS

.

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

SYSAUX Tablespace:

[Create tablespace in the Oracle Database 10.2 environment]

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

--> New "SYSAUX" tablespace

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

.



PL/SQL procedure successfully completed.



SQL> spool off

SQL> exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production



Under most circumstances, at this point, the database would be backed up. This demonstration will skip over that part of the upgrade



Next, we create a PFILE from our SPFILE that we can make changes to: 



SQL> create pfile from spfile;



File created.



SQL> exit

$ vi /u01/app/oracle/product/9.2.0/dbs/inittest.ora





Edit the pfile using this the diagnostic output as a guide. Here is my file:



*.background_dump_dest='/u01/app/oracle/admin/test/bdump'

*.compatible='9.2.0.0.0'

*.control_files='/u02/oradata/test/control01.ctl',

                '/u02/oradata/test/control02.ctl',

                '/u02/oradata/test/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/test/cdump'

*.db_block_size=8192

*.db_cache_size=117440512

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

#*.hash_join_enabled=TRUE

*.instance_name='test'

#*.java_pool_size=33554432

*.java_pool_size=67108864

*.large_pool_size=28311552

*.open_cursors=300

*.pga_aggregate_target=104857600

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.session_max_open_files=20

#*.shared_pool_size=38797312

*.shared_pool_size=177861837

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/app/oracle/admin/test/udump'







set pagesize 50000

tti "Database growth per month for last year"

select to_char(creation_time, 'RRRR Month') "Month",

       sum(bytes)/1024/1024 "Growth in Meg"

  from sys.v_$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month')

;