I already blogged about Oracle decision to release SQL Developer Data Modeler as a free product. I would certainly never shell out 3000$ (original Oracle price) for the license of Data Modeler, because the product is simply not worth that much, especially compared to 479$ Data Modeler from Quest, a product that is imho at least as good as Oracle Data Modeler, if not better. Now that Oracle SQL Developer Data Modeler is a free product it certainly deserves to be evaluated. I’ll probably use Oracle Data Modeler in my next pet home project and if I found something worth writing about I’ll publish it here in the comments section. For now I just want a working copy to sit on my desktop, waiting for some free time on my part.

Downloading and installing Data Modeler on my Ubuntu Lucid Lynx was a breeze, because I already downloaded and installed JDK 6 for SQL Developer.

Steps for installing SQL Developer Data Modeler:

$ cd /home/alesk/Downloads
$ sudo unzip datamodeler-2.0.0-584-no-jre.zip -d /oracle
$ sudo chown -R oracle:dba /oracle/datamodeler/
$ cd /oracle/datamodeler
$ sh ./datamodeler.sh
-- the first time you run the script it'll ask you to
-- provide path to jdk installation directory, in my case:
/oracle/jdk1.6.0_21

That’s it. The only thing that remains to be done is to create new desktop launcher. SQL Developer Data Modeler starts surprisingly fast, considerably faster than SQL Developer. Screenshot example:

One of the first thing I checked was for which databases we can generate DDL, the list is much shorter compared to Quest Data Modeler, but for the mainly Oracle shop it’s more than adequate:

This is MY Sticky Note about installation of patchset 10.2.0.5 on Windows x64 (single instance, no database vault), followed by Patch 2 installation. Make sure you read README.html that comes with Patchset, this sticky note is MY note and as such is not a substitute for README.html.

-- Make a backup of database, Oracle Software (ORACLE_HOME) and Oracle inventory
-- Install Patchset 10.2.0.5 binaries.

sql> connect / as sysdba
sql> startup upgrade
sql> spool utlu102i.log
sql> @?\rdbms\admin\utlu102i.sql
sql> spool off

I got two warnings:

**********************************************************************
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
**********************************************************************
WARNING: --> "shared_pool_size" needs to be increased to at least 201326592
WARNING: --> "java_pool_size" needs to be increased to at least 67108864

sql> shutdown immedite

< fix the problem >

sql> startup upgrade
sql> spool patchset_10205.log
sql> @?\rdbms\admin\catupgrd.sql
sql> spool off

sql> shutdown immediate
sql> startup

sql> @?\rdbms\admin\utlrp.sql

-- check status of components:

set lines 100
column comp_name format a40
column version format a20
column status format a20
SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

-- RMAN catalog upgrade (if the RMAN catalog is not yet at >= 10.2.0.5)

rman catalog rman/rman@tns
rman> upgrade catalog;

Because of the bug introduced with patchset 10.2.0.5 I postponed deployment of patchset 10.2.0.5 until Oracle released a fix in Patch 1 in mid September 2010, then I saw that Patch 2 (which is also a security bundle CPU October 2010) is planned for mid October 2010 and decided to wait for Patch 2 rather than installing Patch 1.

--
-- MY sticky note for installing Patch 2 on top of 10.2.0.5
-- Again, reading README.HTML is MANDATORY!
-- Download patch# 10155844 (p10155844_10205_MSWIN-x86-64.zip) and 
-- Opatch 10.2.0.5, patch# 6880880 (p6880880_102000_MSWIN-x86-64.zip).
-- Follow instructions in readme for Opatch installation.
--  Patch 2 archive unzip in some temporary directory 
-- (my location of choice was D:\ORACLE\10205_Patch2)
1) Backup database
2) Backup Oracle inventory (C:\Program Files\Oracle\...)
3) shutdown immediate
4) net stop OracleServiceSID
5) net stop OracleORA10TNSListener
6) net stop "Distributed Transaction Coordinator"
7) cd D:\Oracle\10205_Patch2\10155844
8) set PATH=D:\ORACLE\ORA10\Opatch;%PATH%
9) opatch lsinventory
10) opatch apply
11) check log file (D:\ORACLE\ORA10\cfgtoollogs\opatch)
-- post installation steps
12) net start OracleServiceSID
13) cd D:\ORACLE\ORA10\BUNDLE\Patch2
14) sqlplus /nolog
15) connect / as sysdba
16) startup     
17) @catcpu.sql
18) quit
19) check log file (D:\ORACLE\ORA10\cfgtoollogs\catBundle)
20) you can check REGISTRY$HISTORY for Patch 2 installation note
21) optioanl: if catcpu.sql reports invalid objects run utlprp.sql
22) net start OracleORA10TNSListener
23) net start "Distributed Transaction Coordinator"

