행위

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

DB CAFE

(→‎언두 테스트)
(→‎테이블 UPDATE 시나리오 SQL)
 
(같은 사용자의 중간 판 39개는 보이지 않습니다)
1번째 줄: 1번째 줄:
 
=== 언두 테스트 ===
 
=== 언두 테스트 ===
 +
<source lang=sql>
 +
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;
 +
 +
</source>
 +
 
<source lang=sql>
 
<source lang=sql>
 
-- 테이블 생성  
 
-- 테이블 생성  
13번째 줄: 82번째 줄:
 
w_ins tbl_ins;
 
w_ins tbl_ins;
 
BEGIN
 
BEGIN
−
FOR i IN 1..1400000 LOOP  
+
FOR i IN 1..8960000 LOOP  
 
   w_ins(i).COLA :=i;
 
   w_ins(i).COLA :=i;
 
   w_ins(i).COLB :=300000;
 
   w_ins(i).COLB :=300000;
24번째 줄: 93번째 줄:
 
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
 
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
 
END LOOP;
 
END LOOP;
−
   FORALL i in 1..1400000 INSERT INTO TB_BIG VALUES w_ins(i);
+
   FORALL i in 1..8960000 INSERT INTO TB_BIG VALUES w_ins(i);
 
   COMMIT;
 
   COMMIT;
 
END;
 
END;
 
/
 
/
−
 
+
-- 8,960,000 건
 
</source>
 
</source>
  
37번째 줄: 106번째 줄:
 
</source>
 
</source>
  
−
 
+
==== UPDATE 테스트 ====
 
<source lang=sql>
 
<source lang=sql>
 
sh-4.2$ cat update_tb_big.sql
 
sh-4.2$ cat update_tb_big.sql
52번째 줄: 121번째 줄:
 
15:54:14 SQL>
 
15:54:14 SQL>
 
====== 종료 ====== 총 7분 40초 , 460초  
 
====== 종료 ====== 총 7분 40초 , 460초  
 +
</source>
  
 +
==== CTAS 테스트 ====
  
−
 
+
<source lang=sql>
 
sh-4.2$ cat ctas_tb_big.sql
 
sh-4.2$ cat ctas_tb_big.sql
 
-- rename old  
 
-- rename old  
87번째 줄: 158번째 줄:
 
16:35:46 SQL>
 
16:35:46 SQL>
 
====== 종료 ====== 총 7분 43초 , 463초  
 
====== 종료 ====== 총 7분 43초 , 463초  
 +
</source>
 +
==== UPDATE 테스트 ====
 +
* 아카이빙 로그 사이즈
 +
** 로그 테스트 전 
 +
<source lang=sql>
 +
bash-4.2# du -h
 +
16K .
 +
</source>
 +
** 로그 테스트 후
 +
<source lang=sql>
 +
-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 .
 +
</source>
 +
 +
==== CTAS 테스트 ====
 +
<source lang=sql>
 +
 +
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 ./
 +
 +
</source>
 +
=== CTAS NOLOGGING MODE ===
 +
 +
==== ctas nologging mode ====
 +
<source lang=sql>
 +
 +
bash-4.2# du -h
 +
16K .
 +
</source>
 +
* 00:19.21 소요
 +
<source lang=sql>
 +
-- UNRECOVERABLE 옵션 사용
 +
SQL> @ctas_tb_big_nolog.sql
 +
 +
Table altered.
 +
 +
Elapsed: 00:00:00.03
 +
 +
Table created.
 +
 +
Elapsed: 00:00:19.21
 +
</source>
 +
 +
==== ctas nolooging 처리후 ====
 +
<source lang=sql>
 +
 +
bash-4.2# du -h
 +
1.2G .
 +
==> nolooging 인데도 사이즈가 크게 줄지 않음......
 +
 +
 +
--- 테이블 drop 시
 +
drop table tb_big2 purge;
 +
 +
bash-4.2# du -h
 +
1.3G .
 +
</source>
 +
 +
 +
==== insert ~ select  nologging ====
 +
* 01:19 초 소요
 +
<source lang=sql>
 +
 +
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
 +
</source>
 +
 +
<source lang=sql>
 +
 +
bash-4.2# du -h
 +
1.2G .
 +
bash-4.2#
 +
</source>
 +
 +
=== 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 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 테이블스페이스 사이즈 확인 ===
 +
 +
=== 테이블스페이스 사이즈 확인 ===
 +
<source lang=sql>
 +
 +
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;
 +
 
</source>
 
</source>

2024년 6월 26일 (수) 13:27 기준 최신판

thumb_up 추천메뉴 바로가기

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[편집]

  • 진행 순서
  1. create table t1 (테이블 생성 후 2개 블럭에 데이터 입력.)
    1. 인덱스 생성
    2. 통계 정보 생성
  2. 블럭 번호 조회
  3. 로그 스위치
  4. 변경전 블록 덤프조회
  5. 테이블 업데이트 id가 5~9 인건
    1. 데이터 변경 후 블럭 덤프
    2. 언두 블럭 덤프
    3. 롤백 / 커밋 수행
  6. 리두 로그 덤프 수행


-- 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;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Undo_세미나&oldid=12012"