Avatar of Greg Clough
Greg Clough
Flag 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.
Cloud ComputingOracle DatabasePostgreSQLAWSFreeBSD

Avatar of undefined
Last Comment
mrjoltcola

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
arnold

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Shalom Carmel

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Greg Clough

ASKER
@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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

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."
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Greg Clough

ASKER
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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.