eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« Percona最新发布XtraBackup for MySQL 8.0 | 文摘首页

Oracle跟踪和诊断的利器: ORADEBUG 手册

转载自:http://psoug.org/reference/oradebug.html

Oracle ORADEBUG
Version 11.1
GENERAL
Background Processes List SELECT b.name, p.pid
FROM gv$bgprocess b, gv$process p
WHERE b.paddr = p.addr
ORDER BY 1;
Dispatcher Processes List SELECT d.name, p.pid
FROM gv$dispatcher d, gv$process p
WHERE d.paddr = p.addr;
Job Queue Process List SELECT s.paddr, s.sid, j.job
FROM gv$session s, dba_jobs_running j
WHERE s.sid = j.sid;

SELECT pid
FROM gv$process
WHERE addr = '17';
Parallel Execution Slave Processes List SELECT pid, server_name, status
FROM gv$px_process;
Shared Server Processes List SELECT s.name, p.pid
FROM gv$shared_server s, gv$process p
WHERE s.paddr = p.addr;
SGA Variables List SELECT ksmfsnam
FROM x$ksmfsv
WHERE ksmfsnam LIKE '%\_' ESCAPE '\';
Switches
Switch Format Description
-G <Inst-List | def | all> Parallel oradebug command prefix
-R <Inst-List | def | all> Parallel oradebug prefix return output
CALL
Invoke function with arguments oradebug call <func> [arg1] ... [argn]
SQL> oradebug call ?
CLOSE_TRACE
Close trace file oradebug close_trace
SQL> oradebug close_trace
CORE
Dump core without crashing process oradebug core
SQL> oradebug core
CURRENT_SQL

Get current SQL
oradebug current_sql
SYS UWCLASS
conn / as sysdba conn uwclass/uwclass
GRANT execute ON dbms_support
TO uwclass;
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid =
sys.dbms_support.mysid);
oradebug setorapid 26
SELECT t.tablespace_name
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name;
oradebug core
DELETE
Delete a watchpoint oradebug delete <local|global|target> watchpoint <id>
SQL> oradebug delete ?
DIRECT_ACCESS
Fixed table access oradebug direct_access <set/enable/disable command | select query>
TBD
DMPCOWSGA
Dump & map SGA as COW oradebug dmpcowsga <SGA dump dir>
SQL> oradebug dmpcowsga "c: emp"
DUMP
Invoke named dump oradebug dump <dump_name> <lvl> [addr]
-- dump_name values can be obtained from oradebug dumplist

SQL> oradebug setmypid
SQL> oradebug dump library_cache 4
Invoke ASH dump SQL> oradebug setmypid
SQL> oradebug dump ashdump 10
DUMPLIST

Print a list of available dumps
oradebug dumplist
SQL> oradebug dumplist

Available Dumps

