Well the pic doesn’t show it well but there was a great turnout at this workshop with Mike and Nina in Michigan. We had a crew of six people go from Chicago and a seventh joined us to stay at my folks’ house. The weather was great, the music was fantastic and I think that we all had an awesome weekend! Plus I finally nailed that airstep I was working on with Amie. RJ took a video of it; if I can get a copy of that video then I’ll post it…

For the past month I’ve been intending to put together a post about DBMS_SERVICES and TAF - so I guess it’s about time to do it. I introduced the topic back in February in my discussion of 10g enhancements to client-side TAF configuration. As I pointed out then, there are two ways to configure TAF in 10g: client-side with TNSNAMES and server-side with DBMS_SERVICES.

The big advantage of centralized configuration is manageability. In 10g, even a TNSNAMES approach can be a little more centralized through the use of tools like DBCA and srvctl. (That’s what the other post was about.) But Oracle 10g introduced a completely new feature which pretty much rethinks the whole paradigm: as part of 10g’s expanded services framework you can now define TAF settings as an attribute of services instead of defining them in the TNSNAMES file.

However there are limitations to this approach. Before diving into specifics it’s worth noting a few of them:
Read more

Just stumbled onto an interesting situation with TAF. Turns out that you can get an ORA-08176 error (consistent read failure; rollback data not available) from a SELECT statement that fails over with Transparent Application Failover in a RAC configuration. And I seem to have found a situation where adding a NOT NULL constraint to a query triggers this error. What in the world could cause t`his to happen?

Let’s reproduce the error and see if you can guess.

Making it Happen Again

We’ll start by making a table with lots of rows so that we can get a long-running query going. I’m also going to stuff some data in there to make sure that this table uses a good number of blocks and a full scan requires a little I/O.
Read more

Heh - just ran into an all-too-familiar “duuh” situation. I was launching DBCA to update a few settings on some services in a test database and it was taking …f…o…r…e…v…e…r… to run.

It must have spent a minute on this partially updated screen. Now I haven’t launched DBCA in a week or so on this machine… and it is a vmware-based test cluster so it’s a bit slow anyway… but this just seemed excessive.

So I jumped into another window and typed “ps” to see what running. Lo and behold, here’s what I see:
Read more

You would think that eventually I would learn that sleep is important. But Saturday night we had a birthday party for Mandie and Heather over at Joel’s place that went from 8 until about 1am. Then when I came home Jeremiah and Brendon were having a party so I just went over there for another few hours. So I didn’t get to bed until about 3:30am. Oops. :)

Aris and I (well really Aris, not me) decided Wednesday to have a party last night. So after working rather late on finishing my article about stored outlines (it’s in the other section of my website) I got home around 11pm and the party was still going strong. I finally went to bed around 2am but all these guys were still hanging out.

Also, I have confirmed what I suspected to be true: I don’t have much of an accent.

Tuesday I wrote about a client in Houston and removing the RULE hint from queries as part of their 10g migration. This led into a discussion about the four ways to control SQL statement execution: rewriting the SQL, hints, stored outlines and SQL profiles. I then discussed the first two methods - rewriting SQL and using hints - and gave examples of both.

However these first two methods rest on the assumption that you are able to control the SQL statements. If you are using a third-party application where you do not have control over the SQL statements that are generated then these methods will not work. But fortunately Oracle introduced a feature almost 10 years ago in 8i release 1 (8.1.5) that can address this dilemma: stored outlines. Stored outlines are an intriguing feature of Oracle that seem to be rather underutilized by the Oracle community in general. They’re almost a throwback to the pre-oracle days of compiled queries in the sense that you can specify and hardcode exactly how you want a query to be executed.

Let’s have a closer look.
Read more

I was just thinking today about how useful it might be to have a quick reference to permissions on Oracle RAC databases. Can’t tell you how many time I’ve asked “what should the permissions be for this”… so I’m just going to use this post for all the files I can think of and their default or recommended permissions. I’ll probably update this post as I think of other files and directories that I left out.

Often it is possible to restrict access to files and directories further than Oracle does by default and the database will still function fine. I am aiming to list the defaults or recommended values but would welcome any comments about more secure possibilities.
Read more

Had an interesting question come up yesterday. Was on the phone with a colleague who’s configuring TSM backups on a 3-node cluster that he’s just setup for a client in Miami. He wanted to parallelize the backup job across the cluster and was configuring the channels in RMAN. They were not using the sys account for backups so they had to create another account with SYSDBA privileges.

Although it is administered with the same GRANT and REVOKE syntax as other roles, SYSDBA is not a normal role. When you grant the SYSDBA role to a user their password is added to a special O.S. file called the password file - not stored in the data dictionary like other roles. On unix platforms this password file file must reside in the $ORACLE_HOME/dbs and must be named orapw[SID]. On windows it resides in %ORACLE_HOME%\database and must be called PWD[SID].ora. It is created with the orapwd command-line utility.

When creating the user account for RMAN backups this is the problem they encountered:
Read more

Last week I was in Houston teaching a class on 10g New Features and Performance Tuning for a group of developers at a fairly large organization downtown. I try to make my classes as interactive as possible and we had a number of interesting discussions about topics such as appropriate uses for bitmap join indexes, IOT’s, and cluster tables in this organization’s specific applications.

But there was one topic that kept coming up throughout the week. In the past, when experiencing problems with query performance they’d discovered that the RULE hint seemed to make queries run faster. So they now have a large number of queries that use the RULE hint… and they’re afraid to remove the hint for fear that their queries will start to bomb. But since this hint is officially desupported in 10g (although it still works) they are concerned about what to do when they upgrade.

Now you can in fact just leave the “RULE” hint in your queries. Oracle 10g will not ignore the hint and your queries will work but they will not use new features in Oracle such as bitmap indexes and Oracle Support will no longer help you troubleshoot them. So how do you control the way these SQL statements are executed? There are four methods for controlling SQL execution in Oracle 10g: rewriting the SQL, hints, stored outlines and SQL profiles.
Read more