Oracdebug ( Undocumented Utility )

Oracdebug ( Undocumented Utility )

 

改天读～

 

September 11th, 2008 | Author: admin

Reading time: 4 - 6 minutes

Oradebug utility is veryA useful for all the DBA.

Oradebug is mainly useful in case of hang analysis , tracing perticular process etc.With oradebug you can litteraly see the database engine.

Understanding the trace file generated by oradebug requires lots of expertise.

Among the many useful things that can be done with oradebug are:
- enabling/disabling the SQL tracing for another user's session.
- suspending intensive processes
- finding information about shared memory and semaphores
- closing the trace file so that new one can be generated
- manipulating and dumping internal structures
- wake up processes etc.
Examples:

• Oradebug setospid

Get the OS PID from spid column of v$process and set oradebug on it.

SQL> select program,spid from v$process;

PROGRAMAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SPID
------------------------------------------------ ------------
PSEUDO
oracle@quartz (PMON)AAAAAAAAAAAAAAAAAAAAAAAA 4106
oracle@quartz (PSP0)AAAAAAAAAAAAAAAAAAAAAAAAAA 4108
oracle@quartz (MMAN)AAAAAAAAAAAAAAAAAAAAAAAA 4110

SQL> oradebug setospid 4106
Oracle pid: 2, Unix process pid: 4106, image: oracle@quartz (PMON)
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug procstat
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/product/10.2.0/db_1/admin/dev/bdump/dev_pmon_4106.trc

• oradebug setorapid

Dump statistics for the DBWR background process:

SQL> select pid,name from v$process p, v$bgprocess b where b.paddr = p.addr;

PID NAME
---------- -----
2 PMON
3 PSP0
4 MMAN
5 DBW0
6 LGWR
7 CKPT
8 SMON
9 RECO
10 CJQ0
11 MMON
12 MMNL

PID NAME
---------- -----
16 ARC0
17 ARC1
18 ARC2
21 QMNC

15 rows selected.

SQL> oradebug setorapid 5
Unix process pid: 4112, image: oracle@quartz (DBW0)

SQL> oradebug procstat
Statement processed.

SQL> oradebug tracefile_name
/u01/app/oracle/product/10.2.0/db_1/admin/dev/bdump/dev_dbw0_4112.trc

• oradebug dump systemstate

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug dump systemstate 10;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/product/10.2.0/db_1/admin/dev/udump/dev_ora_557.trc

• oradebug dump processstate

SQL> oradebug setmypid
Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> oradebug dump PROCESSSTATE 10;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/product/10.2.0/db_1/admin/dev/udump/dev_ora_1079.trc

• oradebug dump errorstack

Dump of the process call stack and other information.

SQL> oradebug setospid 13446
Oracle pid: 12, Unix process pid: 13446, image: oracle@apollo (TNS V1-V3)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.

• oradebug dump controlf

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.

SQL> oradebug setospid 19272
Oracle pid: 26, Unix process pid: 19272, image: oracle@tomcat (TNS V1-V3)
SQL> oradebug dump controlf 4
Statement processed.

• oradebug dump file_hdrs

To dump one or more blocks:
alter system dump datafile <file_number> block min <first block > block max <last block >;
Example
select segment_name, header_file, header_block from dba_segments
where segment_type='ROLLBACK';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
--------------- ----------- ------------
SYSTEMAAAAAAAAAAAAAAAAAAAAAAAAAAAA 1AAAA 2
RBS0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2AAA 2
RBS1AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 3202
RBS2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 2 6402

• oradebug dump heapdump

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump heapdump 2
Statement processed.

------------------ part of the trace fileAAAAAA --------------------
*** SESSION ID:(7.14111) 2003-09-03 13:01:21.412
******************************************************
HEAP DUMP heap name="sga heap" desc=0x80000030
extent sz=0xfc4 alt=48 het=32767 rec=9 flg=2 opc=0
parent=0 owner=0 nex=0 xsz=0x3d2bdf4
EXTENT 0
Chunk b5815ff8 sz= 41699524 permAAAAAAAAAAAAAAA "permAAAAAAAAA " alo=19395672
Chunk b7fda8bc sz= 887380 freeAAAAAAAAAAAAAA "AAAAAAAAAA "
Chunk b80b3310 sz= 560 freeable "library cache " ds=b80b5a0c
Chunk b80b3540 sz= 2588 freeable "sql areaAAAAAAAAAAAAAAAAAAAA " ds=b80b5898
Chunk b80b3f5c sz= 732 freeable "sql areaAAAAAAAAAAAAAAAAAAAAA " ds=b80ba69c
Open the trace file and look for the ds (descriptor address).Convert hex to decimal and do the
dump.
SQL> oradebug dump heapdump_addr 1 3087751692
--------------------------- part of the trace file generated using command above -------------
Statement processed.
*** 2003-09-03 13:12:35.330
*** SESSION ID:(8.20679) 2003-09-03 13:12:35.322
HEAP DUMP heap name="library cache" desc=0xb80b5a0c
extent sz=0x224 alt=32767 het=8 rec=9 flg=2 opc=0
parent=80000030 owner=b80b5804 nex=0 xsz=0x224
EXTENT 0
Chunk b80b3324 sz= 464 permAAAAAAAAAAAAAAAAA "permAAAAAAAAA " alo=176
Chunk b80b34f4 sz=AAAAAAAAAA 76 freeable "kgltbtabAAAAAAAAA "
EXTENT 1
Chunk b80b55b4 sz= 500 permAAAAAAAAAAAAAAAAA "permAAAAAAAAA " alo=500
Chunk b80b57a8 sz=AAAAAAAAAA 40 freeAAAAAA "AAAAAAAAAA "
EXTENT 2
Chunk b80b57f4 sz= 244 permAAAAAAAAAAAAAAAAA "permAAAAAAAAA " alo=244
Chunk b80b58e8 sz=AAAAAAAAAA 52 freeAAAAAA "AAAAAAAAAA "
Chunk b80b591c sz=AAAAAAAAAA 76 freeable "kgltbtabAAAAAAAAAA "
Chunk b80b5968 sz=AAAAAAAAAAA 76 freeable "kgltbtabAAAAAAAAA "