Oracle Common dump Command __oracle

Oracle Common Dump command

Memory Dumps

1). Global Area

ALTER session SET The EVENTS ' immediatetrace name Global_area level n ';

1 contains PGA 2 contains SGA 4 contains UGA 8 contains indrect memory

2). Library Cache

ALTER session SET The EVENTS ' immediatetrace name Library_cache level n ';

1 Library cache Statistics 2 contains hash table histogram

3 contains object handle 4 contains object structure (Heap 0)

3). Row Cache

ALTER session SET The EVENTS ' immediatetrace name Row_cache level n ';

1 Row cache Statistics 2 contains hash table histogram 8 contains object structure

4). Buffers

ALTER session SET The EVENTS ' immediatetrace name buffers level n ';

1 Buffer Header 2level 1 + block header

3 Level 2 + block contents 4level 1 + hash Chain

5 Level 2 + hash chain 6level 3 + hash Chain

8 Level 4 + users/waiters 9 Level 5 + users/waiters

Ten Level 6 + users/waiters

5). Buffer

ALTER session SET EVENTS ' immediatetrace name buffer level n ';

N is the rdba of a specified block, which dumps all versions of a block in buffer.

6). Heap

ALTER session SET The EVENTS ' immediatetrace name heapdump level level ';

1 PGA Summary 2SGA Summary 4 UGA Summary

8 Call (CGA) Summary 16User Call (CGA) Summary 32Large Call (LGA) Summary

1025 PGA content 2050SGA content 4100 UGA content

8200 Current call content 16400User call content 32800Large Call content

7). Sub Heap

Prior to Oracle version 9.0.1

ALTER session SET The EVENTS ' immediatetrace name heapdump_addr level n ';

If n is a subheap address, dumps the Subheap summary information

If n is the Subheap address +1, the dump is the SUBHEAP content

After Oracle version 9.2.0

ALTER session SET The EVENTS ' immediatetrace name heapdump_addr level n, addr m ';

where M is the subheap address, N is the subheap of the 1 dump, n is the contents of the 2 dump Subheap

8). Process State

ALTER session SET The EVENTS ' immediatetrace name processstate level n ';

9). System State

ALTER session SET The EVENTS ' immediatetrace name systemstate level n ';

10). Error State

ALTER session SET The EVENTS ' immediatetrace name errorstack level n ';

0 Error Stack 1level 0 + function Call stack

2 Level 1 + process State 3level 2 + context Area

11). Hang Analysis

ALTER session SET The EVENTS ' immediatetrace name hanganalyze level n ';

12). Work Area

ALTER session SET The EVENTS ' immediatetrace name workareatab_dump level n ';

1 SGA Information 2Workarea table summary information 3Workarea table details

13). Latches

ALTER session SET The EVENTS ' immediatetrace name latches level n ';

1 Latch Information 2 statistic information

14). Events

ALTER session SET The events ' Immediatetrace name events level n ';

1 Session 2process 3 system

15). Locks

ALTER session SET The EVENTS ' Immediatetrace name locks level n ';

16). Shared Server Process

ALTER session SET The EVENTS ' immediatetrace name shared_server_state level n ';

n takes a value of 1~14

17). Background Messages

ALTER session SET The EVENTS ' immediatetrace name Bg_messages level (pid+1) ';

Filedumps

1). Block

ALTER SYSTEM DUMP datafile file#block block#;

ALTER SYSTEM DUMP datafile file# blockmin min# block MAX max#;

2). Tree Dump

ALTER session SET The EVENTS ' immediatetrace name treedump level object_id ';

3). Undo Segment Header

ALTER SYSTEM DUMP undo_header ' segment_name ';

4). Undo for a Transaction

ALTER SYSTEM DUMP UNDO block ' segment_name ' XID xidusnxidslotxidsqn;

5). File Header

ALTER session SET The EVENTS ' immediatetrace name File_hdrs level n ';

1 file header information in the control file 2level 1 + file header information

3 level 2 + data file header information 10level 3

6). Control file

ALTER session SET The EVENTS ' immediatetrace name Controlf level n ';

1 File header information 2level 1 + database information + checkpoint information

3 Level 2 + Reusable section information 10level 3

7). Redo Log Header

ALTER session SET The EVENTS ' immediatetrace name REDOHDR level n ';

