내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
쿼리 : 언두헤더 덤프하는 법 undo header dump
Session 1
emp 테이블 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 (null) 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 (null) 20 7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 (null) 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 (null) 20 7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30 7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20 7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10 |
sal이 800인 row 업데이트
1 2 3 | SQL> update emp2 set sal = 9999 where sal = 800; 1 row updated. |
emp 테이블 재 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 9999 (null) 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 (null) 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 (null) 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 (null) 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 (null) 20 7839 KING PRESIDENT (null) 17-NOV-81 5000 (null) 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 (null) 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 (null) 20 7900 JAMES CLERK 7698 03-DEC-81 950 (null) 30 7902 FORD ANALYST 7566 03-DEC-81 3000 (null) 20 7934 MILLER CLERK 7782 23-JAN-82 1300 (null) 10 14 rows selected. |
Session 2
UNDO 정보 조회
1 2 3 4 5 | SQL> SELECT UBAFIL, UBABLK, UBAREC FROM V$TRANSACTION; UBAFIL UBABLK UBAREC ---------- ---------- ---------- 3 122632 18 |
UNDO 세그먼트 조회
1 2 3 4 5 6 7 8 9 | SQL> SELECT SEGMENT_NAME FROM DBA_EXTENTS WHERE FILE_ID = 3 AND 122632 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1; SEGMENT_NAME ----------------------- _SYSSMU9_3050522976$ |
트랜잭션 정보 조회
1 2 3 4 5 | SQL> SELECT XIDUSN, XIDSLOT, XIDSQN FROM V$TRANSACTION; XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 9 14 35871 |
확인한 정보로 DUMP
1 2 3 4 5 6 7 | SQL> ALTER SYSTEM DUMP UNDO BLOCK '[조회한 SEGMENT_NAME]' xid [조회한 XIDUSN XIDSLOT XIDSQN]; System altered. SQL> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_3050522976$' xid 9 14 35871; System altered. |
dump 파일 있는 경로로 이동
1 | [oracle@rac1 trace]$cd /app/oracle/diag/rdbms/orcl/orcl/trace/ |
ls -ltr로 제일 최근에 변경된 파일 확인
1 2 3 4 5 6 | [oracle@rac1 trace]$ls -ltr . . -rw-r----- 1 oracle dba 196844 Apr 30 11:03 alert_orcl.log -rw-r----- 1 oracle dba 92 Apr 30 11:03 orcl_ora_7866.trm -rw-r----- 1 oracle dba 4327 Apr 30 11:03 orcl_ora_7866.trc |
vi 로 해당 파일 내용 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 | [oracle@rac1 trace]$ vi orcl_ora_7866.trc Trace file /app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7866.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /app/oracle/product/11.2.0/db_1 System name: Linux Node name: rac1 Release: 4.1.12-94.3.9.el7uek.x86_64 Version: #2 SMP Fri Jul 14 20:09:40 PDT 2017 Machine: x86_64 VM name: VMWare Version: 6 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 23 Unix process pid: 7866, image: oracle@rac1 (TNS V1-V3) *** 2018-04-30 11:02:22.481 *** SESSION ID:(1.25) 2018-04-30 11:02:22.481 *** CLIENT ID:() 2018-04-30 11:02:22.481 *** SERVICE NAME:(SYS$USERS) 2018-04-30 11:02:22.481 *** MODULE NAME:(sqlplus@rac1 (TNS V1-V3)) 2018-04-30 11:02:22.481 *** ACTION NAME:() 2018-04-30 11:02:22.481 ******************************************************************************** Undo Segment: _SYSSMU9_3050522976$ (9) xid: 0x0009.00e.00008c20 Low Blk : (0, 0) High Blk : (98, 127) Object Id : ALL Layer : ALL Opcode : ALL Level : 2 *** 2018-04-30 11:02:28.534 ******************************************************************************** Undo Segment: _SYSSMU9_3050522976$ (9) xid: 0x0009.00e.00008c1f Low Blk : (0, 0) High Blk : (98, 127) Object Id : ALL Layer : ALL Opcode : ALL Level : 2 ******************************************************************************** UNDO BLK: Extent: 77 Block: 264 dba (file#, block#): 3,0x0001df08 xid: 0x0009.00e.00008c1f seq: 0x135 cnt: 0x12 irb: 0x12 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset --------------------------------------------------------------------------- 0x01 0x1f60 0x02 0x1efc 0x03 0x1eac 0x04 0x1e50 0x05 0x1dc8 0x06 0x1d68 0x07 0x1ce0 0x08 0x1c80 0x09 0x1bf8 0x0a 0x1b88 0x0b 0x1b28 0x0c 0x1a80 0x0d 0x1a3c 0x0e 0x19dc 0x0f 0x1998 0x10 0x1938 0x11 0x18b0 0x12 0x184c *----------------------------- * Rec #0x12 slt: 0x0e objn: 13791(0x000035df) objd: 13791 tblspc: 0(0x00000000) * Layer: 13 (Transasction Segment) opc: 9 rci 0x11 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- KTSFUNLK (xnt freelist link) undo: freelist header block tsn: 0 rdba: 0x0040ac68 class: 4 block rdba: 0x0040ac69 itl index: 2 Opcode: LUPD_UNLBLK (unlink block) Slot no: 1, Count: 1 Flag: = 1 xid or slot0 ccnt: 0x0000.000.00000000 Head: 0x00000000 Tail: 0x00000000 KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x00c1df08.0135.11 *----------------------------- * Rec #0x11 slt: 0x0e objn: 13791(0x000035df) objd: 13791 tblspc: 0(0x00000000) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *----------------------------- uba: 0x00c1df08.0135.09 ctl max scn: 0x0000.01767b62 prv tx scn: 0x0000.01767b6e txn start scn: scn: 0x0000.00000000 logon user: 12 prev brb: 12705541 prev bcl: 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: 0x0040ac69 hdba: 0x0040ac68 itli: 2 ispac: 0 maxfr: 4863 tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12 ncol: 8 nnew: 1 size: -1 col 5: [ 2] c2 09 +++++++++++ Next block not in extent map - rollback segment has been shrunk. + WARNING + Block dba (file#, block#): 0,0x00000000 +++++++++++ ************************************* Total undo blocks scanned = 1 Total undo records scanned = 2 Total undo blocks dumped = 1 Total undo records dumped = 2 ##Total warnings issued = 1 ************************************* *** 2018-04-30 11:03:28.675 ******************************************************************************** Undo Segment: _SYSSMU9_3050522976$ (9) xid: 0x0003.012.0001df08 Low Blk : (0, 0) High Blk : (98, 127) Object Id : ALL Layer : ALL Opcode : ALL Level : 2 |
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 With절 (0) | 2018.05.03 |
|---|---|
| Raw Device 에서 File System 으로 데이타파일 변환하기 (0) | 2018.04.30 |
| Oracle Job 수행 이력 확인 쿼리, 뷰 (0) | 2018.04.30 |
| 오라클 히든파라미터 조회 방법 (0) | 2018.04.19 |
| default temporary tablespace 지정(database, user) (0) | 2018.04.13 |