ADJUST_SCN HANGDIAG_HEADER MMAN_ALLOC_MEMORY
ALRT_TEST HEAPDUMP MMAN_CREATE_DEF_REQUEST
ARCHIVE_ERROR HEAPDUMP_ADDR MMAN_CREATE_IMM_REQUEST
ASHDUMP HM_FW_TRACE MMAN_IMM_REQUEST
ATSK_TEST INSTANTIATIONSTATE MMON_TEST
AWR_FLUSH_TABLE_OFF IOERREMUL MODIFIED_PARAMETERS
AWR_FLUSH_TABLE_ON IOERREMULRNG NEXT_SCN_WRAP
AWR_TEST IR_FW_TRACE OBJECT_CACHE
BC_SANITY_CHECK JAVAINFO OCR
BG_MESSAGES KCBO_OBJ_CHECK_DUMP OLAP_DUMP
BLK0_FMTCHG KDLIDMP OPEN_FILES
BUFFER KRA_OPTIONS PGA_DETAIL_CANCEL
BUFFERS KRA_TRACE PGA_DETAIL_DUMP
CALLSTACK KRB_BSET_DAYS PGA_DETAIL_GET
CHECK_ROREUSE_SANITY KRB_CORRUPT_INTERVAL PIN_BLOCKS
CONTEXTAREA KRB_CORRUPT_REPEAT PIN_RANDOM_BLOCKS
CONTROLF KRB_CORRUPT_SIZE POKE_ADDRESS
CROSSIC KRB_CORRUPT_SPBAD_INTERVAL POKE_LENGTH
CRS KRB_CORRUPT_SPBAD_REPEAT POKE_VALUE
CSS KRB_CORRUPT_SPBITMAP_INTER POKE_VALUE0
CURSOR_STATS KRB_CORRUPT_SPBITMAP_REPEA POOL_SIMULATOR
CURSORDUMP KRB_CORRUPT_SPHEADER_INTER PROCESSSTATE
CURSORTRACE KRB_CORRUPT_SPHEADER_REPEA REALFREEDUMP
DATA_ERR_OFF KRB_FAIL_INPUT_FILENO RECORD_CALLSTACK
DATA_ERR_ON KRB_OPTIONS RECOVERY
DBSCHEDULER KRB_OVERWRITE_ACTION REDOHDR
DROP_SEGMENTS KRB_PIECE_FAIL REDOLOGS
DUMP_ADV_SNAPSHOTS KRB_SET_TIME_SWITCH REFRESH_OS_STATS
DUMP_ALL_COMP_GRANULES KRB_SIMULATE_NODE_AFFINITY ROW_CACHE
DUMP_ALL_COMP_GRANULE_ADDR KRB_TRACE RULESETDUMP
DUMP_ALL_OBJSTATS KRB_UNUSED_OPTION RULESETDUMP_ADDR
DUMP_ALL_REQS KRBMRSR_LIMIT SAVEPOINTS
DUMP_PINNED_BUFFER_HISTORY KRBMROR_LIMIT SELFTESTASM
DUMP_TEMP KRC_TRACE SET_NBLOCKS
DUMP_TRANSFER_OPS KSDTRADV_TEST SET_TSN_P1
DUMPGLOBALDATA KSFQP_LIMIT SHARED_SERVER_STATE
ENQUEUES KSKDUMPTRACE SIMULATE_EOV
ERRORSTACK KTPR_DEBUG SYSTEMSTATE
EVENT_TSM_TEST KSTDUMPALLPROCS SYSTEMSTATE_GLOBAL
EXCEPTION_DUMP KSTDUMPALLPROCS_CLUSTER TEST_DB_ROBUSTNESS
FAILOVER KSTDUMPCURPROC TEST_GET_CALLER
FBHDR KUPPLATCHTEST TEST_SPACEBG
FBINC KXFPCLEARSTATS TEST_STACK_DUMP
FBTAIL KXFPDUMPTRACE TR_CRASH_AFTER_WRITE
FILE_HDRS KXFPBLATCHTEST TR_CORRUPT_ONE_SIDE
FLASHBACK_GEN KXFXCURSORSTATE TR_READ_ONE_SIDE
FLUSH_CACHE KXFXSLAVESTATE TR_SET_ALL_BLOCKS
FLUSH_JAVA_POOL LATCHES TR_SET_BLOCK
FULL_DUMPS LDAP_KERNEL_DUMP TR_SET_SIDE
GC_ELEMENTS LDAP_USER_DUMP TRACE_BUFFER_OFF
GES_STATE LIBRARY_CACHE TRACE_BUFFER_ON
GLOBAL_AREA LOCKS TREEDUMP
HANGANALYZE LOGERROR TR_RESET_NORMAL
HANGANALYZE_PROC LOGHIST UPDATE_BLOCK0_FORMAT
HANGANALYZE_GLOBAL LONGF_CREATE WORKAREATAB_DUMP
DUMPSGA
Dump fixed SGA oradebug dumpsga

oradebug dumpsga <bytes>
SQL> oradebug dumpsga
c:\oracle\product\admin\orabase\udump\orabase_ora_2120.trc
DUMPTYPE
Print/dump an address with type info oradebug dumptype <address> <type> <count>
TBD
DUMPVAR
Print/dump a fixed PGA/SGA/UGA variable oradebug dumpvar <pga|sga|uga> <name> [level]
SQL> oradebug setmypid
SQL> oradebug dumpvar SGA kcbnbh
EVENT
Set trace event in process oradebug EVENT <event>
TRACE NAME CONTEXT FOREVER, LEVEL <level>
SQL> oradebug setmypid

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

SQL> oradebug unlimit
FFBEGIN
Flash Freeze the Instance oradebug ffbegin
SQL> oradebug ffbegin
FFDEREGISTER
FF deregister instance from cluster oradebug ffderegistger
SQL> oradebug ffderegister
FFRESUMEINST
Resume the flash frozen instance oradebug ffresumeinst
SQL> oradebug ffresumeinst
FFSTATUS
Flash freeze status of instance oradebug ffstatus
SQL> oradebug ffstatus
FFTERMINST
Call exit and terminate instance oradebug ffterminst
SQL> oradebug ffterminst
FLUSH
Flush the current contents of the trace buffer to the trace file use oradebug flush
SQL> oradebug flush
HANGANALYZE

