Rotate listener log (TNS-12547, TNS-12560, TNS-00517) Part II

In mine previous post I have shown how to rotate listener on Linux environment. First comment from certain Mr. Gorgy was why there is no where to find Windows version for the same purpose.

So here it is. Script was tested with Oracle 10g on Windows 2000/XP/2003/2008.

:: Works for all Oracle databases pre-11g
:: %ORACLE_HOME% muste be defined (otherwise will produce an error 'Directory not found')
:: 11g have changes in a way that introduce universal "diagnostic_dest" parameter
:: which is not cover in this solution (will produce an error 'Directory not found')

:: Make Sure Listener name is passed
@if "%1" == "" goto QUIT_WITH_MESSAGE

@ECHO OFF
SETLOCAL

:: Set the Environment
@set DATUM=%date:~10,4%%date:~4,2%%date:~7,2%
@set VRIJEME=%time:~0,2%%time:~3,2%
@SET NOW=%DATUM%_%VRIJEME%
:: No need for upper and lower case values (Windows!)
@SET LSNR=%1

@if NOT EXIST %ORACLE_HOME%\network\log GOTO NOLOGDIR

@if NOT EXIST %ORACLE_HOME%\network\log\%LSNR%.log GOTO NOLOGFILE

%ORACLE_HOME%\bin\lsnrctl set current_listener %LSNR%
IF %ERRORLEVEL% NEQ 0 GOTO ERR
%ORACLE_HOME%\bin\lsnrctl set log_file temp.log
IF %ERRORLEVEL% NEQ 0 GOTO ERR

copy %ORACLE_HOME%\network\log\%LSNR%.log %ORACLE_HOME%\network\log\%LSNR%.log.%NOW%
IF %ERRORLEVEL% NEQ 0 GOTO ERR

@DEL %ORACLE_HOME%\network\log\%LSNR%.log
IF %ERRORLEVEL% NEQ 0 GOTO ERR

%ORACLE_HOME%\bin\lsnrctl set current_listener %LSNR%
IF %ERRORLEVEL% NEQ 0 GOTO ERR
%ORACLE_HOME%\bin\lsnrctl set log_file %LSNR%.log
IF %ERRORLEVEL% NEQ 0 GOTO ERR

:: add content which has been logged in the meantime
@IF NOT EXIST %ORACLE_HOME%\network\log\temp.log GOTO END
@type %ORACLE_HOME%\network\log\temp.log >> %ORACLE_HOME%\network\log\%LSNR%.log.%NOW%
@DEL %ORACLE_HOME%\network\log\temp.log
GOTO END


:NOLOGFILE
@ECHO ON
@echo "File %ORACLE_HOME%\network\log\%LSNR%.log not found..."
@GOTO QUIT_WITH_MESSAGE

:NOLOGDIR
@ECHO ON
@echo "Directory %ORACLE_HOME%\network\log not found..."
@GOTO QUIT_WITH_MESSAGE

:QUIT_WITH_MESSAGE
@ECHO ON
@echo Usage: %0 LSNR
@echo where LSNR is full name of listener name in current %ORACLE_HOME% home
@echo Information:
@echo 'LSNR' value is case insensitive!
GOTO END

:ERR
@ECHO ON
@echo "There was some errors. Please look in directory %ORACLE_HOME%\network\log for some problem files."
@echo "Recheck lsnrctl configuration manually!"
GOTO END

:END
@ENDLOCAL

Cheers!

Rotate listener log (TNS-12547, TNS-12560, TNS-00517)

The problem

On many 32 bit OS platforms (Linux and Windows as well) you may face error after starting listener.

TNS-12547:TNS:lost contact
TNS-12560:TNS:protocol adapter error
TNS-00517:lost contact
Intel SVR4 UNIX Error: 32: Broken pipe

The main cause of error like this is 2 GB file limitation which listener.log has succeeded and cannot grow any longer.

The solution

Solution is to rotate listener log. For that i use very easy but useful bash script.

#!/bin/bash

# Works for all Oracle databases pre-11g 
# 11g have changes in a way that introduce universal "diagnostic_dest" parameter
# which is not cover in this solution and will produce an error 'Directory not found'

function quit_with_message {
  echo "Usage: ${0} LSNR";
  echo "       where LSNR is full name of listener name in current ${ORACLE_HOME} home" 
  echo "Information:"  
  echo "       'LSNR' value is case insensitive!"
  echo "  "
  exit 1
  exit;
} 

# Make Sure Listener name is passed
if [ -z "${1}" ]; then 
  quit_with_message;
fi; 

# Set the Environment
DATUM=`date '+%Y%m%d'`; export DATUM
VRIJEME=`date '+%H%M%S'`;export VRIJEME
export NOW=${DATUM}_${VRIJEME};

# We need upper and lower case value of listener name
LSNR=`echo ${1} | tr '[:lower:]' '[:upper:]'`
lsnr=`echo ${1} | tr '[:upper:]' '[:lower:]'`

date ; 

if [ -d ${ORACLE_HOME}/network/log ]; then
  if [ -f ${ORACLE_HOME}/network/log/${lsnr}.log ]; then
    ${ORACLE_HOME}/bin/lsnrctl << EOF!
    set current_listener ${LSNR}
    set log_file temp.log
EOF!
    cat ${ORACLE_HOME}/network/log/${lsnr}.log >> ${ORACLE_HOME}/network/log/${lsnr}.log.$NOW ;    

    rm ${ORACLE_HOME}/network/log/${lsnr}.log ;

    ${ORACLE_HOME}/bin/lsnrctl << EOF!
    set current_listener ${LSNR}
    set log_file ${lsnr}.log
EOF!

# add content which has been logged in the meantime
    cat ${ORACLE_HOME}/network/log/temp.log >> $ORACLE_HOME/network/log/${lsnr}.log.${NOW} ;
    rm ${ORACLE_HOME}/network/log/temp.log ;
  else
    echo "File ${ORACLE_HOME}/network/log/${lsnr}.log not found...";
    echo "  "
    quit_with_message ;
  fi ;
else
  echo "Directory ${ORACLE_HOME}/network/log not found...";
  echo "  "
  quit_with_message ;
fi ;

date

Run this script periodically (through crontab in period that suit your log size) or on demand.

As you can see in header comment, this script is for listeners configuration that rely on (and included) 10 version. To get listener version perform with set environement:

[oracle HACT2@iishacrac2 ~]$ lsnrctl version

LSNRCTL for Linux IA64: Version 10.2.0.4.0 - Production on 24-MAY-2010 15:05:46

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNSLSNR for Linux IA64: Version 10.2.0.4.0 - Production
        TNS for Linux IA64: Version 10.2.0.4.0 - Production
        Unix Domain Socket IPC NT Protocol Adaptor for Linux IA64: Version 10.2.0.4.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux IA64: Version 10.2.0.4.0 - Production
        TCP/IP NT Protocol Adapter for Linux IA64: Version 10.2.0.4.0 - Production,,
The command completed successfully
[oracle HACT2@iishacrac2 ~]$

The end

When I said "on demand" usage, I though situation when you need to test results of new listener configuration. This change is for instance, when you involve TAF, LB or secure listener and want to monitor new changes. For such an cases it is really good habit to separate logs for future comparation.

Because "You asked for it" I have added Windows version with same functionality. Hope now is all covered.

2018.05.23 addition

