用alter system dump命令dump redo、 undo、 data block示例 (二）

        alter system dump datafile block示例

查看当前数据所在数据文件和数据块

1. > select * from tt;
2. 
   
3. A
   
4. ----------
   
5. a
   
6. 2
   
7. 
   
8. > select dbms_rowid.rowid_block_number(rowid) bl,dbms_rowid.rowid_relative_fno(rowid) fno from tt;
   
9. 
   
10. 
    
11.         BL FNO
    
12. ---------- ----------
    
13.        182 4
    
14.        182 4

执行dump命令

1. > alter system dump datafile 4 block 182;
2. 
   
3. System altered.
   

 #必须拥有sysdba权限用户执行

1. > oradebug setmypid;
   
2. 
   
3. Statement processed.
4. #查看trace文件路径
5. > oradebug tracefile_name;
   
6. 
   
7. /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc

查看dump出来的trace文件

1. [oracle@uumile ~]$ more /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc
   
2. Trace file /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_7427.trc
   
3. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
   
4. With the Partitioning, OLAP, Data Mining and Real Application Testing options
   
5. 
   
6. ORACLE_HOME = /s01/oracle/app/oracle/product/11.2.0.4/dbhome_1                       #操作系统的一些基本信息
   
7. System name: Linux
   
8. Node name: uumile
   
9. Release: 2.6.18-308.el5
   
10. Version: #1 SMP Fri Jan 27 17:17:51 EST 2012
    
11. Machine: x86_64
    
12. Instance name: test
    
13. Redo thread mounted by this instance: 1
    
14. Oracle process number: 18
    
15. Unix process pid: 7427, image: oracle@uumile (TNS V1-V3)  
16.                           
    
17. *** 2015-03-27 15:58:17.941                                                            #执行dump的会话信息
    
18. *** SESSION ID:(1.21) 2015-03-27 15:58:17.941
    
19. *** CLIENT ID:() 2015-03-27 15:58:17.941
    
20. *** SERVICE NAME:(SYS$USERS) 2015-03-27 15:58:17.941
    
21. *** MODULE NAME:(sqlplus@uumile (TNS V1-V3)) 2015-03-27 15:58:17.941
    
22. *** ACTION NAME:() 2015-03-27 15:58:17.941
    

1. Start dump data blocks tsn: 4 file#:4 minblk 182 maxblk 182
   
2. Block dump from cache:
   
3. Dump of buffer cache at level 4 for tsn=4 rdba=16777398
   
4. BH (0x85fdd170) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85cbc000
   
5. set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
   
6. dbwrid: 0 obj: 15054 objn: 15054 tsn: 4 afn: 4 hint: f
   
7. hash: [0x85fdf1d8,0x90e6bee0] lru: [0x85fdd398,0x85fdd128]
   
8. ckptq: [NULL] fileq: [NULL] objq: [0x85fdd3c0,0x85fdd150] objaq: [0x85fdd3d0,0x85fdd160]
   
9. st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 4
   
10. flags: block_written_once redo_since_read
    
11. LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
    
12. BH (0x85fdf120) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85cf0000
    
13. set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
    
14. dbwrid: 0 obj: 15050 objn: 15050 tsn: 4 afn: 4 hint: f
    
15. hash: [0x85fe76b0,0x85fdd228] lru: [0x85fdf348,0x85fdf0d8]
    
16. lru-flags: on_auxiliary_list
    
17. ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
    
18. st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33
    
19. flags:
    
20. BH (0x85fe75f8) file#: 4 rdba: 0x010000b6 (4/182) class: 1 ba: 0x85dca000
    
21. set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 417,19
    
22. dbwrid: 0 obj: 15035 objn: 15035 tsn: 4 afn: 4 hint: f
    
23. hash: [0x873d98e0,0x85fdf1d8] lru: [0x85fe7820,0x85fe75b0]
    
24. lru-flags: on_auxiliary_list
    
25. ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
    
26. st: FREE md: NULL fpin: 'kdswh11: kdst_fetch' tch: 0 lfb: 33
    
27. flags:
    
28. Block dump from disk:
    
29. buffer tsn: 4 rdba: 0x010000b6 (4/182)
    
30. scn: 0x0000.000940e1 seq: 0x01 flg: 0x06 tail: 0x40e10601
    
31. frmt: 0x02 chkval: 0x138d type: 0x06=trans data
    
32. Hex dump of block: st=0, typ_found=1
    
33. Dump of memory from 0x00002AC9C7033A00 to 0x00002AC9C7035A00
    
34. 2AC9C7033A00 0000A206 010000B6 000940E1 06010000 [.........@......]
    
35. 2AC9C7033A10 0000138D 00000001 00003ACE 000940AC [.........:...@..]
    
36. 2AC9C7033A20 00000000 00320002 010000B0 00050008 [......2.........]
    
37. 2AC9C7033A30 0000012E 00C007DF 002E0043 00002002 [........C.... ..]
    
38. 2AC9C7033A40 000940E1 00000000 00000000 00000000 [.@..............]
    
39. 2AC9C7033A50 00000000 00000000 00000000 00000000 [................]
    
40. 2AC9C7033A60 00000000 00020100 0016FFFF 1F701F8E [..............p.]
    
41. 2AC9C7033A70 00001F70 1F930002 00001F8E 00000000 [p...............]
    
42. 2AC9C7033A80 00000000 00000000 00000000 00000000 [................]
    
43. Repeat 502 times
    
44. 2AC9C70359F0 012C0000 2C320101 61010101 40E10601 [..,...2,...a...@]
    
45. Block header dump: 0x010000b6
    
46. Object id on Block? Y
    
47. seg/obj: 0x3ace csc: 0x00.940ac itc: 2 flg: E typ: 1 - DATA
    
48. brn: 0 bdba: 0x10000b0 ver: 0x01 opc: 0
    
49. inc: 0 exflg: 0
    
50. Itl Xid Uba Flag Lck Scn/Fsc
    
51. 0x01 0x0008.005.0000012e 0x00c007df.0043.2e --U- 2 fsc 0x0000.000940e1
    
52. 0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
    
53. bdba: 0x010000b6
    
54. data_block_dump,data header at 0x2ac9c7033a64
    
55. ===============
    
56. tsiz: 0x1f98
    
57. hsiz: 0x16
    
58. pbl: 0x2ac9c7033a64
    
59. 76543210
    
60. flag=--------
    
61. ntab=1
    
62. nrow=2
    
63. frre=-1
    
64. fsbo=0x16
    
65. fseo=0x1f8e
    
66. avsp=0x1f70
    
67. tosp=0x1f70
    
68. 0xe:pti[0] nrow=2 offs=0
    
69. 0x12:pri[0] offs=0x1f93
    
70. 0x14:pri[1] offs=0x1f8e
    
71. block_row_dump:                                                                            #表中存放的数据的信息
    
72. tab 0, row 0, @0x1f93
    
73. tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
    
74. col 0: [ 1] 61
    
75. tab 0, row 1, @0x1f8e
    
76. tl: 5 fb: --H-FL-- lb: 0x1 cc: 1
    
77. col 0: [ 1] 32
    
78. end_of_block_dump
    
79. End dump data blocks tsn: 4 file#: 4 minblk 182 maxblk 182

总结：用alter system dump命令可以dump出oracle的文件和块的内容，这对于我们研究和理解oracle的内部结构有很大好处。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28697282/viewspace-1476075/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28697282/viewspace-1476075/