Oracle Internals Notes : Controlfile Dumps

最新推荐文章于 2013-07-09 14:41:54 发布

转载最新推荐文章于 2013-07-09 14:41:54 发布 · 169 阅读

The contents of the current controlfile can be dumped in text form to a process trace file in the user_dump_dest directory using the CONTROLF dump. The levels for this dump are as follows.

Dump Level	Dump Contains
1	only the file header
2	just the file header, the database info record, and checkpoint progress records
3	all record types, but just the earliest and latest records for circular reuse record types
4	as above, but includes the 4 most recent records for circular reuse record types
5+	as above, but the number of circular reuse records included doubles with each level

For example, the following syntax could be used to get a text dump on the controlfile in the trace file of the current process showing all the controlfile record types but only the oldest and most recent of the circular reuse records.

oradebug setmypid
oradebug dump controlf 3

Of course, the session must be connected AS SYSDBA to use the ORADEBUG facility. However, any session with the ALTER SESSION privilege can use the following event syntax to take the same dump.

alter session set events 'immediate trace name controlf level 3';
http://www.ixora.com.au/scripts/dumps.htm

Oracle Advanced Performance Tuning Scripts

Dump and Tracing Scripts

trace_file_name.sql

This script reports the pathname to the trace file for the current session. The trace file name is remembered by SQL*Plus for use by other APT scripts.
Note: The format of the trace file name is platform specific. This script contains three separate queries for different platforms. It is the user's responsibility to comment out those that do not apply.

Download script for: 7.3, 8.0 or 8.1

trace_event_on.sql

This is a simple script to set a trace event in the current session. Trace information is written to the process trace file in the user_dump_dest directory. The trace events are normally numbers in the range 10000 to 10999. For example, event 10053 can be used to trace cost-based optimizer execution path evaluation, and event 10104 can be used to trace hash join processing. Other event numbers are mentioned elsewhere on this site. For most events, the level is merely 1. However, multiple levels are defined for some events to control the level of detail returned.
Warning: Setting events changes the behavior of the Oracle server, and can destroy databases. Do not set events on production instances unless you have checked with Oracle Support.

Download script for: 7.3, 8.0 or 8.1

trace_event_off.sql

This is a simple script to stop a trace in the current session.
Download script for: 7.3, 8.0 or 8.1

which_events.sql

This script checks to see which events are set in the current session.
Download script for: 7.3, 8.0 or 8.1

post_event.sql

This script can be used to set an event in another session. To select a session before running this script, please see the first three scripts on the Session Scripts page. To turn an event off, use level 0.
Download script for: 7.3, 8.0 or 8.1

dump.sql

This is a simple script to dump an internal structure to the trace file for the current process. The systemstate and library_cache dumps are the most frequently used, at levels 10 and 4 respectively.
Warning: These dumps can produce VERY large trace files. They can also have a noticeable effect on system performance. There are other dumps that can damage databases. As always, don't use these things on production instances unless you have checked with Oracle Support.

Download script for: 7.3, 8.0 or 8.1

dump_logfile.sql

This script dumps the contents of a redo log file to the trace file for the current process in human (?) readable format. One nice thing about this script is that it can remind you about the syntax for the options.
Warning: Log file dumps can produce VERY large trace files. As always, don't do this on production instances unless you have checked with Oracle Support.

Download script for: 7.3, 8.0 or 8.1

redo_time.sql

Two of the options for dumping logfiles are to specify minimum and maximum time boundaries for the redo to be dumped. Those times need to be expressed in decimal using a special formula, which is what this script does.
Download script for: 7.3, 8.0 or 8.1

trace_zip.sql

This script can be used to dynamically zip the current process's trace file, prior to taking a large dump or trace, like a redo log file dump or systemstate dump. It creates a named pipe in place of the process's trace file, and spawns a gzip process to compress it. This slows down the taking dumps and traces significantly, but it is sometimes necessary if disk space is short.
This script is Unix specific, and uses the trace_file_name.sql script above.

Download script for: 7.3, 8.0 or 8.1

trace_nozip.sql

This script deletes the named pipe created by trace_zip.sql.
Download script for: 7.3, 8.0 or 8.1

blockdump.sql

This is one of the most important script for learning about Oracle internals. It dumps data blocks in a human readable form to the process' trace file. The Oracle8 facility allows for a range of blocks to be dumped, but in Oracle7 you can only dump one block at a time. Another important difference to be aware of is that Oracle8 does consistent gets on the blocks to be dumped, whereas Oracle7 dumps the current mode buffer.
Download script for: 7.3, 8.0 or 8.1

next_blockdump.sql

In Oracle7 you can only dump one block at a time, but this script can be used to dump consecutive blocks.
Download script for: 7.3

hex_blockdump.sql

This script does blockdumps in hex, rather than in human-readable format.
Download script for: 7.3, 8.0 or 8.1

next_hex_blockdump.sql

And this is the Oracle7 follow-on script.
Download script for: 7.3

header_dump.sql

This script dumps a segment header block by name, rather than by numbers.
Download script for: 7.3, 8.0 or 8.1

treedump.sql

This script dumps the structure of an index tree. The dump file contains one line for each block in the tree, indented to show its level, together with a count of the number of index entries in the block.
Download script for: 7.3, 8.0 or 8.1

buffers.pl

This script is intended for researching where new blocks are introduced to each buffer pool, and how the buffer aging algorithm works. It reads a trace file containing a dump of the buffer headers at level 4, and prints out the file and block number of each buffer in LRU order. The buffers dump can be taken as follows:
oradebug dump buffers 4
or
alter session set events 'immediate trace name buffers level 4';
The trace file must not contain more than one dump, so each dump must be taken from a new session.
Download script for: 8.1 -- It probably works back to 7.3 also, but has not been tested.

containing_chunk.sql

This script identifies the shared pool chunk containing a specific memory address. The address must already be defined to SQL*Plus in decimal as &Addr. This script is dependent on the rawtonum function below.
Download script for: 8.0 or 8.1

rawtonum.sql

This script creates a function that converts RAW values to NUMBERs. This is useful for calculating offsets from memory addresses.
Download script for: 7.3, 8.0 or 8.1

create_v$my_event.sql

This script creates a view over V$SESSION_EVENT that selects just the rows for the current session.
Download script for: 7.3, 8.0 or 8.1