ORACLE Recovery times - share your experience

[Volker Borowski]

Hi,

I need to estimate a time range for an Oracle database recovery.
I alread did some recovery on a 4 way L-Server where I achieved roughly 10GB/h (no media transfer involved, just the time to apply already restored redologs). Now is this good or bad ?
Please share your experience.

Machine, num-CPU, configured SGA Mem, Redo-GB per hour/minute.

Thanks for feedback
Volker

[Joaquin Gil de Vergara]

it depends!

(size of DB, type of crash...)

sometimes recovery is very fast.... in others you need reencarnate database and process is more delicated....

but... usually when you issue the "recover database" from sqlplus or svrmgrl, recovery is successfull and quite fast! (I talk from my experience of course!)

good luck!

[Robert-Jan Goossens · Accepted solution]

Hi Volker,

10 GB/H is good, just did a recovery got 7200 MB/H (2MB/s) with media involved (DLT8000)

Robert-Jan.

[Volker Borowski]

OK,

I see the database size is a point I missed.
I talk about a database which will be initially 750GB in size and which will grow about 50GB a month. SO anything around one TB will be a good reference for me, but you are invited to share your experience for smaller databases as well.

My above mentione sample was taken from a 250GB database and ~40GB redolog applied within 4 Hours ( -> 10GB/h). So perhaps you could mention the size of your database as well.

I guess esp. those people working with shadow-databases might be interested in these values as well, because you might find out, that somebody is faster than you for some reason :-)

So once again, please share.
Thanks
Volker

[Robert-Jan Goossens]

Ok did not know you are talking about the big ones (:-) my example was a small one without shadow databases (22 GB). For the large ones we use shadow databases.

RJ

[Alexander M. Ermes]

Hi there.
Guys, this depends on some factors :
backup machine
- one tape drive against several
- backup software allowing parallel restore
with several drives
- organization of backup ( filesystems order on tape )

- type of recover ( complete, partial, tablespace etc )

I had to restore a 20 GB database this morning.
restore took approx. 1h 45 minutes.
Recovery withing the database ( recover until time ) and rest of tasks took 15 minutes, so after two hours we were back to business.

Volker, your time is ok. If it should take longer, think about the points i just mentioned.
Rgds
Alexander M. Ermes

[doug mielke]

We clone our 100 gig or so database weekly to offer as a user test instance.

Recovering from hot backup, followed by the application of about 6 hours of archive logs, ( a dozen or so, 32MG each) xfer over 100 mb ethernet to a NAS filer takes about 4 hours.

[Steven E. Protter]

D320, 750 MB RAM. 8.1.7 32 Bit HP-UX 11.00

Disk failure while Oracle was running cold backup. Restored from a fuzzy backup taken by omniback a few hours earlier. It took two days to bring the one congtainer online. Oracle said it would never come online, they were wrong.

Practice:

rp5450 2GB RAM. 11.11 64 Bit OS. 8.1.7.4.0

2 Hours for recovery and roll forward after a proper cold backup and transactions.

SEP

[Indira Aramandla]

Hi,

Datbase recovery 10 GB/H is good.

We do a resore and apply redo logs of 13 GB database every month and it takes 1 hour and 35 minutes. This time involves restoring from
media (DLT7000) as well.

[Indira Aramandla]

Hi,

10GB/H is good.

We do the a restore and recover to a point in time to our DR site every month and here are the stats.

Server Model : 9000/800/K360
RAM : 2 GB
Media : DLT7000
RDBMS : Oracle 7.3.4

To restore and recover the database of 13 GB it tables 1 hour 35 minutes (media time included).

Server model :9000/800/L1500-7x ith
RAM : 2 GB
Media :LTO-Ultrium
RDBMS : oracle 8.1.7.4


To restore and recover the database of 13 GB it tables 1 hour 25 minutes (media time included).

[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