"Undo 세미나"의 두 판 사이의 차이

5.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

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;