I was reading one of the post for oracle hotbackup and also recovery using the hotbackups. There was quite a lot of information in the post and it took me 3 readings to understand the contents. The post was written excellently well, its just that the concept is bit complex. I thought of expressing the same in my words. At the end of this post, I have given the reference for original post as well.

This post is about few details about what happens during hot backup and how the Oracle database recovers from the hot backup.

When we want to take a hot backup for the database, the neccessary condition for the same is that, your database should be archivelog enabled. You can take a backup of one tablespace at a time by putting the tablespace in “Begin backup” mode.

What happens when we do begin backup:

You can start hot backup for a tablespace by issuing following command.

SQL> alter tablespace APPS_TS_SEED begin backup;

Tablespace altered.

This tablespace contains 2 datafiles.

SQL> select file_name from dba_data_files
2 where tablespace_name = ‘APPS_TS_SEED’;

FILE_NAME
——————————————————————————–
/dy/oracle/product/apps11i/scmidcdata/a_ref01.dbf
/dy/oracle/product/apps11i/scmidcdata/a_ref02.dbf

You can copy these datafiles now to backup location. When you issue a begin backup command the section of headers in these files will get freezed and cannot be updated. When I say section, that mean that some part of header is still updatable. The hot backup SCN and checkpoint counter is still moving in accordance to database activities. Note then this hot backup SCN is not the same as master checkpoint SCN of the datafile. Master checkpoint SCN is the one which will get updated normally when checkpoint happens, where as “hot backup SCN” is the one which gets incremented when the datafile is in backup mode and redos are getting generated. So obviously “hot backup SCN” should be greater then master checkpoint SCN. Also there is begin backup SCN, this is the SCN # when we put the tablespace in “Begin Backup” mode. This information is also updated in the datafile and gets freezed till the time tablespace is in backup mode.

So when the file is in begin backup mode, no data will get updated in the datafile, but in the header, hot backup SCN and checkpoint counter will get updated when user activities happens.

When a user does “end backup”, following activities happens.

1) Oracle will unfreeze the frozen section of the header

2) master checkpoint SCN in datafile and control file will get updated with hot backup SCN

3) The status in the file header goes from 0x1 (hot backup mode) to 0x4 (normal online mode).

4) A redo record for each file in the tablespace is created with the file number and begin backup SCN.

Note here that when we are issuing “end backup” command, actually in redo log files the SCN at the time of “Begin backup” will be updated.

5) During recovery after the end backup redo record is encountered the file in question has its status updated from 0x1 (hot backup mode) to 0x0 (consistent mode).

We saw above that tablespace APPS_TS_SEED is having 2 datafiles, so when we issue a command “alter tablespace APPS_TS_SEED end backup;” redo file will have a end backup marker for both of these files. This is shown as given below.

==============================================================
REDO RECORD – Thread:1 RBA: 0x000a17.00000242.0010 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.0496800c SUBSCN: 1 01/01/2008 22:54:53
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker – file:19 scn: 0x0000.04967e28

REDO RECORD – Thread:1 RBA: 0x000a17.00000242.0048 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.0496800c SUBSCN: 1 01/01/2008 22:54:53
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker – file:20 scn: 0x0000.04967e28
==============================================================

The SCN mentioned in “End backup marker” lines is the SCN at the time of “Begin backup” command.

OK, the important thing is, why Oracle is having such a behaviour. Why its putting a begin backup SCN number along with a file number when we are issuing a command “End Backup”. Lets take a deep look into that.

Lets say that we have a recent backup at ‘y’ time (say 10-Nov-2007) and before taking this hot backup we have another hot backup taken previous to that at y-n (where n = 1 week, so actual date will be 3-Nov-2007). So when we took a backup on y-n time, redo files where having the end backup marker and the begin backup SCN number at the time this backup was initiated. Also when we are taking a backup now on y time, the redo log will again have a end backup marker along with begin backup SCN number.

Now assume that we are restoring our backup taken at y time. We have restored all the files from the backup taken at y time except 1 datafile. One of the datafile was corrupted in our latest hot backup, so we are restoring that one file from the 1 week old hot backup (y-n).

After restore when we start recovering the database, it will start applying the redos from y-n time (since that is the oldest file in our restoration). For the file, which we restored from the y-n backup, the redo records will start getting applied. The recovery process for this file will continue until the begin backup SCN # in datafile header is equal to the end backup marker SCN # in redo log file. When the begin backup SCN # in the datafile header is same as end backup marker SCN # in redo log file, oracle will mark the file headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo). This does not mean that this file does not need any recovery. This only means that it does not need any hot-backup-neccessary redo. This file can further be recovered from the further redos. Do not confuse this consistancy of the file with database consistancy.

What will happen if the redo log is not having this begin backup SCN number and just the file number.

In this case when it start applying the redos from y-n backup and the recovery would stop after all redo records containing the end backup marker from the y-n backup had been encountered. It wont even apply the redos from y backup.
Thats the reason Oracle Provide another level of protection by specifying the begin backup SCN. In this case oracle will compare the begin backup SCN # in datafile header(backup taken at y time) with the end backup marker SCN # present in redo log file (at y-n time) and if the begin backup SCN # in datafile header is equal to end backup marker SCN # in redo log file, then it will mark datafile headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo). Again after making the file consistant, further transactions after end backup will be applied from redo log files.

From the previous scenario, with the begin backup SCN embedded in the redo record for each file, Oracle would continue to recover through the necessary redo. It would consider a file consistent only when it found the redo record for the end backup statement that contained the begin backup SCN that corresponds with the begin backup SCN stored in the file header. The begin backup SCN is a portion of the frozen section of the file header when the BEGIN BACKUP command was issued.

I hope this helps !!

References:

Thanks to Eric providing such details.
http://esemrick.blogspot.com/2006/02/pleasure-of-finding-oracle-things-out.html