Friday, February 24, 2006

The Pleasure of Finding Oracle Things Out

Yesterday I was speaking with a colleague about various technical topics (as we frequently do) and he brought up a good question. He remembered reading that upon the issuance of the ALTER TABLESPACE END BACKUP command Oracle creates a redo record with the begin backup SCN and he wanted to know why Oracle needed to put this SCN in the redo record? More specifically, why doesn’t Oracle create the end backup marker in the redo stream with just the file number? Then during recovery, Oracle could simply read this redo record and consider all “hot-backup-necessary” redo for that file applied?

That seemed like a fair question. I told him that I had seen this begin backup SCN in a log file dump associated with an END BACKUP statement. So, I knew the information he read is correct. However, I could not immediately give him an answer for this mechanic. Of course, the fact that I could not initially get my mind around the mechanic and formulate a response other than, “I don’t really know” bothered me. I assume it is fairly common knowledge that all redo between the BEGIN BACKUP and END BACKUP commands is required for recovery; every good backup and recovery guide tells us this. But, I really didn’t know why the begin backup SCN was required in the redo record created from the END BACKUP statement.

I took pen to paper and reviewed what I knew of the END BACKUP statement.

  • When issued it tells Oracle to unfreeze the frozen section of the header. Notice I said section. The entire header of each affected file is not immutable during the course of a user-managed hot backup. For instance, the hot backup SCN and checkpoint counter in the header is moving in accord with the database changes to the file(s).
  • Oracle updates the master checkpoint SCN in the file header and control file with the hot backup SCN (the current SCN for the file). Remember a file in backup mode is still subject to normal modifications, checkpoints, etc.
  • The status in the file header goes from 0x1 (hot backup mode) to 0x4 (normal online mode).
  • A redo record for each file in the tablespace is created with the file number and begin backup SCN.
  • 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).

    Here is an example of a redo record created for a file affected by the END BACKUP statement. I have bolded the begin backup SCN.

REDO RECORD - Thread:1 RBA: 0x000030.00000002.0010 LEN: 0x0038 VLD: 0x01
SCN: 0x0000.004b0b57 SUBSCN: 1 02/23/2006 15:59:59
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:17.1
End backup marker - file:3 scn: 0x0000.004b0b4c

Okay, let me return to my colleague’s question. Why does Oracle place the begin backup SCN in the redo record and not just the file number? Consider the following:

At time y we take a complete hot backup of our production database. Of course we cannot instantaneously take a backup at a single point in time as it takes some time to get all tablespaces in hot backup mode. But, for the sake of this example let y be the “time” we associate with our most recent backup of the database. We also have complete hot backups of our database at times y-n, and y-2n where n is a day, week or some other arbitrary time interval.

What happens if we restore all but one of our files from the backup taken at y. Maybe, we had a corrupt version restored for one of the files and we had to restore a copy from the y-2n backup. That is, we have one file restored from y-2n and the rest from y. Our recovery would start using log files from y-2n as that is the oldest file in our restored database. In our redo stream we have redo records for the END BACKUP commands for the files backed up during the y-2n backup. Remember, each of these redo records contains this mysterious begin backup SCN. While rolling forward Oracle would hit these redo records from the y-2n complete hot backup. What would happen if Oracle simply put the file number in the redo record for the END BACKUP command? It would immediately update the file headers to 0x0 (file is consistent and needs no further “hot-backup-necessary” redo) for the files backed up at y. Well, this would cause major issues. The recovery would stop after all redo records containing the end backup marker from the y-2n backup had been encountered. However, Oracle provided another level of protection from this scenario, the begin backup SCN.

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.

All is well, now that it feels I have gotten my mind around this one.


Anonymous Anonymous said...

First thing
why states changes to ox0 when end backup redo record encounters??
i take backup on say 1-july-2006 nite. completed backu with in 8 hours.
my files corrupted on 3-july-2006.
I want to restore upto current time.
According to you it will make it consistent as soon as it find end backup redo record???

why it stores begin backup SCN in end backup redo record??????
well going by your example if i am resoring from privious one backup say i taken it on 30 -june -2006.
then end backup of this begin backup will be on redo record of 1-june-2006. and it will make my file consistent and will say no further records needed???????

7/14/2006 12:36 AM  
Blogger Eric S. Emrick said...

anonymous said...

First thing...

Eric said...

Yes, after rolling through the end backup marker it will make the FILE consistent (no fracture blocks). However, it does not mean the file is at a consistent point in time with the other relevant files in the database. The FILE is consistent not necessarily the DATABASE.

anonymous said...


Eric said...

