Testing iSCSI

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

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

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

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

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

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

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.