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

作者：盐析白兔 | 2024-04-13 09:58:20

踩

alter system dump undo

用alter system dump命令，我们可以dump 出redo logfile，undo 、data block。
dump出来的文件可以用于研究oracle结构，数据恢复等。

alter system dump logfile 示例

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

#instance在nomount状态就可以dump logfile
> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------- ------------------------
test STARTED
#执行dump logfile命令
> alter system dump logfile '/s01/oracle/app/oracle/oradata/TEST/onlinelog/o1_mf_3_bhlp1tqc_.log';
System altered.
#拥有sysdba权限用户执行
> oradebug setmypid;
Statement processed.
#找出dump出来的trace文件的名字
> oradebug tracefile_name;
/s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6560.trc

dump生成的trace文件会非常大，因此我们在这里只研究下logfile的头部
通过logfile的dump文件，我们还可以进一步的验证 redo logfile的内部结构。

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

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

#查看dump生成的trace文件内容

$ more /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6826.trc
Trace file /s01/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_6826.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: 1
Oracle process number: 18
Unix process pid: 6826, image: oracle@uumile (TNS V1-V3)
*** 2015-03-27 14:29:15.319 #生产trace的会话的信息
*** SESSION ID:(1.13) 2015-03-27 14:29:15.319
*** CLIENT ID:() 2015-03-27 14:29:15.319
*** SERVICE NAME:(SYS$USERS) 2015-03-27 14:29:15.319
*** MODULE NAME:(sqlplus@uumile (TNS V1-V3)) 2015-03-27 14:29:15.319
*** ACTION NAME:() 2015-03-27 14:29:15.319
********************************************************************************
Undo Segment: _SYSSMU7_511509616$ (7) #dump的undo extent信息
********************************************************************************
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 7 #blocks: 55
last map 0x00000000 #maps: 0 offset: 4080
Highwater:: 0x00c0012a ext#: 4 blk#: 2 ext size: 8
#blocks in seg. hdr 's freelists: 0
#blocks below: 0
mapblk 0x00000000 offset: 4
Unlocked
Map Header:: next 0x00000000 #extents: 7 obj#: 0 flag: 0x40000000
Extent Map
-----------------------------------------------------------------
0x00c000e1 length: 7
0x00c000e8 length: 8
0x00c00118 length: 8
0x00c00120 length: 8
0x00c00128 length: 8
0x00c00158 length: 8
0x00c00160 length: 8
Retention Table
-----------------------------------------------------------
Extent Number:0 Commit Time: 1427432432
Extent Number:1 Commit Time: 1427432432
Extent Number:2 Commit Time: 1427432432
Extent Number:3 Commit Time: 1427432432
Extent Number:4 Commit Time: 1427432432
Extent Number:5 Commit Time: 1427432432
Extent Number:6 Commit Time: 1427432432
TRN CTL:: seq: 0x006b chd: 0x0005 ctl: 0x001d inc: 0x00000000 nfb: 0x0001
mgc: 0xb000 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x00c00128.006b.20 scn: 0x0000.00092e5a
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.006b.1f ext: 0x4 spc: 0x1038
uba: 0x00c0012a.006b.03 ext: 0x4 spc: 0x1814
uba: 0x00000000.0065.24 ext: 0x4 spc: 0x6f6
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x0125 0x001a 0x0000.000931db 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427432432
0x01 9 0x00 0x0126 0x001e 0x0000.00093611 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427435117
0x02 9 0x00 0x0126 0x000d 0x0000.00093350 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x03 9 0x00 0x0125 0x000c 0x0000.000931f9 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427432432
0x04 9 0x00 0x0126 0x0020 0x0000.000931e7 0x00c00121 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x05 9 0x00 0x0124 0x0008 0x0000.00092e64 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427430031
0x06 9 0x00 0x0126 0x0007 0x0000.000931e0 0x00c000e8 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x07 9 0x00 0x0125 0x0019 0x0000.000931e1 0x00c000eb 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x08 9 0x00 0x0123 0x0018 0x0000.0009300a 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427431231
0x09 9 0x00 0x0125 0x0016 0x0000.000931e5 0x00c0011b 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x0a 9 0x00 0x0125 0x0001 0x0000.00093537 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427434532
0x0b 9 0x00 0x0124 0x0006 0x0000.000931df 0x00c000e5 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x0c 9 0x00 0x0125 0x0011 0x0000.0009333c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x0d 9 0x00 0x0125 0x000f 0x0000.0009335a 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x0e 9 0x00 0x0125 0x000b 0x0000.000931de 0x00c000e2 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x0f 9 0x00 0x0125 0x0012 0x0000.00093365 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x10 9 0x00 0x0125 0x0000 0x0000.000931da 0x00c0015e 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x11 9 0x00 0x0125 0x0002 0x0000.00093346 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x12 9 0x00 0x0126 0x001b 0x0000.0009336f 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x13 9 0x00 0x0125 0x000a 0x0000.0009352d 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427434532
0x14 9 0x00 0x0124 0x0013 0x0000.00093454 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433933
0x15 9 0x00 0x0126 0x001d 0x0000.0009364a 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427437278
0x16 9 0x00 0x0124 0x0004 0x0000.000931e6 0x00c0011e 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x17 9 0x00 0x0125 0x0021 0x0000.000931e9 0x00c00128 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x18 9 0x00 0x0124 0x0010 0x0000.000931b6 0x00c0015b 0x0000.000.00000000 0x00000001 0x00000000 1427432432
0x19 9 0x00 0x0124 0x001f 0x0000.000931e2 0x00000000 0x0000.000.00000000 0x00000000 0x00000000 1427432432
0x1a 9 0x00 0x0125 0x000e 0x0000.000931dd 0x00c00160 0x0000.000.00000000 0x00000001 0x00000000 1427432432
0x1b 9 0x00 0x0126 0x0014 0x0000.00093379 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427433333
0x1c 10 0x80 0x0125 0x0004 0x0000.0009369a 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 0
0x1d 9 0x00 0x0125 0xffff 0x0000.0009365c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427437278
0x1e 9 0x00 0x0125 0x0015 0x0000.0009361c 0x00c00128 0x0000.000.00000000 0x00000001 0x00000000 1427435118
0x1f 9 0x00 0x0124 0x0009 0x0000.000931e4 0x00c000ef 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x20 9 0x00 0x0125 0x0017 0x0000.000931e8 0x00c00124 0x0000.000.00000000 0x00000003 0x00000000 1427432432
0x21 9 0x00 0x0125 0x0003 0x0000.000931ea 0x00c0012a 0x0000.000.00000000 0x0000000d 0x00000000 1427432432
EXT TRN CTL::
usn: 7
sp1:0x00000000 sp2:0x00000000 sp3:0x00000000 sp4:0x00000000
sp5:0x00000000 sp6:0x00000000 sp7:0x00000000 sp8:0x00000000
EXT TRN TBL::
index extflag extHash extSpare1 extSpare2
---------------------------------------------------
0x00 0x00000000 0x00000000 0x00000000 0x00000000
0x01 0x00000000 0x00000000 0x00000000 0x00000000
0x02 0x00000000 0x00000000 0x00000000 0x00000000
0x03 0x00000000 0x00000000 0x00000000 0x00000000
0x04 0x00000000 0x00000000 0x00000000 0x00000000
0x05 0x00000000 0x00000000 0x00000000 0x00000000
0x06 0x00000000 0x00000000 0x00000000 0x00000000
0x07 0x00000000 0x00000000 0x00000000 0x00000000
0x08 0x00000000 0x00000000 0x00000000 0x00000000
0x09 0x00000000 0x00000000 0x00000000 0x00000000
0x0a 0x00000000 0x00000000 0x00000000 0x00000000

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

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/盐析白兔/article/detail/415761