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

  • apporacle

    2015-03-27 15:12:20
  • Oracle
  • 原创
    用alter system dump命令，我们可以dump 出redo logfile，undo 、data block。
    dump出来的文件可以用于研究oracle结构，数据恢复等。
    
alter system dump logfile 示例

     在database nomount/mount/open状态都可以dump logfile

  1. #instance在nomount状态就可以dump logfile
  2. > select instance_name,status from v$instance;              

  4. INSTANCE_NAME STATUS
  5. -------------------------------- ------------------------
  6. test STARTED

  8. #执行dump logfile命令
  9. > alter system dump logfile '/s01/oracle/app/oracle/oradata/TEST/onlinelog/o1_mf_3_bhlp1tqc_.log';  

  11. System altered.

  13. #拥有sysdba权限用户执行
  14. > oradebug setmypid;              
  15. Statement processed.
  16. #找出dump出来的trace文件的名字
  17. > oradebug tracefile_name;       
  18. /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6560.trc
dump生成的trace文件会非常大，因此我们在这里只研究下logfile的头部
通过logfile的dump文件，我们还可以进一步的验证 redo logfile的内部结构。

  1. $ more /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6560.trc
  2. Trace file /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6560.trc
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORACLE_HOME = /s01/oracle/app/oracle/product/11.2.0.4/dbhome_1     #操作系统和实例的一些基本信息
    System name:    Linux
    Node name:      uumile
    Release:        2.6.18-308.el5
    Version:        #1 SMP Fri Jan 27 17:17:51 EST 2012
    Machine:        x86_64
    Instance name: test
    Redo thread mounted by this instance: 0
    Oracle process number: 17
    Unix process pid: 6560, image: oracle@uumile (TNS V1-V3)




    *** 2015-03-27 13:54:57.455                                        #生成trace文件的会话的相关信息
    *** SESSION ID:(1.3) 2015-03-27 13:54:57.455
    *** CLIENT ID:() 2015-03-27 13:54:57.455
    *** SERVICE NAME:() 2015-03-27 13:54:57.455
    *** MODULE NAME:(sqlplus@uumile (TNS V1-V3)) 2015-03-27 13:54:57.455
    *** ACTION NAME:() 2015-03-27 13:54:57.455

  4. Initial buffer sizes: read 1024K, overflow 832K, change 805K
  5. Log read is SYNCHRONOUS though disk_asynch_io is enabled!
  6.  
  7. DUMP OF REDO FROM FILE '/s01/oracle/app/oracle/oradata/TEST/onlinelog/o1_mf_3_bhlp1tqc_.log'   #开始dump的redo logfile
  8.  Opcodes *.*
  9.  RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
  10.  SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
  11.  Times: creation thru eternity
  12.  FILE HEADER:
  13.         Compatibility Vsn = 186647552=0xb200400
  14.         Db ID=2171792810=0x8172edaa, Db Name='TEST'       #dbname和dbid在这里可以看到
  15.         Activation ID=2171749802=0x817245aa
  16.         Control Seq=1782=0x6f6, File size=102400=0x19000
  17.         File Number=3, Blksiz=512, File Type=2 LOG
  18.  descrip:"Thread 0001, Seq# 0000000027, SCN 0x0000000894ab-0x00000009167a"
  19.  thread: 1 nab: 0x5c5a seq: 0x0000001b hws: 0x5 eot: 0 dis: 0
  20.  resetlogs count: 0x3412c92a scn: 0x0000.00000001 (1)
  21.  prev resetlogs count: 0x0 scn: 0x0000.00000000
  22.  Low scn: 0x0000.000894ab (562347) 03/26/2015 08:45:30
  23.  Next scn: 0x0000.0009167a (595578) 03/27/2015 08:40:30
  24.  Enabled scn: 0x0000.00000001 (1) 03/06/2015 15:33:19
  25.  Thread closed scn: 0x0000.00091678 (595576) 03/26/2015 17:37:33
  26.  Disk cksum: 0x3ca2 Calc cksum: 0x3ca2
  27.  Terminal recovery stop scn: 0x0000.00000000
  28.  Terminal recovery 01/01/1988 00:00:00
  29.  Most recent redo scn: 0x0000.00000000
  30.  Largest LWN: 0 blocks
  31.  End-of-redo stream : No
  32.  Unprotected mode
  33.  Miscellaneous flags: 0x800000
  34.  Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
  35.  Zero blocks: 8
  36.  Format ID is 2
  37.  redo log key is 81f88fc52949729eb46b435bd284
  38.  redo log key flag is 5
  39.  Enabled redo threads: 1
  40.  
  41. REDO RECORD - Thread:1 RBA: 0x00001b.00000002.0010 LEN: 0x0070 VLD: 0x05
  42. SCN: 0x0000.000894ad SUBSCN: 1 03/26/2015 08:45:30
  43. (LWN RBA: 0x00001b.00000002.0010 LEN: 0001 NST: 0001 SCN: 0x0000.000894ab)
  44. CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:17.3 ENC:0
  45.  
  46. REDO RECORD - Thread:1 RBA: 0x00001b.00000003.0010 LEN: 0x02a4 VLD: 0x05
  47. SCN: 0x0000.000894ae SUBSCN: 1 03/26/2015 08:45:31
  48. (LWN RBA: 0x00001b.00000003.0010 LEN: 0002 NST: 0001 SCN: 0x0000.000894ae)
  49. CHANGE #1 TYP:0 CLS:15 AFN:1 DBA:0x00400080 OBJ:4294967295 SCN:0x0000.00081688 SEQ:1 OP:5.2 ENC:0 RBL:0
  50. ktudh redo: slt: 0x0041 sqn: 0x00000015 flg: 0x0412 siz: 264 fbi: 0
  51.             uba: 0x00400219.001b.04 pxid: 0x0000.000.00000000
  52. CHANGE #2 TYP:0 CLS:16 AFN:1 DBA:0x00400219 OBJ:4294967295 SCN:0x0000.00081687 SEQ:1 OP:5.1 ENC:0 RBL:0

