[NTLUG:Discuss] network backup solutions

[Robert Pearson]

Chris Cox wrote---
>The backup routine has to handle sending a ALTER SYSTEM QUIESCE RESTRICTED
>to the Oracle DB (vis ssh for example from the backup control host).  Now,
>the question is, can you live with the downtime
>or not?  Depending on the size of the db instances and the amount of
>availability you have to provide, you may have to place the DB onto
>a filesystem that is easily snapshotted via LVM.  Then you could
>quiesce, LV snapshot and ATLER SYSTEM UNQUIESCE, then backup the snapshot
>LV.  Backing up Linux filesystems using LV snapshotting is a good
>idea in general.

This is an excellent solution if your storage architecture is set up
for it.
I have only done this with EMC Symmetrix storage running EMC
TimeFinder software. It is the Feature/Function set of the
TimeFinder software that is important here, not EMC storage.
The process is exactly the same as above.
If you can find a product with the TimeFinder Feature/Function
set then use whatever storage makes sense and that product.
Snapshotting is very close. I am not sure it has the "granularity"
of TimeFinder.
You are not directly backing up the "live" Oracle database on the
Production servers but a "snapshotted" copy of the volumes so any
good backup software will work. You do have to have an Oracle
script running on the backup server to QUIESCE the database and
tell TimeFinder to "break" the mirror and create the volumes.
Depending on the amount of storage this took from 15 minutes to
two hours. The database is then restarted and put back to work
and the backup software volumes are independently copied to
tape for Historical or site Disaster Recovery.
There are actually two backups here. One that is disk to disk.
The second is disk to tape. The disk copy is a complete record
of the last 24 hours transactions and available for Recovering a
corrupted database without using tapes.
We did this for a large national equipment rental firm.

There are two alternate solutions if you do not have the ability,
time or storage volume setup to use Chris' recommendation---
(1) Use the Oracle tools to backup the Oracle database.
The primary item of interest is the rollback logs. Export this
Oracle backup to a local disk file and then compress it so you
can save lots of these files. Copy the compressed file to other
disks, tape and today, to the 40 GB disk that fits in your pocket.
The copy to tape can be done by ordinary daily backups and
any good backup software. The tapes are only for Historical
or Disaster Recovery. The local disk files are for Recovering
a corrupted database. There has to be a defined process, and
some scripting, for both making the backups and compressing
the file and doing the Recovery.
(2) Exactly the same process except use a commercial product
like SQL-Backtrack from BMC Software. SQL-Backtrack
can do everything for both backup and Recovery.

The Financial Services company used solution (1) above until
they installed SQL-Backtrack. Then they used solution (2).
The product is not important. The process and the product
Feature/Function set is.

In the Oil Patch we had to do three level backups and then
three level restores to get an application with an integrated
Oracle database fully restored and working correctly.

The main objective of all these backup solutions, in a
database world, is actually "lightening" fast Recovery for
corrupted databases. To Recover a corrupted database,
the Oracle DBAs I worked with, only wanted the latest
rollback logs in a disk file on, or easily accessible from,
the server with the problem.

Your backup objective is very important.
In the case of a Local Disaster the need for Historical Recovery
(from tape) will be limited because the impact is small. It might only
be a cluster mirror that goes down. Normally the Recovery effort
would bring this cluster mirror back up and allow it to resync with
the working mirror. My experience has been, in the case of databases,
the DBAs like to have a disk file copy of the latest rollback log and
do a recovery on the database to a close point and then allow mirror
resyncing. I may be out of date on this.

In the case of a site Disaster your need for a remote site and the
ability to do Historical Recovery will be major.

You also need to have the RTO (Recovery Time Objective) and the
RPO (Recovery Point Objective) defined. In the case of a Financial
Services company the RTO is 4 hours and the RPO is 100% fully
operational. For a large national equipment rental firm the RTO is 24
hours and the RPO is to get storage working so the volumes on disk
can be restored. If the disks cannot be recovered then the tapes have
to be used. If the tapes are unusable you are out of business.

Thanks,

Robert Pearson
rdpears2001 at yahoo.com

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.710 / Virus Database: 466 - Release Date: 6/23/2004