Solved

upgrade 10g R2 and 11g R2 to 12c

Posted on 2014-12-15
32
1,867 Views
Last Modified: 2015-01-05
Dear all,

any real useful upgrade guide for upgrading Oracle from 10g R2 and 11g R2 to 12c?
0
Comment
Question by:marrowyung
  • 21
  • 10
32 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 40501924
any one successfully upgrade one instance and really succes without any problem ?

OR what is thep problme you experienced and how you guys solve it?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40501939
Please also list down any high level steps, preferrably from the one who really did the migration.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40502371
What version of 10g?  Direct upgrade to 12c is supported from 10.2.0.5 and higher.  Documentation for that is here -> http://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD60047

For upgrades, the best high level steps come from the documentation.  That is where we always start.  The 12c upgrade guide can be found here -> http://docs.oracle.com/database/121/UPGRD/toc.htm
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40504259
did you tried the update ? any incident and how to solve this ?

basically how many steps, any step by steps upgrade process with screenshot?

"What version of 10g? "

10g R2
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40504464
As posted, you need the full 4 parts of the version number to determine if direct upgrade is possible.

Every upgrade is different.  There is really nothing that is transferable from one to another.  It all depends on what features you use and how you use them.  That is why you should be going through may iterations of upgrades on non-production databases to figure these things out.

I can say that working in a company with over 80 production databases we can see more than half of them go with no problems at all and then one has a major problem.  We did nothing different and the particular database that has a problem isn't special.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40506307
the 10g R2 we have is Version 10.2.0.3.0

"There is really nothing that is transferable from one to another. "

just shutdown the Oracle and copy all data file and log file , archieve log and redo log to the target one, restore the configuration file and data file on the target one will do ?

"I can say that working in a company with over 80 production databases we can see more than half of them go with no problems at all and then one has a major problem."

so that mean 40% of Db server has problem ? it still a huge percentage ! or just 10% ?

what is the major problem is it and how you solve that?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40506753
As per the documentation link that I provided, 10.2.0.3 is cannot be upgraded directly to 12.  You would have to have an intermediate upgrade to a version where direct upgrade is supported, or you should be able to do an upgrade with exp/imp (or expdp/impdp).

Not sure why you are including steps to copy all the files.  I don't believe that is required in the upgrade.

Typically we see minor problems in about 5-10% of the upgrades.  Major issues only occur in one or two databases.

There is no one problem or one solution that you ever see.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40508542
"you should be able to do an upgrade with exp/imp (or expdp/impdp)."

this will export schema as well ?

"Not sure why you are including steps to copy all the files.  I don't believe that is required in the upgrade."

exp/imp  is the best?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40508638
from your point of view, what version should I upgrade to first then 12c ? what is the way to migrate it easily?

RMAN backup and restore ?

any migration wizard to do everything for us ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40508788
we also concern about what has to be change on application so that it can works well with Oracle 12c from Oracle 10g, any reserve keyworld concern and the real need to know informatoin from your experience?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40508872
exp/imp (or expdp/impdp) will bring schemas across.  It is a way, it may not be the best way.

If you want to do the 2 step upgrade, I would say the latest version of 10.2 that you can get your hands on and then go to 12.

There is a utility provided to do the upgrade.  It is detailed in the upgrade guide that I already posted a link for.  It is provided by Oracle and is their preferred method of upgrade.

I don't see how you can do an upgrade with RMAN.

Application changes are going to depend on your application.  Nobody else can guide you on that.  The release notes should give you the guidance to see what if anything needs to change.  From my experience, unless you use OCI you are shouldn't need more than a recompile.  Again, it depends on your application.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40512509
"exp/imp (or expdp/impdp) will bring schemas across.  It is a way, it may not be the best way.
"

a vendor said it should be no risk on doing this! what is the best way then ?

"If you want to do the 2 step upgrade, I would say the latest version of 10.2 that you can get your hands on and then go to 12."

What is the 2 step upgrade you are referring to ?  you mean upgrade our existing Version 10.2.0.3.0
 to the latest 10g R2 vesrion (like 10.2.0.5 ) and then upgrade to 12c directly ?

"I don't see how you can do an upgrade with RMAN."

ok, ignore it.

"my experience, unless you use OCI you are shouldn't need more than a recompile.
"

what is an OCI ?

from previous PHP and MYSQL ugprade, what we expenice is we need:
1) a POC/test platform to have one of hte database upgrade to the vesrion we want and we test it
2) check the reserved keyword for PHP so that developer can see if any application code needs to be change.

So basically we are going to do some gap analysis, please suggest!
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40512581
we are using the following language to talk to Oracle:

•      PHP
•      HTML5, JavaScript, Ajax.
•      Oracle APEX

any idea on reserve keyword and sth else needs to be care with migrating from our oracel to 12c ?
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 100 total points
ID: 40512617
Regarding reserved words, there should be only few -if any- problems. If you want to check, you might want to use this view:
select *
             from v$reserved_words

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 40512714
run this one  on 10.2.0.3.0 oracle ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40512719
so I strongly belive that 11g Enterprise  11.2.0.1.0 can be upgrade to Oracle 12c direclty ?
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:marrowyung
ID: 40512720
so for the machien we can't upgrade direclty like 10.2.0.3.0, we can only relies on export and import.

