{"id":1021,"date":"2009-01-22T08:28:14","date_gmt":"2009-01-22T12:28:14","guid":{"rendered":"http:\/\/onlineappsdba.com\/index.php\/2009\/01\/22\/upgrade-oracle-database-10g-to-11g-r1-111x\/"},"modified":"2009-01-23T16:30:30","modified_gmt":"2009-01-23T20:30:30","slug":"upgrade-oracle-database-10g-to-11g-r1-111x","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2009\/01\/22\/upgrade-oracle-database-10g-to-11g-r1-111x\/","title":{"rendered":"Upgrade Oracle Database 10g to 11g R1 (11.1.X)"},"content":{"rendered":"

This post covers step by step upgrade of Oracle Database from version 10g (10.2.0.1) to 11g R1 (11.1.0.6) on Unix. You can use this document (with few additional steps mentioned in upgrade guide) to upgrade from 9i or for database on Windows Operating System.
\n.<\/p>\n

Important points before you start 11g upgrade
\n<\/strong><\/u>
\n1.<\/strong> Read Chapter 1, 2 & 3 of Database Upgrade Guide here here<\/a>
\n2<\/strong>. Read 11g Installation Guide for your operating system from here<\/a>
\n3.<\/strong> Direct Upgrade is possible from 10.2.0.1 and higher
\n4.<\/strong> There are two methods to upgrade database
\na)<\/strong> Database Upgrade Assistant<\/strong> (DBUA) and
\n\u00a0 b)<\/strong> Manual Upgrade<\/strong>
\nWe are going to use Manual Upgrade
\n5.<\/strong> Install 11g database software in different ORACLE_HOME from source Database<\/p>\n

.<\/p>\n

Upgrade Steps<\/strong><\/u><\/p>\n

1. Prepare to Upgrade<\/strong><\/p>\n

1.1<\/strong> Install 11g in different (new) ORACLE_HOME with following consideration
\na)<\/strong> When prompted for Upgrade an Existing Database (Select NO)<\/p>\n

<\/a><\/p>\n

b)<\/strong> On Select Configuration Option<\/strong>\u00a0 select Install Software Only<\/p>\n

<\/a><\/p>\n

.
\nUse Installation Guide from here<\/a>
\n1.2<\/strong> Analyze database using pre-upgrade information tool ( utlu111i.sql sql<\/strong> script available at 11g_oracle_homne\/rdbms\/admin\/utlu111i.sql\u00a0 run it from source database i.e. 10g)<\/strong><\/font><\/p>\n

sqlplus “\/as sysdba”
\nSQL>spool upgrade_info.log
\nSQL>@\/11g_oracle_home\/rdbms\/admin\/utlu111i.sql<\/font><\/p>\n

1.3<\/strong> Check the output of the Pre-Upgrade Information Tool in upgrade_info.log <\/strong>and fix any issues<\/p>\n

Obsolete\/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
\n**************************************************
\n–> “background_dump_dest” replaced by\u00a0 “diagnostic_dest”
\n–> “user_dump_dest” replaced by\u00a0 “diagnostic_dest”
\n–> “core_dump_dest” replaced by\u00a0 “diagnostic_dest”<\/font><\/p>\n

To fix this <\/font><\/font>obsolete parameter, comment out from initialization parameter file and replace with new parameter like comment above three deprecated parameters and add *.diagnostic_dest<\/strong><\/p>\n

WARNING: –> Database is using an old timezone file version.
\n…. Patch the 10.2.0.1.0 database to timezone file version 4
\n…. BEFORE upgrading the database.\u00a0 Re-run utlu111i.sql after
\n…. patching the database to record the new timezone file version.<\/font><\/p>\n

To find time zone file version on source database (10g) run
\nSQL> select * from v$timezone_file;<\/strong>
\nIf time zone file version is less than 4 then apply time zone patch\u00a0 5632264<\/strong> manually for 10.2.0.1 database (more on How to manually upgrade time zone file version on database coming soon … <\/font>)<\/p>\n

1.4<\/strong> Check invalid objects
\nSQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;
\n<\/strong>
\n1.5<\/strong> Check version & status of all database components
\nSQL>select comp_name,version, status from dba_registry;<\/strong><\/p>\n

1.6<\/strong> Take backup of source database i.e. 10g database<\/p>\n

1.7<\/strong> If you are using spfile, create pfile
\nSQL> create pfile from spfile ;<\/strong><\/p>\n

This will create pfile in 10g $ORACLE_HOME\/dbs\/init[SID].ora<\/p>\n

1.8<\/strong> Copy initialization file (pfile) from source (10g) to target (11g)<\/p>\n

1.9<\/strong> Adjust initialization parameter specific to 11g like<\/p>\n

a) <\/strong>Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
\n*.diagnostic_dest=’\/11g_base’\u00a0 (11g Base Directory)
\nb)<\/strong> Change
\n*.compatible=’10.2.0.1.0′
\nto
\n*.compatible=’11.1.0′<\/p>\n

.<\/p>\n

.<\/p>\n

2. Upgrade Database
\n<\/strong><\/u><\/p>\n

2.1<\/strong> Shut down source database (10g) – Your downtime starts here<\/strong><\/font>
\nsqlplus “\/as sysdba”
\nSQL>shutdown immediate<\/strong><\/p>\n

2.2<\/strong> Set your environment variables to Oracle Database 11g Release 1 (11.1) :<\/p>\n

export ORACLE_HOME=\/u01\/oracle\/11gbase\/11.1.0
\nexport ORACLE_SID=TEST
\nexport PATH=$ORACLE_HOME\/bin:$PATH<\/p>\n

2.3<\/strong> Start Upgrade
\nsqlplus “\/as sysdba”<\/p>\n

SQL> startup upgrade <\/strong>
\nCheck shared_pool & java_pool size, to set new values<\/p>\n

SQL>alter system set java_pool_size=512M;
\nSQL>alter system set shared_pool_size=800M;
\n<\/strong>
\nSQL> spool upgrade.log <\/strong>
\nSQL> @?\/rdbms\/admin\/catupgrd.sql<\/strong>\u00a0 (Upgrade process shut down database after catupgrd.sql)<\/p>\n

Check alert log in 11g at
\n\/11gBase_Install\/diag\/rdbms\/[sid]\/[SID]\/trace\/alert_[SID].log<\/p>\n

2.4<\/strong> Run Post-Upgrade Status Tool provides a summary of the upgrade
\nSQL> startup<\/strong>
\nSQL>@?\/rdbms\/admin\/utlu111s.sql<\/strong><\/p>\n

If you hit error like”ORA-00001: unique constraint (SYS.I_DIANA_VERSION) violated” check metalink note # 744693.1<\/font><\/p>\n

2.5<\/strong> Perform upgrade actions that do not require the database to be in UPGRADE mode
\nSQL>@?\/rdbms\/admin\/catuppst.sql<\/strong><\/p>\n

2.6<\/strong> Compile Invalid Objects
\nSQL>@?\/rdbms\/admin\/utlrp.sql<\/strong>
\nCheck invalid objects
\nSQL> select count(*) from dba_objects where status like ‘INVALID’;<\/strong><\/p>\n

.<\/p>\n

.<\/p>\n

3. Post Upgrade steps<\/strong><\/u><\/p>\n

3.1<\/strong> Check status of database components
\nSQL>select comp_name,version, status from dba_registry;<\/strong><\/p>\n

3.2<\/strong> Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g) oracle_home to target (11g) oracle_home<\/p>\n

3.3<\/strong> Back up upgraded (11g) database<\/p>\n

.<\/p>\n

References<\/strong><\/u><\/p>\n