Manual Upgrade of a 10gr2 Database To a 11gr2 Database

Introduction
The goal of this article is to give an example of an manual upgrade from 10gr2 database to 11gr2 database.
In my example, the 10gr2 database is BDTEST.

A. Check list to do Source Database

 1 Clean up duplicate objects in the SYS and SYSTEM schemas 
Some objects created under SYS schema during the installation of the DB, can be created also under SYSTEM schema with manaul launch scripts (for example for replication).
Exception for :
            AQ$_SCHEDULES TABLE
            AQ$_SCHEDULES_PRIMARY INDEX
            DBMS_REPCAT_AUTH PACKAGE
            DBMS_REPCAT_AUTH PACKAGE BODY

1-1 To find duplicate objects, run as SYS:
   select object_name, object_type 
   from dba_objects  
   where object_name||object_type in 
   (select object_name||object_type  
    from dba_objects 
    where owner = 'SYS') 
    and owner = 'SYSTEM';



1-2 to drop duplicate objects, run as SYSTEM:
set pause off 
set heading off 
set pagesize 0 
set feedback off 
set verify off 
spool duplicate_objects.sql 
select 'DROP ' || object_type || ' SYSTEM.' || object_name || ';' 
from dba_objects 
where object_name||object_type in 
   (select object_name||object_type  
    from dba_objects 
    where owner = 'SYS')
and  object_name not in ('AQ$_SCHEDULES', 'AQ$_SCHEDULES_PRIMARY', 'DBMS_REPCAT_AUTH', 'DBMS_REPCAT_AUTH' )
and owner = 'SYSTEM'; 
spool off 
exit 

2. Purge Recylce bin
purge the recycle bin beforethe upgrade.
SQL> purge DBA_RECYCLEBIN;

3. Cluster
If CLUSTER_DATABASE is set to TRUE, change it to FALSE before upgrading the database.


4. Pre-Upgrade Tool
To prepare your 10gr2 database for upgrade, you must first download the scirpt utility utlu112i_sql.  Refer to My Oracle Support Document 884522.1: How to Download and Run Oracle's Database Pre-Upgrade Utility for details on downloading and running the Pre-Upgrade Utility. This SQL script checks the following:
1. Database configuration: Determines if the logfiles and datafiles are sized adequately for the upgrade
2. Initialization parameters: Reports which initialization parameters need changing, replacing or removing before the upgrade
3. Components: Which installed components will be upgraded
4. Miscellaneous Warnings: Any other situations requiring attention before the upgrade
5. Required tablespace: Ensure that the SYSAUX tablespace is created in the current database BEFORE the upgrade is carried out.
6. Timezone file version: Reports which file version is used and when/how to upgrade the timezone version.


Failure to run the pre-upgrade tool (utlu112i.sql) will result in the following error while running the catupgrd.sql script :

SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number










From source server (10gr2) as sys:

SQL> spool /u03/upgrade/utlu112i_1.log
SQL>  @/u03/upgrade/utlu112i_1.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 08-09-2012 11:25:35
Script Version: 11.2.0.1.0 Build: 004
**********************************************************************
Database:
**********************************************************************
--> name:          BDTEST
--> version:       10.2.0.5.0
--> compatible:    10.2.0.1.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1331 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 34 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 721 MB
--> TBS_ADMIN_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_STREAM tablespace is adequate for the upgrade.
.... minimum required size: 3 MB
--> TBS_TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest         11.1       DEPRECATED   replaced by  "diagnostic_dest"
--> user_dump_dest               11.1       DEPRECATED   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 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.5.0 database timezone version
.... to the latest version which comes with the new release.
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 BEMIS has 33 INVALID objects.
.... USER PUBLIC has 1 INVALID objects.
.... USER ADMINSTRM has 2 INVALID objects.
.... USER SYS has 2 INVALID objects.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> There are files which need media recovery.
.... Ensure no files need media recovery prior to upgrade.
WARNING: --> Sync standby database prior to upgrade.
WARNING: --> Your recycle bin contains 2359 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database.  The command:
        PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
    EXECUTE dbms_stats.gather_dictionary_stats;
**********************************************************************
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
    SELECT name,description from SYS.V$PARAMETER WHERE name
        LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'
  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init.ora or spfile.
**********************************************************************
SQL> spool off

Now, on the 10gr2 database BDTEST,  perform the recomendations from utlu112i_1.log.


5. Compile invalid objects :
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

6. Gather stats for tables that lacks statistics :

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

B. Upgrade

• Do a cold backup pf BDTEST (10gr2) and move all the files to the 11gr2 server.
• [oracle@**** ~]$ export ORACLE_SID=BDTEST
• on the 11gr2 serveur :

