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.
No comments:
Post a Comment