Dell teaming with Oracle Enterprise Linux?

Just noticed that Dell change the "Red Hat Enterprise Linux" label on their support website to just "Enterprise Linux".
Is this the first step for Dell embracing Oracle Enterprise Linux?

Sun 2002 flashback

We've been doing some shuffling around of hardware at work and I've recommissioned some old Sun hardware for a new QA environment.
Oh the memories I have of old Sun kit :)
The most exciting thing I've installed is a Sun v480 connected to a T3 brick. That combo was pretty much the industry standard Oracle solution when I had my first job in "real" systems administration and it was probably one of the first the real mid-range installs I did (not counting ageing 420r/450's).
To be honest, it's a match made in heaven. A quad cpu v480 with a t3 brick (or two) kicked out some real performance numbers in it's day. The v480 is probably one of the nicest boxes to work with (considering age and all), the boards are easy to fit, the RSC is awesome, it's simply rock solid. Running Solaris 8 of course (we still have *lots* of clients on Solaris 8 (with Solaris 10 gaining ground)).


The T3 array is pretty cool, but it brings back the frustrations everyone had with early low-end SANs. The limitation to two disk RAID volumes with very limited zoning and slicing. But hey, it's pretty old and is still pretty fast.



Now if I only could figure out how to get rid of that darn Sun E250 we still have running Sybase 12.5.4, it just works to well so far.

Reclaiming LOB space in Oracle

Reclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact;, but what do we do for lobs?
We need a manual reclaim for the lob column.

A little demo:

(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 14 12:19:02 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Create a table and sequence to play with
HLINDEN@spinner1> create table lob_test (id number, data blob);

Table created.

HLINDEN@spinner1> create sequence lob_test_seq;

Sequence created.

-- Load 50 rows with 1.5Mb blobs (see code bellow)
HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

-- Find out what our lob segment is called
HLINDEN@spinner1> select object_name,object_type from user_objects where
  2  created>sysdate-interval '5' minute and object_type='LOB';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000199575C00002$$      LOB

-- Display the current size of the lob segment
HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Ok, let's delete those blobs and see what the size is after
HLINDEN@spinner1> delete from lob_test purge;

50 rows deleted.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
        75

-- Still 75Mb, hm, perhaps it recycled if we insert more data?

HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
       150

-- Nope, not recycled. We need to issue a shrink command to free up the 
-- space immediately
HLINDEN@spinner1> delete from lob_test;

50 rows deleted.

HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);

Table altered.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where 
segment_name='SYS_LOB0000199575C00002$$';

        MB
----------
         0
-- All gone!

My simple blob loading code:

DECLARE
  src_file      BFILE := bfilename('TMP', 'data.dat');
  dst_file      BLOB;
  lgh_file      BINARY_INTEGER;
  cur_id        NUMBER(10);
BEGIN
  FOR i IN 1..50
  LOOP
    INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
          RETURNING id into cur_id;
    -- lock record
    SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    lgh_file := dbms_lob.getlength(src_file);
    dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
    dbms_lob.fileclose(src_file);
  END LOOP;
END;
/

Oracle 11g - one month to go

Yep, exciting times. July 11th, lots of new cool stuff.

Read about a couple of new things in Insider .

If you're lucky enough to be in New York, sign up for the launch event!

New blade enclosure from Sun

Ever since Andy Bechtolsheim returned to Sun pretty much everything on the Sun x64 server line meet all expectations and then some. Sure, there has been the slight ethernet chipset problem, but generally the AMD kit has really kicked ass
Though some people where surprised to see first (new) blade offering from Sun, the 8000 series. A 4-socket blade offering when most of the market moved away from larger blade enclosures and focused on 2-socket systems. The 8000 still made good sense for larger customers like financial institutions and telcos. Server consolidation on a large level.

Now the other day Sun released a second (third actually, the 8000 comes in two models) blade offering. The 6000 series. It's a fairly basic 10U enclosure, 10 blade slots and the classic Ethernet modules. Not very exciting at first glance, I have to admit. Sounds like the PowerEdge 1955 box but not with the same high density (Dell can do 10 blades in 7U).

The thing cool about the 6000 enclosure is that it is not a Blade enclosure in the classic sense, it's not "stripped down computers in a box". It's actually 10 high capacity servers. It enclosure offers the same expandability as normal 1U servers.
All blade modules are dual socket, can take up to 16 DIMMS, four 2.5" SAS drives and even two PCI-e slots. That's right, normal PCI-e slots, no more proprietary on board FC-AL or Infiniband modules. Just slot in a couple of standard c-PCIe cards and off you go. No more opening up the blade to install modules.
Another new feature is a hardware RAID controller on-board (for good and bad indeed).
The whole enclosure is based around industry standards and open solutions.

In addition to all this cool stuff the real winner here is the selection of blades.
Sun offers no less than 3 blade types.
The t6300 UltraSPARC T1 blade,
the x6220 AMD blade and
the x6250 Intel Xeon blade
It's the first Intel Xeon (including quad-core procs) offering from Sun, with many more to come.


Watch Andy talk about the new box here.

Run system commands from Oracle with PL/SQL

I friend of mine asked if it was possible to show the exact Linux kernel version on an Oracle server without actually having shell access to the server.
He had full access to Oracle with sysdba/dba roles etc, but not SSH.
I've seen some versions of executing system commands from Java but never really liked the idea of invoking Java for something simple like that.

One way I thought of would be to use dbms_scheduler to execute a job with an executable job_typ. The first problem was to find a way to actually return the standard output from the execution to Oracle.
Ok, so my 'hack' here is a stored procedure (entirely in PL/SQL) that creates a job with dbms_scheduler; calling /bin/sh as the executable and hands it a temporary script to execute. In the script I have a simple redirect to a temporary spool file and then the procedure simply reads and outputs the content of the file. It's a bit of a hack but at least it gets the job done and doesn't use Java.
I haven't drilled down on what kind of permissions you need to actually use the procedure but I suspect it's quite a lot.
The temporary spool file handling in my example is quite poor, but works. :)
A word of warning as usual when using PL/SQL, this code example is a proof of concept. It needs *loads' of error catching etc. in order to be production ready, use with caution.

Example of use:

oracle@htpc:~$ rsqlplus hlinden/password as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 30 21:55:06 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> set serveroutput on
SQL> @system_run

Procedure created.

SQL> exec system_run('ls -l /home/oracle/bin');
total 12
-rwxr-xr-x 1 oracle dba  797 Nov  5  2006 backup_controlfile.sh

PL/SQL procedure successfully completed.

SQL> exec system_run('uname -a');
Linux htpc 2.6.20-15-generic #2 SMP Sun Apr 15 06:17:24 UTC 2007 x86_64 GNU/Linux

PL/SQL procedure successfully completed.

SQL>

And here is the procedure code:

CREATE OR REPLACE PROCEDURE system_run(cmd IN varchar2)
IS
  script_file   varchar2(40) := 'my-temp-script.sh';
  script_data   varchar2(4000);
  MyFile        utl_file.file_type;
  d             varchar2(4000);
  dump_file     varchar2(40) := '/tmp/my-temp-file.dat';
  dump_type     utl_file.file_type;
BEGIN
  -- Open file
  MyFile := utl_file.fopen('TMP',script_file,'w');
  -- Write data to file
  script_data := '#!/bin/bash' || chr(10) || cmd||'>'||dump_file;
  utl_file.put_line(MyFile, script_data, FALSE);
  -- Close file
  utl_file.fflush(MyFile);
  utl_file.fclose(MyFile);
  -- Purge old logs, no fun anyway
  dbms_scheduler.purge_log(JOB_NAME=>'TEST');
  -- Execute script
  -- The job is created as disabled as
  -- we execute it manually and will
  -- drop itself once executed.
  dbms_scheduler.create_job(
    job_name   => 'TEST',
    job_type   => 'EXECUTABLE',
    job_action => '/bin/bash',
    number_of_arguments => 1,
    start_date => SYSTIMESTAMP,
    enabled    => FALSE);
  dbms_scheduler.set_job_argument_value('TEST', 1, '/tmp/'||script_file);
  dbms_scheduler.enable('TEST');
  -- Wait for the job to be executed
  -- usually done within 1 second but
  -- I set it to 2 just in case.
  dbms_lock.sleep(2);
  -- Open the output file and
  -- print the result.
  dump_type := utl_file.fopen('TMP',dump_file,'r');
  loop
    begin
      utl_file.get_line(dump_type,d);
      dbms_output.put_line(d);
    exception
      when others then
        exit;
    end;
  end loop;
  utl_file.fclose(dump_type);
  -- Clean up our temp files
  utl_file.fremove('TMP', script_file);
  utl_file.fremove('TMP', dump_file);
END;
/

Cyrus IMAP file system tuning

Been busy, not enough blogging, bla bla bla. I know.
Just a lot of stuff going on at work at the moment, mergers and integrations.

We've had some problems with one of our IMAP servers at work running Postfix and Cyrus IMAPd. A for the job quite well speced machine, dual Xeons and 3x146Gb disk in RAID5 (4x146 in RAID10 would have been nicer). Anyway, the machine has got 50 or so IMAP users and perhaps 100Gb spool data on a ReiserFS partition.
The machines' avg. load has peaked at over 6.00 with about 75% in iowait on a bad day. I suspect that modern fancy e-mail search tools are to blame for the problems, applications building search indexes and such (Apple Mail anyone?).
Monitoring of the server showed quite a lot of inode update activity, even though there isn't *that* much new email coming in.
Must be our old (not so) dear friend atime that's making a little mess, I've used the noatimea and nodiratime mount options in the past with great success. Seen performance improvements of a couple of percent.
The mount-options noatime and nodiratime simply disables the feature to update the access timestamp of a file (and directory). I.e. when someone clicks and reads an email in their mail application the inode atime timestamp is updated. When is this atime timestamp used? Never.
Did a quick online remount of the spool fs with noatime and nodiratime.
The result?
Avg. load hasn't touch 1.00 since. Wow! I was expecting an improvement, but not that big.
Great and easy way to improve performance on IMAP spools.

mount -o remount,noatime,nodiratime /var/spool/imap

And don't forget to update /etc/fstab with the same mount options.
Cool.