1 control files in the Redo log information 2 level 1 + file header information

3 level 2 + log file header information 10level 3

8). Redo Log

ALTER SYSTEM DUMP LOGFILE ' FileName ';

ALTER SYSTEM DUMP LOGFILE ' FileName '

SCN MIN minscnscn Max maxscntime minmintimetime Max MaxTime

LAYER Layeropcode Opcode

DBA MIN file#. block# DBA MAX file#. block#

RBA MIN logfilesequence#. block# RBA maxlogfilesequence#. block#;

Where time = (((yyyy-1988) * + mm-1) * + dd-1) * + hh) * + mi) * + SS;

9). Loghist

ALTER session SET The EVENTS ' immediatetrace name loghist level n ';

The earliest and latest log history items in the 1 dump control file >1dump 2^n log history items

Oracle trace files are divided into three types, one is the background alarm log file, logging database after the start, shutdown and run

The activity of a process, such as table space creation, rollback segment creation, some alter commands, log switches, error messages, and so on. When a database fails, the file should be viewed first, but the information in the file is not necessarily associated with any error status. Background Alarm Log

File saved in the directory specified by the Background_dump_dest parameter, the file format is SIDALRT.LOG. Another type is a background trace file created by background processes such as DBWR, LGWR, and Smon. Background tracking files are generated according to the background process, and the background trace files are saved in the directory specified by the Background_dump_dest parameter, the file format is SIDDBWR.TRC, SIDSMON.TRC, etc. There is also a type of user trace file generated by the user process (Server processes) connected to Oracle. These files are generated only when an error is encountered during a user session. In addition, the user can generate the class file by performing an Oracle trace event (see later), which is saved in the directory specified by the User_dump_dest parameter in the file format oraxxxxx.trc,xxxxx as the process number (or thread number) for creating the file.

Oracle Trace Events

Oracle provides a class of commands to dump (dump) information contained within Oracle's various internal structures into trace files so that users can resolve various failures based on the contents of the file. There are two ways to set up trace events, one of which is to set up events in the Init.ora file, so that after the open database, all sessions will be affected. Set the format as follows:

event= "Eventnumber trace name Eventname[forever,] [level lv#]: ..."

With: Symbols, you can set multiple events consecutively, or you can set multiple events by using the event continuously.

Another approach is to use the Altersession set events command during a session, which affects only the current session. Set the format as follows:

Alter session SET Events ' [event# |immediate]trace name EventName [Forever] [, Level Levelnumber]: ... '

By: Symbols, you can set multiple events consecutively, or you can set multiple events by using the Altersession set events continuously.

Format Description:

event# refers to the event number that triggers the dump, which can be an Oracle error number (tracking the specified event when a corresponding error occurs) or an Oralce internal event number, which is between 10000 and 10999 and cannot be used with the immediate keyword.

After the immediate keyword indicates that the command is issued, dump the specified structure immediately into the trace file, which is used only in altersession statements and cannot be used with eventnumber, forever keywords.

Trace name is a keyword.

EventName refers to the event name (see later), which is the actual structure name to be dump. If the eventname is the context, the tracking is based on the internal event number.

The Forever keyword indicates that the event remains in effect during the instance or session cycle and cannot be used with immediate.

Levels are event-level keywords. However, there is no level at the dump error stack (errorstack).

Levelnumber represents event level numbers, typically from 1 to 10, 1 for Dump structure header information, and 10 for all information about the dump structure.

blockdump Event : Dump data file, index file, rollback segment file structure

Alter session SET Events ' immediate trace name Blockdumplevel 66666 '; --Represents a block of data with a dump block address of 6666.

After Oracle 8 The command has been changed to:

Alter system dump datafile Block 9; --Represents the 9th block of data in the Dump data file Number 11.

Controlf Event : Dump control file structure

Alter session SET Events ' immediate trace name Controlf Level10 '; --Represents all the contents of the dump control file.

REDOHDR Event : header information for dump redo log

Alter session SET Events ' immediate trace name Redohdr Level1 '; --A control file entry that represents the header of the dump redo log.

Alter session SET Events ' immediate trace name Redohdr Level2 '; --The common file header that represents the dump redo log.

Alter session SET Events ' immediate trace name Redohdr Level10 '; --Represents the full file header of the dump redo log.

Note: The content dump of the redo log can take the following statement:

