Oracle Database 10.2 De-Supported

OK, that’s an alarmist title and is not really true. However, I feel this topic is important enough to warrant the title. I’ve talked to a lot of people lately that were not aware of the actual support status of Database 10.2 and below (it changed on July 2010 and the 1 year grace period ended July 2011). My goal of this post is certainly not to be critical of anyone, just to spread the word and allow people to make informed decisions.

The document “Oracle Lifetime Support Policy” (PDF, 474KB) discusses most of the details. There’s also a FAQ on MOS entitled “Oracle Database 10.2 End of Premier Support – Frequently Asked Questions [ID 1130327.1]” (HTML Version or Full Flash Version (for now…(details on that here)).

Here’s my quick summary of some key points:

• 10.2 general availability was July 2005
• “Premiere Support” is what you probably think of as “supported”.
  • It extends for 5 years from the date of general availability.
  • It ended for 10.2 in July of 2010.
  • Oracle offered a 1 year, no-cost grace period for 10.2 premiere support customers which ended in July of 2011. This was basically a year of free Extended Support.
• “Extended Support” is a for-cost option that allows you to extend Premiere Support for an additional 3 years.
• “Sustaining Support” is the level of support after Premiere Support ends, assuming you are not paying for Extended Support.
  • This is probably where a large majority of the 10.2 and earlier customers are today, but may not be aware of this.
  • This does NOT include a number of key services such as “New updates, fixes, security alerts, data fixes, and critical patch updates”.

A number of customers that I’ve talked to realized this situation when they went to download the latest Critical Patch Update and could not as they were on Sustaining Support.

The PDF also contains the following chart which is a nice overview of the dates for planning purposes:

Logger Project Moved Temporarilly

The site samplecode.oracle.com was decommissioned recently. I was hosting a number of projects there including “Logger”, my PL/SQL instrumentation package. Until I find a new home, here’s a temporary link to the latest release (1.4) or if you just want to view the readme use this link. I’ll update this post when I decide on a new home.

This project is still very much alive and I will continue to update it based on your suggestions. I know there are a number of them in my “queue”, I just haven’t had time. My plan is to find a public place to host it such as github where everyone can contribute.

A Little Hard Drive History and the Big Data Problem

Most of the “Big Data Problems” I see are related to performance. Most often these are long running reports or batch processes, but also include data loads. I see a lot of these as one of my primary roles is in leading Exadata “Proof of Value” (POV) engagements with customers. It’s my observation that over 90% of the time, the I/O throughput of the existing system is grossly under-sized. To be fair, the drives themselves in a SAN or NAS are not the bottleneck, it’s the 2x 2 Gbps or 2x 4 Gbps Fibre channel connection to the SAN more often than not. However, the metrics for hard drive performance and capacity over the last 30 or so years were a lot easier to find so that’s what this post is about.

In short, our capacity to store data has far outpaced our ability to process that data. Here’s an example using “personal” class drives to illustrate the point. In the year 2000 I could by a Maxtor UDMA 20 GB hard drive connected locally via (P) ATA which provided roughly 11 MB/s of throughput (the drive might be limited to a bit less, but not much). Today, I can buy a 3 TB Seagate Barracuda XT ST32000641AS that can sustain 123 MB/s of throughput. So, lets say I need to scan (search) my whole drive. 21 years ago that would take 31 minutes. Today it would take 7 hours! What?!?! The time it takes to search the data I can store has increased by a factor of 14x. How can this be? What about Moore’s Law? Sorry, that only works for processors. Oh right, I meant Kryder’s Law. Kryder’s Law states that magnetic disk areal storage density doubles annually and has nothing to do with performance. Well, actually this phenomenon is half the problem since throughput isn’t doubling annually.

I did a little research and found some relevant data on seagate.com, tomshardware.com, and of course The Wayback Machine. Now on to the data and some graphs (click to enlarge graphs):

	Seagate ST506	Rodime R032	Seagate ST3550A	Quantum Fireball ST3 2A	IBM DTTA-351010	Seagate Cheetah X15	Seagate Cheetah X15.3	Seagate Cheetah 15k.6	Seagate Cheetah 15k.7	Seagate Pulsar XT.2 SSD
Year	1979	1983	1993	1998	1999	2001	2003	2008	2011	2011
Capacity (MB)	5	10	452	3276	10240	18432	36864	460800	614400	409600
Capacity (GB)	0.005	0.010	0.441	3.2	10	18	36	450	600	400
Throughput (MB/s)	5	0.6	11.1	7.6	9.5	29	63.6	164	204	360
Capacity Factor of Change	1x	2x	90x	655x	2,048x	3,686x	7,373x	92,160x	122,880x	81,920x
Throughput Factor of Change	1x	0.1x	2x	2x	2x	6x	13x	33x	41x	72x
Capacity Percent Change	0%	100%	8,940%	65,436%	204,700%	368,540%	737,180%	9,215,900%	12,287,900%	8,191,900%
Throughput Percent Change	0%	-88%	122%	52%	90%	480%	1,172%	3,180%	3,980%	7,100%
Time in Seconds to Read Full Drive	1	16	41	431	1078	636	580	2810	3012	1138

So, what does all of this mean? Well, to me it means if you’re architecting a data warehouse or even small data mart, make sure you focus on the storage. Over and over again I get drawn into discussions about the minutiae of chip speeds or whether Linux is faster than Solaris, yet when I ask about the storage the answer is almost universally “we’ll throw it on the SAN”. OK, how many HBAs and what speed? Is the SAN over-utilized already? etc, etc, etc.

So, how does this relate to Exadata? The central focus of Exadata is I/O throughput. By including processing power in the storage servers, Exadata has a fixed relationship between capacity and throughput. As you add capacity, you add throughput.

Dropbox for Servers

I love dropbox. Let me say that again: I love dropbox! I’ve been using it for well over a year now and it’s the best file synchronizing / sharing service I’ve found (and I’m certainly not alone in that opinion). I use it on my laptop, my home desktop, my home server, my iPhone, my iPad, and with selective-sync, on my wife’s laptop. All of my important files (up to 50 GB worth which is what I pay for) are synced between all of those places all of the time. I don’t send 20 MB email attachments (which completely annoys me), I send links to download from dropbox. The list goes on and I’m now on a bit of a tangent.

Sever Use Case

I work on a lot of shared servers, such as for POVs (proof of value, benchmark) and most of them are Linux-based (I also sometimes use Amazon EC2 instances to teach APEX Classes). I found myself spending a lot of time transferring files to and from the server such as SQL scripts, AWR reports, SQL Monitoring reports, etc via scp (sometimes rsync). I didn’t want to setup my dropbox account on the server as well since it has all of my personal files too. Then I had a bit of an epiphany(and I’m sure I’m not the first one to think of this):

1. Create a 2nd, free dropbox account (2 GB of free space)
2. Setup that account on the linux server using the CLI version of dropbox located here. Dropbox has a formal, supported Linux client, but there were a number of package dependencies and specific versions that were more challenging to work around than it was worth.
3. Share a folder between my primary dropbox account and my 2nd dropbox account.

Since all my secondary dropbox account will see are the folders I share with it, anyone else that happens to be on the server won’t have access to my personal files. Whatever files I add to that folder on my laptop will almost instantly show up on the server and vice versa.

Security Caveat

Having co-authored a book on security (“Applied Oracle Security“), it’s clearly one of my primary interests. While dropbox does encrypt your files when stored on it’s server and does use TLS to encrypt them on the network, and they have policies and internal controls in place to prevent their own employees from decrypting them without authorization, it’s still possible. They store and can access the encryption keys. If the government compels them to decrypt your files, they can and will. So, if you’re storing your tax records or corporate intellectual property, I strongly encourage you to use some form of client-side encryption. Personally, I use a truecrypt file (which I mount as a disk) for super-sensitive stuff within dropbox. Actually, my whole dropbox folder sits on a truecrypt volume (50 GB) which prevents someone that steals my laptop from getting to all of my files, but this does nothing to stop dropbox employees from accessing them. I then create small (1-30 MB) truecrypt files / volumes within dropbox to encrypt anything that is super-sensitive before it leaves my laptop. So, my layers are Big Truecrypt Volume > Dropbox Folder > Little truecrypt volumes for any sensitive files.

Off-Topic: Avoid Levana Baby Monitors

I know this is completely off topic and I haven’t had time to blog in a while but there’s been a new addition in our family. I know there are a lot of parents out there and after the experience I’ve had with Levana, I’ll do everything in my power to make sure others avoid their products. Here’s a link to my review on amazon : http://sn.im/levana-review. I’ll also try and post some positive product reviews soon to offset this negativity…

Static Version of rlwrap for sqlplus

Quick Summary

If you don’t have root access to a machine, it can be challenging to compile rlwrap (a nice companion to sqlplus). You can compile it on another machine / VM, then use a utility called statifier to produce one binary that includes all of the required dependencies. If you want to just cut to the chase, you can download the version I compiled for OEL 5.6 x64 here.

How to Compile

If you don’t trust what I’ve compiled (and you probably shouldn’t if you care about security), here are the steps to compile your own version.

On a machine or VM that you control running a comparable Linux distribution:

1. Download and compile rlwrap.
2. Download and compile statifier.
3. Compile your static version of rlwrap:

   # statifier /usr/local/bin/rlwrap /tmp/rlwrap_static_x64

Copy the file to your locked-down server (where you don’t have root), then:

1. Put it in some logical directory such as ~/local/bin

2. chmod +x ~/local/bin/*

3. I create a symlink for it (but you could just rename it if you want):

   ln -s ~/local/bin/rlwrap_static_x64 ~/local/bin/rlwrap

4. Edit ~/.bashrc and add it to your path:

   export PATH=$PATH:~/local/bin
   alias sqlplus='rlwrap sqlplus'

5. source ~/.bashrc

More Details

You might be saying, “but Tyler, surely your sys admins trust you with root?”. No, they don’t, and I’m fine with that. I work on a lot of Exadata POVs where we have absurdly tight timeframes. The machines are managed by another group (a group which I have a tremendous amount of respect for). So, I never get root, but that also limits my liability to some degree if something should go wrong.

Yes, I know you can compile the required libraries (gnu readline and ncurses) and set compiler flags to point to different directories for these libraries, but something always goes wrong and I’m tired of hacking around with it.

My Brief Review of “Expert Oracle Exadata”

I purchased the Alpha version of Expert Oracle Exadata (amazon link) several months ago, written by Kerry Osborne, Tanel Poder and Randy Johnson. The print copy is still in pre-order (shipping very soon), but you can order the ebook from apress here. My primary role these days is talking about and working with Exadata in customer presentations, classes, and POVs. While I’m not in the same league as the real Exadata experts such as the authors, I’ve worked with Exadata enough over the past year or so to have a good sense about what you need to know.

This book is simply outstanding. The concepts are clear and concise, and ordered in a nice logical progression.  The language has a very approachable, conversational tone. Most importantly, the book is filled with examples that serve to either reinforce or prove the concept of that section. In case you are concerned that there is a lot of marketing “fluff”, forget it. None of the authors work for Oracle and are completely free to speak their minds (so are Oracle employees, just emphasizing the point here). Their primary motivation is to educate and they clearly highlight some of the “issues” you can encounter in the real world with Exadata (yes, they exist).

Additionally, the comments from Kevin Closson that appear throughout the production release of the book as “Kevin Says” are worth the cost of the book alone. Kevin was one of the architects of Exadata and there are very few people (if any) that know more about Exadata from the high level concepts down to the code itself. He also tends to tell it like it is, keeping the facts paramount, and all other considerations secondary. On a related note, Kevin was a great mentor to me over the last year or so when I got thrown into the Exadata world. I had no clue about hardware (“what’s an HBA?”), nor much experience with data warehousing. I can’t thank him enough for his time and  efforts.

In my opinion, this is by far the best source of information out there on Exadata. Actually, I usually reccomend that people watch Kevin’s webcast entitled “Oracle Exadata Storage Server Technical Deep Dive. Part I” (I also made an iPhone friendly version of it here with chapter markers) first, as it gives you a 1 hour intro to the concepts. After watching that, buy (and read) the book.

To the authors, thank you for thanking me! They added a nice thank you section to the “Unofficial Editors” in which I was mentioned by name. This was a very nice touch and I sincerely appreciate it. They helped me a whole lot more than I helped them.

Logger 1.4.0 Released

This is just a minor update to logger (project page here) to fix a bug in detecting DB Version 11.2 on RAC.

One important point to note is that in APEX 4.0 (or possibly 4.1), the APEX team removed the “create any context” priv when creating a schema associated with provisioning a workspace. Logger needs this priv. So, this means you won’t be able to simply install logger through the SQL Workshop without granting this priv to the schema owning logger. The biggest impact will be on hosted instances such as apex.oracle.com as you can’t simply ask someone to grant this priv to you (and please don’t try). I will try to see if it’s easy to work around this issue and what the implications are in the next few weeks.

AWR Formatter 1.6 Released

I made some minor updates to my previously released Google Chrome Extension called AWR Formatter. Enhancements include:

• Performance increased by 3x in rendering large AWR reports.
• Added new tabs to SQL Popup for Query Plan, Color SQL, and AWR SQL Report

The new options in the SQL Popup expose features of the AWR repository that I don’t think many people are aware of, but I think are really useful.

Installation

If you already have it installed, it should update automatically and the version will say 1.6. If you do not have it installed:

• Open this URL in Google Chrome: http://dl.dropbox.com/u/4131944/AWR-Format/AWR-Format.crx
• Choose “Install”
• Go to Tools > Extensions and check the “Allow access to file URLs” box. This is critical as it only works on local files.

I hope to add support for RAC reports soon…

awrload.sql > ORA-12899: value too large for column….

Today I tried to load exported AWR data from a recent Exadata POV via $ORACLE_HOME/rdbms/admin/awrload.sql and recieved the following error:

ORA-12899: value too large for column "SYS"."WRH$_ACTIVE_SESSION_HISTORY"."MODULE"

Searched all over and didn’t find an answer (including MOS). The issue was that the data was exported from an 11.2.0.2 database and I was trying to import into 11.2.0.1. I don’t really expect that to work flawlessly, but a better error would have been nice. Patched my local DB up to 11.2.0.2 and everything worked great. Hopefully google will lead someone here and save them some time…