dumps
immediate dumps
conditional dumps
memory dumps
file dumps
immediate dumps
there are three ways of specifying an immediate dump
immediate dumps can be specified using the alter session command
alter session set events
'immediate trace name dump levellevel';
immediate dumps can be specified in oradebug
oradebug dump dump level
immediate dumps can also be specified using the dbms_system.set_ev procedure
execute dbms_system.set_ev (sid,serial#,65535,level,'dump');
immediate dumps cannot be invoked from init.ora
conditional dumps
conditional dumps are invoked when an error occurs
conditional dump can be specified in the init.ora file e.g
event=error trace name dump levellevel
conditional dumps can be specified in the current session
alter session set events
'error trace name dump levellevel';
conditional dumps can be specified in oradebug
oradebug event error trace namedump level level
oradebug session_event error trace namedump level level
memory dumpsdumping the global area
the following command can be used to dump the global area
alter session set events 'immediate trace name global_area levellevel';
levels are (bitmask)
level
description
1
include pga
2
include sga
4
include uga
8
include indirect memory dumps
the following oradebug command has the same effect
oradebug dump global_area level
the structure of the fixed sga is externalised in x$ksmfsv
name
type
description
addr
raw(4)
indx
number
inst_id
number
ksmfsnam
varchar2(64)
name
ksmfstyp
varchar2(64)
type
ksmfsadr
raw(4)
address
ksmfssiz
number
size in bytes
this can be queried using
select
substr (ksmfsnam,1,20) as name,
substr (ksmfstyp,1,20) as type,
ksmfsadr as address,
ksmfssiz as size
from x$ksmfsv;
dumping the library cache
the following command can be used to dump the library cache
alter session set events 'immediate trace name library_cache levellevel';
where level is one of the following
level
description
1
dump library cache statistics
2
include hash table histogram
3
include dump of object handles
4
include dump of object structures (heap 0)
for example
alter session set events 'immediate trace name library_cache level 1';
the following oradebug command has the same effect
oradebug dump library_cache level
dumping the row (dictionary) cache
row_cachethe row cache is also known as the dictionary cache
the following command can be used to dump the row cache
alter session set events 'immediate trace name row_cache level level';
where level is one of the following
level
description
1
dump row cache statistics
2
include hash table histogram
8
include dump of object structures
for example
alter session set events 'immediate trace name row_cache level 1';
the following oradebug command has the same effect
oradebug dump row_cache level
dumping multiple buffers
multiple buffersto dump buffer headers and buffer contents for buffers currently in the cache
alter session set events 'immediate trace name buffers level level';
where level is one of the following
level
description
1
buffer headers only
2
level 1 + block headers
3
level 2 + block contents
4
buffer headers only + hash chain
5
level 1 + block headers + hash chain
6
level 2 + block contents + hash chain
8
buffer headers only + hash chain + users/waiters
9
level 1 + block headers + hash chain + users/waiters
10
level 2 + block contents + hash chain + users/waiters
dumping individual buffers
individual buffersin oracle 8.0 and above is is possible to dump buffer all buffers currently in the cache for a specific block
for example where a block has been modified and is subject to consistent read from a number of transactions, there may be more than one copy of the block in the buffer cache
first identify（确定，定位，指定） the tablespace number for the block e.g for tablespace ts01
select ts# from sys.ts$
where name = 'ts01';
set the tablespace number using
alter session set events
'immediate trace name set_tsn_p1 level level'; //
where level is the tablespace number + 1
identify （确定，定位，指定）the relative dba for the block
this is equal to
relativefilenumber * 4194304 + blocknumber
e.g. for a block with relative file number of 5 and a block number of 127874
5 * 4194304 + 127874 = 21099394
dump the buffer using
alter session set events
'immediate trace name buffer level level';
where level is therelative dba e.g.
alter session set events
'immediate trace name buffer level 21099394';注释： alter session set events
'immediate trace name set_tsn_p1 level level'; 和alter session set events
'immediate trace name buffer level 21099394';
这两句话要联合起来使用，才能把一个指定的数据块的内容给转存出来。
也就是说，dump（转存）一个指定的数据块的内容时，要将该数据块所在的表空间的表空间号提供给负责执行dump（转存）的进程（通过使用第一句话），之后，负责执行dump（转存）的进程还得知道该数据块的rdba是多少（通过使用第二句话），这样，负责执行dump（转存）的进程就可以锁定（或说找到）该数据块的具体位置从而将其内容转存出来。这里，说明一点，rdba里使用的是相对文件号（relativefilenumber），相对文件号（relativefilenumber）是给在一个表空间范围里的文件进行的编号，dba里使用的是绝对文件号（absolutefilenumber，简称filenumber），绝对文件号（absolutefilenumber，简称filenumber）是给在一个数据库范围里的文件进行的编号。
dumping memory heaps
memory heapsto dump the top-level heap in a memory area
alter session set events
'immediate trace name heapdump level level';
levels are
level
description
1
pga summary
2
sga summary
4
uga summary
8
callheap (current)
16
callheap (user)
32
large pool
64
streams pool
128
java pool
1025
pga with contents
2050
sga with contents
4100
uga with contents
8200
callheap with contents (current)
16400
callheap with contents (user)
32800
large pool with contents
65600
streams pool with contents
131200
java pool with contents
levels are correct to oracle 10.2.0.1
the levels can be combined. for example a level 3 dump will contain both the pga and sga
dumping memory subheaps
memory subheapseach heap may have one or more subheaps. these can be dumped using the command
alter session set events
'immediate trace name heapdump_addr level level';
where the level is the address of the subheap.
the syntax for this command changed in oracle 9.2
oracle 9.0.1 and below
oracle 9.2.0 and above
oracle 9.0.1 and belowfor oracle 9.0.1 and below a summary dump is obtained by setting the level to the decimal value of the address. a detailed dump is obtained by adding 1 to the decimal value of the address.
for example to dump the subheap at address 0x8057eb78, first convert the address to decimal (2153245560)
the subheap address can be found in the heapdump, for example
ds=0x8057eb78
for a summary dump use
alter session set events
'immediate trace name heapdump_addr level 2153245560';
for a detailed dump, add 1 to the address e.g.
alter session set events
'immediate trace name heapdump_addr level 2153245561';
oracle 9.2.0 and abovein oracle 9.2 and above, for a summary dump
alter session set events
'immediate trace name heapdump_addr level 1, addr 2153245560';
the following oradebug command has the same effect
oradebug dump heapdump_addr 1 2153245560
in oracle 9.2 and above, for a detailed dump
alter session set events
'immediate trace name heapdump_addr level 2, addr 2153245560';
note that in oracle 9.2 it is no longer necessary to add 1 to the address
the following oradebug command has the same effect
oradebug dump heapdump_addr 2 2153245560
dumping process state
process stateto dump the process state use:
alter session set events
'immediate trace name processstate level level';
for example
alter session set events 'immediate trace name processstate level 10';
the following oradebug command has the same effect
oradebug dump processstate level
the amount of library cache dump output for state object dumps can be limited using event 10065
dumping system state
system statea system state dump contains the process state for every process.
every state object for every process is dumped.
a state object represents the state of a database resource including
processes sessions enqueues (locks) buffers state objects are held in the sga
a system state dump does not represent a snapshot of the instance because the database is not frozen for the duration of the dump. the start point of the dump will be earlier than the end point.
oracle recommends the use of system state dumps to diagnose
hanging databases slow databases database errors waiting processes blocking processes resource contention to dump the system state use
alter session set events
'immediate trace name systemstate level level';
for example
alter session set events 'immediate trace name systemstate level 10';
the following oradebug command has the same effect
oradebug dump systemstate level
a system state dump can be triggered by an error, for example the following init.ora parameter
event = 60 trace name systemstate level 10
will generate a systemstate dump whenever a deadlock is detected (ora-00060)
the amount of library cache dump output for state object dumps can be limited using event 10065
dumping the error stack
error stackan error stack describes the current state of a process. it includes the current sql statement and the process state for the process.
oracle recommends taking an error stack dump to diagnose
what the process is doing a problem identified by a systemstate dump processes looping or hanging error stack dumps can also be triggered by an error
to dump an error stack use
alter session set events
'immediate trace name errorstack level level';
where level is one of the following
level
description
0
error stack only
1
error stack and function call stack
2
as level 1 plus the process state
3
as level 2 plus the context area
the following oradebug command has the same effect
oradebug dump errorstack level
an alternative syntax for the same command is
oradebug event immediate trace name errorstack level
to dump a level 3 errorstack when ora-00942 (table or view does not exist) use
alter session set events
'942 trace name errorstack level 3';
a conditional errorstack dump can also be specified without a level e.g.
alter session set events
'604 trace name errorstack';
alternatively a conditional errorstack dump can be specified in the init.ora file
event = 942 trace name errorstack level 3
errorstacks can also be dumped conditionally using oradebug
oradebug event 942 trace name errorstack level 3
a level 3 errorstack contains the following divs
call stack trace
files currently opened
process state
pinned buffer history
cursor dump
fixed pga
fixed sga
fixed uga
in memory trace dump
dumping hang analysis
hang analysisthis dump is available in oracle 8.0.6 and oracle 8.1.6 and above. it is not available in oracle 8.1.5.
it contains
systemstate level 1 dump processes currently waiting, blocking or spinningerrorstacks the hanganalyze dump is more selective than a systemstate dump in that it only includes details of processes of interest. it is particularly intended for situations where a database is hanging.
for example
alter session set events
'immediate trace name hanganalyze level 5';
the following oradebug command has the same effect
oradebug dump hanganalyze level
e.g.
oradebug dump hanganalyze 5
a hanganalyze dump can also be generated on an error e.g. when a deadlock is detected (ora-00060) using the init.ora file
event = 60 trace name hanganalyze level 5
or in the current session using
alter session set events
'60 trace name hanganalyze level 5';
dumping work areas
work areasto dump the current workareas use
alter session set events 'immediate trace name workareatab_dump level level';
levels are (bitmask)
level
description
1
global sga info
2
workarea table (summary)
3
workarea table (detail)
the following oradebug command has the same effect
oradebug dump workareatab_dump level
dumping enqueues
enqueuesto dump the current enqueue states use
alter session set events 'immediate trace name enqueues level level';
levels are
level
description
1
dump a summary of active resources and locks, the resource free list and the hash table
2
include a dump of resource structures
3
include a dump of lock structures
the following oradebug command has the same effect
oradebug dump enqueues level
dumping latches
latchesto dump the current latch status use
alter session set events 'immediate trace name latches level level';
levels are
level
description
1
latches
2
include statistics
the following oradebug command has the same effect
oradebug dump latches level
dumping events
eventsto dump the events set for the current session
alter session set events 'immediate trace name events level level';
levels are
level
description
1
session
2
process
4
system
the following oradebug command has the same effect
oradebug dump events level
dumping shared server (mts) processes
shared server (mts) processesto dump buffer headers and buffer contents for buffers currently in the cache
alter session set events 'immediate trace name shared_server_state level level';
where level is in the range 1 to 14
the following oradebug command has the same effect
oradebug dump shared_server_state level
in oracle 8.1.7 and below, this dump is called mtsstate
dumping background messages
background process messages can be traced using
alter session set events
'immediate trace name bg_messages level level';
where level identifies the background process (+1)
for example to find the level of smon, first identify the index number using the following query
select indx + 1 from x$ksbdp
where ksbdpnam = 'smon';
the following oradebug command has the same effect
oradebug dump bg_messages level
file dumps
dumping columns
columnsto dump the internal representation of columns use the dump built-in function
dump (column_value, format)
where format is
format
description
8
octal
10
decimal
16
hexadecimal
17
single character
for example
select dump (1001,16) from dual;
returns
typ=2 len=3: c2,b,2
to output a column in hexadecimal use the 'xxxxxxxx' format mask e.g.
select to_char (65536,'xxxxxxxx') from dual;
returns
10000
dumping database blocks
database blocksthe syntax used for dumping database blocks changed when oracle8 was introduced
oracle7 and below
oracle8 and above
oracle 7 and belowin oracle7, blocks are identified by a file number and a block number. these must be converted into a data block address. the block can then be dumped using the alter session command.
column decimaldba new_value decimaldba
select
dbms_utility.make_data_block_address (&file,&block) decimaldba
from dual;
alter session set events
'immediate trace name blockdump level &decimaldba';
oracle 8 and abovein oracle8 and above, blocks are uniquely identified by an absolute file number and a block number. the syntax of the alter system command has been extended to include block dumps
to dump a block
alter system dump datafile absolute_file_number
block block_number;
to dump a range of blocks
alter system dump datafile absolute_file_number
block min minimum_block_number
block max maximum_block_number;
the datafile clause can specify an absolute file number of a datafile name. if the datafile clause specifies a datafile name, the blocks can also be dumped from a closed database e.g.
alter system dump datafile 'file_name'
block block_number;
normally a symbolic block dump is output. however, this may not be possible if the block has become corrupt. it is possible to output the block dump in hexadecimal.
to dump a block in hexadecimal, enable event 10289
alter session set events
'10289 trace name context forever, level 1';
dump the block(s) using one of the above commands and then disable 10289 again using
alter session set events
'10289 trace name context off';
on unix systems blocks can also be dumped using theod utility.
dd bs=8k if=filename skip=200 count=4 | od -x
where
bs is the oracle block size e.g. 8k if is the datafile name skip is the number of blocks to skip from the start of the filecount is the number of blocks to dump as blocks are written back to the datafiles asynchronously by dbwr, it is possible that changed blocks have not been written back to the disk when they are dumped using operating system utilities.
the probability that a block has been written back to disk can be increased by performing a checkpoint using
alter system checkpoint;
or a logfile switch using
alter system switch logfile;
dumping indexes
index tree dumpsan index tree can be dumped using
alter session set events 'immediate trace name treedump level object_id';
where object_id is the object number of the index (in dba_objects)
the tree dump includes
branch block headers leaf block headers contents of leaf blocks the following oradebug command has the same effect
oradebug dump treedump object_id
in oracle 9.2 a treedump may crash if the index has been created by a primary / unique constraint e.g.
create table t1 (c1 number primary key);
or
create table t1 (c1 number);
alter table t1
add constraint t1pk primary key (c1);
the treedump crashes when ind$.property > 256
this problem can be prevented by creating the index before creating the constraint
dumping undo segment headers
undo headersto dump an undo segment header use the command function
alter system dump undo_header 'segment_name';
in oracle 9.0.1 and above, if system managed undo is enabled, the segment name has to be in double quotes and upper case. this is because there is a leading underscore in the segment name.
a list of undo segment ids and names can be obtained using
select segment_id, segment_name
from dba_rollback_segs
order by segment_id;
dumping undo blocks
undo blocksto dump an undo block use the equivalent datafile block dump command
for example to dump an undo block in oracle8 and above use
alter system dump databaseabsolute_file_number block block_number;
dumping undo for a transaction
undo for a transactionto dump all the undo written for a specific transaction, first identify the transaction id using
select xidusn, xidslot, xidsqn
from v$transaction;
dump the undo using the transaction id
alter system dump undo block 'segment_name'
xid xidusn xidslot xidsqn;
dumping file headers
to dump all the datafile headers use
alter session set events 'immediate trace name file_hdrs levellevel';
levels (circa oracle 8.1.5) are
level
description
1
dump datafile entry from control file
2
level 1 + generic file header
3
level 2 + datafile header
10
same as level 3
in later versions, level 7 appears to generate additional trace
the following oradebug command has the same effect
oradebug dump file_hdrs level
dumping control files
controlfilesto dump the current latch status use
alter session set events 'immediate trace name controlf level level';
levels (circa oracle 8.1.5) are
level
description
1
generic file header
2
level 1 + database information + checkpoint progress records
3
level 2 + reuse record div
10
same as level 3
in later versions, level 15 appears to generate additional trace
the following oradebug command has the same effect
oradebug dump controlf level
dumping redo log headers
redo log headersto dump the redo log headers
alter session set events 'immediate trace name redohdr level 1';
levels (circa oracle 8.1.5) are
level
description
1
dump redo log entry from control file
2
level 1 + generic file header
3
level 2 + log file header
10
same as level 3
the following oradebug command has the same effect
oradebug dump redohdr level
dumping redo logs
redo logsto identify the current redo log use
select member from v$logfile
where group# =
( select group# from v$log
where status = 'current'
);
to dump a redo log file use
alter system dump logfile 'filename';
e.g.
alter system dump logfile 'r:/oracle/oradata/jd92001/redo01.log';
the syntax of this statement is as follows
alter system dump logfile 'filename'
scn min minimumscn
scn max maximumscn
time min minimumtime
time max maximumtime
layer layer
opcode opcode
dba min filenumber . blocknumber
dba max filenumber . blocknumber
rba min logfilesequencenumber . blocknumber
rba max logfilesequencenumber . blocknumber;
the minimum and maximum scn is a decimal number
the minimum and maximum time is a decimal number representing the number of seconds since midnight 01jan1988. these are calculated using the following formula
time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) *
60 + mi) * 60 + ss;
yyyy
year
mm
month
01-12
dd
day
01-31
hh
hour
00-23
mi
minute
00-59
ss
second
00-59
where
this is the same formula that is used to represent time within the redo log
the layer and opcode are those used to indicate specific operations within the redo log e.g. layer 5 opcode 4 is an undo segment header commit operation
note that there must be spaces around the periods in the dba and rba.
see metalink note 1031381.6 for further examples
http://www.juliandyke.com/index.htm
oracle internals
参考：set_tsn_p1 百度