Analyze system hang for stand-alone
oradebug hanganalzye [level] [syslevel]
SQL> oradebug setmypid;
SQL> oradebug hanganalyze;
SQL> oradebug flush;
Analyze system hang for RAC SQL> oradebug setmypid;
SQL> oradebug -g def hanganalyze 1
SQL> oradebug flush;
HELP
Describe one or all commands oradebug help

oradebug help <command>
SQL> oradebug help
SQL> oradebug help flush
LKDEBUG
Invoke global enqueue service debugger oradebug lkdebug
SQL> oradebug lkdebug
MAPCOWSGA
Map SGA as COW oradebug mapcowsga <SGA dump dir>
SQL> oradebug mapcowsga "c: emp"
NSDBX
Invoke CGS name-service debugger oradebug nsdbx
SQL> oradebug nsdbx
PEEK
Print/Dump memory oradebug peek <addr> <len> [level]
SQL> oradebug peek 0x20005F0C 12
POKE
Modify memory. Never perform this function on a production database! oradebug poke <addr> <len> <value>
SQL> oradebug poke 0x20005F0C 4 0x46495845
PROCSTAT
Dump process statistics oradebug procstat
SQL> oradebug setmypid

SQL> oradebug procstat
RESUME
Resume execution oradebug resume
SQL> oradebug resume
SESSION_EVENT
Set trace event in session oradebug session_event <text>
SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1
SETINST
Set instance list oradebug setinst <instance# .. | all>
SQL> oradebug setinst "1"
SETMYPID
Sets the oradebug PID to the current process oradebug setmypid
SQL> oradebug setmypid
SETORAPID

Set PID of Oracle process to debug
oradebug setorapid <orapid> ['force']
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = DBMS_SUPPORT.MYSID);

or

SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$session
WHERE sid = (SELECT sid FROM gv$mystat WHERE ROWNUM = 1));

/*
or one of the processes from the background, dispatcher, job queue, parallel, or shared server process lists
*/

SQL> oradebug setorapid 19
SETOSPID
Set OS pid of process to debug

The operating system process ID is the PID on Unix systems and the thread number for Windows systems
oradebug setospid <ospid>
Do not use as it often fails. Use setorapid instead.
SETORAPNAME
Set Oracle process name to debug oradebug setorapname <orapname>
TBD
SETVAR
Modify a fixed PGA/SGA/UGA variable oradebug setvar <pga|sga|uga> <name> <value>
SQL> oradebug setvar SGA kcfdfk 200
SGATOFILE
Dump SGA to file oradebug sgatofile <SGA dump dir>
SQL> oradebug ffbegin
SQL> oradebug sgatofile "c: emp"
SQL> oradebug ffresumeinst
SHORT_STACK
Get abridged OS stack oradebug short_stack
SQL> oradebug short_stack
SHOW
Show watchpoints oradebug show <local|global|target> watchpoint <id>
SQL> oradebug show?
SKDSTTPCS
Helps translate PCs to names oradebug skdsttpcs <ifname> <ofname>
SQL> oradebug skdsttpcs?
SUSPEND
Suspends the current process oradebug suspend
SQL> oradebug suspend
TRACEFILE_NAME
Get trace file name

Will not return a value on Windows systems
oradebug tracefile_name
SQL> oradebug tracefile_name
UNLIMIT
Unlimit the size of the trace file oradebug unlimit
SQL> oradebug unlimit
WAKEUP
Wake up Oracle process oradebug wakeup <orapid>
SELECT pid
FROM gv$process
WHERE addr = (
SELECT paddr
FROM gv$bgprocess
WHERE name = 'SMON');

SQL> oradebug wakeup 7
WATCH
Watch a region of memory oradebug watch <address> <len> <self|exist|all|target>
SQL> oradebug watch?
Demo

Taking a heap dump during a large (sorting) query
-- This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which
-- executes the query. In session 2 identify the PID using


SELECT pid
FROM gv$process
WHERE addr IN (
SELECT paddr
FROM gv$session
WHERE sid = dbms_support.mysid);

In this example the PID was 12

In session 1 set the Oracle PID using

ORADEBUG SETORAPID 12

In session 2 start the query

SELECT ... FROM t1 ORDER BY ....

In session 1 suspend session 2

ORADEBUG SUSPEND

The query in session 2 will be suspended

In session 1 run the heap dump

ORADEBUG DUMP HEAPDUMP 1

The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2

ORADEBUG RESUME

The query in session 2 will resume execution

历史上的今天...

By eygle on 2018-09-30 12:52 | Comments (0) | Oracle摘 | 3318 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com