Halis way

Backups, cheaper and faster?

2008-03-09T23:21:00.001Z

When did backups become easy and affordable?
I've been sort of slacking off in the backup game in the last year or so, our backups have been managed by other parts of the company and some backups have been outsourced to BSP's. I recently spec and deploy a basic Netbackup solution for our office servers at work and was amazed how extremely simple the whole thing was. The first backup system I worked with was Legato Networker (now owned by EMC) and it was at the time a pretty great product but it was quite difficult to manage, lots of things needed to be scripted and setting up a new tape library required quite a lot of tweaking.
Secondly, the budget was at first glance fairly limited but when we speced up what we needed and got a couple of quotes in I was amazed of how much we actually got for our money.
I wanted basic disk staging then duplication to tape. The obvious choice for tape was LTO4, we get pretty good price / performance, market leading capacity and it's a solid standard.
Most suppliers actually quoted the same tape library, just branded by different vendors. HP, Dell, IBM. All where IBM 3573 tape libraries, it's a excellent and affordable library. Dell came in best in price as usual. We opted for SAS attached. The backup server, my first choice server these days, is the Dell PowerEdge 2950. I buy it for pretty much 75% of all requirements. Speced out with a pair of Quad-Core 1.86GHz processors, 4Gb RAM, 6x500Gb SATA drives (in RAID5) for the disk staging pool and a SAS5 to attach the library.
Software wise the server is running CentOS 5.1 and Symantec/Vertias Netbackup 6.5 “Linux Starter Edition (LSE)”. The LSE is a pretty good deal, you get a server license, tape library license and 5 client licenses (with bare metal, open file etc.) for a fairly low cost.
Installing and configuring Netbackup was as easy as it gets, the tape library was detected on boot, no sysctl.conf changes needed. Stepped through the netbackup installation process, added the tape library (less than 5 clicks!!), defined a couple of tape label policies, created a 2Tb disk staging pool with my new tape group as the target, just add water (ehm, clients actually).

The total cost was under £13.000 (about $22.000), installation time was less than one day, included was:

Veritas Netbackup 6.5 Linux starter edition
Netbackup clients 5-pack

Netbackup Exchange agent

Dell PowerEdge 2950 server

Dell PowerVault TL2000 library w/ one LTO4 SAS drive

25x LTO4 tapes

3x LTO cleaning tapes

CentOS Linux 5.1 (free)

How to delete an object with a special character in Oracle

2008-02-20T17:52:00.000Z

There are some things in Oracle that are possible but shouldn't be possible.
One thing I love to hate is the fact that you can create tables with almost any name, just as long as you double quote it.
I.e.:

SQL> create table "My Fruit Table" (id number);

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
My Fruit Table                 TABLE

Horrible! And what's even more horrible is that people actually do this.

Now to the problem, a user created a table with a special character in the name. Not even sure what character but I need a way to drop it. PL/SQL to the rescue.
Example:

SQL> set serveroutput on
-- Lets create a dummy table with a bogus character in the name
SQL> declare
        a varchar2(500);
begin
        a:='create table "abctest'||chr(150)||'" (id number)';
        execute immediate a;
end;
/

PL/SQL procedure successfully completed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
abctest?                       TABLE

SQL> desc "abctest?";
ERROR:
ORA-04043: object "abctest?" does not exist

-- And a bit of PL/SQL to drop it
-- change the abctest% string to something matching your single table.
SQL> declare
        a varchar2(500);
        tname varchar2(50);
begin
        a:='select table_name from user_tables where table_name like ''abctest%''';
        execute immediate a into tname;
        dbms_output.put_line('Table name is: '||tname);
        execute immediate 'drop table "'||tname||'"';
end;
/

Table name is: abctest?

PL/SQL procedure successfully completed.

SQL> select * from tab;

no rows selected

Handy.

Buying marketshare

2008-02-03T18:55:00.000Z

Ok, I'm way waay late on commenting on the two most recent IT company takeovers .

Sun buys MySQL
Tricky one, I have to honestly say I have a hard time seeing where MySQL fits in to Sun's portfolio.
Sun is one of Oracle biggest partners (the biggest after Dell?) and they are one of the biggest backers of PostgreSQL, then even employee a few of head lead Postgres developers.
Sun has got two options with MySQL, they can either invest a lot of RnD in MySQL and actually turn it in to a really good product. MySQL is without a doubt a product with incredible potential, but is today pretty much a over-sized way to store CSV index files (bit harsh perhaps but hey).
Or they can ride out the profits by selling LAMP-stack boxes and let MySQL sustain itself, I doubt they can make up for the billion dollars they payed for it though.
Oh, and what will Sun make of the (rather evil) closed source MySQL Enterprise Monitor administration product from MySQL. Sun having new closed source software? Errr!

Some advice for sun,
1) Add real constraint support. This is a show stopper for many, and the InnoDB approach isn't good enough. MySQL needs native solid from the ground up constraint support, independent of storage engine, version bla bla. If people don't want the "performance loss" of constraints, don't add the constraints!
2) Sort out proper I/O management, tablespaces, clean partitioning all that stuff. MySQL has improved a lot on this point over the last 12 months. But more work is needed, especially if they are planning on selling MySQL powered Thumpers.
3) 100% solid transactional support. MVCC would be fun. I've seen a few to many transactional problems in MySQL. Some sort of logging/undo/redo system is needed.

On top of that I wouldn't mind a clean hot-backup tool, requires proper transactional support (with something like SCN's etc), 3rd party scripting language support (PgPerl anyone?).

That's about 250.000 RnD hours, go get busy!

Oracle buys BEA
This is most likely very good news. I'm a big fan of the Oracle database (duh) and a fan of Weblogic Application Server.
What I'm not a big fan of is Oracle Application Server (oc4j).
I hope Oracle realize that Weblogic is the superior product and works with Weblogic as the front runner and port oc4j features to Weblogic and not the other way around. I doubt that will be the case though. If not, I hope they don't kill off Weblogic any time soon.

Hmm, that's all for now.

Websphere ND dmgr permission problems

2008-01-18T11:23:00.000Z

Ran in to a really weird problem with one of our Websphere 6.1 Network deployment setups yesterday and as I couldn't find one single page about the problem in google I thought I'd blog it.
I'm not much of a Websphere admin but managed to fix it after a while.

The problem started when a datasource was updated and all of the sudden all node agents stopped trusting the deployment manager (dmgr). Syncing the nodes failed and hence pretty much everything failed to restart / deploy. Running servers where fine though.
We got this message in the logs:

[1/17/08 13:30:52:020 GMT] 00000028 RoleBasedAuth A   SECJ0305I: 
The role-based authorization check failed for admin-authz operation 
SSLAdmin:temporarilyDisableCertificateAuthentication:java.lang.Long.
The user UNAUTHENTICATED  (unique ID: unauthenticated) was not granted
any of the following required roles: administrator.

My best guess is that node agent configuration was corrupted in some way.
How do you fix the problem then?
Fairly easy actually.
# Stop all node-agents that seem broken (that would probably be all of them!).
# Go to the node agents bin directory on the node (usually something like $WAS_HOME/profiles//bin/).
# Manually sync the node with syncNode.sh, point to the SOAP connector (default is 8879) on the DMGR server. See example

./syncNode.sh dmgrhost 8879 -username websphere -password webfear

# Start the node agent and verify that the logs are happy. Kick off a cell sync from dmgr. You should see entries similar to this in the logs:

[17/01/08 16:14:59:872 GMT] 0000002f NodeSyncTask  A   ADMS0003I: 
The configuration synchronization completed successfully.

Solaris Express on a Toshiba Satellite Pro A200

2008-01-01T11:25:00.000Z

I bought myself one of those cheap laptops the other month. I needed a small machine for testing and since laptops are just as cheap (if not cheaper) as desktops these days I got a laptop.
The machine came with Vista but I wanted to triple boot Vista, Ubuntu and Solaris Express Community Edition.

Use diskmgmt.msc in Vista to shrink the partition the machine came with, Windows can do this natively so there is no need to use Partition Magic or similar tools. Create at least three new partitions. One for Solaris, one for Linux and one for Linux swap.
Secondly install Solaris, boot off the CD and go through the basic installer. The widescreen resolution worked out of the box (as usual). Do a full install, spending time "fixing" a smaller installer is just annoying. Solaris will install it's grub boot loader on both the MBR and superblock (on the Solaris partition). It probably makes sense to leave a large slice unused so it can be used with ZFS after the installation is done.
Install Ubuntu. Nothing simpler than that.
Edit Ubuntu's grub menu config (/boot/grub/menu.lst) to include Solaris. Simply point it to the Solaris parition (hd0,2 for me). Add these lines at the end of the file.
```
title Solaris
root (hd0,2)
chainloader
```

Done!

I had to install the gani NIC driver in Solaris to get the Ethernet card working and the Open Sound System sound card driver to get sound working.
The Atheros WiFi card is supposed to be supported but I couldn't get it to work, even after adding the pci device alias to the driver. I'll post an update if I get it to work.

Solaris 10 on Dell PowerEdge 1950 and 2950

2007-12-20T17:58:00.000Z

I probably get an email every couple a weeks about this one, not sure why.
E-mails regarding something in Solaris not working on Dell PowerEdge 9th gen servers.

For any device driver related issues with Solaris on x86, the first resource to check if you run in to trouble is the Sun HCL, the 'hardware compatability list'.
Solaris 10 works fine on most Dell boxes, but some need NIC or HBA drivers.

For a Dell 2950 that tells us that Solaris works, from release 11/06 upwards, it also tells us to download the ethernet driver here and the MegaRAID SAS driver here.

NB, the Sun HCL does not mention the new 'III' series 9-gen server yet. I would guess they are working on as Sun and Dell recently partenered to provide better Solaris support. The PERC6 card may need some new drivers.

Converting MySQL "on update current_timestamp" to Oracle

2007-11-28T21:20:00.000Z

Another short simple SQL for all out there in the process of converting old MySQL schemas to Oracle.

MySQL has got a built in feature to automatically update a column to the current timestamp whenever the row is updated, just by using the default-clause. The "on update current_timestamp" feature can be quite handy if you have lazy developers who can't be bothered writing full insert statements. :)

The MySQL create table statement would be something like this:

create table p (
 id int,
 a varchar(10),
 d timestamp DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP,
 constraint p_pk primary key (id)
);

Not difficult to do in Oracle either, but we need a trigger to assist.
Example:

SQL> alter session set nls_Date_format='HH24:MI:SS';

Session altered.

SQL> create table p (id number, a varchar2(10), d date default sysdate, 
       constraint p_pk primary key (id));

Table created.

SQL> insert into p(id,a) values(1,'test');

1 row created.

SQL> host cat p_test.sql
CREATE OR REPLACE TRIGGER p_d_trig
BEFORE UPDATE ON p
FOR EACH ROW
BEGIN
        select sysdate into :new.d from dual;
END p_d_trig;
/

SQL> @p_test

Trigger created.

SQL> select * from p;

        ID A          D
---------- ---------- --------
         1 test       21:15:05

SQL> update p set a='foo' where id=1;

1 row updated.

SQL> select * from p;

        ID A          D
---------- ---------- --------
         1 foo        21:16:44

SQL>

Azul Systems Java appliance

2007-11-04T21:03:00.000Z

So, I've been dragged more and more in to managing Java application containers like Weblogic, Websphere and JBoss. These have a tendency to be hugely complex beasts, almost as complex as our favorite database and performance optimization is sometimes quite difficult, or simply it's to much effort to actually upgrade or replace the server.
Azul offers a quite neat (but a tad pricey) solution to this. They off-load Java computation to a separate appliance, the smallest model has 96 processing cores and 48Gb of ram. The big daddy has a massive 768 cores and 768Gb ram. It's a by Azul in house engineered hardware with custom software (I would guess the OS is semi-based on one of our open-source friends (have a look at the ifconfig output)). The application server still a normal server (Linux/Solaris etc), the small JVM on the server pretty much acts as a proxy between external resources such as JDBC sources and the actual JVM on the appliance.
Their marketing crew calls it a "turn key solution", it's not really that easy but it's pretty straight forward to use.

The appliance itself takes about 15 minutes to install, setup the networking and the compute domain name and you are pretty much done with the appliance itself.
The application side is almost as easy. Azul provides sort of a "JDK wrapper", you unpack the wrapper and run a simple shell script to integrate it with an existing JDK, the script asks for the path to the JDK to "mimic". Works with IBM and Sun JDK's, both 1.4 and 1.5 (I haven't tried with Jrockit).
Change your appserver init script's to use the new JAVA_HOME, give it a couple of azul specific JVM options, give it 10Gigs or so of heap and off you go. One thing to remember is that most garbage collection arguments are now obsolete, azul uses it's own custom "pausless gc".
The first thing that hit me when starting the first app-server was how incredibly slow it was to deply, deploying EJB's etc took ages, but hm, yes, that's an almost single threaded operation. The application itself felt "ok" when using it, now the cool part, we really couldn't get the application to run slowly with our benchmarks, at least not if we count out the fact that it eventually trashed the database sever (just some random 8-core thing) behind it. Bottlenecks in the application tiers where all gone! It doesn't matter if 5 users are online or 250, the application performs exactly the same.
The simple conclusion now is "we need a bigger Oracle box!".

Azul provides a quite good web management console for the appliance, the gui provides functionality to manage compute pools if you need to throttle CPU and memory usage between applications or servers and also provides views to monitor applications and utilization.

I guess one could call it a bit of a custom "throw hardware at the problem" solution, the box itself is quite expensive but for a medium sized company with loads of j2ee apps it makes sense.

Oracle SQL to return a alphabetical subset

2007-11-01T12:15:00.000Z

Yes, I know, I've been waaay bad at blogging lately. Been busy working on new projects at work, upgrading applications to Java1.5 containers (Websphere 6.1, Weblogic 9.2 etc). On the fun side we've got an Azul Java-acceleration box, that really needs a couple of blog entries!

Anyway, got asked if there was a way to return a resultset of data based on the leading character, the use case was to ignore all strings starting with a,b, or d and return e to z.

Fairly straight forward but a good SQL to have.
I simply grab the first character in the varchar and compare it's ASCII value to the ASCII value D.

SQL> select * from t;

A
----------
Atest
Btest
Etest
Htest
Wtest
Dtest
dtest
SQL> with data as (
  2  select ascii(upper(substr(a,1,1))) a_val,a from t
  3  )
  4  select * from data where a_val not between ascii('A') and ascii('D') order by a
  5  /

     A_VAL A
---------- ----------
        69 Etest
        72 Htest
        87 Wtest
SQL>

Oracle 11g: Archive log alert log output

2007-08-21T08:42:00.000+01:00

Starting with Oracle 11g the archivelog process is no longer logged to the alert log by default. For good and bad in my opinion, nice to get rid of the log entries on smaller databases but it can be quite useful to see the details on larger systems with multiple log destinations.

The log level is simply a database parameter and can easily be changed, look at this page to figure out what level you want. I figured 79 would be a good starting point (64+8+4+2+1). Databases running dataguard should probably have 207 (add 128) to include FAL service details.

How to set the trace level:

[oracle@vm-rac1 ~]$ rsqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sun Aug 19 02:35:23 2007

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter log_archive_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace                    integer     0
SQL> alter system set log_archive_trace=79;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@vm-rac1 ~]$ talert # my shell alias to tail the current alert log
  Current log# 1 seq# 7 mem# 0: /u01/app/oracle/oradata/test11g/redo01.log
Sun Aug 19 02:03:14 2007
ARC3: Evaluating archive   log 3 thread 1 sequence 6
ARC3: Beginning to archive thread 1 sequence 6 (627362-628594) (test11g)
ARC3: Creating local archive destination LOG_ARCHIVE_DEST_10: 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_%u_.arc' (thread 1 sequence 6)
 (test11g)
ARC3: Creating local archive destination LOG_ARCHIVE_DEST_1: 
'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf' (thread 1 sequence 6)
 (test11g)
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_10: 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'
 (test11g)
Committing creation of archivelog 
'/u01/app/oracle/flash_recovery_area/TEST11G/archivelog/2007_08_19/o1_mf_1_6_3dh5pld1_.arc'
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: 
'/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'
 (test11g)
Committing creation of archivelog '/u01/app/oracle/11.1.0/db_1/dbs/arch1_6_630935902.dbf'
ARC3: Completed archiving thread 1 sequence 6 (627362-628594) (test11g)

Read details about the log trace level here and general alert log related stuff here.

Oracle 11g: basic rundown

2007-08-19T21:09:00.000+01:00

Ok, so 11g has been out some time but I've been to busy to really dig in to it until now. 11g is probably the first release that doesn't really many new features; no, before you send me angry e-mails hear me out :)
Take a look at the new features list. What do most of these features do? Easy, they just make the most out of the fairly old but totally outstanding REDO and UNDO management in Oracle. Why not use what we already got? Ok, some things are totally new features like the improved partitioning and new data types for the medical and life-sciences industry.

Partitioning and compression
The coolest things in 11g is probably the VLDB features for partitioning and compressions, can really reduce storage costs (at the same time as storage costs are plummeting with the competition from iSCSI). It is now possible to create "automatic" partitions, i.e. instead of adding a partition every month with DDL when doing a bulk load Oracle can automatically partition the table as per predefined rules. Pretty cool and saves a lot of time.

Direct NFS
Another pretty cool feature, at least for me as I'm using a lot of Netapp NFS storage, is the Direct NFS client in Oracle (Linux). Switch to the direct NFS lib and Oracle will do NFS ops outside the Linux kernel, which removes the NFS caching layer and kernel block device mapping etc. Gives a quite massive performance increase on some workloads. This is what I'm playing most with at the moment.

Data Guard
Lots of new cool stuff for Data Guard. Most significantly is probably the fact that you can have an open standby, available for real-time queries such as reporting applications and other read only operations. In fact, you can even open a Data Guard instance in read-write mode and mess about with it and later just flashback to a SCN where the db was in sync with the Data Guard master db and just roll forward to the current SCN using the normal FAL services. How sweet isn't that for DR testing and schema change testing. DR testing during live operations, that's a couple of late nights saved.
Other new data guard features include redo shipping compression, the normal improvements to the DG broker, sqlplus manageability etc.

Workload replay
Another thing that is really useful for deployment testing and change control is the database replay feature, one can "record" all transactions over a period of time and replay them after a change has happened to ensure performance and functionality has not degraded.

Improved ASM features.
You can now bring online a replaced drive and new writes will go directly to the drive while Oracle sync data from a redundant mirror in the background. Reduces resync time quite a fair bit.

PL/SQL
Couple of improvements to plsql;
Use of sequences in declares.
Real compound triggers.

New data types
Not really my thing but probably useful for a lot of people.
Spatial and multimedia support for medical imaging, life-sciences and other fairly odd things. :) Docs here.

A couple of minor things have changed as well, the alert log lives in a new place (with it's buddies in the new diag system), the logging levels in the alert log can now be adjusted as well (got a blog entry about that in a couple of days).

Lots of fun things to play with and to blog about.

Ah, almost forgot. Larry pulled a version hack on us again. As some of you may now there was never an Oracle Version 1 release, the first version was called 2 since Larry Ellison didn't think anyone would buy version 1. The first (public) 11g release is 11.1.0.6

Oracle Database 11g available for download

2007-08-11T21:40:00.000+01:00

Well, it's about time. Oracle finally made 11g available for download. Only 32-bit Linux so far though and I have a feeling we'll have to wait a while for most other platforms (possibly a 64-bit Linux download soon).

Download it here.

Lots of new cool stuff to blog about, I'm away on holiday for a week but my recently upgraded lab machines at home are sitting there waiting. Fun times when I get home.

Limit user sessions in Oracle

2007-07-30T21:55:00.000+01:00

Developers sometimes enjoy configuring their JDBC datasources after their own likings, testing a performance issue by having a gazzillion threads to the database "just in case we need them".
Well in my opinion, if you need what is obviously too many threads, then there is a coding issue.
So to make people think twice before flooding a service with connections I've started limiting the number of sessions they are allowed to have.
Easy as always to do in Oracle. First we allow resource limits by setting the resource_limit to true, then we simply create a profile and assign that to the user. The profile can set the limit of how many session a user is allowed to have.

A little demonstration;

oracle@htpc:~$ rsqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 30 21:56:12 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> show parameter resource_limit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
SQL> alter system set RESOURCE_LIMIT=true scope=both;

System altered.

SQL> create profile sesslimit limit sessions_per_user 2;

Profile created.

SQL> create user fish identified by fish123
  2  default tablespace data profile sesslimit;

User created.

SQL> grant create session to fish;

Grant succeeded.

SQL> connect fish/fish123
Connected.
SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- I'll start two sessions in another terminal.
SQL> connect fish/fish123
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


SQL>

Using SERVICE_NAMES in Oracle

2007-07-08T18:42:00.000+01:00

The use of "SERVICE_NAMES" in Oracle is quite an old and probably well known feature but perhaps not everyone is familiar with it yet.
Got asked today about a recovery scenario where the administrator had a failed instance (broken data files, no logs, no backups, just a nightly exp), a new database was created with 'dbca', but with a new name to test importing the exp file.
All worked fine, but there was a problem with the database name. The application had the service name set in a number of config files and there was also a number of ETL scripts with service names hardcoded. The thinking at the time was to delete the old instance, remove all traces of it (oratab etc.) and then create it *again* with the same name.
Now hold on here, we have tested the imp in a new database, all is fine and all we want to do is allow connections to the old database instance name?
That's pretty much a one-liner, not a new database.
We can simply add the new name we want to "listen on" to the SERVICE_NAMES parameter.
Easy peasy.

Ok, here is what we should do. Quite a long example for something simple.
But hey, just want to make it clear.

oracle@htpc:admin$ lsnrctl status
-- What's the current db_name and service_names?
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:31:22

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                08-JUL-2007 18:23:39
Uptime                    0 days 0 hr. 7 min. 43 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/10g/network/admin/listener.ora
Listener Log File         /u01/oracle/10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "peggy" has 2 instance(s).
  Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...
  Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggyXDB" has 1 instance(s).
  Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy_XPT" has 1 instance(s).
  Instance "peggy", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:24 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> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      peggy
SQL> show parameter service_names

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      peggy
SQL> 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- What can we connect to?
oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/peggy

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:53 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> 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:31:58 2007

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:
-- Ouch, that's the one we want!

oracle@htpc:admin$ rsqlplus hlinden/hlinden as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:32:01 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

-- Here is the 'one-liner'
SQL> alter system set service_names='peggy,dog' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  599785472 bytes
Fixed Size                  2022600 bytes
Variable Size             167772984 bytes
Database Buffers          423624704 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.
SQL> 
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
-- Let's see what changed. What can we connect to now?
oracle@htpc:admin$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 08-JUL-2007 18:33:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                08-JUL-2007 18:23:39
Uptime                    0 days 0 hr. 10 min. 18 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/10g/network/admin/listener.ora
Listener Log File         /u01/oracle/10g/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=htpc)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dog" has 1 instance(s).
  Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy" has 2 instance(s).
  Instance "peggy", status UNKNOWN, has 1 handler(s) for this service...
  Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggyXDB" has 1 instance(s).
  Instance "peggy", status READY, has 1 handler(s) for this service...
Service "peggy_XPT" has 1 instance(s).
  Instance "peggy", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@htpc:admin$ rsqlplus hlinden/hlinden@//htpc/dog

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:34:18 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
-- It works, but where are we?

SQL> select sys_context('userenv','SERVICE_NAME') from dual;

SYS_CONTEXT('USERENV','SERVICE_NAME')
------------------------------------------------------------------------------------------------------------------------
dog

SQL> select sys_context('userenv','DB_NAME') from dual;

SYS_CONTEXT('USERENV','DB_NAME')
------------------------------------------------------------------------------------------------------------------------
peggy

The new top 500 supercomputer list is out!

2007-07-01T22:48:00.000+01:00

Finally had some time to look at the latest Top500 supercomputer list released last week at ISC '07 last week.

Lots of changes compared to the old list; two quite interesting new things. First of all is Intel multi-core chips, secondly we have Blades, loads and loads of blades.
A significant number of entries on the list are using Intel Xeon 5160 3.0GHz dual-core CPU's and there is also a number of quad-core systems.
Another interesting point is that the crown in terms of number of systems on the list has been passed from IBM (with 192 systems) to HP (with 201 systems).
The HP Cluster Platform BL460c holds quite a few spots on the list. Can't say I've heard much about HP's HPC offering so far, they're not exactly beating the drum on that one (compared to the IBM guy who's calling me every 2 months).

SGI holds a few new slots on the list as well, which is extra fun to see considering the hard last year SGI have had. One Altix system even made it to number 10 on the list (with Columbia on number 13).

Dell teaming with Oracle Enterprise Linux?

2007-06-28T09:32:00.001+01:00

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

2007-06-24T20:54:00.001+01:00

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

2007-06-14T12:19:00.000+01:00

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

2007-06-12T23:05:00.000+01:00

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

2007-06-10T17:34:00.001+01:00

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

2007-05-30T21:55:00.000+01:00

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

2007-05-28T10:59:00.000+01:00

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.

Insider for Oracle - funky GUI bits

2007-05-09T21:47:00.000+01:00

We've been doing a lot of application benchmarking and tuning at work in the last couple of months as we are prepare for a new major release. I've been involved in the Oracle side of things and have spent a lot of time tracking down expensive (or plain weird) SQL-statements and trying to optimize the Oracle databases we use.

After spending about 6 - 7 hours a day running my normal sqlplus scripts over and over trying different things, monitoring benchmark runs I got a bit bored of text output and had a quick google for some sort of basic v$whats_up GUI tool and found this little cool little app called Insider for Oracle, it a great app for doing spot-on monitoring, sort of like Spot light from Quest, but without the hassle. Insider tries to be a more proactive tool with round the clock monitoring, but for that, it's no good.

The thing I find it useful for is when we start a benchmark I can trail what's going on with the system. See new SQL statements, wait events, SGA usage, PGA usage, hot segments etc. Nothing I can't do from sqlplus but it makes my tasks a bit easier.
Price wise it's quite a bargain for what I use it for, the license is for each database on the application "workspace" (i.e. how many databases you want to look at at once). And for what I use it for just looking at one DB at a time, that works out pretty cheap, 599USD or so. Cheap as chips.
I've found a couple of bugs (and submitted bug reports accordingly) and I've even submitted a couple of enhancement suggestions.
Check out their company blog.

OpenSolaris SXDE upgrade time

2007-05-07T22:04:00.000+01:00

It's been a while since I last played with Solaris Express Community Edition, so long that it actually changed names. There are now two versions, one for "random people" and one version for developers. SXCE is not released as often as SXDE but is more tested and stable, SXDE also comes with a lot more goodies, like Sun Studio 11 and Netbeans.
Anyway, the install of SXDE b63 under VMWare workstation 6 RC was as easy as ever.
However, at first boot I almost thought the VM was frozen for a minute or so, after grub it just sits there with a cursor in the corner of the screen for about 2 minutes before kicking off the kernel. All good after that though.
One thing I wanted to test was the new simplified resource capping in Solaris Zones. It was very straightforward and easy to use.
A couple of extra commands in zonecfg and the zone is capped to one core and 512Megs of memory. No more messing around with projects, except if you want really fine grained capping that is.
Cool!

Took a screenshoot:

How to do an "insert ignore" in Oracle

2007-04-30T20:28:00.000+01:00

Ok, I should start off with a disclaimer. This is not a good idea to do.
Using the "insert ignore" statement is a way to let MySQL insert data from a dataset which may contain duplicate constraints to existing data, and simply skip the duplicate row.
Sounds like a great way to screw up data doesn't it? Should not be used unless you really know what is going on.

Ok, in MySQL we can do this.

mysql> insert ignore into a select * from b;
Query OK, 1 row affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

But can this be done in Oracle without to much fuss? After a discussion on IRC a guy asked why can't we simply use the merge statement?
Well we can and it is probably a quite good suggestion if you *really* want to ditch those dupe rows.

-- So what do we got?
SQL> select * from a;

         A          B
---------- ----------
         1          2
         2          3

SQL> select * from b;

         A          B
---------- ----------
         1          3
         3          4

SQL> merge into a using b on (a.a=b.a) 
  when not matched then insert values (b.a,b.b);

1 row merged.

SQL> select * from a;

         A          B
---------- ----------
         1          2
         2          3
         3          4

SQL>

-- Let's rollback that and have a look at the exeuction plan.

SQL> rollback;

Rollback complete.

SQL> set autotrace on
SQL> merge into a using b on (a.a=b.a) 
  when not matched then insert values (b.a,b.b);

1 row merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 1973318225

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |      |     2 |    64 |     7  (15)| 00:00:01 |
|   1 |  MERGE               | A    |       |       |            |          |
|   2 |   VIEW               |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     2 |    64 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| B    |     2 |    52 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| A    |     2 |    12 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."A"(+)="B"."A")

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        819  bytes sent via SQL*Net to client
        778  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Looks ok.

Oracle under CentOS 5

2007-04-15T20:41:00.000+01:00

I'm a bit late on this one, but ...
CentOS version 5 was released the other day. For those of you unfamiliar with CentOS it is RedHat Enterprise Linux recompiled and supported by the open source community.
Pretty much RHEL for free.
I can't say there is anything *really* exciting in the new version of RedHat, sure there is Xen support and the general updated versions of the applications included.
New version of Open Office, PostgreSQL etc.

One big difference is that everything has to be NTPL threaded. NPTL threading is the first real *good* threading implementation in Linux. The old Linux threads model is way old. Although NPTL has been available (and the default) in RHEL since version 3, it is not the only supported threading model.

-So what does this mean for Oracle?
No more LD_ASSUME_KERNEL hacks.
10g is fine with this, no problem at all. However 9i will have some issues with this, the old way to get Oracle running was simply to set LD_ASSUME_KERNEL to 2.4.19 and Linux would use the Linux threads model. No go in version 5.
Big question, will Oracle support 9i under RHEL5? I'm honestly not to bothered, by the time RHEL5 is mature and tested I really hope that people have switched to 10gR2.
9iR2 has been around for a lng time now and will be for some time. But new system design implementation on 9iR2?

I've done a quick test install of Centos 5 and 10gR2 (10.2.0.1 and patchset 10.2.0.2) and the install was very painless, pretty similar to Centos 4 with a few changes in the RPM's needed.
RedHat did add a few SHM parameters to sysctl.conf so that section needs reviewing though.

New dual socket AMD system from Dell

2007-04-11T23:25:00.000+01:00

Dell just introduced a new two socket AMD Opteron system, the PowerEdge 2970, a system designed to compete with Sun x4200 and the HP DL385 G2.
Fairly similar to the Intel-based PowerEdge 2950 but with AMD procs. Same redundancy with dual psu's, two onboard nic's and the onboard DRAC5 remote management adapter.
Comes default with the 8 slot 2.5" SAS backplane which is nice to see. I'm all in favour of 2.5" drives over 3.5" in small servers. Up to 32Gb RAM, with expensive 4GB DIMMS that is, 32Gb comes in at about $35000 USD :)
Perfect little J2EE application server running Linux.

It's interesting to see the latest trends in that area. A number of our clients, major investment banks, are currently in the middle of Linux migration projects.
One thing I think slowed Linux adaptation in past has been the "32-bit barrier" and the limit it puts on memory allocation. A J2EE app with a maximum heap size of about 1.5Gb isn't very popular. 8 or even 16Gb is not unusual to see on the Solaris/SPARC side.
Now with AMD Opteron processors (and Intel EM64T) and a mature 64-bit Linux environment that problem has gone away. Websphere 6.0.2 runs perfectly on my test machine with 6Gb heap, performance is great.
Actually all but one of our "Linux clients" are moving to AMD machines over Intel Xeon's, HP and Sun AMD based hardware only so far.
One good example is a project that is currently being migrated from a six processor Sun v890 to a HP DL585, they even got a small performance increase.
Sad to see the SPARC's go but fun to see new platforms coming. Linux one one side and Solaris 10 on the other side.
But the SPARC's will be back, the new "rock" SPARC processor just hit silicone and with it's massive coherent memory access space of 256 Tb, the expected number of cores and performance. It will kick some serious ass.

Understanding Oracle pseudo-columns

2007-03-23T15:18:00.000Z

All the cool people I meet at Oracle events keep referring to "function based indexes" as pseudo-columns. I never really understood the whole thing about it, sure I figures one could consider it a pseudo-column as it was sort of a calculated value that was stored in the database. Never thought much about investigating it either.
However, when playing around with some composite indexes including functions today I finally got it. They are indeed pseudo-columns, or calculated columns as I think Oracle wants to call them in 11g.

As always,
an example:

SQL> create table d as select * from all_objects;

Table created.

SQL> insert into d select * from d;

66242 rows created.

SQL> update d set object_id=rownum;

132484 rows updated.

-- Ok, got a test table. Let's index it with some pseudo stuff.SQL> create index d_1 on d(upper(status));

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
D_1                            SYS_NC00014$

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")

SQL> create index d_2 on d(object_id,upper(status),timestamp);

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
D_1                            SYS_NC00014$
D_2                            OBJECT_ID
D_2                            SYS_NC00014$
D_2                            TIMESTAMP

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")
D_2                            UPPER("STATUS")

-- Thats the index bit, we got two indexes with the same "function",
-- so Oracle will index the same pseudo column! Eureka!
-- Some tests then...SQL> set autotrace traceonly exp
SQL> select object_name from d where upper(status)='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement

-- A column can go in the where clause, even pseudo-columns.SQL> select object_name from d where sys_nc00014$='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement


-- Since it is a column we can obviously select it as well.SQL> select SYS_NC00014$ from d where object_id=500;

SYS_NC0
-------
VALID

Execution Plan
----------------------------------------------------------
Plan hash value: 2591304836

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    18 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| D_2  |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=500)

Note
-----
   - dynamic sampling used for this statement

-- You can even rename the pseudo-column.
-- Oracle did actually dump core after I did that so better not.SQL> alter table d rename column SYS_NC00014$ to dog;

Table altered.

SQL> alter table d rename column dog to SYS_NC00014$;

Table altered.

Solaris 10 NFS version when using the automounter

2007-03-21T22:58:00.000Z

Classic problem, new stuff is cool and all. But sometimes people (like myself) can't be bothered with configuring everything to the extreme so all new features are working and are compatible. For basic things, the basics is enough.
Take NFSv4, a default Linux box these days do support NFSv4 but I'm usually to lazy to configure it and just slap in an old NFSv3 line in my exports file. Fine with me, fine with most things, except one thing.
The Solaris 10 automounter, it detects the NFSv4 support and automatically tries to mount the NFS share in v4 mode when browsing /net.
Sidenote, if you are not familiar with the /net feature in Linux and Solaris it's pretty neat. See it as a cli version of "My network neighbourhood" in Windows. You can simply cd to /net/<machine name>/<share path> and the automounter will mount that share on that machine. Easy peasy.
Back to my problem, what do I want? I want the default NFS mount level to be vers=3.
Easy change, just uncomment and set NFS_CLIENT_MAXVERS to 3 in /etc/default/nfs.

Here's an example, the machine dolphin is my old trustworthy Linux workstation (yes, I like examples):

[root@marble /]$ cd /net/dolphin/
[root@marble dolphin]$ ls
shared
[root@marble dolphin]$ cd shared
bash: cd: shared: Permission denied
[root@marble dolphin]$ grep NFS_CLIENT_VERSMAX /etc/default/nfs
#NFS_CLIENT_VERSMAX=4
[root@marble dolphin]$ vim /etc/default/nfs
.. edit ... 
[root@marble dolphin]$ grep NFS_CLIENT_VERSMAX /etc/default/nfs
NFS_CLIENT_VERSMAX=3
[root@marble dolphin]$ cd shared
[root@marble shared]$ ls -l
total 84
drwxrwxr-x   6 hlinden  hlinden  4096 Mar  8 15:29 apps
drwxrwxr-x   6 hlinden  hlinden  4096 Sep  7  2006 archive
drwxrwxr-x  11 hlinden  hlinden 12288 Mar 21 16:32 download
drwxrwxr-x  15 hlinden  hlinden  4096 Mar  1 11:05 tmp
[root@marble shared]$

Oracle constraints in XML data

2007-02-25T16:48:00.000Z

Oracle introduced pretty cool XML support in 9i, it's even cooler in 10g and I don't understand why people don't use it more often. I keep seeing XML data stored in CLOB's all the time. Why not store it as proper XML, it's possible to index, query and even update individual XML elements, attributes or nodes. Fast, simple, easy, no need for full text indexes. Performance of xpath queries is pretty good if indexed correctly, 25 000 XML documents 10k each is still in the sub second range when hitting an index.

One thing that can be quite nice to have in the XML store is constraints to avoid duplicate data, indexes on XML data are pretty much plain standard pseudo-column indexes (or "functional indexes" as some refer to them as).
We just use the basic extract() or extractValue() functions in Oracles XML feature set.

Here's an example on how to to create unique constraints (indexes) on XML elements (or attributes):

SQL> create table x (a xmltype);

Table created.

