How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1’
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1’ console output:

How to generate systemdump when the instance is hung?

Using oradebug one can analyze if an instance is hung

$ sqlplus “/as sysdba”
SQL> oradebug mysetpid
Statement processed.

— no limit on trace file
SQL> oradebug unlimit
Statement processed.

SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc

— Wait for couple of minutes
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc

— Wait for couple of minutes and get systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.

— Wait for couple of minutes and run systemstate dump
SQL> oradebug dump systemstate 10
Statement processed.

If one cannot connect using SQL*Plus then using PRELIM option one can connect without allocation of a session. Information on PRELIM

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Sun Dec 26 11:47:18 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.
$ sqlplus /nolog
SQL> set _prelim on
SQL> connect /as sysdba
Prelim connection established
SQL> oradebug setmypid
Statement processed.
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/oradata/admin/TEST/udump/test_ora_11852.trc
— Wait for couple of minutes
SQL> oradebug dump systemstate 10
Statement processed.

Attach using debugger and dump systemstate
$ gdb /u01/oracle/product/10.2.0/db_1/bin/oracle 22878
GNU gdb Red Hat Linux (6.3.0.0-1.153.el4_6.2rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type “show copying” to see the conditions.
There is absolutely no warranty for GDB. Type “show warranty” for details.
This GDB was configured as “x86_64-redhat-linux-gnu”…(no debugging symbols found)
Using host libthread_db library “/lib64/tls/libthread_db.so.1”.

Attaching to program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878

(gdb) print ksudss(10)
$1 = -1073762080
(gdb) print ksdhng(3)
$2 = 1
(gdb) detach
Detaching from program: /u01/oracle/product/10.2.0/db_1/bin/oracle, process 22878
(gdb) quit

How to find/list the events set in a session?

Using “oradebug dump events 1” one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

SQL> SELECT distinct sid FROM v$mystat
SID
———-
131
In another session login as an account as sysdba
SQL> SELECT pid, spid FROM v$process where addr IN (SELECT paddr FROM V$session where sid = 131);
PID SPID
———- ——-
12 3420

SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

Or in 11g:
SQL> oradebug eventdump session
1410 trace name context forever, level 12
sql_trace level=12

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.

Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

How to find shared libraries needed to run a binary?

Depending on the platform different command may be available, below are the commands for AIX and SUN to find the shared libraries needed to run a binary. This may not show all the libraries needed as the application may be referenced in the code and dynamically loading them during different phases of execution of a program.

For AIX
# if the binary is 64bit one can use -X32_64 or -X64
/u01/app/oracle/bin $ dump -X32_64 -H oracle

oracle:

***Loader Section***
Loader Header Information
VERSION# #SYMtableENT #RELOCent LENidSTR
0x00000001 0x0000071e 0x00040902 0x000000e2

#IMPfilID OFFidSTR LENstrTBL OFFstrTBL
0x00000008 0x00413b28 0x000056a5 0x00413c0a

***Import File Strings***
INDEX PATH BASE MEMBER
0 /u01/app/oracle/rdbms/lib/:/u01/app/oracle/lib/:/usr/lib:/lib
1 libc.a shr_64.o
2 libpthreads.a shr_xpg5_64.o
3 libjox10.a shr.o
4 libdl.a shr_64.o
5 libperfstat.a shr_64.o
6 libodm.a shr_64.o
7 libc.a aio_64.o

For SUN (may be AIX depends on version).
$ cd $ORACLE_HOME
$ ldd oracle
oracle needs:
/usr/lib/libc.a(shr_64.o)
/usr/lib/libpthreads.a(shr_xpg5_64.o)
/u01/app/oracle/lib/libjox10.a(shr.o)
/usr/lib/libdl.a(shr_64.o)
/usr/lib/libperfstat.a(shr_64.o)
/usr/lib/libodm.a(shr_64.o)
/usr/lib/libc.a(aio_64.o)
/unix
/usr/lib/libcrypt.a(shr_64.o)
/usr/lib/libcfg.a(shr_64.o)
/usr/lib/liblvm.a(shr_64.o)