ORACLE Recovery times - share your experience

[Volker Borowski]

Valuable feedback so far folks, many thanks!

More Input wanted, esp. focused on the recovery time. The media time is not that interesting for me, because I have verified technical specs and testruns to calculate this. But I do not have data for plain recovery of redologs.

So where are the folks running standby databases. How big are your redologs and how long does it take to apply one on your shadow DB.

100MB, 150MB, 200MB in two minutes ??????

What are your values ?
Thanks
Volker

[Tim Sanko]

Hi We have EMC EDM as well as timefinder BCVs and the works that tie into 8530s,8830s....
There are many recovery options that we have.

A reverse sync of the BCVs will take 2 1/2 hours 1.5 TB. Then a Oracle DB roll forward recovery to a point in time.

The tape based recovery for the database using
EDM is > 180 GB/HR via direct connect to 6 DLT7000. If we use the 20 Ultrium it goes to 1.6 GB/hr. This doesn't includes the 2 1/2 hr direct connect reverse sync unless you want it to happen automatically.

You would be taking entire DISK image with that type of solution. For non-fibre channel
recoveries, the result is much slower.

Your enterprise backup system is an expensive, but necessary insurance policy.

A 1TB DB at 10GB/hr takes 4 days to recover.

We can't afford to be out of business that long. We have whole computer room UPS. and dual local UPS for out production ERP machine.

A wealth of riches, but necessary for the job we do...

Tim

[Geoff Wild]

We just had to do a recovery this weekend.

While trying an Oracle 9.2 upgrade, the DBA blew up the production database - 1.5 TB SAP. We are on EMC - had the bcv's split - unfortunately - the wrong command to reverse establish them was executed - so we had to do a tape restore, then apply redo logs. We have a lot of redo's - we archive them to tape every hour.

Total time to recover - 24 hours.

Rgds...Geoff

BTW - I was on vacation (first day back was supposed to be today) - and was called in to assist last night...

[doug mielke]

Geoff,
Any note like yours should also include the estimated recovery time for the DBA's injuries.

[Rita C Workman]

Hmm..finished our DR site a couple months ago. Our DR plan is bi-directional, so DR site is actually running production databases and has potential to fail to primary site & visa-versa.
Here was the results of our test with users on one of the larger databases here:

2 Sites:
Primary site running: Oracle 8i on approx 700+ gb database / K570 servers @11i with 3.5gb RAM and running Continental Cluster / EMC 8530 running SRDF using adaptive copies made at 2 hour incrementals (with required Oracle logs running in SYNC_MODE at all times-redo/archive/control)

Failover site running: RP8400's @11i with 8gb RAM / EMC 8530 running SRDF.

Broke the links between sites before the adaptive copies made....and failed over.
Database brought up & current with transactions in well under 30 minutes (....and that included time to logon at the DR site, modify hostfile & DNS file to point where we wanted...and login to PC !!) Had user make change to database in this state (so they could track it..) and then switched everything back to the Primary site...back up there in about the same time !! User checked to see if changes showed back at the Primary - and there it was.

Using both sites as bi-directional failovers/production sites, really helped to justify the costs !...and everyone seemed happy with the results..

Rgrds,
Rita

[Volker Borowski]

Geoff,

your case will be most compareable to mine.
Could you please let me know the amount of time your machine did need for applying the redologs.
If you did a recovery using sapdba, you should have a log in sapbackup telling you how many logs (of what size) have been applied when the recovNNN.sql scripts have been executed.
Not brrestore media times, just the plain recovery.
If you did the entire procedure with sapdba, it would be great if you could attach the sapdba-logfile from this recovery. I would not even care, if you anonymize all site-specific data like SID and datafile-names and so on. You could even clip out anything that comes from brrestore (this will make sure that it is not that lengthy :-).

Just to be clear, I do differ between restore (offsite / offline Media back to disk either datafiles or archivelogs) and recover (the time the database needs to execute "RECOVER DATABASE ...").

No more rabbits for restore or mixed values (allthough still interested in those (say 3 pts)), but still rabbits to win for real life values in term of:

Applied XXX GB of redo per YYY minutes against ZZZ GB Database on NUM-CPU and amount-GB RAM.

Thank's a lot so far
Volker

[Volker Borowski]

New data, after customer decided for test !

Solaris 12 CPU 12GB box. Database small (20 GB) yet (fresh installed), is expected to grow up to 1TB by mid of 2004.
Managed to argue for test cenario
- backup 20GB DB
- create 10GB of redo (leave them at ArchiveDest)
- restore backup
- recover 10GB of redo to become current.

Unfortunately, the recovery did effectively only did run against 3 datafiles with a size of 2GB.

I tried
recover database noparallel;
--> Resulted in 10GB beinig applied within 31:09 minutes (wow!). Had 6.4 GB of database buffer cache.

recover database parallel;
--> Resulted in 10GB beinig applied within 28:28 minutes (wow!). Had 8 GB of database buffer cache.

Now since the recovery was effectively only against 3*2GB of datafiles, the entire datafiles might have been within the DB-Buffer-cache at the end of the recovery (in both cases) without the need to write anything to disk through this recovery (this appears to be not very representative).
Second: The parallel recovery decided to go for five threads on a 12CPU machine and 15 datafiles belonging to the database.

So I consider this not to be very representative data at all, but right now it is better than nothing.

I'm trying to convince for a second test after the DB has grown to about 300-500 GB, but have no confirmation yet.

You are still invited to share information about this aspect.

Thanks
Volker

[Anand_31]

Hi,

Lots of interesting facts from many members!

It is difficult to predict the time taken to recover a database. As most have pointed out, depending on the type of disaster, you may need to restore a part of the database (datafiles) or the complete database. The size of the database does not matter. It could be a 100 Peta Bytes, but what you want to recover does matter.

The key here is the NUMBER OF ARCHIVELOGS to apply and also the current REDOLOG files (if they are required or can be used). In Oracle 8i and above there are some interesting V$ views that give you the some insight into what's happening during recovery. Other than that, the best way is to keep the tests going, like you are doing now!

If you are looking at minimising recovery time, then there are many techniques (BCVs, Replication, etc.). Obviously, more horsepower in your machine means faster recovery.

Hope this helps!

anand