While running a script you may get an error like:

LSNRCTL> set log_file temp.log
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=somelinux)(PORT=1234)))
TNS-12508: TNS:listener could not resolve the COMMAND given

This is because you have set in listener.ora

ADMIN_RESTRICTIONS_LISTENER_SOMEDB = on

which place some restrictions in lsnrctl command line.

Cheers!

Blocking session ... detect, unblock, notify (Part II)

In my first post on blocking session problem (Blocking session ... detect, unblock, notify) I have shown how to handle these problems pretty clear and, from mine point of view, easy to understand. Recent events on one RAC database and little less experienced DBA, force me to make some updates on this subject to avoid all possible traps that mine solution may have in explanation.

The problem

In the middle of one day on one RAC database (3 nodes), "sb" procedure had output like:

SQL> @sb
Oracle version: 10.1.0.4.0 (10.1.0.4.0)
Blocker         Inst  SID     Serial    [sec]   Lock Type     Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction
       ZLSA        2   372           126     1128     Exclusive 
        SLPE        2   368            48     6836     Exclusive 
        DRI2        3   237           232     6727     Exclusive 
2. DRI2          3     237         232     6724   Transaction 
        DRIA        2   355           195      216     Exclusive  
        NESE        3   114            80     5527     Exclusive  
3. NESE          3     114          80     5521   Transaction
        DRI2        2   379           328      388   Share-Table 
        DRI2        3   115           269     5439   Share-Table 
        DRI2        3   423           619     3529     Exclusive 
        DRI2        2   373           108     5952   Share-Table 
        NESE        1   303          2811     4870     Exclusive 
        DRLO        3   205           749     2862     Exclusive 
        NESE        3   146           220     4766     Exclusive 
        
