Re: RMAN vs. OS Backups

From: John P. Higgins <jh33378_at_deere.com>
Date: Thu, 31 Dec 1998 11:20:05 -0600
Message-ID: <368BB245.183F9DE7@deere.com>

Pete,

When you imported the 4 tables, they were not consistent with the rest of your database. In fact, unless you took special precautions during the export, the 4 tables were not even consistent with each other! If the tables were static or your application could stand this, no problem.

The other point about import is that you cannot apply the logs to re-capture the transactions since the export.

My integrated application, SAP, cannot stand this. All 8,000+ SAP tables must be consistent. I can restore / recover any datafile or tablespace to the current point-in-time. Or I can restore / recover the entire database to a prior point-in-time.

Fortunately, my only needs for prior point-in-time recoveries came during initial data loads (surprise, the legacy data wasn't as clean as the users thought!). This would be a MAJOR pain in a production system.

While DBA errors are always possible, these are minimized in the SAP environment. In addition, whenever the DBA's need to perform a high-risk procedure, we first shut down the application and the database and take a cold backup.

I have a greater fear of a runaway program or user error. Here is what I have done for 'logical' recovery: I bought BMC DB-Log Master for Oracle. By reading the Oracle redo logs (on-line and / or archived), this product can create the SQL statements to back out any transaction. In some cases, this can be used directly; in other cases it must be interpreted by the users who manually enter the reversing transactions.

Yes, export / import have many valid functions outside the backup / recovery arena.

Peter Sharman wrote:

John

There's a lot of useful information in what you sent out, but I'd have
to disagree with one point (sorry to nit pick!).  I have recovered
databases using only an export file.  It was a Production system, and a
junior DBA (not me!) dropped 4 tables which luckily ended up being
static.  Rather than recovering the database to a point-in-time and thus
losing data, we were lucky enough to just have to import 4 tables.
Export also allows the recovery of just one table more easily than an
image copy (even though both are possible), as well as its other
advantages during migration, defragmentation etc.  Admittedly, these are
outside a backup/recovery scenario.

Pete

"John P. Higgins" wrote:

>  We used to do OS backups, both hot and cold. The databases were
> small, and backup to the small, slow unix tapes was feasible. We had
> good scripts executed by cron. By good scripts, I mean that they
> automatically picked up the changes to the set of database files.
>
> Then we installed SAP. The SAP databases are much bigger. We have over
> 30 databases in the 20 - 70 GB range. In the future, our databases may
> reach 500 - 1000 GB. 7*24 operation means cold backups are seldom
> possible.
>
> We have been using OEBU (RMAN's predecessor) for 2 years. As we
> convert to Oracle 8, we are naturally converting to RMAN. We use
> Veritas NetBackup as our media manager. This routes the backups to a
> set of high-spead tape drives in a tape silo. Veritas keeps track of
> which file(s) are on which tape(s).
>
> OEBU and RMAN error-checking is superior to anything we coded into our
> own scripts. It is not possible to get half a backup -- the backup job
> succeeds completely or it fails.
>
> OEBU and RMAN automatically backup and purge the archived log files as
> part of every database backup. We also automatically run archive log
> backups / purges anytime the archive directory exceeds 50% full. This
> is where the network connection to the tape silo is a great benefit --
> no one has to mount a tape.
>
> We use two backup catalogs. One records half the databases, the other
> records the other half. Each records the backups of the other!
>
> Don't belittle the benefits of the recovery catalog. This has all the
> info needed to recover a file, a tablespace, the whole system to
> current or a prior point-in-time. Yes, you could include all this in
> your OS backup scripts -- many (most?) do not.
>
> As we have added databases, we have developed a procedure to restore a
> backup to a different machine and rename the database files, etc. In
> other words, we clone it. Easy with OEBU or RMAN -- a little harder
> with OS backups.
>
> RMAN adds a new style of hot backup: it no longer must place a
> tablespace in backup mode before copying the files. This eliminates
> the increased logging that accompanies pre-RMAN hot backups.
>
> Another new feature with RMAN is true, block-level incremental
> backups. We are still trying to optimize our strategy for
> incrementals.
>
> As for export, we do a weekly export to /dev/null. This checks the
> validity of all databalocks below the tablespaces' high water marks.
> Since there is no possibility to recover logged transactions after an
> import, export / import has no place in backup / recovery.
>
> I can't know your situation, so I can only offer our experience.
>
> HTH
>
> Rodgers, Tony (Atlanta) wrote:
>
>> Does anyone have any practical experience using RMAN in Oracle8
>> such that you could briefly explain the benefits of using it rather
>> than
>> a
>> combination of O/S backups and exports?
>>
>> I'm familiar with some of the benefits RMAN is supposed to offer in
>> general, but there is some overhead associated with its use (such as
>>
>> the keeping of the recovery catalogs).  Is it simply easier to just
>> do
>> operating system backups (cold) periodically than wrestle with the
>> RMAN configurations?  Or would you highly recommend using RMAN?
>>
>> Just curious before launching a backup/recovery scheme for a
>> development database.
>
--

Regards

Pete

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Peter Sharman                              Email: psharman@au.oracle.com

WISE Course Development Manager            Phone: +1.650.607.0109
(int'l)
Worldwide Internal Services Education            (650)607 0109 (local)
San Francisco

"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not!  It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  Received on Thu Dec 31 1998 - 11:20:05 CST