alter system dump undo header示例


  1. #执行dump undo header命令

  3. > alter system dump undo header '_SYSSMU7_511509616$';
  5. System altered.
  6. #sysdba权限用户执行
  7. > oradebug setmypid;
  8. Statement processed.
  9. #找到当前的trace文件名
  10. > oradebug tracefile_name;
  11. /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6826.trc

#查看dump生成的trace文件内容
  1. $ more /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6826.trc

  3. Trace file /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6826.trc        #操作系统的一些基本信息
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5. 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: 6826, image: oracle@uumile (TNS V1-V3)
  18. *** 2015-03-27 14:29:15.319                                                            #生产trace的会话的信息
  19. *** SESSION ID:(1.13) 2015-03-27 14:29:15.319
  20. *** CLIENT ID:() 2015-03-27 14:29:15.319
  21. *** SERVICE NAME:(SYS$USERS) 2015-03-27 14:29:15.319
  22. *** MODULE NAME:(sqlplus@uumile (TNS V1-V3)) 2015-03-27 14:29:15.319
  23. *** ACTION NAME:() 2015-03-27 14:29:15.319
  26. ********************************************************************************
  27. Undo Segment: _SYSSMU7_511509616$ (7)                                                    #dump的undo extent信息
  28. ********************************************************************************
  29. Extent Control Header
  30. -----------------------------------------------------------------
  31. Extent Header:: spare1: 0 spare2: 0 #extents: 7 #blocks: 55
  32. last map 0x00000000 #maps: 0 offset: 4080
  33. Highwater:: 0x00c0012a ext#: 4 blk#: 2 ext size: 8
  34. #blocks in seg. hdr 's freelists: 0
  35. #blocks below: 0
  36. mapblk 0x00000000 offset: 4
  37. Unlocked
  38. Map Header:: next 0x00000000 #extents: 7 obj#: 0 flag: 0x40000000
  39. Extent Map
  40. -----------------------------------------------------------------
  41. 0x00c000e1 length: 7
  42. 0x00c000e8 length: 8
  43. 0x00c00118 length: 8
  44. 0x00c00120 length: 8
  45. 0x00c00128 length: 8
  46. 0x00c00158 length: 8
  47. 0x00c00160 length: 8
  49. Retention Table
  50. -----------------------------------------------------------
  51. Extent Number:0 Commit Time: 1427432432
  52. Extent Number:1 Commit Time: 1427432432
  53. Extent Number:2 Commit Time: 1427432432
  54. Extent Number:3 Commit Time: 1427432432
  55. Extent Number:4 Commit Time: 1427432432
  56. Extent Number:5 Commit Time: 1427432432
  57. Extent Number:6 Commit Time: 1427432432
  59. TRN CTL:: seq: 0x006b chd: 0x0005 ctl: 0x001d inc: 0x00000000 nfb: 0x0001
  60. mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
  61. uba: 0x00c00128.006b.20 scn: 0x0000.00092e5a
  62. Version: 0x01
  63. FREE BLOCK POOL::
  64. uba: 0x00000000.006b.1f ext: 0x4 spc: 0x1038
  65. uba: 0x00c0012a.006b.03 ext: 0x4 spc: 0x1814
  66. uba: 0x00000000.0065.24 ext: 0x4 spc: 0x6f6
  67. uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
  68. uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
  69. TRN TBL::
  71. index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
  72. ------------------------------------------------------------------------------------------------
  73. 0x00 9 0x00 0x0125 0x001a 0x0000.000931db 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427432432
  74. 0x01 9 0x00 0x0126 0x001e 0x0000.00093611 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427435117
  75. 0x02 9 0x00 0x0126 0x000d 0x0000.00093350 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  76. 0x03 9 0x00 0x0125 0x000c 0x0000.000931f9 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427432432
  77. 0x04 9 0x00 0x0126 0x0020 0x0000.000931e7 0x00c00121 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  78. 0x05 9 0x00 0x0124 0x0008 0x0000.00092e64 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427430031
  79. 0x06 9 0x00 0x0126 0x0007 0x0000.000931e0 0x00c000e8 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  80. 0x07 9 0x00 0x0125 0x0019 0x0000.000931e1 0x00c000eb 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  81. 0x08 9 0x00 0x0123 0x0018 0x0000.0009300a 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427431231
  82. 0x09 9 0x00 0x0125 0x0016 0x0000.000931e5 0x00c0011b 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  83. 0x0a 9 0x00 0x0125 0x0001 0x0000.00093537 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427434532
  84. 0x0b 9 0x00 0x0124 0x0006 0x0000.000931df 0x00c000e5 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  85. 0x0c 9 0x00 0x0125 0x0011 0x0000.0009333c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  86. 0x0d 9 0x00 0x0125 0x000f 0x0000.0009335a 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  87. 0x0e 9 0x00 0x0125 0x000b 0x0000.000931de 0x00c000e2 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  88. 0x0f 9 0x00 0x0125 0x0012 0x0000.00093365 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  89. 0x10 9 0x00 0x0125 0x0000 0x0000.000931da 0x00c0015e 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  90. 0x11 9 0x00 0x0125 0x0002 0x0000.00093346 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  91. 0x12 9 0x00 0x0126 0x001b 0x0000.0009336f 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  92. 0x13 9 0x00 0x0125 0x000a 0x0000.0009352d 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427434532
  93. 0x14 9 0x00 0x0124 0x0013 0x0000.00093454 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433933
  94. 0x15 9 0x00 0x0126 0x001d 0x0000.0009364a 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427437278
  95. 0x16 9 0x00 0x0124 0x0004 0x0000.000931e6 0x00c0011e 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  96. 0x17 9 0x00 0x0125 0x0021 0x0000.000931e9 0x00c00128 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  97. 0x18 9 0x00 0x0124 0x0010 0x0000.000931b6 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427432432
  98. 0x19 9 0x00 0x0124 0x001f 0x0000.000931e2 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427432432
  99. 0x1a 9 0x00 0x0125 0x000e 0x0000.000931dd 0x00c00160 0x0000.000.00000000 0x00000001 0x00000000 1427432432
  100. 0x1b 9 0x00 0x0126 0x0014 0x0000.00093379 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
  101. 0x1c 10 0x80 0x0125 0x0004 0x0000.0009369a 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 0
  102. 0x1d 9 0x00 0x0125 0xffff 0x0000.0009365c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427437278
  103. 0x1e 9 0x00 0x0125 0x0015 0x0000.0009361c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427435118
  104. 0x1f 9 0x00 0x0124 0x0009 0x0000.000931e4 0x00c000ef 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  105. 0x20 9 0x00 0x0125 0x0017 0x0000.000931e8 0x00c00124 0x0000.000.00000000 0x00000003 0x00000000 1427432432
  106. 0x21 9 0x00 0x0125 0x0003 0x0000.000931ea 0x00c0012a 0x0000.000.00000000 0x0000000d 0x00000000 1427432432
  107. EXT TRN CTL::
  108. usn: 7
  109. sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
  110. sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
  111. EXT TRN TBL::
  112. index extflag extHash extSpare1 extSpare2
  113. ---------------------------------------------------
  114. 0x00 0x00000000 0x00000000 0x00000000 0x00000000
  115. 0x01 0x00000000 0x00000000 0x00000000 0x00000000
  116. 0x02 0x00000000 0x00000000 0x00000000 0x00000000
  117. 0x03 0x00000000 0x00000000 0x00000000 0x00000000
  118. 0x04 0x00000000 0x00000000 0x00000000 0x00000000
  119. 0x05 0x00000000 0x00000000 0x00000000 0x00000000
  120. 0x06 0x00000000 0x00000000 0x00000000 0x00000000
  121. 0x07 0x00000000 0x00000000 0x00000000 0x00000000
  122. 0x08 0x00000000 0x00000000 0x00000000 0x00000000
  123. 0x09 0x00000000 0x00000000 0x00000000 0x00000000
  124. 0x0a 0x00000000 0x00000000 0x00000000 0x00000000
请使用浏览器的分享功能分享到微信等