To kill first from the list, perform:
        
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '282,6661' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '282,6661' IMMEDIATE;
        
        
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''282,6661'''' IMMEDIATE''; end; '
,instance=>1
); 
  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

Previously mentioned DBA, was using mine solution for a long time without any problems and like in many situation before, submit a job like it was advised.

After some time when he checked blockers with "sb" procedure again, he was astonished that procedure produce the same output like before-like nothing happened. Without thinking why, he submit job again ... and later once again and after third submit that results no "unblocking", he came to me and ask for help.

The analyze part

Like in many situation like this, checking instance alert log file (this is RAC database) is the first step that DBA should do. It's content has at the end entries like:

ORA-12012: error on auto execute of job 1241
ORA-00030: User session ID does not exist.
ORA-06512: at line 1

Checking of dba_jobs shows me that 2 jobs are trying to execute again and again to perform "kill session" for the session that doesn't exist!

Logical question was "How session doesn't exist if "sb" is still showing it? "

After checking, answer was very easy-because of bug in it's logic!

If you look in code of "sb" procedure, you'll find this part in definition of blocking session:

CURSOR c_w IS 
  SELECT  gvw.sid waiter_sid, 
          gvw.inst_id waiter_inst_id 
  FROM gv$lock gvb, gv$lock gvw, gv$session gvs 
  WHERE (gvb.id1, gvb.id2) in ( 
                               SELECT id1, id2 FROM gv$lock WHERE request=0 
                               INTERSECT 
                               SELECT id1, id2 FROM gv$lock WHERE lmode=0 
                              ) 
    AND gvb.id1=gvw.id1 
    AND gvb.id2=gvw.id2 
    AND gvb.request=0 
    AND gvw.lmode=0 
    AND gvb.sid=gvs.sid 
    AND gvb.inst_id=gvs.inst_id 
ORDER BY gvb.CTIME desc ;

All looks OK (and in fact it is!!) but in some real cases, like this one was, it was not properly used. Later in code I loop through that cursor without checking status column (that I do not have at all!) of that session and shows all sessions that are blocked by every cursor entry. So shortly, it was wrong because this cursors shows all sessions regardless their status (which can be "ACTIVE", "INACTIVE", "KILLED" etc.) and some of them might get STATUS = 'KILLED'!
Beside this there was a problem with waiters that are mixed because there was no blockers SID passed as an parameter. So this is why I completely removed c_w cursor and put all in one c_waiters which has two parameters:

CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
      nvl(username,'Blocker ???') waiter_user, 
      gvl.sid, 
      gvs.serial# serial, 
      gvl.inst_id, 
      gvl.ctime , 
      gvs.status STATUS,
      module,
      decode(gvl.request, 
                         0, 'None',
                         1, 'NoLock',
                         2, 'Row-Share',
                         3, 'Row-Exclusive',
                         4, 'Share-Table',
                         5, 'Share-Row-Exclusive',
                         6, 'Exclusive',
                            'Nothing-'
      ) lock_req,
      action
     FROM gv$lock gvl, 
          gv$session gvs
    WHERE gvl.request>0
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid
      AND gvl.inst_id=p_blockers_waiter_inst_id
  ORDER BY gvl.CTIME desc
  ;

And this is exactly what happened in this case. First submitted job really killed that blocking session ("mark for kill") and other two hasn't find anything to kill so they were trying to execute and after some time gone to broken status after n retries.

To make more clear, let me show you the same situation with added STATUS field:

Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
        ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    ACTIVE
        DRIA        2   355           195      216     Exclusive     ACTIVE
        NESE        3   114            80     5527     Exclusive     ACTIVE
3. NESE          3     114          80     5521   Transaction    ACTIVE
        DRI2        2   379           328      388   Share-Table     ACTIVE
        DRI2        3   115           269     5439   Share-Table     ACTIVE
        DRI2        3   423           619     3529     Exclusive     ACTIVE
        DRI2        2   373           108     5952   Share-Table     ACTIVE
        NESE        1   303          2811     4870     Exclusive     ACTIVE
        DRLO        3   205           749     2862     Exclusive     ACTIVE
        NESE        3   146           220     4766     Exclusive     ACTIVE

Now this all looks reasonable...but...then come the second bug. The second bug in sb procedure was that "kill statement" was always constructed from first blocker in list. And this was again wrong because this session may be killed and then this kill command would produce an error!

The solution

When I realize that, I rewrite a new sb procedure:

/* ---------------------------------------------------------------------------
 Filename: check_bloking_session.prc
 CR/TR#  : 
 Purpose : Find blocking sessions and create release statements for RAC and non RAC environment  

 Date    : 08.07.2005.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g only (should work on 11g as well and lower versions)

 Changes (DD.MM.YYYY, Name, CR/TR#):
          11.12.2009, Damir Vadas
                      added p_to and p_cc as parameters to avoid bad header encodding on hac server
                      (no success!) 
          30.12.2009, Damir Vadas
                      added checking (LENGTH(s_mail_text)+LENGTH(p_text)+1 <= 4000)
                      because "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
          06.01.2010, Damir Vadas
                      added 32k size of message and exception log handler
          30.04.2010, Damir Vadas
                      fixed bug with showing last blocker not first one in kill statement
                      added STATUS column to show if session is killed (needed for previous fixed)
          01.09.2010, Damir Vadas
                      fixed bug blockers and waiters to be 11g compatible
--------------------------------------------------------------------------- */ 
set serveroutput on size 123456
set linesize 140
set pagesize 100
set trimout on

declare
  cursor c_blockers IS
    SELECT DISTINCT 
       nvl(username,'BLOCKER ???') blocker_user, 
       gvl.sid, 
       gvs.serial# serial, 
       gvl.inst_id, 
       gvl.ctime ,
       gvs.status STATUS,      
       module, 
       action,
       decode(gvl.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-'
       ) lock_type
     FROM gv$lock gvl, 
          gv$session gvs
    WHERE gvl.request=0
      AND gvl.block=1
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
  ORDER BY CTIME desc
  ;  
  
  CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
      nvl(username,'WAITER ???') waiter_user, 
      gvl.sid, 
      gvs.serial# serial, 
      gvl.inst_id, 
      gvl.ctime , 
      gvs.status STATUS,
      module,
      decode(gvl.request, 
                         0, 'None',
                         1, 'NoLock',
                         2, 'Row-Share',
                         3, 'Row-Exclusive',
                         4, 'Share-Table',
                         5, 'Share-Row-Exclusive',
                         6, 'Exclusive',
                            'Nothing-'
      ) lock_req,
      action
     FROM gv$lock gvl, 
          gv$session gvs
    WHERE gvl.request>0
      AND gvl.sid=gvs.sid
      AND gvl.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid
      AND gvl.inst_id=p_blockers_waiter_inst_id
  ORDER BY gvl.CTIME desc
  ;

  bHasAny  boolean;
  i        NUMBER := 0;
  v_sid    NUMBER := 0;
  v_serial NUMBER := 0;
  v_first_sid    NUMBER := -1;
  v_first_serial NUMBER := -1;
  v_instance        BINARY_INTEGER := 0;
  v_first_instance  BINARY_INTEGER := 0;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  s_job_command     VARCHAR2 (4000);
  s_mail_text       VARCHAR2 (32000);
  s_subject         VARCHAR2 (256);
  s_smtp_out_server VARCHAR2 (256);
  s_host_name       v$instance.host_name%TYPE;
  s_db_name         v$database.name%TYPE;
  s_instance_name   v$instance.instance_name%TYPE;
  s                 VARCHAR2(1024);
  
  PROCEDURE add_message_line (p_text VARCHAR2) is
  BEGIN
    -- for proceduire version with mail part
    -- s_mail_text := s_mail_text || p_text || chr(10);     
    dbms_output.put_line (p_text);
  END;
  
begin
  dbms_utility.db_version(db_ver,db_ver2);
  add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  bHasAny := FALSE;  
  FOR v_blockers in c_blockers loop
    bHasAny := TRUE;     
    IF (i=0) THEN                  
      add_message_line (rpad('Blocker',12,' ')||'  '||'  Inst  '||rpad('SID',6,' ')||'  '||rpad('Serial',8,' ')||'  '||'  '||rpad('[sec]',6,' ')||' '|| 
                        lpad('Lock Type',10,' ')||' '|| lpad('Status',14, ' ') || lpad('Module',13,' ')
                        );
      add_message_line (rpad('-',120,'-'));
    END IF;
    v_sid := v_blockers.sid;
    v_serial := v_blockers.serial;
    v_instance := v_blockers.inst_id ;
    -- only first blocker (who is not killed!) is important!
    IF v_blockers.status != 'KILLED' AND v_first_sid = -1 THEN
      v_first_sid := v_blockers.sid;
      v_first_serial := v_blockers.serial;
      v_first_instance := v_blockers.inst_id ;
    END IF;
    add_message_line (LPAD(to_char(i+1),3,' ')||'. '||rpad(v_blockers.blocker_user,12,' ')||'  '||rpad(v_blockers.inst_id,4,' ')||'  '||rpad(v_blockers.SID,8,' ' )||'  '||lpad(v_blockers.serial,5,' ')||'  '||lpad(v_blockers.CTIME,7,' ')||' '||
                      lpad(v_blockers.lock_type,13,' ')||lpad(v_blockers.status,14,' ')||lpad(nvl(v_blockers.module,'?'),15,' ')
                     );
    FOR v_waiters IN c_waiters (v_blockers.sid,v_blockers.inst_id ) LOOP 
      add_message_line (lpad(chr(9),9)||rpad(v_waiters.waiter_user,10,' ')||'  '||rpad(to_char(v_waiters.inst_id),4,' ')||rpad(to_char(v_waiters.sid),4,' ')|| ' '||lpad(to_char(v_waiters.serial),12,' ')||' ' ||' '|| 
                  ' ' ||lpad(to_char(v_waiters.ctime),6,' ')||' '|| lpad(to_char(v_waiters.lock_req),13,' ')||' '|| lpad(v_blockers.status,14,' ')|| ' '|| lpad(to_char(nvl(v_waiters.module,'?')),15,' ')
                  );      
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  -- show any kind of blocker (killed one also...as info)
  IF bHasAny THEN
    -- show blocking rows info
    -- add_message_line (chr(9));
    -- show_blocked_records (s_blocked_rows);
    -- dbms_output.put_line(s_blocked_rows);
    -- add_message_line (s_blocked_rows);  
    -- for blockers that are valid (not killed!) create kill statement
    IF ( v_first_sid > 0 ) THEN
      SELECT instance_name, host_name INTO s_instance_name, s_host_name FROM v$instance;
      SELECT name INTO s_db_name FROM v$database; 
      add_message_line (chr(9));
      add_message_line ('To kill first from the list, perform: ');
      add_message_line (chr(9));
      IF db_ver like '10%' THEN
        add_message_line ('NON RAC (or RAC logged on that node):');
        add_message_line ('---------------------------------');
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line (chr(9));
        add_message_line (chr(9));
        s := '''begin execute immediate ''''ALTER SYSTEM KILL SESSION '''''''''||v_first_sid||','||v_first_serial||''''''''' IMMEDIATE''''; end; ''';     
        add_message_line ('RAC (logged on any node) :');
        add_message_line ('--------------------------');
        s_job_command := 'declare'|| chr(10)||
                         '  v_job binary_integer;'|| chr(10)|| 
                         'begin'|| chr(10)||
                         '  DBMS_JOB.submit ('||
                         ' job     =>v_job'||chr(10)||
                         ',what    =>'||s||chr(10)||
                         ',instance=>'||v_first_instance||chr(10)||
                         '); '||chr(10)||
                         '  commit;'||chr(10)||
                         'end;'|| chr(10) ||
                         '/'
        ;
        add_message_line (s_job_command);
      ELSIF db_ver like '11%' THEN
        add_message_line (chr(9));
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');    
      ELSE
        add_message_line (chr(9));
        add_message_line ('Untested version... Try to logon as priveledged user on node ('||s_host_name||') and perform:');
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');    
      END IF; 
    ELSE
      add_message_line ('All valid blocker sessions allready killed. Try kill with OS level command!');
    END IF;
  ELSE
    add_message_line ('No blocking sessions found!');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- auto_log_error_core('"check_blocking_session" procedure. ');
    null;
end;
/

Now when you run the sb procedure, the same situation will look like:

SQL> @sb
Oracle version: 10.1.0.4.0 (10.1.0.4.0)
Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
       ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    ACTIVE
        DRIA        2   355           195      216     Exclusive     ACTIVE
        NESE        3   114            80     5527     Exclusive     ACTIVE
3. NESE          3     114          80     5521   Transaction    ACTIVE
        DRI2        2   379           328      388   Share-Table     ACTIVE
        DRI2        3   115           269     5439   Share-Table     ACTIVE
        DRI2        3   423           619     3529     Exclusive     ACTIVE
        DRI2        2   373           108     5952   Share-Table     ACTIVE
        NESE        1   303          2811     4870     Exclusive     ACTIVE
        DRLO        3   205           749     2862     Exclusive     ACTIVE
        NESE        3   146           220     4766     Exclusive     ACTIVE
        
