This discussion is archived
10 Replies Latest reply: Nov 20, 2012 6:45 AM by Dom Brooks RSS

9i to 11g Data Migration

rsar001 Newbie
Currently Being Moderated
Hi There,
We have a requirement from our client to upgrade one of their main production databases from a single instance Oracle 9.2.0.8 to a two node RAC running Oracle 11.2.0.3. The current database size is just over 500GB (~400GB in db_segments). Both existing and new box(es) are running Solaris 5.10 and they are hosted in different data centers.

I did suggest using transportable tablespaces to move the data around as it’s a lot less manual work and fairly fast. Having said that, the migration strategy should include going back from 11g to 9i as well (in the event of a back-out) and I’m not entirely sure we would be able to do that (downgrade going from 11g -> 9i) with transportable tablespaces. Unfortunately the client doesn’t have the appropriate licensing to use golden gate or data guard.

I was also thinking of using standard physical standby which should be fine as far as licensing goes for the client; however, do we still have to manually move the data to the new database 1st and then synch up the two databases? Also how would this work as a backout strategy if we need to revert back to 9i?

The actual production cut over for the database from one data center to the other is likely not expected to go longer then 12-15 hours I imagine as we may only get a 24 hour window, but we need to complete in half the time because the other half might well be reserved for backout, other testing, ..etc.

The network link between the 2 data centers is extremely fast and reliable. I believe the link is 2 GB DWDM, and only 20 percent of the pipe is actually being used up (lots of bandwidth available).

Thanks in advance and really appreciate all your help.


