This is the 124^th edition of Log Buffer, the weekly review of database blogs. Welcome.

Here’s what the Oracle blogosphere had to offer. On Oracle and more, Amardeep Sidhu announces the birth of the North India Oracle Users Group. Not too far away, on the AskDba.org Weblog gets the word out about a seminar by Tom Kyte on 18th December in Bangalore, arranged by the All India Oracle User Group.

Vivek Sharma examined Library Cache Latch Contention and Scalability to answer the question, “Why is Library Cache Latch Contention referred to as a ‘Severe Scalability Issue’?”

Marco Gralike discusses the value of setting an “impossible” password for SYS — even when you can use LOCK.

Greg Rahn of Structured Data explained the use of a preprocessor for external tables. He writes, “Before External Tables existed in the Oracle database, loading from flat files was done via SQL*Loader. One option that some used was to have a compressed text file and load it with SQL*Loader via a named pipe. This allowed one not to have to extract the file, which could be several times the size of the compressed file. As of 11.1.0.7, a similar feature is now available for External Tables . . . ”

On Oracle Corp’s Oracle Certification blog, Paul Sorensen debuts a new booklet called “Performing an Oracle DBA 1.0 to DBA 2.0 Upgrade”: “The knowledge required by the typical Oracle DBA has increased, requiring additional skills and in some cases job responsibilities. Oracle Certified Masters Joel Goodman and Harald van Breederode discuss this shift in DBA skills in this 10-page PDF booklet . . . ”

(more…)

Hello, there! With another Ubuntu release, it has come the time to update our series of posts on how to install Oracle 11g on Ubuntu. If you’ve been following, we’ve been publishing updated howtos since Ubuntu 7.04:

• Installing Oracle 11g on Ubuntu 8.04 Hardy Heron LTS
• Installing Oracle 11g on Ubuntu 7.10 Gutsy Gibbon
• Installing Oracle 11g on Ubuntu 7.04 Feisty Fawn

In fact, in this article I refer several times to previous posts regarding some configuration aspects and why I chose particular values. Also, note that this series of posts is a work in progress and we were able to improve this series with your help. So please do post comment below as your collaboration is very much appreciated.

In this post, we’ll see the steps needed to install Oracle 11gR1 on an Ubuntu 8.10 Intrepid Ibex box all the way to creating your very first database. I’ve been working very hard to ensure that at every new post, the results you get when executing this procedure are as deterministic as possible, leading to a successful setup.

Please keep in mind that this is not a supported architecture, so pay special attention to the order in which I do things. Don’t rush and try to merge steps, as it took me a lot of attempts to make this setup work properly. There are some reboots and also there’s a specific order you need to do things, so please follow the instructions step-by-step and verify the the results of every single command.

Preparation

Let’s get down to it, shall we? The first thing to do is to get some files. We need an ISO image of Ubuntu (a CD/DVD will do) and one of Oracle 11gR1. Get Ubuntu 8.10 Server here; and Oracle 11gR1 here. (It’s free, but you have to register on the Oracle website to download it.)

It’s a good idea to check the md5sum of each image after downloading from the Internet.

(more…)

This is the 123^rd edition of Log Buffer, the weekly review of database blogs. Welcome.

Let’s begin with some SQL Server, where it was a week of technical tips. Alexander Kuznetsov looks at defensive database programming. “In most cases LIKE conditions should by followed by ESCAPE clauses,” he asserts, continuing, “You have a choice: you can either have a CHECK constraint disallow special characters, or you can fix the procedure,” thus error-proofing your logic. Readers Alejandro Mesa and Adam Machanic suggest a couple refinements.

On In Recovery, Paul S. Randal shows how to move constraint indexes, taking as his cue a question he heard at SQL Connections Fall in Las Vegas: “Can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints?” Short answer: yes. “This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX.”

Piotr Rodak offers his example of the use of a numbers table, which “ . . . [implements] string split functionality for parameters passed as CSV arrays.” He asks for more examples, and Adam pops up there, too.

Stephen Forte has a method for using a TSQL Common Table Expression to find dupe records, which he was discussing at this week’s Tech Ed Europe. Nifty.

Speaking of duplicates. Mark, of Ajarn’s SQL Corner, wants us to know why he hates DISTINCT: (more…)

Recently we quickly and efficiently resolved a major performance issue with one of our New York clients. In this blog, I will discuss about this performance issue and its solution.

Problem statement

The client’s central database was intermittently freezing because of high CPU usage, and their business severely affected. They had already worked with vendor support and the problem was still unresolved.

Symptoms

Intermittent High Kernel mode CPU usage was the symptom. The server hardware was 4 dual-core CPUs, hyperthreading enabled, with 20GB of RAM, running a Red Hat Linux OS with a 2.6 kernel.

During this database freeze, all CPUs were using kernel mode and the database was almost unusable. Even log-ins and simple SQL such as SELECT * from DUAL; took a few seconds to complete. A review of the AWR report did not help much, as expected, since the problem was outside the database.

Analyzing the situation, collecting system activity reporter (sar) data, we could see that at 08:32 and then at 8:40, CPU usage in kernel mode was almost at 70%. It is also interesting to note that, SADC (sar data collection) also suffered from this CPU spike, since SAR collection at 8:30 completed two minutes later at 8:32, as shown below.

A similar issue repeated at 10:50AM: (more…)

Welcome to the 122^nd edition of Log Buffer, the weekly review of database blogs.

Let’s start with MySQL. As you probably know, the Americans were at the polls this week. In keeping with this, Keith Murphy is polling for opinion on the question: MySQL on Debian or Ubuntu? The conversation is here; the poll is on Keith’s Diamond Notes.

A quick note by Sun CEO Jonathan Schwartz on how, with the outcome of those other polls, change has come to America, in the form of its new president and his choice of DBMS. Okay, it’s probably not his choice, but the association must be appealing.

Duleepa “Dups” Wijayawardhana also has a big new job. He’s taking over from Jay Pipes as MYSQL Community Manager, and his advice to himself is, Don’t Panic!. As a compatriot of Dups, I say: congratulations, eh!

Can we create a cross tab in MySQL? Yes we can! Arnold Daniels shows us how. Sheeri and Corey show some more.

On so many trails … so little time, Pabloj elucidates new syntax (the new WITH ROLLUP) for the old challenge of adding a “Total” row at the end of your tabular output.

Brian Aker was barking up a nearby tree . . .  or a nearby column, at any rate. Here’s his item on column stores in Drizzle. He writes, “I believe the second most important decision we will make long term for engines is going to be which column store we pick up on. I suspect we might even need two.” But which two? (more…)

This week gives me a chance to get back into something I love to do—write. For those who don’t know, my name is Keith Murphy and I am a MySQL DBA at the Pythian Group. In addition, I have the privilege of being the editor of the MySQL Magazine, a quarterly  magazine for those who use MySQL on a daily basis, either as a DBA or a developer. The sixth issue was just released last week and is available for download now. But enough about me! Let’s see what you all had to say this week.

Beginning with the world of MySQL.

Monty Taylor kicks things off, bringing us news of the ability to use the innodb plugin with Drizzle. I just think its great how the Drizzle development is moving along so rapidly. My thanks to everyone involved.  Monty also puts out a call to help defray the cost of the purchase of the drizzle.org domain name.

There have been several posts this week on virtualization of MySQL Server. I point you to a colleague at Pythian, Sheeri Cabral, who wrote about some of the benefits our customers have already experienced while using virtualized servers. While virtualized servers are not a panacea, they definitely have a place in the DBA’s environment. If you don’t work with any currently, you will be doing so down the road.

The OpenSQL “unconference” is coming! The date is Nov 14 -16 in Charlottesville, Virginia, USA. Details and registration information are available here. Speaking of conferences, the MySQL Users Conference, which is in April in Santa Clara, CA, USA has extended its deadline for topic proposals.

Baron has a great post on naming conventions for your schema.

In the how-to department, Falko Timme has a good tutorial on how to set up GreenSQL to protect your databases from SQL Injection attacks. Finally, Johan Andersson has a good introductory blog post on optimizing queries for a NDB cluster, and a post on how you can get the Cluster Sandbox tool for testing the MySQL Cluster.

The Oracle world had sad news this week. Carl Backstrom, who was an Oracle employee and APEX developer, was killed early Sunday morning in a car accident in Nevada. (more…)

Recently, I resolved a performance issue with one of our esteemed clients. I found the problem interesting and worth blogging about.

The problem

An application makes 300 static connections to database DB1 in the database server, say CLNTDB server. The application relies on database link and over a period of time, each session executes SQL through a database link, creating a connection in central database PROD1. So, there are 300 connections in the PROD1 database coming from the DB1 database through database links. Performance is fine during normal operation.

The problem starts when the application is shutdown. Shutting down the application in DB1 creates massive CPU consumption on the PROD1DB server. Unfortunately, this spike in CPU usage lasts for five to ten seconds and causes the ASM heartbeat to fail. Considering that PROD1 is a central database, this has a global effect on applications’ functionality. See the presentation below for graphical representation of this problem.

The symptoms

Looking at the symptoms in detail, we can see the CPU usage in sys mode. It is not uncommon to see high CPU usage during a storm of disconnects. But, this specific CPU usage is much higher and uses all CPUs in kernel-mode. If this is a problem due to process destruction, then this will show up on the CLNTDB server too, as it too faces 300 disconnects. But, this problem manifests only on the PROD1DB server.

mpstat output from the PROD1DB server shows the CPU usage in %sys mode. Notice the numbers below, under sys column—almost all CPUs are used in sys mode. We need to drill down further to understand why this many CPUs are used in sys mode. It can also be seen that no other columns have any abnormally higher values:

(more…)

This 9^th post will describe how to remove a node from a 11.1 RAC cluster in silent mode. It will differ from the associated documentation in that it will allow you to remove the node even if it is not available anymore. This procedure has only a few differences from the 10.2 one in the 6^th post. For a complete series agenda, see below:

1. Installation of 10.2 And 11.1 Databases
2. Patches of 10.2 And 11.1 databases
3. Cloning Software and databases
4. Install a 10.2 RAC Database
5. Add a Node to a 10.2 RAC database
6. Remove a Node from a 10.2 RAC database
7. Install a 11.1 RAC Database
8. Add a Node to a 11.1 RAC database
9. Remove a Node from a 11.1 RAC database (this post!)
10. A ton of other stuff you should know

In what follows, we’ll remove rac-server5 from the configuration. We assume that rac-server5 is not accessible anymore. However, to cover the case when the node is still available, I’ll mark specific sections with a (*), meaning that the step should be done only if you still have access to that node; it can just be ignored otherwise.

(more…)

Previously on Log Buffer: Log Buffer #119.

And now.

Welcome to Log Buffer #120. My name is Warner, and I’m a SQL Server DBA at The Pythian Group. This is my first time on Log Buffer duties ever, so here’s hoping I can give everyone a fair and unbiased look at this week in the database blogging world (and related).

I admit I had no idea of the community or state of the PostgreSQL RDBMS, and so I definitely learned some new stuff this week. First off, over on “The Scale-out Blog” Robert Hodges invites us all to get our shoephone and get smart about the new world of PostgreSQL replication.

Moving over to “ad’s corner”, Andreas Scherbaum gives us a glimpse of the glitz and glam of PGDay opening. Then he entices us with the title, “Party in the evening” just to horrify us by revealing that—you’d better sit down—by the end of the event half of the beer was still untouched. Next time Andreas, who you gonna call?

“Everything is a DNS problem,” is my new voicemail message, and also the title for Kris Buytaert’s blog, where we go deep into uncovering once again the 10 month-old enigma of why did Sun buy MySQL.

Next up, I will speak to you, Mr. (or Ms.) Cross-Platform DBA. You think you know all of Oracle’s exp command-line switches? All of SQL Server’s bcp formats? And all of the things you can do with . . .  whatever you use with DB2? (more…)

Welcome to the 119^th edition of Log Buffer, the weekly review of database blogs.

We start in the MySQL world with some engine news. On Brian “Krow” Aker’s Idle Thoughts, Brian explains the state of engines in Drizzle, the pared-down MySQL. He begins, “So many engines, and so little to choose from. This is one of our two major decision points in Drizzle right now.” Maria, Falcon, PBXT, and InnoDB are in the dock.

Arjen Lentz asks a simple question: Would you prefer InnoDB to be the default storage engine?, also the subject of a quick poll he created.

While we’re on the subject of defaults, Giuseppe Maxia, the Data Charmer reports on STATEMENT-based replication as the default in MySQL 5.1: “MySQL 5.1.29, the next (and last) release candidate, will revert the default replication mode to STATEMENT based  . . . MIXED mode won’t be the default anymore.” The commenters are not totally happy with that.

Well, one of the good things about MySQL is that, if you’re not pleased with it as it comes, you can soup it up yourself. (more…)