Upgrade from Oracle Database 10.2.0.2 to 11.2.0.2 (In 2014)

database upgrade from 10g to Oracle 11g.

Pre-Requisite:

You should have the Oracle database 10g, which you want to migerate.
Also here we are upgrading to Oracle Database 11g – (11.2.0.2)

Part A.
Install the New Oracle Database Software & Apply any patches necessary.
We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.

Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0

then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.2.0

Just a parallel 11.2.0 directory can be created and we can install oracle home in this location.

Start the installation using the below command

./runInstaller

Screen 1 – Select Product Install

select “Oracle Database 11g”

Screen 2 – Select Installation Method
Choose “Advanced Installation”

Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.

Screen 4 – Select Installation Type
Choose “Enterprise Edition”

Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME

Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further

Screen 7 – Upgrade an Existsing Database
Choose “No”

Screen 8 – Select Configuration Option
Choose “Install Software Only”

Screen 9 – Privileged system groups

Based on the group of oracle user, this value has to be set.

Screen 10 – 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.

1.      BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED

2.      COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0.  THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.

Part B.
 Run the Pre-Upgrade Information Tool (You Must Run This tool)

1.      Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2.      Set your environment to the one that is being upgraded.  Assuming 10g.
3.      Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4.      Start SQL*Plus and login as ‘/ as sysdba’
5.      Spool the results to a log file:
o   SQL> SPOOL upgrade_info.log
6.      Run the Pre-Upgrade Information Tool:
o   SQL> @utlul12i.sql
o   SQL> SPOOL OFF
Check the output of the Pre-Upgrade Information Tool in upgrade_info.log.
The following is an example of the output generated by the Pre-Upgrade Information Tool:


Oracle Database 11.2 Pre-Upgrade Information Tool 10-14-2008 23:25:25
.
**********************************************
Database:
**********************************************
--> name: ORCL
--> version: 10.2.0.2.0
--> compatible: 10.2.0.2
--> blocksize: 8192
Run the Pre-Upgrade Information Tool
Upgrading to the New Release 3-9
--> platform: Linux IA (32-bit)
--> timezone file: V2
.
***********************************************
Tablespaces: [make adjustments in the current environment]
**********************************************
WARNING: --> SYSTEM tablespace is not large enough for the upgrade.
.... currently allocated size: 560 MB
.... minimum required size: 910 MB
.... increase current size by: 350 MB
.... tablespace is NOT AUTOEXTEND ENABLED.




