Recently we did database upgrade from 10g to Oracle 11g.I would like share that activity with you.
Pre-Requisite:
You should have the Oracle database 10g, which you want to migerate.
Also here we are upgrading to Oracle Database 11g – Beta 6 (11.1.0.6)
Step 1) Installing Oracle 11g Home
We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home as a seperate ORACLE_HOME in parallel to 10g Oracle Home.
Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0
Just a parallel 11.1.0 directory can be created and we can install oracle home in this location.
Start the installation using the below command
./runInstaller -invPtrLoc /u01/app/oracle/oracle/product/11.1.0/oraInst
Screen 1 – Select Product Install
select “Oracle Database 11g”
Screen 2 – Select Installation Method
Choose “Advanced Installation”
Screen 3 – Specify Inventory directory and creditials
Note: We are providing local inventory here inside the corresponding ORACLE_HOME location.
Screen 4 – Select Installation Type
Choose “Enterprise Edition”
Screen 5 – Installation Location
Oracle Base as parent directory of ORACLE HOME
Screen 6 – Product Specific Pre-requisite Checks
It may gives below warning, we can ignore and proceed further
Screen 7 – Upgrade an Existsing Database
Choose “No”
Screen 8 – Select Configuration Option
Choose “Install Software Only”
Screen 9 – Privileged system groups
Based on the group of oracle user, this value has to be set.
Screen 10 – Summary
Click on “Install”
At the end of installation, installer will ask to run root.sh script. Do not press OK button.
Run root.sh as a root user and once done, press OK button. This will complete the software installation for Oracle Database 11g.
Step 2) Pre-Upgrade Utility
In 11g Home you installed, go to $ORACLE_HOME/rdbms/admin and copy the file utlu111i.sql to some temp location.
[oracle]$ cd $ORACLE_HOME
[oracle]$ cd rdbms/admin/
[oracle]$ pwd
/u01/app/oracle/oracle/product/product/11.1.0/db_1/rdbms/admin
[oracle]$ cp utlu111i.sql /tmp
The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
Most of the time issue use to come up with time zone….
Then login to the 10g oracle database and run the above sql you copied.
Oracle Database 11.1 Pre-Upgrade Information Tool 23-02-2011 01:34:07
.
**********************************************************************
Database:
**********************************************************************
–> name: ORCL
–> version: 10.2.0.1.0
–> compatible: 10.2.0.1.0
–> blocksize: 8192
–> platform: Linux IA (32-bit)
–> timezone file: V2
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
.
.
.
.
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. 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.
The utility will give the output in the form of recommendations to be implemented before starting the upgrade. Unless these requirements are met, the upgrade will fail.
Step 3) Executing the recommended steps
Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home
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.
Finding the Version of existing timezone files:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 2
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;
VERSION
———-
2
If the Version of the existing timezone is less than 4, then apply the patch for Version 4 timezone files.
Check the database version
SQL> select banner from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
PL/SQL Release 10.2.0.1.0 – Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 – Production
NLSRTL Version 10.2.0.1.0 – Production
For 10.2.0.1 check the metalink note ID 413671.1. We have a table which defines the patch to be applied.
Always try to use the official patch
The script (and on 10g also the csv file) are normally delivered through installation of a patch in the Oracle home. Please note that before using this note you are advised to double check that the time zone patches are not available for your patchset. Applying the “correct” patch through opatch is always preferable to the manual method described in this note.
If there is no official patchset for the version you are currently having then you can Identify the utltzuv2.sql & timezdif.csv combination patch for a different patchset, but same release.
For example if you run 10.2.0.1 and you are trying to find the utltzuv2.sql script & timezdif.csv file you can find the correct patch 5632264 for 10.2.0.2 and this will be applicable to 10.2.0.1 as well.
Please follow the metalink note ID 396387.1
Once you identify the correct patchset(5632264 for 10.2.X), download the same and unzip it.
[oracle]$ unzip p5632264_10202_LINUX.zip
[oracle]$ ls
etc files README.txt
[oracle]$ cd files/oracore/zoneinfo
[oracle]$ ls
readme.txt timezlrg.dat timezone.dat
Backup $ORACLE_HOME/oracore/zoneinfo directory
[oracle]$ cp -R $ORACLE_HOME/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo_backup
Copy the .dat files
[oracle]$ cp timezone.dat timezlrg.dat $ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version again
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
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;
VERSION
———-
4
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.
Step 4) Run Pre-Upgrade Utility again
After executing the recommended steps, run the pre-upgrade utility once again to make sure, you don’t get any critical warnings.
Run the pre-upgrade utility script on 10g database while connecting from 10g oracle home.
If every thing looks fine, Shut down the database from 10g Oracle Home
This time make sure you dont have the critical warnings like the one with TIMEZONE version.
Step 5) Starting Upgrade
Source the following variables for 11g Oracle Home
[oracle]$ export ORACLE_HOME=/u01/app/oracle/oracle/product/product/11.1.0/db_1
[oracle]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle]$ export ORACLE_SID=orcl
[oracle]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
connected to the database sys as sysdba
sqlplus “/ as sysdba” –> will be connected to idle instance
SQL> startup upgrade
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 611000320 bytes
Fixed Size 1301588 bytes
Variable Size 201327532 bytes
Database Buffers 402653184 bytes
Redo Buffers 5718016 bytes
Database mounted.
Database opened.
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
Once the upgrades finishes. It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
Check the dba_registry for the components and its status
Step 6) Post-Upgrade Steps
Once the upgrade completes, restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP
Run utlu111s.sql to display the results of the upgrade:
SQL> @?/rdbms/admin/utlu111s.sql
.
Oracle Database 11.1 Post-Upgrade Status Tool 23-02-2011 05:22:40
.
Component Status Version HH:MM:SS
.
Oracle Server
. VALID 11.1.0.6.0 00:19:02
JServer JAVA Virtual Machine
. VALID 11.1.0.6.0 00:02:55
Oracle Workspace Manager
. VALID 11.1.0.6.0 00:00:54
OLAP Analytic Workspace
. VALID 11.1.0.6.0 00:00:26
OLAP Catalog
. VALID 11.1.0.6.0 00:00:58
Oracle OLAP API
. VALID 11.1.0.6.0 00:00:25
Oracle Enterprise Manager
. VALID 11.1.0.6.0 00:11:00
Oracle XDK
. VALID 11.1.0.6.0 00:00:53
Oracle Text
. VALID 11.1.0.6.0 00:00:50
Oracle XML Database
. VALID 11.1.0.6.0 00:03:52
Oracle Database Java Packages
. VALID 11.1.0.6.0 00:00:21
Oracle Multimedia
. VALID 11.1.0.6.0 00:04:25
Spatial
. VALID 11.1.0.6.0 00:05:18
Oracle Expression Filter
. VALID 11.1.0.6.0 00:00:13
Oracle Rules Manager
. VALID 11.1.0.6.0 00:00:12
Gathering Statistics
. 00:04:03
Total Upgrade Time: 00:55:57
PL/SQL procedure successfully completed.
Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode:
SQL> @?/rdbms/admin/catuppst.sql
Run utlrp.sql to recompile
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
1576
SQL> @?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects
2 where status = ‘INVALID’;
COUNT(*)
———-
0
This completes the upgrade.
Hi,
Sir good article on upgrade iam planing to upgrade database from 10gr2 to 11gr can u pls tell me which patchset will be applicable
Thanks in advance
Regards
Kavita
Dear Kavita,
I beleaves that your current oracle version is 10.2.0.2 (at least).
Install Oracle 11.1 Database software.
Apply the latest patchset to the oracle 11.1 database software.
So first download oracle patch by step by step from oracle site as download>>Database patches>>>Oracle>>>).
Make selection as per your platform & 32/64 bit.
Let me know your OS version & 32/64 bit,oracle base version.
Thanks for valuable feedback….let me know in case any issues.
Thanks & Regards,
Samadhan
Sir,
I have upgrade Oracle Database from 10g to 11g but when i connect
“sys as sysdba”
It shows
SQL*Plus: Release 11.1.0.7.0 – Production ……………….
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0… – 32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
What is the issue
Pls help
Thanks
Hey,
Sorry for late reply….. don’t worry it seems to be that u upgrade DB only on 11g.
But your client is still running as 10g, so please upgrade client as well.
Please let me know if you still have any issue or any update.
Thanks & Regards,
Samadhan
Hi Kavitha. Where are U from? This is Purna Yadav from Atlanta, GA.
Hye she is from Mumbai , India … you can cath her on kavita.oracle@gmail.com 🙂
Hi ,
Sir thanks for responsing back … Sir my oracle version is 10.2.0.1.0 ad i have downloaded the 11 gr2 from oracle website my platform is ole 5.5 and its 32 bit Sir iam just getting confused in patch sets so just wanted to know which one
Thanks &Regards
Kavita
Dear Kavita,
First of all sorry for late reply….As you have already downloaded 11gr2.
Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations.
So you need not to look for any patchset ….I hope this this is useful to you….if you still have any confision then you are always welcome….
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
Hey good guide for the upgrade.
Thx a lot Dear for kind words…..
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
Hi Sir,
Very good article on upgrade. I am planing to upgrade database from 10gr2 to 11gr in windows 2003 Itaniumn. Can u pls tell me which patchset will be applicable and any procedue for Itanium windows platform. Please advice.
Regards
Jose H.
Dear Jose,
Thanks for encouraging responce.You can find the required patchset on oracle download site.
{lease try & let me know if you find any difficulty for same.
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
Sweet blog, greetings from Europe!
Dear Philip,
Thx a lot….!!!!
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”
Hi samadhan,
i was upgrading from 10g to 11gR2 . when i run the script catupgrd.sql i have found following errors..
i successfully open my database with STARTUP UPGRADE command.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade not supported from version 10.2.0.1.0
ORA-06512: at “SYS.VERSION_SCRIPT”, line 49
Dear satish,
A direct upgrade from 10.2 to 11.2 is not supported in 10.2.0.1, you have to be at least in 10.2.0.2.
So depending on 32 bit or 64 bit first apply the patch for 10.2.0.2 with respective patchset.
Then Oracle 11.2.0.2 is delivered with the Patchset 10098816. You don’t have to install Oracle 11.2.0.1 as starting with Oracle 11.2 every Patchset is already a full installation.
Hpe this info works you, Let me know if any more info you required.
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”
Good points
Hi Boss,
This is Ganesh Datt Tiwari. We are running SAP on Oracle 10.2.0.4 and HP-UX 11iv3.
I m process to upgrade oracle 10g to 11g . Software has been installed and oracle home is created but when i m trying to start upgrade then it is giving the error “Control file can not be identified and Invalid No.
Regards
Ganesh Datt Tiwari
Dear Ganesh,
Let me know exactly what error you are gettting. Please put ora error msg with error code.
Thanks & Regards,
Samadhan
https://samadhandba.wordpress.com/
“Key for suceess, always fight even knowing your defeat is certain….!!!!”
Hi Samadhan,
Very nice post!! am impressed!!
its very nice!
I am planing to do the upgrade from 10.2.0.4 to 11g2r. This is the great help.
Dear Tren.
Thx a lot dear….!!!!
Thanks & Regards,
Samadhan
hi samadhan,
i try to upgrade database from 10g to 11g then eaisely i can understand ur steps.
thanks for ur help……………
Dear Krishna,
Thanks a lot for your kind words….. !!!!
Thanks & Regards,
Samadhan
Very Good Post Sir
Thx for ur Post
Dear Gaurav ,
Thx a lot …. request you to pls dont call sir…..!!!!
Thanks & Regards,
Samadhan
thanks bhaiyya
Dear chandu,
Thx a lot bro…!!!!
Thanks & Regards,
Samadhan
Hi Sir ,,
can we upgrade 10.2.0.1.0 to 11.2.0.1 if not then which version u can upgarde
Dear Varun,
Hope you are doing well.Yes you can upgrade from 10.2.0.2.0 (atleast you nedd this version of 10g.)to 11.2.0.1 directly.
Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.1), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations.
So you need not to look for any patchset ….I hope this this is useful to you….if you still have any confision then you are always welcome….
Thanks & Regards,
Samadhan
And bro pls dont call sir….. !!!!!
Respected Sir,
i try to upgrade database from 9i to 10g pls give me solution then eaisely i can understand ur steps.
I want to upgrade server version ecc5 to ecc6
thanking you
somnath
Dear Somnath,
Sorry for late reply, very soon i will come up with the practical document of patch upgrade from 9i to 10g.
Thanks & Regards,
Samadhan
Gr8 job brother…
-Bharath
Dear Bharath,
Thx a lot bro….!!!!
Thanks & Regards,
Samadhan
Hello Sir,
I am upgrading from 10.2.04 to 11R2 .. all steps completed upto catupgrade step.. when i run catupgrd script it wil through error like
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade not supported from version 10.2.0.1.0
ORA-06512: at “SYS.VERSION_SCRIPT”, line 49
Please help me anyone of its urgent…
Thank you
srinivas
Dear Srinivas,
Sorry for late reply…. As u said ur upgrading from 10.2.0.4 to 11R2 ….
But here in ur case ur logs showing 10.2.0.1.0 — [ORA-20000: Upgrade not supported from version 10.2.0.1.0].
So please check your dba-Registry…. coz there is conflict of version…. as u cant upgrade from 10.2.0.1.0 to 11R2.
Let me know the status…. 🙂
Thanks & Regards,
Samadhan
Hi Dear,
Can i have answers of these questions. Iam preparing for an interview, and i need these answers ASAP. Please help me out for this.
Wating for your reply.
Sohail.
44. Can you change SHARED_POOL_SIZE online?
45. Can you Redefine a table Online?
46. Can you assign Priority to users?
47. You want users to change their passwords every 2 months. How do you enforce this?
48. How do you delete duplicate rows in a table?
49. What is Automatic Management of Segment Space setting?
50. What is the difference between DELETE and TRUNCATE statements?
51. What is COMPRESS and CONSISTENT setting in EXPORT utility?
52. What is the difference between Direct Path and Convention Path loading?
53. Can you disable and enable Primary key?
54. What is an Index Organized Table?
55. What is a Global Index and Local Index?
56. What is the difference between Range Partitioning and Hash Partitioning?
57. What is difference between Multithreaded/Shared Server and Dedicated Server?
58. Can you import objects from Oracle ver. 7.3 to 9i?
59. How do you move tables from one tablespace to another tablespace?
60. How do see how much space is used and free in a tablespace?
Dear Sohail,
Sorry for late reply…. PLease find the answer for your reqested questions….
44. Can you change SHARED_POOL_SIZE online?
Yes You can change it ALTER SYSTEM SET SHARED_POOL_SIZE=;
45. Can you Redefine a table Online?
Yes we can Redefine it using DBMS_REDEFINITION
46. Can you assign Priority to users?
Yes….
47. You want users to change their passwords every 2 months. How do you enforce this?
You can enforce this by using profile parameter
48. How do you delete duplicate rows in a table?
You will find lot of queries for same on google like….
delete from table where rowid not in ( select min(rowid) from Table group by column1..,column2,…column3..)
49. What is Automatic Management of Segment Space setting?
Automatic Segment Space Management (ASSM) introduced in Oracle9i is an easier way of managing space in a segment using bitmaps. It eliminates the DBA from setting the parameters pctused, freelists, and freelist groups.
ASSM can be specified only with the locally managed tablespaces (LMT).
The CREATE TABLESPACE statement has a new clause SEGMENT SPACE MANAGEMENT.
50. What is the difference between DELETE and TRUNCATE statements?
For this please check https://samadhandba.wordpress.com/2010/12/19/script-what-wait-events-are-sessions-waiting-on/
51. What is COMPRESS and CONSISTENT setting in EXPORT utility?
COMPRESS:
Compress will cause export to resize the initial extents for all tables so they will import into one initial extent.
The default value is YES.
CONSISTENT:
If consistent is set to YES export will make the export consistent to a single point in time. A rollback segment will be retained.
52. What is the difference between Direct Path and Convention Path loading?
Conventional Path
During conventional path loads, multiple data records are read in and placed in a bind array.
When the bind array is full (or there is no more data left to read), it is passed to Oracle for insertion.Conventional path uses the Oracle SQL interface with the array option.
Direct Path
A direct path load creates data blocks that are already in Oracle database block format.
These database blocks are then written directly to the database bypassing most RDBMS processing.
53. Can you disable and enable Primary key?
Yes…
ALTER TABLE emp ADD PRIMARY KEY (empno) DISABLE / ENABLE ;
54. What is an Index Organized Table?
Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure.Besides storing the primary key values of an Oracle indexed-organized tables row, each index entry in the B-tree also stores the non-key column values. Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Because the non-key columns of a row are present in the B-tree leaf block itself, there is no additional block access for index blocks.
55. What is a Global Index and Local Index?
Local index:
Local Partitioned indexes are easier to manage and each partition of local indexes are associated with that partition.
Global index:
Global index used in OLTP environments and offer efficient access to any individual record.We have two types of Global index. They are global Non-partitioned index and Global partitioned index.Global nonpartitioned indexes behave just like a nonpartitioned index.
56. What is the difference between Range Partitioning and Hash Partitioning?
Range Partitioning
Range partitioning is used when partitions based on ranges of column values.
This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example,months of the year. Performance is best when the data evenly distributes across the range.
Hash Partitioning
Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions.Rows are mapped into partitions based on a hash value of the partitioning key.
57. What is difference between Multithreaded/Shared Server and Dedicated Server?
In a Shared Server architecture MANY sessions (user connections) have ONE server process associated.
In a Dedicated Server architecture ONLY ONE sessions (user connections) have ONLY ONE server process associated.
58. Can you import objects from Oracle ver. 7.3 to 9i?
Yes….
59. How do you move tables from one tablespace to another tablespace?
Yes we can…
alter table table_name move tablespace the_new_tblspce;
60. How do see how much space is used and free in a tablespace?
Views are sm$ts_used,sm$ts_free,sm$ts_avail …
Thanks & Regards,
Samadhan
Hi Samadhan,
First of all thank you for these valuable posts. I need a clarification.
I’m going to upgrade from oracle 10g to 11g…
“Is there any need to change the dbc files that were pointing it after updation?”
I’m sure we dont need just wanted to confirm. Also I need to be sure that the database tables,sequence,synonym etc wont be affected because of upgradation.I’m guessing it wont be affected at all. Like I said, it would be better if I get a confirmation since I’m dealing with sensitive databases.
Thanks in advance.
Dear Bennyanderson,
Thx for your kind words…. There is no need to change any dbc file and tables,synonym,sequence are not going to get affect because of upgradation. Hope I clear your douts….. Let me know if you have any further query or suggestion…. 🙂
Bye tc…
Thansk & Regards,
Samadhan
Thanks a lot dear………….
you can find complete upgrade steps with screenshots at following link.
http://www.solarishowto.com/?p=574
Dear John,
Thx a lot for your suggestion….
Thanks & Regards,
Samadhan
While upgrading DB and apps want to be in up or down
Dear,
While upgrading DB and Apps should be always down.
HI GOOD GUIDE FOR UPGRADE WITH EACH N EVERY COMMAND
THANKS A LOT….
Hi Samadhan,
I Successfully upgrade my database from 10g to 11gr2, but I am not able to open any GUI utility like dbca,netca,netmgr.
Could you help me regarding same
thanks & Regards,
Rahul
Dear Rahul,
Pls check ur DISPLAY env twice.
Dear please check DISPLAY environment once again 🙂
hi do u have any idea about oracle 11g(11.2.0.1.0) installation
Thanks in advance
Many Thanks
Joseph
Well dear its simple as 10g …. once u start installation and if u have exp of 10g installation then u can easily manage it 🙂
Hi Samadhan,
We are having Oracle as our database for our SAP production server which is on ECC6.0 and now we are planning to migrate to ECC6.0 EhP5 and it requires us to migrate our existing database which is 10.2.0.4 to 11 g. Also we are on windows 2003 Server environment. So kindly be specific to windows for which I would be thankful please. If you can get back with the complete step-by step details of the upgrade it would be more helpful rather.
Vishnu
Dear please share ur mail id so that i can share with doc which i prepared for recent upgrade….
<Dear Samadhan
I've executed the upgrade to 11.2.0.3 successfully, but now i need to install the oracle client 11.
could you please help me with the procedure?
Tnahnks
Dear,
Just download the require SAR file.
create new folder as 11x_64 in /oracle/client.
And try the command SAPCAR -xvf OCL.SAR in this folder.
And then just link it by command ln -s instantclient_11202 instantclient
Please let me know if you still have doubts.
Thanks & Regards,
Samadhan
Hi samadhan,
I am new in oracle dba….I am from oracle development having 8 year, could you please suggest me how to start towards Oracle Core DBA
I want to more concentrate on Oracle DBA tunning and ( Backup & Recovery )
Thanks
Dear,
First get the complete command on architecture , start reading the DBA’s blog which provide real time scenarios.
For backup & recovery best practice is to have test DB on ur machine and do the practice.
As ur developer u will easily have commend on performance tunning.
Thanks & Regards,
Samadhan
hi brother it was good ,i have one doubt can u tell me, in the bash profile we r setting the 11g oracle home instead of 10g home which was already working and we r installing 11g software only right. after installation 11g software, what we need to do because data files controlfiles and redo files and other files are in u01/…/10g directory right,so how oracle instance will start and how will it work please let me know and oracle software only option install only softare then what about controlfile/pfile give details, waiting to ur reply urgent.
thanks Ravi
Dear Ravi,
Your D/C/R file will be access by Oracle 11g home , this will be handle by oracle itself.
i.e it is good practice to keep these files aside from oracle home location.
Hope you got the answer , please correct me.
Thanks & Regards,
Samadhan
Hi samadhan,
we are using SAP with HP-UX and upgrade oracle from 10g to 11g but one clearification as per my 10g variable of oracle_home=/oracle/SID/102_64 in 11g oracle_home=/oracle/SID/112_64.
Dear,
Yes it is as oracle_home=/oracle/SID/112_64 …. this what u wana know or something else u wana.
My oracle database 11g very slow from today the query was taking 2 minutes to display results today it is taking 30 – 40 minutes how to troubleshoot it?
Dear,
Please chk the explain plan twice, there are may be any issue like index might become invalid or table fragmented or most imp stats missing.
super steps yar it really helpful
Hello Samadhan,
Great post. I have a few questions. I’m trying to create a database 10.1.0.2 (32 bit) on a Windows Server 2008 R2 (64 bit) but when it reaches to do something with the Oracle Configuration Assistant it fails with an error Ora-12546 (TNS-error). My guess is that there is some incompatibility with the server (i tried to install it on 2 separate fresh machines that had Windows Server 2008 R2 64 bit and i’m getting the exact same error.
Now since i can not find a solution to this, and if you don’t have any recommendations on how to solve the above problem I would like to migrate from 10.1.0.2 to 11gR2. Do i need to do something else beside the steps mentioning above?
I have a lot of packages on my existing database. Am I going to have anyproblems with them?
Regards,
Chris
Dear Chris,
In first look when we talk about tns error , it is the only issue with listener.
Please check ur listener and tns file twice.
Because there is nothing to do with upgrade as issue for tns.
Sorry for late reply, let me know if you have any issue.
Thanks & Regards,
Samadhan
Hi Samadhan,
First of all I just want to add one important thing, by reading your post what i felt is just like your name. I am very much pleased with your post and reply to the comments posted. How properly you have replied to each comment.
I will try and explain my issue:
I am not DBA, I am Java + Oracle (SQL) Developer.
At one of my client I am having windows server 2008 32 bit version OS.
I am trying to install “Oracle 10gR2 (10.2) for Microsoft Windows (32 bit)”,
but it is giving me error.
I am having few queries :
1. Will above version work on mentioned OS?
2. If not Is there anything extra i need to do?
3. Can you please help me in this step by step?
Regards,
Manish Maniyar.
Dear Manish,
Thanks for your kind words,let me know what exact error ur getting.
Please post error here if possible.
As per my knowledge you can install 10.2 on 2008.
Let me know if you have any issue or error.
Sorry for late reply , as i was out of touch for long time.
Thanks & Regards,
Samadhan
Hi samadhan,
This step by step is valid for release 10.2.0.2.0, with SO: SunOS 64 bits?
Thanks & Regards,
Andres
Hi…bro I’ve learnt Oracle dba and am nt able to understand it in respect to practicals…can you please give me suggestion or easy ways to understand it…..Waiting fr ur reply…Thnx
Well bro …. install oracle at ur home … do the practice …. keep reading real time scenarios on blogs …. check youtube orale clips …. 🙂
Thanks a lot
Hi Samadhan
Great Article. Can we upgrade oracle 10.1.0.1 to 11.2.0.1 directly on AIX 5.3 environment
Ganpath
Ganpath,
To upgrade to 11g minimum u need to be on 10.1.0.2 platform.
Thanks & Regards,
Samadhan
How much time does it take to upgrade from 10g to 11g?
Hey it is totaly depend on ur DB size and HW configuration of ur server ….
Generaly in my case it tooks from 4 – 8 hrs ….
Hii samadhan hw ru
im gopalreddy really im very impress to ur articles i hope its very usefull so many ppl like me
recently im getting job in oracle dba so i hope ur postings very usefull to me
tcc bye
Hi Gopal,
Thanks for kind words…. keep visiting and commenting ….
Please feel open to suggest or comment ….
Hi,
How to we know the database is upgrade or not (using views)
U can see it by the views as mention below….
select VERSION from dba_registry;
select * from v$version;
thanks samad
ur upgradation is very use full to me
hi samadhan, I’m very beginner for DBA. My question is what files are mandatory to start my database?
Control file ,Datafile ,redo file,pfile ,spfile 🙂
nice
Hi ,
I want to upgrade from oracle db 9i to 11g .Can you please guide me .
Thanks in advance.
Dear first you need to perform 9i to 10g … and then from 10.2.0.2 to 11g …. you will find the post for same in this blog
hi ,
if there is any upgrade from 10g to 11g .. will there there be any changes in pro*c compilers, as my code heavily depends on Pro*C files.
Hi as per my knowledge there should not be any changes ….
Hi,
Nice article !
Can I cross check some of my understandings regarding manual out of place upgrade.
you need not to copy the datafiles, logfiles from old oracle home 10g to 11g before upgrade.
you need not to create new controlfile for oracle home 11g from 10g database.
Regards,
Vikas
Yes you are absolutely correct dear 🙂
Hi Samadhan,
Very good article. It may help me in tomorrow’s interview.
Good stuff…Keep up the good work:)
Hi samadhan
you have created 2 Oracle home:
Example my 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
then my 11g Oracel Home is : /u01/app/oracle/oracle/product/11.1.0
After upgrading can we remove the 10g Oracle Home is : /u01/app/oracle/oracle/product/10.2.0
Yes you can, just keep backup of network files and bin directory ….
Thanks & Regards,
Samadhan
Good Work Dude…. Keep It Up…. This Article Will Help For Upcoming DBA’s In Interviews & Work … Indirectly U R Helping Many DBA’s Like Me…. Thanks…..
Thanks bro for encouraging responce…. 🙂
Really helpful for all dba’s, thanks for the step by step instructions. Good job samadhan once again. Only thing i seen negative in ur blog is lot of spelling mistakes i think u r not doing it intentionally. Any how its great blog will share to my friends aswell. thanks dude.
Hahahaahaha ya very true , I straight way start writting whenever I face issue / learn new things . I amd not good enogh with my english , kindly adjust dear …. I am trying improve that 🙂
Hi Samadhan,
Really a very good article. Thanks a lot for your post. soon I am going to upgrade our database from 10g to 11g and this article will be very helpful at the time of upgradation. keep up good working sir.
Its really very nice article and quick response as well.
Indrasena
Thanks a lot pal for this exiting topic!!
Thanks buddy,
that is very helpful.
Dear Samadhan,
This is super and very useful for me.
This is document is very useful for me. I’m just a beginner and bit confused in Timezone. Let me try migrating and let you know if i have any doubt. 🙂
Good Work, Keep it up
hi dear samadhan,
your article is very nice and useful for each and every DBA,so keep it up …
all the best.
Hi Madan,
This is good article and very useful for Us, Thanks for your support.
Great..!!! Very Helpfull…Thanks alot
Hi Samadhan,
Very good article.
Hi SAMADHAN,
nice blog appreciated !!!
hope u remember its Varun(RIL)
YUP , Sure Varun , I remember … how are you .. shar your mail id. Thanks!!!!
Good Samadhan!
Hello Samadhan Sir I Am new at your blog site.
Also beginner At DBA .
I Have given up gradation task from 10g to 11g .
Now really I found the best Site And Also the best Person.
wish u the best sir And I will try your tutorial And will tell u the result.
simply superb, as bigginer am able to understand very clearly, thanks a lot, i love this blog.