alter system dump logfile ' logfilename ';

loghist Event : Log history entry in dump control file

Alter session SET Events ' immediate trace name loghist Level1 '; --Represents the earliest and latest log history entries for the dump.

Levelnumber is greater than or equal to 2 o'clock, representing the Levelnumber log history entry for 2.

Alter session SET Events ' immediate trace name Loghist Level4 '; --Represents a dump of 16 log history items.

File_hdrs Event : Dump header information for all data files

Alter session SET Events ' immediate trace name File_hdrslevel 1 '; --A control file item that represents the head of all data files on dump.

Alter session SET Events ' immediate trace name File_hdrslevel 2 '; --Represents a common file header for all data files in dump.

Alter session SET Events ' immediate trace name File_hdrslevel 10 '; --Represents the full file header for all data files in dump.

errorstack Event:dump error stack information

Oracle errors can be obtained in this way when the foreground process will get an error message when the Oracle error occurs, but in some cases the error message is not available.

Alter session SET Events ' 604 Trace name Errorstack forever ';--Represents the dump error stack and the process stack when a 604 error occurs.

systemstate Event : Dump all system State and process state

Alter session SET Events ' immediate trace name Systemstatelevel 10 '; --Represents dump all system state and process state.

coalesec Event : dump specifies the free interval in the table space

When Levelnumber is in hexadecimal, the two High-order bytes represent the number of free intervals, and the two Low-order bytes represent tablespace numbers, such as 0x00050000 representing 5 free intervals in the dump system tablespace, converted to decimal 327680, namely:

Alter session SET Events ' immediate trace name coalesec level327680 ';

processsate Event : Dump process status

Alter session SET Events ' immediate trace name Processsatelevel 10 ';

Library_cache Event : Dump Library cache information

Alter session SET Events ' immediate trace name Library_cachelevel 10 ';

Heapdump Event : Information in the dump PGA, SGA, UGA

Alter session SET Events ' immediate trace name Heapdump Level1 ';

Row_cache Event : Information in the Dump data dictionary buffer

Alter session SET Events ' immediate trace name Row_cachelevel 1 ';

Internal Event Number

1, 10013: for monitoring transaction recovery

2, 10015: Dump Undo Segment Head

3, 10029: Used to give the login information during the session

4, 10030: Used to give out the cancellation information during the session

5, 10032: Dump sorting statistics

6, 10033: Dump sort growth statistics

7, 10045: Tracking freelist Management operations

8, 10046: Tracking SQL statements

9, 10053: Dump optimization strategy

10, 10059: Simulate create and purge errors in the redo log

11, 10061: Prevent Smon from clearing temporary segments at startup

12, 10079: Dump sql*net statistical information

13, 10081: Dump high water mark change

14, 10104: Dump Hash Connection statistics

15, 10128: Dump zoning Information

16, 10200: Dump consistency Read information

17, 10201: Dump consistency read in undo application

18, 10209: Allow impersonation of errors in control files

19, 10210: Triggering data block Check Event

20, 10211: Triggering index Check events

21, 10213: Simulation crashes after writing control file

22, 10214: Simulate a write error in the control file, Lv#1-9 represents the block number that generated the error, otherwise each control file will be faulted

23, 10215: Simulate a read error in the control file

24, 10220: Dump Undo head Change

25, 10221; dump undo Change

26, 10224: the separation and deletion of the dump index

27, 10225: Dump changes based on the range of dictionary management

28, 10229: Simulate I/O errors on the data file

29, 10231: Set to ignore the corrupted data block when full table scan

Alter session SET Events ' 10231 Trace name context off ';

event = "10231 Trace name Context forever, LEVEL10";

30, 10232: will be set to soft damage (Dbms_repair package set or db_block_checking is true)

Data block dump to trace file

31, 10235: for memory heap Check

Alter session SET Events ' 10235 Trace name context Forever,level 1 ';

32, 10241: Dump remote SQL execution

33, 10246: Tracking Pmon Process

34, 10248: Tracking Dispatch process

35, 10249: Tracking the MTS process

36, 10252: Analog Write Data file header error

37, 10253: Analog write redo log file error

38, 10262: Allow the connection when there is a memory leak

Alter session SET Events ' 10262 Trace name context forever,level 300 ';

39, 10270: Dump Share Cursors &