Difference Between UNDO & REDO

Difference Between UNDO & REDO

Check the default undo tablespace.
show parameter undo

How to create new undo tablespace?
create undo tablespace UNDOTBS2 datafile '/u01/app/oracle/oradata/PRIM/undotbs2.dbf' size 400m;

Now create test tablespace:
1.create tablespace test datafile '/u01/app/oracle/oradata/PRIM/test001.dbf' size 400m;
2.create table test_tab (txt char(1000) tablespace test;
3.insert into test_tab values ('teststring1');
4.insert into test_tab values ('teststring2');
commit;

alter system checkpoint;

How to check the which redolog file is using by my database?

set linesize 1000
col member for a60
select member, l,status from v$log l, v$logfile f where l,group# = f.group# and l.status = 'CURRENT';

Check the redolog files status:
strings /u01/redo3.log | grep teststring

Check the undo tablespace status:
strings /u01/undotbs01.log | grep teststring

Note:
Insert statements does not generate undo data, any changes or modified/update/delete data will generate undo data

Chech the datafile status:
strings /u01/test01.dbf | grep teststring (because we used alter system checkpoint)

How to change the default undo tablespace?
alter system set undo_tablespace='UNDOTBS2'; (dynamic parameter)

Note:
We can create multiple undo tablespaces in database, but only 1 undo tablespaces can be active.

alter system switch logfile;

set linesize 1000
col member for a60
select member, l,status from v$log l, v$logfile f where l,group# = f.group# and l.status = 'CURRENT';

update test_tab set txt= 'teststring_uncommit' where txt = 'teststring1';
Don't hit commit, open another session and another update statement.

update test_tab set txt = 'teststring_committed' where txt = 'teststring2';
commit;

Now we have 2 types of data, commit and uncommitted data under test_tab table.

Check the redolog files status:
strings /u01/redo3.log | grep teststring

Note:
Inside current Redo log files can contain redo and undo
Redo log files can contain committed and uncommitted data.

Check the undo tablespace status:
strings /u01/undotbs01.log | grep teststring

Note: lgwr write very fast compare to dbwr process, it writes every commit and every 3 seconds.

alter system checkpoint,
all the buffers will be pushed/flushed from the memory into the datafiles.

When ckpt will write?
alter system checkpoint, online redo log switch and alter database begin backup command.

Check the undo tablespace status:
strings /u01/undotbs01.log | grep teststring

Now it will contain old images/data.

Check the datafile status:
strings /u01/test01.dbf | grep teststring (because we used alter system checkpoint)

Now it will store committed and uncommitted data.

Note:
lgwr writes both undo and redo for every dml in a transaction, a transaction can be committed and as well uncommitted data irrespective of whether ckpt has taken place or not.