[oracle@**** ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 9 10:31:25 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.
SQL> startup UPGRADE;
SQL> spool catupgrd.log
SQL> @?/rdbms/admin/catupgrd.sql
......

SQL>

SQL>@?/rdbms/admin/utlrp.sql

SQL>shutdown immediate;

SQL>startup

Execute the script utlu112i.sql that provides a summury of the upgrade :
SQL>  @?/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool    08-09-2012 14:27:06

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

Database:

**********************************************************************
--> name:          BDTEST

--> version:       11.2.0.1.0

--> compatible:    11.2.0.0.0

--> blocksize:     8192

--> timezone file: V4

Database already upgraded; to rerun upgrade use rdbms/admin/catupgrd.sql.

PL/SQL procedure successfully completed.

SQL>

Main important changes
According to the oracle note Oracle 11gR2 Upgrade Companion [ID 785351.1], the main changes 
in behavior between 10g and 11g are :

New Background Processes (Oracle Database 10g to 11g Change)

The following are the new database background process that you could be
running depending upon the features being used.

• DBRM (database resource manager) process is responsible for setting resource plans and other resource manager related tasks.
• DIA0 (diagnosability process 0) (only 0 is currently being used) is responsible for hang detection and deadlock resolution.
• EMNC (event monitor coordinator) is the background server process used for database event management and notifications.
• FBDA (flashback data archiver process) archives the historical rows of tracked tables into flashback data archives. Tracked tables are tables which are enabled for flashback archive. When a transaction containing DML on a tracked table commits, this process stores the pre-image of the rows into the flashback archive. It also keeps metadata on the current rows.
• FBDA is also responsible for automatically managing the flashback data archive for space, organization, and retention and keeps track of how far the archiving of tracked transactions has occurred.
• GTX0-j (global transaction) processes provide transparent support for XA global transactions in an Oracle RAC environment. The database autotunes the number of these processes based on the workload of XA global transactions. Global transaction processes are only seen in an Oracle RAC environment
• GEN0 (general task execution process) performs required tasks including SQL and DML
• GMON maintains disk membership in ASM disk groups.
• MARK marks ASM allocation units as stale following a missed write to an offline disk.
• SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task.
• VKTM (virtual keeper of time) is responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms and available only when running at elevated priority
• VKRM (virtual scheduler for resource manager process) manages the CPU scheduling for all managed Oracle processes. The process schedules managed processes in accordance with an active resource plan.

Initialization Parameters (Oracle Database 10g to Oracle Database 11g Change)

1. OPTIMIZER_USE_INVISIBLE_INDEXES
OPTIMIZER_USE_INVISIBLE_INDEXES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of invisible indexes. The default
value for OPTIMIZER_USE_INVISIBLE_INDEXES is FALSE.

2. OPTIMIZER_USE_PENDING_STATISTICS
OPTIMIZER_USE_PENDING_STATISTICS was introduced in Oracle Database 11g
Release 1. This parameter specifies whether or not the optimizer uses pending
statistics when compiling SQL statements. The default value for
OPTIMIZER_USE_PENDING_STATISTICS is FALSE.

3. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES was introduced in Oracle Database
11g Release 1. This parameter enables or disables the automatic recognition of
repeatable SQL statements, as well as the generation of SQL plan baselines for such
statements. The default value for OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is
FALSE.

4. OPTIMIZER_USE_SQL_PLAN_BASELINES
OPTIMIZER_USE_SQL_PLAN_BASELINES was introduced in Oracle Database 11g
Release 1. This parameter enables or disables the use of SQL plan baselines stored in
SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for
the SQL statement being compiled. If one is found in SQL Management Base, then the
optimizer will cost each of the baseline plans and pick one with the lowest cost. The
default value for OPTIMIZER_USE_SQL_PLAN_BASELINES is TRUE.


Automatic Optimizer Statistics Collection

In Oracle Database 11g Automatic optimizer statistics collection runs as part of the automated
maintenance tasks infrastructure (AutoTask) and is enabled by default to run in all predefined
maintenance windows. The AutoTask statistics collection replaces the Oracle Database 10g
GATHER_STATS_JOB. Automatic optimizer statistics collection is enabled by default. If for some 

reason automatic optimizer statistics collection needs to be disabled, you can disable it using the 

DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
To re-enable automatic optimizer statistics collection, you can enable it using the ENABLE
procedure in the DBMS_AUTO_TASK_ADMIN package
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;


for complete information, please refer to the [ID 785351.1] note.