Undo 세미나
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
{{:UNDO 와 REDO 테스트}
목차
1 데이터 UPDATE 처리 과정[편집]
1.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
1.2 블럭 디버깅[편집]
-- trace file 구분자 입력
alter session set tracefile_identifier = 'DBCAFE';
-- sql 실행
SQL> @core_demo_02.sql
1.2.1 trace file 분석[편집]
---------------------------
-- session info
*** SESSION ID:(277.62324) 2024-06-26T09:13:49.151026+00:00
1.2.2 컬럼 rowid 로 데이터 파일번호,데이터 블럭 위치 찾기[편집]
- 예제에서 id컬럼 5~9까지 값을 변경 함.
select rowid -- extended_format
, id
, dbms_rowid.rowid_to_restricted(rowid, 0) r_f
, dbms_rowid.rowid_object(rowid) object
, dbms_rowid.rowid_relative_fno(rowid) file_no
, dbms_rowid.rowid_block_number(rowid) block_no
, dbms_rowid.rowid_row_number(rowid) row_nillruJer
from sys.t1
WHERE id BETWEEN 5 AND 9
;
1.2.3 Block dump from cache[편집]
Start dump data blocks tsn: 0 file#:1 minblk 117153 maxblk 117153
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=0 rdba=4311457
BH (0xb1f51150) file#: 1 rdba: 0x0041c9a1 (1/117153) class: 1 ba: 0xb1048000
set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0
dbwrid: 0 obj: 86126 objn: 86126 tsn: [1/0] afn: 1 hint: f
hash: [0xacff8348,0x96f61f90] lru: [0xb8ff2db8,0xaafa3ab0]
ckptq: [NULL] fileq: [NULL]
objq: [0x7d16ecb8,0x7d16ecb8] objaq: [0x7d16eca8,0x7d16eca8]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' fscn: 0x8577c9 tch: 1
flags: only_sequential_access
LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]
Printing buffer operation history (latest change first):
cnt: 12
01. sid:05 L338:zibmlt:set:MSHR 02. sid:05 L144:zibmlt:mk:EXCL
03. sid:05 L122:zgb:set:st 04. sid:05 L830:olq1:clr:WRT+CKT
05. sid:05 L951:zgb:lnk:objq 06. sid:05 L372:zgb:set:MEXCL
07. sid:05 L123:zgb:no:FEN 08. sid:05 L083:zgb:ent:fn
09. sid:01 L940:z_sw_cur:sw:cq 10. sid:01 L070:zswcu:ent:ob
11. sid:01 L082:zcr:ret:TRU 12. sid:00 L192:kcbbic2:bic:FBD
13. sid:00 L191:kcbbic2:bic:FBW 14. sid:00 L602:bic1_int:bis:FWC
15. sid:00 L822:bic1_int:ent:rtn 16. sid:00 L832:oswmqbg1:clr:WRT
buffer tsn: 0 rdba: 0x0041c9a1 (1/117153)
scn: 0x8577c1 seq: 0x02 flg: 0x04 tail: 0x77c10602
frmt: 0x02 chkval: 0xe03e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000B1048000 to 0x00000000B104A000
..... 생략 ......
1.2.4 Block header dump[편집]
1.UPDATE 전 t1 테이블 블럭 덤프
tab 0, row 4, @0x1d3f
tl: 117 fb: --H-FL-- lb: 0x0 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
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
- 78 78 78 78 78 78 은 'xxxxxx'
SELECT HEXTORAW(78) FROM dual; -- 소문자 x
2.UPDATE 후 t1 테이블 블럭 덤프
tab 0, row 4, @0x2a7
tl: 121 fb: --H-FL-- lb: 0x2 cc: 4
col 0: [ 2] c1 0a
col 1: [ 2] c1 06
col 2: [10] 59 59 59 59 59 59 59 59 59 59
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
- 59 59 59 59 59 59 59 59 59 59
SELECT HEXTORAW(59) FROM dual;-- 대문자 Y
3.변경 사항
1.3 undo 블럭 덤프[편집]
1.3.1 UNDO BLK[편집]
********************************************************************************
UNDO BLK:
xid: 0x0009.015.000013cb seq: 0x6e9 cnt: 0x1 irb: 0x1 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x0014
*-----------------------------
* Rec #0x1 slt: 0x15 objn: 11582(0x00002d3e) objd: 11582 tblspc: 1(0x00000001)
* Layer: 10 (Index) opc: 21 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x0100104b
*-----------------------------
index general undo (branch) operations
KTB Redo
op: 0x05 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: R itc: 2
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.015.000013cb 0x0100104b.06e9.08 ---- 1 fsc 0x0000.00000000
0x02 0x0009.014.000013c9 0x0100104c.06e9.14 ---- 6 fsc 0x0000.00000000
Dump kdige : block dba :0x00c130e0, seghdr dba: 0x00c02cba
1.4 온라인 리두 파일 덤프[편집]
1.4.1 redo 블럭 체인지 벡터[편집]
REDO RECORD - Thread:1 RBA: 0x000086.0000000b.0010 LEN: 0x05d8 VLD: 0x0d CON_UID: 1
SCN: 0x0000000000857805 SUBSCN: 1 06/26/2024 09:14:14
(LWN RBA: 0x000086.0000000b.0010 LEN: 0x00000006 NST: 0x0001 SCN: 0x0000000000857805)
CHANGE #1 CON_ID:1 TYP:0 CLS:1 AFN:1 DBA:0x0041c9a1 OBJ:86126 SCN:0x00000000008577c1 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: 0x0009.007.000013de uba: 0x0100104d.06ee.3a
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0041c9a1 hdba: 0x0041c9a0
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
1.4.2 undo 블럭 체인지 벡터[편집]
CHANGE #10 CON_ID:1 TYP:0 CLS:34 AFN:4 DBA:0x0100104d OBJ:4294967295 SCN:0x0000000000857805 SEQ:3 OP:5.1 ENC:0 RBL:0 FLG:0x0000
ktudb redo: siz: 96 spc: 1086 flg: 0x0022 seq: 0x06ee rec: 0x3d
xid: 0x0009.007.000013de
ktubu redo: slt: 7 wrp: 5086 flg: 0x0000 prev dba: 0x00000000 rci: 60 opc: 11.1 [objn: 86126 objd: 86126 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: 0x0100104d.06ee.3b
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0041c9a2 hdba: 0x0041c9a0
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
2 redo 테이블스페이스 사이즈 확인[편집]
3 테이블스페이스 사이즈 확인[편집]
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;
구분 | 변경 전 | 변경 후 | 비 고 |
---|---|---|---|
dba(데이터 블럭 주소) | @0x1d3f | @0x2a7 | xxxxxx => YYYYYYYYYY 로우 길이가 길어져 주소이동 |
??????? | tl:117 | tl:121 | |
Lock Byte | lb: 0x0 | lb: 0x2 | 해당 로우는 ITL 2번째 슬롯을 사용하는 트랜잭션에 의해 락이 설정됨을 의미. |