MADHU ORACLE APPS & RAC DBA: Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Posted in January 22nd, 2009

byAtul Kumar in advanced, database, upgrade

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.
.

Important points before you start 11g upgrade

1. Read Chapter 1, 2 & 3 of Database Upgrade Guide here here
2. Read 11g Installation Guide for your operating system from here
3. Direct Upgrade is possible from 10.2.0.1 and higher
4. There are two methods to upgrade database

a) Database Upgrade Assistant (DBUA) and
b) Manual Upgrade
We are going to use Manual Upgrade
5. Install 11g database software in different ORACLE_HOME from source Database

Upgrade Steps

1. Prepare to Upgrade

1.1 Install 11g in different (new) ORACLE_HOME with following consideration
a) When prompted for Upgrade an Existing Database (Select NO)

b) On Select Configuration Option select Install Software Only

.
Use Installation Guide from here
1.2 Analyze database using pre-upgrade information tool ( utlu111i.sql sql script available at 11g_oracle_homne/rdbms/admin/utlu111i.sql run it from source database i.e. 10g)

sqlplus “/as sysdba”
SQL>spool upgrade_info.log
SQL>@/11g_oracle_home/rdbms/admin/utlu111i.sql

1.3 Check the output of the Pre-Upgrade Information Tool in upgrade_info.log and fix any issues

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”

To fix this obsolete parameter, comment out from initialization parameter file and replace with new parameter like comment above three deprecated parameters and add *.diagnostic_dest

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.

To find time zone file version on source database (10g) run
SQL> select * from v$timezone_file;
If time zone file version is less than 4 then apply time zone patch 5632264manually for 10.2.0.1 database (more on How to manually upgrade time zone file version on database coming soon … )

1.4 Check invalid objects
SQL> select object_name, owner, object_type from all_objects where status like ‘INVALID’;

1.5 Check version & status of all database components
SQL>select comp_name,version, status from dba_registry;

1.6 Take backup of source database i.e. 10g database

1.7 If you are using spfile, create pfile
SQL> create pfile from spfile ;

This will create pfile in 10g $ORACLE_HOME/dbs/init[SID].ora

1.8 Copy initialization file (pfile) from source (10g) to target (11g)

1.9 Adjust initialization parameter specific to 11g like

a) Remove *.background_dump_dest, *.core_dump_dest, *.user_dump_dest and add
*.diagnostic_dest=’/11g_base’ (11g Base Directory)
b) Change
*.compatible=’10.2.0.1.0′
to
*.compatible=’11.1.0′

2. Upgrade Database

2.1 Shut down source database (10g) - Your downtime starts here
sqlplus “/as sysdba”
SQL>shutdown immediate

2.2 Set your environment variables to Oracle Database 11g Release 1 (11.1) :

export ORACLE_HOME=/u01/oracle/11gbase/11.1.0
export ORACLE_SID=TEST
export PATH=$ORACLE_HOME/bin:$PATH

2.3 Start Upgrade
sqlplus “/as sysdba”

SQL> startup upgrade
Check shared_pool & java_pool size, to set new values

SQL>alter system set java_pool_size=512M;
SQL>alter system set shared_pool_size=800M;

SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql (Upgrade process shut down database after catupgrd.sql)

Check alert log in 11g at
/11gBase_Install/diag/rdbms/[sid]/[SID]/trace/alert_[SID].log

2.4 Run Post-Upgrade Status Tool provides a summary of the upgrade
SQL> startup
SQL>@?/rdbms/admin/utlu111s.sql

If you hit error like”ORA-00001: unique constraint (SYS.I_DIANA_VERSION) violated” check metalink note # 744693.1

2.5 Perform upgrade actions that do not require the database to be in UPGRADE mode
SQL>@?/rdbms/admin/catuppst.sql

2.6 Compile Invalid Objects
SQL>@?/rdbms/admin/utlrp.sql
Check invalid objects
SQL> select count(*) from dba_objects where status like ‘INVALID’;

3. Post Upgrade steps

3.1 Check status of database components
SQL>select comp_name,version, status from dba_registry;

3.2 Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source (10g) oracle_home to target (11g) oracle_home

3.3 Back up upgraded (11g) database

References

