unit_test command in oradebug

Thanks to Dan Morgan from PSOUG for getting me to start down this road. Dan pointed me at the unit_test command in oradebug in 11g as well as the dbke_test test:

SQL> oradebug unit_test [arg] [...]

So, what to type in after unit test. There's no usage output when you input garbage:

SQL> oradebug unit_test foo
ORA-00070: command foo is not valid

There's a promising section in the output of strings of an oracle 11g binary with the word 'test' appearing frequently:

ksuxkil_test
ksudss_dbg_test
dbke_test
...

SQL> oradebug unit_test dbke_test foo

Invalid option for dbke_test
Usage:
dbke_test dde_unit_main
dbke_test dde_flow_direct
dbke_test dde_flow_kge
dbke_test dde_test_tagrule
dbke_test sweep
dbke_test dde_test_split
dbke_test dde_flow_kge_soft
dbke_test dde_flow_kge_ora
dbke_test dump_incid_cache
dbke_test reset_incid_cache
dbke_test suspend

Aha! Seems a few of these tests visible in the binary have usage prompts.

I played around with dbke_test (related to ADR) and came up with a couple nice permutations:

SQL> oradebug unit_test dbke_test dde_flow_kge_ora 12333 0 0
SQL> oradebug unit_test dbke_test dde_flow_kge_ora stupid_idiot 0 0

ORA-700 anyone?
SQL> oradebug unit_test dbke_test dde_flow_kge_soft foo bar baz

Run it on a test system unless you want to give your fellow DBA a heart attack. Thanks again to Dan for sharing.

Secrets of the ORA-600 lookup tool

Metalink's ORA-600/ORA-7445 and call stack lookup tool is incredibly useful for finding reference information on various permutations of these errors. I opened the page source for this Metalink page and it is quite interesting. The entire table of error arguments and their corresponding Metalink note IDs is visible. Also we can see the code for processing call stacks, which is educational. We learn, for instance, that the tool supports gdb backtraces.

There is always a link to this tool at the bottom of my web page, http://www.ora-600.net.

SQL*Plus 'prelim' connection

A little known feature of SQL*Plus is the 'prelim' connection, which allows you to connect to an Oracle instance's SGA without alloctaion a session. This is very handy when you are dealing with a completely hung instance that is no longer accepting logins, even as SYSDBA.

You can use the prelim connection to obtain diagnostics from within your own session or in an already-running session in the database. You can take diagnostic traces like hanganalyze and systemstate, or query x$ structs.

Here's an example of dumping the contents v$wait_chains (a new view in 11g) using the prelim connection:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug direct_access enable trace
Statement processed.
SQL> oradebug direct_access disable reply
Statement processed.
SQL> oradebug direct_access set content_type = 'text/plain'
Statement processed.
SQL> oradebug direct_access select * from x$ksdhng_chains
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_ora_3025.trc

Here's an example of dumping hanganalyze and systemstate from within he already-running DIAG process:

$ sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production
SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established
SQL> oradebug setorapname diag
Oracle pid: 4, Unix process pid: 9837, image: oracle@host (DIAG)
SQL> oradebug dump hanganalyze 1
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug tracefile_name
/opt/oracle/diag/rdbms/test01/test01/trace/test01_diag_9837.trc

I believe prelim was quietly introduced in 10g. It does not appear to be available in 9i. This feature is extremely important for obtaining diagnostics upon the first occurrence of a hang. It prevent encountering that hang over and over, never finding the cause.

Thanks, and be sure to check out my whitepapers and articles at http://www.ora-600.net.

Jeremiah