he..he... kalo masih kurang jelas dan DSI tidak ditemukan, ini saya paste dari salah satu DSI :)
DSI403e: Recovery Architecture Components D12866GC10 Edition hal 12 (versi PDF): Rollback The server process reads the undo blocks belonging to the transaction, reads the appropriate data blocks, and puts the old data into the blocks again. (Incidentally, this process moves the block forward in time again. It does not restore the block to previous state, as other transactions may have altered other rows in the same block). Making a change to the data block generates redo; however no undo is generated. Similar to a commit, a final record is inserted into the redo buffer and a flush is requested. kalo baca paragrap diatas, tulisan si tom memang berkorelasi, ya itu dia cuman butuh effort untuk membuktikannya mengenai dbwr -- lgwr, mustinya kalau konsep ACID dan arstektur oracle dah ngelotok , bukan sesuatu yg dipertanyakan :) begini pada saat si buffer cache sedang sibuk kerja, terjadi transaksi, kadang si dirty buffer harus diflush oleh dbwr ke datafile, nah sebelum dirty buffer tsb benar2 yakin akan ditulis, oracle akan make sure bahwa LOG nya sudah duluan ketulis di disk (write ahead logging), logikanya hal tsb secara normal operasional mungkin gak mungkin karena ada checkpoint, transaksi punya "transaction control" , dll. tapi.... DSI403e: Recovery Architecture Components D12866GC10 Edition hal 64 (versi PDF): LGWR before DBWn: At some point the block buffer cache needs to write the dirty blocks to disk. The DBWn is not permitted to write a block that has redo information in the redo buffer. Only blocks that do not have redo log data in memory may be written out. If the DBWn process must write the blocks, it will signal the LGWR process to flush the redo buffer, and then wait with writing the data blocks until the LGWR signals back it has written the redo to disk. This ensures that the first rule (log before data) is also maintained on the disk versions of the redo and block data. -- thanks and regards ujang | oracle dba jakarta | http://ora62.wordpress.com On Wed, Sep 24, 2008 at 1:25 PM, Yoel Susanto <[EMAIL PROTECTED]> wrote: > Karena diskusi ini sudah semakin tidak terbaca secara historical...tanggapan > saya taruh di > > http://www.indo-oracletech.com/forum/viewtopic.php?f=4&t=307&p=1582#p1582 > > Siilakan di baca pak Ujang :) > > 2008/9/24 Ujang Jaenudin <[EMAIL PROTECTED]> > >> 1. google saja DSI401 (dumps, crashes, and corruption) >> 2. dbwr bisa memerintahkan lgwr untuk flush log buffer (dbwr write >> ahead logging), kejadiannya bisa pas checkpoint, >> atau pada saat free buffer inspected, etc >> 3. pelaksana flushing tetep si lgwr, >> >> hm....saya kalo baca article si tom kok rasanya gak bisa secara mudah >> diterima secara harfiah saja, butuh another way to prove it :) >> bahkan kadang2 musti buka kamus basic arsitektur oracle :) >> >> >> -- >> thanks and regards >> ujang | oracle dba >> jakarta | http://ora62.wordpress.com >> >> On Tue, Sep 23, 2008 at 9:56 PM, Yoel Susanto >> <[EMAIL PROTECTED]<yoel.susanto%40gmail.com>> >> wrote: >> > 1. hmmm menarik, bisa bantu saya untuk mengerti analisa anda dari hasil >> test >> > case itu pak? >> > bagian mana yang menunjukan pada anda bahwa anda penambahan redo di redo >> > file saat anda melakukan rollback. >> > >> > 2. anda bilang >> > >> > *rollback -> flush log buffer >> > >> > pada bagian ini sepertinya match : >> > "rollback is buffered in the cache just like data blocks are. >> > the rollback blocks are written into the buffer cache and if needed, >> > dbwr will write them to disk - >> > into the rollback segments on disk. "* >> > >> > - Which process flushes log buffer sih pak? DBWR atau LGWR? :) >> > - Saya yakin rollback yang di maksud Tom disini adalah rollback blocks >> not >> > rollback event/execution. >> > rollback segments juga bukan nama lain dari redo log file khan. >> > >> > 2008/9/23 Ujang Jaenudin >> > <[EMAIL PROTECTED]<ujang.jaenudin%40gmail.com> >> > >> > >> >> :) >> >> mengenai log buffer space, ya....penyebabnya 2 itu.... >> >> saya asumsikan "log buffer space" wait event ini berkorelasi dgn >> >> statistic "redo log space requests". >> >> >> >> rollback -> flush log buffer >> >> >> >> pada bagian ini sepertinya match : >> >> "rollback is buffered in the cache just like data blocks are. >> >> the rollback blocks are written into the buffer cache and if needed, >> >> dbwr will write them to disk - >> >> into the rollback segments on disk. " >> >> >> >> untuk membuktikannya, saya coba test case di pc berikut ini: >> >> >> >> select * from v$log; >> >> -- current redolog adalah group#1 >> >> >> >> select member from v$logfile; >> >> >> >> alter session set tracefile_identifier = TRACE_REDOLOG; >> >> >> >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; >> >> >> >> ----- Redo read statistics for thread 1 ----- >> >> Read rate (ASYNC): 8292Kb in 9.75s => 0.83 Mb/sec >> >> Total physical reads: 12288Kb >> >> Longest record: 11Kb, moves: 0/19413 (0%) >> >> Change moves: 10634/35530 (29%), moved: 3Mb >> >> Longest LWN: 600Kb, moves: 2/1914 (0%), moved: 0Mb >> >> Last redo scn: 0x0000.c3f3c723 (3287533347) >> >> ---------------------------------------------- >> >> >> >> begin >> >> for i in (select dcid from cc.dialcodes) loop >> >> update cc.dialcodes set dctext='wwwwwwwwwwwwwwwwwwwwwwwwwwww' where >> >> dcid=i.dcid; >> >> end loop; >> >> rollback; >> >> end; >> >> >> >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; >> >> >> >> ----- Redo read statistics for thread 1 ----- >> >> Read rate (ASYNC): 9496Kb in 10.73s => 0.86 Mb/sec >> >> Total physical reads: 12288Kb >> >> Longest record: 11Kb, moves: 0/22245 (0%) >> >> Change moves: 12217/41052 (29%), moved: 3Mb >> >> Longest LWN: 602Kb, moves: 2/1995 (0%), moved: 0Mb >> >> Last redo scn: 0x0000.c3f3c92e (3287533870) >> >> ---------------------------------------------- >> >> >> >> begin >> >> for i in (select dcid from cc.dialcodes) loop >> >> update cc.dialcodes set dctext='wwwwwwwwwwwwwwwwwwwwwwwwwwww' where >> >> dcid=i.dcid; >> >> end loop; >> >> commit; >> >> end; >> >> >> >> alter system dump logfile 'E:\ORADATA\ORA10\REDO01.LOG'; >> >> >> >> ----- Redo read statistics for thread 1 ----- >> >> Read rate (ASYNC): 9887Kb in 11.79s => 0.82 Mb/sec >> >> Total physical reads: 12288Kb >> >> Longest record: 11Kb, moves: 0/23457 (0%) >> >> Change moves: 12898/43338 (29%), moved: 4Mb >> >> Longest LWN: 602Kb, moves: 2/2031 (0%), moved: 0Mb >> >> Last redo scn: 0x0000.c3f3c9a4 (3287533988) >> >> ---------------------------------------------- >> >> >> >> ====================================================== >> >> detail rollback: >> >> >> >> REDO RECORD - Thread:1 RBA: 0x000031.00004a1e.0164 LEN: 0x00f8 VLD: >> >> 0x01 >> >> SCN: 0x0000.c3f3c914 SUBSCN:1036 09/23/2008 19:05:46 >> >> CHANGE #1 TYP:0 CLS: 1 AFN:6 DBA:0x018000a3 OBJ:51441 >> >> SCN:0x0000.c3f3c914 SEQ: 98 OP:11.5 >> >> KTB Redo >> >> op: 0x02 ver: 0x01 >> >> op: C uba: 0x00804ba1.0055.36 >> >> KDO Op code: URP row dependencies Disabled >> >> xtype: XR flags: 0x00000000 bdba: 0x018000a3 hdba: 0x0180008c >> >> itli: 1 ispac: 0 maxfr: 4858 >> >> tabn: 0 slot: 21(0x15) flag: 0x2c lock: 0 ckix: 0 >> >> ncol: 7 nnew: 1 size: 0 >> >> col 5: [28] >> >> 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 >> 77 >> >> 77 77 77 >> >> CHANGE #2 TYP:0 CLS:36 AFN:2 DBA:0x00804ba1 OBJ:4294967295 >> >> SCN:0x0000.c3f3c914 SEQ: 6 OP:5.6 >> >> ktubu redo: slt: 35 rci: 54 opc: 11.1 objn: 51441 objd: 51441 tsn: 6 >> >> Undo type: Regular undo Undo type: User undo done Last >> >> buffer split: No >> >> Tablespace Undo: No >> >> 0x00000000 >> >> ktuxvoff: 0x04b4 ktuxvflg: 0x0002 >> >> >> >> REDO RECORD - Thread:1 RBA: 0x000031.00004a1f.006c LEN: 0x00f8 VLD: >> >> 0x01 >> >> SCN: 0x0000.c3f3c914 SUBSCN:1037 09/23/2008 19:05:46 >> >> CHANGE #1 TYP:0 CLS: 1 AFN:6 DBA:0x018000a3 OBJ:51441 >> >> SCN:0x0000.c3f3c914 SEQ: 99 OP:11.5 >> >> KTB Redo >> >> op: 0x02 ver: 0x01 >> >> op: C uba: 0x00804ba1.0055.35 >> >> KDO Op code: URP row dependencies Disabled >> >> xtype: XR flags: 0x00000000 bdba: 0x018000a3 hdba: 0x0180008c >> >> itli: 1 ispac: 0 maxfr: 4858 >> >> tabn: 0 slot: 20(0x14) flag: 0x2c lock: 0 ckix: 0 >> >> ncol: 7 nnew: 1 size: 0 >> >> col 5: [28] >> >> 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 77 >> 77 >> >> 77 77 77 >> >> >> >> ================================================== >> >> detail commit; >> >> >> >> REDO RECORD - Thread:1 RBA: 0x000031.00000006.0070 LEN: 0x01e4 VLD: >> >> 0x01 >> >> SCN: 0x0000.c3e3e94a SUBSCN: 6 09/19/2008 18:00:53 >> >> CHANGE #1 TYP:0 CLS:32 AFN:2 DBA:0x00800231 OBJ:4294967295 >> >> SCN:0x0000.c3e3e94a SEQ: 3 OP:5.1 >> >> ktudb redo: siz: 88 spc: 3526 flg: 0x0022 seq: 0x0073 rec: 0x31 >> >> xid: 0x0008.00c.0000018a >> >> ktubu redo: slt: 12 rci: 48 opc: 11.1 objn: 9001 objd: 9001 tsn: 2 >> >> Undo type: Regular undo Undo type: Last buffer split: No >> >> Tablespace Undo: No >> >> 0x00000000 >> >> KDO undo record: >> >> KTB Redo >> >> op: 0x02 ver: 0x01 >> >> op: C uba: 0x00800231.0073.2f >> >> KDO Op code: DRP row dependencies Disabled >> >> xtype: XA flags: 0x00000000 bdba: 0x00c07e0d hdba: 0x00c00bcb >> >> itli: 1 ispac: 0 maxfr: 4858 >> >> tabn: 0 slot: 25(0x19) >> >> CHANGE #2 TYP:0 CLS: 1 AFN:3 DBA:0x00c07e0d OBJ:9001 >> >> SCN:0x0000.c3e3e94a SEQ: 1 OP:11.2 >> >> KTB Redo >> >> op: 0x02 ver: 0x01 >> >> op: C uba: 0x00800231.0073.31 >> >> KDO Op code: IRP row dependencies Disabled >> >> xtype: XA flags: 0x00000000 bdba: 0x00c07e0d hdba: 0x00c00bcb >> >> itli: 1 ispac: 0 maxfr: 4858 >> >> tabn: 0 slot: 25(0x19) size/delt: 153 >> >> fb: --H-FL-- lb: 0x1 cc: 37 >> >> null: >> >> >> >> >> >> >> >> 01234567890123456789012345678901234567890123456789012345678901234567890123456789 >> >> -------N-----N-------NNNNNN--NNNN--N- >> >> col 0: [ 2] c1 42 >> >> col 1: [ 6] c5 07 30 19 1d 3e >> >> col 2: [13] 33 6b 62 6b 67 76 74 70 70 64 6e 70 67 >> >> col 3: [ 6] c5 04 06 63 60 1f >> >> col 4: [ 2] c1 18 >> >> col 5: [12] 54 41 42 4c 45 20 41 43 43 45 53 53 >> >> col 6: [ 7] 43 4c 55 53 54 45 52 >> >> col 7: *NULL* >> >> col 8: [ 2] c1 17 >> >> col 9: [ 3] 53 59 53 >> >> col 10: [ 5] 55 53 45 52 24 >> >> col 11: [ 9] 49 54 55 40 53 45 4c 24 32 >> >> col 12: [ 7] 43 4c 55 53 54 45 52 >> >> col 13: *NULL* >> >> col 14: [ 2] c1 06 >> >> col 15: [ 2] c1 07 >> >> col 16: [ 2] c1 03 >> >> col 17: [ 1] 80 >> >> col 18: [ 2] c1 02 >> >> col 19: [ 2] c1 02 >> >> col 20: [ 2] c1 04 >> >> col 21: *NULL* >> >> col 22: *NULL* >> >> col 23: *NULL* >> >> col 24: *NULL* >> >> col 25: *NULL* >> >> col 26: *NULL* >> >> col 27: [ 3] c2 54 16 >> >> col 28: [ 2] c1 02 >> >> col 29: *NULL* >> >> col 30: *NULL* >> >> col 31: *NULL* >> >> col 32: *NULL* >> >> col 33: [ 2] c1 02 >> >> col 34: [12] 53 45 4c 24 46 35 42 42 37 34 45 31 >> >> col 35: *NULL* >> >> col 36: [ 7] 78 6c 09 13 12 1a 33 >> >> >> >> ================================================== >> >> >> >> kalo saya tangkap dari dump redolog tsb, even rollback akan flush ke >> >> redolog :) >> >> >> >> >> >> -- >> >> thanks and regards >> >> ujang | oracle dba >> >> jakarta | http://ora62.wordpress.com >> >> >> >> 2008/9/23 Yoel Susanto >> >> <[EMAIL PROTECTED]<yoel.susanto%40gmail.com><yoel.susanto% >> 40gmail.com> >> >> >: >> >> >> >> > Pak Ujang, >> >> > >> >> > 1. yup, kalo gak salah itu angkanya statistic, namun hal tersebut >> masih >> >> > berhubungan..... >> >> > redo log space requests itu hanya symptom, disini saya guessing saja >> >> > rootcause nya :) >> >> > >> >> > -- Yoel >> >> > Oohh gitu... >> >> > Maap saya salah tangkap kemana arah argument anda soalnya di akhir >> >> > nya >> >> anda >> >> > tulis >> >> > >> >> >>> penyebab utama redo log buffer space : >> >> >>> - ukuran log buffer yg terlalu kecil >> >> >>> - IO tempat redolog buffer terlalu lambat >> >> > >> >> > Jadi saya mengira anda menyalahkan log buffer-nya. >> >> > >> >> > 2. tidak mutlak berlaku, tergantung transaksinya, tapi kalau batch >> >> > mustinya terjadi :) >> >> > >> >> >> >> >> >> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1539606184994 >> >> > >> >> > -- Yoel >> >> > Statement yang bagian mana yang harus saya baca nih. >> >> > Saya tidak melihat atau menangkap statement yang berkata/berarti : >> >> > Rollback event/operation akan memflush redo data dari log buffer ke >> redo >> >> log >> >> > file >> >> > >> >> > 2008/9/23 Ujang Jaenudin >> >> > <[EMAIL PROTECTED] <ujang.jaenudin%40gmail.com> >> <ujang.jaenudin%40gmail.com> >> >> >> > >> >> > >> >> >> > 1. TS nya tanya tentang redo log space, bukan buffer space. >> >> >> > "redo log space requests" itu berhubungan dengan space di redo log >> >> FILE, >> >> >> > bukan buffer. >> >> >> > >> >> >> >> >> >> yup, kalo gak salah itu angkanya statistic, namun hal tersebut masih >> >> >> berhubungan..... >> >> >> redo log space requests itu hanya symptom, disini saya guessing saja >> >> >> rootcause nya :) >> >> >> >> >> >> > 2. User melakukan Rollback transaction tidak akan mem-flush redo >> >> buffer >> >> >> ke >> >> >> > redolog. >> >> >> >> >> >> tidak mutlak berlaku, tergantung transaksinya, tapi kalau batch >> >> >> mustinya terjadi :) >> >> >> >> >> >> >> >> >> >> >> >> >>
