Oracle Database 10.2.0 to 11.1.0 Upgrade


Prerequisite: You should have the Oracle database 10g which you want to migrate. Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)

Steps 1:
Installing Oracle 11g Home :

My existing 10g Oracle Home /u01/app/oracle/oracle/product/10.2.0
New 11g Oracle Home : /u01/app/oracle/oracle/product/11.1.0
Install Oracle 11g Database Software

          ./runInstaller

Select Installation Method – Choose “Advanced Installation”
Specify Inventory directory and credentials.

Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Select Installation Type – Choose “Enterprise Edition”
Installation Location – Oracle Base as parent directory of ORACLE HOME
Product Specific Pre-requisite Checks – It may gives below warning, we can ignore and proceed further.

Upgrade an Existing Database – Choose “No”
Select Configuration Option – Choose “Install Software Only”
Privileged system groups – Based on the group of oracle user, this value has to be set.

Summary – Click on “Install”
At the end of installation, installer will ask to run root.sh script. Do not press OK button. Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.

Step 2:
Pre-Upgrade Utility :

>> In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
>> Then login to the 10g oracle database and run the above sql you copied.

NOTE:

Oracle Database 11.1 Pre-Upgrade Information Tool 03-10-2013 22:28:31
**********************************************************************
Database:
**********************************************************************

–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2

**********************************************************************
Tablespaces: [make adjustments in the current environment] **********************************************************************

–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 720 MB
…. AUTOEXTEND additional space required: 240 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 469 MB
…. AUTOEXTEND additional space required: 444 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 411 MB
…. AUTOEXTEND additional space required: 181 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
…. AUTOEXTEND additional space required: 41 MB

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

— No update parameter changes are required.

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

— No renamed parameters found. No changes are required.

**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile] **********************************************************************

–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”

**********************************************************************
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
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> OLAP Analytic Workspace [upgrade] VALID
–> OLAP Catalog [upgrade] VALID
–> EM Repository [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Spatial [upgrade] VALID
–> Data Mining [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
–> Oracle OLAP API [upgrade] VALID

**********************************************************************
Miscellaneous Warnings
**********************************************************************

WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
WARNING: –> Database contains stale optimizer statistics.
…. Refer to the 11g Upgrade Guide for instructions to update
…. statistics prior to upgrading the database.
…. Component Schemas with stale statistics:
…. SYS
…. SYSMAN
…. XDB

WARNING: –> Database contains schemas with objects dependent on network
packages.

…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER SYSMAN has dependent objects.

WARNING: –> EM Database Control Repository exists in the database.

…. Direct downgrade of EM Database Control is not supported. Refer to the
…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

PL/SQL procedure successfully completed.

>> The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.

Step 3:
Executing the recommended steps :

>> Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home.

NOTE :
WARNING: –> Database is using an old timezone file version.
…. Patch the 10.2.0.1.0 database to timezone file version 4
…. BEFORE upgrading the database. Re-run utlu111i.sql after
…. patching the database to record the new timezone file version.
>> Finding the Version of existing timezone files:

SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 2
>> Check the database version.

SQL> select banner from v$version;
BANNER
—————————————————————
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production

>> For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.
>> Once you identify the correct patchset (5632264 for 10.2.0), download the same and unzip it.
>> $ unzip p5632264_10202_LINUX.zip
>> $ cd files/oracore/zoneinfo
>> readme.txt timezlrg.dat timezone.dat
>> Backup $ORACLE_HOME/oracore/zoneinfo directory from 10g home.
>> Copy the files from patch 5632264 to 10g ORACLE_HOME/oracore/zoneinfo/.
>> SQL> select * from v$timezone_file;

FILENAME VERSION
———— ———-
timezlrg.dat 4
SQL> SELECT CASE COUNT(DISTINCT(tzname))
WHEN 183 then 1
WHEN 355 then 1
WHEN 347 then 1
WHEN 377 then 2
WHEN 186 then case COUNT(tzname) WHEN 636 then 2 WHEN 626 then 3 ELSE 0 end
WHEN 185 then 3
WHEN 386 then 3
WHEN 387 then case COUNT(tzname) WHEN 1438 then 3 ELSE 0 end
WHEN 391 then case COUNT(tzname) WHEN 1457 then 4 ELSE 0 end
WHEN 392 then case COUNT(tzname) WHEN 1458 then 4 ELSE 0 end
WHEN 188 then case COUNT(tzname) WHEN 637 then 4 ELSE 0 end
WHEN 189 then case COUNT(tzname) WHEN 638 then 4 ELSE 0 end
ELSE 0 end VERSION
FROM v$timezone_names;
VERSION
———
4

>> Gather Dictionary stats:
>> Connect as sys user and gather statistics
>> SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
>> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);
PL/SQL procedure successfully completed.
>> SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);
PL/SQL procedure successfully completed.

Step 4:
Run Pre-Upgrade Utility again :

>> After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.
>> Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
>> If every thing looks fine, Shut down the database from 10g Oracle Home
>> This time make sure you dont have the critical warnings like the one with TIMEZONE version.

Step 5:
Starting Upgrade :

Source the following variables for 11g Oracle Home

>> Copy the parameter files from 10gORACLE_HOME/dbs directory to 11gORACLE_HOME/dbs directory.
>> $export ORACLE_HOME=/u01/oracle/product/11.1.0
>> $ export PATH=$ORACLE_HOME/bin:$PATH
>> $ export TNS_ADMIN=$ORACLE_HOME/network/admin
>> $ export ORACLE_SID=orcl
>> sqlplus sys as sysdba -à will be connected to idle instance

SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.

SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
>> Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
>> Check the dba_registry for the components and its status.

Step 6:
Post-Upgrade Steps :

>> Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
>> Run utlu111s.sql to display the results of the upgrade.
SQL> @?/rdbms/admin/utlu111s.sql

Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
Component Status Version HH:MM:SS
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57
PL/SQL procedure successfully completed.
>> Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @?/rdbms/admin/catuppst.sql
>> Run utlrp.sql to recompile
SQL> select count(*) from dba_objects where status = ‘INVALID’;
COUNT(*)
———
1576
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———
0
This completes the upgrade.

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *