Kevin Closson's Blog: Platforms, Databases and Storage

Learning “New and Exciting” Things About Really Old Stuff

…that’s what Max Kanat-Alexander seems to be doing based upon his recent Oracle-bashing rant. Now, I’m not calling Max to the mat because I’ve learned that the Web grants virtual get out of jail free cards to people earning a living developing free stuff, and I have in the recent past been a user of bugzilla (Max is the primary developer), albeit not by choice. I never could get too excited about a bug tracking system that wasn’t integrated with customer support, contracts, field logistics and other general CRM. There certainly is no shortage of bug tracking software, but I’m not blogging about that.

Hey, Old Dogs: Time For New Tricks

The bit I don’t like about Max’s rant is the absurd assertion that Oracle professionals must certainly have never used any other database. I quote:

Most Oracle DBAs, it seems, have never used any other database system. Or they have, but it was in Ancient Times before there was a SQL Standard or something. (By the way, that would have to have been before 1992, when SQL-92 was made. Hi, welcome to the 90’s!)

The nineties? Please! The first SQL ANSI standard was 1986–7 years after Oracle made the first SQL-based commercial RDBMS with lessons taken from the System/R and other playbooks. Yes, 1986, which according to Max’s profile coincided with his days in elementary school. That coincided with the time period in which I was developing and maintaining Informix ACE/ALL applications that fronted IBM 370 mainframes. No, Max, Oracle professionals are not, by and large, rdbms-xenophobes. In fact, the opposite is true. Most shops that deploy Oracle also deploy other products because they have real data centers. By the way, using databases before there was a SQL standard (1986, not 1992) wouldn’t have much to do with SQL because it was, uh, quite scarce.

Max quickly throws us a bone:

I don’t think Oracle is a totally worthless product.

But seemingly recants with the following red-herring:

I know that my Oracle install stopped working once just because I had added, oh, a fifth database to it. Apparently you have to explicitly tell Oracle (with a very cryptic command that’s specific to just your system, because it involves filesystem paths) that you want to have more than about five databases.

I’m not even going to validate that assertion by discussing it. Wait, I changed my mind. No, I’m not going to do it-the assertion is absurd. Just because one tries to base more than 5 databases from a single ORACLE_HOME and had a filesystem-related problem certainly doesn’t mean it can’t be done or isn’t supported. It’s all about configuration resources. How about 80 databases from a single shared ORACLE_HOME in a cluster?

No, Max, we don’t like Oracle because we are ignorant. We use it to solve problems. Can some of those problems be solved by free stuff? I suppose, but I don’t care.

Max continues:

Okay, so I’m biased and I have an unusual viewpoint…[text deleted]…Most people aren’t porting a shipping ANSI SQL application to many different databases. But I am, which means I’ve learned a lot about all the databases.

Huh? Applications supported on all of Oracle, DB2, SQL Server, etc, etc? Avante Garde!

Finally, Max lays it all out there in true protest style:

In every other database out there, an empty string and NULL are not the same thing. The Oracle SQL Reference tells you not to treat an empty string like a NULL (because they might change that behavior in the future), but they don’t actually give you any way to not treat it like a NULL!

You can’t SELECT a CLOB (that’s a TEXT field to the rest of the world) if there’s a GROUP BY clause. What?

Subtracting one month from March 29, 2007 gives you…February 29, 2007, a day that never existed. In fact, because it never existed, Oracle throws an error if you do that. Other databases just give you February 28 (or March 1 if you’re adding, I think).

Oracle doesn’t support the ANSI SQL “LIMIT” clause, it uses something weird in the WHERE clause instead.

Oracle has a hard limit on IN clauses of 1000 items. But it doesn’t complain if you OR together multiple IN clauses with 1000 items each…

Oracle doesn’t allow identifiers to be longer than 32 characters (index names, column names, etc.).

Oracle doesn’t support ON UPDATE CASCADE for foreign keys. Even MySQL supports that, nowadays.

And each of these 7 points have not been churned over and over about a million times on the Web?

Readers, have anything to say about this?

While doing an install of 11g x86_64 11.1.0.6 Clusterware today I hit a problem I’ve seen before but had to think for a moment about what was the cause of the error. I figured if I had to scratch my head on this one, someone, someday would likely be out googling for the answer. Here is the error text as it appears in the log:

The location /data/ocr.dat, entered for the Oracle
Cluster Registry (OCR) is not shared across all the nodes in the cluster.
Specify a shared raw partition or cluster file system file that is visible by
the same name on all nodes of the cluster.

In the following screenshot you can see that in the shell at the bottom I first did a chown ora.dba of the nfs directory where I want to locate the OCR file. Clear evidence of cheating. See, when I did that I was able to proceed to the next screen (voting disk). I instead hit the back button on the voting disk screen and changed the ownership of the /data directory just to raise the error and make this quick blog entry. Nice, aren’t I? Anyway, the deal is that this is an error message without an error number erroring erroneously-afterall, the /data directory was in fact shared between the nodes. The problem was that the install precedure uses a write in that directory as evidence of whether it is shared or not. If it isn’t writable it thinks it isn’t shared.

For sentimental reasons I’ve taken interest in Dataupia. See, their offices are in One Alewife Center, Cambridge, Mass and due to my background in NUMA technology I harbor sentimental feelings for the MIT Alewife System, which, along with DASH were truly the front-front runners in early non-commercial implementations of NUMA technology. However, beyond that cursory connection between Dataupia’s operations location and an obscure non-commercial NUMA system, I quickly find myself confounded by Dataupia. But confounded on the basis of the technology?  Oh, no, I’m much too petty for that.

What confounds me is how to pronounce the name of this outfit. Yes, you heard right. Let me get this straight, I’m told that some pronounce it day-tah-toe-pee-yah. Ugh, I only see one letter t. I’ve heard folks pronounce it day-tah-yoo-toe-pee-ah. I say it is impossible to get more than 5 syllables out of Dataupia and I still only see one letter t.

That leaves me with the only phonetically correct possibility: day-tah-you-pee-ah.

How Much Data (Love) Do You Need?
I hate poor marketing…with a passion. Even more so when it smells so similar to a Leo Sayer tune. Check out the following screen shot. What does “as much data as an organization needs” mean? But honestly, repeating the very same lyrics in the next stanza-just for emphasis sake, or just in case we had forgotten so quickly. And while I’m being so petty, could someone tell me what in the heck “persistent access” is supposed to mean? Even after reading that pair of words twice within 20 words in the same paragraph I still don’t understand it. The only thing that comes to mind when I think of persistent access is VNC or Sun Ray. Anyway, here is the screen shot:

Proofless Benchmarks
Does anyone see any proof in this link to a supposed “benchmark?”

In the transcript of yesterday’s earnings call, Larry Ellison said:

We are not going to sit on our laurels; we have a major database innovation that we will announce in September of this year. It is going to be a very big and important announcement for us so we are not standing still in database.

I know, and that is why I can’t blog about any of the work I’m doing at Oracle…at least until September.

I’m not going to hide my true feelings any longer. I hate Fibre Channel SANs.

Yes, I know, I haven’t exactly hidden my position on that topic, considering all the SAN related postings I’ve made. Look, Fibre Channel SAN was great technology for connecting large numbers of disks to a single, large SMP. I just don’t think the technology has a rightful place in Grid computing. I know, broken record.

I’m glad to see that one year after HP bought my former company (PolyServe), the technology is starting to show up in interesting packaging as this piece in Enterprise Storage Forum points out. That’s right, HP is using PolyServe for extremely large scale clustered NAS-a good fit. But that is not why I’m blogging the point.

A few things about the HP StorageWorks 9100 Extreme Data Storage System stand out to me. First, HP has pumped PolyServe’s stomach. I’ve sifted through the, er, um, egestion and found a Fibre Channel SAN suspended in the colloid. Ah, what a relief. That’s right, the StorageWorks 9100 is not a Fibre Channel SAN gateway. It’s all SAS.

The other thing that I see percolating to the top of HP’s messaging on this solution is the fact that if you so desire you can execute applications in the NAS heads. So if you want to have local-disk access speed for data you’ve ingested via NFS, you can do that. Consider, for instance, the ability to log into a NAS head and perform compression without any network overhead. But then, that’s nothing new since this particular product has always supported that sort of mix.

I also read somewhere that folks are critical of HP’s goal to bother offering such extreme capacity. Some folks (rightfully) argue that Petabyte storage “needs” are usually a sign of postponing the difficult work of determining what to archive and when. ILM is tough business to get right, I know. However, the main application of this particular HP offering is for Web 2.0, and face it, when someone wants to view a photo they don’t want to see a browser hour glass while the application goes off to near-line storage for a photo that hasn’t been viewed in the last 90 days.

Finally, I see in the materials that they tout 200MB/s NAS bandwidth per NAS head. I think they are low-balling for safety sake. This system consists of 3.5″ SAS drives and 12 of them offer substantially more than 200MB/s. Trust me. More like 900-1000 MB/s actually. But then, that depends on the workload. I suppose randomly plucking out photo/video content mixed with writes at the rate of 200MB/s per NAS head sounds pretty good.

What does this have to do with Oracle?

Well, I’m an Oracle-minded guy who hates Fibre Channel SANs and Oracle on NFS is fully supported. And, oh yeah, I do like SAS. Finally, I’m former PolyServe and it is my blog, so, I blogged it 🙂

…have been greatly exaggerated. However, I certainly have not been blogging as frequently as I’d like. The fact is that I am heads down doing performance work on a product that I cannot speak about until Oracle OpenWorld. Larry Ellison is happy to launch the product at the show and I shan’t steal thunder!

I did notice that my seemingly dead blog was propped by Jeff Hardison who was the gentleman that recommended WordPress as a blogging platform to me. He also gave me sage advice like, “blog everyday, even if it is just a short piece”, which I seem to have ignored completely. I can’t bring myself to blog about nothing. About all I could blog about these days is not having time to blog.

Communicator’s Conference Highlights

Quite some time back I made a blog entry about deploying Oracle Clusterware for non-RAC purposes. As I pointed out in that entry, there were license ramifications. That was then, this is now.

In this press release about Oracle Clusterware, Oracle is announcing that Oracle Enterprise Linux (with Unbreakable Linux Support) can deploy Oracle Clusterware to provide high availability services for any purpose they so desire.

Now that, is interesting.

Some additional, related links:

Making Applications Highly Available Using Oracle Clusterware

Oracle Clusterware API

Considerations for “Stretch Clusters” with Oracle Real Application Clusters

No worries, we won’t have to lower the Cone of Silence. True, you will see use of an “underbar” init.ora parameter in this post, but its use is not the central theme. No, no Silver Bullets here. This is another post in the Little Things Doth Crabby Make series.

I routinely brag about the sophistication level of my blog readers, so, folks, don’t let me down. Let’s start a thread about why the contents of the following session output would make my Little Things list. OK, come on…

SQL> set timing on
SQL> 
SQL> alter session set "_parallel_broadcast_enabled" = FALSE
  2  
SQL> select count(*) from ap_ae_lines_all where AE_LINE_ID > 1397437860 ;

  COUNT(*)
----------
         0

Elapsed: 00:01:21.70
SQL> 
SQL> alter session set "_parallel_broadcast_enabled" = FALSE;

Session altered.

Elapsed: 00:00:00.00
SQL> 
SQL> select count(*) from ap_ae_lines_all where AE_LINE_ID > 1397437860 ;

  COUNT(*)
----------
         0

Elapsed: 00:01:30.46

But my, oh my, how I’ve tried. OK, I guess my new name is Fan Boy. I know for a fact that I’ve been pretty relentless on this particular server for over 100 days of its current 215-day life.

-sh-3.00$ cat /etc/redhat-release
Red Hat Enterprise Linux AS release 4 (Nahant Update 3)

-sh-3.00$ uptime
 14:41:17 up 215 days, 14:32, 15 users,  load average: 37.85, 37.48, 25.89

And, top(1):

  top - 14:40:44 up 215 days, 14:31, 15 users,  load average: 40.91, 38.05, 25.62
Tasks: 309 total,  30 running, 278 sleeping,   0 stopped,   1 zombie
Cpu0  : 92.8% us,  7.2% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu1  : 90.1% us,  9.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu2  : 89.3% us,  9.8% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.9% hi,  0.0% si
Cpu3  : 90.1% us,  9.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu4  : 89.2% us,  9.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.9% hi,  0.0% si
Cpu5  : 89.1% us, 10.9% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu6  : 92.8% us,  7.2% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Cpu7  : 93.7% us,  6.3% sy,  0.0% ni,  0.0% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:  10393736k total,  9347616k used,  1046120k free,     1892k buffers
Swap: 10288440k total,   838236k used,  9450204k free,  6264396k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14919 kclosson  15   0  120m  84m 7076 S 30.3  0.8   0:17.67 sqlldr
14942 kclosson  15   0  119m  84m 7068 S 29.4  0.8   0:17.75 sqlldr
14940 kclosson  15   0  120m  84m 7068 S 28.6  0.8   0:16.21 sqlldr
15008 kclosson  16   0  668m  35m  29m R 28.6  0.3   0:16.48 oracle
14924 kclosson  15   0  119m  84m 7076 R 26.8  0.8   0:16.39 sqlldr
14932 kclosson  16   0  120m  84m 7068 R 26.8  0.8   0:17.07 sqlldr
14959 kclosson  15   0  668m  34m  29m S 25.9  0.3   0:15.96 oracle
14961 kclosson  16   0  668m  34m  29m R 25.9  0.3   0:14.90 oracle
14945 kclosson  15   0  119m  84m 7076 S 25.0  0.8   0:16.07 sqlldr
14980 kclosson  15   0  668m  34m  29m S 25.0  0.3   0:15.09 oracle
14935 kclosson  16   0  119m  84m 7068 S 24.1  0.8   0:15.05 sqlldr
14947 kclosson  16   0  119m  84m 7072 R 24.1  0.8   0:15.90 sqlldr
14943 kclosson  15   0  119m  84m 7076 R 23.2  0.8   0:14.75 sqlldr
14938 kclosson  16   0  120m  84m 7068 S 22.3  0.8   0:14.35 sqlldr
14941 kclosson  15   0  119m  84m 7076 R 22.3  0.8   0:15.96 sqlldr
14951 kclosson  15   0  120m  84m 7068 S 22.3  0.8   0:16.96 sqlldr
14921 kclosson  16   0  120m  84m 7068 R 21.4  0.8   0:17.84 sqlldr
14934 kclosson  15   0  120m  84m 7076 S 21.4  0.8   0:16.13 sqlldr
14929 kclosson  15   0  119m  84m 7076 R 20.5  0.8   0:17.70 sqlldr
14950 kclosson  16   0  119m  84m 7068 R 20.5  0.8   0:13.63 sqlldr
14922 kclosson  15   0  120m  84m 7068 S 19.6  0.8   0:17.40 sqlldr
14977 kclosson  15   0  668m  34m  29m R 18.7  0.3   0:16.38 oracle
15002 kclosson  16   0  668m  34m  29m R 18.7  0.3   0:15.00 oracle
14920 kclosson  16   0  119m  84m 7076 R 17.8  0.8   0:17.97 sqlldr
14923 kclosson  16   0  119m  84m 7068 R 17.0  0.8   0:13.44 sqlldr
14925 kclosson  16   0  120m  84m 7068 S 17.0  0.8   0:13.06 sqlldr
14927 kclosson  16   0  119m  84m 7076 R 17.0  0.8   0:15.05 sqlldr
14931 kclosson  16   0  119m  84m 7076 R 17.0  0.8   0:15.18 sqlldr
14957 kclosson  15   0  668m  34m  28m S 17.0  0.3   0:14.16 oracle
14930 kclosson  16   0  120m  84m 7068 R 16.1  0.8   0:15.31 sqlldr
14986 kclosson  15   0  668m  34m  29m R 16.1  0.3   0:14.37 oracle
14936 kclosson  15   0  119m  84m 7068 S 15.2  0.8   0:15.58 sqlldr
14964 kclosson  15   0  668m  34m  29m S 15.2  0.3   0:17.10 oracle
15014 kclosson  15   0  668m  34m  28m S 12.5  0.3   0:12.83 oracle
14949 kclosson  16   0  120m  84m 7076 S  7.1  0.8   0:15.70 sqlldr
14955 kclosson  16   0  666m  35m  31m R  4.5  0.4   0:03.11 oracle
14966 kclosson  16   0  666m  35m  31m R  4.5  0.3   0:02.80 oracle
14998 kclosson  15   0  666m  35m  31m S  4.5  0.3   0:02.68 oracle

There are 120 spindles downwind of this….drumroll please….

$ asmcmd
ASMCMD> ls
SDATA1/
SDATA2_1/
SDATA2_2/
SDATA2_3/
ASMCMD> rm -fr *
ASMCMD>

Eek!

In other news, I just learned that Greg Rahn is about to get his hands on a set of HP DL580 G5 (Tigerton goodies). I’d sure like to get Silly Little Benchmark numbers from that sweetheart of a system…wish in one hand, you-know-what in the other I suppose.

Oracle’s acquisition spree has wound up producing odd bedfellows.

I’ve often wondered why companies really want to own their computers. HP has voiced the same rhetorical question by launching AIaaS.

Why Do They Put Those Data Centers There?
But Atlanta? Is power really that cheap in Atlanta? It must be all that cheap hydroelectric power down there.

What’s This Have To Do With Oracle?
This is an Oracle-related blog, but I can’t answer that question. I’m watching just like you all are.

The Irony
I find it very ironic that HP announced this hosting initiative at their Technology@work Conference in Barcelona Spain considering the other hot news about the HP DL785 which is an 8-socket “Barcelona” Socket-F based Server. I am happy to see Proliant 8-socket Servers.

More on the DL785

More on the “Barcelona” Quad-Core.

Some rambling thoughts…

Oracle Database Administrator or Data Administrator?
Oracle continues to add features/functionality that makes the dutiful Database Administrator look a lot more like a Data Administrator. Some would argue it started with ASM since volume management was placed into the hands of the DBA. I’d argue it started with RMAN since that feature was one of the most significant, early features that took functionality away from the OS (and 3^rd Party software) and put control in the hands of the DBA. After all, Oracle backups before RMAN had very little to do with Oracle (version 7 and earlier). I’d argue further that features like SecureFiles also make the Database Administrator more of a Data Administrator-or at least it seems it should make a DBA start to think like a “Data Administrator.” I think that trend will continue. I think that increases the value proposition of the Oracle DBA in the datacenter. I think it is a good thing.

DBAs might even find themselves performing lower-level storage operations such as creating OS-level logical drives. One such example would be HP Smart Array logical drives and the associated CLI is the hpacucli command on Linux.

I’ve been Meaning to Blog This One…
Quite some time ago I was fiddling with hpacucli. Yes, it was late at night. Yes, I was tired and crabby. Why would what’s happened in the following screen output make me crabby?

# hpacucli  
Array Configuration Utility CLI 7.15.19.0
Detecting Controllers...Done.
Type "help" for a list of supported commands.
Type "exit" to close the console.
>  ctrl all show status
Error: Expecting "controller" in target. Type "help" for more information.
> ctrl all show status
Smart Array P400 in Slot 1
   Controller Status: OK
   Cache Status: Not Configured
   Battery Status: OK

… the CLI doesn’t parse out leading whitespaces.

Maybe somebody, someday will be battling the hpacucli CLI and google their way to this post.

P.S., For goodness sake, please don’t go running to your local HP rep to tell him I’m bashing HP. Yes, HP Smart Array is great technology. I just don’t think whitespaces are as significant as hpacucli does 🙂

Yes, I admit, sometimes little things make me crabby. Or is it that being crabby makes me easily irritated at small things? I think it’s the former rather than the latter.

Angry Email Makes Me Crabby
I am surprised at how many emails I’ve received from angry Storage Administrators about my position against Fibre Channel. I generally just ask them to not take it so personally. After all, there is more to storage than whatever storage networking protocol any particular server is plumbed with. Anyone who reads my Manly Man series about Fibre Channel will see that I’m just not a big fan of the current misapplication of Fibre Channel technology. As I point out in installment number two in the Manly Man series, Fibre Channel was originally intended to solve the difficulties associated with connecting hundreds (or thousands) of low capacity drives to a small number of large servers. These days it is being used to provide storage connectivity for hundreds of servers to a couple of huge FC storage arrays. And these hundreds of servers have both Ethernet and FC connectivity-overkill, plain and simple.

A lot is changing in the storage world where Oracle is concerned. I hope to come out of the foxhole to blog more about it over the next few months. Well, maybe I should say a few months from now. In the meantime I’ll be making some datacenter visits. I always learn something interesting when I get a chance to do that.

Little Changes Make Me Crabby
Like, for instance, the following example of the change to iostat -x output from RHEL4 to RHEL5. Notice how the rkB/s and wkB/s columns are missing? Yes, it is simple math to divide [rw]sec/s by 2 to get the same data, but there must be at least 42 of you folks that are feeding iostat -x output through some text processing, right? Time to adjust your scripts. Or is it? I wonder how many folks are really diving into OEL5/RHEL5? It seems a conservative IT shop with a functional RHEL4 configuration would most likely stay put. Any readers care to voice their experience with OEL5/RHEL5 adoption? Is it happening? Are folks waiting for their 11g adoption with plans to team that up with a move to OEL5/RHEL5?

 
# uname -r
2.6.18-53.ELsmp
# iostat -x 1 1
Linux 2.6.18-53.el5 (host1)        03/07/2008

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.08    0.00    0.04    0.03    0.00   99.85

Device:         rrqm/s   wrqm/s   r/s   w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0        0.16    10.38  0.33  0.95    10.55    90.64    79.47     0.02   14.12   0.86   0.11
sda               0.00     0.00  0.00  0.00     0.01     0.00    21.05     0.00    3.18   2.87   0.00
dm-0              0.00     0.00  0.45 11.33    10.47    90.64     8.58     0.39   32.67   0.09   0.11
dm-1              0.00     0.00  0.00  0.00     0.01     0.00     8.00     0.00    5.05   0.51   0.00
cciss/c0d1        0.00     0.00  0.00  0.00     0.01     0.00    10.12     0.00    4.56   2.63   0.00
cciss/c0d2        0.00     0.00  0.00  0.00     0.01     0.00    10.38     0.00    4.30   2.52   0.00
cciss/c0d3        0.00     0.00  0.00  0.00     0.01     0.00    10.38     0.00    6.17   3.35   0.00
cciss/c0d4        0.00     0.00  0.00  0.00     0.01     0.00    10.38     0.00    5.50   3.22   0.00
cciss/c0d5        0.00     0.00  0.00  0.00     0.01     0.00    10.64     0.00    3.38   2.59   0.00
cciss/c0d6        0.00     0.00  0.00  0.00     0.01     0.00    10.64     0.00    4.05   3.31   0.00
cciss/c0d7        0.00     0.00  0.00  0.00     0.01     0.00    10.12     0.00    4.44   2.29   0.00

$ uname -r
2.6.9-34.ELsmp
$ iostat -x 1 1
Linux 2.6.9-34.ELsmp (host2)    03/07/2008

avg-cpu:  %user   %nice    %sys %iowait   %idle
           1.79    0.00    1.28    0.30   96.63

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
cciss/c0d0   0.06  11.83  0.14  3.21   23.07  120.39    11.53    60.19    42.77     0.68  201.33   4.60   1.54

…just a quick blog entry to plug Daniel Morgan’s (of Puget Sound Oracle User Group) very useful collection of scripts and other goodies:

Morgan’s Library