SQL> insert into x values('<type><name>dog</name></type>');

1 row created.

SQL> insert into x values('<type><name>cat</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/name'));

Index created.

SQL> insert into x values('<type><name>cat</name></type>');
insert into x values('<type><name>cat</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x values('<type><name>fish</name></type>');

1 row created.

-- Lets try the constraint on an attribute.
-- attributes are handeled just like elements but need a @ sign prefix

SQL> drop index xui;

Index dropped.

SQL> truncate table x;

Table truncated.

SQL> insert into x(a) values('<type id="1"><name>sally</name></type>');

1 row created.

SQL> insert into x(a) values('<type id="2"><name>bob</name></type>');

1 row created.

SQL> create unique index xui on x(extractValue(a, '/type/@id'));

Index created.

SQL> insert into x(a) values('<type id="2"><name>carol</name></type>');
insert into x(a) values('<type id="2"><name>carol</name></type>')
*
ERROR at line 1:
ORA-00001: unique constraint (HLINDEN.XUI) violated


SQL> insert into x(a) values('<type id="3"><name>carol</name></type>');

1 row created.

-- Ok, lets see if we can have duplicate names.

SQL> insert into x(a) values('<type id="4"><name>carol</name></type>');

1 row created.

SQL>

And some docs to read.

Viewing bind variable values in 10g

2007-02-21T22:43:00.000Z

Oracle 10g introduced a couple of new nice views to help tune queries that use bind variables.
One cool view is v$sql_bind_capture, this view hold the latest captured value for each bind variable in queries that has been run.
First have a look in v$sql to find the SQL query you are looking for, join the sql_id to v$sql_bind_capture and to view the bind variable values for that query.
Example:

select
  sql_id,
  t.sql_text SQL_TEXT,
  b.name BIND_NAME,
  b.value_string BIND_STRING
from
  v$sql t
  join v$sql_bind_capture b
  using (sql_id)
where
  b.value_string is not null
  and sql_id='f8pavn1bvsj7t'
/

SQL_TEXT                                    BIND_NAME  BIND_STRIN
------------------------------------------- ---------- ----------
select con#,obj#,rcon#,enabled,nvl(defer,0) :1         9110
 from cdef$ where robj#=:1

I found a pretty bad example here, an Oracle internal dictionary query, but it should show the point.

Mixing Dell PowerEdge 1955 and 1855 blades

2007-02-21T17:11:00.000Z

After looking around the Internet for information regarding mixing Dell PowerEdge 1855 and 1955 blades in one enclosure I found some varying "opinions" whether it works or not.

To get things right.
Yes, it is possible to mix and match any 1855 and 1955 blades in one single enclosure
However, there are two small requirements.

The DRAC/MC needs firmware 1.3 or later (everyone should upgrade to 1.3, even if you don't have any 1955 blades).
You need the digital KVM modules, they rock, get them!

The 1955 blades plug-in during operations just as any 1855 blades would, you get a sensor detect and they power on just fine.

Printout from the DRAC/MC:

[Server Module Power Consumption Table]
<Slot#> <Server Name>    <Blade Type> <Power State> <Current/Max Consumption>
 1       Server-1         PE1855       ON            300/300W
 2       Server-2         PE1855       ON            300/300W
 3       Server-3         PE1855       ON            300/300W
 4       Server-4         PE1855       ON            300/300W
 5       Server-5         PE1855       ON            300/300W
 6       Server-6         PE1855       ON            300/300W
 7       Server-7         PE1855       ON            300/300W
 8       Server-8         PE1955       ON            304/304W
 9       Server-9         PE1955       ON            304/304W
 10      Server-10        N/A          N/A           N/A

Cool!

Extended deployment

2007-02-15T21:36:00.000Z

Ok, I admit it. I've sucked at blogging lately.
In my defence I have had a quite annoying cold that's been hanging on for the last two week, I *really* hate having a fever, I get cranky. I wrote half a blog post about using Oracle XE for reporting with materialized views but never finished it, will probably do that tomorrow or Monday.
Beyond that I just haven't had anything exciting to do, at work I've been working with Sybase, can't say that's very exciting. Especially not very exciting when one has to support a major investment bank which has a team of Sybase DBA's which seems to know even less about Sybase than I do (and trust me, I'm a Sybase noob).

One exciting thing I've been toying with is IBM Websphere Extended Deployment (XD). Everyone knows that Websphere isn't very exciting, the XD edition has got some pretty cool features though. And one has to love how simple IBM explains the huuuge cost savings it brings.

- "A client can buy a few as four medium sized mainframes and deploy a number of applications across these machines and achieve unprecedented utilization".

Ok, back up a bit here IBM. The concept of Websphere XD works pretty much all platforms. So why bring out the mainframes, I can't really see many clients needing four mainframes for a normal Websphere deployment.
Anyway, what XD brings is pretty much a resource manager and an object grid. Say you have a blade server with 10 blades, you want to deploy two applications which will be load balanced. Instead of telling Websphere to deploy each application on five servers you define metrics of what kind of response times you expect the applications to have, then Websphere will allocate as much resource as needed (or send angry emails requesting more servers). Say one application runs on 2 servers and the other application on the remaining 8, then every Friday everyone in the company needs to use the first application for a couple of hours. Websphere will see the extra utilization and assign a couple of more servers to this app, then when not used any more they will be returned to the second application. Neat!
Another thing you can use XD for is to distribute a large set of data, you can write a distributed application and requests will be sent to the server holding that data. It's way cheaper to buy 16 servers with 16Gb RAM each than to buy one server with 256Gb RAM, let each server hold a piece of the data in RAM in an object grid.
XD also brings some cool monitoring features and other crud. See the comparison of the different versions here.

Enough about Websphere, it's not that great. It's just a nice challange to work with. :-)

bash globbing and dot-files

2007-02-01T12:58:00.000Z

Found a nifty little feature in bash.

Globbing is expanding file pattterns, like when you typ "ls -l file*" in bash it is not ls that does the file matching and filtering. bash will glob ("expand") the file list file* and ls will get all the files as arguments.
Now, per default bash doesn't glob dot-files. If I do "ls *" I will not get .bashrc and .bash_profile etc. Luckily it is easy to change this behavior. Set the bash option dotglob to enabled and it works!
Example:


[hlinden@spinner testdir]$ ls -Al
total 0
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 .dotfile1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 .dotfile2
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file2
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file3
[hlinden@spinner testdir]$ echo *
file1 file2 file3
[hlinden@spinner testdir]$ shopt -s dotglob
[hlinden@spinner testdir]$ echo *
.dotfile1 .dotfile2 file1 file2 file3
[hlinden@spinner testdir]$

Just put shopt -s dotglob in your .bashrc file or in a global /etc/profile.d file.
Another quite nice globbing feature is to have case insensitive globbing.
Check this out:

[hlinden@spinner testdir]$ shopt -s nocaseglob
[hlinden@spinner testdir]$ ls -l F*1
-rw-rw-r--  1 hlinden hlinden 0 Feb  1  2007 file1
[hlinden@spinner testdir]$

Flashback a user or schema in Oracle

2007-01-21T23:34:00.000Z

Since 10g we've had the quite cool feature 'flashback', or rather we've had it since waay back. But now Oracle gave us an easy way to use it. We have the recyclebin as a new feature though. Cool but sometimes a bit confusing.

Flashback can be done at two levels, the whole database or for a single object. Why on earth didn't Oracle include a "flashback schema" feature. I would guess most users use flashback when doing testing, like schema upgrades and things like that. And if the test fails you want to restore the tables to before the scripts ran (ok workspaces comes to mind here but that's another post).
I took some time and wrote a script to flashback all tables and dependant objects from the recyclebin and to flashback existing tables to a previous version.
Since flashback require row movement to be enabled the script checks if it needs to enable that for the tables and will do so if needed.

-- Ok, let's create a little mess we can clear up.
SQL> drop table i;

Table dropped.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

SQL> update a set d=sysdate;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 21-JAN-07 11.29.53.000000 PM
         2 21-JAN-07 11.29.53.000000 PM

-- Commited and all! We sure screwed that up

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
T                              TABLE
D                              TABLE
BIN$J5ZNZK2dxdvgQKjAKF9ZFQ==$0 TABLE

4 rows selected.

-- Lets run the script to generate our flashback script
-- The script will prompt you for the number of minutes you want to go back
SQL> @flashback_user
How far back do you want to flashback (in minutes)?
Enter value for minute: 8
         8
testuser

Spooling flashback_user_testuser.sql

alter table T enable row movement;
flashback table A to timestamp sysdate - interval '8' minute;
flashback table T to timestamp sysdate - interval '8' minute;
flashback table D to timestamp sysdate - interval '8' minute;
flashback table I to before drop;

SQL> @flashback_user_testuser

Table altered.


Flashback complete.


Flashback complete.


Flashback complete.


Flashback complete.

SQL> select tname,tabtype from tab;

TNAME                          TABTYPE
------------------------------ -------
A                              TABLE
D                              TABLE
T                              TABLE
I                              TABLE

4 rows selected.

SQL> select * from a;

        ID D
---------- ----------------------------
         1 30-NOV-06 10.34.35.000000 PM
         2 06-NOV-06 08.15.54.000000 AM

2 rows selected.
-- And we are back in business.

Download my script here: http://halisway.hifichoice.com/flashback_user.sql.

Read more about flashback here.

Balancing SGA in Oracle 9i

2007-01-16T10:22:00.000Z

Now with 10g having sga_target for automatically managing your SGA memory size some people get lazy and just forget about SGA. But most of us still have 9i databases to maintain.

Time for a quick overview on how to reduce disk i/o with the help of db_buffers.

I've got a Sun v480 with 4Gb RAM running one instance, it's not under a lot of load but could do with some tuning.
Lets look how our current SGA settings looks:

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
sga_max_size                         big integer 2283246744

SQL> select * from v$sgastat order by pool,bytes;

POOL        NAME                            BYTES
----------- -------------------------- ----------
java pool   free memory                  67108864
shared pool trigger source                    152
shared pool fixed allocation callback         496
shared pool trigger defini                    576
shared pool trigger inform                    920
shared pool PLS non-lib hp                   2088
shared pool joxs heap init                   4240
shared pool KQR S SO                         5416
shared pool table definiti                  18984
shared pool PX subheap                      28096
shared pool session heap                    29560
shared pool KGK heap                        33368
shared pool DG Broker heap                  39200
shared pool MTTR advisory                  388024
shared pool errors                         390856
shared pool sessions                       905840
shared pool message pool freequeue         940944
shared pool sim memory hea                1014808
shared pool KSXR receive buffers          1034000
shared pool FileIdentificatonBlock        1791824
shared pool parameters                    1827072
shared pool PL/SQL DIANA                  1960192
shared pool 1M buffer                     2098176
shared pool Checkpoint queue              2622720
shared pool KQR M PO                      2885104
shared pool dictionary cache              3229952
shared pool KQR L PO                      3372488
shared pool event statistics per sess     3762720
shared pool KQR L SO                      5260312
shared pool KGLS heap                     5424464
shared pool KQR M SO                      6135256
shared pool FileOpenBlock                11813536
shared pool PL/SQL MPCODE                22044808
shared pool miscellaneous                25966760
shared pool library cache                66948552
shared pool sql area                    166511368
shared pool free memory                 248709688
            fixed_sga                      734360
            log_buffer                     787456
            buffer_cache               1073741824

40 rows selected.

Ok, we can see that we do have quite some free memory
in the shared pool, what should we do with that?
from looking at iostat and wait statistics I've noticed that the disk
subsystem (a simple Sun 3310 in this case) is getting a bit hammered sometimes.
Lets see how the db cache is doing.

SQL> select size_factor, size_for_estimate, estd_physical_read_factor
  2    from v$db_cache_advice order by size_factor;

SIZE_FACTOR SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR
----------- ----------------- -------------------------
      .0938                96                 4344.0852
      .1875               192                  163.0333
      .2813               288                    23.782
       .375               384                   22.5502
      .4688               480                   20.6444
      .5625               576                   10.4806
      .6563               672                    1.4957
        .75               768                    1.0948
      .8438               864                    1.0367
      .9375               960                    1.0102
          1              1024                         1
     1.0313              1056                     .9999
      1.125              1152                      .813
     1.2188              1248                     .7185
     1.3125              1344                     .5829
     1.4063              1440                      .536 <- Lets aim for this
        1.5              1536                     .5196
     1.5938              1632                     .5139
     1.6875              1728                     .5079
     1.7813              1824                     .5055
      1.875              1920                     .5043

21 rows selected.

Right, from looking in the v$db_chace_advice view we can determine that a few hundred Mb more of cache would be a good idea. There is quite a lot of free memory at the OS level, so we can probably increase the sga_max_size with 250Mb as well.

-- Reduce the shared pool with 150Mb.
SQL> alter system set shared_pool=379584512 scope=spfile

System altered.

-- Add max size with 250Mb
SQL> alter system set sga_max_size=2545390744 scope=spfile;

System altered.

-- Add the 250Mb we added + the 150Mb we reduced from the shared pool.
SQL> alter system set db_cache_size=1493172224 scope=spfile;

System altered.

-- Restart Oracle
SQL> shutdown immediate
...
SQL> startup

That should do the trick, lets see what the server thinks about things in a couple a weeks time or so.

Don't forget:
Please have more than one look at the statistics before you do anything, the free memory in the shared pool could be free today but needed all other days. Take a few snapshots over time and analyze the results before doing any changes. And whatever you do, don't over allocate SGA.
The last thing you want is the SGA to be swapped to disk or the system swapping out other things.

Cleaning up sqlplus cut and pastes in vim

2007-01-10T11:08:00.000Z

Classical problem, you write a short piece of SQL in sqlplus, you then want to cut and paste it to vim edit it. But then you get the really annoying line numbers in front of each line.
Sure you can use save myquery in sqlplus and open the file myquery.sql in vim, but perhaps you want to run vim on another machine, perhaps you run gvim.
So ok, time to get rid of the line numbers.

Here's what we got in sqlplus:

SQL> list
  1  select
  2  e.ename,
  3  d.dname
  4  from
  5  dept d,
  6  emp e
  7  where
  8  d.deptno=e.deptno
  9* order by e.ename
SQL>

Lets cut n paste those numbered lines to a vim session.
We now need to delete the first 4 characters of each line. Easy.
Switch to visual mode, jump to the end of the file, step two rows right, hit delete char.
Or in vim command terms (in normal mode (esc-esc)).

ctrl-v G l l x

Breakdown:
ctrl-v to get in to visual mode
G to jump to the end of the file
l to move the cursor right
x to delete the selected area

Visual mode is quite powerful, you can use almost the same commands to comment out code.
Place the cursor in the first line you want to comment out, hit ctrl-v to do a visual mark, use j to move the cursor down to the last line you want to comment out, hit I to get insert, type two hyphens and hit escape. Done.
In vim command terms:

ctrl-v j j j I -- esc

Time for HP Proliant?

2007-01-07T19:43:00.000Z

Now I can't say I've blogged much about HP server, simply because it's not been very exciting hardware. Dell and Sun have always had better products at better prices and have always been better to deal with.
Now recently, especially with the G5 series of it's Proliant line HP have some quite exciting and unique products. The one thing I've always liked with HP is that they've been a long term AMD Opteron supporter. The where the second big server vendor after IBM to launch Opteron based servers. The DL385 was unique at the time and served me well in a previous company I worked for. The following DL585 (4 sockets, 128Gb RAM max and 8x 2.5" SAS slots) is still a pretty ok Oracle server, the G2 version comes with a pretty cool front accessible CPU and memory drawer for easy upgrades and repairs is even better. But price wise it's on par with the Sun x4600, and the x4600 will let you scale to 8-sockets over of the 4-socket DL585.
The Intel equivalent, the DL580 offers front accessible memory access, CPU's are still top lid accessible.

Now with the Intel-based (dual and quad core Xeon 3000, 5000, 5100 and 5300 boxes) machines in the "G5" series we have a few pretty cool new features, the focus on 2.5" SAS drives is nice to see, Sun is going the same way but Dell is clinging on to 3.5" drives (probably to keep the price tag down), some Dell machines can be fitted with a 2.5" backplane (like the PE1950).
The DL360 makes a decent app server with two sockets and up to 32Gb RAM, nice 6 slot SAS backplane and the DL380 adds more PCI I/O and can take up to 8 drives.
Big brother (in the 2-socket famaily) is the ML370, this is a pretty large machine for a 2-socket box, 5U in the rack, but it makes a great VMWare server, loads of local disk (16 SAS slots), loads of I/O and can take a massive 64Gb RAM. Great for stand-alone Oracle deployments as well.
The smallest member in the 300-family is the DL320, pretty standard 1-socket Xeon 3000 machine (3.5" drives though), makes a decent webserver or network appliance.

Ok, now on to the reason I actually started getting excited about HP hardware again.
The recently introduced DL 320s hybrid data server (or "storage server").
It's pretty much the same as the DL320, but the "s" adds some pretty cool stuff. It can take 12 SAS or SATA drives. Which makes it a pretty interesting machine, one could say this is the "HP Thumper", the HP equivalent of the Sun x4500. It's not as big and impressive as the x4500 but it's still interesting. Applications like distributed data mining, data analysis and video processing fits like a glove on this box.

A few bad things about HP, everything is extra. The price you see is not the price you pay. The list price of a normal, say DL380, includes exactly what is listed. You even have to add the second sets of fans to get redundancy. Half the features in the iLO2 (remote management interface) require extra licenses to be activated. I think HP needs to re-think this. As it stands today I would not look at buying HP servers as generic servers, possibly if I have a project that require something only HP can offer (like a DL320s). It simply doesn't make sense financially.
If I want standard server, I go to Dell. If I want business critical servers, I go to Sun. It's as easy as that. If HP can offer competitive pricing I would not hesitate to at least evaluate deploying HP instead of Dell and in some cases Sun boxes.

Happy new 2007

2007-01-02T18:16:00.000Z

It's been a pretty good year. Lots of cool stuff has happened and lots of interesting technology is maturing and evolving. Most notably OpenSolaris, While most other UNIX-vendors are pretty much sitting on their behinds watching time go by Sun is kicking in a higher gear. ZFS is being adopted by the FreeBSD and MacOS communities. Iscsi is maturing and VMWare is getting some pretty cool features, Veritas is giving away the basic edition of Storage Foundation. Oracle 11g looks very promising.

All well, I'm flying back to the UK tomorrow (finally), can't wait to get back to London.

Merry Christmas and VMWare 6

2006-12-26T16:41:00.000Z

.. and all that. :)

