Database recovery question

[Axr2]

Background : Database DB1 has 4 tablespaces system,ts1,ts2,ts3,ts4. It is in archivelog mode.

1) shutdown DB1. cold copy system.dbf, ts1,2,3.dbf (but NOT ts4.dbf) and the controlfiles.

2) startup DB1. Switch a logfile. Issue "alter tablespace ts4 beging backup". Hot copy ts4.dbf. Issue "alter tablespace ts4 end backup;". Switch another logfile.

3) Wipeout DB1.

4) Restore all the above datafiles, controlfiles (from steps 1,2) and archivelog files.

5) Startup mount and issue a "recover database using backup controlfile"

This seems to apply all the archivelog files (including latest), but comes back saying system01.dbf needs more recovery and does not allow me to open resetlogs! Any suggestions on how to go about this?

[Axr2]

My recovery scenario stems from the below problem.

I've got a 700G database (in archivelog mode) that I need to backup everynight. I have a 3 hour downtime window. During the time, the database is shutdown (by the customer) and a cold backup is initiated. However, the backup does not complete in the window. After a lot of tuning and such, I can only copy about 600G to tape in 3 hours..Thus I am left with an incomplete / useless backup. I was wondering if I can somehow salvage this "useless" backup of 600G, and make it somewhat useful, by doing a regular "hot backup" on the remaining datafiles (100G worth). Note: All archivelogs are being backed up successfully.

No, the customer will absolutely not allow me to do a hotbackup for the entire database (700G). They claim they face serious performance issues due to additional redolog generation. In any case, I'm not the customer's DBA..and am not allowed to tune his database etc. I'm sure the next obvious qn will be - why am I even doing the cold backup..I might as well leave the database up during the 3 hour window..and take 1 additional hour to backup the remaing 100G. But that is besides the point. Given, the above scenario (para 1), please let me know if you can think of ways by which I can achieve my goal.

RMAN is being phased in..it will be a few weeks before it goes live with this customer. So that's besides the point too.

[nagarjuna]

http://www.dbasupport.com/forums/sho...threadid=40442

[Axr2]

The link doesn't address my concern. Thanks.

[nagarjuna]

I thought that link gives your solution for your back up problem.

anyhow for your recovery problem, try recovering without using back up controlfile clause. It should help.

"recover database"

[nagarjuna]

Originally posted by nagarjuna
anyhow for your recovery problem, try recovering without using back up controlfile clause. It should help.

"recover database"

nope.. this is not the solution. Probably you are missing one archive log file. Did you archive the last switched redo log?

[Axr2]

Please see post again. No archivelogs are missing. It applies everything and yet is incomplete.

As for the link, it merely proposes spending more money on fancy hardware to speedup backups. It does nothing for me.

[nagarjuna]

Originally posted by Axr2
Please see post again. No archivelogs are missing. It applies everything and yet is incomplete.

As for the link, it merely proposes spending more money on fancy hardware to speedup backups. It does nothing for me.

Looks like you missed to read that thread completely.. part of it is as follows..

We used to take cold backup of our 310GB database on sundays with 4 channels. It takes around 2~3 hours. We used IBM-Tivoli tape library. Our file system was RAW.

But, my dream yet possible strategy would be something like..

1. Move all your read only segments to a read only tablespace and free the headers of these tablespaces by making them read only. Take one time back up of these. by doing this, we can minimize the size of actual database to be taken backup regularly.

2. Organize the tablespaces into 5 or 6 groups. You can take back up of these groups at one per day. That is, you are taking a set of tablespaces one day and other set next day and so on. Obviously, the database should be in archive log mode and all of these backups are hot backups.

By following this strategy, we can make optimal use of available resources.

No no, the strategy mentioned above is not my idea.. Happened to read Andrew Symkovsky's reply in some other forum.

=======================
and as far as your recovery scenario is concerned.. am sure, you are missing some redo information..

[marist89]

Originally posted by Axr2

No, the customer will absolutely not allow me to do a hotbackup for the entire database (700G).

They're stupid. They would rather have an incomplete cold backup than a full hot backup. You're going to get into a situation where the database fails and you can't recover. I would think about leaving.

They claim they face serious performance issues due to additional redolog generation.

This is impossible. Sure, there will be additional redo generated, BUT THEIR DATABASE IS DOWN DURING THE COLD BACKUP.

In any case, I'm not the customer's DBA..and am not allowed to tune his database etc. I'm sure the next obvious qn will be - why am I even doing the cold backup..I might as well leave the database up during the 3 hour window..and take 1 additional hour to backup the remaing 100G. But that is besides the point.

That's not besides the point. A complete backup is a complete backup. When the beast goes down you will be expected to pick up the pieces. If you don't have a proper b&r strategy, you won't be able to do that.

You have two choices to meet your backup window in your current scenario:
1. reduce the size of the backup - put some objects in readonly tablespaces, archive off some historical data, etc.
2. get faster backup media - if you continue to need to copy this much data and you have a fixed window of time, you need faster tape drives or you need to backup to disk.

Can you copy half the datafiles one day, half the next, and still recover? Sure. It will require lots of testing and a well documented recovery plan. This would be an immediate kludge to a bad situation.

If this were my customer (and I decided I didn't want to leave, which would be a stretch) I would suggest implementing a weekend Hot backup and incremental cumulative backups on weekdays.

[Axr2]

Jeff, thanks for stating what is "obvious" to me, you and every other sane DBA - that the customer is nuts, I need to get better hardware, put tablespaces in readonly mode where possible, and use RMAN. All of that is a given. I'm working on settting them up with RMAN incrementals. But I don't expect to go live with RMAN with these guys for a few weeks atleast.

I need some backups to cover my rear in the interim. Yes, I perfectly realize that I'm treading dangerous waters here etc. Please try to work with me here .

Can you copy half the datafiles one day, half the next, and still recover? Sure. It will require lots of testing and a well documented recovery plan. This would be an immediate kludge to a bad situation

Yes, this is kinda what I need..but not exactly. I want to mix 75% cold backup and 25% hot backup and yet be able to perform a database recovery. As simple as that.


Nagarjuna - Yes, I'd read the thread. 1) It talks of splitting the backups into multiple hot backups - thats not the case here 2) Next, it doesn't provide an example or clear cut recovery procedures. While, I am. I can cut n paste the exact recovery commands..