This was the presentation I gave at Open World 2007. It went pretty well, judging by how full the room was. I estimate more than 300 people attended.

Although the title may sound a bit like a sales pitch, the content is more substantial. It covers the features that are ready to use out-of-the-box, things that will make your life easier from day one, without any kind of “implementation” procedure. One-line changes do not count as implementation.

I was please to recognize a few of Pythian’s clients in the crowd. They came to see me after the session to say how much they liked it. Judging by how lively the audience was, especially in the wake of the big conference party, I would say most people enjoyed it.

I saw a few people take pictures of my slides, and had some ask me for the slides. As promised, here they are: 11g: The Perfection of a Masterpiece.

I will next be going to the UK Oracle User Group Conference in December giving my presentation on memory. I look forward to seeing you there.

Just a quick note to say I’m leaving today for San Francisco to attend Oracle OpenWorld. I’ll be making my presentation on Thursday at 14:30, in Room 304. Look for IOUG: Oracle Database 11g –The Perfection of a Masterpiece (Session ID: S291070).

I’ll be posting more about my OOW adventures here, so please stay tuned.

I hope to see you there!

–Christo

Thinking I had something new, I wrote this article about recovering deleted files. However, it turns out Frits Hoogland had already blogged about recovery of deleted files on linux, as Frits pointed out in a comment on my blog, where he also mentioned a metalink note on this matter.

The note ID is: 444749.1 “Retrieve deleted files on Unix / Linux using File Descriptors”. I went and looked into it and the procedure it describes.

Although it does explain how to recover the deleted file, this procedure will leave the database in an inconsistent state. It will corrupt your database. Queries will produce the wrong results randomly, depending on cache usage, how busy the database is, et cetera.

(Before you read the details, I would like to point out that this metalink note is not fully reviewed, as it states in the very beginning of the note:)

“This document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.”

(more…)

So you have accidentally removed a datafile from your production database? First thing, DON’T PANIC! There’s an easy way to recover deleted datafiles, for as long as your database remains up. The procedure below works on linux, however this method conceivably can work for other platforms.

This procedure will even work if your database is in NOARCHIVELOG mode.

You may have reached this posting through Google, and in a rush to get the solution right away, so here it is.

The recovery is done in two phases.

Phase 1: instant recovery to prevent Oracle errors (like ORA-27041 “unable to open file”, ORA-01110, ORA-01116)

1. Find the PID of DBWRITER for the right database.

   ps -ef | grep dbw0_SID_OF_YOUR_DB
   oracle   12272     1  0 10:55 ?        00:00:00 ora_dbw0_test
   oracle   12941 11501  0 12:36 pts/0    00:00:00 grep dbw0_test
   

2. List the deleted file handles for that DBWRITER process.

   ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
   
   lrwx------  1 oracle oinstall 64 Oct 15 11:24 10 -> /home/oracle/product/10.2.0.2/dbs/lkinsttest (deleted)
   lrwx------  1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
    

3. Create a symbolic link to your datafile with the original name.

   ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

   That’s all. Now you are no longer going to get errors. However, if your database goes down now, you will lose that datafile for good.

Phase 2: restore the file
(more…)

I am finally back from New York City, where I attended the official Oracle 11g launch.

The question that everyone is asking is, “When can I download it?”. The Linux release will be probably available towards the end of August, according to Oracle president Charles Phillips’s opening speech. 11g for other systems should arrive this quarter.

Now that availability of the download is out of the way, let’s focus on what 11g is all about.

The main message I got is that Oracle 11g is the consumer release. According to Mr. Phillips, what this means is that Oracle has listened to its clients and has worked in the areas that the consumers needed the most. The fact that Ari Kaplan, the president of the IOUG, was on stage during the launch speaks for itself. I see this as a major change in how Oracle is developing its database product, another proof that Oracle is serious about getting involved and listening to its user community.

As for what’s new in 11g, three major features come to mind: (more…)

Or, Why is My Transaction So Big?

My team and I still use old-style rollback segments for one of my client’s 10g production databases. We just never found the need to switch to automatic undo management. There are a number of 1GB rollback segments. They are that size because they need to be able to support large transactions. At the same time, we don’t want to have transactions bigger than 1GB as this is an OLTP system.

For the past few weeks we’ve had a strange problem. One of the web calls would cause one of the rollback segments to become full by using 1GB of undo data. Eventually the session would do a rollback, but we couldn’t track just what was filling up the transaction.

We could have installed logminer and analyzed the redo logs, but I had a better idea that required less time.
(more…)

I have been asked by a client to try out Oracle 10g’s transparent encryption. I’ve created the wallet, set it up into the sqlnet.ora file, and opened it. I initialized the certificate, created a test table, and encrypted a column with it.

Maybe you’ve done the same, and thought, “now my data is safe and encrypted”.

Are you sure? Did you check?

I did. And here’s what I found.
(more…)

Have you ever been in a room full of people that cheer every time they get an ora-600? Have you ever been in a room where you would say, “And now let’s corrupt that disk and see what happens,” and people would gather around you?

Let me tell you the story of the on-site beta testing of Oracle 11g, where success is measured by the number of times you caused Oracle to misbehave. And let me tell you, it’s very hard to do so, even in the beta. It takes a specific mindset and a bit of adjustment to get in the mood to make things not work.

I am very happy that Oracle decided to organize these beta events, and I am honoured to be part of the testing team. In my day-to-day work, I always try to make things work. I always look for stability using proven technology. But during the Oracle on-site beta testing sessions, I tried the newest technology, fresh out of the oven. I looked at slides describing something awesome, then I got to try it. Right away, on the spot.

I had the people who created it behind my back watching me testing it. Sometimes I felt like a lab mouse being shown a new brand of cheese, with each bite being observed. And then, if I managed to get an error message, the creators were all interested in how I broke it. The opposite of my day-to-day life.

Oracle 11g is a significant release, not so much for the new features added, but more for the perfection of the existing ones. Work has been done to improve almost every aspect of how the database works.

You’ve seen what I have to say about Enterprise Manager. I used to dislike it, because it didn’t offer anything extraordinary. In Oracle 10g, it was borderline. In Oracle 11g, Enterprise Manager has become a key component.

I’ve been quiet for the last few months, as things have been pretty busy here at Pythian. Now I have some new resources, so I will have more time for blogging. 11g will get the attention it deserves.

This year, there are three of us going at Collaborate 07. All three of us will be presenting from the IOUG side.

And we are:

Looking forward to seeing you all there!

Allow me to guess the sequence of events that brought you to this blog, possibly via google.

The given interface(s), "eth0" is not public. Public interfaces should be used to configure virtual IPs.

srvctl add nodeapps -n crmnode1 -o /ora/ora10 -A 1.2.3.4/255.255.255.0

srvctl start nodeapps -n node1

/opt/oracle> srvctl start nodeapps -n node1
node1:ora.node1.vip:Invalid parameters, or failed to bring up VIP (host=node1)
node1:ora.node1.vip:Invalid parameters, or failed to bring up VIP (host=node1)
CRS-1006: No more members to consider
CRS-0215: Could not start resource 'ora.node1.vip'.
CRS-0210: Could not find resource ora.node1.LISTENER_NODE1.lsnr

Well look no more, and here’s the solution.

When you issued the add nodeapps command, you did not include the interface name. It’s ok, it’s not your fault, it’s how the example was formated in these 2 notes and the srvctl documentation as well.

I know you want to fix this right now, but please read the whole blog before running any commands:

srvctl modify nodeapps -n node1 -A 1.2.3.4/255.255.255.0/eth0

Dont forget the other nodes, just replace the node number (can all be done on 1 node). The srvctl documentation actually shows that there’s an interface parameter, just very casually and only in the Options section. Extract:

-n node_name
 Node name.

-o oracle_home
 Oracle home for the cluster database.

-A addr_str
 The node level VIP address (name|ip/netmask[/if1[|if2|…]]).

Then bring up the nodeapps and check status:

srvctl start nodeapps -n node1
crs_stat -t

That’s it. Before running those though, you may want to make sure this is the problem you are experiencing.

Run this (replace node1):

crs_stat -p ora.node1.vip|grep USR_ORA_IF

If it shows empty (”USR_ORA_IF=”) then it’s most likely this is your problem.

I hope you dont encounter any other issues with your RAC installation. But if you do, and nothing in Metalink or Google helps you figure it out, I’d love to help.