How to check if files are backed out?

[Andreas D. Skjervold]

Hi.
I'm setting up Oracle Recovery Manager to backup databases to a disk staging area.
Filebackup is then taken of this disk.

My problem is that this disk will need to be cleaned up after each filebackup (or at least before its full!) to leave room for new rman backups.

Is there a way of checking if a file has been backed out, so that these can safely be deleted?

We use Legato Networker to do the filebackup
(Of course the Networker Module for Oracle would solve this problem, but ..$$$)

Andreas

[Josee Bourget-Thuma]

Hello Andreas,

Do you do hot backups or cold?

If you do cold, it would be easy to schedule a mv of the files once the backup is finished to a "HOLD" area. The disk would be clean and this could also allow you to double check that things were backed up. Legato mails a list of the backed up files after each backup. You could cross reference both hold disk and backup list and discover a pattern which would allow you to schedule deletions safely from the hold disk. Just an idea.

Josee

[Andreas D. Skjervold]

Hi
I do a hot backup and the resulting files go directly to the hold are ( a big disk )

The problem is that when Legato finishes backup it sends mail of which FILESYSTEMS that where backed out. So I will have no way to crosscheck between this mail and the list of new files produced by rman.
Even if Legato should produce such a list it would require boring work to do this crosschecking every day.

I would like a script to do this checking and for that to be possible it would have to check each file residing on the staging disk if it has been backed out.
The Legato software must be doing this checking as it is able to do incremental backups, how?
Does it have an internal database of files and modification dates or is it some structure in the filesystem metadata that contains this information???

Andreas

[Josee Bourget-Thuma]

OK, a couple more ideas...

1- Consider a cold backup if at all possible for a few reasons. I'm sure you've heard this before but recovery from a hot backup is not preferred. You may lose data. Second, it would solve the problem you're trying to solve in the most efficient manner.

2-
a. Your archive files all have system date stamps.
b. You should also find in your Legato email a line telling you "END TIME: Wed Feb 14 09:00:16 2001:" for example.

c. I'm assuming your backup takes about the same time each time. Using the awk command, it's very easy to check the date stamp of the log files again and estimated backup completion time. (Add a fudge factor here)

d. You can create one script which will do this:
ll /oracle/arch_dir | /usr/bin/awk -f awk.pgm

e. Create awk.pgm with a line to check if a sub-set of $8 is less then expected backup completion, if so ... delete those files.

You may not have a completely clean disk with this method but at least you'll recover some space.
Sorry I don't have time to write one for you.

Hope this helps!
Josee...

[Josee Bourget-Thuma]

Andreas,

I was re-reading for accuracy, para 2.c. should read:
"..., it's very easy to check the date stamp of the log files againST an estimated backup completion time. (Add a fudge factor here) "

Also, I don't know how incremental backups work "mechanically" but I suspect this is done based on date stamps as you mention.

Keep me posted!
Josee

[Andreas D. Skjervold]

Hi and thanks for the ideas,

but I think its a bit on the side of what I'm looking for.

The operation I do today is the following:
1. check the Legato filebackup of the "hold" disk manually.
2. delete files older than the last OK backup on this disk using:
find /backup -atime +2 -exec rm {} \;
ensuring that the atime number corresponds to the last backup ( + 1 day for safety)

What I'm not able to find is a function to do this using a check condition on each file, like if the following would exist:
find /backup -"check if backed up" -exec rm () \;

Andreas

[Josee Bourget-Thuma]

Hi again!

Maybe I'm missing something here but what's stopping you from putting your manual command into cron?

Do I understand correctly that files on the staging area are backup up?

If your backup starts at the same time on the same days of the week, do a clean up of all files a day older then the current backup start date/time BEFORE it starts?

This would leave "a piece" of the previous backed up files, prevent your disk from ever being too full for completion of the current backup and ensure you don't delete anything you might need, recently backed up.

It would seem logical that each file residing on the staging disk IS backed out if it exists prior to the next backup?

What is your backup schedule anyway?
Why isn't cold backup possible for you?

I'm sticking this out until I understand fully and we resolve your issue.

Josee

[Andreas D. Skjervold]

Hi again
Nice that you take the time to discuss this.

Of course I can put my command into cron but doing that I have to rely on the backup to do its job, so that I don't risk deleting files that aren't backed out yet. Have had some problems with the Legato software resulting in occational breaks in the schedule, and having to restart the backup later on.
If I where to schedule the deletion of my files using cron and, worst case, the Legato job stops and I or the IT-department do not notice this??, the cronjob would delete files before they where backed out.
I could maximize the number of days that the files stays on the hold-disk, thus reducing the chance of error, but as long as the posibility remains it will always happen!!

But this might be a good time to stop being paranoid...

The reason for not doing cold backups is that the software is running nightover testcases that will be broken if shuting the database down.
Futhermore yoyo'ing the database shouldn't be nessecary, infact have been told that this might result in fragmentation of memoryspace(?).
As for the risk of loosing data when doing online backups I'm naively trusting my RMAN.
(Whats this... didn't I have a paranoia for errors in the filebackup of my disk...)

So.. what now...

Think I will settle with scheduling my deletion statement in cron, adding some extra days for safety.

Andreas

[Josee Bourget-Thuma · Accepted solution]

HI!

This forum is great isn't it?
I think it's funny that you trust one case of backup and not the other... ;-) We have lost data here when both backup media and RAID failed at the same time! LOL But that's an uncommon occurence. We ended up processing for one whole month in the rear. Lesson learned from that? Refresh your backup as often as possible and keep them as far back as you can. You can't prevent data loss from time to time but you CAN minimize how much time you spend reprocessing...

Here's a few more ideas based on my new understanding of your backup setup.

You still haven't told me how often you get a Legato backup. I'm gathering that you want to delete from staging area once files are on tape. Lets suppose you got a Legato backup everyday. If you delete as I suggested in my previous note but apply deletion to previous backup (2 days old), you would reduce chances of loss. Did I explain that correctly? Let me try this again: Delete files dated from just before the previous Legato backup rather than the one about to happen.

Another idea would be to schedule a zip/tar of the last 2 RMAN backups end then delete the files from the oldest one. Hard for me to help here not knowing sizes. So in order you would have: zip/tar files, delete, then start Legato backup.

To ensure that you do have the data to reprocess in case of failed backup, you could also schedule dumps your data to a flat files per table. This would add redundancy to your backup plan. It's a good practice to have as DBA anyway. Those files can also be put on your staging area and then be on tape somewhere.

Well my friend, I think we beat this one to death!

Have a little faith! ;-)
Good luck to you!
Stay in touch...

Josee.BT@wcom.com

[Andreas D. Skjervold]

Yeah he's dead but he wouldn't lie down!

Have 1 Legato every night.
Each RMAN backup ends up between 1 and 2 GB including backup of archived redologs.

Found that I have space enough to let the files remain on the staging area for a week before deletion, so I should be farly safe.

As for the logical dump of the database, this is undoubtly a good thing to have, because of this being a development site with higly priviliged users, they may accidentally drop objects etc..
I will look into this asap.

Thanks for the tip

Andreas

PS. Have to go as our administrative servers are being shutdown for relocation, as we are moving our department in Oslo to a new building. Thank God my servers are safely here north in Bodo!!... I just be without my mailbox this weekend... :(