2011_BookMatter_OracleCore - 墨天轮文档

2011_BookMatter_OracleCore

盖国强

371

34页

1次

2019-06-20

5墨值下载

A P P E N D I X

231

Dumping and Debugging

A Summary of This Book’s Hackery

Over the past eight chapters, I’ve been casually mentioning symbolic dumps, peeking at memory,

internal structures, and setting events. This appendix is a summary of the methods I’ve used in the book

to investigate or demonstrate some of the things that Oracle does.

oradebug

There are various ways in which one session can affect how another session behaves. One of the oldest

and easiest is the oradebug utility, which you can take advantage of if your account has sysdba privileges.

Many of the things you can do with oradebug you can also do by setting events, so some of the tricks

listed in this section will reappear in different guises later on.

Suspending Processes

Some of the things I did to get a better understanding of dbwr and lgwr required those processes to stop.

Oradebug allows you suspend and resume a process. The first thing to do is to attach to an Oracle

process, which I tend to do by using the pid from v$process. There are many simple queries you can

write to find the process id for a background process, and the following is an example that probably

works across many versions of Oracle (though there are simpler options from 10g and later):

select

prc.pid

from

v$bgprocess bgp,

v$process prc

where

bgp.name = 'LGWR'

and prc.addr = bgp.paddr

;

On the system I’m looking at right now this query returns the value 6, which I use as follows

(warning: do not do this on a system that anyone else is using—you may find that the instance crashes,

or that the process can’t be made to resume):

APPENDIX ■ DUMPING AND DEBUGGING

232

SQL> oradebug setorapid 6

Windows thread id: 1052, image: ORACLE.EXE (LGWR)

SQL> oradebug suspend

Statement processed.

SQL> -- get some other session to do a little work and commit

SQL> -- it will hang on the commit, waiting on log file sync.

SQL> oradebug resume

Statement processed.

SQL>

Suspending lgwr is particularly risky. Don’t forget that there may be some recursive SQL going on

that will make sessions hang; for example, if you have database auditing enabled with audit connect,

any ordinary end-user sessions trying to connect or disconnect will hang because their connection will

try to commit after inserting or updating a row in sys.aud$.

One particular use I made of the suspend mechanism was to demonstrate that one of my long-held

assumptions (that pmon detected local instance deadlocks) was wrong. I had actually made this

observation in the original manuscript, and one of my reviewers pointed out that I was wrong—so I

suspended pmon, set up a deadlock situation, and waited for the ORA-00060 “Deadlock detected” error;

sure enough, even with pmon suspended, the deadlock was still trapped and reported within the (fairly

standard) 3 seconds.

Dumps

You can use oradebug to dump memory structures to trace files. When I do this I usually connect as sys

and then attach oradebug to my own process to generate the dumps. Once you’re in oradebug, there is a

command to show the trace file name, but it seems to work only after the first use of the trace file;

however, you can modify the trace file name by setting the tracefile_identifier, and this counts as a

first use:

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

Statement processed.

SQL> alter session set tracefile_identifier = xxx;

Session altered.

SQL> oradebug tracefile_name

c:\oracle\admin\d10g\udump\d10g_ora_2256_xxx.trc

SQL>

If you want to see a list of all the available dumps, the command is oradebug dumplist. Many dump

commands take two parameters—the name of the dump and the level at which to dump. The dump

level then tends to follow two patterns: one is simply the higher the level the more that gets dumped; the

other is a bitmap approach, where different powers of 2 result in different dumps. Table A-1 lists the dump

commands I used in the course of writing this book. The effects are not consistent across versions; this

table covers 10g+.

of 34

5墨值下载

oracle 日常运维

关注

评论