Oracle 11g Installation Guide
Oracle Database 11g Upgrade Guide
429825.1 Complete Checklist for Manual Upgrades to 11gR1
744693.1 ORA-00001 For SYS.DIANA_VERSION$ During RDBMS Upgrade From 11.1.0.6 To 11.1.0.7
413671.1 Applying “version 4″ Time Zone Files on an Oracle Database
396387.1 Workarounds when Database time zone patches are not available for your patchset
396671.1 Usage of utltzuv2.sql before updating time zone files in Oracle 10
730057.1 Upgrading to 11g Fails with ORA-01722: invalid number

Issues during Oracle 11g Database upgrade and how to upgrade 11i/R12 database to 11g coming soon…

Previous in series

Related Posts for DB Upgrade

1. Upgrade overview for Oracle Database to 11g Release 1 (11.1)

2. Upgrade Oracle Database 10g to 11g R1 (11.1.X)

Popularity: 39% [?]

Share This

Readers who viewed this page, also viewed:

29 users commented in " Upgrade Oracle Database 10g to 11g R1 (11.1.X) "

Follow-up comment rss or Leave a Trackback

venu said,

in January 23rd, 2009 at 8:46 am

Hi atul,

Nice doc..
Appreciate your efforts.
Thanks a ton for you to keep us updated to latest oracle technologies.

Thanks,
Venu

venu said,

in January 23rd, 2009 at 8:48 am

One doubt..
Is is mandatory to modify compatibility parameter before 11g upgrade?

Thanks,
Venu

Geetha said,

in February 3rd, 2009 at 5:45 am

Hi Atul

Could you please let me know the downtime of upgrade.

Rgds
DBA.

kumar said,

in April 20th, 2009 at 10:30 pm

Good document….Thanks.

Geetha said,

in May 19th, 2009 at 3:36 pm

Hi Atul

Post db upgrade when trying to bring db up,got below error message.
Kindly let me know the possibility of error.

SQL> startup
ORACLE instance started.

Total System Global Area 284565504 bytes
Fixed Size 1299428 bytes
Variable Size 92277788 bytes
Database Buffers 188743680 bytes
Redo Buffers 2244608 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
Process ID: 12879
Session ID: 155 Serial number: 3

Rgds

Atul Kumar said,

in May 19th, 2009 at 5:03 pm

ORA-01092: ORACLE instance terminated Disconnection forced is a generic error when Oracle detects a serious problem at database start time.

Check alert log and see root cause of issue

Siva said,

in July 7th, 2009 at 2:08 am

After upgrade I found file_name is comming null as selecting from dba_data_files

SQL> alter tablespace USERS add datafile ‘/u/07/ORACLE/psgdba/users02.dbf’ size 500m;

Tablespace altered.

SQL> select file_name from dba_data_files;

5 rows selected.

sunil said,

in September 9th, 2009 at 3:17 am

thanks alot

Jeff said,

in October 2nd, 2009 at 11:27 pm

Hello, Thanks for the information. We are upgrading and moving the database from one server to another at the same time. What should the process of upgrade be? Should we upgrade the db first before moving it to the new server or upgrade it after?

João Rodolfo said,

in November 5th, 2009 at 5:29 am

Did you already upgrade an Oracle 10g RAC instance to Oracle 11g RAC with ASM?

Nagarajan said,

in July 7th, 2010 at 11:53 am

Hi Atul,

What would be the additional steps in case of a physical standby database?

Can you please post the sequence of that also and where it fits in the list of steps that you have mentioned here?

Many thanks for this article.

Thanks
Nagarajan

Boopathi said,

in July 29th, 2010 at 7:58 am

this is very good Doc .. I was Done successfully done Oracle 10g to 11g up gradation.

thx a lot…

we r excepting Doc’s from ur side.

thanks
Boopathi.P

Dhanasekar said,

in August 3rd, 2010 at 3:53 pm

Hi Shiva,

Even i too came across the same situvation, pls try to run the catuprd script or try to query the dba_data_files from another window. Probably you could see all the datafiles.

Thanks,
Dhanasekar

champak chacha said,

in December 2nd, 2010 at 10:40 am

Hi Beta,

this is champak lal gada from dhaulakuan Delhi.
I would like to thank you for this wondrful article on behalf of my grandson.

