This post is the first of a series of ten posts that will explore some of the Oracle Universal Installer (OUI), Network Assistant (NETCA), Database Creation Assistant (DBCA), Database Upgrade Assistant (DBUA), and many more syntaxes you can use to script or speed up Oracle Installations. The agenda should follow the one below:

1. Installation Of 10.2 And 11.1 Databases (this post!)
2. Patching 10.2 And 11.1 Databases
3. Cloning Software and Databases
4. Installing 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
10. A ton of other stuff you should know

Actually, I may have to split the post #10 into ten more posts to cover all the other syntaxes you could use with Oracle Enterprise Manager, Application Server, or on Windows. Anyway, for now let’s focus on the very beginning: how to install 10.2 or 11.1 non-RAC database; how to apply the latest patch set; and how to create a instance database from a template.

Foreword

First, just because it’s on the Internet doesn’t make it true — even if it’s on the Pythian Blog. There will be a lot of syntaxes in those ten posts and even if they’ve been all tested, (1) the testing conditions are probably very different from your environment, and (2) the commands have been customized so that they appear generic. Be careful; it’s very likely that the syntaxes will be wrong for you. Test them yourself on a test environment and don’t execute them if you don’t understand what every part of them is supposed to do.

One of the reasons for these posts is that it’s kind of difficult to figure out by yourself how you should run one particular tool. The information is spread across the reference manuals, the response files, the online help, and sometimes Oracle Metalink or people that managed to make it work.

In addition, if the syntax looks similar for all the tools, they differ more than we can guess first. Let’s take some examples to illustrate that and to begin with the syntaxes:

(more…)

RDA 4.11 is out, with a couple of new features. “Oracle Database Diagnostics Collector” (ORADDC) is one of those. It allows you to easily activate all kinds of traces, dumps, or stack collections.

Once RDA 4.11 installed, run ./rda.pl -T oraddc to collect whatever you need. The tool will guide you. To learn more about its features, you can display its man page: run ./rda.pl -M oraddc or ./rda.pl -M.

This may become one of the most used RDA modules for Oracle Support Services and Oracle database administrators stuck in different situations. For now, let’s start with a more basic question: “How to leverage RDA in a RAC environment ?”

About RDA

RDA (Remote Diagnostic Agent) is one of the primary tools used by Oracle Support Services to diagnose any problem related to the Oracle stacks (from the Virtual Machine to the CRM). You can leverage this tool for your own needs and to speed up your problem resolution.

Using RDA before you install the Clusterware

Installing the Oracle Clusterware is an error-prone process, and in most cases, the errors come from unmet prerequisites, Fortunately, the CVU (Cluster Verification Utility) helps diagnose any problem in the initial setup you’ve performed. The best way to use it is probably to download the latest version and to validate the prerequisites according to the release you want to install. Once you’ve downloaded and unzipped the latest release from OTN, you can for example check if the prerequisites are met before you install Clusterware, with a command like the one below: (more…)

As you already know, I’ve spent a whole week near Redwood, California. Saying that the week has been difficult to manage is a euphemism:

• My heater engine broke at home (I didn’t even know it had an engine!), almost leaving my children in the cold for a night. Thank you, people have been so helpful that day!
• The snow storm in Canada prevented my plane from taking off from Chicago to Ottawa Saturday afternoon.
• The same snow storm prevented even the bus from leaving Boston after I decided this could have been the best, and only way to go, and found a plane to go there.
• And many more bad stories I don’t want to talk about . . .

However, the week has been more than useful. I have worked very hard, and had some very good fun too. As always, I’m amazed what people are able to do with Oracle technologies. We’ve learned a lot from the good and bad experiences of several Oracle customers. Unfortunately, there is not a lot I can tell you.

Here is something I can share anyway: one of the things I’ve liked the most is to spend some time (not enough!) with Kuassi Mensah. The guy is awesome! As a Product Manager at Oracle, he knows probably everything about JServer (the JVM in Oracle 11g), and he is one of the best guys on the subject of some of the key connection layer to access an Oracle database, including JDBC, OCI, and Web Services.

If you’re interested by any of those subjects, you should subscribe to his 360° blog and read his book. If you’re lucky and can attend the Conférence PHP Québec in Montréal this week, rush to his presentations, especially the one about the Database Resident Connection Pool.

Hey Kuassi! I hope the weather will be nice in Canada now that I’m back home. However, you may have to rename your blog “36°” for the end of the week — which is not so bad in this winter.

Like Alex G., I’ve left Ottawa and its wonderful snowy landscape this morning for, let’s say, a sunnier place.

But that’s all we have in common! I know the guy told you he is feeling some stress about his presentation but honestly, I doubt it! He has been preparing it for so long, he knows the subject so well, and he is so smart — just like so many people at Pythian, which is why I like it here so much. And to tell you the truth, I wouldn’t like to be at the Hotsos Symposium this year and have to choose between Alex and Cary Millsap.

So Alex, while you’ll be off socializing (and probably drinking more beers than you should), I will be working very hard, as always, this time with a couple of Oracle product managers.

The good news is everything went perfectly in Ottawa, Chicago, and here in San Francisco. I did not lose anything, and everything has just been on-time. The not-so-good — my hotel in Belmont seems about 800 miles away from Oracle Headquarters in Redwood City. I snapped this photo as I flew by on the highway:

When I arrived there, I had a couple of emails in my inbox; I should be able to meet the people I want to (except for Chuck and Larry, who are obviously too busy).

What I’ll be doing in the next five days is kind of a secret. Unfortunately, that’s all I’m allowed to tell you, except maybe that I know many people who would love to be in my shoes this week.

ASM is definitely one of the coolest technologies inside the Oracle Database. On the other hand, the ability of the storage arrays to provide a read/write access to a copy or a “snapshot” of its content is something we can easily leverage as an Oracle DBA. For a couple of weeks, I wanted to copy a database stored in an ASM Disk Group with one of those storage technologies and mount it on the same server; unfortunately, this is not supported even with 11.1.0.6. The good news is that I finally overcame all the obstacles to do it in a specific case. This post relates a couple of the tips I used to get to that result.

What You May Want to Know Before You Start

Several people helped me, even if most of them don’t know. Luca Canali did an awesome presentation about ASM at UKOUG. Alexandro Vargas’s Blog is a must-have in the list of your RSS feed if you work with ASM or RAC. Last but not least, Alex Fatkulin rocks when you have a problem with Oracle. I’m very lucky to work 10 metres away from him and other amazing folks at Pythian.

N.B.: What I describe below is among the worst things you can ever do with ASM. You can use it to play around, but never use it with anything other than test data. If you lose something because of me, you’ll be the only one to blame!

I’ve tested the whole process with Oracle 11.1.0.6 on Ubuntu Gutsy Gibbon, based on a previous post I made on this blog. In order to copy the ASM disks, I’ve used the dd command, but it should not make any difference with a storage array command.

(more…)

Recreating the Voting disk may not be as easy as written in Metalink. If you work with RAC, you know about Metalink Note 399482.1 : “How to recreate OCR/Voting disk accidentally deleted”. Of course, you back up the voting disk every time you change your RAC configuration, or on a regular basis. You probably played with the procedure and it worked just fine. Like you, I did all of that.

Yesterday, I had to recreate this precious file when it was lost a couple of hours after the whole software stack had been installed. It was, I guess, just before we would have setup our monitoring on the server that would have backed up the voting disk. When recreating the voting disk, I was amazed that the root.sh on the second node failed with the message below:

Failed to upgrade Oracle Cluster Registry configuration

But, what amazed me even more is that, despite several issues showing up on my Metalink Search with the same error, I wasn’t able to find the fix. At least nobody wanted to share one with me. However, it might frequently occur that you haven’t all nodes installed together, and it’s very likely that some of you will run into this if you are asked to recreate the Voting disk file. It happens when you haven’t installed all the nodes with the first Clusterware runInstaller command, but instead installed only some of them and then added some nodes with the addNodes.sh script.

How You Can Tell This is Your Problem

(more…)

I’ve recently moved to Ubuntu Linux, and this post describes my attempt to play around with Oracle ASM on Ubuntu. For this demonstration, I used Oracle 11.1.0.6 on Gutsy Gibbon. I hope it will be useful to somebody out there.

Important Notice: What I describe below is among the worst thing you can ever do with ASM. You can use it to play around but never use it with anything other than test data. If you lose something because of me, you’ll be the only one to blame !

Question #1: How do you simulate a disk from a file?

(See here for more details.)

If you have a free partition or disk to be used as an ASM disk, just skip this step. If you don’t, you can create a file with the dd command and create a device that actually loops to the file with the losetup command.

Let’s assume you’ve created a directory named /asmdisks (and you have write access to it). Run the command below to create a file named disk1 that is 3GB in size:

$ dd if=/dev/zero of=/asmdisks/disk1 bs=1024k count=3072
3072+0 records in
3072+0 records out
3221225472 bytes (3.2 GB) copied, 80.9113 seconds, 39.8 MB/s

Once you’ve created the file, map it to a device named loopN in /dev. You can list the used loop devices with the losetup -a command.

Once you’ve made sure the one you plan to used is free, e.g. /dev/loop1, you can map the device to the file with the following commands (you have to be root) :

# losetup /dev/loop1 /asmdisks/disk1
# losetup -a
/dev/loop1: [0802]:7438407 (/asmdisks/disk1)

Question #2: How do you create an interface to the disk that is usable by ASM?

(more…)

I have been using this SQL*Plus command for a while now:

SQL> show spparameter service_names

SID      NAME          TYPE        VALUE
-------- ------------- ----------- ---------
*        service_names string      REDX

And guess what ?

• The right syntax is actually show spparameters (with a “s” at the end of it).
• It’s an 11g New Feature!

It doesn’t work with 10g:

SQL> show spparameters service_names

SP2-0735: unknown SHOW option beginning "spparamete..."
SP2-0735: unknown SHOW option beginning "service_na..."

I thought MySQL was trying to become as big as Oracle but it could be that Oracle is trying to become as cool as MySQL — it has had something like this for a long time! Now Oracle should add SHOW TABLES too.

Anyway, this is really 11g’s most useful feature so far, though.

At the risk of making it seem like this is all we’re talking about here at Pythian, here we go again.

Paul Vallee pointed me towards this article by John Dvorak that more or less echoes a blog post I wrote in French the day previous for my personal blog that you can read here: Le rachat de MySQL AB vu d’un paranoïaque; hopefully you read French and if not, maybe Google Translate will do a decent job; and if not well you can read Dvorak’s his is in English and he gets half the story!

So, first things first, Dvorak’s article leads me to think my theory is indeed wrong: if Dvorak says I’m right ; I’m afraid I’m wrong. (more…)

I’ve been told that using NOT EXISTS in (Oracle) SQL is a bad idea, and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN. So I decided to check if it is true.

In order to start, here is my test case:

create table t1(id number,
   constraint t1_pk primary key(id));

  create table t2(id number);

begin
  for i in 1..100 loop
    insert into t1 values(i);
  end loop;
  commit;
end;

begin
  for i in 1..100000 loop
    insert into t2
      values(mod(i,97));
  end loop;
  commit;
end;
/

create index t2_idx on t2(id);

exec dbms_stats.gather_table_stats(USER,'T1');

exec dbms_stats.gather_table_stats(USER,'T2');

First, I checked what I’d been told, i.e. that the OUTER JOIN is more efficient than the NOT EXISTS. In order to do that, I wrote a simple SELECT and displayed the plan for both syntaxes (my database is 11.1.0.6 on Linux 32-bits). As I assumed, it’s not the case. In fact, both orders took the same plan. (more…)