RE: Database performance during hot backup

But if moving to RMAN is not feasible for one reason or another, doing the "hot" backup less than all the tablespaces at a time can help reduce the amount extra logging, since only blocks from the datafiles in tablespace currently in backup mode require an initial full block preservation to redo when changed during the hot backup window of that tablespace.

Thinking in terms of doing a tablespace at a time may also reduce to urge to blast the file copies trying to get them out of the way in as little elapsed time as possible, which might indeed inflict a peak i/o load that interferes with reads as well as writes. Having the tablespace(s) containing UNDO be in backup mode for as little time as possible is also useful, so you might try to get those out of the way in a relatively quiet period for changes. In extremus you can rotate undo_tablespaces with the alter system set undo_tablespace command so new transactions stop going into the undo tablespace you're trying to back up. (Notice that will not shift a currently running monolith to the new place, just new transactions.)

The elapsed time to back up pales in importance in comparison with the elapsed time to recover. Think about preserving head room for things still happening while backing up or doing a single tablespace recovery, but also plan for the utilizing everything you have for a full recovery or if applications are unusable due to what is unavailable anyway. That thought process applies to both RMAN and physical backups.

Heterochronous recovery works just fine as long as you have the redo and archived redo required to span the overall duration of the backups.

And move to RMAN unless there is some important reason to stay with physical backups.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rich Jesse
Sent: Friday, March 30, 2012 11:16 AM
To: oracle-l_at_freelists.org
Subject: Re: Database performance during hot backup

Hi Amir,

> I am trying to understand if it is expected to see performance
> degradation of DML transactions that are active during a hot backup.
> It is my understanding that the only noticeable thing that happens
> during a hot backup for DML statement is an increase in the amount of
> redo generation and that is because Oracle starts to log full images
> of data blocks before a change is made to a data block. I am not aware
> of any impact on performance while in hot backup mode, unless the LGWR
> is being constrained for some reason.

My kneejerk is two things:

1. The hot backup will certainly use system resources, especially IO. If your instance is heavy on IO or IO-bound, then the possibly significant IO load of the backup would likely impact Oracle transactions.
2. You may want to consider using RMAN instead of the traditional hot backup.

Rich

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l