Been chilling in Sweden for the last few days, had a terrible flight back with a 2 hour delay due to fog over south east england and then a very annoying crying baby on the plane.
We even had a distubance in the queue to the gate, some guy from the middle east was making a scene and started threatening a few other people for bumping in to him. I have to admit, I knew it wasn't a good idea to mess with airport security, but these guys where hard. The scene was quite funny, when they asked him to come with them and he started questioning them they said something in the lines of "Sir, you have 2 seconds to comply or we will remove you with force." We didn't seem him again after that.
Anyway, got socks and a few good CD's for Christmas. Always good with new music.
I installed VMWare Workstation 6 beta yesterday and had a play around with that. A word of warning, although it has some cool features and is meant to be a bit faster the beta version is a debug build and in classic vmware-style the debug builds are slow... really slow. Booting CentOS 4 takes at least 50% longer. The "freeze bug" when starting a VM in Vista is fixed though.

All jolly good fun!

Using Solaris projects with Oracle

2006-12-21T21:44:00.000Z

In older versions of Solaris (9 and earlier) we used to set the system resource control in /etc/system, in Solaris 10 and later we do it in a neater way. No more reboots or mocking about, all online, even while Oracle is running.
It's all about projects now.
I've just finished installing 10gR2 under Solaris Express Community Release Build 54 (long name ey?) under VMware on my new laptop.
Example project:

root@solvm[~]$ projadd -U oracle -K \
"project.max-shm-memory=(priv,4g,deny);\
project.max-sem-nsems=(priv,256,deny);\
project.max-sem-ids=(priv,100,deny);\
project.max-shm-ids=(priv,100,deny)" oraproj

-- Lets review our project

root@solvm[~]$ su - oracle
Sun Microsystems Inc.   SunOS 5.11      snv_54  October 2007
oracle@solvm[~]$ projects
default oraproj
oracle@solvm[~]$ projects -l oraproj
oraproj
        projid : 101
        comment: ""
        users  : oracle
        groups : (none)
        attribs: project.max-sem-ids=(priv,100,deny)
                 project.max-sem-nsems=(priv,256,deny)
                 project.max-shm-ids=(priv,100,deny)
                 project.max-shm-memory=(priv,4294967296,deny)
oracle@solvm[~]$

It's described slightly different in the Oracle installation manual but I'd say my way is slightly better. prctl is legacy. You can use usermod -K project=oraproj oracle to set the new project as the users default project (not really necessary)

Manual operations in an automatic SGA database

2006-12-14T20:56:00.000Z

Oracle 10g introduced the nifty feature of automatic SGA, simply set the parameter sga_target and sga_max_size to the maximum size you can afford and Oracle will do the rest. But we still have a few manual settings, say you want a tablespace with a non-default block size (also a new feature) than the database default and you create, say a 4k block size, tablespace in a 8k database and you allocate a db_4k_cache_size for that. How do Oracle allocate that memory?

Test:

SQL> select name,value from v$parameter
       where name like 'sga_target'
     /

NAME            VALUE
--------------- --------------------
sga_target      599785472

SQL> select name,round(bytes/1024/1024) M from v$sgainfo;

NAME                                              M
---------------------------------------- ----------
Fixed SGA Size                                    2
Redo Buffers                                      6
Buffer Cache Size                               416
Shared Pool Size                                140
Large Pool Size                                   4
Java Pool Size                                    4
Streams Pool Size                                 0
Granule Size                                      4
Maximum SGA Size                                572
Startup overhead in Shared Pool                  68
Free SGA Memory Available                         0

11 rows selected.

SQL> alter system set db_4k_cache_size=4M;

System altered.

SQL>  alter system set db_4k_cache_size=0;

System altered.

-- What actually happend here, from what 
-- did Oracle steal those 4Mb
-- Let's query the v$sga_resize_ops view to see that was done.

SQL> select parameter,oper_type,initial_size,target_size 
       from v$sga_resize_ops 
       where start_time>sysdate-1
     /

PARAMETER        OPER_TYPE     INITIAL_SIZE TARGET_SIZE
---------------- ------------- ------------ -----------
db_cache_size    SHRINK           436207616   427819008
db_4k_cache_size GROW                     0     8388608
db_4k_cache_size SHRINK             8388608           0
db_cache_size    GROW             427819008   436207616

SQL>

Ok, we can see that Oracle shrank the db_cache_size with the requested size and allowed the db_4k_cache_size to grow and when reset back to zero it freed the memory and grew the db_cache_size. Oracle could just as well have allocated the memory to the large_pool if the automatic SGA engine thought that would make more sense.
I've got the last 7 days worth for resize ops in my weekly database report that is e-mailed to me every Monday morning, it's definitely worth keeping an eye on whats going on. Sometimes it's just easier to configure the SGA manually.

Testing iSCSI

2006-12-11T22:34:00.000Z

I've been reading a lot about iSCSI lately, Sun is pushing the ZFS and iSCSI integration in the latest release of Open Solaris and it's the new hot thing in the Oracle community. I remember testing the Linux target ("server side") a few years ago and back then it was quite flaky, far from production ready.
This time around it seems pretty solid.

I just used the very simple instrucions here to compile the iscsi kernel module in Ubuntu, I created two "zero files" using dd (one for my VMware Linux test and one for my Vista laptop) and added them as LUN's to the /etc/ietd.conf config file.

# cat /etc/ietd.conf
Target starbuck
        Lun 0 Path=/u03/starbuck_lun0.dat,Type=fileio
Target oravm
        Lun 0 Path=/u03/oravm_lun0.dat,Type=fileio

The installation in CentOS 4 was simple, do a yum -y install iscsi-initiator-tools and set the discovery host and initiator name in /etc/iscsi.conf. Starting /etc/init.d/iscsi gives:

iscsi-sfnet: Loading iscsi_sfnet version 4:0.1.11-3
iscsi-sfnet: Control device major number 254
iscsi-sfnet:host3: Session established
scsi3 : SFNet iSCSI driver
  Vendor: IET       Model: VIRTUAL-DISK      Rev: 0
  Type:   Direct-Access                      ANSI SCSI revision: 04
SCSI device sdd: 524288 512-byte hdwr sectors (268 MB)
SCSI device sdd: drive cache: write through
SCSI device sdd: 524288 512-byte hdwr sectors (268 MB)
SCSI device sdd: drive cache: write through
 sdd: unknown partition table
Attached scsi disk sdd at scsi3, channel 0, id 0, lun 0

Create a partition and file system on the whole disk with

printf "n\np\n1\n\n\nw\n" | fdisk /dev/sdd && mkfs.ext3 /dev/sdd1

Done!

Conneting the iSCSI disk in Vista was pretty straight forward as well, add the iSCSI discovery IP and do a refresh of your targets.

Read about Oracle RAC and iSCSI in this article.

Monitoring RMAN I/O performance

2006-12-10T23:47:00.000Z

When trying to cut rman backup times there are a lot of parameters to try to tune, database settings, storage subsystem settings, file system settings etc etc.
It can sometimes be hard to remember all the results you get, luckily Oracle keeps them for you. Just query the v$rman_backup_job_details view for the results.

Example:

column END_TIME format a16
column START_TIME format a16
column COMMAND_ID format a20

select 
 command_id, 
 to_char(start_time,'YYMMDD HH24:MI') start_time,
 to_char(end_time,'YYMMDD HH24:MI') end_time,
 round(input_bytes/1024/1024) backup_size,
 round(INPUT_BYTES_PER_SEC/1024/1024) in_mb_sec,
 round(OUTPUT_BYTES_PER_SEC/1024/1024) out_mb_sec 
from 
 v$rman_backup_job_details 
where
 start_time > sysdate - 7
order 
 by start_time
/

COMMAND_ID           START_TIME       END_TIME         BACKUP_SIZE  IN_MB_SEC OUT_MB_SEC
-------------------- ---------------- ---------------- ----------- ---------- ----------
2006-12-10T00:34:25  061210 00:35     061210 00:45           13722         22         10
2006-12-10T23:17:48  061210 23:17     061210 23:26           13784         26         12

Oracle ASM instance startup script

2006-12-10T20:10:00.000Z

Tought I'd publish my Oracle ASM instance sysv (Redhat/CentOS/Unbreakable etc) init script. The default oracleasm script provided by asmlib only prepares the disks available to ASM, you also need to start the basic instance for ASM. I've set the ASM instance to start just after oracleasm and to be killed just before it. Make sure your normal Oracle instance startup scripts are started in the correct order, i.e. after this script for startup and before this script for shutdown.
It reads the default /etc/sysconfig/oracle config file, if nothing it sets it goes to defaults and should be able to start the instance. If you don't have the ASM instance name set in /etc/sysconfig/oracle (as ASM_SID=FOO) and your instance name is not +ASM you may have to edit the script to change the default ASM SID.

Download the script here. Copy the script to /etc/init.d/ and use chkconfig --add oraasm to add it to startup. Do a chkconfig --list oraasm to verify that the script was added the runlevels you use.

Please report any bugs, problems or suggestions for the script to me.

Date range generation in Oracle

2006-12-05T22:15:00.000Z

Another interesting question someone asked in the IRC #oracle chat.
-How do I create a table with all dates from 2000 until today in it?
It's rather simple in plain SQL, no PL/SQL or scripting required.

SQL> create table date1 (d date);

Table created.

SQL> insert 
  into date1(d) 
select 
  to_date('00-01-01','YY-DD-MM') + level 
from 
  dual 
where 
  (to_date('00-01-01','YY-DD-MM')+level) < sysdate 
connect 
  by level<=1000000
/

2530 rows created.

SQL> select min(d) s, max(d) e from date1;

S         E
--------- ---------
02-JAN-00 05-DEC-06

SQL>

Oracle trace event 10132

2006-12-05T20:32:00.000Z

The new "in" Oracle trace event seems to be 10132, it's pretty cool. Most Oracle DBA's and developers have probably at least heard about trace levels 10046 and 10053.
10132 is a different from the classic two in the sense that it is is really easy for anyone to intepret, the 10132 trace event gives you a full execution plan and optimizer settings in the udump (user_dump_dest) directory.
It's an easy thing to throw in a script or even as a logon trigger when troubleshooting query problems. Think hibernate or other (weird) ORM tool.

Lets take a look:

SQL> alter session set events
  2  '10132 trace name context forever, level 12';

Session altered.

SQL> select e.ename,d.dname from dept d, emp e where
  2  d.deptno=e.deptno;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
...
MILLER     ACCOUNTING

14 rows selected.

SQL>

And the trace we get looks like this.

Laptops - 3 years ago and today

2006-12-03T21:14:00.000Z

So I figured it was time to get a new laptop, my old trustworthy Dell Inspiron 8600 has done a great job but it's starting to show it's age, it's actually 3 years old on the day next week (Yes, remembering the exact date you bought your last laptop is weird, I know). My 8600 had a few scratches and marks but it still looks pretty ok. Performance wise it's not to bad, I've been running Open Solaris for the last 18 months and it's been working great, felt fast and snappy. But when it comes to heavy multi tasking, Solaris container and VMWare it's not really keeping up with times.
After browsing the laptop market it was quite clear that getting a Core2 machine was the best option if I wanted performance. I was very tempted to get a cool Acer Ferrari 4006, the price is pretty great now when the 5000-series is just around the corner, but the AMD ML-40 benchmarks put me off, the Core2 totally trash it. Since my old Dell has been working great I went and got a new Dell to replace it. Although I was looking for a smaller machine this time I actually went for pretty much the exact replacement for the 8600, the new Dell Inspiron 6400. Funny thing, the price of the new machine turned out to be pretty much exactly the same as I payed for my old machine.

	Dell Inspiron 6400	Dell Inspiron 8600
CPU	Intel Core2 Duo 2.0Ghz	Intel Pentium M 1.4GHz
Memory	2048Mb	768Mb
Disk	120Gb 5400rpm	30Gb 5400rpm
Graphics	ATI X1400 128MB	Nvidia Geforce 5250Go
Network	54Mbit wifi + 100Mbit wired	11Mbit Wifi + 100Mbit wired
Optical	DVD+RW	DVD/CD+RW
Pointing device	Touchpad	Touchpad / poiting stick
Screen	1680x1050 high gloss	1680x1050
Weight	2.8 kg	3.1 kg
Size	A bit smaller	Quite big
VMWare speed	Fast	Slow

It's quite nice to be able to boot up a CentOS 4.4 running 10gR2 and still have great performance. Both in the VM and in the host OS.
The one thing I don't like is that the 6400 don't have the pointing stick thingy, just the touch pad. But I'm getting used to it.

Oracle database links using full TNS entries

2006-11-29T11:36:00.000Z

Database links can be most useful, say you want to extract data to a reporting system or something like that. This is a quite common scenario people have problems with and come to the Oracle support IRC channel and ask about, especially developer with just a user account on the remote database and plain user account in the target database. They want to create a database link but don't have access to tnsnames.ora file at the OS level on the server, they may not even have SSH access to the server.
No problem, you can use the full TNS entry pretty much everywhere you can use the TNS alias, just remember to single quote it.

Database link example:

[hlinden@whale dblink]$ rsqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Nov 29 11:17:26 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> select db_link from user_db_links;

no rows selected

SQL> CREATE DATABASE LINK prodsyslink
 CONNECT TO ro_user IDENTIFIED BY ro_user_pass USING
 '(DESCRIPTION=(ADDRESS_LIST=(
   ADDRESS=(PROTOCOL=TCP)(HOST=prodserver)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=prod_db)))'
/

Database link created.

SQL> select db_link from user_db_links;

DB_LINK
------------------------------
PRODSYSLINK

SQL> select * from t@prodsyslink;

         A          B
---------- ----------
         1          2
         2          1

SQL>

Cool, now it's possible to create a nice materialized view or whatever is needed.

Oracle docs about CREATE DATABASE LINK.

Redmond technology

2006-11-26T22:29:00.000Z

Disclimer: Long pro-Microsoft ranting. I know most of my blog readers are hard core Unix geeks so please be warned if you are sensitive to Microsoft material.

Ok, I have to admit it, I'm posting this entry from a machine running Windows Vista.
And on top of that, I have to admit that Microsoft SQL Sever 2005 seems pretty cool. SQL server is not comparable to Oracle by far but it's certainly improved over the 2000 release. I've even fiddled around a bit with C# and OLDEDB providers in Vista. It's pretty sweet, using something like the Vista search API is peanuts, it's easy and clean, all the basics is there and easy accessible.
After using Vista for about a week (No, I did not download the torrent, I'm using a developer licensed copy from MSDN) I quite like most aspects of it, the new aero interface is quite fast, it's "cute" without getting annoying and slow. My favourite feature is the basic search functionality which works pretty much everywhere, you can sort of filter the result set in all views. If you type "Comm" in the Start-menu Windows filters the selection to the Command Prompt, so instead of classic browsing you can just type the first few letters of something and just hit enter, sort of like we have done in Unix for the last 15 years ;). Anyway, without a doubt, I feel more productive using Windows Vista than I have felt in a long time.

I've been quite distanced from the Redmond scene for a while, the "2003 kit" did nothing for me, but as a technologist I have to admit that there are one or two quite interesting things coming from the Redmond campus in the next six months (and I'm not talking about the Zune here.

Another thing that comes to mind here is that Microsoft is in a way shifting away from it's core customer base and trying to gain the interest of Unix shops and "the bigiron people".
Take a product like Exchange 2007, it's a pretty solid mail server (if you pair it with a couple of Postfix releays), but deploying a quite basic Exchange installation is getting pretty tricky, I have to admit I've only seen the beta2 release so far so the final version is probably better, compare that to the super simple and straight forward Exchange 5.4 deployment. Sure, you had a few gotchas in 5.5 and you always swore about all it's shortcomings, but the installation was done in 45 minutes or so. A company with say 100 employees and perhaps 3-4 servers can't just have "the IT guy" any more, not unless he is a pretty good and dedicated IT guy. Microsoft product deployments almost require consultants or at least a decent support channel. Things you previously associated more with larger and more back office deployments like an Oracle database, SAP or IFS.
They are adding all this great functionally, but are they abandoning the ease of use and simplicity of their server products?
Myself I don't mind the added complexity as long as the new features can help me get the most out of my deployment, something that Oracle has done an excellent job of. While it is a very complicated product, it is also the most powerful database on the market. Can we have it both ways? Probably not.

So what's cool?

Vista in itself isn't that cool, but I think what people will do with it will be pretty cool. Both developers and end-users will benefit. I think we'll see the same thing we saw when the MacOS X revolution started. Innovation from all corners of the market.
SQL Server 2005 - pretty cool. But not great, it'll kick some more Sybase butt and perhaps excite a few more developers to give up their Access MDB databases.
.NET framework 3.0 - same as the two above. Managed code is here to stay, .NET, Java and python. Now when you can have managed code in unmanaged code (confusing yes) I think we'll see a more rapid adaptation.

Enough of this silliness, I need to sort myself out, cat some files, pipe some stuff.
Seriously, I'll stick to my motto, use whatever tools are best for the job and don't be afraid to try new products. Homogeneous environments and all that is nice, but when it all comes down to providing the service your clients require you have to look at all the options. Web 2.0 is all about providing a better service than the competition. If that's Microsoft or OpenSolaris, so be it.

Classic screenshot:

More cores, less space

2006-11-23T21:37:00.000Z

Dell just started shipping the "mark ii" 9-series boxes capable of taking the new quad-core Intel Xeon processor.
Unfortunately the older 9th generation are not compatible with the new quad core processors. Intel made some last minute design changes to the new chip that required Dell to revise their motherboard designs.
Look out for a "II" symbol on the front of your 9th gen boxes.
Quad-core will be available on the SC1430, 1900, 2900, 1950, 2950 and the 1955 blades.
Performance is said to be up to 60% better than a dual core system, which sort of puts 2-socket machines in "enterprise territory" for the first time. Sure, we got the 8-core Sun T1, but that is a quite niche market machine.
A 8-core Dell PowerEdge 2950 with dual quad core procesors, 8Gb RAM and a pair of 72Gb SAS disks costs just under £6000 ($10kUSD).

I'd love to do some JBoss benchmark on a mk2 1950.

Oracle merge-statement

2006-11-22T21:55:00.000Z

The merge statement has been in Oracle since version 9, but is still not very widely used.
When looking at data loading and merging code I think the most common bit of PL/SQL I've seen is the "merge" statement written in pl with slow and boring if-statements to see check if an entity exists in a table and if it exists the pl-code will do an update, otherwise it will do an insert.
Two words, super slow.

A much better solution is to use the merge-statement which does exactly that, it's basically
"if exists then update else insert". Merge data from one data entity to another.
The basic concept is sort of a join between the loading entity and the destination table.
You can load from a normal table or from select query.

Example:

SQL> select * from emp_load;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30

SQL> MERGE INTO emp e USING emp_load l
  ON (e.empno=l.empno)
  WHEN MATCHED THEN
    UPDATE SET
      e.ename = l.ename,
      e.job = l.job,
      e.mgr = l.mgr,
      e.hiredate = l.hiredate,
      e.sal = l.sal,
      e.comm = l.comm,
      e.deptno = l.deptno
  WHEN NOT MATCHED THEN
    INSERT VALUES (
      l.empno, l.ename, l.job,
      l.mgr, l.hiredate,
      l.sal, l.comm, l.deptno
    )
/

5 rows merged.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30
 7369 SMITH      CLERK      7902 17-DEC-80   800                  20
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600      300         30
 7521 WARD       SALESMAN   7698 22-FEB-81  1250      500         30
 7566 JONES      MANAGER    7839 02-APR-81  2975                  20
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850                  30
 7782 CLARK      MANAGER    7839 09-JUN-81  2450                  10
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7839 KING       PRESIDENT       17-NOV-81  5000                  10
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500        0         30
 7876 ADAMS      CLERK      7788 23-MAY-87  1100                  20
 7900 JAMES      CLERK      7698 03-DEC-81   950                  30
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7934 MILLER     CLERK      7782 23-JAN-82  1300                  10

16 rows selected.

If you want to load from a query you can use this syntax:

MERGE INTO emp e USING 
    (select * from emp_load 
      where hiredate > sysdate - 7) l
  ON (e.empno=l.empno)
...

Check out the merge docs .

The Week of Oracle Database Bugs

2006-11-22T12:09:00.000Z

In an effort to try to get Oracle to improve their response time and responsiveness to fixing critical security flaws in the Oracle database the Argeniss security firm has started the "The Week of Oracle Database Bugs".
In all honesty, Oracle are really bad at fixing critical bugs. Not that many databases are Internet facing, but the concept of social hacking and internal hacking is not unheard of.

I hope we see a positive response from Oracle.

Random bits

2006-11-18T16:48:00.000Z

I must admit that I haven't been very active on the blogging side lately. Been quite busy at work and a fair amount of my free time has been consumed by excessive Battestar Galactica viewing. :)
Work has been quite busy lately, not sure what I've actually done, lots of small things add up I guess. We recently signed a few new customers and need to configure staging environments and of course they have to use the most boring stuff around, Sybase and boring Webfear.
Why can't people just face the facts and use JBoss and Oracle?

On the plus side I've had to get a closer look at the FSC PrimePower Solaris boxes. The SPARC64 processor isn't that bad actually, it's pretty fast. I'd love to get some "end customer" pricing for their systems (noone pays list).

Productivity wise I'm writing a small system to find new physical devices on a LAN, it simply polls the MAC address table from a switch and stores the entries in a PostgreSQL database and when a new MAC is found it sends an e-mail alert to the administrator. That bit is currently working and I'm just writing a small PHP-interface to add comments to MACs so they can be easily identified. I'll post the code when it's done if anyone is interested.

Album recommendation:
And the artist of the "random time unit" is Blondie!
The album Autoamerican must without a doubt be one of the most creative and uprising albums of all times. Rap, jazz, reggae and disco on the same album is not something we see every day.

Oracle Alert log shell alias

2006-11-14T12:38:00.000Z

Inspired by Laurent Schneiders recent shell alias blog post I decided to post a nice little shell alias I use.
The alias opens the current alert log file in vi, it reads the background dump dest from sqlplus and constructs the full path to the file name. Works pretty well, it's quite convenient to have one single command to quickly open the alert log, it requires that your oraenv (ORACLE_HOME, PATH and ORACLE_SID) is set correctly.

The alias:
alias alert='vi + $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'

If you just want the last 20 lines of the alert log you can use this alias:
alias talert='tail -20 $(printf "set heading off pages 0 feedback off\n select value from v\$parameter where name='\''background_dump_dest'\'';\n"|sqlplus -S "/ as sysdba")/alert_${ORACLE_SID}.log'

Oracle controlfile backup script

2006-11-05T22:45:00.000Z

Wrote a small script to get a "usable" copy of the "CREATE CONTROLFILE" statement one can backup to trace. In a heavily used database it can be quite tricky to find the control file backup without actually "looking for it", in the sense of actually query the database for your thread number.
The script will dump the controlfile to trace and copy it to the local directory with the file name control_INSTANCENAME_TIMESTAMP.sql, should be fairly simple to integrate the script in to a generic backup process.

#!/bin/bash
ORACLE_HOME=/u01/oracle/10g
ORACLE_SID=peggy
PATH="$PATH:$ORACLE_HOME/bin"
export PATH ORACLE_HOME ORACLE_SID

sqlplus -S / as sysdba <<EOF  >/dev/null
set echo off feedback off verify off pages 0
column TRCLOC new_value TRCFILE
column TRCDATE new_value FILEDATE
column INSTNAME new_value INSTANCE
select to_char(sysdate, 'YYYYMMDD_HH24MISS') TRCDATE
  from dual;
select instance INSTNAME
  from v\$thread;
alter database backup controlfile to trace;
select
        c.value || '/' ||
        instance || '_ora_' ||
        ltrim(to_char(a.spid,'fm99999')) ||
        '.trc' TRCLOC
from
        v\$process a, v\$session b,
        v\$parameter c, v\$thread c
where
        a.addr = b.paddr
        and b.audsid = userenv('sessionid')
        and c.name = 'user_dump_dest'
/
host cp &TRCFILE control_&instance._&filedate..sql
exit
EOF

Example usage:

oracle@htpc:bin$ ls
backup_controlfile.sh
oracle@htpc:bin$ ./backup_controlfile.sh
oracle@htpc:bin$ ls -1
backup_controlfile.sh
control_peggy_20061105_225110.sql
oracle@htpc:bin$ grep -m1 "CREATE CONTROLFILE" control*
CREATE CONTROLFILE REUSE DATABASE "PEGGY" NORESETLOGS  ARCHIVELOG
oracle@htpc:bin$

Found the trace location query over at Asktom.

Quick update:
The blogger editor buggered up the script the first time around, had to use ampersand encoding to get the << and > to work properly.

rlwrap for rhel / centos / unbreakable

2006-10-30T13:49:00.000Z

Just buil a new RPM file for rlwrap 0.26 for Centos 4, Redhat 4 and Oracle Unbreakable Linux. I've added my (very) basic sqlplus auto complete file.

If you are not familiar with rlwrap but use Oracle on Linux/Unix you should definatly have a look. rlwrap provides basic "bash style" command line editing in sqlplus (and other command line tools), you get command history with working arrow keys and normal command line editing.

Download 32-bit version: rlwrap-hali-0.26-1.i386.rpm
Download 64-bit version: rlwrap-hali-0.26-1.x86_64.rpm

Running Oracle Unbreakable Linux under VMWare

2006-10-29T20:21:00.000Z

I finally got around to do a proper install of Unbreakable Linux today.
Pretty much as expect, it's pretty much identical to CentOS. It's got yum and all that.

Took the opertunity to try the new free version of VMWare GSX Server, or at least what used to be known as GSX. They just call it VMWare server now.
The VMware tgz install on my 64-bit Ubuntu 6.10 was quite smooth. You still have to enter a serial, easily obtained from filling out a simple form on the vmware website. The installation of vmware-authd failed though. Since Ubuntu's xinetd doesn't read /etc/inetd.conf I had to create an entry in /etc/xinet.d/.

hlinden@htpc:~$ cat /etc/xinetd.d/vmware
service vmware
{
        disable         = no
        socket_type     = stream
        protocol        = tcp
        user            = root
        wait            = no
        user            = root
        server          = /usr/sbin/vmware-authd
        port            = 902
}

Speed wise VMWare server is great, I only allocated 384Mb RAM to the VM and it still feels quite snappy and fast. I've only tried running two VM's at the same time so far. But my first impressions are simply, great stuff! The VM creation wizard thingy even had Solaris x86 in the list, haven't tried it yet but I sure will.

Took a few screenshots during the Unbreakable Linux installation.

Oracle Unbreakable Linux

2006-10-25T22:07:00.000+01:00

Ok, so Oracle is doing "a CentOS".
Oracle is taking the Redhat Enterprise Linux source code, removing all the trademarks, logos and references to Redhat and recompiling it with the Unbreakable Linux label. If there are any bugs found and redhat don't fix them fast enough (at all?) Oracle will take care of that and guarantee all bugs will be fixed and backported to all versions of Unbreakable Linux.
Sounds pretty good to me, especially when you look at the support cost model.

The basic support contract "network level" gives you access to patches via Oracles up2date equivalent. And the cost is a mear $99 per year per system.
If you want enterprise support you get that for a couple of hundred more.
If you want premium support you get that for about $1399 per year, you can't even get that support level from Redhat.
The full support policy can be found here.
And... even better... support contract are 50% off for the first few months (although I still haven't found where you can actually buy them).

Read more. Take a few minutes (about 60) to watch Larrys keynote, quite interesting.

Download ISO images, free for all.

Dell AMD Opteron servers

2006-10-23T23:56:00.000+01:00

We've known about this for quite some time, I think I heard the first rumors last November and we saw new AMD desktop machines from Dell about a month ago.
And now we have to new shiny Opteron Socket-F machines. They all run Linux of course.

First the anticipated Dell PowerEdge SC1435. A nice little box, as I've said before, the rack mounts in the SC series just get things done. I do think the price tag at around $3000 USD is a bit high. That'll probably drop quite a lot if you haggle with Dell and buy them in bulk. It is a perfect JBoss server, deployed in a nice farm of course, or it would make a very nice HPC node.
Now if just Dell would send me a few of these to test, they could use a bit of blogger hype. :)

The second new box looks even more interesting,
the Dell PowerEdge 6950.
A four socket machine with up to 8 cores and 64 Gigs of memory. On par with the HP DL585 and sort of a "half size" machine compared to the Sun X4600. I wonder which box packs the best bang for buck, but thats for another post. But a similar speced X4600 cost about twice as much, but on the other hand you get the extra scalability and future proof CPU cards instead of on-board socket-f.
The PE 6950 takes 5 internal SAS disks and has got 7 (free) PCIe slots.
Thats quite a lot of I/O for more NIC's and HBA's, do I dare to say this makes a very nice RAC node. Probably one of the top players in the area, team it up with a decent SAN and you are in business.
Starting price is not bad, not bad at all. An entry-level machine comes in at around $10000 USD.
.

See the product videos over at direct2dell.

Enjoy.

Oracle 10gR2 on Ubuntu 6.10 AMD64

2006-10-21T20:14:00.000+01:00

I just finished installing Oracle 10gR2 on my Intel Core2 Ubuntu 6.10 machine running in x86_64 (AMD64) mode. The installation is fairly straight forward with a few gotchas. In comparison to CentOS/Redhat, Ubuntu 6.10 has got much more native 64-bit libraries, pretty much everything is actually 64-bit. Under Redhat most libraries are 32-bit. And the Oracle Universal Installer (OUI) uses a 32-bit Java runtime so hence we have to install a few i386 compatability libraries to get it working.

First of all make sure your machines hostname resolves to your external IP and not the loopback IP (127.0.0.1).
Double check using this command

$ grep $(hostname) /etc/hosts
192.168.95.40           htpc

If you see 127.0.0.1 just edit the hosts file to have the external IP.

Now you need to install a few compat libraries and 32-bit libraries.

sudo apt-get install gcc libaio1 lesstif2 
  lesstif2-dev make libc6 libstdc++5 \
  lib32stdc++6 lib32z1 ia32-libs

This will probably cascade in to quite a few packages depending on how much you have installed, be patient and let it download everything it needs. The extra ia32 libraries is probably the biggest change to the old 32-bit installation.

Edit /etc/sysctl.conf to have the kernel parameters required by Oracle (I've added a few extras here as well to avoid unecessery swapping).
Add these lines:

# Oracle stuff
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
vm.swappiness=10

Create the oracle user and required groups (you don't really need the nobody group, it's only used by root.sh to be the group owning the oraenv and dbhome scripts).

sudo groupadd nobody
sudo groupadd oinstall
sudo groupadd dba
sudo useradd -s /bin/bash -g oinstall -G dba oracle
sudo passwd oracle

Increase system limits by adding these lines to /etc/security/limits.conf:

* soft nproc 2047
* hard nproc 16384
* soft nofile 1024
* hard nofile 65536

To avoid the non-redhat warnings during the prereqs run this command:

echo 3 > /etc/redhat-release

Add a few symlinks to avoid any script problems:

sudo ln -s /usr/bin/awk /bin/awk
sudo ln -s /bin/true /bin/rpm
sudo ln -s /usr/bin/basename /bin/basename
sudo ln -s /lib/libgcc_s.so.1 /lib/libgcc_s.so
sudo mkdir -p /u01/oracle/10g
sudo chown -R oracle:oinstall /u01/oracle/10g
sudo chmod -R 775 /u01/oracle

Time to run the installer, make sure you are allowed to use the X11 server (export DISPLAY, xhost, vnc whatever).
To get the 32-bit JRE to work you need to set the XLOCALELIBDIR before running the installer:

export XLOCALELIBDIR=/usr/lib32/X11/locale
cd /your/install/dir
./runInstaller

I just ran the defaults straight through (except the SID name and password). The installation without any warnings or errors. The linker worked fine and everything was configure properly, dbca created the standard database.

oracle@htpc:~$ uname -a
Linux htpc 2.6.17-10-generic #2 SMP Fri Oct 13 15:34:39 UTC 2006 x86_64 GNU/Linux
oracle@htpc:~$ rsqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Oct 21 20:13:45 2006

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> select platform_name from v$database;

PLATFORM_NAME
--------------------
Linux 64-bit for AMD

SQL>

This guide is based on the Oracle 10gR2 x64_64 installation guide and I've also used Dizwells excellent Ubuntu 6.06 32-bit guide for some ubuntu specific notes. Thanks.

Cool, now I can alt-tab between sqlplus and my latest Prison Break episodes on the TV . :)

Funny temporary table behavior in Postgres

2006-10-20T17:43:00.000+01:00

Was playing around with temporary tables in PostgreSQL and found this quite weird behavior.
When you have a table called say animal and create a new temporary table with the same name postgres is totally ok with the "duplicate" name. Since the temporary table ends up in a different schema I presume. Anyway, it was quite confusing before I figured out what was going on.
It's the same both in 8.1 and 8.2beta. Not sure if it to be expected, a oversight from my side or simply a "bug".

Example:

[postgres@dolphin ~]$ psql
Welcome to psql 8.2beta1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# create user test password 'test';
CREATE ROLE
postgres=# create database testdb owner test template template0;
CREATE DATABASE
postgres=# \c testdb test
You are now connected to database "testdb".
testdb=> create table animal (dog int);
CREATE TABLE
testdb=> \d
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | animal | table | test
(1 row)

testdb=> \d animal
    Table "public.animal"
 Column |  Type   | Modifiers
--------+---------+-----------
 dog    | integer |

testdb=> create temporary table animal (cat int);
CREATE TABLE
testdb=> \d
         List of relations
  Schema   |  Name  | Type  | Owner
-----------+--------+-------+-------
 pg_temp_1 | animal | table | test
(1 row)

testdb=> \d animal
   Table "pg_temp_1.animal"
 Column |  Type   | Modifiers
--------+---------+-----------
 cat    | integer |

testdb=> drop table animal;
DROP TABLE
testdb=> \d animal
    Table "public.animal"
 Column |  Type   | Modifiers
--------+---------+-----------
 dog    | integer |

testdb=>

SQL challange #1

2006-10-17T21:27:00.000+01:00

Ok, time for a little competition. Myself and Lukas Smith where helping a user with an at first very simple SQL problem. But after a few minutes we wouldn't find a "nice" way to solve it.

So here is the problem:
Dataset:

1, 2
2, 1
3, 4
3, 6
4, 3
6, 3

And the output from the query we want is:

1,2
3,4
3,6

All rows with a matching sister "a=b" relation should be "merged".

Lukas found one quite good way using exists in a self-join.

Any RDBMS goes, ANSI SQL is an advantage but not necessary. Analytics perhaps?
Nicest/fastest/cleanest query wins. Post any questions as comments.
E-mail me or post your solution as a comment to this blog.

See Lukas' invite to the challenge. :)

Thanks and good luck!

Running Ubuntu 6.10 on Abit AB9

2006-10-14T16:08:00.000+01:00

Playing around with my new media center PC at the moment.
Ubuntu 6.10 Edgy (daily build from Oct 13th) installed without too many problems yesterday, most of the hardware on my Abit AB9 pro motherboard was detected out of the box.
However the ethernet controller, a Realtek 8111B, card was not detected.
From lspci I got Realtek Semiconductor Co., Ltd. Unknown device 8168 (rev 01) and after a quick look in google I found this article on how to get the card working. Worked great, just download, change the three MODULE_PARM lines in the src to MODULE_PARM_DESC and it compiles ok.

A few notes on installing Linux on the AB9 motherboard though, a few thought from my experience. First of all, use a very recent version of Linux. Ubuntu 6.10 worked for me and I'd expect Fedora Core 6 would work.
Use the jmicron controller, not the ICH8R controller for the disk and optical drive.

Another thing, which I think is just related to me not having used debian/ubuntu enough. After I partitioned my disk and create ext3 file systems using labels I was unable to mount using LABEL=foo, turns out I had to restart the udev service before they showed up. In CentOS, they just appear under the dev-mapper.

I'm quite impressed on how good it looks on my LCD television. Even xvid files in HDTV resolution look great.

Blog design updates

2006-10-06T08:34:00.000+01:00

Joined the blogger beta program yesterday which allowed me to do a few cool updates to the template of the blog.
Note the "Latest Oracle downloads" RSS-feed and the labels amongst other things.

Media Center 10g

2006-10-05T22:03:00.000+01:00

I'm currently shopping around for a combined media center PC / Oracle test box, must be the first time someone tried to combine those two in one box. And the first time someone plan to hook up a Oracle server to a television set. But they, there is a first for everything.
Currently looking at the new Intel Core2 processors. The 2.13GHz E6400 seems to be the best value for money. I wouldn't mind the E6600 with 4Mb cache, but the extra £67 can probably be spent on something that adds more value to the system, like an extra SATA disk to distribute the IO load. Not sure if AMD AM2 processors are a valid option anymore? The new Intel 965/975 chipsets are probably not supported in Solaristhough, where as a Nforce chipset probably works gret.
Found a quite decent looking case for the system as well, the Silverstone LC17.
.
I'll post a photo of sqlplus on a 32" LCD television once I buy the system. :-)

Dell 9th generation servers

2006-10-03T21:02:00.000+01:00

Quick overview of Dells 9th generations rack-mount server line up

It's been a couple of months now since Dell introduced the first machines in the new "9th generation" server line-up, the PowerEdge 1950, 2950, 2900 and the Blade 1955 all looked like quite nice machines, they can be fitted with the new DRAC/5 card and onboard PERC.
And they have recently been accompanied by slightly simpler (and more cost effective) SC1435 and the baby 860.

All machines use the new generation Intel processors (64-bit), FB-DIMM memory and SAS and/or SATA disk drives.
I'm not going to go on about products specs and other sales crap, you can find all that on the Dell website. Just my view on the machines and what they are suitable for. Everything from using Linux as the OS on the box. Solaris 10 is currently not confirmed as working, using 06/06 you still need a driver disk for the PERC card and ethernet is unsupported so you need a addon Intel PCI card to even make use of the server.

Dell PowerEdge 1950
Nice 1U pizza box machine. I would use this as an application or web server, if you equip it with the 4 2.5" disk backplane you could possibly have a very small database on it. Disk I/O is very likely to be a bottle neck though. Hooking it up to a SAN is another option. Good layout of the internal components and the back connectors are easy to access. The PCIe slots are a bit limited in lenght, but then again there aren't many long cards available any more (anyone remember the old full lenght PCI raid cards?). Quite redundant with dual "most things".
To summarize, a perfect box for a Apache HTTPd or Java app server.
Starting price in a usable spec: $3437

Dell PowerEdge 2950
Pretty much the same machine as the 1950 but with more I/O. Six 3.5" drives or eight 2.5" drives. Decent small office file server, takes 1.8Tb internally (3Tb if you go SATA), room for extra Ethernet cards if you want to run iSCSI. Would also be a good "all in all" intranet server, put Postgres, PHP, Postifx and few other things on it. Or install a Zimbra and use it as a e-mail server.
Slap 8 2.5" drives in it and it is a nice decent DB server. If you team up a group of these and hook them up to a SAN they make great RAC-nodes. As the 1950 it is fairly redundant and can be trusted to work by itself for non-critical things.
Starting price in a usable spec: $3757

Dell PowerEdge 2900
Not really a rack-mount box but it does come with a rackmount kit. Only included here as it is the only "one box solution" for larger installations such as a Oracle EE, mid-sized file server or even a large e-mail server. Takes up to 10 internal drives (8 + 2 cage), thats 3Tb with current SAS drives. Lots of PCIe I/O, six slots for HBAs and NICs. If you like virtualization it would make a great VMWare box, it takes an impressive 48Gb RAM (you don't want to see the price tag for this though).
5U in a rack though, I hope you're not paying per U if you colocate it.
Starting price in a usable spec: $4370

Dell PowerEdge 860
This one I don't really understand. First of all, who came up with the name 860, it is clearly a 950. Second of all I don't really get where this box is meant to be used, the SC1435 is a better choice in 90% of all cases. The only advantage over the SC1435 is the fact that it has the normal kick ass DRAC/5 card as an option. It takes Celeron, Pentium D and Xeon 3000 series processors, I really *really* recommend against using a Celeron proc in anything that goes in a rack.
Possible uses (if I have to) would be a DNS server, mail relay, basic web server or management server.
Starting price in a usable spec: $1391

Dell PowerEdge SC1435
This little guy is not officially out yet, should be available in the comming weeks. The tower equivalent SC1430 is currently available though. I must admit that this is probably one of my favorites in the line-up, not because it's a great flexible, highly redundant machine. But simply because it's cheap and gets the job done! No hot swap anything. This is probably the perfect app or web server for larger load balanced solutions. This is however an AMD Opteron Socket-F server, not a Intel as the other. Quite confusing to be honest. I would have hoped that Dell launched a completely new line for the opteron gear.
I would not consider this box unless I was installing at least 4 (or possibly even 6) nodes for a single deployment. If they fail, you'll have a longer down time period than most other boxes since you probably have to get it out of the rack to service it. But it's cheap and just get things done. I can imagine many high performance computing farms rubbing their hands when they see this box, it's very suitable for a Linux beowulf cluster.
You can read my review of the older SC1425.
Starting price in a usable spec: $2900 (estimated)

Dell PowerEdge 1955
The Blade server in the new family. Data processing, web servers, app servers and possibly other uses if you add the FC daughter card. Quite decent machines and as a 1855 admin I'm very pleased so far.
One thing to remember though, the 1955/1855 chassis backplane is not redunant, deploy in groups of two chassis and at least 8 servers to make them cost effective. Price excluding chassis, which usually comes in at about $3000 with redundant PSU's, DRAC, IP-KVM and a pair of switch modules.
Starting price in usable spec: $2516

Regarding the price, the starting list price on Dells website is unusable. One disk drive included, way to little RAM, no DRAC card etc. All machines have been speced out to a suitable minimal config for actuall deployment use with redundant PSU's, a PERC card, at least 2Gb RAM etc and Bronze NBD support. The size and usage of the box has also been considered, as an example the 2900 was fitted with six drives since it's main purpose is disk and I/O expandability.

Update:
For Solaris 10 support see this post.

PostgreSQL 8.2 hits beta

2006-10-01T20:56:00.000+01:00

PostgreSQL 8.2 hit beta other day.
Just installed it in a Solaris zone on my laptop (you need a fairly recent Solaris Express release to use the a dtrace [pdf] functionallity).
A few new cool features.

dtrace support in Solaris
Warm standby server support
CREATE INDEX without locking the table
Improved syntax of the config file, support for SI units (kilobyte, megabyte, hour etc)
Basic support for SQL 2003 analytical functions (I couldn't find any docs about this).
Faster vacuuming
Improved WAL and log management

Quite a lot of exciting stuff. Analytics could be fun and the speedups in WAL and logging could make a improvement for write intense operations, an area where postgres have been quite slowish in the past.

Full list and downloads here. Cool.

Rotating tables (and sybase)

2006-09-27T21:33:00.000+01:00

Got a question on IRC today about the equivalent of MySQLs MAX_ROW setting on a table. The problem at hand was more specific. The table should always contain N number of rows, if a new row was inserted, the oldest should be deleted. I wrote this quick example to provide a possible solution.

CREATE TABLE top10data (
        id      NUMBER(2) NOT NULL,
        name    VARCHAR2(20) NOT NULL
);

CREATE VIEW top10 AS
        SELECT id,name FROM top10data;

INSERT INTO top10data VALUES(1,'one');
INSERT INTO top10data VALUES(2,'two');
INSERT INTO top10data VALUES(3,'three');
INSERT INTO top10data VALUES(4,'four');
INSERT INTO top10data VALUES(5,'five');
INSERT INTO top10data VALUES(6,'six');
INSERT INTO top10data VALUES(7,'seven');
INSERT INTO top10data VALUES(8,'eight');
INSERT INTO top10data VALUES(9,'nine');
INSERT INTO top10data VALUES(10,'ten');
COMMIT;

-- trigger on the view, catch the insert and 
-- do it on the actuall table
CREATE OR REPLACE TRIGGER top10_trigger
        INSTEAD OF INSERT ON top10 FOR EACH ROW
BEGIN
        UPDATE top10data SET id=id+1;
        DELETE FROM top10data WHERE id>10;
        INSERT INTO top10data VALUES(1,:new.name);
END top10_trigger;
/

-- insert example, note that we insert on the view
SQL> select * from top10 order by id;

        ID NAME
---------- --------------------
         1 one
         2 two
         3 three
         4 four
         5 five
         6 six
         7 seven
         8 eight
         9 nine
        10 ten

10 rows selected.

SQL> insert into top10(name) values('New one');

1 row created.

SQL> select * from top10 order by id;

        ID NAME
---------- --------------------
         1 New one
         2 one
         3 two
         4 three
         5 four
         6 five
         7 six
         8 seven
         9 eight
        10 nine

10 rows selected.

This solution may have some concurrency problems if it is heavly populated. To get around that problem you could use a sequence and increment the ID from the trigger and delete based on rank() of the numbers.

On another issue
I've been working a bit with Sybase lately.
To say the least, I do not like it.

The way it handles users, permissions and the "database owner" thing. I don't like it.
Data space and log space, whats this about. Isn't it just better to define logs and a number of "data storage thingies" (why don't we call them tablespaces) for anyone to use. Why pre-assign space and devices to a database (schema'ish) . I don't like it.
The structure of transaction logging and checkpointing. It just feels ancient. I don't like it.
If you do a database dump on Solaris/SPARC and try to load it on a Linux/x86 machine you need to do a data conversion process thingie. Whats that about, sure big endian vs. small endian when using raw devices. But a dump should be universal. Sybase should forget about always working with plain raw devices (disk, tape etc) and allow people to actually make use of the file system provided. Besides if you want "endian less" raw devices just use a cds volume under vxvm. Transportable tablespaces anyone? I don't like it.
The basic footprint is tiny compared to Oracle. The installation takes about 10 minutes and the sybase server uses about 40Mb of memory when you first start it up. So for a basic testing / dev database it is quite nice. I do like it.

And I've been messing around a bit with DB 2 as well. I'm not sure I like it yet but it is miles better than Sybase. I'll have to think about that one for a while (and play with it a lot more).

Update of updates

2006-09-20T21:07:00.000+01:00

Another quickie update, nothing fun to report anyway. Been busy fighting WebSphere lately, trying to get decent performance on a Sun T2000 server. Out of the box performance was horrible. Websphere 5.1 is still far from satisfactory, I'm putting my hope on Websphere 6.0 now.

Updated Sun boxes:
Sun finally decided to update the classic vXX0 range. Just a simple face lift to be honest, they still run the same range processors. The v440 has been replaced by the new v445, the v240 with the v245 and the 1U v210 is replaced by the v215.
The main two updates are the use of SAS drives and the addition of PCIe. Same basic features as the Galaxy and Niagra servers. They updated the chassi to Andys design.
My favorite new feature is the fact that you can have twice as many drives in the v445. 8 SAS drives instead of the old 4 SCSI. Might even be usable as a Oracle server without external storage. Oh, one last thing. No Solaris 8 support, bad bad. Who uses Solaris 9 anyway?!

Oracle patch release:
Patch release 9.2.0.8 is out, not much new. Bug fixes and a patch for the dreaded select-view-permission-hack security hole. I have tried applying it to both 9.2.0.4 and 9.2.0.6 machines without any hickups. 4547809 in Metalink (now if Oracle would just make these patches available for anyone to download *sigh*).

Btw, I've switched to KDE at work as well.

KDE - Not that krap after all

2006-09-12T23:51:00.000+01:00

Ok, I've been quite "anti KDE" the last few years, with good reason imo. The last time I tried KDE must have been around 2001 or 20002 and back then it wasn't that good.
Now the other week my beloved OpenSolaris powered laptops hard drive failed on me. After I spend about a week moaning about it I finally got around to buying a new drive for it. Replace the old 30gb 4200rpm with a snappy 80gb 5400rpm drive (yes, the speed difference is really significant). Since I didn't have a recent Nevada build ready and was to lazy to download one I decided to install Kubuntu instead, got a free CD at an event the other day. Simple install as always with Ubuntu. Even the widescreen res on my laptop worked out of the box. Easy setup, did the usuall apt-get install oracle-xe, install postgres.
Then I realized I really lliked using KDE, things where so much easier than GNOME. KDE has probably got less features, but it just works. The integration between the applications is great, the little IM client Kopete is much nicer than gaim. KDE seems to use less memory than GNOME as well. Oh well, I saved disk space for Solaris and will probably install that as soon as I get around to downloading a recent version. Nevada B48 should be out any day now.

Fancy group by rollup example

2006-09-08T12:52:00.000+01:00

Played around with group by rollup today and thought I'd post a little example.

Let's look at the basic usage of the rollup clause

select
  deptno,
  ename,
  sum(sal) sal from emp
  group by rollup (deptno,ename)
/

-- Here's the output from sqlplus

   DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        10 CLARK            2450
        10 MILLER           1300
        10                  8750
        20 FORD             3000
        20 ADAMS            1100
        20 JONES            2975
        20 SCOTT            3000
        20 SMITH             800
        20                 10875
        30 WARD             1250
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30                  9400
                           29025

18 rows selected.

Ok, thats all well and cool. Oracle sums up the previous rows when the group by condition change, which in our case is for each individual and for the department. When a rollup happens all "non rolled up" columns are returned as nulls as shown in the above example. Notice the last row which has sum of all employees and hence nulls are returned for the two rollup columns.
You probably want to label the rows instead of the nulls with something more usefull, like a small "Sum" descriptor or something. As usuall, decode() is our friend.
Example with pretty labels:

with empt as
(
  select
  deptno,
  ename,
  sum(sal) sal from emp
  group by rollup (deptno,ename)
)
select deptno,
  decode(ename,
    null,
    decode(rownum,count(*) over (),'TOTAL SUM','DEPT SUM'),
    ename) ename,
  sal
from empt
/

-- And the output from sqlplus

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 CLARK            2450
        10 KING             5000
        10 MILLER           1300
        10 DEPT SUM         8750
        20 ADAMS            1100
        20 FORD             3000
        20 JONES            2975
        20 SCOTT            3000
        20 SMITH             800
        20 DEPT SUM        10875
        30 ALLEN            1600
        30 BLAKE            2850
        30 JAMES             950
        30 MARTIN           1250
        30 TURNER           1500
        30 WARD             1250
        30 DEPT SUM         9400
           TOTAL SUM       29025

18 rows selected.

Cool.

Sending SMS from Oracle (abuse warning)

2006-09-06T17:21:00.000+01:00

Ok, this post is mostly to annoy people who think stuff like this should be done from the application layer. I do agree with them but these things can done from the db layer as well.
Anyway, here is a little code sniplet to send a SMS text message from the database just using a insert statement. The SMS provider we use at work for basic Nagios alerts and stuff like that is Sign.Up To, cheap and quite reliable (they did have some downtime yesterday though). To send the actually message we use a trigger which in turn calls the quite handy (and abusive) dbms package utl_http, utl_http works in Oracle 8 and up (including XE).
If you haven't got utl_http installed you can do so by running the two scripts ?/rdbms/admin/utlhttp.sql and ?/rdbms/admin/prvthttp.plb as sysdba on the server. The response you get back is either a message id or the keyword "fail" if the message fails for some reason, the trigger simply catch that message and store it a table.
This code is just proof of concept and should not be used for anything "real", no error handeling or input checking. It just sends off the data to the message aggregator. And it can't handle any fancy characters in the message nor spaces. You can easily implement basic URL encoding using this function written by Tome Kyte.

create table smslog (
        sms_id number(8),
        smsno varchar2(18) not null,
        message varchar2(160) not null,
        status varchar2(150),
        sendtime date
);

alter table smslog add constraint
  smslog_pk primary key (sms_id);

create sequence smslog_pk_seq;

CREATE OR REPLACE TRIGGER smslog_insert_trigger
  BEFORE INSERT ON smslog FOR EACH ROW
DECLARE
  req   UTL_HTTP.REQ;
  resp  UTL_HTTP.RESP;
  val   VARCHAR2(2000);
  url   VARCHAR2(300);
BEGIN
  url := 'http://sms.sign-up.to/smpp_send.php?username=username&from=oracle&
account=account_no&password=yourpassword&
message='||:new.message||'&to='||:new.smsno;
  req := UTL_HTTP.BEGIN_REQUEST(url);
  UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0 Oracle');
  resp := UTL_HTTP.GET_RESPONSE(req);
  UTL_HTTP.READ_LINE(resp, val, TRUE);
  UTL_HTTP.END_RESPONSE(resp);
  :new.status := val;
  :new.sendtime := sysdate;
  SELECT smslog_pk_seq.nextval INTO :new.sms_id FROM dual;
END smslog_insert_trigger;
/

-- And a sample sent message.SQL> insert into smslog(message,smsno) 
  2  values('Eat_more_fruit','4479697xxxxx');

1 row created.

SQL> select * from smslog;

 SMS_ID SMSNO           MESSAGE              STATUS        SENDTIME
------- --------------- -------------------- ------------- ---------
      1 4479697xxxxx    Eat_more_fruit       3211821       06-SEP-06

NFS locking issues

2006-09-04T22:49:00.000+01:00

Been quite busy at work, decommissioning some old servers.
We are trying to get rid of everything pre RHEL/CentOS 3.x. After buying a new shiny file server for our home directories (a Dell 2850 with lots of cheap disk space) I ran in to some very weird problems. Everything worked fine at first, I myself (as a XFCE user) didn’t have any problems at all, but our GNOME users ran it some serious problems. When logging in to GNOME the desktop did not start properly, gnome-panel looked like a grey bar without any tools and the desktop menu didn’t appear on right click. Simply, nothing in GNOME worked. Not in Centos, not in Fedora, not even JDS in Solaris worked.
After installing the GNOME debuginfo packages for Linux it appeared to be some weird NFS locking problem (haven’t we all hear that one before) in gconfd. NLM auth lock calls got denied. Hard to troubleshoot why and how at the time, we simply remounted the NFS shared with the nolock mount option in Linux and the sort of undocumented mount option llock in Solaris.
NFSv3 is just a mostly irritating patch work of addons, statd, lockd etc etc.
Hate NFSv3, why didn’t I include a NFSv4 migration while we where at it.
The nolock "hack" will have to do for now, I’ll will try to troubleshoot the problem later on.

fstab line in Linux:

fs1:/export/home  /export/home nfs rsize=16384,wsize=16384,intr,nolock

I'll try to post some rather interesting Oracle 10gR2 Sun Fire T200 server benchmarks later on this week. I've found a rather good benchmarking tool called Swingbench. Written in Java and easy to use. It comes with a data loading utility to populate the database with nice bogus data and then has a few tools to simulate different workloads.
Been reading a lot about file system tuning recently as well, ext3 tuning parameters and Oracles filesystemio_options parameter, need to dig up a good test box for that so I can evaluate the options. My current two disk machine doesn't cut it for I/O load testing.

Finally a quick movie recommendation.
Bought a new LCD TV the other day so I've been overindulging in movies recently.

And a big thanks to Lukas Smith for sending me a few DVDs from my wish list.

Oracle dates diffs using analytics

2006-08-25T11:10:00.000+01:00

Simple little analytical query to find out the difference between two dates on adjecent rows in oracle using the lead analytical function lead() fetches the value from the next row in the resultset, you can use lag() to find the previous row.

SQL> select * from leadtest;

        ID TAG        STAMP
---------- ---------- ---------
         1 dog        05-JAN-06
         2 dog        05-MAY-06
         3 dog        05-SEP-06
         4 cat        05-APR-06
         5 cat        28-APR-06
         6 cat        28-SEP-06
         7 cat        28-OCT-06

7 rows selected.

SQL> with t as
  2  (
  3    select
  4      tag,
  5      stamp,
  6      lead(stamp,1,stamp) over 
  7        (partition by tag order by stamp) stamp2
  8    from
  9      leadtest
  10  )
  11    select tag, stamp, stamp2, stamp2 - stamp diff from t
  12 /

TAG        STAMP     STAMP2          DIFF
---------- --------- --------- ----------
cat        05-APR-06 28-APR-06         23
cat        28-APR-06 28-SEP-06        153
cat        28-SEP-06 28-OCT-06         30
cat        28-OCT-06 28-OCT-06          0
dog        05-JAN-06 05-MAY-06        120
dog        05-MAY-06 05-SEP-06        123
dog        05-SEP-06 05-SEP-06          0

7 rows selected.

-- Lets try lag() as well.
SQL> with t as
  2  (
  3    select
  4      tag,
  5      stamp,
  6      lag(stamp,1,stamp) over 
  7        (partition by tag order by stamp) stamp2
  8    from
  9      leadtest
  10  )
  11    select tag, stamp, stamp2, stamp - stamp2 diff from t
  12 /

TAG        STAMP     STAMP2          DIFF
---------- --------- --------- ----------
cat        05-APR-06 05-APR-06          0
cat        28-APR-06 05-APR-06         23
cat        28-SEP-06 28-APR-06        153
cat        28-OCT-06 28-SEP-06         30
dog        05-JAN-06 05-JAN-06          0
dog        05-MAY-06 05-JAN-06        120
dog        05-SEP-06 05-MAY-06        123

7 rows selected.

And there was Oracle

2006-08-22T23:58:00.000+01:00

Found a cool link on the oracle-l mailing list to a guy running Oracle 4.1 under DOS. :)
Geeky indeed.
Can't say I remember UFI (previous version of sqlplus), since I was a toddler when it was released. At least they had scott/tiger.

Check the video http://technology.amis.nl/blog/?p=1127

Using Oracle Workspaces

2006-08-13T19:22:00.000+01:00

Say the thing we can't do with versioning in Oracle. One really useful feature I often use for day to day stuff is workspaces. A workspace is an independent space where you can play with a table (or a whole bunch of tables) before you actually decide you want your new data to be the live data. Inside a workspace you can commit and rollback data without affecting any other workspace. Even though one should always test and prepare any work on a staging system it can be reassuring not to actually modify a live table without knowing how it will look. You can have almost unlimited numbers of workspaces and even have child workspaces to a parent. You can even do DDL operations. The workspace for the live data is simply called LIVE (in caps).
Let's do a little test.

-- Lets begin with enabling versioning 
-- and creating the new workspace
HALI@spinner1> exec dbms_wm.enableversioning('t1');

PL/SQL procedure successfully completed.

HALI@spinner1> exec dbms_wm.createworkspace('test');

PL/SQL procedure successfully completed.

-- Don't forget to actually switch to your new ws
HALI@spinner1> exec dbms_wm.gotoworkspace('test');

PL/SQL procedure successfully completed.

HALI@spinner1> select * from t1;

        ID NAME
---------- ----------
         1 test
         2 test2

-- Ok, lets update the table and insert a new row
OPS$HLINDEN@spinner1> insert into t1(id,name) values(3,'test 3');

1 row created.

OPS$HLINDEN@spinner1> update t1 set name='test 2' where id=2;

1 row updated.

-- Note that we actually commit the data.
OPS$HLINDEN@spinner1> commit;

Commit complete.

HALI@spinner1> select * from t1;

        ID NAME
---------- ----------
         1 test
         2 test 2
         3 test 3

-- Now we switch bake to the LIVE data.
HALI@spinner1> exec dbms_wm.gotoworkspace('LIVE');

PL/SQL procedure successfully completed.

-- And the live data is unchanged at this point.
HALI@spinner1> select * from t1;

        ID NAME
---------- ----------
         1 test
         2 test2

-- But we are happy with our updated data and want
-- our changed to be the added to the live workspace.
HALI@spinner1> exec dbms_wm.mergetable('test','t1');

PL/SQL procedure successfully completed.

HALI@spinner1> select * from t1;

        ID NAME
---------- ----------
         1 test
         2 test 2
         3 test 3

-- Don't forget to clean up after the operation.
HALI@spinner1> exec dbms_wm.removeworkspace('test');

PL/SQL procedure successfully completed.

HALI@spinner1> exec dbms_wm.disableversioning('t1');

PL/SQL procedure successfully completed.

Read the Oracle documentation for workspaces.

New JBoss platform

2006-08-06T11:23:00.000+01:00

I'm currently in the process of evaluating a new JBoss platform for our company. A few months this would have been a quite simple task. But now with the new Intel Xeon 5100 "Woodcrest" processors on the market, it's a new ballgame.
The three different platforms I'm researching at the moment are:

Sun X4100 running Solaris 10
Sun T1000 running Solaris 10
Dell PowerEdge 1950 running CentOS 4

My previous choice would have been the X4100 without question, the price/performance is good and it's a sort of common platform that can be used for other tasks as well if our needs would change.
Sun recently introduced a dual SAS disk kit for the T1000, which actually makes it usable as an app server, price wise it is slightly pricey (the 2x72Gb SAS kit is about 650GBP (1000USD)), and it's single PSU. The T2000 would have been a better choice but the cost is simly to high. The biggest benefit it gives is it's top speed with huge amounts of threads and the fact it runs Solaris. We're quite likely to run several Jboss instances on these boxes so running these in Solaris containers is quite a good benefit.
I presume most people have seen the amazing Intel benchmarks of the 5100-series CPU's. It just flies. Trust me here, I know a lot of you probably hate dell kit, but the Dell "9-series" is pretty darn good. Pretty decent remote management with the new DRAC/5 card (it is in fact better than the LOM in the galaxy line (X4100 etc)).

Quick summary (for midspec machines with 8Gb RAM, procs as listed and max number of 72Gb SAS disks and an extra dual port NIC in the 1950):

	Sun X4100	Sun T1000	Dell 1950
Processor	Dual AMD 280 4-cores	SPARC T1 8-cores	Dual Intel Xeon 5130 4-cores
Max memory	16Gb	16Gb	32Gb
Disks	4x SAS	2x SAS	4x SAS or 2x SATA
NIC	4x GIG	4x GIG	2x GIG
PCI	2 PCIe	1 PCI	2 PCIe or PCI-x
PSU	Dual	Single	Dual
Unit cost	£4500	£6000	£3850
Support pa	£420	£850	£110
3yr cost	£5760	£8550	£4130

I'll post my decision when I make it. I have to say I'm leaning towards Dell at the moment.

Oracle group_concat() updated (again)

2006-08-04T17:44:00.000+01:00

I've ranted about this twice before. The mysql group_concat() equivalent in Oracle has in the past been quite problematic query.
We found a quite good solution a while back and after checking Tom Kytes blog I found a even better solution today.
This is mighty impressive SQL code, very simple functions but used in a very clever way.

Have a peek.

with data
 as
 (
  select job,
    ename,
    row_number() over (partition by job order by ename) rn,
    count(*) over (partition by job) cnt
  from emp
 )
 select job, ltrim(sys_connect_by_path(ename,','),',') scbp
  from data
  where rn = cnt
  start with rn = 1
  connect by prior job = job and prior rn = rn-1
  order by job
/

JOB       SCBP
--------- ----------------------------------------
ANALYST   FORD,SCOTT
CLERK     ADAMS,JAMES,MILLER,SMITH
MANAGER   BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN  ALLEN,MARTIN,TURNER,WARD

How cool is that, nothing extra to the query! Just beautiful use of the analytical functions.

Full TK post over here.

Fabric 7 servers

2006-07-20T10:26:00.000+01:00

Found this very cool new server vendor called Fabric 7.
The thing that sets them aside from other vendors is that they actually have something new to offer, they recently introduced their first products, the Q80 and Q160, to the market. AMD Opteron servers, we've all seem those before and know that's about.

Here's the cool part, they support hardware level partitioning. Similar functionality you get in a Sun Fire 2900 and up system. You can cut up the box in four domain, run VMWare in on, Solaris 10 two and Windows in the forth. Sure you could probaly do all that in VMWare, but in some cases you need dedicated hardware for a project. A basic system with 4 single-core processors and 8Gb RAM starts a $29k. Not a great price when comparing to the Sun x4600 which starts at $26k for a 4 dual-core system with 16Gb RAM. They do however come in at about the same price for a 8 dual-core system with 32Gb RAM. These are list prices, haggeling is up to you.

This can be a very interesting company in the future. I wouldn't be suprised if one of the big fours try to acquire Fabric 7.

It's HOT!

2006-07-18T20:19:00.000+01:00

Been quite slack with the blogging lately.
First of all, it's freakin' hot in London at the moment. 35C tomorrow (95F).
Second of all I'm in the middle of moving to a new flat in east London, about 3 minutes away from London bridge.
And lastly I've I'm still quite busy at work, migrating lots of services to new hardware. Batteling with Windows server for the first time in a year or so.

The only thing that keeps my mood up is the new cool and freshing "frappe" from Caffe Nero. I prefer the mint flavored one. Yummy!

Solaris 10 06/06 on Dell PE 1855 blades

2006-07-13T15:13:00.000+01:00

Just installed Solaris 10 06/06 on a Dell PowerEdge 1855 blade.
Everything worked out of the box. Drivers for the raidcard, ethernet etc. was all included.
I just mounted the ISO image in the virtual media interface in the remote management DRAC card and ran the manual installer as usual. Default BIOS settings, except that I disable hyperthreading to get better performance.

root@juliet:[etc]$ psrinfo -v
Status of virtual processor 0 as of: 07/13/2006 15:07:14
  on-line since 07/13/2006 14:18:56.
  The i386 processor operates at 3200 MHz,
        and has an i387 compatible floating point processor.
Status of virtual processor 1 as of: 07/13/2006 15:07:14
  on-line since 07/13/2006 14:19:02.
  The i386 processor operates at 3200 MHz,
        and has an i387 compatible floating point processor.
root@juliet:[etc]$ isainfo -v
64-bit amd64 applications
        mon sse3 sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
        mon sse3 sse2 sse fxsr mmx cmov sep cx8 tsc fpu
root@juliet:[etc]$ prtdiag
System Configuration: Dell Computer Corporation PowerEdge 1855
BIOS Configuration: Dell Computer Corporation A04 08/24/2005
BMC Configuration: IPMI 1.5 (KCS: Keyboard Controller Style)

==== Processor Sockets ====================================

Version                          Location Tag
-------------------------------- --------------------------
Intel Xeon                       PROC_1
Intel Xeon                       PROC_2

==== Memory Device Sockets ================================

Type    Status Set Device Locator      Bank Locator
------- ------ --- ------------------- --------------------
DDR2    in use 1   DIMM1_A
DDR2    in use 1   DIMM1_B
DDR2    empty  2   DIMM2_A
DDR2    empty  2   DIMM2_B
DDR2    empty  3   DIMM3_A
DDR2    empty  3   DIMM3_B

==== On-Board Devices =====================================
LSI Logic 53C1020 Ultra 320 SCSI
ATI RADEON 7000 PCI Video
Intel 82546GB Gigabit Ethernet
Intel 82546GB Gigabit Ethernet

==== Upgradeable Slots ====================================

ID  Status    Type             Description
--- --------- ---------------- ----------------------------
1   available PCI-X            DC_CONN

Look at the prtconf -pv
It was especially cool to have ZFS on a production ready box. The blade I installed Solaris on had a pair of mirrored 146Gb drives so I created a 40Gig system partition and a 100Gb partition for ZFS.
Now I just need to investigate how well WebLogic 8.1 plays in a zone.

As a really annoying side note I can mention that it took almost 2 days to get Windows 2000 server installed on another blade. Getting drivers and a retail copy of Windows 2000 with slipstreamed SP4 was quite difficult. We usually use MSDN media to install development server but the Dell Server Assitant (required to get Windows 2000 working at all) requires a retail OEM version of Windows 2000. Then again, I'm not a very Windowsy' person :)

New massive Sun servers

2006-07-11T20:12:00.000+01:00

Sun, or should I say Andy Bechtolsheim, announced some really awesome servers at the NC event today.
I mean, these new boxes will kick some HP butt.

Sun x4600 - an 8-socket (16-way) Opteron machine. 4 SAS drives, 8 PCI-e, up to 128Gb! memory. Sounds like a fun box.

Sun x4500 - ok, most companies are moving away from internal storage but now when iSCSI is making great progress it is time to rething the mid size storage sector. This little 4U box has nothing less than 48!! internal SATA drives. Up to 24Tb per machine, with 10 machines in a rack, thats 240Tb per rack. Yes please.

Sun Blades 8000 series - nice blade enclosure. Future proof, 2 pci-e slots per blade which is nice. And the network backplane has a smart config. Instead of having a interface card on the blade and connect via ethernet to the switch, the "switch" connects to the blade via PCI-e, so the actuall ethernet card is in the switch itself. Small customer base though, only massive datacenters can afford this type of kit.

I can see a very nice Oracle Data Warehouse setup here. One x4600 processing machine backed by 24Tb iSCSI storage mounted from a x4500 :)
And, we can build a massvie 48-way 3 node RAC deployment in just 12U's (plus proper storage).

HPC Top500 - AMD vs. Intel

2006-07-08T16:34:00.000+01:00

The latest top 500 HPC list was released about a month back. A lot of new systems on the list which is great news. Opterons are climbing fast and IBM is gaining a lot of market share with Blue Gene systems.

One thing struck me as quite interesting when I first glanced the list, Xeon processors seems to get higher "per cpu" benchmark figures. I know these numbers aren't the only thing to go after when it comes to comparing HPC processing power. The type of load etc . makes a difference. But still, it's a known fact that Opterons are faster in almost all tasks.
I did a quick calculation of the top 100 entries which had Xeons or Opterons, the average Rmax per cpu is almost 50% better for Xeons?!
Xeon gets 4.40 and Opteron gets 3.00.

This makes me even more courius about the next list where we'll hopefully have some Xeon 5100 systems.

Busy with the blades

2006-07-05T21:19:00.000+01:00

Have been quite slow on the blogging side lately. Actually quite busy at work installing a bunch of new servers. We ordered some Dell kit last week, PowerEdge 2850's and more interestingly PowerEdge 1855 blades.
I must say I really love the blade concept, not so much by the annoying "data center cost saving" hype. I strongly doubt that 10 Xeon blades use much less power than 10 1U Xeon servers.
Power wise, the enclosure wants 4 16 Amp 3-pole plugs.. go figure :)

No, what I really like about blade servers is the ease of management and administration. The Dell Blade enclosures we got has 2 built in switch modules, DRAC/MC card and the optional IP-over-KVM module. All these cool modules are cheap so I can't imagine anyone ordering a blade enclosure without them.

Then we have the cable side of things. Each enclosure holds 10 servers, from the enclosure we have:

4 power cables

2 ethernet uplinks (one from each switch module)

1 ethernet management interface

Thats 7 in total, compare to 10 1U servers:

10x 2 power cables

10x 2 ethernet cables to switches

10x 3 kvm (mouse, keyboard and screen to a KVM switch)

10x 1 ethernet management interface

Thats 80!! in total. Or about 180 meters of cables. Dang! If we would have had fibre-channel connectivity we would have saved another 18 cables.

Ok, now to what really counts. Price!
For us it started making sense after about 4-5 servers, thas just silly. The question was more "why not" to buy blades. Each blade came in at about £1350 (dual 3.2Ghz, 4Gb, 2x146Gb) and the enclosure itself was about a silly £1000 (including the modules). Thats £15000 for 10 machines, a similar speced PE 1850 costs about £1600. Not that much of saving you say, a lousy £1000 for 10 machines.
But look at the extra costs around having 10 servers, take the 10 slots on a KVM switch, take the 18 extra ports in ethernet switches, take the freakin' cables. I'm assuming a cost of £1200 for a 16 port KVM switch, allocating 10 of those costs £750, 18 switch ports in two switches at £500 each is £416, 28 cat5 cables at perhaps 60 pounds for a pack.
I'm not counting the extra allocation of PDU's due to the fact the blade enclosure uses the previously mentioned 16A 3-pole sockets which of you probably have to have a few extra installed.
Now we have a cost saving of about £2260 per enclosure.
The biggest saving however will be in time, I'll blog about the ease of use another day. Funny enough the blades will be installed with a huge mix of opterating systems. Windows, both 2000 server and 2003 server, alongside Centos 3, Centos 4 and Solaris 10. Most of them running Weblogic, Websphere and Jboss.

And a small PS. Nooo, I could not have bought Opterons nor the new Conroe procs. These machines are going to be used mainly for application support mimicking customer environments. And as most people probably know, investment banks don't mix with the latest technology.

Turning your desktop in to a desktop

2006-07-02T16:34:00.000+01:00

Found a very cool video on YouTube.
Some canadian grad student has written a prototype desktop system called BumpTop™ which is designed to be an actuall desktop. With piles of documents and applications. You can pile up documents and sort them just as you would on a real life desktop.

Check out the video.

Project website.

Compute sum of in plain Oracle SQL

2006-06-28T15:58:00.000+01:00

Once again a post about Oracle analytics. Indeed the best thing since sliced bread.

In sqlplus we have the posibility to insert breaks and to trigger basic events on these breaks. However in plain SQL it is not as easy, not that it is very hard either. Using analytics and the cool classic "decode rownum" hack we can get sort of the same results. On the last returned row you will get the full sum of the previous rows, in a new column however. But at least it's all done at the database layer and in plain SQL.

An example as usual

SQL> break on report
SQL> compute sum of sal on report
SQL> select ename, sal from emp
  2  /

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
JAMES
           ----------
sum             29025

15 rows selected.

SQL> clear breaks
breaks cleared
SQL> select e.*, (decode(rownum,count(*)over(),sum(e.sal)over(),null)) sum
  2  from (select ename, sal from emp union 
  3  select null,null from dual order by ename) e
  4  /

ENAME             SAL        SUM
---------- ---------- ----------
ADAMS            1100
ALLEN            1600
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JAMES
JONES            2975
KING             5000
MARTIN           1250
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500
WARD             1250
                           29025

16 rows selected.

Update
You can also use the "group by rollup" features, I've writen a small article about it here.

Oracle group_concat() updated

2006-06-23T15:09:00.000+01:00

Update (again)! 2006-08-04
Some even better code was posted here!

