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

  • apporacle

    2015-03-27 16:28:33
  • Oracle
  • 原创
        alter system dump datafile block示例

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

  1. > select * from tt;

  3. A
  4. ----------
  5. a
  6. 2

  8. > select dbms_rowid.rowid_block_number(rowid) bl,dbms_rowid.rowid_relative_fno(rowid) fno from tt;


  11.         BL FNO
  12. ---------- ----------
  13.        182 4
  14.        182 4
执行dump命令

  1. > alter system dump datafile 4 block 182;

  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

  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的内部结构有很大好处。







请使用浏览器的分享功能分享到微信等