Cheers
  • 1. Re: 9i to 11g Data Migration
    user296828 Expert
    Currently Being Moderated
    I did suggest using transportable tablespaces to move the data around as it’s a lot less manual work and fairly fast. Having said that, the migration strategy should include going back from 11g to 9i as well (in the event of a back-out) and I’m not entirely sure we would be able to do that (downgrade going from 11g -> 9i) with transportable tablespaces. Unfortunately the client doesn’t have the appropriate licensing to use golden gate or data guard.
    For sure TTS would be faster than expdp/impdp. But TTS you have check the database and make sure the TRANSPORT_SET_CHECK passes for all included tablespaces.
    I was also thinking of using standard physical standby which should be fine as far as licensing goes for the client; however, do we still have to manually move the data to the new database 1st and then synch up the two databases? Also how would this work as a backout strategy if we need to revert back to 9i?
    Data guard is another better solution but should not you be thinking of using Logical Standby Database?
    The actual production cut over for the database from one data center to the other is likely not expected to go longer then 12-15 hours I imagine as we may only get a 24 hour window, but we need to complete in half the time because the other half might well be reserved for backout, other testing, ..etc.
    For roll-back why do you need half the downtime windows. You are on solaris, should not you be able to take the snapshot of the zone and keep it. If rollback required, just put the image back.
  • 2. Re: 9i to 11g Data Migration
    rsar001 Newbie
    Currently Being Moderated
    Unfortunately, data guard is not an option since my client doesn't have the required licenses in place. As for rollback, I guess our concern is if we switch over to the new system, and after say 48 hrs we dicover something seriously going wrong and we do want to rollback, then simply restoring the image will not help as we have to apply all the changes post go live to the old 9i database.

    Is there any other way we can achieve this?


    Thanks
  • 3. Re: 9i to 11g Data Migration
    user296828 Expert
    Currently Being Moderated
    and after say 48 hrs we dicover something seriously going wrong
    I Would be very keen to know, what you think that could go wrong after 48 Hours of system being live?
    Don't you test application system with new database before you do the production?
    If you do not know the worst which could wrong then you will not be able to have a rollback solution for it.
  • 4. Re: 9i to 11g Data Migration
    rsar001 Newbie
    Currently Being Moderated
    I guess the point 'm trying to make here, is that we need to apply those changes to the old 9i database in the case of rollback, and we are looking for a strategy to achieve that - How would we go on about it? what would be an ideal method that anyone have trialled out here with the limitations we have in place?

    Obviously the plan will be documented, properly and successfully trialed prior to the go live.

    Thanks
  • 5. Re: 9i to 11g Data Migration
    Justin Cave Oracle ACE
    Currently Being Moderated
    If you want the option to go back to the 9i system after 48 hours of transactions have been processed without using something like a logical standby, you would probably need to do something like have both systems and/or multi-master materialized views up and running simultaneously with Streams replicating the changes between the 9i and 11g environments in real time. This likely adds significantly to the complexity of setting up the new environment particularly since you're then going to have to tear down all that replication infrastructure once you decide to stick with the 11g system. On the other hand, that does mean that the downtime window you'll need during the actual migration will be much smaller since you'll be getting the 11g system up and running well before the downtime window starts and you'll only be switching over once the 11g system has been live for a while as a replication source.

    Whether it really makes sense to invest this amount of time in configuring (and testing) the replication environment, testing that adding replication to the existing 9i environment doesn't cause any issues, testing your fallback plan, etc. is, of course, something your business needs to consider.

    Justin
  • 6. Re: 9i to 11g Data Migration
    user296828 Expert
    Currently Being Moderated
    As mentioned above as well, if you want to achieve to that goal where you want to rollback the system after 48 hours of it being live, definitely you need to spend some more money and take advantage of oracle's features.
  • 7. Re: 9i to 11g Data Migration
    jgarry Guru
    Currently Being Moderated
    11g wrote:
    and after say 48 hrs we dicover something seriously going wrong
    I Would be very keen to know, what you think that could go wrong after 48 Hours of system being live?
    Don't you test application system with new database before you do the production?
    If you do not know the worst which could wrong then you will not be able to have a rollback solution for it.
    I've seen people not notice stuff wrong for years. Hey, if the computer says it, it has to be right, eh?

    More often, I've seen the new system correct unnoticed errors, and have to explain why. People can get defensive when you have to tell them their precious spreadsheet formula has had rounding errors all along.

    A major upgrade like this is likely to mostly work ok, but have a just a few performance issues, then it becomes a judgement call as to how serious they are. Yes, they should have done more load testing, but that is often easier said than expensed. You often just can't know that something was dependent on a bug or misfeature.
  • 8. Re: 9i to 11g Data Migration
    rsar001 Newbie
    Currently Being Moderated
    I think we're deviating from the question I've asked folks..

    Have anyone done this migration (single instance to RAC - 9i to 11g) in the past of a fairly large database? How have they achieved this? what were the things to keep in mind and lessons learned? And what was their rollback strategy/plan?

    Thanks
  • 9. Re: 9i to 11g Data Migration
    JohnWatson Guru
    Currently Being Moderated
    I would think that your only real option is exp/imp. Shouldn't take too long for a DB that size, and you can a lot to tune the import.
    Transportable tablesapce are not possible, because they need Enterprise Edition licences and from what you say about Data Guard, you must be running on Standard Edition.
    You can't use manual standby, because you want to upgrade.
    I can see no way to fallback to your previous environment after cut-over to the new. Not a problem: do it a few times on test until everyone has signed off the migration as valid.
    Good luck!
  • 10. Re: 9i to 11g Data Migration
    Dom Brooks Guru
    Currently Being Moderated
    Have you thought about implementing this change incrementally?

    E.g. going to 11g and later going to two node RAC.

    RAC does not suit all applications.
    Node affinity is crucial.
    I would be concerned about troubleshooting problems related to the migration to 11g AND any cluster issues related to using RAC.

    Why RAC?
    and after say 48 hrs we dicover something seriously going wrong and we do want to rollback
    Unless you can license the appropriate technology this is a no-go in my opinion.
    Otherwise you're talking about similar downtime to the upgrade to do the downgrade.

    I think you have to have sufficient testing to minimise the risks and then just fix going forward.

    See Oracle case study:
    http://www.oracle.com/technetwork/database/manageability/9i-to-11g-real-world-customer-exper-133754.pdf

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points