To kill first from the list, perform:
        
NON RAC (or RAC logged on that node):
---------------------------------
ALTER SYSTEM DISCONNECT  SESSION '237,232' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '237,232' IMMEDIATE;
        
        
RAC (logged on any node) :
--------------------------
declare
  v_job binary_integer;
begin
  DBMS_JOB.submit ( job     =>v_job
,what    =>'begin execute immediate ''ALTER SYSTEM KILL SESSION
''''237,232'''' IMMEDIATE''; end; '
,instance=>3
); 
  commit;
end;
/

PL/SQL procedure successfully completed.

SQL>

So it would construct kill statement for second blocker and skip first blocker which is "marked for kill". And now this is regular.

Kill "marked for kill"

If you look in oracle theory, "KILLED" status does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill. If the marked session persists for some time you may consider killing the process at the operating system level.

So practically it was still running in Oracle and the only way to immediately kill this session was to perform from OS level command!

Just to remind you differences between DISCONNECT and KILL SESSION

• IMMEDIATE clause with KILL SESSION
  instruct Oracle to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.

• IMMEDIATE clause with DISCONNECT SESSION
  disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.

If you are on Linux this action is peace of cake but on Windows ... good luck with "orakill" command, command which in most of the cases doesn't kill at all!

Be proactive

According this do not forget to replace other part of solution (proactive checking of blocking session-"check_blocking_session.prc") which I place in job with 5 minutes schedule and run automatically. Here is final version for that cause.

CREATE OR REPLACE procedure check_blocking_session (
/* $Header: check_blocking_session.prc 1.12 10/19/2010 20:45 damirv $ */
/* ---------------------------------------------------------------------------

           Copyright(C) 1995-2009 TEB Informatika d.o.o.


 Filename: check_blocking_session.prc
 CR/TR#  : 
 Purpose : Find blocking sessions and create release statements for RAC and non RAC environment  

 Date    : 08.07.2005.
 Author  : Damir Vadas, damir.vadas@gmail.com
 
 Remarks : Tested on 10g only (should work on 11g as well and lower versions)

 Changes (DD.MM.YYYY, Name, CR/TR#):
          11.12.2009, Damir Vadas
                      added p_to and p_cc as parameters to avoid bad header encodding on hac server
                      (no success!) 
          30.12.2009, Damir Vadas
                      added checking (LENGTH(s_mail_text)+LENGTH(p_text)+1 <= 4000)
                      because "ORA-06502: PL/SQL: numeric or value error: character string buffer too small"
          06.01.2010, Damir Vadas
                      added 32k size of message and exception log handler
          30.04.2010, Damir Vadas
                      fixed bug with showing last blocker not first one in kill statement
                      added STATUS column to show if session is killed (needed for previous fixed)
          01.09.2010, Damir Vadas
                      fixed bug blockers and waiters to be 11g compatible
          20.09.2010, Damir Vadas
                      Reunited logic for script/procedure
          19.10.2010, Damir Vadas
                      Removed p_smtp_out_server VARCHAR2 DEFAULT 'mail.iskon.hr' declaration
--------------------------------------------------------------------------- */
                                        p_sender          VARCHAR2 DEFAULT 'dba@teb-informatika.hr',
                                        p_to              VARCHAR2 DEFAULT 'damir.vadas@teb-informatika.hr',
                                        p_cc              VARCHAR2 DEFAULT null,
                                        p_subject         VARCHAR2 DEFAULT 'Blocking session occur!',
                                        p_send_mail       BOOLEAN  DEFAULT TRUE)
AS
  b_is_script BOOLEAN DEFAULT FALSE;
  CURSOR c_blockers IS
    SELECT DISTINCT 
           NVL(username,'BLOCKER ???') blocker_user, 
           gvb.sid, 
           gvs.serial# serial, 
           gvb.inst_id, 
           gvb.ctime ,
           gvs.status STATUS,      
           module, 
           action,
           NVL (
             decode(gvb.type, 
                       'AE', 'Edition Lock',                                          -- Prevent Dropping an edition in use 11g
                       'BL','Buffer hash table',
                       'CF','Control File Transaction',
                       'CI','Cross Instance Call',
                       'CS','Control File Schema',
                       'CU','Bind Enqueue',
                       'DF','Data File',
                       'DL','Direct-loader index-creation',
                       'DM','Mount/startup db primary/secondary instance',
                       'DR','Distributed Recovery Process',
                       'DX','Distributed Transaction Entry',
                       'FI','SGA Open-File Information',
                       'FS','File Set',
                       'IN','Instance Number',
                       'IR','Instance Recovery Serialization',
                       'IS','Instance State',
                       'IV','Library Cache InValidation',
                       'JQ','Job Queue',
                       'KK','Redo Log "Kick"',
                       'LS','Log Start/Log Switch',
                       'MB','Master Buffer hash table',
                       'MM','Mount Definition',
                       'MR','Media Recovery',
                       'PF','Password File',
                       'PI','Parallel Slaves',
                       'PR','Process Startup',
                       'PS','Parallel Slaves Synchronization',
                       'RE','USE_ROW_ENQUEUE Enforcement',
                       'RT','Redo Thread',
                       'RW','Row Wait',
                       'SC','System Commit Number',
                       'SH','System Commit Number HWM',
                       'SM','SMON',
                       'SQ','Sequence Number',
                       'SR','Synchronized Replication',
                       'SS','Sort Segment',
                       'ST','Space Transaction',
                       'SV','Sequence Number Value',
                       'TA','Transaction Recovery',
                       'TD','DDL enqueue',
                       'TE','Extend-segment enqueue',
                       'TM','DML enqueue',
                       'TO', 'Temp Object',                                              --Sync DDL and DML operations on a temp object
                       'TS','Temporary Segment',
                       'TT','Temporary Table',
                       'TX','Transaction',
                       'UL','User-defined Lock',
                       'UN','User Name',
                       'US','Undo Segment Serialization',
                       'WL','Being-written redo log instance',
                       'WS','Write-atomic-log-switch global enqueue',
                       'XA','Instance Attribute',
                       'XI','Instance Registration', 
                       decode(substr(gvb.type,1,1),
                                   'L','Library Cache     ('||substr(gvb.type,2,1)||')', -- Library cache lock instance lock (A..P = namespace)
                                   'N','Library Cache Pin ('||substr(gvb.type,2,1)||')', -- Library cache pin instance (A..Z = namespace)
                                   'Q','Row Cache         ('||substr(gvb.type,2,1)||')', -- Row cache instance (A..Z = cache)
                                   '????'
                       ),
                       'Bl. lt:"'||gvb.type||'"' 
                   ), 'NULL Block Type'
           ) lock_type
     FROM gv$lock gvb, gv$lock gvw, gv$session gvs
    WHERE (gvb.id1, gvb.id2) in (
                                 SELECT id1, id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1, id2 FROM gv$lock WHERE lmode=0
                                )
      AND gvb.id1=gvw.id1
      AND gvb.id2=gvw.id2
      AND gvb.request=0
      AND gvw.lmode=0
      AND gvb.sid=gvs.sid
      AND gvb.inst_id=gvs.inst_id
  ORDER BY CTIME desc
  ;
  
  -- waiters 
  CURSOR c_waiters (p_blocker_waiter_sid gv$lock.sid%TYPE, p_blockers_waiter_inst_id gv$lock.inst_id%TYPE) IS
    SELECT 
           NVL(username,'WAITER ???') waiter_user, 
           gvs.sid, 
           gvs.serial# serial, 
           gvs.inst_id, 
           gvw.ctime , 
           gvs.status STATUS,
           module,
           action,
           decode(gvw.request, 
                              0, 'None',
                              1, 'NoLock',
                              2, 'Row-Share',
                              3, 'Row-Exclusive',
                              4, 'Share-Table',
                              5, 'Share-Row-Exclusive',
                              6, 'Exclusive',
                                 'Nothing-'
           ) lock_req
     FROM gv$lock gvw, gv$session gvs
    WHERE 1=1
      AND gvw.request>0
      AND gvw.lmode=0
      AND gvw.sid=gvs.sid
      AND gvw.inst_id=gvs.inst_id
      AND gvs.blocking_session=p_blocker_waiter_sid 
      AND gvs.blocking_instance=p_blockers_waiter_inst_id
  ORDER BY CTIME desc
  ;
  
  bHasAny  boolean;
  i        NUMBER := 0;
  v_sid    NUMBER := 0;
  v_serial NUMBER := 0;
  v_first_sid    NUMBER := -1;
  v_first_serial NUMBER := -1;
  v_instance        BINARY_INTEGER := 0;
  v_first_instance  BINARY_INTEGER := 0;
  db_ver            VARCHAR2(128);
  db_ver2           VARCHAR2(128);  
  s_job_command     VARCHAR2 (4000);
  s_mail_text       VARCHAR2 (32000);
  s_subject         VARCHAR2 (256);
  s_smtp_out_server VARCHAR2 (256);
  s_host_name       v$instance.host_name%TYPE;
  s_db_name         v$database.name%TYPE;
  s_instance_name   v$instance.instance_name%TYPE;
  s                 VARCHAR2(1024);
  
  s_blocked_rows    VARCHAR2(4000);
  
  l_temp            VARCHAR2(64);

  FUNCTION add_indent_char RETURN VARCHAR2 
  IS
  BEGIN
    IF b_is_script THEN
      RETURN chr(255);
    ELSE
      RETURN chr(9);
    END IF;
  END;

  PROCEDURE add_message_line (p_text VARCHAR2) is
  BEGIN
    dbms_output.put_line (p_text);
    s_mail_text := s_mail_text || p_text || chr(10);     
  END;
  
