Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question of degrees in Oracle DB recovery

RE: Question of degrees in Oracle DB recovery

From: Bobak, Mark <>
Date: Tue, 29 Jun 2004 12:29:30 -0400
Message-ID: <>


First (very critical) question: Are you in archivelog mode? If not, and my first guess would be that you are not, then your assumptions about your ability to recover "as-of" the=20 date of the last backup, and you inability to recover to an arbitrary point-in-time, are correct.

Running your database in archivelog mode provides two big benefits: 1.) You can do "hot" backups (while the database is up.) 2.) You can do point-in-time recovery, also known as "incomplete = recovery".

The only real downside to archive log mode is that you have to manage, maintain, and backup, all those archive logs. This in itself can be a task.

Very briefly, Oracle backups must be "self-consistent" to be valid and usable. There are two ways to accomplish that: 1.) Do a cold backup. You do a non-abort shutdown and copy all=20 the files. They're guaranteed to be consistent cause the shutdown made sure of it.
2.) In archivelog mode, the datafiles can be copied "hot". Because they are hot, they may not be self-consistent. So, along with all the datafiles, you'll need, at a minimum, all the archive logs from the point in time when the start of the first datafile copy to the completion of that last datafile copy.
  a.) If you're doing conventional (non-RMAN) backups, you'll need

       to put each tablespace into backup mode (alter tablespace ts_name = begin backup;)

       before copying any of it's datafiles, and out of backup mode
       (alter tablespace ts_name end backup;) when the last datafile has
       been copied.  In this case, the window for which you'll need
       archive logs is actually from when the first tablespace goes
       into backup mode till when the last tablespace exits backup mode.
  b.)  If you're using RMAN, (a *good* thing, in my opinion), then you
       won't need to worry about putting tablespaces into backup mode,
       because RMAN is integrated w/ the database, it knows how to=20
       read the file consistently.  Note however, you'll still need
       the archive logs as outlined above, for the database to be

A few words about "backup mode":
When you do a conventional "non-RMAN" backup, you're copying data files with some O/S utility. That O/S utility does not know when Oracle is writing to the file, or what the size of the write is. So, in a = database
with an 8k block size, it's possible that Oracle writes a block with = multiple
1k or 1/2k writes, and it's possible that the O/S could copy a = particular
block with 1/2 the data before the write and 1/2 the data after the = write.
This is called a "split block" and is bad. To avoid this problem, = Oracle
provides the backup mode functionality. When you put a tablespace into backup mode, what happens is that the checkpoint change numbers in the=20 header stop updating and the backup checkpoint change number is updated.
>From that point forward, any time Oracle encounters a block that has not
changed since the backup mode began, it will log the entire block to the redo log, not just the change vector. That way, in the event of a recovery, if the block in the restored datafile was split during = backup,
application of the redo log changes will overwrite the entire block, = thereby
repairing the split block.

Bottom line:

1.) Change your database to archivelog mode. 2.) Make sure your on-line redo log is mirrored, if not in hardware, = than in Oracle.
3.) Same for controlfiled, mirror them. 4.) Once you're in archive log mode, you don't *need* to do hot = backups,

     it is nice to have that ability and not impact database uptime. 5.) Consider RMAN, if at all possible. RMAN is the future, and it

     also helps make your backups less error-prone. 6.) When you think you have a valid backup and recovery strategy, TEST = IT!
8.) Come up with more scenarios, and test it again!

Additional reading:
Oracle Concepts Manual
Oracle Backup and Recovery Manual
Oracle Recovery Manager (RMAN) Manual
Rama Velpuri's Backup and Recovery Book

Hope that helps,


-----Original Message-----
[]On Behalf Of Wolfe Stephen S GS-11 6 MDSS/SGSI
Sent: Tuesday, June 29, 2004 12:00 PM
Subject: Question of degrees in Oracle DB recovery

First off, I'm an Oracle newbie for sure. My main question now is more DR policy/intent
Oriented than technical. I'm still in the discovery process of all the ways an Oracle instance can be recovered, I'm now reading a PDF on online point-in-time recovery strategies and this is where I have a question.

How many of you guys provide as close as possible to the transaction-on-the-fly point-in-time recovery?

Currently, we do only an offline, once a day backup to a SAN on two Oracle applications. I was asked last Friday if we had a catastrophic failure (server destruction or totally non-recoverable disk failure) how would I recover our TPOCS database. I replied I could recover to whatever was there at 00:15 that day, because, with Crondsys we stop the database, then backup the entire Oracle directory and all of its subdirectories (I was told I actually only needed to keep the oradata folder but we have a large SAN so why not get all the stuff config file, etc) and an interface directory where daily interface files and archives are kept from a system that sends data to TPOCS via importable text delimited flat files.

I received a few concerned looks because the using departments were under the impression that I could bring them back to just before the failure. I can't and the vendor that was tasked to provide the database application was only tasked to provide a 24 hour backup scenario. If a site wants anything better they have to do it on their own after submitting the plan and procedures to the tier 3 helpdesk (the vendor) for approval.

I am doing a lot of reading right now, but I would like to get your ideas on the cost and complexity of getting a true PIT recovery system in place or can a near PIT be established like configuring the redo logs to reside on the SAN instead of the local server?


Stephen S. Wolfe, GS-11, DAFC
Data Services Manager
(813) 827-9972 DSN 651-9972=3D20

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Tue Jun 29 2004 - 11:26:27 CDT

Original text of this message