Regards,
Champak

ashokreddy said,

in December 30th, 2010 at 11:09 pm

hi atul

your article is very good. If possible please send atleast 60 pages for my projects.

Atul Kumar said,

in December 31st, 2010 at 5:55 am

@ ashokreddy,
You can get your 60 pages from

http://download.oracle.com/docs/cd/B28359_01/server.111/b28300/toc.htm

Rajesha Iyengar said,

in February 1st, 2011 at 4:36 pm

Hi,
Need your input on Oracle database upgrade from 10g (10.2.04) to 11gR2. (Both ebusiness suite 11.5.10) and other Non ERP systems running on oracle database 10g.

Does the upgrade impacts any of the applications (for example database procedures, packages, tables, forms etc.. does the code needs to be modified).

Would it be possible to know before the upgrade to identify the application programs/codes getting impacted

Thanks
Rajesha

aisufi said,

in February 8th, 2011 at 4:49 am

Hi Atul/Colleague,
We are on Oracle apps 12.0.6 and database 10gR2.I need your help, how & what to explain to management that we should go for 11gR2 from 1ogR2

Regards,

Atul Kumar said,

in February 8th, 2011 at 7:26 am

@aisufi,
Most convincing point is support matrix, check when is 10gR2 going out of support for 12.0.6 and then present it to management.

For technical reasons, check new features in 11gR1 and see if you are planning to use some of those features.

Sid said,

in March 15th, 2011 at 3:13 am

Grt job….

tralveller said,

in March 15th, 2011 at 6:09 am

hello,

i dont find the “utlu111i.sql”-file in the oracle-home-folder

I can download this file somewhere separately?
thanks for any answers

best regards

Atul Kumar said,

in March 15th, 2011 at 7:41 am

@ tralveller,
Are you using 11gR1 or 11gR2 software ?
Are you looking for this file in new Home or Old Home ?

If you are using 11gR1 and looking in new Oracle_Home then could you please confirm what installation type you used (client , server..)

usestuser said,

in November 13th, 2011 at 7:51 pm

We have db on 10g. which i will be upgarding to 11g with encryption of 1 tablespace.

AS the scope is just to encrypt 1 tablespace with upgrade(from 10g to 11gR1), i am using the approach for upgeade to 11gR1 as mention by you in above steps.

Question is the 10g database which I will be upgrading to 11gR1 has 3 tablespace which are in read only status. In this case can I still use above steps to upgrade or no?

Regards,

Sudhakar said,

in December 22nd, 2011 at 4:28 pm

Hi Atul,

I refer the document, which helps alot during the upgrade the DB.

muhammad.shahid said,

in January 12th, 2012 at 5:05 am

Hi,
We have running EBS on single node (apps,db on same server) on linux.
Now we have planned to migrate database from linux to AIX while apps will remain on linux(split EBS to two nodes).
Can anyone help how this will be done.

Regards,
Muhammad Shahid

muhammad.shahid said,

in January 12th, 2012 at 5:09 am

Hi,
We have running EBS 12.0.6 on single node (apps,db(10.2.0.3) on same server) on linux4.
Now we have planned to migrate database 10g from linux to AIX 5.3 while apps will remain on linux(split EBS to two nodes).
I know that this will be done with export/import.
But on new AIX server we should install oracle 10g from normal media or EBS db media.
Is there any difference both 10g media’s( like in utility,packages,autoconfig etc).

Can anyone help how this will be done.

Regards,
Muhammad Shahid

baddi said,

in January 12th, 2012 at 7:15 am

Thanks,

John Levear said,

in January 12th, 2012 at 5:20 pm

thanks for sharing. you can find complete steps with screenshots at following link.

http://www.solarishowto.com/?p=574

Atul Kumar said,

in January 13th, 2012 at 3:51 am

@ muhammad.shahid,

How about using transportable database for platform migratio?

Check metalink note 734763.1 Using Transportable Database to migrate E-Business Suite R12 Using Oracle Database 10gR2 or 11g

Check if there are any limitations for migration from Linux to AIX athttp://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-platformmigrationtdb-131164.pdf

Upgrade Oracle Database 10g to 11g R1 (11.1.X)

No comments: