Step by Step Upgrading Oracle 10g to Oracle 11g

Hell ! Yes , my Oracle 10g in Windows XP SP2 is the older one i.e 10.1.0.2 which doesn’t Support the SOA Suite installation . Oracle suggest to upgrade this to 10R2 (10.2.0.4 or higher ) . But bit lethargic about this upgrade as I do have to again download the Patch from Metalink  i.e of  size ~972 MB . Since I have had Oracle 11g installer so best possible option is to throw my Oracle 10g DB and install 11g afresh . But again the pain is to export all Db objects and again import into new DB .What if the new DB failed to import in 11g ? What if there is Unicode/Non-Unicode character set incompatibility occurs ? I know patchset available but I can’t rely on my knowledge about applying those pathset successfully !

After lots of brainstorming the final thought come is that there might be some way to upgrade the DB from already installed 10g to 11g using the available installer rather using the upgrade patchset for 11g. So finally kickoff  11g installation after executing the setup file and follow the onscreen instruction step by step .

Here you goes …

—————————————————————————————–

—————————————————————————————-

So I have installed 11g softwares without having any Database . My aim is to upgrade the existing 10g instance as 11g .

1) Copy file : utlu111i.sql to d:\temp (any temporary location) from “D:\Oradb11g\RDBMS\ADMIN” .

2) Connected into Oracle 10g instance while DB is up and running and execute the .sql file from SQL prompt and get the below stat .

3) To resolve the below warning :

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.

Execute :

SQL> select * from v$timezone_file;

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;

If the output of this is < 4 then prior to upgrade we need to apply the Timezone patch .So I need to apply it prior to go ahead . For my Windows 32 bit the patch(5731187) could be found at Metalink.

Download the file : p5731187_10105_WINNT.zip and un-archive to location as : D:\temp\5731187 .

4) Copy the files from location : D:\temp\5731187\files\oracore\zoneinfo and paste it to existing Oracle 10g location “D:\oracle\oracore\zoneinfo” . Make sure to keep backup of existing folder so that you  could revert if any problem occurs .

5) Stop the DB instance and Start . After that execute Step 2&3 again and the warning should be removed and timzone file version should be upgraded. Otherwise assist Oracle Support .

6) To remove below warning execute the below command from sysdba users .

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.

7) I skip the other warnings .To avoid showing other warnings use Oracle Documentation about Pre-Upgrade Information

Final) Now lets perform the Database upgrade steps using : Database Upgrade Assistant(DBUA) a GUI to upgrade the DB and it can be found under Start -) All Programs -) Oracle 11g Home -) Configuration and Migration Tool and follow through the onscreen steps ….

N.B : You if are interested about the Manual upgradation using the scripts rather using the DBUA you can take the reference of onlineappsdba or Advait Blog .

After running for quite a long time (more than ~1 hr) the upgrade finishes . But alas ! life is no longer easy and another problem crops up .Enterprise Manager console is not getting configured as  there are some problem with 11g TNS service .Apart from this, everything looks fine after post upgradation messages ! 🙂

So lets wash hands and think more . The problem was I supposed to delete the Oracle11g TNS Service for 11g from Windows Registry after Software installation only and before creating the DB using DBUA .That is the reason why the TNS fails and this inturn cause the configuration failure for EMCA . The nuisance created by me and need to solve by me only 😦 . This problem might not crops up in your case ,I suppose .Otherwise follow the below steps to create the TNS . Run the “Net Configuration Assistant” from Program files OracleHome entry of start menu .Otherwise run : D:\Oradb11g\bin\launch.exe .

Create the TNS service is pretty easy there .Add new service with a name and the entry will be added automatically in Windows service list . Check by running : services.msc from Windows Run option .

N.B : Dont try to tweak registry and copy 10g TNS listener or modify it to point to 11g . Its pretty hard believe me until you dont know the impact in Windows Heart … i.e at Registry ! Also don’t try to create service manually by using sc.exe windows utility . Rely on Oracle Native Net Config Assistant all time 🙂

Hence … My Oracle 11g TNS is ready …DB is up and running fine…See below :

Now lets resolve the problem of EMCA.Follow the below command line utilities to drop the Enterprise Manager Repository and reconfigure it .Its pretty simple …again if you know the right way of doing it and finally reconfigure it .

And finally all is done and lets Cheers ! 🙂 🙂 🙂