BEGIN
  b_is_script := NOT p_send_mail;
  add_message_line (chr(9));
  dbms_utility.db_version(db_ver,db_ver2);
  add_message_line ('Oracle version: '||db_ver|| ' ('||db_ver2||')');
  dbms_output.put_line (chr(9)); 
  bHasAny := FALSE;  
  FOR r_blockers in c_blockers loop
    bHasAny := TRUE;     
    IF (i=0) THEN       
      add_message_line (chr(9));    
      add_message_line (rpad('Blocker',17,' ')||
                        lpad('Inst',4,' ')||
                        lpad('SID',7,' ')||
                        lpad('Serial',8,' ')||
                        lpad('[sec]',10,' ')|| 
                        lpad('Lock Type/Req. lock',30,' ')||
                        lpad('Status',13, ' ') ||
                        lpad('Module',25,' ')
                        );
      add_message_line (rpad('-',120,'-'));
    END IF;
    v_sid := r_blockers.sid;
    v_serial := r_blockers.serial;
    v_instance := r_blockers.inst_id ;
    -- only first blocker (who is not killed!) is important!
    IF r_blockers.status != 'KILLED' AND v_first_sid = -1 THEN
      v_first_sid := r_blockers.sid;
      v_first_serial := r_blockers.serial;
      v_first_instance := r_blockers.inst_id ;
    END IF;
    -- print blockers sessions
    add_message_line (rpad((to_char(i+1)||'.'||r_blockers.blocker_user),17,' ')||
                      lpad(r_blockers.inst_id,4,' ')||
                      lpad(r_blockers.SID,7,' ' )||
                      lpad(r_blockers.serial,8,' ')||
                      lpad(r_blockers.CTIME,10,' ')||
                      lpad(r_blockers.lock_type,30,' ')||
                      lpad(r_blockers.status,13,' ')||
                      lpad(nvl(r_blockers.module,'?'),25,' ')
                     );
    -- print waiters session for current blocker
    FOR r_waiters IN c_waiters (r_blockers.sid ,r_blockers.inst_id) LOOP 
      IF b_is_script THEN
        l_temp := add_indent_char; -- || add_indent_char;
      ELSE
        l_temp := add_indent_char;
      END IF;
      add_message_line (l_temp||
                        rpad(r_waiters.waiter_user,17,' ')||
                        lpad(r_waiters.inst_id,4,' ')||
                        lpad(r_waiters.sid,7,' ')||
                        lpad(r_waiters.serial,8,' ')||
                        lpad(r_waiters.ctime,10,' ')||
                        lpad(r_waiters.lock_req,30,' ')|| 
                        lpad(r_waiters.status,13,' ')|| 
                        lpad(nvl(r_waiters.module,'?'),25,' ')
                  );      
    END LOOP;
    IF bHasAny THEN
      i:=i+1;
    END IF;
  END LOOP;
  
  IF (v_first_serial = 1 ) THEN
    add_message_line (chr(9));
    add_message_line ('Warning!');
    add_message_line ('-------------------------------------------------------------------------------');
    add_message_line ('Blocking sessions with serial=1 are core ORACLE PROCESSES.');
    add_message_line ('Killing them without REAL NEED and DOUBLE CHECKING may dangerous your instance!');
    add_message_line ('-------------------------------------------------------------------------------');
    add_message_line (chr(9));
  END IF;
  -- show any kind of blocker (killed one also...as info)
  IF bHasAny THEN
    IF NOT b_is_script THEN
      -- show blocking rows info
      add_message_line (chr(9));
      add_message_line ('---------------  Blocking records information  ---------------');
      add_message_line (chr(9));
      show_blocked_records (s_blocked_rows);
      IF s_blocked_rows is null THEN
        add_message_line(chr(9)||chr(9)||'No locked records found.');
      ELSE
        s_mail_text := s_mail_text || s_blocked_rows || chr(10);   
      END IF;
    END IF;
    -- for blockers that are valid (not killed!) create kill statement
    SELECT instance_name, host_name INTO s_instance_name, s_host_name FROM v$instance;
    SELECT name INTO s_db_name FROM v$database; 
    s_subject := p_subject || '  '|| s_db_name || '('|| s_instance_name || ') on ' || s_host_name ||''; 
    add_message_line (chr(9));
    IF (v_first_sid > 0 ) THEN
      add_message_line ('To kill first from the list, perform: ');
      add_message_line (chr(9));
      IF db_ver like '10%' THEN
        add_message_line ('NON RAC (or RAC logged on that node):');
        add_message_line ('---------------------------------');
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line (chr(9));
        add_message_line (chr(9));
        s := '''begin execute immediate ''''ALTER SYSTEM DISCONNECT SESSION '''''''''||v_first_sid||','||v_first_serial||''''''''' IMMEDIATE''''; end; ''';     
        add_message_line ('RAC (logged on any node) :');
        add_message_line ('--------------------------');
        s_job_command := 'declare'|| chr(10)||
                         '  v_job binary_integer;'|| chr(10)|| 
                         'begin'|| chr(10)||
                         '  DBMS_JOB.submit ('||
                         ' job     =>v_job'||chr(10)||
                         ',what    =>'||s||chr(10)||
                         ',instance=>'||v_first_instance||chr(10)||
                         '); '||chr(10)||
                         '  commit;'||chr(10)||
                         'end;'|| chr(10) ||
                         '/'
        ;
        add_message_line (s_job_command);
      ELSIF db_ver like '11%' THEN
        add_message_line (chr(9));
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||',@'||v_first_instance||''' IMMEDIATE;');    
      ELSE
        add_message_line (chr(9));
        add_message_line ('Untested version... Try to logon as priveledged user on node ('||s_host_name||') and perform:');
        add_message_line ('ALTER SYSTEM DISCONNECT  SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');
        add_message_line ('ALTER SYSTEM KILL        SESSION '''||v_first_sid||','||v_first_serial||''' IMMEDIATE;');    
      END IF;
    ELSE
      add_message_line ('All valid blocker sessions allready killed. Try kill with OS level command!');
    END IF; 
    IF NOT b_is_script THEN
      s_mail_text := s_mail_text ||  (chr(9)) || chr(10);
      s_mail_text := s_mail_text ||  ('This is automated message so, do not reply to this mail.') || chr(10);
      s_mail_text := s_mail_text ||  ('Regards,')|| chr(10);
      s_mail_text := s_mail_text ||  ('Your auto DBA d.o.o.')|| chr(10);
      s_mail_text := s_mail_text ||  (CHR(9));
      select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') into s from dual;
      s_mail_text := s_mail_text ||  ('Zagreb, '||s);
      -- if you run this script from sqlplus then there is no need for this (p_send_mail=FALSE)
      IF p_send_mail THEN
        SYS.UTL_MAIL.send(sender => p_sender,
                  recipients => p_to,
                          cc => p_cc,
                     subject => s_subject,
                     message => s_mail_text,
                   mime_type => 'text/plain; charset=us-ascii');
      END IF;
    END IF;
  ELSE
    dbms_output.put_line ('No blocking sessions found!');
  END IF;
/*
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
    */
END;
/

The end

If you get mail now for blocking session and all blocker sessions are killed, then you'll see output like this:

Blocker         Inst  SID     Serial    [sec]   Lock Type     Status       Module
----------------------------------------------------------------------------------------------------
1. ANAL          1     282        6661     7981   Transaction    KILLED
        ZLSA        2   372           126     1128     Exclusive     ACTIVE
        SLPE        2   368            48     6836     Exclusive     ACTIVE
        DRI2        3   237           232     6727     Exclusive     ACTIVE
2. DRI2          3     237         232     6724   Transaction    KILLED
        DRIA        2   355           195      216     Exclusive     ACTIVE

All valid blocker sessions already killed. Try kill with OS level command!

What is right and correct message!

For the end here is how this rewrite version produce result. Here is mail that you'll get in a case of blocking session:

Oracle version: 11.2.0.1.0 (11.1.0.0.0)

Blocker         Inst   SID    Serial    [sec]   Lock Type        Status          Module
------------------------------------------------------------------------------------------------------------------------
 1. DAMIRV        1     20        6       54   Transaction      INACTIVE         SQL*Plus
       DAMIRV      1     141       40       26     Exclusive       INACTIVE        SQL*Plus
 2. DAMIRV        1     141       4       42   Transaction        ACTIVE         SQL*Plus
       DAMIRV      1     144       58       16     Exclusive         ACTIVE        SQL*Plus

---------------  Blocking records information  ---------------
DAMIRV (1 '20,68')
               DAMIRV    1     141,40           26 sec       Exclusive         ACTIVE        SQL*Plus
               SELECT C1 FROM DAMIRV.X WHERE rowid = 'AAAVoXAAGAAAAS1AAA' ;
                       (delete x                                  )

DAMIRV (1 '141,40')
               DAMIRV    1     144,58           16 sec       Exclusive         ACTIVE        SQL*Plus
               SELECT C1 FROM DAMIRV.XX WHERE rowid = 'AAAVoYAAGAAAAS8AAA' ;
                       (delete xx where c1='1'                    )



To kill first from the list, perform:

ALTER SYSTEM DISCONNECT  SESSION '20,68,@1' IMMEDIATE;
ALTER SYSTEM KILL        SESSION '20,68,@1' IMMEDIATE;
--------------------------------------------------------
This is automated message so, do not reply to this mail.
Regards,
Your auto DBA d.o.o.

Zagreb, 21.05.2010 23:39:33

As you see now you can see which row is causing block and statement that is blocked in waiter session-looks promising!

Keep in mind that these two procedures are called in two separate calls, so sometimes second call may shown no blocking records ... and they do not exists in second case call and was existing in first time call.
Cheers!

DBMS_CRYPTO in action

DBMS_CRYPTO is well known package collection that are used in encrypting or hashing values in oracle database. This is true out of the box collection set that really help oracle people in many security tasks.

Regardless there are many nice topics on this subject (one of them is of course Database Security Enhancements in Oracle Database 10g about all important in this area), my intention is to show a little bit different approach for very common problem: encrypting column values and showing them fully, partially or not at all based on custom rule.

The problem

Let us suppose we have a table with some sensitive data we should protect somehow:

SQL>  DESC SEC_DEMO_TBL
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 A_USERNAME                                NOT NULL VARCHAR2(32)
 A_CREDIT_CARD_NUMBER                      NOT NULL VARCHAR2(32) 

SQL> insert into SEC_DEMO_TBL values (1, 'SMITH',  '0123456-789-0001');

1 row created.

SQL>  insert into SEC_DEMO_TBL values (2, 'PITT',  '0123456-789-0002');

1 row created.

SQL>  insert into SEC_DEMO_TBL values (3, 'JOLIE', '0123456-789-0003');

1 row created.

SQL>  commit;

Commit complete.

SQL> select * from SEC_DEMO_TBL;

        ID A_USERNAME                       A_CREDIT_CARD_NUMBER
---------- -------------------------------- --------------------------------
         1 SMITH                            0123456-789-0001
         2 PITT                             0123456-789-0002
         3 JOLIE                            0123456-789-0003

SQL> 

A_CREDIT_CARD_NUMBER is a column where now exists open credit card numbers. A task is to crypt it and show their values according to 4 types of users:

1. ordinary oracle user cannot see any part of A_CREDIT_CARD_NUMBER column at all!
2. '***********' to ordinary user (see stars only instead of cc number)
3. '******xxxx' to privileged user (see last four numbers of the cc number)
4. 'xxxx****xxxx' to power user (see first and last four numbers of cc number)
5. 'xxxxxxxxxxxxx' to data admin user (see complete cc number)

The implementation

The implementation is divided in next four parts:

1. Implement security policy that will allow four different outputs
2. Create core functions
3. Convert existing data to crypted values
4. Ensure that new incoming data are crypted as well
5. Test results

Implement security policy that will allow four different outputs

As an owner of the table, create view and public synonym on that view in a way that A_CREDIT_CARD_NUMBER will be masked with incomming function

SQL> create or replace force view SEC_DEMO_VW as (
  2    SELECT id, 
  3           a_username, 
  4           cc_security.decrypt_data(A_CREDIT_CARD_NUMBER) CC 
  5      FROM SEC_DEMO_TBL
  6  ); 

Warning: View created with compilation errors.

SQL> show errors;
No errors.

SQL> create or replace public synonym SEC_DEMO for SEC_DEMO_VW;

Synonym created.

SQL> 

Force view is nice oracle feature that allow us to create views based on objects that doesn't exist in the moment of creation. Regardless view is invalid it will become valid on first usage. Beside that masking practice (different public name from real object name) is always a good security feature.

Because I allways like oracle based security solutions, this approach will involve 4 different roles which should be grant to chosen users:

• SEC_CC_0_NUMBERS
• SEC_CC_4_NUMBERS
• SEC_CC_8_NUMBERS
• SEC_CC_ALL_NUMBERS

SQL> CREATE ROLE SEC_CC_0_NUMBERS IDENTIFIED BY VALUES '1F5C90D6079FF8AA';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_0_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_4_NUMBERS IDENTIFIED BY VALUES '3B078E03C4E9230E';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_4_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_8_NUMBERS IDENTIFIED BY VALUES '3F5493B72C7BFA18';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_8_NUMBERS', 'DEFAULT_CON
SUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> CREATE ROLE SEC_CC_ALL_NUMBERS IDENTIFIED BY VALUES 'DE1D7A90367C516C';

Role created.

SQL> BEGIN
  2    SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();
  3    SYS.DBMS_RESOURCE_MANAGER.create_pending_area();
  4    SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP ('SEC_CC_ALL_NUMBERS', 'DEFAULT_C
ONSUMER_GROUP', false);
  5    SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();
  6  END;
  7  /

PL/SQL procedure successfully completed.

It is very important to create rolas under privileged user who is trusted. In this case I have used owner of the table schema. And of course, secure rolas with password so altering that role can be done only from appropriate user.

For test we'll have 4 users:

1. A_USER_0 (granted "SEC_CC_0_NUMBERS" rola)
2. A_USER_4 (granted "SEC_CC_4_NUMBERS" rola)
3. A_USER_8 (granted "SEC_CC_8_NUMBERS" rola)
4. A_USER_ALL (granted "SEC_CC_ALL_NUMBERS" rola)

SQL> create user a_user_0 identified by a_user_0;

User created.

SQL> create user a_user_4 identified by a_user_4;

User created.

SQL> create user a_user_8 identified by a_user_8;

User created.

SQL> create user a_user_all identified by a_user_all;

User created.

SQL> grant create session to a_user_0;

Grant succeeded.

SQL> grant create session to a_user_4;

Grant succeeded.

SQL> grant create session to a_user_8;

Grant succeeded.

SQL> grant create session to a_user_all;

Grant succeeded.

SQL> 

As you can see user has no other privilege except create session. Then, grant appropriate roles to them:

SQL> grant sec_cc_0_numbers to a_user_0;

Grant succeeded.

SQL> grant sec_cc_4_numbers to a_user_4;

Grant succeeded.

SQL> grant sec_cc_8_numbers to a_user_8;

Grant succeeded.

SQL> grant sec_cc_all_numbers to a_user_all;

Grant succeeded.

SQL> 

Create core functions

When users and security part is done, next comes creation of core functions that we'll use. In this example I'll use one package with only two exposed functions encrypt_data and decrypt_data and this is all I need at all:

CREATE OR REPLACE PACKAGE cc_security
/*--------------------------------------------------------------------------------------------------------------------
 NAME    : cc_security
 PURPOSE : Utility which holds sensitive security data. MUST BE WRAPPED!

 Date    : 02.07.2009.
 Author  : Damir Vadas

 Remarks : owner of package must have grant execute on DBMS_CRYPTO package


 Changes (DD.MM.YYYY, Name, CR/TR#):
 -------------------------------------------------------------------------------------------------------------------- */
IS
    FUNCTION encrypt_data (
                           p_input_string      IN  VARCHAR2
                          ) RETURN VARCHAR2;

    FUNCTION decrypt_data (
                           p_input_string      IN  VARCHAR2
                           ) RETURN VARCHAR2;
END cc_security; 
/

Package body:

CREATE OR REPLACE PACKAGE BODY cc_security
IS
    -- This is most sensitive part because based on this seed all encrypting is done!
    -- Use some oracle random generator for it
    p_seed  VARCHAR2(24) := 'IFDXNJEQETNPTMSBICPSQXJK';
    p_seed_length PLS_INTEGER DEFAULT 24; 

    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
    
    -- Defined Encryption Methods:
    -- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher chaining, pad with zeroes 
    SH1_ECB_ZERO CONSTANT PLS_INTEGER := DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_OFB + DBMS_CRYPTO.PAD_ZERO;

/*
I allways prefer separate functions because of reusability code. So this function will return: 
   0  ordinary user - norights at all!
   4  privileged user with SEC_CC_4_NUMBERS role
   8  power user with SEC_CC_8_NUMBERS role
  16  admin user with SEC_CC_ALL_NUMBERS 
*/
function level_cc_user_rights (p_username IN DBA_USERS.USERNAME%TYPE
                               ) return INTEGER
IS
  l_retval INTEGER;
BEGIN
  SELECT decode (granted_role, 
                             'SEC_CC_4_NUMBERS',4,
                             'SEC_CC_8_NUMBERS',8,
                             'SEC_CC_ALL_NUMBERS',16,
                             0
                )
   INTO l_retval
   FROM dba_role_privs
   WHERE grantee=p_username;
  return l_retval;
EXCEPTION
  WHEN no_data_found then
    return -1;
  WHEN OTHERS THEN
    return -1;
END level_cc_user_rights;

FUNCTION encrypt_data (
                       p_input_string        IN  VARCHAR2                       
                      ) RETURN VARCHAR2
IS       
  converted_seed      RAW(128);
  converted_string    RAW(128);
  encrypted_string    RAW(128);
BEGIN
  -- Convert incoming string and supplied seed to RAW datatype using the 
  -- new UTLI18N package to convert the string to the AL32UTF8 character set
  converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');
  IF length(nvl(p_seed,'1'))<>p_seed_length THEN
    RETURN NULL;
  END IF;

  converted_seed   := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');

  -- Encrypt the RAW value using the ENCRYPT function and the appropriate
  -- encryption type
  encrypted_string := 
                     DBMS_CRYPTO.ENCRYPT(
                         src => converted_string
                        ,typ => SH1_ECB_ZERO
                        ,key => converted_seed
                        ,iv =>  NULL);
  RETURN to_char(encrypted_string);
EXCEPTION
  WHEN OTHERS THEN
    SQLERRMSG := SQLERRM;
    SQLERRCDE := SQLCODE;
    RETURN NULL;
END encrypt_data;

FUNCTION decrypt_data (
                       p_input_string        IN  VARCHAR2
                      ) RETURN VARCHAR2
IS
  converted_string    VARCHAR2(128);
  decrypted_string    VARCHAR2(128);
  converted_seed      RAW(64);
  l_user              VARCHAR2(32);
  l_cc_level          INTEGER;
BEGIN
  -- Convert incoming string and supplied seed to RAW datatype using the 
  -- new UTLI18N package to convert the string to the AL32UTF8 character
  -- set
  IF length(nvl(p_seed,'1'))<>p_seed_length THEN
    RETURN NULL;
  END IF;

  converted_string := UTL_I18N.STRING_TO_RAW(p_input_string, 'AL32UTF8');

  converted_seed   := UTL_I18N.STRING_TO_RAW(p_seed, 'AL32UTF8');
        
  -- Encrypt the RAW value using the ENCRYPT function and the appropriate
  -- encryption type
  decrypted_string := 
      DBMS_CRYPTO.DECRYPT(
           src => p_input_string
          ,typ => SH1_ECB_ZERO
          ,key => converted_seed
          ,iv =>  NULL);
        
  -- Convert incoming string to RAW datatype, using the UTLI18N package 
  -- to convert the string to the AL32UTF8 character set
  converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');

  -- Check if user is priviledged to decrypt data 
  SELECT sys_context('USERENV', 'SESSION_USER')
    INTO l_user 
    FROM dual;
  
  l_cc_level :=level_cc_user_rights(l_user);   
  IF l_cc_level=0 THEN
    -- ordinary user-shows only stars
    RETURN '***************';
  ELSIF l_cc_level=4 THEN
    -- privileged user-shows only 4 last digits...
    IF LENGTH(converted_string) <= 4 THEN
      RETURN NULL;
    ELSE
      RETURN '***********'|| SUBSTR(converted_string,length(converted_string)-3,4);
    END IF;
  ELSIF l_cc_level=8 THEN
    -- power user-shows only first and last 4 digits...
    IF LENGTH(converted_string) <= 8 THEN
      RETURN NULL;
    ELSE
      RETURN SUBSTR(converted_string,1,4) || '*******'|| SUBSTR(converted_string,length(converted_string)-3,4);
    END IF;  
  ELSIF l_cc_level=16 THEN
    -- admin user-show all!
    RETURN converted_string;
  ELSE
    -- This is for all "classic oracle users" they see null!
    RETURN null;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    SQLERRMSG := SQLERRM;
    SQLERRCDE := SQLCODE;
    RETURN NULL;
END decrypt_data;

END cc_security;
/

For this part of task I strongly suggest to use ready made oracle functions from DBMS_CRYPTO package because they are fully tested and provide full compatibility to any future version and any kind of database upgrade.
Keep in mind that this package body MUST BE WRAPPED because it holds the most sensitive data which should not be available even to DBA's!

As you remember we have created view which now can be completed with grant select for previously created users. So as an table owner perform:

SQL> grant select on SEC_DEMO_VW to a_user_0,a_user_4,a_user_8,a_user_all;

Grant succeeded.

SQL> 

With this grant only named users can even select data from table (there is no select on SEC_DEMO_TBL table at all), so first request is applied (ordinary oracle user has no no query right at all).

Convert existing data to crypted value

Existing data in column A_CREDIT_CARD_NUMBER has to be encrypted first. This is done on a very easy way through simple SQL:

SQL> update SEC_DEMO_TBL
  2  set A_CREDIT_CARD_NUMBER = cc_security.encrypt_data (A_CREDIT_CARD_NUMBER);

3 rows updated.

SQL> commit;

Commit complete.

SQL> select * from SEC_DEMO_TBL;

        ID A_USERNAME                       A_CREDIT_CARD_NUMBER
---------- -------------------------------- --------------------------------
         1 SMITH                            93B38FD3A694DA2A11988AAE1F0E148D
         2 PITT                             93B38FD3A694DA2AE7C11AA4994D5B04
         3 JOLIE                            93B38FD3A694DA2AEB1B968C370095E9

SQL> 

Keep in mind that in this step you loose original A_CREDIT_CARD_NUMBER values so if you haven't done a backup or proper test your code, you might get in trouble retrieving values back!

Ensure that new incoming data are crypted as well

For any new data (or editing existing ones) a trigger is the best way to ensure validity of future actions. This trigger is for upadate and insert as well.

CREATE OR REPLACE TRIGGER SEC_DEMO_TBL$TGBIU
 BEFORE INSERT OR UPDATE
 ON SEC_DEMO_TBL  FOR EACH ROW
BEGIN
  IF inserting THEN
    IF :new.A_CREDIT_CARD_NUMBER is not null THEN
      :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER);
    END IF;
  END IF;
  IF updating THEN
    IF :new.A_CREDIT_CARD_NUMBER is not null AND :new.A_CREDIT_CARD_NUMBER != :old.A_CREDIT_CARD_NUMBER THEN
      :new.A_CREDIT_CARD_NUMBER := cc_security.encrypt_data(:new.A_CREDIT_CARD_NUMBER);
    END IF;
  END IF;
END SEC_DEMO_TBL$TGBIU;
/

Do small test and insert data as table owner:

SQL> conn damirv/*****
Connected.

SQL> insert into SEC_DEMO_TBL values (4,'RAMBO', '0123456-789-0004');

1 row created.

SQL> commit;

Commit complete.

SQL> 

Insert seems to be proper!

Test results

Here is the result for all 4 users...Results are obvious!

SQL> conn a_user_0/a_user_0
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH                            ***************
         2 PITT                             ***************
         3 JOLIE                            ***************
         4 RAMBO                            ***************

SQL>

SQL> conn a_user_4/a_user_4
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ---------------
         1 SMITH                            ***********0001
         2 PITT                             ***********0002
         3 JOLIE                            ***********0003
         4 RAMBO                            ***********0004

SQL> 

SQL> conn a_user_8/a_user_8
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ---------------
         1 SMITH                            0123*******0001
         2 PITT                             0123*******0002
         3 JOLIE                            0123*******0003
         4 RAMBO                            0123*******0004

SQL> 

SQL> conn a_user_all/a_user_all
Connected.
SQL> select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH                            0123456-789-0001
         2 PITT                             0123456-789-0002
         3 JOLIE                            0123456-789-0003
         4 RAMBO                            0123456-789-0004

SQL> 

All looks OK!

The end

The beauty of this solution is that it is bullet proof because it is implemented through Oracle roles-admin tool for user's right administration. In a moment roles can be revoked from user or granted to some other user.

If you connect as owner of table, who own package, table and view but have no proper rola granted, you are unable to see data:

SQL> conn damirv/*****
Connected.
SQL>  select * from SEC_DEMO;

        ID A_USERNAME                       CC
---------- -------------------------------- ----------------
         1 SMITH
         2 PITT
         3 JOLIE
         4 RAMBO

SQL>

As you see even as an owner of procedure and table cannot see data in it's own schema and you are treated as ordinary user! While in the same time, trigger in owner's schema is updating/inserting data without problems! Of course, as owner you can see plain data (with crypted column) as shown at the beggining but if this is a problem, lock this user!

And for the end, the whole administration of this solution is centralized in one package and one trigger what allows easy ugrade path in the future.

Cheers!

New Oracle 11g DBA book

Recently I get an opportunity to read a new book Oracle Database 11g Underground Advice for Database Administrators from April C. Sims.

The book was really interesting for because of two major points:
1) It covers many things "beyond" regular Oracle DBA work (what is real gold for experienced DBA)
2) It is based on real life experience of one DBA showing many new technical features in real examples.

And that all for Oracle 11g database...version which is still not too much in live usage around the world.

One free chapter can be read on this location.

If you find it interesting $35.99 is not too much for a literature like this and, of course, can be bought online on Amazon book store.