Oracle 11g Manual Up gradation

Oracle 11g Manual Up gradation

  1. Install Oracle 11g software only.Do not create any Database on it.
    Choose Advanced Installation

     Choose No

    Choose Software only

    It will Take 10-15 minutes to install on machine

   2. Connect on Oracle 10g Database.Execute script and spool on for collect the information on database up gradation pre upgradation requirement.

    3. After executing script open the file 

      Oracle Database 11.1 Pre-Upgrade Information Tool    05-09-2012 16:43:54      
--> name:          ORACLE10                                                   
--> version:                                                 
--> compatible:                                                 
--> blocksize:     8192                                                       
--> platform:      Microsoft Windows IA (32-bit)                              
--> timezone file: V2                                                         
Tablespaces: [make adjustments in the current environment]                    
--> SYSTEM tablespace is adequate for the upgrade.                              
.... minimum required size: 723 MB                                              
.... AUTOEXTEND additional space required: 243 MB                               
--> UNDOTBS1 tablespace is adequate for the upgrade.                            
.... minimum required size: 464 MB                                              
.... AUTOEXTEND additional space required: 394 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                                               
--> EXAMPLE tablespace is adequate for the upgrade.                             
.... minimum required size: 78 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 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                                                                    
....   OLAPSYS                                                                
....   SYSMAN                                                                 
....   CTXSYS                                                                 
....   XDB                                                                    
WARNING: --> Database contains schemas with objects dependent on network      
.... 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.

4. In Step 3,Those red lined warning need to focus.

Increase the Tablespace size  as per requirement.

Remove *_dump  from Parameter File as it obsolete.

Patch need to apply for upgrade the timezone info.

If it is less than 4 then need to apply patch.

Check it by executing query..

select version from v$timezone_file; 


Download Patch set from


Copy the @@p5731187_10105_WINNT\5731187\files\oracore\zoneinfo\

Files to @@\db_1\oracore\zoneinfo

Take backup of zoneinfo folder before replace this file.

Stop the services of oracle if replace get error .

Restart services and again database and execute query to check the update on timezone.

SQL> select version from v$timezone_file;


5.Create PFILE for Upgraded Oracle 11g Database.

create pfile='C:\INITORACLE10.ora' from spfile;

Edit  INITORACLE10.ora

Change  *.compatible='10.2.0'  to *.compatible='11.1.0'




6. Copy this updated pfile to new oracle 11g home.

7.Take full Database backup.If any wrong with up gradation then restore directly

8. Shut down Database completely.

9.Create new SID by Oradim.Drop all other SID for those database need to update.

oradim -delete -sid oracle10
Instance deleted.

--Set New Oracle11g Home

set oracle_sid=D:\app\Administrator\product\11.1.0\db_1

oradim -new -sid oracle10 -intpwd bhavesh -startmode auto

Instance created.

10.Change the directory from command prompt

cd @@app\Administrator\product\11.1.0\db_1\admin 

Connect iSQLPLUS  /nolog

@@app\Administrator\product\11.1.0\db_1\admin>set oracle_sid=oracle10@@app\Administrator\product\11.1.0\db_1\admin>sqlplus / as sysdba

SQL*Plus: Release - Production on Wed May 9 18:17:06 2012

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

Connected to an idle instance.

11.Start Database with newly created and modified pfile

 >startup upgrade pfile='D:\app\Administrator\product\INITORACLE10.ora'

ORACLE instance started.

Total System Global Area  610992128 bytes
Fixed Size                  1334884 bytes
Variable Size             159383964 bytes
Database Buffers          444596224 bytes
Redo Buffers                5677056 bytes
Database mounted.
Database opened.

12.Start spooling for database upgrade information.

 spool C:\upgrade11g.log

13.Execute catupgrd.sql script from /rdbms/admin.

14.Execute post upgrade script utlu111s.sql script

15.Execute UTLRP.sql to valided objects.

16.Collect Fresh Statistics.




 17.Check Updated version.

No comments:

Post a Comment

Dear User,

Thank you very much for your kind response