Dumps from SQL
Many of the oradebug commands to dump structures are also available from SQL*Plus, sometimes with options to refine or restrict the content of the dump. Many of the dumps shown in the previous section can be done with a call to alter session, or to the package dbms_system. Then there are further dumps that you can get through alter system calls.
oradebug Alternatives
If you don't want to use oradebug, the following, as a representative sample, is how you can dump the buffer cache from an ordinary SQL*Plus session:
alter session set tracefile_identifier = 'alter_session';
execute dbms_system.ksdwrt(1,'Example using alter session')
alter session set events 'immediate trace name buffers level 1';
I usually try to dump some text comments whenever I want to dump data through a looping program, which is where dbms_system.ksdwrt is useful. It takes two parameters: the second is a piece of text, while the first tells Oracle where to write the text. The options are 1 for the trace file, 2 for the alert log, and 3 to write to both.
The typical oradebug dump turns into an immediate event, with the word level appearing between the dump name and the level at which to dump.
Note Although many oradebug dump commands have an SQL equivalent, there will be some translations that don't work. An example is oradebug dump library_cache {address} to dump an individual library cache object. You will probably get a trace file containing nothing but the comment "In-flux value for handle."
You may already be familiar with dbms_system.set_ev() as one of the many methods for enabling sql_trace (or event 10046 as it is often known). However, there is a dummy event number (65,535) that can be used to make the call perform the named dumps:
alter session set tracefile_identifier = 'dbms_system';
execute dbms_system.ksdwrt(1,'Example using dbms_system')
declare
m_serial number(15,0);
begin
select serial#
into m_serial
from v$session
where sid = dbms_support.mysid
;
dbms_system.set_ev(
si => dbms_support.mysid,
se => m_serial,
ev => 65535,
le => 1,
nm => 'buffers'
);
end;
/
To give them their full names, the five parameters for dbms_system.set_ev are sid, serial#, event, level, and name. If you set the event to 65535, then the name and level parameters can take on the names and levels used in the oradebug command.
Data File Blocks
The first of the alter system commands that I use regularly (often wrapped in a little PL/SQL procedure to identify index root blocks, or segment header blocks) is the call to dump a block or group of blocks. The syntax is
alter system dump datafile {File no} block {Block no};
alter system dump datafile 4 block 129;
alter system dump datafile {File no} block min {Block min} block max {Block max};
alter system dump datafile 4 block min 129 block max 133;
In recent versions of Oracle this version of the call will dump the on-disk copy and all the copies of the selected blocks from memory and, thanks to the complexity of the private redo mechanism, may report one of the memory copies as being corrupted. There is a variation on this command that uses the data file name rather than a file number:
alter system dump datafile '{name}' block {Block no};
alter system dump datafile '{name}' block min {Block min} block max {Block max};
If you use this version of the call, the blocks obviously have to be read from disk (which means you have to issue a checkpoint to make sure that you're looking at the latest version, of course).
Although you can dump virtually any data file block with the previous calls (file headers are the one exception I know of, but there may be others), there is a special case that I use occasionally for undo segment headers, which dumps the block by segment name:
alter system dump undo header '{segment name}';
alter system dump undo header ' _SYSSMU3$';
Log Files
The other thing that I dump fairly regularly is the log file. The basic syntax for this is very simple:
alter system dump logfile '{filename}';
alter system dump logfile 'C:ORACLEORADATAD10GREDO01.LOG'
In most cases I tend to issue alter system switch logfile; before doing a little bit of work and then dumping the whole file, but there are many options for being selective about which bits of the file you want to dump. My Oracle Support (the application formerly known as MetaLink) has a note (ID 1031381.6) giving you some of the possible options. The document is not quite complete, however. As far as I know the full syntax is
alter system dump logfile '{filename}'
scn min {first SCN to dump}
scn max {last SCN to dump}
time min {seconds since an oddly arbitrary point in time}
time max {ditto}
layer {integer}
opcode {integer}
xid {usn} {slot} {sequence} -- 10g and later but breaks on IMU redo
Objno {object id} -- 10g and later but breaks on IMU redo
dba min {datafile no} . {blockno} -- remove the dot for 10g and later
dba max {datafile no} . {blockno} -- remove the dot for 10g and later
rba min {log file seq#} . {blockno} -- remove the dot for 10g and later
rba max {log file seq#} . {blockno} -- remove the dot for 10g and later
As you can see, there are options to be selective. The ones that I find more useful are the ones that specify a database block, the object id, and the transaction id. I have, on occasion, used all the other options on production systems apart from the redo block address. The option I've used most commonly is to dump all the redo related to a given database block—and this has usually been to find when, or how, the block was corrupted.
For example, for 9i and 10g respectively:
alter system
dump logfile 'C:ORACLEORADATAD920REDO02.LOG'
dba min 11 . 10 dba max 11 . 10
;
alter system
dump logfile 'C:ORACLEORADATAD10GREDO02.LOG'
dba min 5 10 dba max 5 10
;
Note that the spaces either side of the dot are needed for 9i and earlier, but the dot is not needed from 10g and later (the same is true for the rba min and rba max options). Remember, also, that the file number is the absolute file number, not the relative file number. Since the absolute and relative files numbers are usually equal in small databases (up to 1023 files) you may want to set event 10120 before creating a couple of files as this will allow you to create files where the absolute and relative files numbers don't match.
If you want to scan all the redo generated for a time period, you first have to do a little experimentation (probably). You can specify a time, but it's expressed as the number of seconds since an internally selected starting point—except that there is an error in the code converting seconds to dates: it thinks there are 31 days in every month of the year.
I have a simple script that I use (modifying it as necessary) to dump a small time range in the recent past—the following code, followed by the first few lines of the dump, was for a 5-minute range starting 10 minutes ago on the date that I wrote this appendix:
select
(
(sysdate - 10/(24*60)) -
to_date('25/7/1987 00:00:00','dd/mm/yyyy hh24:mi:ss')
) * 86400 start_time,
(
(sysdate - 5/(24*60)) -
to_date('25/7/1987 00:00:00','dd/mm/yyyy hh24:mi:ss')
) * 86400 end_time
from
dual
;
alter system
dump logfile 'C:ORACLEORADATAD10GREDO02.LOG'
time min 760100786
time max 760101086
DUMP OF REDO FROM FILE 'C:ORACLEORADATAD10GREDO02.LOG'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: 08/25/2011 11:06:26 thru 08/25/2011 11:11:26
The last thing that's worth a mention is the layer/opcode pair. I've mentioned OP codes in the context of redo records in a couple of chapters, and pointed out, for example, that OP code 5.4 is a commit record. The code actually breaks down into two parts, the layer and the opcode within the layer (in this context the word "layer" carries no implication of there being a hierarchy, by the way). Layer 5 is the transaction management layer, layer 10 is the index layer, layer 11 is the table layer, and so on.
Note If you want to know more about the possible layers and OP codes, one of the more comprehensive lists of OP codes can be found on Julian Dyke's website at www.juliandyke.com/Internals/Redo/Redo.html.
It can be useful, occasionally, to analyze what sort of activity had been going on for a period when you think the redo generation has been extreme. I do know of people that have written C programs to read the raw redo log and analyze the appearance of the redo opcodes—and I have to admit that there was one occasion when I could have used such a program—but if you need to do something of this sort, you can always dump the redo for a specified time period and then use something like grep to pick out the OP codes or, as I have done occasionally, read the file into an external table and analyze it with SQL or PL/SQL. The problem with this approach, of course, is that the trace file is likely to be enormous.
The following, for example, is the call to dump the redo only for records that include opcode 10.11 (set pointer to previous leaf block)—one of the actions that occurs when you split an index leaf block and have to set up the links that logically put the new block in the right place. This is followed by the first few lines of output from running the Windows find command against the trace file, looking for the text OP:10.11:
alter system dump logfile 'C:ORACLEORADATAD10GREDO01.LOG'
layer 10 opcode 11 ;
---------- C:ORACLEADMIND10GUDUMPD10G_ORA_3028.TRC
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c1b OBJ:88098 SCN:0x0.04a9ab0a SEQ: 1 OP:10.11
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c1c OBJ:88098 SCN:0x0.04a9ab13 SEQ: 1 OP:10.11
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c1d OBJ:88098 SCN:0x0.04a9ab19 SEQ: 1 OP:10.11
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c1e OBJ:88098 SCN:0x0.04a9ab20 SEQ: 1 OP:10.11
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c1f OBJ:88098 SCN:0x0.04a9ab26 SEQ: 1 OP:10.11
CHG #2 TYP:0 CLS: 1 AFN:5 DBA:0x01800c20 OBJ:88098 SCN:0x0.04a9ab2d SEQ: 1 OP:10.11
I've had to do a little cosmetic edit on the trace file lines to fit the page, but I haven't lost any information by doing so. There are two things you might see as suspicious if this were from a production system. The first is that the OBJ is always the same—we have one index only, which is growing. The second is that the DBA is increasing by one block at a time—every time we add a block to this index, the next block we add is a new block that we add "to the left" of the previous block, meaning we have an index that is accepting lots of data one (or a few) row at a time in descending order.
I've mentioned this example only as an indication of how you may spot a pattern of activity if you can extract a subset of the redo records easily. In this case, from a 5-minute window on the log file, the full trace file was about 38MB—for this particular opcode the trace file was only 160KB, making the option for detailed investigation viable.
One last thought: if you try to dump layer 9999 opcode 9999, Oracle will scan the entire redo log file for an opcode that doesn't exist, so this is a way of validating the integrity of a log file.
Note The instance that you use to dump a file, whether it's a redo log file or a data file, doesn't have to be the instance that created the file. It doesn't even have to be the same version of Oracle, although you usually run into problems if you try to use an older version of Oracle to dump a newer file.