Oracle Upgrade/Migration : What method are you using?

I tweeted a couple of days ago about an important upgrade/migration I was doing yesterday. I was moving a smallish, but high profile, database from Oracle 11g on HP-UX Itanium to Oracle 12c on Oracle Linux inside a VMware VM. Almost immediately Joey D’Antoni came back with the question, “What method are you using?” I thought it might be worth writing a little something about the decision process.

When you are deciding how to upgrade and/or migrate a database there are a few things to consider:

  • Platform : Prior to Oracle 10g, if you wanted to change platforms, you didn’t have much of a choice, it was exp/imp only. As well as introducing data pump, Oracle 10g introduced the ability to convert datafiles and image copy backups using the RMAN CONVERT command. This meant transportable tablespaces were a valid option for platform migration, with or without upgrades.
  • Size : Above a certain size, waiting for an expdp/impdp is not practical. You are going to want to minimise downtime.
  • Allowable Downtime : Despite what the marketing people would have us believe, not all databases have to be 24×7. You should always try to minimise downtime, but it is not always necessary to eliminate it completely. If downtime is an issue there are options, but they may involve additional effort and cost.
  • Money : You can do almost anything if you are willing to throw time and money at it.
  • Junk : When you take a look at a database that has gone through successive upgrades, you can often see the lingering signs of old crap and bad decisions that will haunt you forever. It is really nice if you can start fresh and correct some of the mistakes of the past.

Not everyone has the luxury of large amounts of downtime, so what can you do to minimise downtime? Here are a few options, each having their own pros and cons, as well as restrictions depending on if your focus is migration, upgrade or both. It’s not an exhaustive list. 🙂

  • Transportable tablespaces, or transportable database, reduce migration time to about the time it takes to copy the files between servers. Depending on your storage tech, this could be a very short time indeed. The nice thing about this is it can be used across platforms and between versions, so an “upgrade” using TTS can be really quick. I used this for a Solaris to Oracle Linux move about a year ago.
  • You can use backups to restore a database to a new location, then keep recovering it using archived redo logs until the changeover time. Then ship the final archived redo logs, recovery the new database and you’re done. You could do the same thing with incrementally updated image copy backups. This is more about migration than upgrade.
  • When you are reading the previous point, you are probably thinking that sounds similar to Data Guard, and you could use this to switch machines and/or perform a rolling upgrade. If you are using standard edition, you could use Dbvisit Standby. Either way, the migration could be as quick as a switchover.
  • You can do an RMAN DUPLICATE to switch servers. It’s not going to upgrade your database, but you can use this to move it, then upgrade later.
  • You can replicate between the old and new system until your changeover point. You might use something like Golden Gate or Dbvisit Replicate to do this.
  • You can use the good old expdp/impdp. It’s probably going to be slow, and require a lot of downtime, but it’s logically simple.

There are lots of variations on a theme. The important point is you pick what’s right for you.

So what did I pick for this upgrade/migration? It was good old expdp/impdp. Why?

  • The database was relatively small.
  • The downtime involved was acceptable. It’s mostly accessed during the day and I started the process stupidly early to minimise the effect on the users.
  • It allowed me to clean up a lot of crap in the process. The database was about 18 years old and had been through previous upgrades and server migrations. It was bearing the scars of those and I wanted to clean it all up.
  • It allowed me to both upgrade and migrate in a single step, so it was logically very simple.

As always, there is not a *best* solution. You have to pick what is right for you and the constraints you are working with.

How did it go? Fine. We had already gone through the process in a Dev and Test environment, and I had done a run through on the new production kit also, so I knew it would work and I also had accurate timings, which made it easier to sell it to the decision makers.

Happy days!

Cheers

Tim…

PS. We will inevitably have some firefighting to do, as people always forget about the odd interface or application that connects to the system once in a blue moon. The old instance is turned off, so we should find out if anything has been forgotten pretty quick! 🙂

Author: Tim...

DBA, Developer, Author, Trainer.

2 thoughts on “Oracle Upgrade/Migration : What method are you using?”

  1. Hi Tim,

    I am planning to upgrade my almost 1TB production database and don’t want to use expdp/impdp. Can you please guide me if we can upgrade using rolling upgrade and if you have any article written for it??

  2. Farhan: You are probably not going to like my answer. 🙂

    Can you do rolling upgrade? I assume you are talking about RAC. If so the answer is yes and no. You can do a rolling upgrade of GI (Clusterware and ASM), but not the database. Major upgrades are not supported for rolling upgrades.

    http://docs.oracle.com/database/121/UPGRD/intro.htm#UPGRD12354

    Upgrades don’t have much to do with the size of the DB. It’s more to do with the complexity of the system, like the number of DB objects etc. You might find a 50G database takes the same length of time to upgrade as a 30TB database.

    Cheers

    Tim…

Comments are closed.