Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, oraboy.

Asked: July 22, 2005 - 1:41 pm UTC

Last updated: December 23, 2011 - 8:48 am UTC

Version: 9.2

Viewed 10K+ times! This question is

You Asked

Recently I had a discussion on redolog with my collegue and I got struck here..

Its clear that LGWR flushes redo entries into Redo log under different cases including 33% full, 1 minute, log switch etc..and on while recovering from failures/crash, oracle would simply replay necessary redo entries from REDO/ARCH files..Correct me if I am wrong?

And its mentioned in docs that Redo stores segment info and change vector (binary info enough to replay the change) and that includes rowid information too. Possibly that could be related to why SAME statement generates more REDO when it affects more number of rows. (more rowids and change vector to store - correct?)

Now thinking about LOGMINER, if the exact statement is not stored in text into redo, then it would have to construct the statement back from the change vector..A simple single delete statement would have
affected 10000 rows ..In that case , does LOGMINER have to read the whole of change vector set to reconstruct DELETE statement back or is it stored anywhere ?

In other words, I am really curious whether Oracle has the statements stored somewhere within logfile or does it have to reconstruct

My friend says it has to be second option..I am thinking option 1 (probably if not in text, somewhere hashed or something)

Need your expert advise for clarity

Thanks in advance




and Tom said...

lgwr flushes the redo log when it is 1/3 full, or 1meg full, or every 3 seconds or when anyone commits - whatever happens first.

The more rows you change, the more redo generated, yes.

Logminer would show 10,000 single row delete statements for a delete statment that deleted 10,000 rows. You would see 10,000 "delete from t where rowid = ....." statements from logminer. You do not get the original SQL, to get that you would audit.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

recovery process

abz, February 12, 2007 - 7:53 am UTC

Very nice explanation.

I would like to know, what happens during
recovery... YES YES I KNOW THE REDO IS REPLAYED, but
what actually happenes, does it construct them into
DML and execute or directly apply the changes to the
data blocks in datafiles.

Tom Kyte
February 12, 2007 - 11:30 am UTC

it is a binary operation, bits and bytes - it does not generate SQL for recovery.

commit with out redolog files

Prasad, March 28, 2011 - 11:05 pm UTC

Hi Tom,

As per Oracle documentation unless change vectors written to online redo, oracle will not send message to client saying that "commit complete" to ensure changes are made permanent. But I have observed the following, I have deleted online redo log files and did few inserts and commit too and I have gotten commit complete message also. Now I am not sure about the Oracle statement. Any light on this is greatly appreciated.

Thanks,
Prasad Mynumpati.
Tom Kyte
March 29, 2011 - 4:20 am UTC

see

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:49326584371174

in Unix, when you erase a file, it doesn't really "erase it". As long as someone has it open (like LGWR), the file still exists - even if YOU cannot see it anymore.

Nothing to do with oracle on this one, everything to do with Unix file handling.

A reader, March 29, 2011 - 8:00 pm UTC

Great answer, I was having this question for very long time. I understand that it is the behaviour of Unix but after getting "commit complete" message in case no redo's are available, it is not really commit complete because we can not recover in case of crash. Do you agree...

Thanks,
Prasad Mynupati.
Tom Kyte
March 30, 2011 - 1:40 am UTC

The commit really - truly - honestly is complete.

But yes, you could not recover because you purposely erased (unlinked) the files. It would be just like LOSING the redo file AFTER it was closed and we advanced to the next one. Meaning: it is not any different then you erasing a NON-current redo log before it was archived. Meaning: you should multi-plex (use redundancy) and secure (make it so no one can actually do this) as part of your availability requirements.

Kamran Ali, December 23, 2011 - 5:11 am UTC

Hi Tom
So we can't say that Redo Log Buffer will records the number i mean if we update a row from 100 to 200 so it wont save the 200? it will just be the change in the data blocks. right?

Tom Kyte
December 23, 2011 - 8:48 am UTC

it will have 200 in there, it saves a redo change vector - a bit of data that describes "these bytes on this block must be change to this value".

that value would include the 200 - since that is what it must become.

Gautham- More questions on Redo Change Vector

Gautham, March 02, 2014 - 2:58 am UTC

Hi Tom,
A quick question of how logminer or goldengate constructs a sql from redo vector.
Let us assume a table t1 with (col1 number pk,col2 varchar2(500));
Say we have data as 1,'xxx'

Now I update the table as
1.update t1 set col2='yyy' where col1=1;

From your previous answer, I fathom the only information redo change vector is going to store is

file id, block id, row number,bytes offset,'yyy'

or something similiar to that. I am ignoring the redo for undo etc as it is beyond the scope of my question.

How will logminer or golden gate , construct the original statement by reading the above change vector?
How will it know that the update happenned 'where col1=1' so that it can run the same sql on the goldengate target?

I assume since adding supplemental logging at the database level , the redochange vector has more information
like the pk value?
I am kind of confused on how this works.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.