RE: Question of degrees in Oracle DB recovery

From: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Tue, 29 Jun 2004 20:23:18 +0200
Message-Id: <6.0.1.1.0.20040629201308.440bad70@pop.xs4all.nl>

Stephen,

Apart from being able to keep your DB up-and-runnng 24x7, doing hot backups also preserve the carefully optimize SQL statements in your SQL area, not to forget all your caches etc.

At I site I'm working (almost) every database gets shut down on Sunday. The spikes in the I/O graphs are tremendous: when everyone comes in, Oracle has to re-optimize all statements, and refill all caches.

I think it was Connor McDonald (www.oracledba.co.uk) who discussed this topic two years ago at UKOUG in Birmingham. Oracle is at its best in optimizing I/O and memory-usage. Don't waste that by breaking up a nice and stable situation, reached at the end of the day, by doing cold backups.

When you perform OS/backups (non-RMAN), be aware that seldomly visited datablocks are not validated. Although this doesn't happen too often with today's HW, verification by creating exports or running dbverify

At 12:29 PM 6/29/2004 -0400, you wrote:
>Stephen,
>
>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
> consistent.
>
>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.

Regards, Carel-Jan

===
If you think education is expensive, try ignorance. (Derek Bok) ===

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jun 29 2004 - 17:37:34 CDT