Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Bkp n Recovery’ Category

‘switch logfile’ not a correct method after hot backup

Posted by Pavan DBA on April 25, 2012

In order to be sure that all changes generated during a hot backup are available for a recovery, Oracle recommends that the current online log be archived after the last tablespace in a hot backup is taken out of backup mode. A similar recommendation stands for after a recovery manager online database backup is taken.

Many DBA’s or Oracle books will suggest using the ‘alter system switch logfile’ command to achieve this. The use of ‘switch logfile’ to obtain archives of the current logs is not a perfect to follow. Because the command returns success as soon as the log writer has moved to the next log, but before the previous log has been completely archived. This could allow a backup script to begin copying that archived redolog before it is completely archived, resulting in an incomplete copy of the log in the backup.

A better command to use for archiving the current log is ‘alter system archive log current.’

This command will not return until the current log is completely archived.

For the same reasons outlined above, a backup script should never just back up “all the archived redologs.” If a script does not restrict itself only to those logs that it knows to be archived, it may improperly try to back up an archived redolog that is not yet completely archived.

To determine which logs are archived, a backup script should query the v$archived_log view to obtain a file list for copying.

Posted in Bkp n Recovery | Tagged: | 2 Comments »

Media recovery not required in case of instance failure during hot backup

Posted by Pavan DBA on April 25, 2012

If an instance crashes or is aborted while one of the tablespaces is in backup mode, the tablespace will appear to need media recovery upon startup. In normal instance failure situations, automatic crash recovery using the online redologs will be automatically performed, with no intervention required.

Because hot backup mode artificially freezes the checkpoint SCN in the datafile header, it will appear on startup after a crash to be a file restored from backup. Consequently, Oracle will suggest that you apply archived redologs from the time that the tablespace was placed in backup mode.

As I explained in my earlier post related to hot backup ( the only difference between a datafile in backup mode and not in backup mode is the checkpoint SCN in the header. Because there is no data missing from the datafile, there is no need to apply recovery to it. For this reason, Oracle provides a way to clear the hot backup flags in the datafile header before you open the database.

If an instance will not start up automatically after a failure, and the reason is that a datafile needs recovery,then the v$backup view may be queried from mount mode. This view will show which datafiles are in backup mode. Based on this information, the following command may be used to take the files out of backup mode:

alter database datafile <filenum> end backup;

Posted in Bkp n Recovery | Tagged: | Leave a Comment »

What happens during HOT BACKUP???

Posted by Pavan DBA on April 23, 2012

I am just trying to explain my little knowledge on hot backup process…

During hot backup, a script or begin backup command puts a tablespace into backup mode, then copies the datafiles to disk or tape, then takes the tablespace out of backup mode.

Many people are having a misconception regarding hot backup that, during hot backup DBWR process will stop writing into datafiles. Also, they say that while the datafiles are not writable, changes are stored somewhere in the SGA, the redologs, the rollback segments etc places and will be written back to datafiles when the tablespace is taken out of backup mode.

In fact, Oracle’s tablespace hot backup does not work this way at all. It absolutely does not stop writing the datafiles, and actually allows continued operation of the database almost exactly as during normal operation.

1. The tablespace is checkpointed 2. The checkpoint SCN in the datafile header freeze to increment with checkpoints 3. Full images of changed DB blocks are written to the redologs

The above three actions are all that is required to guarantee consistency once the file is restored and recovery is applied. By freezing the checkpoint SCN in the file headers, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the redolog file containing that SCN, and apply recovery starting there. Note that checkpoints to datafiles in hot backup mode are not stopped during the backup, only the incrementing of the main checkpoint SCN flag will stop increasing.

By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be available in the redologs in case they are ever used for a recovery.

Also we will observe that huge redo will be generated during hot backup. This is the result of the logging of full images of changed blocks in these tablespaces to the redologs. Normally, Oracle logs an entry in the redologs for every change in the database, but it does not log the whole image of the database block. By logging full images of changed DB blocks to the redologs during backup mode, Oracle eliminates the possibility of the backup containing fractured blocks. To understand this reasoning, you must first understand what a fractured block is….

Typically, Oracle database blocks are a multiple of O/S blocks. For instance, most Unix filesystems have a default block size of 4k, while Oracle’s default block size is 8k. This means that the filesystem stores data in 4k chunks, while Oracle performs reads and writes in 8k chunks, or multiples. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd etc. As it is making this copy, it is reading in O/S-block sized increments. If the database writer happens to be writing a DB block into the datafile at the same time when backup is reading that block, your backup copy of the DB block could contain some O/S blocks before and after writing by DBWR. This kind of blocks which are having mismatched halves are called fractured blocks.

By logging the full block image of the changed block to the redologs, it guarantees that in the event of a recovery, any fractured that might be in the backup copy of the datafile will be resolved by replacing them with the full image of the block from the redologs.

Posted in Bkp n Recovery | Tagged: , , | 24 Comments »

which process updates controlfile, when doing complete recovery of it?

Posted by Pavan DBA on October 12, 2010

Long back i posted a voting question in my blog…

which process updates controlfile, when doing complete recovery of it?

I got results as follows


But unfortunately max votes got for a incorrect option. The correct answer is Server process.

Many DBA’s don’t know that we can perform complete recovery when we lost controlfile. (even i had some good argument with a friend on my blog on this)

If you want to know how to do complete recovery, see below link
By reading above post, you might have got the point that we are creating new controlfile. In such cases, to open the database we require latest SCN to be there in controlfile to match it with datafiles and redolog files.

If it doesn’t match, it will fail to open. So server process will take that responsibility to update the controlfile with latest SCN and this info will be taken from datafiles

Posted in Bkp n Recovery | Leave a Comment »

why it is not necessary to take undo backup?

Posted by Pavan DBA on September 29, 2010


really long time to see you…here is the new post of mine

You know…it is not necessary to take undo tablespace backup either in cold backup or hot backup. But ofcourse many of DBA’s will include that in their script…

Now a big question…if undo is not being backed up and lets say i need to do instance recovery or database recovery and need to rollback a transaction, how that will happen.

Here is the answer…

when you do some transaction, redo entries will be generated..accepted ! Just like that whenever some changes happend to undo tablespace (or more clearly undo segments) oracle will generate redo entries.

So even though you doesn’t backup undo, you have the redo entries through which you can recover or rollback the transactions.

Wann to read more…just check here

Posted in Bkp n Recovery | Tagged: , | Leave a Comment »

%d bloggers like this: