Undo and redo are synonymous in Oracle. The interesting fact is that the undo information, stored in undo tablespaces or undo segments are well protected by redo as well.
When Oracle blocks are changed, including undo blocks, oracle records the changes in a form of vector changes which are referred to as redo entries or redo records. The changes are written by the server process to the redo log buffer in the SGA. The redo log buffer is then flushed into the online redo logs in near real time fashion by the log writer LGWR.
In other words, undo data is considered as table data or index data, any changes to undo generates a redo, and that is logged i.e., for every DML’s, the undo is stored in UNDO tablespace and Redo log files.
This mechanism is in place to have the data when the system crashes and it works as follows.
1. Oracle creates a redo even for undo
2. Checkpoint occurs
3. Dirty blocks are written to data files
4. Buffers containing the undo are written to undo tablespace.
The question rises why are we keeping the undo information in redo log files too instead of undo tablespace.
Let’s assume that we have undo data only in buffer cache and in undo tablespace and see what happens.
i. User runs the update statement and does not commit.
ii. Redo data for the update statement/change is stored in redo buffer.
iii. Undo data for the change is stored in the buffer cache
iv. Suppose, checkpoint did not occur and the undo data will not be available in the undo tablespace. But still the datafile will have uncommitted data.
v. Now, suddenly our system crashes.
vi. This will clean the data in the buffer cache.
vii. When you perform recover y of the database, redo data will be applied but datafiles will have uncommitted data. Due to this reason, we cannot open the database as we need undo information to rollback the uncommitted changes. If the undo information is wiped out from the buffer cache, we will not have this info; finally database will be in inconsistent state. i.e., we need to go for inconsistent recovery.
Above is the case when we perform a recovery of the tablespace using online and archive redo logs files when the datafile is lost/corrupted. i.e., the tablespace cannot be brought online due to uncommitted data in the datafile was lost and do not have undo information for the changes in redo log file.
Now, the second assumption is that if we have undo information stored only in Redo log files, what will happen.
The foremost function of LGWR is to write the changes/redo in the redo buffer to redo log files in a circular fashion so that the redo log files can be reused once the changes in the redo files are written to datafiles or archive log files if the database is in archive log mode.
i. Now, consider I am making a transaction and does not commit,
ii. Changes are written in the redo log. (Note, we are not going to use undo tablespace)
iii. Now, checkpoint occurs.
iv. Now, the uncommitted data will be written to datafiles
v. Suddenly, I decide to rollback.
vi. Next, what oracle does is
If the redo log is not overwritten,
Then,
Oracle will search entire redo log for the undo information and rollback
Else,
The redo log is overwritten
Oracle will not find the undo information and cannot rollback.
If oracle had designed to have the redo file not overwritten until the active undo information, then redo log file size will grow drastically as it will have both undo and redo data.Moreover, it will have following disadvantages
a. In case of any rollback, oracle has to search huge date of redo log files which would degrade the performance.
b. Since, it is multi user system, when one user performing rollback and other users session doing transactions to write redo will create redo contention.
Let us see the demonstration that the undo information is available in both undo tablespace and redo log files.
1. Create a new UNDO tablespace for testing even though we have default UNDO tablespace.
SQL> create undo tablespace undotbs1 datafile '/data1/noasmdb/datafile/undotbs101.dbf’ size 500M;
Tablespace created.
2. Create a TEST_UNDO_REDO tablespace for testing.
SQL> create tablespace test_undo_redo datafile '/data1/noasmdb/datafile/tsundoredotbs.dbf' size 1M;
Tablespace created
3. Create a table in the tablespace TEST_UNDO_REDO
SQL> create table undo_test_tab (tstcol varchar2(32))tablespace test_undo_redo;
Table created
4. Check for the current redo log group
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
5. Now, insert 2 rows in to the table and commit.
SQL> insert into undo_test_tab values ('testundovalue1');
1 row created.
SQL> insert into undo_test_tab values ('testundovalue2');
1 row created.
6. Now, let us see where all the inserted value is available.
It is available in Redo log file.
[oracle@vm1 redofiles]$ strings redo_g02a.log |grep testundovalue*
testundovalue1
testundovalue2
[oracle@vm1 redofiles]$ strings redo_g02b.log |grep testundovalue*
testundovalue1
testundovalue2
The data is committed but still it is not available in Datafile for the table as we have not perform manual checkpoint or no query to any big table to free buffers or log switch.
[oracle@vm1 datafile]$ strings tsundoredotbs.dbf | grep testundovalue*
The data is not available in UNDO tablespace.
[oracle@vm1 datafile]$ strings undotbs101.dbf |grep testundovalue*
7. Perform a manual checkpoint so that the changes can be written to the datafile.
SQL> alter system checkpoint;
System altered.
8. Now, the data are written to datafiles.
[oracle@vm1 datafile]$ strings tsundoredotbs.dbf | grep testundovalue*
testundovalue2,
testundovalue1
9. Switch the undo tablespace to UNDOTBS1
SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
10. Now, let us do switch logfile once so that it will move to Group 1 which does not have the above data.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select a.group#,b.member,a.status from v$log a,v$logfile b where a.group#=b.group# order by a.group#;
GROUP# MEMBER STATUS
---------- --------------------------------------------- ----------------
1 /data1/noasmdb/redofiles/redo_g01a.log CURRENT
1 /data1/noasmdb/redofiles/redo_g01b.log CURRENT
2 /data1/noasmdb/redofiles/redo_g02b.log ACTIVE
2 /data1/noasmdb/redofiles/redo_g02a.log ACTIVE
3 /data1/noasmdb/redofiles/redo_g03b.log ACTIVE
3 /data1/noasmdb/redofiles/redo_g03a.log ACTIVE
11. Next, let us update one row and do not commit from one session and update the 2nd row and commit from another session.
Session 1
SQL> update undo_test_tab set tstcol='Newtestundovalue1' where tstcol='testundovalue1';
1 row updated.
Session 2
SQL> update undo_test_tab set tstcol='undovalue2_commit' where tstcol='testundovalue2';
1 row updated.
SQL> commit;
Commit complete.
12. Check in Redo log file of Group 1 whether do we have both committed and uncommitted data.
[oracle@vm1 redofiles]$ strings redo_g01a.log |grep undovalue*
testundovalue1
Newtestundovalue1
testundovalue29
undovalue2_commitvm1
[oracle@vm1 redofiles]$ strings redo_g01b.log |grep undovalue*
testundovalue1
Newtestundovalue1
testundovalue29
undovalue2_commitvm1
13. Now check in undo datafile for the same contents but it will not be there as the checkpoint is not yet happened unless the flush of the dirty buffers to disk taken place, in that case, even the datafiles would contains the changed data.
[oracle@vm1 datafile]$ strings undotbs101.dbf |grep undovalue*
14. Now, perform manual checkpoint
SQL> alter system checkpoint;
System altered.
15. Now, check in undo files. We will notice the old value/pre-update values are available
[oracle@vm1 datafile]$ strings undotbs101.dbf |grep undovalue*
testundovalue2
testundovalue1
16. Check in datafiles, we will see both committed and uncommitted data are available.
[oracle@vm1 datafile]$ strings tsundoredotbs.dbf |grep undovalue*
undovalue2_commit,
Newtestundovalue1,
testundovalue2,
testundovalue1
In case, if we the flush of the dirty to disk had taken place as mentioned in Step 13, then we will find the data as below in undo and datafile
[oracle@vm1 datafile]$ strings undotbs101.dbf |grep undovalue
testundovalue2
testundovalue1
[oracle@vm1 datafile]$ strings tsundoredotbs.dbf |grep undovalue
undovalue2_commit,
Newtestundovalue1,
testundovalue2,
testundovalue1
Conclusion
Irrespective of Oracle doing checkpoint or not, both undo and redo data for a transactions (DML) whether committed or not committed are written to redo log files by LGWR. (step 12)
When oracle does checkpoint,
The undo information in the database buffer cache are written to undo tablespace (step 15)
The committed/not committed dirty buffers are written to datafiles.
References
http://pavandba.files.wordpress.com/2009/11/undo_redo1.pdf
Oracle Core Essential Internals for DBAs and Developers
No comments:
Post a Comment