Upgrade for 8.1.7.0.0 or 8.1.7.4.1 to 10g release 2 (10.2.0.1.0)

Note: If you want to upgrade 8.1.7.0.0, you must apply patch 2376472 (Down load from Metal ink) or you can upgrade directly from 8.1.7.4.1


We can use three methods to upgrade our oracle database.


Export / Import
Database Upgrade Assistant
Manually by using Scripts (We prefer manually method to upgrade our database)


Step 1 (Pre upgrade Task)


a) Install the oracle 10g database software in new oracle home.
b) Connect to the oracle database form 9i and run pre-upgrade scripts (utlu102i), which is store in Oracle 10 g Home/rdbms/admin.
c) Follow the steps suggested from output of above steps.



Output will show you following information:
Database Name, Version and Compatible



Log files Section: Log file should not less then 4MB. If log file less the 4 MB then Create additional log file as per suggested size (15MB) and drop the smaller ones.


Tablespaces Section: This section Show you required size of System, TEMP and DRSYS tablesapce. If size is not enough then you must increase size of tablespace.



For system tablespace minimum required size is 598 MB
For TEMP Tablespace minimum required size is 59 MB
For DRSYS tablespace minimum required size is 5 MB


Rollback Segments Section: This Section show you required size of rollback segments Rollback Segments must be 70MB. if size less then 70 MB alter storage clause MAXEXTENTS UNLIMITED by using following Command:


ALTER ROLLBACK SEGMENT

Parameter section: This will suggest you Paameter for 10g

"compatible" must be set to at least 9.2.0
"shared_pool_size" needs to be increased to at least 187332895
"java_pool_size" needs to be increased to at least 67108864
"streams_pool_size" is not currently defined and needs a value of at least 50331648
"large_pool_size" needs to be increased to at least 8388608
"pga_aggregate_target" is not currently defined and needs a value of at least 25165824
"session_max_open_files" needs to be increased to at least 20

Deprecated Parameter Section: This will show you Deprecated Parameters "mts_dispatchers" new name is "dispatchers"

Obsolete Parameters Section: This will show you Obsolete Parameter

"job_queue_interval"
"distributed_transactions"
"oracle_trace_collection_name"
"max_enabled_roles"

Component Section: This section show you database component will be upgraded or installed.

Oracle Catalog Views [upgrade]
Oracle Packages and Types [upgrade]
JServer JAVA Virtual Machine [upgrade]
The 'JServer JAVA Virtual Machine' JAccelerator (NCOMP) is required to be installed from the 10g Companion CD.
Oracle XDK for Java [upgrade]
Oracle Java Packages [install]
Oracle Text [upgrade]
Oracle XML Database [install]
Oracle interMedia [upgrade]
The 'Oracle interMedia Image Accelerator' is required to be installed from the 10g Companion CD.
Spatial [upgrade]

Miscellaneous Warnings Section:

WARNING: --> Your database is using an obsolete NCHAR character set.
In Oracle Database 10g, the NCHAR data types (NCHAR, NVARCHAR2, and NCLOB)
are limited to the Unicode character set encoding (UTF8 and AL16UTF16), only.
See "Database Character Sets" in chapter 5 of the Oracle 10g Database Upgrade
Guide for further information.

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

CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains INVALID objects prior to upgrade.

USER MDSYS has 4 INVALID objects.

USER SYS has 41 INVALID objects.

SYSAUX Tablespace Section: Create tablespace in the Oracle Database 10.2 environment. New "SYSAUX" tablespace minimum required size for database upgrade: 500 MB

Step 2 (As per instruction of above output, you must change database and initSID file.)

1. Shutdown the 8i instance

SQL>SHUTDOWN IMMEDIATE

2. Stop the OracleServiceSID Oracle service of the oracle 8i database

C:\> NET STOP OracleService

3. Delete the OracleServiceSID at command line of 8i Home

C:\>ORADIM –DELETE –SID

Step 3

1.Create the new oracle database 10g service at command prompt using the following command.

C:\>ORADIM –NEW –SID –INTPWD –STARTMODE A

2. Put your init file in database folder at new oracle 10g home from 8i.

Step 4

1. Connect to the new oracle 10g instance as a user SYSDBA privilege and issue following command:

SQL>STARTUP UPGRADE

You don’t need to use the PFILE option to specify the location of your initialization parameter file in our case because we are using INIT file in default location (which is reside in Oracle10gHome/database). We have just put init file at new oracle 10g home from 8i.

IMPORTANT:

The error may be occur, when you attempting to start the new oracle Database 10g release. If you receive, issue the SHUTDOWN ABORT commands to shut down the database and correct the problem.

Step 5

Create SYSAUX table space for the database.
CREATE TABLESPACE sysaux DATAFILE ‘sysaux01.dbf’
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

NOTE:

If you are upgrading from 10.1, then skip step5 otherwise create SYSAUX tablespace. In oracle 10g , the SYSAUX tablespace is used to consolidate data from a number of tablespace that where separated in previous release.

Step 6

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

SQL> SPOOL upgrade.log

2. Run the upgrade scripts.

SQL>@catupgrd.sql

The catupgrd.sql script determines which scripts need to be run and then runs each necessary scripts.

3. Run the result of the upgrade display report.

SQL>@utlu102s.sql

The Post-upgrade status Tool display the status of the database components in the upgrade database and time required to complete each component upgrade.

4. Turn off the spooling of script result to the log file

SQL>spool off;

5. Shutdown the instance and restart.

SQL> STHTDOWN IMMEDIATE

SQL> STARTUP

Step 7 (Post migration Task)

1. Remove the obsolete initialization parameter from parameter file.

2. Check Invalid objects in database by using following Query:

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

3. Run utlrp.sql scripts to recompile any remaining stored Pl/SQL and java codes.

SQL > @utlro.sql

4. Upgrade User NCHAR Columns (Tasks to Complete Only After Upgrading a Release 8.1.7 Database)

If you upgraded from a version 8 release and your database contains user tables with NCHAR columns, you must upgrade the NCHAR columns before they can be used in the Oracle Database.

You will encounter the following error when attempting to use the NCHAR columns in the Oracle Database until you perform the steps in this section:

ORA-12714: invalid national character set specified

To upgrade user tables with NCHAR columns, perform the following steps:

1. Connect to the database instance as a user with SYSDBA privileges.

2. If the instance is running, shut it down using SHUTDOWN IMMEDIATE:

SQL> SHUTDOWN IMMEDIATE

3. Start up the instance in RESTRICT mode:

SQL> STARTUP RESTRICT

4. Run utlnchar.sql:

SQL> @utlnchar.sql

Alternatively, to override the default upgrade selection, run n_switch.sql:

SQL> @n_switch.sql

5. Shut down the instance:

SQL> SHUTDOWN IMMEDIATE

6. Exit SQL* PLUS