Found a quick nifty peace of code over at asktom.
The code gives a very cheap and simple way to do group concatiation, same as group_concat() in MySQL. This without using PL/SQL. The code uses the new collect function introduced in Oracle 10g, so unfortunatly it is 10g only. 9i people will have to stay with other methods such as the PL/SQL function stragg().

CREATE OR REPLACE TYPE ntt_varchar2 AS TABLE
OF VARCHAR2(4000);

CREATE OR REPLACE FUNCTION to_string (
  nt_in        IN ntt_varchar2,
  delimiter_in IN VARCHAR2 DEFAULT ','
  ) RETURN VARCHAR2 IS

  v_idx PLS_INTEGER;
  v_str VARCHAR2(32767);
  v_dlm VARCHAR2(10);

BEGIN
  v_idx := nt_in.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    v_str := v_str || v_dlm || nt_in(v_idx);
    v_dlm := delimiter_in;
    v_idx := nt_in.NEXT(v_idx);
  END LOOP;
  RETURN v_str;
END to_string;
/

And a quick example:

SQL> select deptno, cast(collect(ename) as ntt_varchar2) as vals
  from emp group by deptno

    DEPTNO VALS
---------- -------------------------------------------------------------------
        10 NTT_VARCHAR2('CLARK', 'KING', 'MILLER')
        20 NTT_VARCHAR2('SMITH', 'JONES', 'SCOTT', 'ADAMS', 'FORD')
        30 NTT_VARCHAR2('ALLEN', 'WARD', 'MARTIN', 'BLAKE', 'TURNER', 'JAMES')
           NTT_VARCHAR2('JAMES')

SQL> select deptno, to_string(cast(collect(ename) as ntt_varchar2)) as vals 
  from emp group by deptno;

    DEPTNO VALS
---------- -------------------------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
           JAMES

Cool!
The origianl thread can be found here.

Thanks to Adrian Billington for coming up with the code.

Oracle analytical functions

2006-06-20T10:30:00.000+01:00

Recently I've found myself using the analytical functions in Oracle more and more. Not really to do in depth analysis, but just to do normal queries.
The analytical functions give you a bigger perspective to work with in a query, you can access more than the current row in a query. Compare it to a "row level aggregator". Things usually done by a group by expression can be done straight in the select clause. You can access the previous rows and following rows in the resultset to compute things.
Very handy indeed.

Quick example using the very basic functions of analytics.

select 
  distinct(deptno), 
  (count(deptno)over(partition by deptno)) /
  (count(deptno)over())*100 dshare 
from 
  emp
where 
  deptno is not null 
order 
  by deptno
/
    DEPTNO DSHARE
---------- ------
        10     21
        20     36
        30     43

This query does two things and divide the two things to get the size of the deptartment in percent. first it finds the size of the current department, then the total head count (excluding top dog). Notice how both functions look at a bigger window than the current row without aggregating the data.

Some things never change

2006-06-15T20:40:00.000+01:00

Looking back at the mid 1980's and the introduction of random strange graphical user interfaces I found two quite funny and very different video clips on YouTube.

How do you introduce a new operationg system?
1. The Microsoft way
2. The Apple way

How could things go so wrong?

Do Oracle still charge for standby databases?

2006-06-13T20:40:00.000+01:00

I'm looking in to some upgrade costs for a few systems and was told by our happy Oracle rep. that full Oracle EE licenses are required for all standby databases (i.e. physical standby data guard). Costly indeed.

Now I stumbled across this PDF on the Oracle website.

From the PDF:
"System: shall be defined as each distinct production database. Test, development, failover and standby databases are not required to be licensed as systems; however, you may run the program on these databases."

Not sure whom to believe.

Oracle Validated Configurations

2006-06-13T08:55:00.000+01:00

Oracle have started giving end-to-end recommendation for database software and hardware platforms. Recommending a server, storage system and operating system for new deployments. No need to ask around any more. Just go to this page

Oddly enough, Oracle chose Linux x86_64 for all their recommended configurations so far, perhaps the Solaris choices are to obvious to list. They claim they see a very high demand for Linux x86_64 systems at the moment. Not suprised by that, large SGA allocation on 32-bit platforms has always been a pain in the neck.
We've had a Sun v20z running 64-bit CentOS for six months or so and haven't seen any major problems. Did see one core dump issue in data guard when configuring the standby database in dgmgrl.

I'm sure we'll see more (Unix) recommended configurations in time.
And still no sight of Ubuntu.

hdesc beta1 release

2006-06-09T11:54:00.000+01:00

I'm pleased to introduce "halis desc" (hdesc for short) for sqlplus :)

Hdesc is a quick and simple plsql program to display information about a table in sqlplus, it aims to be an extension of the normal desc command with the same level of output as the "\d" command in PostgreSQL. I wrote it the other day and haven't done any extensive testing. Since it's a beta release i just included the basic stored procedure, no public synonyms or stuff like that.

Planed features are constraint output format cleanup, list of triggers on table, more partitioning information and possibly table statistics.

Download the code here http://halisway.hifichoice.com/hdesc.sql

Load the stored proc and example

OPS$HLINDEN@orcl> @hdesc

Procedure created.

OPS$HLINDEN@orcl> set serverout on
OPS$HLINDEN@orcl> exec hdesc('t2')
HDesc information for: t2
Column name                    | Data type            | Nullable | Default
------------------------------ | -------------------- | -------- | ----------
T2_ID                          | NUMBER(5)            | NO       |
USER_ID                        | NUMBER               | YES      |
COST                           | NUMBER(4,4)          | YES      |
NUFF                           | VARCHAR2(30)         | YES      |
OBJECT_ID                      | NUMBER               | YES      |
------------------------------ | -------------------- | -------- | ----------
Tablespace: USERS               Monitoring: YES          Partitioned: NO
Last analyzed: 2006/06/08       Row movement: DISABLED   Compression: DISABLE
Constraints:
Primary key T2_PK on column(s): T2_ID
Foreign key: SYS_C0096241 on (USER_ID) references T1(ID)
Foreign key: T2_NUFF_FK on (NUFF,OBJECT_ID) references CT2(OBJECT_NAME,OBJECT_ID)
Unique: T2_COST_UNIQ on (COST)

PL/SQL procedure successfully completed.

Please send any feedback you may have.

Cost-Based Oracle Fundamentals

2006-06-06T14:11:00.000+01:00

Just got a new book about Oracle from Amazon. The book Cost-Based Oracle Fundamentals written by Jonathan Lewis describes the Oracle Cost-Based optimizer in a easy and understandable way. Jonathan Lewis is the director of the UK Oracle user group and is the author of several quite good books, this is however his best book so far.
My only disappointment so far (haven't fine read all sections yet) is that the book doesn't really cover how to "manually" influence the CBO in other ways than SQL HINTS, such as "faking" your own DBMS_STATS and costs.

Any how, the best book on Oracle I've seen in a long time.
Go get it, both for DBAs and developers.

SQL_CALC_FOUND_ROWS in Oracle

2006-06-05T11:22:00.000+01:00

People keep telling me about all these new fancy features MySQL have and how nice it would be if Oracle had the same. My usuall response is "Uhm, had that for 15 years or so". Oracle just call the feature something difrerent. My latest MySQL "fancy feature" question was about SQL_CALC_FOUND_ROWS.

Here's how you get the same functionality it in Oracle

select found_rows, empno, job from 
  (select count(*) over () found_rows, empno, job, rownum rn 
     from emp order by empno) 
where 
  rn between 1 and 5
/
FOUND_ROWS      EMPNO JOB
---------- ---------- ---------
        15       7369 CLERK
        15       7499 SALESMAN
        15       7521 SALESMAN
        15       7566 MANAGER
        15       7654 SALESMAN

You do get a bit more data transfered to the application layer, one value for each column. To avoid this and only get the count on the first row you can modify the inner query to use decode() like this

select decode(rownum,1,count(*) over ()), empno, job 
  from emp order by empno

Ubuntu dapper PXE network install

2006-06-04T13:41:00.000+01:00

I've been testing out the Ubuntu network install environment today. All you need to use the PXE installer is to configure a DHCP server and TFTP server on your network, if you want a more hands off install you can use a "preseed" file. Quite easy to use and it is much more flexible then the ananconda kickstart files.
Ubuntu actually support the kickstart file format as well, so if you don't want to learn the new preconfigure "preseed" format you can just write a normal kickstart syntax file and netboot Ubuntu exactly the same way you would a Redhat/Centos/Fedora box.

Simple instructions on how to configure a Ubuntu netboot server. My install server "tessy" has IP 192.168.95.20 in this example and I'm installing the VIA C3 shoebox PC "elmer" with IP 192.168.95.30.
Don't forget to disable any other DHCP servers you may have on the network such as in broadband routers. Download the netboot.tar.gz and Ubuntu ISO of your choice. I saved both files in the /dl volume on my server.

apt-get install tftpd-hpa
apt-get install dhcp3-server
mkdir -p /var/www/ubuntu-606-server-i386/
mount -o loop /dl/ubuntu-6.06-server-i386.iso /var/www/ubuntu-606-server-i386/
tar xzvf /dl/netboot.tar.gz -C /var/lib/tftpboot

Update the following files to suite your setup:

/etc/default/tftpd-hpa
RUN_DAEMON="yes"
OPTIONS="-l -s /var/lib/tftpboot"

/etc/dhcp3/dhcpd.conf
ddns-update-style none;
option domain-name "hifichoice.com";
option domain-name-servers 83.146.21.6, 212.158.248.5;
ping-check = 1;
default-lease-time 600;
max-lease-time 7200;
log-facility local7;
subnet 192.168.95.0 netmask 255.255.255.0 {
   range 192.168.95.100 192.168.95.150;
   option routers 192.168.95.1;
   filename="pxelinux.0";
   next-server 192.168.95.20;
}

/var/lib/tftpboot/pxelinux.cfg/default
DISPLAY ubuntu-installer/i386/boot-screens/boot.txt
PROMPT 1
TIMEOUT 0
label install-simple
        kernel ubuntu-installer/i386/linux
        append initrd=ubuntu-installer/i386/initrd.gz \ -
        ramdisk_size=14984 root=/dev/rd/0 rw preseed/locale=en_US \ -
        kbd-chooser/method=us netcfg/wireless_wep= netcfg/choose_interface=eth0 \ -
        netcfg/get_hostname=elmer preseed/url=http://192.168.95.20/preseed-606-auto.cfg --
LABEL install
        kernel ubuntu-installer/i386/linux
        append vga=normal initrd=ubuntu-installer/i386/initrd.gz ramdisk_size=14332 \ -
        root=/dev/rd/0 rw  --
My /var/www/preseed-606-auto.cfg

You'll get a few prompts, but nothing annoying. I'll have a deeper look into the preseed file format some other day. I'm sure a hands-off install is possible.

The Ubuntu wiki has a netboot article here and you can read more about the preseed file here.

Oracle index skip scan

2006-06-03T21:44:00.000+01:00

Before we had 9i a composite index practically create left most implicit indeces for the columns in the indexes. We know that we should sort the columns in a composite index with the column with the lowest cardinality to the left. Starting in 9i Oracle introduced a new index scan method called "index skip scan". Even though it's been available for a just over 4 years I haven't seen much news or design articles about this method. With a skip scan you can use any part of a composite index in (almost) any type of query.
It comes with a cost of course since Oracle will have to probe the index the same number of times as the leading column(s). I.e. if you leading column have 10 distinct values, Oracle will have to do 10 probes in the binary tree. Skip scans should usually be avoided in extremly heavy use queries but may be a nice feature to keep in mind for reporting queries and other lower use queries.
The benefit is that you can avoid having several indexes on the same columns, saving you update time and a bit of disk space.

Let us do a little test

OPS$HLINDEN@orcl> create table ct as select * from all_objects;

Table created.

OPS$HLINDEN@orcl> insert into ct select * from ct;

...

OPS$HLINDEN@orcl> commit;

Commit complete.

OPS$HLINDEN@orcl> create index ct_test_idx 
  2  on ct(owner,object_type,object_name);

Index created.

OPS$HLINDEN@orcl> EXEC DBMS_STATS.gather_table_stats('OPS$HLINDEN', 'CT');

PL/SQL procedure successfully completed.

OPS$HLINDEN@orcl> set autotrace traceonly exp
OPS$HLINDEN@orcl> select status from ct where owner='OPS$HLINDEN'
  2  and object_type='TABLE' and object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=49)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=4 Card
          =1 Bytes=49)

   2    1     INDEX (RANGE SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=3 Card
          =1)

OPS$HLINDEN@orcl> select status from ct where object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=29 Bytes=9
          57)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=44 Car
          d=29 Bytes=957)

   2    1     INDEX (SKIP SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=15 Card
          =29)

Notice the extra cost for scanning the index, eventhough we have fewer conditions we have a five fold cost. Now if the index would have been created backwards ,like ct(object_name,object_type,owner), Oracle would hesitate to even use it unless all three columns had conditions since the number of left most distinct values would have been to high (about 14k in this example, with only between 20 and 25 for the other two columns).

Skip scans can be good, but for intense queries do create indexes on the required columns.

Read more here.

Sun cutting costs

2006-05-31T23:40:00.000+01:00

It must be hard to be Jonathan Schwartz a day like this, starting his career as CEO with a 10% head count reduction. Not really news, just a matter of when this would happen. Wall street has been pushing for cost reductions for quite some time now.
Lets hope things will be for the better than Schwartz will be remembered as the man who brought Sun back in to a profitable company.
The statement from Schwartz was pretty clear though. Focus the buisness around the core products and the technology they already have and spend less on specialiced systems.

I'm still convinced Sun will stand strong and keep growing its market share in a number of sectors.

Schwartz has already got a long entry in his blog.

Read the official press release.

Pay for what you use

2006-05-31T14:54:00.000+01:00

Ok, this is a long entry but it's worth the read.

I recently helped a friend of mine who work for a quite small company to sort out one of their secondary Oracle system. They only really have one big database, but they need to have a secondary box for training, testing and development. The system they used to have was a four proc HP DL580g2 server running RHEL 3.
Their main problem was that since they had a training system on the machine they had to pay for Oracle licensing and support for the machine, for all four CPU's. And since their application schema used partitioning and other EE features they had to have EE on this system as well.

They had opted for a term license from Oracle costing $32k annually. The server was starting to run a bit slow as well. Developers often have that effect on systems, starting with one test schema and ending up with about 37. The training system was used by a 10 user classroom perhaps twice a month. Minimal usage (for the training functionality) and load, still a massive license.
The problem, they need a faster system and reduce costs. Both where hard requirements.

Their suggestion was to replace the machine with a HP DL585 with four Opteron processors with RHEL 4, still a $32k per year term license plus support on that. Time to cut back a bit here. Why do they need four cpu's on a training system?

Oracle are nice enough to accept per zone licensing of Oracle under Solaris if you use the resource manager to limit the number of processors the zone can use. I first suggested that they would opt for a Sun T2000 server but they where quite set on using the HP box. Fair enough, they are a HP shop and I'm not in any position to argue with them. So let's stick with the HP box, but let's run Solaris 10 on it. HP even officially support* Solaris 10 on that system. They agreed to do so. Very simple setup, two RAID1 arrays on 15kRPM disks. The databases on the machine aren't very large so there is currently no need for an external disk subsystem.

We installed Solaris 10 update 1 on the server and created two zones. First zone is “training” which was given one CPU and the second zone “dev” was not resource limited at all and could use all CPU's in the system.
One CPU for the training system that had to be licensed, thats a neat little saving of $24k per annum plus the reduced support cost. The server itself came in at about $15k and the Solaris support contract costs $960 per annum compared to the $1500 per year for RedHat. So even with the new hardware they had a nifty saving at about $10k for the first year. Not to mention the full $24k save for year two and three.

I almost sound like a Sun marketer here, but hey, it's plain facts. To prove I'm not a Sun fanboy I'm going to skip the step where I pull out the graphs and ROI calculations. Draw your own graphs! (And may I suggest you do that in Oracle BI Discoverer)

* http://www.hp.com/wwsolutions/solaris/index-all.html

Testing VxVM under Vmware

2006-05-28T23:53:00.000+01:00

Just downloaded and tested the new "free" Vertias Storage foundation software for Linux in a Vmware instance.
Ran in to a few problems, not unexpected.

First, the prein rpm scripts for VXVM fails if you are on a AMD box. Just run through the install script and manually add VRTSvxvm-common and VRTSvxvm-platform with the --nopre rpm options. After rpm is done just run vxinstall to finalize things.

Now for the second hickup. CDS disks do not work under Vmware. For those of you who are not familiar with cdsdisks it is a new platform independant disk format recently introduced in VxVM. It makes it possible to migrate SAN LUN (or physical disks) from
for instance a HP-UX box to a Solaris box without doing any conversion.
To avoid this problem we need to use the old "simple" disk format. The easiest to always default to this is to create two files under /etc/default.
One file called vxdg with the line cds=off and one file called vxdisk with the line format=simple.

The rest is quite straight forward.

# cat /etc/default/vxdg
cds=off
# cat /etc/default/vxdisk
format=simple
# vxdiskadd sdb sdc
[ enter dg1 when prompted for disk group name ]
[ just hit enter for all other prompts ]
# vxdisk list
DEVICE       TYPE            DISK         GROUP        STATUS
sda          auto:none       -            -            online invalid
sdb          auto:simple     dg101        dg1          online
sdc          auto:simple     dg102        dg1          online
# vxassist -g dg1 make u02 1G layout=mirror
# vxprint
Disk group: dg1

TY NAME         ASSOC        KSTATE   LENGTH   PLOFFS   STATE    TUTIL0  PUTIL0
dg dg1          dg1          -        -        -        -        -       -

dm dg101        sdb          -        37701920 -        -        -       -
dm dg102        sdc          -        37701920 -        -        -       -

v  u02          fsgen        ENABLED  2097152  -        ACTIVE   -       -
pl u02-01       u02          ENABLED  2097152  -        ACTIVE   -       -
sd dg101-01     u02-01       ENABLED  2097152  0        -        -       -
pl u02-02       u02          ENABLED  2097152  -        ACTIVE   -       -
sd dg102-01     u02-02       ENABLED  2097152  0        -        -       -

# mkfs.vxfs /dev/vx/dsk/dg1/u02
# mount /dev/vx/dsk/dg1/u02 /u02
# df /u02
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/vx/dsk/dg1/u02    1048576     17338    966793   2% /u02

Vertias actually let me create 5 user volumes before starting to complain about licensing.
VxVM vxvol WARNING V-5-1-12761 You have exceeded the authorized usage for this product and are out of compliance with your License Agreement. Please email sales_mail@symantec.com or contact your Symantec sales representative for information on how to obtain additional licenses for this product.