Inconsistency in Backups

Our production environment is running on AWS cloud. At web layer we're running Apache, App- running in tomcats, Oracle-Standard edition running in EC2.

In our current backup and restore process, we take logical backup using Oracle DataPump Tool, as our Schema size is more than 100 GB it take more than 15-20hrs to complete the backup because compressing that backup itself takes more than 5-6 hrs.  Our maintenance window is only 2hrs. Also, we take EBS snapshot backups of live servers after every 4hrs of all prod instances(web/app/db).

Many times we faced errors when we used EBS snapshot restored backup with our applications and our developers suspected that EBS Snapshot backups are not consistent and we should take backup when instances are in static and steady state.

Could someone please advice a better way to achieve consistent backups.

PS : I heard RMAN can be a better option but I don't know much about it.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>> we take logical backup using Oracle DataPump

Export/Import shouldn't be considered a "backup".  It only allows for a recovery to the point in time the export started.  You will probably lose ALL changes from that point until the failure.  You cannot do a point in time recovery to the time of failure.

>>I heard RMAN can be a better option but I don't know much about it.

Time to learn!!!   RMAN is probably the best way to backup Oracle.  I will warn you now:  As with most things that are powerful and flexible, it is VERY CONFUSING.

Once you understand what it is doing, then the mystery sort of disappears.

I would strongly encourage you to get with Oracle Consulting or a local RMAN Expert for some one-on-one to get you up to speed quickly.

Otherwise, it is a LOT of reading in the docs.

Things to look at when researching:
1:  Block Change Tracking
2:  Incrementally Updated Backups

Trying to learn RMAN from a Q&A site like this will be difficult since every possible combination/way to use RMAN to set up your backup and recovery will be different for every database/organization.
johnsoneSenior Oracle DBACommented:
Essentially you cannot back up an open database without using some "special" commands.

What you want to do is:

do the backup

Then the backup along with your archive logs will make a consistent copy.

If you are not in archive log mode, then the only way you can get a consistent backup would be to shut the database down before doing a backup.

RMAN is probably the best option.  Most people use it now.  I can manage the backups better as it is an Oracle tool.  Again, for your best recovery scenario, you would still need to be in archive log mode.
slightwv (䄆 Netminder) Commented:

Warning:  If the database has a lot of DML activity, this will cause a LOT of REDO to be generated until the END BACKUP is executed.  Thus, a LOT of archived redo logs during this time.  You need to be aware of this.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Geert GOracle dbaCommented:
it would help to get a link, i assume you are using this ?
or ?

amazon is probably taking backups for you as the automated backup features are on by default
it allows you point in time restore

why not use their backups ?

why did you setup your own backups on top of what they setup ?
cloudtechnicianAuthor Commented:
Thanks for your suggestion slightwv, yes I've a DBA in my team, I believe he has some understanding of RMAN, but my doubt is how RMAN can help me in this situation, also what makes RMAN better than Data Pump.
slightwv (䄆 Netminder) Commented:
>> also what makes RMAN better than Data Pump.

1:  Point-In-Time Recovery!!!

Here is the recovery with export/import:
If you start the export at 12AM.  The database crashes at 11:59 the next night, you've lost an entire days worth of data.

With RMAN and any "real" backup solution, you should be able to recover up to the time the database crashed.  You may not lose ANY data depending on the type of crash.

2:  Speed.  With manual incremental, you never have to do a FULL lvl0 backup again.  Well, unless you lose the lvl0 you have.  With block change tracking, Oracle tracks which blocks have changed so it can quickly back them up.  It doesn't have to go looking...
johnsoneSenior Oracle DBACommented:
RMAN takes an actual backup of the data files and metadata in the database.  Datapump is only taking a copy of the data.

Datapump, as far as I know, is not a consistent copy of the data either.  Unless you are using the FLASHBACK_TIMESTAMP parameter, which given the amount of time your copy takes, I doubt you could hold that much undo.  You also cannot roll these forward.

RMAN backup is much more powerful.  Can take incremental backups and can be rolled forward.
cloudtechnicianAuthor Commented:
Hi Geert, Actually, RDS incurs a lot of cost, it's a super costly solution, hence we are using the same setup from couple of years, but we're planning to move to RDS in near future.

