Miladin Modrakovic’s Blog: Oraclue

Each Oracle process has a process state object.Process is running session  and session open transaction.Typically process has only one session object.

To dump a process state  I normally use:

alter session set events ‘immediate trace name processstate level 10′ or

oradebug dump processstate 10

This dump will produce file which has many different information about process itself like process global information, dump of memory , session wait history etc.

The oradebug  unit test harness command has option ( ksdxutdiagpid ) that will produce similar dump but smaller in size and with some information that are not included in processstate dump with level 10.

So here is my short list of commands :

oradebug setmypid
alter system flush buffer_cache;
select * from dba_extents;
oradebug unit_test_nolg ksdxutdiagpid
oradebug tracefile_name

First part of dump file has general process information like pid, sid, session serial etc

*** 2009-06-29 15:45:14.517
Process diagnostic dump for oracle@apollo (TNS V1-V3), OS id=6957,
pid: 29, proc_ser: 139, sid: 238, sess_ser: 30736
——————————————————————————-

Next section  has information about memory, swap and process.

loadavg : 0.21 0.17 0.13
memory info: free memory = 0.00M
swap info:   free = 0.00M alloc = 0.00M total = 0.00M
F S UID        PID  PPID  C PRI  NI ADDR SZ WCHAN  STIME TTY          TIME CMD
0 S oracle    5334     1  0  75   0 – 695798 -     Jun11 ?        00:00:42 ora_lgwr_test
0 S oracle    6957  6956  1  78   0 – 692473 pipe_w 15:41 ?       00:00:02 oracletest11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0 S oracle   25659     1  0  75   0 – 695799 -     Jun01 ?        01:07:44 ora_lgwr_demo

Third part is a short stack dump with all Oracle functions

Short stack dump: <-ksedsts()+315<-ksdxfstk()+32<-ksdxdocmdmultex()+3456<-ksdxdocmdmult()+29<-ksudmp_proc_short_stack()+697<-ksdhng_diag_proc_int(
)+2760<-ksdhng_diag_proc()+27<-ksdhng_diag_proc_ut()+139<-ksdxutdiagpid()+114<-ksdxuth()+1249<-ksdxen_int()+5656<-ksdxen()+14<-opiodr()+1220<-ttcp
ip()+1208<-opitsk()+1449<-opiino()+1026<-opiodr()+1220<-opidrv()+580<-sou2o()+90<-opimai_real()+145<-ssthrdmain()+177<-main()+215<-__libc_start_ma
in()+244<-_start()+41

Next part has information about wait stack and wait state:

Current Wait Stack:
0: waiting for ‘process diagnostic dump’
=0, =0, =0
wait_id=22666 seq_num=22667 snap_id=1
wait times: snap=0.153272 sec, exc=0.153272 sec, total=0.153272 sec
wait times: max=30.000000 sec
wait counts: calls=0 os=0
in_wait=1 iflags=0×1a0
Wait State:
auto_close=0 flags=0×22 boundary=(nil)/-1

and last part is dedicated to session wait history and sampled session history:

Session Wait History:
0: waited for ‘SQL*Net message from client’
driver id=62657100, #bytes=1, =0
wait_id=22665 seq_num=22666 snap_id=1
wait times: snap=0.002478 sec, exc=0.002478 sec, total=0.002478 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.002582 sec of elapsed time
1: waited for ‘db file sequential read’
file#=3, block#=1b179, blocks=1
wait_id=22664 seq_num=22665 snap_id=1
wait times: snap=0.000013 sec, exc=0.000013 sec, total=0.000013 sec
wait times: max=infinite
wait counts: calls=0 os=0
occurred after 0.000127 sec of elapsed time

———-
The history is displayed in reverse chronological order.

sample interval: 1 sec, max history 120 sec
—————————————————
[1 sample,                                                          15:45:14]
waited for ‘db file sequential read’, seq_num: 22340
p1: ‘file#’=0×2
p2: ‘block#’=0×9a53
p3: ‘blocks’=0×9a53
time_waited: >= 0 sec (still in wait)
[1 sample,                                                          15:45:13]
idle wait at each sample
[1 sample,                                                          15:45:12]
waited for ‘db file sequential read’, seq_num: 18907
p1: ‘file#’=0×2
p2: ‘block#’=0×13e03
p3: ‘blocks’=0×13e03
time_waited: 0.003633 sec (sample interval: 0 sec)
[1 sample,                                                          15:45:11]
waited for ‘db file sequential read’, seq_num: 16332
p1: ‘file#’=0×1
p2: ‘block#’=0×2b01
p3: ‘blocks’=0×2b01
time_waited: 0.005140 sec (sample interval: 0 sec)
[10 samples,                                             15:45:01 - 15:45:10]

Cool thing is that one command dump all of this.

Got this idea surfing Tanel’s post on Oracle’s dignostic events .He explained some unkown syntax for setting oracle diagnostic events.Full article can be found at:

http://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-events-part-1-syntax-for-ksd-debug-event-handling/