Everything went smooth during 5 test and one production installment, more are on the way during this week. I’m happy to report that trace files reporting “WARNING:Could not lower the asynch I/O limit” are gone.

Sometimes ORA-600 can actually work for us ;-). Today, I powered on one of the PC’s that was offline for awhile and that it serves me as a test box. I didn’t spot anything weird until I tried to startup database to execute catcpu.sql from CPU Oct. 2010. I was greeted with the ORA-600 [2252] error:

ORA-600 lookup tool (MOS 153788.1) correctly reported (see MOS 138701.1) that I should check system time, since this error is raised when kernel detects system time that is very different from what kernel is aware of. I marked with the yellow the system time. Obviously CMOS battery run out of juice on this box and needs a replacement.
Doesn’t happen very often that ORA-600 works for me and not against me :).

A couple of days ago I needed a quick solution that would limit access to some sensitive data (columns). Only a limited group of end users, selected developer and a DBA should see the content of some columns. Target table is otherwise stored in application schema that is shared by several developers, but only one of them should see the data. I knew that encryption is the way to go. Based on example published in Oracle 10g PL/SQL Packages and type reference for package DBMS_CRYPTO, I wrote simple package that will set decryption key for the session and allow for encryption and decryption of data.

Create or replace package dbcrypt is
  Procedure setkey( p_key  IN VARCHAR2 ); 
  Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC;
  Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC;
end dbcrypt;
/

Create or replace package body dbcrypt is
    
    ecryption_type PLS_INTEGER := SYS.DBMS_CRYPTO.ENCRYPT_AES128 + 
                                  SYS.DBMS_CRYPTO.CHAIN_CBC +
                                  SYS.DBMS_CRYPTO.PAD_PKCS5;
    v_key RAW(16) := null;
    
    Procedure setkey (p_key IN VARCHAR2)
    IS
    BEGIN
      if p_key is not null then
        v_key := UTL_RAW.cast_to_raw(p_key);
       dbms_output.put_line(v_key);
      end if;
    END setkey;
    
    Function encrypt( p_data IN VARCHAR2 ) Return RAW DETERMINISTIC
    IS
      l_data RAW(2048) := UTL_I18N.STRING_TO_RAW(p_data,'AL32UTF8');
      l_encrypted RAW(2048);
    BEGIN
      l_encrypted := sys.dbms_crypto.encrypt                       
                     ( src => l_data,
                       typ => ecryption_type,
                       key => v_key );
      Return l_encrypted;
      EXCEPTION 
        WHEN OTHERS THEN
          -- for the security reason I want to completely silence the error 
          -- stack that could reveal some technical details to imaginary attacker.
          -- Remember, such miss-use of WHEN OTHERS should be considered 
          -- as a bug in almost all other situations.
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END encrypt;

    Function decrypt( p_data IN RAW ) Return VARCHAR2 DETERMINISTIC
    IS
      l_decrypted RAW(2048);
    BEGIN
      l_decrypted := sys.dbms_crypto.decrypt                              
                      ( src => p_data,
                        typ => ecryption_type,
                        key => v_key );
      Return UTL_I18N.RAW_TO_CHAR(l_decrypted,'AL32UTF8');
      EXCEPTION 
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR(-20001,'Access denied!');
    END decrypt;
End dbcrypt;
 /

Let’s see dbcrypt package in action:

--
-- Let's pretend we want to protect first and 
-- last name in table MY_EMP. 
-- Note that encrypted values are much larger than
-- non-encrypted, that's why I used VARCHAR2(100)
--
create table my_emp (
 id  number(10) not null,
 first_name varchar2(100),
 last_name  varchar2(100));

--
-- We can prepare view that will decrpyt columns,
-- this view can then be used in application.
--
create or replace view v_my_emp as
  select id, 
  substr(dbcrypt.decrypt(first_name),1,35) as First_Name,
  substr(dbcrypt.decrypt(last_name),1,35) as Last_Name
  from my_emp;  
--
-- once per session we need to set key that
-- is used fro encryption/decryption for the duration of session.
--
execute dbcrypt.setkey('mysecretpassword');
--
--
-- now, wen can insert some encrypted data
--
insert into my_emp values(1,dbcrypt.encrypt('Bruce'),dbcrypt.encrypt('Scott'));
--
-- you can check that column data is encrypted
-- 
select id, first_name, last_name from my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- --------------------------------
   1 372EC43843E2782F30B56DA46034324D 65FE9535E2FF1FA61B495C7A29F1779F
--
-- since encryption key was already set (see execute statement above),
-- we can simply select from the view and view will take care 
-- of decryption.
--
SQL> select * from v_my_emp;

  ID FIRST_NAME                       LAST_NAME
---- -------------------------------- ----------
   1 Bruce                            Scott

Of course we must be careful that encryption key is not lost and/or exposed (network sniffing, written in some configuration file for application etc.), but this is a different story.