--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 457 MB
.... AUTOEXTEND additional space required: 352 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 617 MB
.... AUTOEXTEND additional space required: 287 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
***********************************************
WARNING: --> "sga_target" needs to be increased to at least 388 MB
.
**********************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
***********************************************
WARNING: --> "plsql_compiler_flags" old value was "INTERPRETED";
new name is "plsql_code_type" new value is "INTERPRETED"
.
***********************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
***********************************************
--> "max_enabled_roles"
--> "remote_os_authent"
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_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
--> Messaging Gateway [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle Label Security [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
Run the Pre-Upgrade Information Tool
3-10 Oracle Database Upgrade Guide
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade]
--> Oracle OLAP API [upgrade] VALID
.
***********************************************
Miscellaneous Warnings
***********************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.2.0 database timezone version
.... to the latest version which comes with the new release.
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
.... WMSYS
.... CTXSYS
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 7 INVALID objects.
.... USER FLOWS_010600 has 1 INVALID objects.
.... USER SYS has 1 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 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.
.
WARNING: --> There are materialized view refreshes in progress.
.... Ensure all materialized view refreshes are complete prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> There are files in backup mode.
.... Ensure no files are in backup mode prior to upgrade.
WARNING:--> There are outstanding unresolved distributed transactions.
.... Resolve outstanding distributed transactions prior to upgrade.
WARNING:--> A standby database exists.
.... Sync standby database prior to upgrade.
WARNING: --> log_archive_format must be updated.
.... As of 10.1, log_archive_format requires a %r format qualifier
.... be present in its format string. Your current setting is:
.... log_archive_format='%t_%s.dbf'.
.... Archive Logging is currently OFF, but failure to add the %r to the
.... format string will still prevent the upgraded database from starting up.
WARNING:--> recycle bin in use.
.... Your recycle bin is turned on and it contains
.... 3 object(s). It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
Run the Pre-Upgrade Information Tool
Upgrading to the New Release 3-11
.... The command: PURGE DBA_RECYCLEBIN
1. .... must be executed immediately prior to executing your upgrade.


Check the warning messages and rectify them as needed.   The following points must be addressed correctly otherwise there maybe problems.
Example:  CONNECT role now only has create session privilege. 
If you have any db links with passwords, you may want to back up the sys.link$ table to save the information.
 Oracle recommends gathering stats before the upgrade:  EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin

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

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

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
———-
2

If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.

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.

Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.

If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.

For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script &timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls
readme.txt timezlrg.dat timezone.dat

Backup $ORACLE_HOME/oracore/zoneinfo directory

[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup

Copy the .dat files

[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo

Bounce the database and check the TIMEZONE version again

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

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

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.

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.

Part C:
Prepare the new 11gR2 ORACLE_HOME
1.      Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2.      Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3.      Update any relative path names in parameter file to fully path names.


Part D:
Upgrade the Database

1.      Shutdown the database:
   SQL> SHUTDOWN IMMEDIATE;

2.      Make sure the following checks:
   The oratab file points to Oracle Database 11g Release 2 Oracle Home
   The following environment variables point to the Oracle 11g Release 2 directories:
§  ORACLE_HOME
§  PATH

3.      Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
   sqlplus ‘/ as sysdba’

4.      Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
   SQL>  STARTUP UPGRADE;

5.    Run the catupgrd.sql script:
   SQL>  SPOOL upgrade.log
   SQL>  @catupgrd.sql
   SQL>  SPOOL OFF

6.      Once completed, shutdown the database and restart it.
   SQL>  SHUTDOWN IMMEDIATE;
   SQL>  STARTUP;

7.      Run the Post-Upgrade Status Tool to provide a summary of the upgrade.
 (If there are any INVALID components, then check upgrade manual for fixes.)
   SQL> @utlul12s.sql

Oracle Database 11.2 Post-Upgrade Status Tool 23-02-2011 05:22:40
Component Status Version HH:MM:SS

Oracle Server . VALID 11.2.0.2.0 00:19:02
JServer JAVA Virtual Machine. VALID 11.2.0.2.0 00:02:55
Oracle Workspace Manager. VALID 11.2.0.2.0 00:00:54
OLAP Analytic Workspace. VALID 11.2.0.2.0 00:00:26
OLAP Catalog. VALID 11.2.0.2.0 00:00:58
Oracle OLAP API. VALID 11.2.0.2.0 00:00:25
Oracle Enterprise Manager. VALID 11.2.0.2.0 00:11:00
Oracle XDK. VALID 11.2.0.2.0 00:00:53
Oracle Text. VALID 11.2.0.2.0 00:00:50
Oracle XML Database. VALID 11.2.0.2.0 00:03:52
Oracle Database Java Packages. VALID 11.2.0.2.0 00:00:21
Oracle Multimedia. VALID 11.2.0.2.0 00:04:25
Spatial. VALID 11.2.0.2.0 00:05:18
Oracle Expression Filter. VALID 11.2.0.2.0 00:00:13
Oracle Rules Manager. VALID 11.2.0.2.0 00:00:12
Gathering Statistics. 00:04:03

Total Upgrade Time: 00:55:57

PL/SQL procedure successfully completed.

8.      Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
    SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9.      Run utlrp to recompile any remaining stored PL/SQL and other objects.
    SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10.  Verify that all objects are valid:
   SQL> SELECT count(*) FROM dba_invalid_objects;
   SQL> SELECT distinct object_name FROM dba_invalid_objects;

This completes the upgrade.