It’s a very good post.In addition to this syntax there are  two  more options  for action keyword that I have used in the past.

Let me start with syntax that’s everyone familiar with:

  alter session set events '10046 trace name context forever, level 12';

action keyword or  trace in this case is most used.

Next one in line would be word debugger/debug

Debugger -  invokes system debugger

examples:

SQL> alter session set events ‘immediate debugger ‘;

Session altered.

OR   SQL> alter session set events ‘immediate debug’;

Session altered.

SQL> alter session set events ‘parse_sql_statement debugger’;

Session altered.

SQL> alter session set events ‘10117 debugger’;

Session altered.

You can use debuger to call  script or command:

vi debug.sh

/bin/echo Hello World! $*

SQL> alter system set”_oradbg_pathname”=’/oracle/admin/test/scripts/debug.sh’;

System altered.

SQL> alter system set events ‘logon debugger’;

System altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
-sh-3.1$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Hello World! 13813

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

Other word but more fun  is word CRASH .It will crash oracle process for testing , so be carefull.

Do not crash wrong session.

examples:

SQL> alter session set events ‘deadlock crash’;

Session altered.

 Crash someone session:

SQL>  oradebug setospid 19779
Oracle pid: 36, Unix process pid: 19779, image:
SQL> oradebug event immediate crash
ORA-00072: process “Unix process pid: 19779, image:  is not active

Crash your own session:

SQL> alter session set events ‘immediate crash’;
alter session set events ‘immediate crash’
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13799
Session ID: 107 Serial number: 7376

Crash on event:

SQL> alter session set events ‘<event_name>  crash’;

Session altered.

SQL> alter session set events ‘parse_sql_statement crash’;

Session altered.

SQL> alter session set events ‘0×23E crash’;

Session altered.

Happy Crashing

Quick test to prove that  Virtual Private Database ( VPD ) works on virtual column.

Here is demo.First create test table and load it ( so lazy that I found this on google too )

/** Create table and insert some rows **/
DROP TABLE employees;
CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  bonus      NUMBER(9,2),
  extra1       NUMBER(3),
  extra2       NUMBER(3),
  bonus1     AS (ROUND(bonus*(1+extra1/100),2)),
  bonus2     NUMBER GENERATED ALWAYS AS (ROUND(bonus*(1+extra2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (1, ‘MARKO’, ‘JANKO’, 100, 5, 10);

INSERT INTO employees (id, first_name, last_name, bonus, extra1, extra2)
VALUES (2, ‘JOE’, ‘JANKO’, 200, 10, 20);

COMMIT;

 /** VPD role **/

drop role view_virtual;
create role view_virtual not identified;

Here is my function that will enable users witt VIEW_VIRTUAL role to view data on column BONUS2.

 /** Create function **/

CREATE OR REPLACE FUNCTION secure_virtual (oowner IN VARCHAR2, ojname IN VARCHAR2)
RETURN VARCHAR2 AS
 
BEGIN
  IF dbms_session.is_role_enabled(‘VIEW_VIRTUAL’)  then
   return ”;
  
  ELSE 
    return ‘1=0′; — don’t show column
  END IF;
END secure_virtual;
/

and finally create policy  (more…)

If you are running sqlplus or RMAN on Windows than you can use upper arrow to get history.What about linux?

First option:

1) Install  readline wrapper:

 yum install rlwrap

2) Change    .bashrc and make alias :

alias sqlplus=’rlwrap sqlplus’ alias rman=’rlwrap rman’

 The other option is to install  qqlplus which you can find on the link

http://gqlplus.sourceforge.net/

I tried it on my Oracle 11 running Linux 64bit and did not work.I used already made executable under Linux folder ( included into compressed source file )  but I am sure this can be fixed by comipiling source code.

Chet  www.oraclenerd.com  sent me  link about Oracle running on Amazon Elastic Compute Cloud.

Did not have chance to play with it yet  but I would like to explore it more..

Demo can be found at:

http://www.oracle.com/technology/tech/cloud/demos/oracle_on_ec2_viewlet_swf.htm

If you had a chance to run database on EC2 please post your comments… cons & pros…

I noticed this weekend that metalink changed.

Classic metalink is still available…  I took me some time to get around..

Also new option seems to slower than old ones and once a while error message will pop up from portal …

I like simplicity ..

Also Oracle released 11g  patchset  11.1.0.7  ( Please see note NOTE:454507.1 ).

From note :

Note: On release 11g and onwards you can use the ASMCMD to backup the ASM metadata.

From Oracle documentation itself:

Syntax and Description

md_backup [-b location_of_backup] [-g dgname [-g dgname …]]

location_of_backup is the location in which you want to store the backup file

dgname is the name of the disk group that you want to back up

Let’s try:

ASMCMD [+] > md_backup -b /tmp/backupdg -g md_data -g md_logs

Disk group to be backed up: MD_LOGS

When open backupd file there are only entries for this disk groups.

It will generate report only on last group!!  Not a big deal but ASM is full of bugs..