Oracle stores the begin backup SCN in the end backup redo entry. This is a fact. It tells Oracle when no further recovery is required to account for the file being in backup mode. Once again, file consistency in this context is the lack of fractured blocks. Just because you hit the proper end backup marker in the redo stream does not mean all recovery is necessarily finished. It just means all recovery on behalf of the backup itself is completed. That is, it can be further recovered without having to be concerned with fractured blocks. Don't confuse FILE consistency with DATABASE consistency.

7/16/2006 9:42 AM  
Anonymous Anonymous said...

Hi Eric,

Really nice post !!! Thanks for such a level of details. I still have 1 doubt in this. Let me explain my point here.

Lets say that current SCN is 5 when we issued a begin backup command and datafile header section contains the checkpoint SCN # as 5. This header section is frozen now and only the hot backup SCN # will get updated when the user activities occur. When we do "end backup", redo log files will have SCN# as 5(Which is same as begin backup SCN) and lets say hot backup SCN # is 8. So SCN from 5 to 8 will get applied after doing "end backup".

At the time of recovery when we restore the datafile from y-2n time, that might have SCN # as 2 (say for example, at that time of begin backup) in the datafile header and the redo at that time will have a end backup marker with SCN # as 2, so in this case when the SCN # in redo matches with SCN# in datafile header, then how the recovery will proceed till SCN #8. As soon as the datafile header SCN # becomes same as redo log end backup marker SCN #, wont it make the datafile status as consistent?

Just want to understand how it will decide to go further with recovery or to stop.

1/02/2008 4:47 AM  
Blogger Unknown said...

Something I don't understand. You speak about a "Hot Backup SCN". Where do you find this?
I have made datafile header dumps , but nowhere I found it.
Could you help me?


10/05/2009 8:05 AM  
Blogger Eric S. Emrick said...

In the data file header dump, the hot backup SCN is on the line with:

Backup Checkpointed at scn:

10/06/2009 11:08 AM  
Blogger daspeac said...

I have heard about another way of access 2003 not recovered or repaird. Besides, you can visit my blogs at: or where I’m trying to share my experience with regard to data corruption issues

11/04/2010 2:46 PM  
Anonymous Anonymous said...

Eric how di u got that redolog records i meani want to begin bacup scn during end backup

could u pls tell me

10/05/2011 8:32 AM  
Blogger Unknown said...

To delve deeply into RedOne Records is an exciting adventure. Advancments in RedOne Records can be linked to many areas. While it has been acknowledged that it has an important part to play in the development of man, it is yet to receive proper recognition for laying the foundations of democracy. Crossing many cultural barriers it still draws remarks such as 'I wouldn't touch it with a barge pole' and 'i'd rather eat wasps' from the aristocracy, who are yet to grow accustomed to its disombobulating nature.

7/10/2014 2:49 AM  
Blogger telas mosquiteiras said...

telas mosquiteira
telas mosquiteiro

As telas mosquiteira sp , telas mosquiteiro sp garantem ar puro por toda casa livrando-a completamente dos mosquitos e insetos indesejáveis. As telas mosquiteira garantem um sono tranquilo a toda família, livrando e protegendo-nas dos mais diversos insetos. Muitos destes insetos são transmissores de doenças e a tela mosquiteira é indispensável no combate a mosquitos transmissores de doenças.

A dengue, por exemplo, já matou centenas de pessoas só na capital de São Paulo e um pequeno investimento em nossas telas mosquiteiras podem salvar vidas. As telas mosquiteiras também impedem a entrada de insetos peçonhentos como as aranhas e os escorpiões, estes insetos também oferecem risco, pois seu veneno em poucos minutos podem levar uma criança a morte.
telas mosquiteira jundiai
telas mosquiteiro jundiai
telas mosquiteira aplhaville
telas mosquiteiro alphaville
telas mosquiteira granja viana
telas mosquiteiro granja vinana
telas mosquiteira cotia
telas mosquiteiro cotia
telas mosquiteira tambore
telas mosquiteiro tambore

A chegada da temporada Primavera/Verão traz consigo a elevação da temperatura e a maior ocorrência de chuvas. Mas não é só isso. As estações mais quentes do ano causam muita dor de cabeça e muitos zumbidos indesejáveis em função das pragas urbanas – pernilongos, baratas, cupins e outros insetos -, que afetam todas as regiões brasileiras.

Nossa missão é oferecer telas mosquiteiras de qualidade a um preço acessível, fazendo com que as telas mosquiteiras sejam uma opção viável para muitas pessoas.

telas mosquiteiras Jundiaí
telas mosquiteiro Jundiai
telas mosquiteiras jundiai
telas mosquiteiro industria
telas mosquiteira restaurante
telas mosquiteiro restaurante
telas mosquiteira empresa
telas mosquiteiro empresa

10/11/2016 9:01 AM  

Post a Comment

<< Home