Can RMAN take the backups of a live DB server and move that to S3 storage which has a schema of more than 100 GB?
slightwv (䄆 Netminder) Commented:
What it boils down to is: Export/Import isn't a "backup".  You need a real backup solution.  RMAN provides this.

>>Can RMAN take the backups of a live DB server

That is what it is designed to do.  Since it is a production database you should be in archive log mode so, yes it can backup a live database.

>> move that to S3 storage which has a schema of more than 100 GB?

RMAN can backup to tape or disk.  How you get the backup files from point A to point B is up to you.

If you want to use the manual incremental, the lvl0 or full backup will need to remain available to the database server.

>>which has a schema of more than 100 GB?

I don't understand what this asking.
johnsoneSenior Oracle DBACommented:
100GB backup shouldn't be an issue for RMAN.

Not sure what you mean by moving it.  Backing up and creating a clone are 2 different things.  If  you are cloning the database to replace an existing one, then yes RMAN can do that.  If you want to selectively add things to an existing database, where you are creating a datastore/datamart or something like that, then RMAN would be the best tool.  If you are doing the backup to disk and then moving it to other storage, it may be possible for RMAN to just back up to the other storage, or you can script the move after the fact.
slightwv (䄆 Netminder) Commented:
>>If  you are cloning the database to replace an existing one,

If cloning is what you are doing, can you link the two databases?  Then you can use export/import across the link and not have to deal with files and compression at all.  You could even write your own data transfer across the link.

You still might have the data consistency issues.

I would still investigate RMAN cloning.
cloudtechnicianAuthor Commented:
Yes,  archive log mode is enabled. Currently, we're taking a backup using Data Pump and backup first compresses on the DB server and then we move the backup to S3 storage to ensure the availability of the backup because what if the backup server itself goes down and by storing it to S3 storage we get 99.999999999% availability of the data.  We take the backups everyday during 2hrs maintenance window but it's crossing everyday. Hence, I think if we can incorporate RMAN in our environment our life would become easy and we wouldn't require to take backup server EBS volume Snapshot every hour because if I understood it correctly RMAN is capable of taking live DB backups.

Correct me if I misunderstood anything here.

Thank you!
johnsoneSenior Oracle DBACommented:
There would be no need to backup the database every hour.  That is what the archive logs are for.  Backup the archives every hour.  Also, look at log shipping and set the S3 storage as a secondary destination so that the logs are automatically stored there as soon as they are generated.

I have never seen a database that was backed up more than once a day.  Most of the databases I have ever worked on were on weekly backups.  The archive logs were backed up hourly, but never the database.
slightwv (䄆 Netminder) Commented:
>>we get 99.999999999% availability of the data.  

You get availability of the export file, NOT the data.  Did you read my scenario above?  With export can lose an entire days worth of data very easily.

So you are not cloning, you just want to "store" the backups on a different server to ensure availability of the backup?  You aren't "applying" the backup to another database?
cloudtechnicianAuthor Commented:
Yes, we're storing the backup so that it can be used in case of disaster recovery.

When we've to apply the latest DB dump to lets say UAT Or DEV environment we just import the latest dump of Prod DB and import it in a DB instance of Dev or UAT environment.
slightwv (䄆 Netminder) Commented:
It sounds like you are looking for confirmation that export/import is a good method.  Sorry, but it isn't.  You should immediately stop using it for anything that you want to call a "backup".  It isn't a backup and never will be a backup.

>> we just import the latest dump of Prod DB and import it in a DB instance of Dev or UAT environment.

WOW,  Hope you don't have a LOT of indexes...  export only captures the index DDL.  ALL indexes must be built from scratch on import.

I rarely see a dev database that contains ALL the data.  Depending on the sensitivity of the data, developers rarely see 'live' data.  testing may or may not contain ALL the data depending on what you are testing.

Anyway: You can use RMAN to recover to a different machine, then just change the database name.

Probably MUCH quicker than import.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
as stated several times above, and i can only urge you to look at the database backup concepts ...
if you are only using expdp ... you don't have a backup

your dba should know this
database backups are a fundamental topic in the basics
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage Software

From novice to tech pro — start learning today.