"Undo 세미나"의 두 판 사이의 차이
DB CAFE
(→trace file 분석) |
(→테이블 UPDATE 시나리오 SQL) |
||
(같은 사용자의 중간 판 19개는 보이지 않습니다) | |||
288번째 줄: | 288번째 줄: | ||
=== undo 테이블스페이스 사이즈 확인 === | === undo 테이블스페이스 사이즈 확인 === | ||
+ | |||
+ | === 데이터 UPDATE 처리 과정 === | ||
+ | ==== 테이블 UPDATE 시나리오 SQL ==== | ||
+ | * 진행 순서 | ||
+ | # create table t1 (테이블 생성 후 2개 블럭에 데이터 입력.) | ||
+ | ## 인덱스 생성 | ||
+ | ## 통계 정보 생성 | ||
+ | # 블럭 번호 조회 | ||
+ | # 로그 스위치 | ||
+ | # 변경전 블록 덤프조회 | ||
+ | # 테이블 업데이트 id가 5~9 인건 | ||
+ | ## 데이터 변경 후 블럭 덤프 | ||
+ | ## 언두 블럭 덤프 | ||
+ | ## 롤백 / 커밋 수행 | ||
+ | # 리두 로그 덤프 수행 | ||
+ | |||
+ | |||
+ | <source lang=sql> | ||
+ | -- 1.create table t1 (2개 블럭에 생성 함.) | ||
+ | create table t1 | ||
+ | as | ||
+ | select | ||
+ | 2 * rownum - 1 id, | ||
+ | rownum n1, | ||
+ | cast('xxxxxx' as varchar2(10)) v1, | ||
+ | rpad('0',100,'0') padding | ||
+ | from | ||
+ | all_objects | ||
+ | where | ||
+ | rownum <= 60 | ||
+ | union all | ||
+ | select | ||
+ | 2 * rownum id, | ||
+ | rownum n1, | ||
+ | cast('xxxxxx' as varchar2(10)) v1, | ||
+ | rpad('0',100,'0') padding | ||
+ | from | ||
+ | all_objects | ||
+ | where | ||
+ | rownum <= 60 | ||
+ | ; | ||
+ | |||
+ | -- 1.1 인덱스 생성 | ||
+ | create index t1_i1 on t1(id); | ||
+ | |||
+ | -- 1.2 통계 정보 생성 | ||
+ | begin | ||
+ | dbms_stats.gather_table_stats( | ||
+ | ownname => user, | ||
+ | tabname =>'T1', | ||
+ | method_opt => 'for all columns size 1' | ||
+ | ); | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | -- 2. 블럭 번호 조회 | ||
+ | select | ||
+ | dbms_rowid.rowid_block_number(rowid) block_number, | ||
+ | count(*) rows_per_block | ||
+ | from | ||
+ | t1 | ||
+ | group by | ||
+ | dbms_rowid.rowid_block_number(rowid) | ||
+ | order by | ||
+ | block_number | ||
+ | ; | ||
+ | |||
+ | BLOCK_NUMBER ROWS_PER_BLOCK | ||
+ | ------------ -------------- | ||
+ | 443673 60 | ||
+ | 443674 60 | ||
+ | |||
+ | -- 3. 로그 스위치 | ||
+ | alter system switch logfile; | ||
+ | execute dbms_lock.sleep(2) | ||
+ | |||
+ | -- 4. 블럭 덤프 실행 (변경전 블록 덤프조회) | ||
+ | execute dump_seg('t1') | ||
+ | Dumped 1 blocks from TABLE t1 starting from block 1 | ||
+ | |||
+ | Trace file name includes: 10609 | ||
+ | |||
+ | -- 5. 테이블 업데이트 id가 5~9 인건 | ||
+ | update | ||
+ | /*+ index(t1 t1_i1) */ | ||
+ | t1 | ||
+ | set | ||
+ | v1 = 'YYYYYYYYYY' | ||
+ | where | ||
+ | id between 5 and 9 | ||
+ | ; | ||
+ | |||
+ | pause Query the IMU structures now (@core_imu_01.sql) | ||
+ | |||
+ | -- 5.1 데이터 변경 후 블럭 덤프 | ||
+ | execute dump_seg('t1') | ||
+ | |||
+ | -- 5.2 언두 블럭 덤프 | ||
+ | execute dump_undo_block | ||
+ | |||
+ | -- 5.3 롤백 / 커밋 수행 | ||
+ | rollback; | ||
+ | commit; | ||
+ | |||
+ | -- 6.리두 로그 덤프 수행 | ||
+ | |||
+ | execute dump_log | ||
+ | </source> | ||
+ | |||
+ | ==== 블럭 디버깅 ==== | ||
+ | |||
+ | <source lang=sql> | ||
+ | -- trace file 구분자 입력 | ||
+ | alter session set tracefile_identifier = 'DBCAFE'; | ||
+ | |||
+ | -- sql 실행 | ||
+ | SQL> @core_demo_02.sql | ||
+ | </source> | ||
+ | |||
+ | ===== trace file 분석 ===== | ||
+ | <source lang=sql> | ||
+ | --------------------------- | ||
+ | -- session info | ||
+ | *** SESSION ID:(395.49059) 2024-06-24T13:04:35.468088+00:00 | ||
+ | </source> | ||
+ | |||
+ | ===== Block dump from cache ===== | ||
+ | <source lang=sql> | ||
+ | Start dump data blocks tsn: 0 file#:1 minblk 443673 maxblk 443673 | ||
+ | Block dump from cache: | ||
+ | Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4637977 | ||
+ | BH (0xb1f596e8) file#: 1 rdba: 0x0046c519 (1/443673) class: 1 ba: 0xb1106000 | ||
+ | set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0 | ||
+ | dbwrid: 0 obj: 73789 objn: 73789 tsn: [1/0] afn: 1 hint: f | ||
+ | hash: [0x9ff8d278,0x752a2038] lru: [0xb9fbc740,0xaefe2990] | ||
+ | ckptq: [NULL] fileq: [NULL] | ||
+ | objq: [0x6ccce6b8,0x6ccce6b8] objaq: [0x6ccce6a8,0x6ccce6a8] | ||
+ | st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x28c8fa tch: 1 | ||
+ | flags: only_sequential_access | ||
+ | LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535] | ||
+ | Printing buffer operation history (latest change first): | ||
+ | cnt: 3 | ||
+ | 01. sid:11 L338:zibmlt:set:MSHR 02. sid:11 L144:zibmlt:mk:EXCL | ||
+ | 03. sid:11 L122:zgb:set:st 04. sid:11 L830:olq1:clr:WRT+CKT | ||
+ | 05. sid:11 L951:zgb:lnk:objq 06. sid:11 L372:zgb:set:MEXCL | ||
+ | 07. sid:11 L123:zgb:no:FEN 08. sid:11 L083:zgb:ent:fn | ||
+ | 09. sid:11 L154:z_sw_cur:bic:FPB 10. sid:11 L940:z_sw_cur:sw:cq | ||
+ | 11. sid:11 L070:zswcu:ent:ob 12. sid:11 L471:bpostapl:bic:FMS | ||
+ | 13. sid:11 L786:pre_apl:bis:FMS 14. sid:11 L353:gcur:set:MEXCL | ||
+ | 15. sid:11 L471:bpostapl:bic:FMS 16. sid:11 L786:pre_apl:bis:FMS | ||
+ | buffer tsn: 0 rdba: 0x0046c519 (1/443673) | ||
+ | scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602 | ||
+ | frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data | ||
+ | Hex dump of block: st=0, typ_found=1 | ||
+ | Dump of memory from 0x00000000B1106000 to 0x00000000B1108000 | ||
+ | |||
+ | ..... 생략 ...... | ||
+ | </source> | ||
+ | |||
+ | ===== Block header dump ===== | ||
+ | <source lang=sql> | ||
+ | Block header dump: 0x0046c519 | ||
+ | Object id on Block? Y | ||
+ | seg/obj: 0x1203d csc: 0x000000000028c8f1 itc: 3 flg: - typ: 1 - DATA | ||
+ | fsl: 0 fnx: 0x0 ver: 0x01 | ||
+ | |||
+ | Itl Xid Uba Flag Lck Scn/Fsc | ||
+ | 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000028c8f1 | ||
+ | 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 | ||
+ | 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 | ||
+ | bdba: 0x0046c519 | ||
+ | data_block_dump,data header at 0xb1106074 | ||
+ | =============== | ||
+ | tsiz: 0x1f88 | ||
+ | hsiz: 0x8a | ||
+ | pbl: 0xb1106074 | ||
+ | 76543210 | ||
+ | flag=-------- | ||
+ | ntab=1 | ||
+ | nrow=60 | ||
+ | frre=-1 | ||
+ | fsbo=0x8a | ||
+ | fseo=0x412 | ||
+ | avsp=0x388 | ||
+ | tosp=0x388 | ||
+ | 0xe:pti[0] nrow=60 offs=0 | ||
+ | .... 생략 .... | ||
+ | block_row_dump: | ||
+ | tab 0, row 0, @0x1f13 | ||
+ | tl: 117 fb: --H-FL-- lb: 0x0 cc: 4 | ||
+ | col 0: [ 2] c1 02 | ||
+ | col 1: [ 2] c1 02 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | col 3: [100] | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | tab 0, row 1, @0x1e9e | ||
+ | tl: 117 fb: --H-FL-- lb: 0x0 cc: 4 | ||
+ | col 0: [ 2] c1 04 | ||
+ | col 1: [ 2] c1 03 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | col 3: [100] | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 | ||
+ | ... 생략 ... | ||
+ | end_of_block_dump | ||
+ | Block dump from disk: | ||
+ | buffer tsn: 0 rdba: 0x0046c519 (1/443673) | ||
+ | scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602 | ||
+ | frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data | ||
+ | Hex dump of block: st=0, typ_found=1 | ||
+ | ... 생략 ... | ||
+ | Block header dump: 0x0046c519 | ||
+ | Object id on Block? Y | ||
+ | seg/obj: 0x1203d csc: 0x000000000028c8f1 itc: 3 flg: - typ: 1 - DATA | ||
+ | fsl: 0 fnx: 0x0 ver: 0x01 | ||
+ | |||
+ | Itl Xid Uba Flag Lck Scn/Fsc | ||
+ | 0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000028c8f1 | ||
+ | 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 | ||
+ | 0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000 | ||
+ | bdba: 0x0046c519 | ||
+ | data_block_dump,data header at 0x7ff0e0927074 | ||
+ | =============== | ||
+ | tsiz: 0x1f88 | ||
+ | hsiz: 0x8a | ||
+ | pbl: 0x7ff0e0927074 | ||
+ | 76543210 | ||
+ | flag=-------- | ||
+ | ntab=1 | ||
+ | nrow=60 | ||
+ | frre=-1 | ||
+ | fsbo=0x8a | ||
+ | fseo=0x412 | ||
+ | avsp=0x388 | ||
+ | tosp=0x388 | ||
+ | </source> | ||
+ | |||
+ | |||
==== undo 블럭 디버깅 ==== | ==== undo 블럭 디버깅 ==== | ||
+ | ===== Undo Segment Header ===== | ||
+ | <source lang=sql> | ||
+ | =================== | ||
+ | Undo Segment Header | ||
+ | =================== | ||
+ | Start dump data blocks tsn: 2 file#:4 minblk 160 maxblk 160 | ||
+ | Block dump from cache: | ||
+ | Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777376 | ||
+ | BH (0xbaf9f378) file#: 4 rdba: 0x010000a0 (4/160) class: 21 ba: 0xba73a000 | ||
+ | set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0 | ||
+ | dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f | ||
+ | hash: [0x76ec93d0,0x76ec93d0] lru: [0xbafa1a58,0xbaf9f2f8] | ||
+ | lru-flags: hot_buffer | ||
+ | obj-flags: object_ckpt_list | ||
+ | ckptq: [0x89f76428,0x8ef9c3a8] fileq: [0x89f76438,0x8ef9c3b8] | ||
+ | objq: [0x8ef9c4b8,0x89f76538] objaq: [0x6c4d6578,0xbaf9f1c8] | ||
+ | use: [NULL] wait: [NULL] | ||
+ | st: XCURRENT md: NULL fpin: 'ktuwh72: ktugus:ktuswr1' fscn: 0x23d008 tch: 42 txn: 0x747dabf0 | ||
+ | flags: buffer_dirty private block_written_once | ||
+ | LRBA: [0x6f.1e8e.0] LSCN: [0x28c8e7] HSCN: [0x28c914] HSUB: [1] | ||
+ | Printing buffer operation history (latest change first): | ||
+ | cnt: 10 | ||
+ | 01. sid:11 L786:pre_apl:bis:FMS 02. sid:11 L803:pre_apl:bis:FPB | ||
+ | 03. sid:11 L353:gcur:set:MEXCL 04. sid:11 L464:chg1_mn:bic:FMS | ||
+ | 05. sid:11 L778:chg1_mn:bis:FMS 06. sid:11 L353:gcur:set:MEXCL | ||
+ | 07. sid:09 L464:chg1_mn:bic:FMS 08. sid:09 L778:chg1_mn:bis:FMS | ||
+ | 09. sid:09 L353:gcur:set:MEXCL 10. sid:09 L464:chg1_mn:bic:FMS | ||
+ | 11. sid:09 L778:chg1_mn:bis:FMS 12. sid:09 L353:gcur:set:MEXCL | ||
+ | 13. sid:11 L464:chg1_mn:bic:FMS 14. sid:11 L778:chg1_mn:bis:FMS | ||
+ | 15. sid:11 L353:gcur:set:MEXCL 16. sid:09 L464:chg1_mn:bic:FMS | ||
+ | buffer tsn: 2 rdba: 0x010000a0 (4/160) | ||
+ | scn: 0x28c914 seq: 0x01 flg: 0x00 tail: 0xc9142601 | ||
+ | frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK | ||
+ | Hex dump of block: st=0, typ_found=1 | ||
+ | Dump of memory from 0x00000000BA73A000 to 0x00000000BA73C000 | ||
+ | </source> | ||
+ | |||
+ | ===== Undo Start block ===== | ||
+ | <source lang=sql> | ||
+ | ================ | ||
+ | Undo Start block | ||
+ | ================ | ||
+ | Start dump data blocks tsn: 2 file#:4 minblk 542 maxblk 542 | ||
+ | Block dump from cache: | ||
+ | Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777758 | ||
+ | BH (0x9cfc4d58) file#: 4 rdba: 0x0100021e (4/542) class: 22 ba: 0x9ca92000 | ||
+ | set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0 | ||
+ | dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f | ||
+ | hash: [0x8efadd98,0x76f2cf30] lru: [0x88fcc460,0x9af633e8] | ||
+ | obj-flags: object_ckpt_list | ||
+ | ckptq: [0xb1f8e140,0xbafca320] fileq: [0x75484058,0xbafca330] | ||
+ | objq: [0xbafca430,0x6c4c42f8] objaq: [0xa6fb41e0,0xb0fb63a0] | ||
+ | use: [NULL] wait: [NULL] | ||
+ | st: XCURRENT md: NULL fpin: 'ktuwh03: ktugnb' fscn: 0x2852b3 tch: 2 txn: 0x747dabf0 | ||
+ | flags: buffer_dirty private block_written_once | ||
+ | LRBA: [0x6f.1ee9.0] LSCN: [0x28c8fa] HSCN: [0x28c913] HSUB: [5] | ||
+ | Printing buffer operation history (latest change first): | ||
+ | cnt: 8 | ||
+ | 01. sid:11 L802:gcur:bis:FPB 02. sid:11 L353:gcur:set:MEXCL | ||
+ | 03. sid:09 L464:chg1_mn:bic:FMS 04. sid:09 L778:chg1_mn:bis:FMS | ||
+ | 05. sid:09 L362:chg1:set:MEXCL 06. sid:09 L464:chg1_mn:bic:FMS | ||
+ | 07. sid:09 L778:chg1_mn:bis:FMS 08. sid:09 L362:chg1:set:MEXCL | ||
+ | 09. sid:09 L464:chg1_mn:bic:FMS 10. sid:09 L778:chg1_mn:bis:FMS | ||
+ | 11. sid:09 L362:chg1:set:MEXCL 12. sid:09 L464:chg1_mn:bic:FMS | ||
+ | 13. sid:09 L778:chg1_mn:bis:FMS 14. sid:09 L362:chg1:set:MEXCL | ||
+ | 15. sid:09 L464:chg1_mn:bic:FMS 16. sid:09 L778:chg1_mn:bis:FMS | ||
+ | buffer tsn: 2 rdba: 0x0100021e (4/542) | ||
+ | scn: 0x28c913 seq: 0x05 flg: 0x00 tail: 0xc9130205 | ||
+ | frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK | ||
+ | Hex dump of block: st=0, typ_found=1 | ||
+ | Dump of memory from 0x000000009CA92000 to 0x000000009CA94000 | ||
+ | </source> | ||
+ | |||
+ | |||
+ | ===== UNDO BLK ===== | ||
+ | <source lang=sql> | ||
+ | ******************************************************************************** | ||
+ | UNDO BLK: | ||
+ | xid: 0x0003.019.00000475 seq: 0x104 cnt: 0x8 irb: 0x8 icl: 0x0 flg: 0x0000 | ||
+ | |||
+ | Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset | ||
+ | --------------------------------------------------------------------------- | ||
+ | 0x01 0x1f80 0x02 0x1f2c 0x03 0x1ea4 0x04 0x1e1c 0x05 0x1db0 | ||
+ | 0x06 0x1d44 0x07 0x1cdc 0x08 0x1c98 | ||
+ | |||
+ | *----------------------------- | ||
+ | * Rec #0x1 slt: 0x1d objn: 1(0x00000001) objd: 4294967295 tblspc: 0(0x00000000) | ||
+ | * Layer: 22 (Tablespace Bitmapped file) opc: 3 rci 0x00 | ||
+ | Undo type: Regular undo Begin trans Last buffer split: No | ||
+ | Temp Object: No | ||
+ | Tablespace Undo: Yes | ||
+ | rdba: 0x00000000Ext idx: 0 | ||
+ | flg2: 0 | ||
+ | *----------------------------- | ||
+ | uba: 0x0100021d.0104.38 ctl max scn: 0x000000000028c27a prv tx scn: 0x000000000028c295 | ||
+ | txn start scn: scn: 0x000000000028c8fa logon user: 0 | ||
+ | prev brb: 16777751 prev bcl: 0 | ||
+ | ktfbhundo - File Space Header Undo: | ||
+ | Space Header DBA:0x400002, File:0x1 | ||
+ | Header Opcode: | ||
+ | Save: Free Extent: | ||
+ | Begin: 443680, Length: 8, Instance: 0 | ||
+ | ..... 생략 ..... | ||
+ | *----------------------------- | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.07 | ||
+ | KDO Op code: LKR row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x00406991 hdba: 0x00400090 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 3 slot: 0 to: 0 | ||
+ | |||
+ | Block dump from disk: | ||
+ | buffer tsn: 2 rdba: 0x0100021e (4/542) | ||
+ | scn: 0x2852b3 seq: 0x1b flg: 0x04 tail: 0x52b3021b | ||
+ | frmt: 0x02 chkval: 0x4b75 type: 0x02=KTU UNDO BLOCK | ||
+ | Hex dump of block: st=0, typ_found=1 | ||
+ | Dump of memory from 0x00007FF0E0927000 to 0x00007FF0E0929000 | ||
+ | |||
+ | ... 생략 ... | ||
+ | |||
+ | ******************************************************************************** | ||
+ | UNDO BLK: | ||
+ | xid: 0x0003.008.00000462 seq: 0x100 cnt: 0x1a irb: 0x1a icl: 0x0 flg: 0x0000 | ||
+ | |||
+ | Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset | ||
+ | --------------------------------------------------------------------------- | ||
+ | 0x01 0x1f10 0x02 0x1e38 0x03 0x1d90 0x04 0x1d1c 0x05 0x1cac | ||
+ | 0x06 0x1c3c 0x07 0x1bc8 0x08 0x1af0 0x09 0x1a48 0x0a 0x1970 | ||
+ | 0x0b 0x18c8 0x0c 0x17f0 0x0d 0x1718 0x0e 0x1640 0x0f 0x1598 | ||
+ | 0x10 0x14f0 0x11 0x1418 0x12 0x1370 0x13 0x1298 0x14 0x11f0 | ||
+ | 0x15 0x1118 0x16 0x1040 0x17 0x0f68 0x18 0x0ec0 0x19 0x0dcc | ||
+ | 0x1a 0x0d84 | ||
+ | |||
+ | *----------------------------- | ||
+ | * Rec #0x1 slt: 0x08 objn: 73609(0x00011f89) objd: 73609 tblspc: 1(0x00000001) | ||
+ | * Layer: 10 (Index) opc: 22 rci 0x00 | ||
+ | Undo type: Regular undo Last buffer split: No | ||
+ | Temp Object: No | ||
+ | Tablespace Undo: No | ||
+ | rdba: 0x0100021d | ||
+ | *----------------------------- | ||
+ | index undo for leaf key operations | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021d.0100.21 | ||
+ | Dump kdilk : itl=2, kdxlkflg=0x21 sdc=32767 indexid=0xc14832 block=0x00c159f0 | ||
+ | (kdxlpu): purge leaf row | ||
+ | number of keys: 3 | ||
+ | key sizes: | ||
+ | 42 42 42 | ||
+ | key :(126): | ||
+ | 06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03 03 c2 16 0a 07 78 7c 06 17 | ||
+ | 11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00 65 06 c5 1c 2b 06 19 43 02 | ||
+ | c1 5e 02 c1 02 02 c1 03 03 c2 16 16 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06 | ||
+ | 19 43 06 00 c1 59 ff 00 66 06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03 | ||
+ | 03 c2 16 2d 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00 | ||
+ | 67 | ||
+ | keydata/bitmap: (1): ff | ||
+ | |||
+ | </source> | ||
+ | |||
+ | ==== redo 블럭 디버깅 ==== | ||
+ | |||
+ | <source lang=sql> | ||
+ | DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/MONGO/redo01.log' | ||
+ | Container ID: 0 | ||
+ | Container UID: 0 | ||
+ | Opcodes *.* | ||
+ | Container ID: 0 | ||
+ | Container UID: 0 | ||
+ | RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff | ||
+ | SCNs: scn: 0x0000000000000000 thru scn: 0xffffffffffffffff | ||
+ | Times: creation thru eternity | ||
+ | FILE HEADER: | ||
+ | Compatibility Vsn = 318767104=0x13000000 | ||
+ | Db ID=2742052466=0xa3706672, Db Name='MONGO' | ||
+ | Activation ID=2742030962=0xa3701272 | ||
+ | Control Seq=8990=0x231e, File size=409600=0x64000 | ||
+ | File Number=1, Blksiz=512, File Type=2 LOG | ||
+ | descrip:"T 0001, S 0000000112, SCN 0x000000000028c925-0xffffffffffffffff" | ||
+ | thread: 1 nab: 0xffffffff seq: 0x00000070 hws: 0x1 eot: 1 dis: 0 | ||
+ | resetlogs count: 0x45d061b5 scn: 0x00000000001d4fd1 | ||
+ | prev resetlogs count: 0x3bf3129f scn: 0x0000000000000001 | ||
+ | Low scn: 0x000000000028c925 06/24/2024 13:04:33 | ||
+ | Next scn: 0xffffffffffffffff 01/01/1988 00:00:00 | ||
+ | Enabled scn: 0x00000000001d4fd1 06/10/2024 12:46:45 | ||
+ | Thread closed scn: 0x000000000028c925 06/24/2024 13:04:33 | ||
+ | Real next scn: 0xffffffffffffffff | ||
+ | Disk cksum: 0x5e1b Calc cksum: 0x5e1b | ||
+ | Terminal recovery stop scn: 0x0000000000000000 | ||
+ | Terminal recovery 01/01/1988 00:00:00 | ||
+ | Most recent redo scn: 0x0000000000000000 | ||
+ | Largest LWN: 0 blocks | ||
+ | End-of-redo stream : No | ||
+ | Unprotected mode | ||
+ | Miscellaneous flags: 0x800000 | ||
+ | Miscellaneous second flags: 0x0 | ||
+ | Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000000000000000 | ||
+ | Zero blocks: 0 | ||
+ | Format ID is 18 | ||
+ | redo log key is 675ff3d7a77b9fb48595ec24a9804d3f | ||
+ | redo log key flag is 15 | ||
+ | High watermark block number: 0 | ||
+ | Enabled redo threads: 1 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000002.0010 LEN: 0x05d8 VLD: 0x0d CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 1 06/24/2024 13:04:45 | ||
+ | (LWN RBA: 0x000070.00000002.0010 LEN: 0x00000006 NST: 0x0001 SCN: 0x000000000028c928) | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x01 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.09 | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: 4 | ||
+ | col 2: [10] 59 59 59 59 59 59 59 59 59 59 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c914 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudh redo: slt: 0x0021 sqn: 0x00000474 flg: 0x0052 siz: 140 fbi: 0 | ||
+ | uba: 0x0100021e.0104.09 pxid: 0x0000.000.00000000 pdbid:1 | ||
+ | CHANGE #3 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x01 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.0a | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: 4 | ||
+ | col 2: [10] 59 59 59 59 59 59 59 59 59 59 | ||
+ | CHANGE #4 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0b | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: 4 | ||
+ | col 2: [10] 59 59 59 59 59 59 59 59 59 59 | ||
+ | CHANGE #5 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0c | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: 4 | ||
+ | col 2: [10] 59 59 59 59 59 59 59 59 59 59 | ||
+ | CHANGE #6 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0d | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: 4 | ||
+ | col 2: [10] 59 59 59 59 59 59 59 59 59 59 | ||
+ | CHANGE #7 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c913 SEQ:5 OP:5.1 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudb redo: siz: 140 spc: 7286 flg: 0x0012 seq: 0x0104 rec: 0x09 | ||
+ | xid: 0x0003.021.00000474 | ||
+ | ktubl redo: slt: 33 wrp: 1 flg: 0x0c08 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] No [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | Begin trans | ||
+ | prev ctl uba: 0x0100021e.0104.04 prev ctl max cmt scn: 0x000000000028c2b9 | ||
+ | prev tx cmt scn: 0x000000000028c2cf | ||
+ | txn start scn: 0x000000000028c920 logon user: 0 | ||
+ | prev brb: 0x01000217 prev bcl: 0x00000000 | ||
+ | BuExt idx: 0 flg2: 0 | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x03 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: Z | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #8 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudb redo: siz: 88 spc: 7144 flg: 0x0022 seq: 0x0104 rec: 0x0a | ||
+ | xid: 0x0003.021.00000474 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] No [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x03 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: Z | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #9 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudb redo: siz: 96 spc: 7054 flg: 0x0022 seq: 0x0104 rec: 0x0b | ||
+ | xid: 0x0003.021.00000474 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] No [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.09 | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #10 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:3 OP:5.1 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudb redo: siz: 96 spc: 6956 flg: 0x0022 seq: 0x0104 rec: 0x0c | ||
+ | xid: 0x0003.021.00000474 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] No [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0a | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #11 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:4 OP:5.1 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktudb redo: siz: 96 spc: 6858 flg: 0x0022 seq: 0x0104 rec: 0x0d | ||
+ | xid: 0x0003.021.00000474 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] No [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | KDO undo record: | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0b | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000005.0018 LEN: 0x00cc VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 2 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0b | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:5 OP:5.6 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | ktuxvoff: 0x1a94 ktuxvflg: 0x0002 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000005.00e4 LEN: 0x00cc VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 3 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.0a | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:6 OP:5.6 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | ktuxvoff: 0x1af4 ktuxvflg: 0x0002 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000005.01b0 LEN: 0x00cc VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 4 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:4 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x02 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: C uba: 0x0100021e.0104.09 | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:7 OP:5.6 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | ktuxvoff: 0x1b54 ktuxvflg: 0x0002 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000006.008c LEN: 0x00c4 VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x03 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: Z | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:8 OP:5.6 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | ktuxvoff: 0x1bb4 ktuxvflg: 0x0002 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000006.0150 LEN: 0x00c4 VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:5 OP:11.5 ENC:0 RBL:0 FLG:0x0000 | ||
+ | KTB Redo | ||
+ | op: 0x03 ver: 0x01 | ||
+ | compat bit: 4 (post-11) padding: 1 | ||
+ | op: Z | ||
+ | KDO Op code: URP row dependencies Disabled | ||
+ | xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518 | ||
+ | itli: 2 ispac: 0 maxfr: 4863 | ||
+ | tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18 | ||
+ | ncol: 4 nnew: 1 size: -4 | ||
+ | col 2: [ 6] 78 78 78 78 78 78 | ||
+ | CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.11 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktubl redo: slt: 33 wrp: 1 flg: 0x0c18 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0] | ||
+ | [Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No | ||
+ | [Temp object] No [Tablespace Undo ] No [User only ] No | ||
+ | Begin trans | ||
+ | BuExt idx: 0 flg2: 0 | ||
+ | |||
+ | REDO RECORD - Thread:1 RBA: 0x000070.00000007.0024 LEN: 0x0058 VLD: 0x01 CON_UID: 1 | ||
+ | SCN: 0x000000000028c929 SUBSCN: 1 06/24/2024 13:04:45 | ||
+ | CHANGE #1 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.4 ENC:0 RBL:0 FLG:0x0000 | ||
+ | ktucm redo: slt: 0x0021 sqn: 0x00000474 srt: 0 sta: 9 flg: 0x4 | ||
+ | rolled back transaction | ||
+ | END OF REDO DUMP | ||
+ | ==== Redo read statistics for thread 1 ==== | ||
+ | Total physical reads (from disk and memory): 1023Kb | ||
+ | -- Redo read_disk statistics -- | ||
+ | Read rate (SYNC): 3Kb in 0.01s => 0.29 Mb/sec | ||
+ | Total redo bytes: 1023Kb Longest record: 1Kb, moves: 0/7 moved: 0Mb (0%) | ||
+ | Longest LWN: 3Kb, reads: 1 | ||
+ | Last redo scn: 0x000000000028c929 (2672937) | ||
+ | Change vector header moves = 1/22 (4%) | ||
+ | </source> | ||
+ | ---- | ||
=== redo 테이블스페이스 사이즈 확인 === | === redo 테이블스페이스 사이즈 확인 === | ||
− | |||
=== 테이블스페이스 사이즈 확인 === | === 테이블스페이스 사이즈 확인 === | ||
511번째 줄: | 1,227번째 줄: | ||
from dbcafe.TB_T1 where rownum = 1; | from dbcafe.TB_T1 where rownum = 1; | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
</source> | </source> |
2024년 6월 26일 (수) 13:27 판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 언두 테스트[편집]
SELECT SUBSTR(SEGMENT_NAME, 1, 20) TB , BYTES/1024/1024 "MB", BLOCKS
FROM USER_SEGMENTS
WHERE SEGMENT_NAME IN ( 'TB_BIG','TB_BIG2');
-- SHARED_POOL/BUFFER_POOL/FLASH_CACHE/BUFFER_CACHE/GLOBAL CONTEXT/PASSWORDFILE_METADATA_CACHE 키워드
alter system flush FLASH_CACHE;
alter system flush BUFFER_CACHE;
alter table tb_big rename to tb_big2;
drop table tb_big2 purge;
alter table tb_big rename to tb_big2;
SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
FROM (SELECT TABLESPACE_NAME, STATUS,
ROUND(SUM(BYTES/1024/1024)) USE_MB
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME,STATUS
)A,
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME LIKE 'UNDO%'
GROUP BY TABLESPACE_NAME
)B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY 1,2
;
SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
, SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
, TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
, TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
, TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
, TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
, TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
, TO_CHAR(XACTS, '9,999') AS XACTS
FROM DBA_ROLLBACK_SEGS A
, V$ROLLSTAT B
WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;
SELECT group#
, status
, member
FROM v$logfile;
select * from V$ARCHIVED_LOG;
select * from v$parameter where name like '%log%';
alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;
drop table tb_big3 purge;
create table tb_big3
as
select cola,colb,22 as colc,cold,cole,colf,colg,colh,coli
from tb_big;
select count(*) from tb_big;
alter system switch logfile;
-- 테이블 생성
CREATE TABLE TB_BIG(
COLA VARCHAR2(20), COLB NUMBER , COLC NUMBER,
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30),
COLG NUMBER , COLH VARCHAR2(30), COLI VARCHAR2(30)
);
-- DATA 입력
DECLARE
TYPE tbl_ins IS TABLE OF TB_BIG%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..8960000 LOOP
w_ins(i).COLA :=i;
w_ins(i).COLB :=300000;
w_ins(i).COLC :=99; -- <== 변경 테스트 대상 컬럼 99를 11로 변경
w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
w_ins(i).COLG :=9999999;
w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
FORALL i in 1..8960000 INSERT INTO TB_BIG VALUES w_ins(i);
COMMIT;
END;
/
-- 8,960,000 건
TB MB BLOCKS
-------------------- ---------- ----------
TB_BIG 1216 155648
1.1 UPDATE 테스트[편집]
sh-4.2$ cat update_tb_big.sql
update tb_big set colc=11;
commit;
====== 시작 ======
15:46:35 SQL> @update_tb_big.sql
8960000 rows updated.
Commit complete.
15:54:14 SQL>
====== 종료 ====== 총 7분 40초 , 460초
1.2 CTAS 테스트[편집]
sh-4.2$ cat ctas_tb_big.sql
-- rename old
alter table tb_big rename to tb_big_old;
-- ctas
create table tb_big
as
select cola,colb,22 as colc
, cold,cole,colf,colg,colh,coli
from tb_big_old;
====== 시작 ======
15:58:56 SQL> @ctas_tb_big.sql
Table created.
15:59:22 SQL>
====== 종료 ======= 총 26초
2 archive log mode 인경우 테스트[편집]
16:28:03 SQL> @update_tb_big.sql
8960000 rows updated.
Commit complete.
16:35:46 SQL>
====== 종료 ====== 총 7분 43초 , 463초
2.1 UPDATE 테스트[편집]
- 아카이빙 로그 사이즈
- 로그 테스트 전
bash-4.2# du -h
16K .
- 로그 테스트 후
-rw-r----- 1 oracle oinstall 196845056 Jun 16 16:28 arch1_37_1171284405.dbf
-rw-r----- 1 oracle oinstall 195198976 Jun 16 16:29 arch1_38_1171284405.dbf
-rw-r----- 1 oracle oinstall 194925056 Jun 16 16:29 arch1_39_1171284405.dbf
-rw-r----- 1 oracle oinstall 195596288 Jun 16 16:30 arch1_40_1171284405.dbf
-rw-r----- 1 oracle oinstall 195552256 Jun 16 16:30 arch1_41_1171284405.dbf
-rw-r----- 1 oracle oinstall 195438592 Jun 16 16:31 arch1_42_1171284405.dbf
-rw-r----- 1 oracle oinstall 189432320 Jun 16 16:31 arch1_43_1171284405.dbf
-rw-r----- 1 oracle oinstall 183622656 Jun 16 16:32 arch1_44_1171284405.dbf
-rw-r----- 1 oracle oinstall 190867456 Jun 16 16:32 arch1_45_1171284405.dbf
-rw-r----- 1 oracle oinstall 190482432 Jun 16 16:33 arch1_46_1171284405.dbf
-rw-r----- 1 oracle oinstall 190621696 Jun 16 16:33 arch1_47_1171284405.dbf
-rw-r----- 1 oracle oinstall 191492608 Jun 16 16:34 arch1_48_1171284405.dbf
-rw-r----- 1 oracle oinstall 185030144 Jun 16 16:34 arch1_49_1171284405.dbf
-rw-r----- 1 oracle oinstall 188132864 Jun 16 16:35 arch1_50_1171284405.dbf
drwxr-xr-x 1 oracle dba 4096 Jun 16 16:35 .
-rw-r----- 1 oracle oinstall 179443200 Jun 16 16:35 arch1_51_1171284405.dbf
bash-4.2# date
Sun Jun 16 16:41:50 UTC 2024
bash-4.2# du -k
2795632 .
bash-4.2# du -h
2.7G .
2.2 CTAS 테스트[편집]
bash-4.2# ls -altr
total 1358964
-rw-r--r-- 1 oracle dba 3079 May 14 2015 init.ora
drwxr-xr-x 1 oracle dba 4096 Jun 10 12:44 ..
-rw-r----- 1 oracle oinstall 24 Jun 10 12:44 lkMONGO
lrwxrwxrwx 1 oracle oinstall 54 Jun 10 13:07 spfileMONGO.ora -> /u01/app/oracle/oradata/dbconfig/MONGO/spfileMONGO.ora
lrwxrwxrwx 1 oracle oinstall 49 Jun 10 13:07 orapwMONGO -> /u01/app/oracle/oradata/dbconfig/MONGO/orapwMONGO
-rw-rw---- 1 oracle oinstall 1544 Jun 16 16:27 hc_MONGO.dat
-rw-r----- 1 oracle oinstall 198791168 Jun 16 16:49 arch1_52_1171284405.dbf
-rw-r----- 1 oracle oinstall 198788096 Jun 16 16:49 arch1_53_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_54_1171284405.dbf
-rw-r----- 1 oracle oinstall 198791680 Jun 16 16:49 arch1_55_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_56_1171284405.dbf
-rw-r----- 1 oracle oinstall 198794240 Jun 16 16:49 arch1_57_1171284405.dbf
drwxr-xr-x 1 oracle dba 4096 Jun 16 16:49 .
-rw-r----- 1 oracle oinstall 198789120 Jun 16 16:49 arch1_58_1171284405.dbf
bash-4.2# du -k ./
1358956 ./
bash-4.2# du -h ./
1.3G ./
3 CTAS NOLOGGING MODE[편집]
3.1 ctas nologging mode[편집]
bash-4.2# du -h
16K .
- 00:19.21 소요
-- UNRECOVERABLE 옵션 사용
SQL> @ctas_tb_big_nolog.sql
Table altered.
Elapsed: 00:00:00.03
Table created.
Elapsed: 00:00:19.21
3.2 ctas nolooging 처리후[편집]
bash-4.2# du -h
1.2G .
==> nolooging 인데도 사이즈가 크게 줄지 않음......
--- 테이블 drop 시
drop table tb_big2 purge;
bash-4.2# du -h
1.3G .
3.3 insert ~ select nologging[편집]
- 01:19 초 소요
SQL> @ins_sel_nologging.sql
Table altered.
Elapsed: 00:00:00.07
Table created.
Elapsed: 00:00:00.10
Table altered.
Elapsed: 00:00:00.04
8960000 rows created.
Elapsed: 00:01:19.94
bash-4.2# du -h
1.2G .
bash-4.2#
4 undo 테이블스페이스 사이즈 확인[편집]
5 데이터 UPDATE 처리 과정[편집]
5.1 테이블 UPDATE 시나리오 SQL[편집]
- 진행 순서
- create table t1 (테이블 생성 후 2개 블럭에 데이터 입력.)
- 인덱스 생성
- 통계 정보 생성
- 블럭 번호 조회
- 로그 스위치
- 변경전 블록 덤프조회
- 테이블 업데이트 id가 5~9 인건
- 데이터 변경 후 블럭 덤프
- 언두 블럭 덤프
- 롤백 / 커밋 수행
- 리두 로그 덤프 수행
-- 1.create table t1 (2개 블럭에 생성 함.)
create table t1
as
select
2 * rownum - 1 id,
rownum n1,
cast('xxxxxx' as varchar2(10)) v1,
rpad('0',100,'0') padding
from
all_objects
where
rownum <= 60
union all
select
2 * rownum id,
rownum n1,
cast('xxxxxx' as varchar2(10)) v1,
rpad('0',100,'0') padding
from
all_objects
where
rownum <= 60
;
-- 1.1 인덱스 생성
create index t1_i1 on t1(id);
-- 1.2 통계 정보 생성
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
method_opt => 'for all columns size 1'
);
end;
/
-- 2. 블럭 번호 조회
select
dbms_rowid.rowid_block_number(rowid) block_number,
count(*) rows_per_block
from
t1
group by
dbms_rowid.rowid_block_number(rowid)
order by
block_number
;
BLOCK_NUMBER ROWS_PER_BLOCK
------------ --------------
443673 60
443674 60
-- 3. 로그 스위치
alter system switch logfile;
execute dbms_lock.sleep(2)
-- 4. 블럭 덤프 실행 (변경전 블록 덤프조회)
execute dump_seg('t1')
Dumped 1 blocks from TABLE t1 starting from block 1
Trace file name includes: 10609
-- 5. 테이블 업데이트 id가 5~9 인건
update
/*+ index(t1 t1_i1) */
t1
set
v1 = 'YYYYYYYYYY'
where
id between 5 and 9
;
pause Query the IMU structures now (@core_imu_01.sql)
-- 5.1 데이터 변경 후 블럭 덤프
execute dump_seg('t1')
-- 5.2 언두 블럭 덤프
execute dump_undo_block
-- 5.3 롤백 / 커밋 수행
rollback;
commit;
-- 6.리두 로그 덤프 수행
execute dump_log
5.2 블럭 디버깅[편집]
-- trace file 구분자 입력
alter session set tracefile_identifier = 'DBCAFE';
-- sql 실행
SQL> @core_demo_02.sql
5.2.1 trace file 분석[편집]
---------------------------
-- session info
*** SESSION ID:(395.49059) 2024-06-24T13:04:35.468088+00:00
5.2.2 Block dump from cache[편집]
Start dump data blocks tsn: 0 file#:1 minblk 443673 maxblk 443673
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4637977
BH (0xb1f596e8) file#: 1 rdba: 0x0046c519 (1/443673) class: 1 ba: 0xb1106000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73789 objn: 73789 tsn: [1/0] afn: 1 hint: f
hash: [0x9ff8d278,0x752a2038] lru: [0xb9fbc740,0xaefe2990]
ckptq: [NULL] fileq: [NULL]
objq: [0x6ccce6b8,0x6ccce6b8] objaq: [0x6ccce6a8,0x6ccce6a8]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x28c8fa tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:11 L338:zibmlt:set:MSHR 02. sid:11 L144:zibmlt:mk:EXCL
03. sid:11 L122:zgb:set:st 04. sid:11 L830:olq1:clr:WRT+CKT
05. sid:11 L951:zgb:lnk:objq 06. sid:11 L372:zgb:set:MEXCL
07. sid:11 L123:zgb:no:FEN 08. sid:11 L083:zgb:ent:fn
09. sid:11 L154:z_sw_cur:bic:FPB 10. sid:11 L940:z_sw_cur:sw:cq
11. sid:11 L070:zswcu:ent:ob 12. sid:11 L471:bpostapl:bic:FMS
13. sid:11 L786:pre_apl:bis:FMS 14. sid:11 L353:gcur:set:MEXCL
15. sid:11 L471:bpostapl:bic:FMS 16. sid:11 L786:pre_apl:bis:FMS
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000B1106000 to 0x00000000B1108000
..... 생략 ......
5.2.3 Block header dump[편집]
Block header dump: 0x0046c519
Object id on Block? Y
seg/obj: 0x1203d csc: 0x000000000028c8f1 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000028c8f1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0046c519
data_block_dump,data header at 0xb1106074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0xb1106074
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388
0xe:pti[0] nrow=60 offs=0
.... 생략 ....
block_row_dump:
tab 0, row 0, @0x1f13
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 02
col 1: [ 2] c1 02
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
tab 0, row 1, @0x1e9e
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 04
col 1: [ 2] c1 03
col 2: [ 6] 78 78 78 78 78 78
col 3: [100]
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30 30
... 생략 ...
end_of_block_dump
Block dump from disk:
buffer tsn: 0 rdba: 0x0046c519 (1/443673)
scn: 0x28c8f2 seq: 0x02 flg: 0x04 tail: 0xc8f20602
frmt: 0x02 chkval: 0x9c7f type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
... 생략 ...
Block header dump: 0x0046c519
Object id on Block? Y
seg/obj: 0x1203d csc: 0x000000000028c8f1 itc: 3 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x000000000028c8f1
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0046c519
data_block_dump,data header at 0x7ff0e0927074
===============
tsiz: 0x1f88
hsiz: 0x8a
pbl: 0x7ff0e0927074
76543210
flag=--------
ntab=1
nrow=60
frre=-1
fsbo=0x8a
fseo=0x412
avsp=0x388
tosp=0x388
5.3 undo 블럭 디버깅[편집]
5.3.1 Undo Segment Header[편집]
===================
Undo Segment Header
===================
Start dump data blocks tsn: 2 file#:4 minblk 160 maxblk 160
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777376
BH (0xbaf9f378) file#: 4 rdba: 0x010000a0 (4/160) class: 21 ba: 0xba73a000
set: 11 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
hash: [0x76ec93d0,0x76ec93d0] lru: [0xbafa1a58,0xbaf9f2f8]
lru-flags: hot_buffer
obj-flags: object_ckpt_list
ckptq: [0x89f76428,0x8ef9c3a8] fileq: [0x89f76438,0x8ef9c3b8]
objq: [0x8ef9c4b8,0x89f76538] objaq: [0x6c4d6578,0xbaf9f1c8]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'ktuwh72: ktugus:ktuswr1' fscn: 0x23d008 tch: 42 txn: 0x747dabf0
flags: buffer_dirty private block_written_once
LRBA: [0x6f.1e8e.0] LSCN: [0x28c8e7] HSCN: [0x28c914] HSUB: [1]
Printing buffer operation history (latest change first):
cnt: 10
01. sid:11 L786:pre_apl:bis:FMS 02. sid:11 L803:pre_apl:bis:FPB
03. sid:11 L353:gcur:set:MEXCL 04. sid:11 L464:chg1_mn:bic:FMS
05. sid:11 L778:chg1_mn:bis:FMS 06. sid:11 L353:gcur:set:MEXCL
07. sid:09 L464:chg1_mn:bic:FMS 08. sid:09 L778:chg1_mn:bis:FMS
09. sid:09 L353:gcur:set:MEXCL 10. sid:09 L464:chg1_mn:bic:FMS
11. sid:09 L778:chg1_mn:bis:FMS 12. sid:09 L353:gcur:set:MEXCL
13. sid:11 L464:chg1_mn:bic:FMS 14. sid:11 L778:chg1_mn:bis:FMS
15. sid:11 L353:gcur:set:MEXCL 16. sid:09 L464:chg1_mn:bic:FMS
buffer tsn: 2 rdba: 0x010000a0 (4/160)
scn: 0x28c914 seq: 0x01 flg: 0x00 tail: 0xc9142601
frmt: 0x02 chkval: 0x0000 type: 0x26=KTU SMU HEADER BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000BA73A000 to 0x00000000BA73C000
5.3.2 Undo Start block[편집]
================
Undo Start block
================
Start dump data blocks tsn: 2 file#:4 minblk 542 maxblk 542
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=2 rdba=16777758
BH (0x9cfc4d58) file#: 4 rdba: 0x0100021e (4/542) class: 22 ba: 0x9ca92000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: -1 objn: 0 tsn: [1/2] afn: 4 hint: f
hash: [0x8efadd98,0x76f2cf30] lru: [0x88fcc460,0x9af633e8]
obj-flags: object_ckpt_list
ckptq: [0xb1f8e140,0xbafca320] fileq: [0x75484058,0xbafca330]
objq: [0xbafca430,0x6c4c42f8] objaq: [0xa6fb41e0,0xb0fb63a0]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'ktuwh03: ktugnb' fscn: 0x2852b3 tch: 2 txn: 0x747dabf0
flags: buffer_dirty private block_written_once
LRBA: [0x6f.1ee9.0] LSCN: [0x28c8fa] HSCN: [0x28c913] HSUB: [5]
Printing buffer operation history (latest change first):
cnt: 8
01. sid:11 L802:gcur:bis:FPB 02. sid:11 L353:gcur:set:MEXCL
03. sid:09 L464:chg1_mn:bic:FMS 04. sid:09 L778:chg1_mn:bis:FMS
05. sid:09 L362:chg1:set:MEXCL 06. sid:09 L464:chg1_mn:bic:FMS
07. sid:09 L778:chg1_mn:bis:FMS 08. sid:09 L362:chg1:set:MEXCL
09. sid:09 L464:chg1_mn:bic:FMS 10. sid:09 L778:chg1_mn:bis:FMS
11. sid:09 L362:chg1:set:MEXCL 12. sid:09 L464:chg1_mn:bic:FMS
13. sid:09 L778:chg1_mn:bis:FMS 14. sid:09 L362:chg1:set:MEXCL
15. sid:09 L464:chg1_mn:bic:FMS 16. sid:09 L778:chg1_mn:bis:FMS
buffer tsn: 2 rdba: 0x0100021e (4/542)
scn: 0x28c913 seq: 0x05 flg: 0x00 tail: 0xc9130205
frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000009CA92000 to 0x000000009CA94000
5.3.3 UNDO BLK[편집]
********************************************************************************
UNDO BLK:
xid: 0x0003.019.00000475 seq: 0x104 cnt: 0x8 irb: 0x8 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f80 0x02 0x1f2c 0x03 0x1ea4 0x04 0x1e1c 0x05 0x1db0
0x06 0x1d44 0x07 0x1cdc 0x08 0x1c98
*-----------------------------
* Rec #0x1 slt: 0x1d objn: 1(0x00000001) objd: 4294967295 tblspc: 0(0x00000000)
* Layer: 22 (Tablespace Bitmapped file) opc: 3 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: Yes
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x0100021d.0104.38 ctl max scn: 0x000000000028c27a prv tx scn: 0x000000000028c295
txn start scn: scn: 0x000000000028c8fa logon user: 0
prev brb: 16777751 prev bcl: 0
ktfbhundo - File Space Header Undo:
Space Header DBA:0x400002, File:0x1
Header Opcode:
Save: Free Extent:
Begin: 443680, Length: 8, Instance: 0
..... 생략 .....
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.07
KDO Op code: LKR row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00406991 hdba: 0x00400090
itli: 2 ispac: 0 maxfr: 4863
tabn: 3 slot: 0 to: 0
Block dump from disk:
buffer tsn: 2 rdba: 0x0100021e (4/542)
scn: 0x2852b3 seq: 0x1b flg: 0x04 tail: 0x52b3021b
frmt: 0x02 chkval: 0x4b75 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FF0E0927000 to 0x00007FF0E0929000
... 생략 ...
********************************************************************************
UNDO BLK:
xid: 0x0003.008.00000462 seq: 0x100 cnt: 0x1a irb: 0x1a icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f10 0x02 0x1e38 0x03 0x1d90 0x04 0x1d1c 0x05 0x1cac
0x06 0x1c3c 0x07 0x1bc8 0x08 0x1af0 0x09 0x1a48 0x0a 0x1970
0x0b 0x18c8 0x0c 0x17f0 0x0d 0x1718 0x0e 0x1640 0x0f 0x1598
0x10 0x14f0 0x11 0x1418 0x12 0x1370 0x13 0x1298 0x14 0x11f0
0x15 0x1118 0x16 0x1040 0x17 0x0f68 0x18 0x0ec0 0x19 0x0dcc
0x1a 0x0d84
*-----------------------------
* Rec #0x1 slt: 0x08 objn: 73609(0x00011f89) objd: 73609 tblspc: 1(0x00000001)
* Layer: 10 (Index) opc: 22 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x0100021d
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021d.0100.21
Dump kdilk : itl=2, kdxlkflg=0x21 sdc=32767 indexid=0xc14832 block=0x00c159f0
(kdxlpu): purge leaf row
number of keys: 3
key sizes:
42 42 42
key :(126):
06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03 03 c2 16 0a 07 78 7c 06 17
11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00 65 06 c5 1c 2b 06 19 43 02
c1 5e 02 c1 02 02 c1 03 03 c2 16 16 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06
19 43 06 00 c1 59 ff 00 66 06 c5 1c 2b 06 19 43 02 c1 5e 02 c1 02 02 c1 03
03 c2 16 2d 07 78 7c 06 17 11 03 30 06 c5 1c 2b 06 19 43 06 00 c1 59 ff 00
67
keydata/bitmap: (1): ff
5.4 redo 블럭 디버깅[편집]
DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/MONGO/redo01.log'
Container ID: 0
Container UID: 0
Opcodes *.*
Container ID: 0
Container UID: 0
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000000000000000 thru scn: 0xffffffffffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 318767104=0x13000000
Db ID=2742052466=0xa3706672, Db Name='MONGO'
Activation ID=2742030962=0xa3701272
Control Seq=8990=0x231e, File size=409600=0x64000
File Number=1, Blksiz=512, File Type=2 LOG
descrip:"T 0001, S 0000000112, SCN 0x000000000028c925-0xffffffffffffffff"
thread: 1 nab: 0xffffffff seq: 0x00000070 hws: 0x1 eot: 1 dis: 0
resetlogs count: 0x45d061b5 scn: 0x00000000001d4fd1
prev resetlogs count: 0x3bf3129f scn: 0x0000000000000001
Low scn: 0x000000000028c925 06/24/2024 13:04:33
Next scn: 0xffffffffffffffff 01/01/1988 00:00:00
Enabled scn: 0x00000000001d4fd1 06/10/2024 12:46:45
Thread closed scn: 0x000000000028c925 06/24/2024 13:04:33
Real next scn: 0xffffffffffffffff
Disk cksum: 0x5e1b Calc cksum: 0x5e1b
Terminal recovery stop scn: 0x0000000000000000
Terminal recovery 01/01/1988 00:00:00
Most recent redo scn: 0x0000000000000000
Largest LWN: 0 blocks
End-of-redo stream : No
Unprotected mode
Miscellaneous flags: 0x800000
Miscellaneous second flags: 0x0
Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000000000000000
Zero blocks: 0
Format ID is 18
redo log key is 675ff3d7a77b9fb48595ec24a9804d3f
redo log key flag is 15
High watermark block number: 0
Enabled redo threads: 1
REDO RECORD - Thread:1 RBA: 0x000070.00000002.0010 LEN: 0x05d8 VLD: 0x0d CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 1 06/24/2024 13:04:45
(LWN RBA: 0x000070.00000002.0010 LEN: 0x00000006 NST: 0x0001 SCN: 0x000000000028c928)
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c914 SEQ:1 OP:5.2 ENC:0 RBL:0 FLG:0x0000
ktudh redo: slt: 0x0021 sqn: 0x00000474 flg: 0x0052 siz: 140 fbi: 0
uba: 0x0100021e.0104.09 pxid: 0x0000.000.00000000 pdbid:1
CHANGE #3 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c8f2 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.021.00000474 uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #4 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #5 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:1 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0c
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #6 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0d
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 2 ckix: 18
ncol: 4 nnew: 1 size: 4
col 2: [10] 59 59 59 59 59 59 59 59 59 59
CHANGE #7 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c913 SEQ:5 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 140 spc: 7286 flg: 0x0012 seq: 0x0104 rec: 0x09
xid: 0x0003.021.00000474
ktubl redo: slt: 33 wrp: 1 flg: 0x0c08 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
Begin trans
prev ctl uba: 0x0100021e.0104.04 prev ctl max cmt scn: 0x000000000028c2b9
prev tx cmt scn: 0x000000000028c2cf
txn start scn: 0x000000000028c920 logon user: 0
prev brb: 0x01000217 prev bcl: 0x00000000
BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #8 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 88 spc: 7144 flg: 0x0022 seq: 0x0104 rec: 0x0a
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #9 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 7054 flg: 0x0022 seq: 0x0104 rec: 0x0b
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #10 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:3 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 6956 flg: 0x0022 seq: 0x0104 rec: 0x0c
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #11 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:4 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 6858 flg: 0x0022 seq: 0x0104 rec: 0x0d
xid: 0x0003.021.00000474
ktubu redo: slt: 33 wrp: 1140 flg: 0x0000 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] No [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
KDO undo record:
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
REDO RECORD - Thread:1 RBA: 0x000070.00000005.0018 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 2 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0b
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:5 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 12 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1a94 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000005.00e4 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 3 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:2 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.0a
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:6 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 11 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1af4 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000005.01b0 LEN: 0x00cc VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 4 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:4 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x0100021e.0104.09
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:7 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 10 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1b54 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000006.008c LEN: 0x00c4 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c51a OBJ:73789 SCN:0x000000000028c928 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c51a hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:22 AFN:4 DBA:0x0100021e OBJ:4294967295 SCN:0x000000000028c928 SEQ:8 OP:5.6 ENC:0 RBL:0 FLG:0x0000
ktubu redo: slt: 33 wrp: 1140 flg: 0x0010 prev dba: 0x00000000 rci: 9 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
ktuxvoff: 0x1bb4 ktuxvflg: 0x0002
REDO RECORD - Thread:1 RBA: 0x000070.00000006.0150 LEN: 0x00c4 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c928 SUBSCN: 5 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0046c519 OBJ:73789 SCN:0x000000000028c928 SEQ:5 OP:11.5 ENC:0 RBL:0 FLG:0x0000
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XR flags: 0x00000000 bdba: 0x0046c519 hdba: 0x0046c518
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 18
ncol: 4 nnew: 1 size: -4
col 2: [ 6] 78 78 78 78 78 78
CHANGE #2 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:1 OP:5.11 ENC:0 RBL:0 FLG:0x0000
ktubl redo: slt: 33 wrp: 1 flg: 0x0c18 prev dba: 0x00000000 rci: 0 opc: 11.1 [objn: 73789 objd: 73789 tsn: 0]
[Undo type ] Regular undo [User undo done ] Yes [Last buffer split] No
[Temp object] No [Tablespace Undo ] No [User only ] No
Begin trans
BuExt idx: 0 flg2: 0
REDO RECORD - Thread:1 RBA: 0x000070.00000007.0024 LEN: 0x0058 VLD: 0x01 CON_UID: 1
SCN: 0x000000000028c929 SUBSCN: 1 06/24/2024 13:04:45
CHANGE #1 CON_ID:1 TYP:0 CLS:21 AFN:4 DBA:0x010000a0 OBJ:4294967295 SCN:0x000000000028c928 SEQ:2 OP:5.4 ENC:0 RBL:0 FLG:0x0000
ktucm redo: slt: 0x0021 sqn: 0x00000474 srt: 0 sta: 9 flg: 0x4
rolled back transaction
END OF REDO DUMP
==== Redo read statistics for thread 1 ====
Total physical reads (from disk and memory): 1023Kb
-- Redo read_disk statistics --
Read rate (SYNC): 3Kb in 0.01s => 0.29 Mb/sec
Total redo bytes: 1023Kb Longest record: 1Kb, moves: 0/7 moved: 0Mb (0%)
Longest LWN: 3Kb, reads: 1
Last redo scn: 0x000000000028c929 (2672937)
Change vector header moves = 1/22 (4%)
6 redo 테이블스페이스 사이즈 확인[편집]
7 테이블스페이스 사이즈 확인[편집]
select *
from dba_tablespaces
order by 1,2;
select owner,count(*)
from dba_segments
where 1=1 -- owner='SYS'
group by owner
order by 1,2
;
-- 테이블 생성
create table dbcafe.TB_T1(A number, B char(1),C varchar2(10))
tablespace TS_DBCAFE;
select * from dbcafe.TB_T1;
-- 세그먼트에는 테이블에 데이터가 입력된적이 없으므로 조회되지 않음. 데이터 입력시 적용됨.
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- 데이터 1건 입력
INSERT INTO dbcafe.TB_T1 VALUES (1,'1','11111');
-- commit 하지 말고 세그먼트 조회
select *
from dba_segments
where segment_name like 'TB_T%'
;
-- header_file 13
-- header_block 130
-- bytes 65536
-- blocks 8
-- extents 1
-- initial_extent 65536
-- next_extent 1048576
-- relative_fno 13
select * from dba_data_files where file_id = 13; -- 테이블스페이 데이터파일 번호 // relative_fno 13
-- [데이터 입력후 아직 커밋하지 않음]
-- 13 130 65536 8 1 65536 1048576
select * from v$session
where status ='ACTIVE'
;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no -- data file number
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;
-- block info
-- rowid file_no block_no
AAAR/wAANAAAACEAAA 13 132
;
-- 트레이스 파일 식별자 설정 (트레이스파일을 쉽게 찾기 위해서 설정 )
alter session set tracefile_identifier ='DBCAFE_Block_Dump';
alter system dump datafile 13 block 132;
-- 트레이스 폴더로 이동
cd $ORACE_BASE/diag/rdbms/{mongo/MONGO}/trace -- DB마다 {---} 위치가 다름
-- 트레이스 파일 찾기
ls |grep DBCAFE_Block_Dump;
MONGO_ora_43565_DBCAFE_Block_Dump.trc <== 트레이스 파일
MONGO_ora_43565_DBCAFE_Block_Dump.trm <== trace meta file,트레이스파일 메타정보를 보관하는 파일 , adrci같은 툴에서 활용함
-- 트레이스 파일 열기
vi MONGO_ora_43565_DBCAFE_Block_Dump.trc
-- 12c 이상 부터는 sql로 조회 가능
SELECT *
FROM V$DIAG_TRACE_FILE
where trace_filename like '%DBCAFE%'
ORDER BY CHANGE_TIME DESC
;
SELECT replace(replace(payload,chr(13)),chr(10)) as payload
FROM V$DIAG_TRACE_FILE_CONTENTS
WHERE TRACE_FILENAME = 'MONGO_ora_23904_DBCAFE_Block_Dump.trc'
ORDER BY LINE_NUMBER
;
Trace file /u01/app/oracle/diag/rdbms/mongo/MONGO/trace/MONGO_ora_23904_DBCAFE_Block_Dump.trc
... 생략 ....
*** 2024-06-23T03:22:44.434510+00:00 (CDB$ROOT(1))
*** SESSION ID:(21.6605) 2024-06-23T03:22:44.434570+00:00
-- 21은 sid
-- 6605는 serial#
..... 생략 ....
-----------------
Start dump data blocks tsn: 6 file#:13 minblk 132 maxblk 132
-- 1. 버퍼캐시 덤프
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=6 rdba=54526084
BH (0x7cf64db8) file#: 13 rdba: 0x03400084 (13/132) class: 1 ba: 0x7c20a000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 73712 objn: 73712 tsn: [1/6] afn: 13 hint: f
hash: [0x75181db8,0x75181db8] lru: [0x9afaa2c0,0x8cf53998]
ckptq: [NULL] fileq: [NULL]
objq: [0x6667add0,0x6667add0] objaq: [0x6667adc0,0x6667adc0]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x273bb5 tch: 2
flags: block_written_once
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x273bb5] HSUB: [3]
Printing buffer operation history (latest change first):
cnt: 3
01. sid:00 L192:kcbbic2:bic:FBD 02. sid:00 L191:kcbbic2:bic:FBW
03. sid:00 L602:bic1_int:bis:FWC 04. sid:00 L822:bic1_int:ent:rtn
05. sid:00 L832:oswmqbg1:clr:WRT 06. sid:00 L930:kubc:sw:mq
07. sid:00 L913:bxsv:sw:objq 08. sid:00 L608:bxsv:bis:FBW
09. sid:00 L607:bxsv:bis:FFW 10. sid:05 L464:chg1_mn:bic:FMS
11. sid:05 L778:chg1_mn:bis:FMS 12. sid:05 L353:gcur:set:MEXCL
13. sid:05 L464:chg1_mn:bic:FMS 14. sid:05 L614:chg1_mn:bis:FBD
15. sid:05 L922:klbc:sw:cq 16. sid:05 L778:chg1_mn:bis:FMS
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007C20A000 to 0x000000007C20C000
........... 생략 ..............
-- 블럭 헤더 덤프
-- Itl 로우 입력한 트랜젝션 이 itl 0x01 슬롯에 저장됨 , 0x01은 1번째
select xidusn,xidslot,xidsqn from v$transaction
where addr = (select taddr from v$session where sid = 21);
-- 세션 정보 조회는 TM 락
SELECT A.SID
, A.SERIAL#
, C.object_name
, A.SID || ', ' || A.SERIAL# AS KILL_TASK
FROM V$SESSION A
INNER JOIN V$LOCK B
ON A.SID = B.SID
INNER JOIN DBA_OBJECTS C
ON B.ID1 = C.OBJECT_ID
WHERE B.TYPE = 'TM'
;
-- xidusn xidslot xidsqn
4 8 1059
-- 16진수 -> 10진수로 변환
4 8 4185
;
-- 16진수 -> 10진수로 변환
select to_number('1059', 'xxxx') from dual;
;
-- 10진수 -> 16진수로 변환
select to_char(1059, 'xxxx') from dual;
;
Block header dump: 0x03400084
Object id on Block? Y
seg/obj: 0x11ff0 csc: 0x0000000000273bb5 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x3400080 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.008.00000423 0x01000614.017b.1f ---- 1 fsc 0x0000.00000000
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x03400084
data_block_dump,data header at 0x7c20a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7c20a064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8a
avsp=0x1f76
tosp=0x1f76
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8a
block_row_dump:
tab 0, row 0, @0x1f8a
tl: 14 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 1] 31
col 2: [ 5] 31 31 31 31 31
end_of_block_dump
Block dump from disk:
buffer tsn: 6 rdba: 0x03400084 (13/132)
scn: 0x273bb5 seq: 0x03 flg: 0x04 tail: 0x3bb50603
frmt: 0x02 chkval: 0x0e55 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F628C33A000 to 0x00007F628C33C000
........... 생략 ............
-- commit;
select rowid
, dbms_rowid.rowid_relative_fno(rowid) as file_no
, dbms_rowid.rowid_block_number(rowid) as block_no
from dbcafe.TB_T1 where rownum = 1;