Oracle认证Oracle的dump命令与dump函数

会计考友

Oracle认证Oracle的dump命令与dump函数
7 }, z1 C0 ]$ H2 P3 F) F5 h一.Memory Dumps
3 d* f( ^" V: }: c1）.Global Area
; i6 N+ r, |% l6 p$ H* W8 zALTER SESSION SET EVENTS ‘immediate trace name global_area level n’;
1 包含PGA
! e% h" [1 x" C+ `+ M2 包含SGA
4 包含UGA
8 包含indrect memory
. E1 ]) V6 w) y' i+ O& K2）.Library Cache
# s+ f2 z$ L3 d# ~. SALTER SESSION SET EVENTS ‘immediate trace name library_cache level n’;
1 library cache统计信息
2 包含hash table histogram
# N$ G8 j3 t5 h, x7 I' s* N3 包含object handle
4 包含object结构（Heap 0）
3）.Row Cache
ALTER SESSION SET EVENTS ‘immediate trace name row_cache level n’;
1 row cache统计信息
2 包含hash table histogram
8 包含object结构
4）.Buffers
ALTER SESSION SET EVENTS ‘immediate trace name buffers level n’;
+ b- m+ a  a! O3 U, N$ t/ {7 w1 buffer header
$ T# g/ f! E4 u0 v1 p2 Y" f; Y5 c/ c8 t2 level 1 + block header
3 level 2 + block contents
/ K  Z# y) |+ u4 level 1 + hash chain
5 level 2 + hash chain
6 level 3 + hash chain
/ C) C7 ~- g9 D& a( R/ F0 K8 level 4 + users/waiters
9 level 5 + users/waiters
4 s1 a/ S# I2 Y: ?3 H8 E10 level 6 + users/waiters
" n0 b2 E% a" L, n5）.Buffer
. ]3 {+ Q( p. R: AALTER SESSION SET EVENTS ‘immediate trace name buffer level n’;
; y+ u$ y, l# f6 {n为某个指定block的rdba，该命令可以转储某个block在buffer中的所有版本。
6）.Heap
ALTER SESSION SET EVENTS ‘immediate trace name heapdump level level’;
  T( D0 b% T4 K6 D3 U# t! U7 R1 PGA摘要
2 SGA摘要
. n6 n! }$ ^5 v  {2 t( v4 UGA摘要
' I+ C4 P& n  P/ u/ M8 Current call（CGA）摘要
16 User call（CGA）摘要
32 Large call（LGA）摘要
, b( T6 d" L) L
  ~& H; X# T$ @$ t' [
" ]9 R- \2 Q( s! c3 ]7 @. i. f1025 PGA内容
2050 SGA内容
4100 UGA内容
5 ]' ^; \; O' ^1 g+ n8200 Current call内容
4 @1 U# V, d# Q$ Q$ S16400 User call内容
3 S# u9 F; G* j32800 Large call内容
7）.Sub Heap
, n4 M# a8 ^0 Z+ ^Oracle 9.0.1版本之前
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n’;
% d2 f6 \9 \% d+ n# @9 x: l若n为subheap的地址，转储的是subheap的摘要信息
, h" Q: U$ m- E& W若n为subheap的地址+1，转储的则是subheap的内容
Oracle 9.2.0版本之后
ALTER SESSION SET EVENTS ‘immediate trace name heapdump_addr level n， addr m’;
4 e# v3 L! |6 N& p) H  _其中m为subheap的地址
n为1转储subheap的摘要，n为2转储subheap的内容
8）.Process State
4 a+ {' I1 d( S7 k% f+ A" g/ oALTER SESSION SET EVENTS ‘immediate trace name processstate level n’;
9）.System State
, o/ }( l( `% f% m/ I6 k+ CALTER SESSION SET EVENTS ‘immediate trace name systemstate level n’;
10）.Error State
ALTER SESSION SET EVENTS ‘immediate trace name errorstack level n’;
7 N1 m& i7 h; ?0 w0 Error stack
. |8 \4 y. Y2 [1 level 0 + function call stack
2 level 1 + process state
* t% [- C0 P9 A3 level 2 + context area
% h" @* C- `. W. k: V: S& f. g- B11）.Hang Analysis
3 ]2 V- H. m, A, k) _& s- [ALTER SESSION SET EVENTS ‘immediate trace name hanganalyze level n’;
* V7 L/ l9 {  G- ]! ]: D' A12）.Work Area
ALTER SESSION SET EVENTS ‘immediate trace name workareatab_dump level n’
1 SGA信息
$ x/ p$ ^2 ^2 O) b7 m: b: q2 Workarea Table摘要信息
3 Workarea Table详细信息
1 ?: C7 @) n: t( M13）.Latches
ALTER SESSION SET EVENTS ‘immediate trace name latches level n’;
- _6 H) {, o$ l. v# g% l1 latch信息
2 统计信息
14）.Events
1 s" N$ A* L$ w  F4 j2 V" JALTER SESSION SET EVENTS ‘immediate trace name events level n’;
1 session
2 process
3 system
7 n5 m2 V' ?" q/ M; P2 n' j* ?15）.Locks
ALTER SESSION SET EVENTS ‘immediate trace name locks level n’;
16）.Shared Server Process
$ M5 f: T. r/ ]0 l- q/ n0 ]1 T2 k; y) pALTER SESSION SET EVENTS ‘immediate trace name shared_server_state level n’;
9 B3 `8 x; }7 u' wn取值为1~14
17）.Background Messages
ALTER SESSION SET EVENTS ‘immediate trace name bg_messages level n’;
( P! w1 c+ h6 qn为pid+1
二.File Dumps
9 }! i) e5 R( Q6 w, a5 S+ Q1）.Block
Oracle 7之前
ALTER SESSION SET EVENTS ‘immediate trace name blockdump level n’;
n为block的rdba
Oracle8以后
; Q4 X3 z) H$ b! w* @: yALTER SYSTEM DUMP DATAFILE file# BLOCK block#;
: [& m- m( z1 k$ j9 J6 B7 _ALTER SYSTEM DUMP DATAFILE file#
' v5 V" P/ ^* ]& e$ v; v/ R2 H1 FBLOCK MIN minimum_block#
BLOCK MAX maximum_block#;
1 n5 D4 _7 T8 j" f6 o% C0 d2）.Tree Dump
ALTER SESSION SET EVENTS ‘immediate trace name treedump level n’;
n为object_id
3）.Undo Segment Header
; U& L* |" r% `- Y# q& g; L7 @7 _/ RALTER SYSTEM DUMP UNDO_HEADER ’segment_name’;
' L% H0 b$ o6 r: z3 J* B& H1 d4）.Undo for a Transaction
ALTER SYSTEM DUMP UNDO BLOCK ’segment_name’ XID xidusn xidslot xidsqn;
# Q+ ]8 s  S8 L& E6 ]5）.File Header
. Y9 s. V! \; l, n; p0 TALTER SESSION SET EVENTS ‘immediate trace name file_hdrs level n’;
1 控制文件中的文件头信息
2 level 1 + 文件头信息
3 level 2 + 数据文件头信息
1 E/ f% {/ U! V* Q5 i& [. F5 z10 level 3

  x, e4 e5 f/ d& ^/ Q0 y
6）.Control file
ALTER SESSION SET EVENTS ‘immediate trace name controlf level n’;
1 文件头信息
1 j6 i9 c- a8 Z! s( P, q  O3 ~6 S( i2 level 1 + 数据库信息 + 检查点信息
" U9 |$ b4 t" K( O3 level 2 + 可重用节信息
10 level 3
* l& p% T! @/ V0 ~; O! r( N4 [2 T4 o7）.Redo log Header
ALTER SESSION SET EVENTS ‘immediate trace name redohdr level n’;
1 控制文件中的redo log信息
2 level 1 + 文件头信息
3 level 2 + 日志文件头信息
10 level 3
* \4 U) l" r4 P# S8）.Redo log
, Y$ t+ t5 |1 {+ x9 x3 |% KALTER SYSTEM DUMP LOGFILE ‘FileName’;
ALTER SYSTEM DUMP LOGFILE ‘FileName’
SCN MIN MinimumSCN
SCN MAX MaximumSCN
: R, x9 g* t8 kTIME MIN MinimumTime
6 Y! ]7 e6 t0 Y4 o' NTIME MAX MaximumTime
5 |/ `, t& x! ~, W5 A8 }* `LAYER Layer
OPCODE Opcode
5 G6 q5 q2 W) L* z3 D  X+ |" \DBA MIN FileNumber 。 BlockNumber
( T( ^+ O8 D2 y4 \' R" `DBA MAX FileNumber 。 BlockNumber
RBA MIN LogFileSequenceNumber 。 BlockNumber
- K% J- r* N1 P9 v2 K: ORBA MAX LogFileSequenceNumber 。 BlockNumber;
其中time = （（（（（yyyy – 1988）） * 12 + mm – 1） * 31 + dd – 1） * 24 + hh） * 60 + mi） * 60 + ss;
9）.Loghist
  R% @( ^" F0 F, }4 NALTER SESSION SET EVENTS ‘immediate trace name loghist level n’;
1 dump控制文件中最早和最迟的日志历史项
% X8 w+ r, I2 P1 _) \) Q+ W1 dump 2^n个日志历史项