for any  verions since 10.2.0.5.0 , we can only use the build in upgrade program to do it ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40513164
Like using the migration wizard, exp/imp can have problems.  I have never seen a 100% solution.  Sometimes it works flawlessly, but never all the time.

As for a 2 step upgrade, the first step is to upgrade to a version that supports direct upgrade (see links above already provided).  Then do the direct upgrade to 12c.

If you don't know what OCI is, then you probably aren't using it.  It is a low level API used to connect to Oracle.  Seeing as you seem to be using standard client connections, I don't see a problem with applications.  You probably want to upgrade the Oracle client on the machines.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40518141
may I know if I should run that on existing Oracle 10g R2 node:


select *
             from v$reserved_words

Open in new window


?

one more notes to you, we have middle tier and the middle tier is calls to the PLSQL (Packages, Functions or Procedures). or any reserve keyword ?

"Like using the migration wizard, exp/imp can have problems. "

so all method has problem on it ? but which one is much better?  migration wizard? our vendor said exp and imp, we can't just let problem happen.

"I don't see a problem with applications.  You probably want to upgrade the Oracle client on the machines. "

you mean there will be no problem on application size anyway but just setup a POC to test it ?

so if problem happens, just upgrade oracle client ?

"As for a 2 step upgrade, the first step is to upgrade to a version that supports direct upgrade (see links above already provided).  Then do the direct upgrade to 12c."

we need to use migration wizard to do it but, for the 2 steps upgrade, still have change to get into trouble, right?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40518375
There is no solution that will guarantee that it will work every single time under every situation.  Period.

exp/imp can be time prohibitive based on the size of your database.

There is only one answer to all your questions.  Set up a test environment and test.  When you are done, test some more.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40521316
"exp/imp can be time prohibitive based on the size of your database."

please explain further about this? you mean if the size of the database is so large, the exp and imp can fail ?

what is the  real life exmaple of THAT SIZE ?

"Set up a test environment and test.  When you are done, test some more. "

as i said before, we will do this but based on the migration expereince, we need both.

a test platform for this kind of test and a reserve keyword list so that programmer can have something to check with.

I ran this:

select *
             from v$reserved_words

Open in new window


and please see the attached excel file for referring, I am not sure what is that mean and what is it for ?
10gR2-reserved-keyword.xls
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40521618
Size of the export file shouldn't be an issue.  The time involved in dumping and reloading the database is the issue.  If you have a window of 12 hours to get the upgrade done and the exp/imp takes 14 hours to complete, then it isn't a viable option.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40523098
ok.


Any suggest on my find out on reserved keyword using :

select *
             from v$reserved_words

Open in new window


?

any situation  in which both upgrade wizard and exp/imp doesn't work ? how to solve it ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40525142
no direct upgrade option also means that we can't use DBUA for that, right?

so 12c's DBUA is not helpful for us.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40525145
johnsone,

"If you have a window of 12 hours to get the upgrade done and the exp/imp takes 14 hours to complete, then it isn't a viable option."


but for some oracle database we have is 10.2.0.x, no direct upgrade option is possible, so exp/imp should be the only one ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40525166
I see some upgrade methods are::

The methods are:
1. Database Upgrade, using either DBUA or the command-line upgrade scripts
2. Transportable tablespaces (TTS) export and import, using the Oracle Database 12c feature full transportable export/import, or the traditional TTS mode
3. Oracle Data Pump Export/Import, using either dump files or network mode
4. The Original Export/Import utilities

this is only for directly upgrade able version, right ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40525268
As I have posted numerous times here, direct upgrade from some versions of 10.2.0 can be direct upgraded to 12c.

Direct upgrade would be done using dbua.

I have never tried transportable tablespaces to do an upgrade.  It may be possible, but I never tried it.  If there are data file differences between the 2 Oracle versions it could take a lot of time to rewrite the used data blocks.  Again, something you would need to test.

expdp/impdp or exp/imp are the original methods.  With the size of current databases I don't see a lot of people using these anymore for upgrades.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40527207
"expdp/impdp or exp/imp are the original methods.  With the size of current databases I don't see a lot of people using these anymore for upgrades. "

so the best method is to upgrade versions of 10.2.0.x to a version which can be direct upgrade to 12c and it should be fastest and easliest one ?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40530686
I would think it would be the easiest and least error prone way to do the upgrade.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40531138
Johnsone,

it seems that you only tried the DBUA before, right ?

so it is perfect from your point of view ?
0
 
LVL 34

Accepted Solution

by:
johnsone earned 400 total points
ID: 40531314
Over the many years that I have been doing upgrades, I have done it both ways.

For the size of the databases that I deal with now, DBUA is the only way to upgrade.  Due to time considerations, expdp/impdp just won't work.  Also the expdp/impdp is manual, in that a DBA has to do it and therefore it is error prone.  We have seen minor problems with DBUA over the years, but worked them out in our testing and had some checks and minor manual steps we did when we got to production.  Every environment is different, so picking a method and testing it multiple times is the best way to go.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40532925
good !
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now