Redo Log and Undo tablespace

Hi All,

Redo logs allows you to replay your transactions as the name suggest Re - Do .

To understand redo logs you need to understand how block buffer cache works. When we insert data into oracle its does not directly  inserted into disk. It goes into buffer first and then when the buffer is full its writen to disk this makes oracle look faster as physical insert takes more time then writing to disk. (Read the lines from  Oracle book at the End)

There are 3 things of interest with redo logs. Redo log buffer (SGA), Online (Redo file group on Disk)  and Archived (After a group is full its archived) . The online Redo has 2 sets in RAM, as one gets full the the data is writen to other redo file. And the first one is written to disk .  The Redo logs which are written to disk are the only ones that can be recovered. The ones that are in redo buffer are not recovered is system fails

Online Redo logs generally tell us what is in the Buffer block ( I used the word generally because  not everything  is covered depends on how recently redo buffer was flushed) .  The buffer

Consider a Scenario of a Insert

Insert will generate undo in the undo segment and redo in the redo buffer. The redo buffer also captures the undo segment changes.  Important point to be remembered is when we insert data into the database they are first writen to block buffer which is part of SGA (RAM) and then database writer flushes them to disk once block buffer is full . There are two scenarios for flushing redo and block buffers. First is when it gets full or when the online redo log file that contains the redo is full and a switch to next group is made at this point the Block buffer is flushed and other is when we commit.

How redo handles system crash

1. If the system crashes now . The entire insert is not recoverable

2. If the block buffer fills up first , then the database has to empty content of block buffer to disk to accept new rows. Before doing this the Redo buffer is writen to disk by log writer. Remember the redo log contains logs for undo segment as well. So if the system crashed immediately after writing redo log before writing the block buffer to disk we can get the system back to stable state.

So The Basic about Redo is " They are always writen first to disk before any changes are writen or more  specifically before the Block buffer is flushed to disk" This allows consistency.

Note about Append -- Since append by passes the SGA or Block buffers they do not generate redo logs . When we use  append the changes are directly writen to disk by passing the RAM.(SGA)

What happens when we commit

When we commit the content of SGA are writen to disk. This means redo logs are writen to disk first and then the Block buffer is flushed to disk. So the data you entered is in the disk. For transaction involving lot of data the redo logs are regularly flushed to disk after they get full and same is case with Block buffer.

Point to be remembered is they allow the system to be recovered only till the point of Commit

Lines From an Oracle  book

To understand how online redo logs are used, you’ll need to know something about checkpointing, how the database buffer cache works, and what a process called Database Block Writer (DBWn) does. The database buffer cache and DBWn are covered in more detail a later on,, but we’ll skip ahead a little anyway and touch on them now.The database buffer cache is where database blocks are stored temporarily. This is a structure in

Oracle’s SGA. As blocks are read, they are stored in this cache, hopefully so we won’t have to physically reread them later. The buffer cache is first and foremost a performance-tuning device. It exists solely to

make the very slow process of physical I/O appear to be much faster than it is. When we modify a block by updating a row on it, these modifications are done in memory to the blocks in the buffer cache.

Enough information to redo this modification is stored in the redo log buffer, another SGA data structure. When we COMMIT our modifications, making them permanent, Oracle does not go to all of the

blocks we modified in the SGA and write them to disk. Rather, it just writes the contents of the redo log buffer out to the online redo logs. As long as that modified block is in the buffer cache and not on disk,

we need the contents of that online redo log in case the database fails.

If, at the instant after we committed, the power was turned off, the database buffer cache would be wiped out.

If this happens, the only record of our change is in that redo log file. Upon restart of the database,Oracle will actually replay our transaction, modifying the block again in the same way we did and

committing it for us. So, as long as that modified block is cached and not written to disk, we can’t reuse that redo log file.

This is where DBWn comes into play. This Oracle background process is responsible for making space in the buffer cache when it fills up and, more important, for performing checkpoints. A checkpoint is the

writing of dirty (modified) blocks from the buffer cache to disk. Oracle does this in the background for us. Many things can cause a checkpoint to occur, the most common being a redo log switch.

As we filled up log file 1 and switched to log file 2, Oracle initiated a checkpoint. At this point, DBWn started writing to disk all of the dirty blocks that are protected by log file group 1. Until DBWn flushes all of

these blocks protected by that log file,

 

Undo segment is the rollback segment. The changes you make to the table, the index values for insert , update or delete are stored in undo segment. Earlier undo segment was called rollback segment.

Redo log primary purpose is for Instance recovery. There are usually 2 redo log files. They gurantee state of data base in case the instance fails.

Redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the undo segment data block, and the transaction table of the undo segments.

Redo entries record data that you can use to reconstruct all changes made to the database, including the undo segments. Therefore, the redo log also protects rollback data. When you recover the database using redo data, the database reads the change vectors in the redo records and applies the changes to the relevant blocks.

Suppose when we are copying data from one table to another it will have a undo so it will have a redo log. By saying it as unrecoverable we are getting rid of redo and undo

create table new_table unrecoverable as select * from old_table where ....;
drop table old_table;