Link to home
Avatar of Greg Clough
Greg CloughFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How do you migrate a "Live" database to the Cloud?

Hi,

I've been wondering what the best way to migrate a live database into the Cloud?  Ideally downtime will be very short (10 minutes?), and risk will be low.

The specifics in our case are:

Oracle v11.2.0.2 on Solaris v10 --> Amazon EC2 Oracle v11.2.0.4 on RedHat (or maybe RDS)
PostgreSQL v9.2 on FreeBSD --> Amazon EC2 PostgreSQL v9.2 on FreeBSD (or maybe RDS)

In each case the databases are 2TB, and any sort of dump/restore will take too long... it takes over 3 hours just to copy the dump file to Amazon, let alone the export and import times.  It's got to be a live migration where the source/target are in-sync.  I have some ideas, but I won't bias you with them just yet.

Just to add one extra wrinkle, the Oracle database is currently WE8MSWIN1252 and I'd like to get it to AL32UTF8 at the same time if possible.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Greg Clough

ASKER

@arnold

1) We're a true 24/7 setup, so planning to take several hours of downtime will likely get me fired. :-P  The total time to move our main database is Export (6 hours) + Copy (3 hours) + Import (12 hours).  I can probably make some efficiency savings along the way, but I'll never get it down under 1 hour... and that's probably the most our users could bear.

2) Yes, I've exported/dumped the database locally, copied it to the cloud, imported and tested functionality.  That side of things is working just fine.

3) Yes, the application will be moved to the cloud alongside the database.  Everything will be prepared beforehand, so the last piece of the puzzle will be the database, plus some other files that we can finish off with rsync.  My main problem is getting the database.

4) I can't really split the data set, as it's one big relational database and moving pieces would require significant code changes in the order of (if the data isn't on Database A, then check on Database B)

I agree that Replication is the only appropriate way, and I've been trying and not found anything that's OK with both a version, OS, and character set swap.  For PostgreSQL I can just create a Standby as we can keep the same version/OS/character set... simple.  For Oracle, it's a bit more complex as everything is changing.  We could stay with Solaris, but it would be Solaris/Intel not Solaris/Sparc as we run now.  I think there must be a way to do it and also achieve the version/OS/character set upgrades.


@shalomc

Yes, that's basically the technique I've been following, but there have been bumps in the road.  PostgreSQL isn't so bad, but Oracle is proving difficult.  I'll be a bit more open about what I've considered for Oracle

* Export/Import - Way too long, and if there is any hiccup then the downtime could balloon if things need to be re-copied.
* DataGuard - Can't swap version/OS/character set, and we can't run Solaris/Sparc in Amazon
* GoldenGate - Surprisingly, this cannot handle the character set swap (and I verified it with 3 internal Oracle folks as I didn't believe it!)
* Streams - I can make this work Solaris/Solaris, but not Solaris/Redhat as they are different endian OS's.  Why this matters I don't know... but hey, we're talking Oracle here!

Eureka!  I have always run "Physical" DataGuard... but I didn't consider "Logical" DataGuard.  That could fit the bill, but has anyone ever used it to migrate an Oracle database to Amazon? (or any other cloud?)  I'll have to research its tolerance to having a new version/OS/character set... so if you have experience in running Logical DataGuard in this configuration please chime in.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
@shalomc

Thanks, yes I've seen way too much AWS documentation in the past few weeks, my eyes are starting to hurt. 8-/  For us, as far as I know we cannot use Goldengate, as we're swapping from a single-byte character set to multi-byte UTF-8.  I didn't believe that GoldenGate wouldn't support it, but like I said, there were 3 different folks inside Oracle that confirmed this.

@arnold

I see what you mean.  We run a website that's got a "long tail" of information, admittedly the old stuff rarely if ever changes.  I can imagine a system where we could do that, but the development and QA time is likely to be extensive.

I've just spoken to the guys at Amazon, and they recommended replication technologies in this order:

GoldenGate
Streams
DataGuard

GoldenGate is eliminated due to the aforementioned Character Set issues, so they said stick with Streams.  Something like this should do the trick:

BEGIN
  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
    schema_names                 => 'MY_SCHEMA',
    source_directory_object      => 'source_directory',
    destination_directory_object => 'dest_directory',
    source_database              => 'MY_DATABASE_SRC',
    destination_database         => 'MY_DATABASE_TRGT',
    dump_file_name               => 'MY_DATABASE_SRC_to_TRGT.dmp',
    log_file                     => 'MY_DATABASE_SRC_to_TRGT.log',
    bi_directional               => FALSE,
    include_ddl                  => TRUE,
    instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
END;
/

Open in new window


I've run this type of thing locally before, but never to the cloud... so I'll let you know how I get on.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
One additional though, do you have the resource capacity to perform the character set transition in-house prior to the transition to the cloud?
It is usually simpler to diagnose and resolve a single issue versus a combination since something could ...... come up.(Murphy' Law one paraphrased iteration "If anything can go wrong, it will."
The current character set is a subset of UTF-8 (everything is), but it's not binary compatible so I can't use any of the csswitch trickery.  Translation using Streams is secure, and I've tested that extensively.  Everything is retained, and nothing is corrupted. (knock on wood!)

As for partitioning, can you run partitions in different databases?  I'd never considered doing that... Hmmm....

Yes, agreed... and it's great advice to only do one thing at a time.  The downside is that getting downtime agreed is a very painful process, so I'm trying to kill two birds with one stone.  Hopefully that won't come back to bite me. :-O
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.