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

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

I’m sure that this topic will be irrelevant to most people. Usually, when your organization needs you to develop a new application they say to take as long as you want. Rarely do we actually have timelines or due dates. But I guess there might be one or two people out there who need to build and deploy a basic app quickly. I’m sure you’re already familiar with RAD (Rapid Application Development) philosophies and you’ve already examined the usual CASE (Computer Aided Software Engineering) tools. You probably already know that typically it’s possible to use these tools to quickly slam together a workable app by sacrificing some scalability and features.

Actually this website was a little bit like that when I put it together. I didn’t have a lot of time but wanted to get something up quickly. In fact I’m quite happy with how it turned out and I took a few notes about lessons that I learned in the process. And as I’ve been thinking about these things I’ve started thinking that these probably apply to general RAD too.
› Read more

Came up with this demo for a class last week and I think that it’s useful.

The demo illustrates one of the most important foundational concepts in an Oracle database: how Oracle provides isolation and consistency. For my favorite technical illustration of undo and redo check out slide #22 of the Redo Internals presentation on Julian Dyke’s website. (A picture is worth… you know!) For a very good description of isolation and consistency read Tom Kyte’s column in Oracle Magazine from Nov 2005.

The database engine must guarantee that the results of your queries are consistent. (The “C” in the “ACID” transactional database model.) Some SQL statements take a long time to run. It is also possible to put a transaction into a read-only or serializable mode. In both of these situations, regardless of how many transaction commit and how much data changes, your statement or transaction must always see the database as if everything happened at the very instant you started it. You need to ignore committed data. If oracle is constantly changing the data in tables and committing this data then how can your statement see the old data? The key is undo (or rollback segments) and multiversioning.
› Read more

Aris, my roommate, just now left for work. He’s working a full day even though it’s a Saturday. He just reminded me that this is another reason he’s not paid enough…

Here’s a snippet of the conversation we had right before he walked out the door:

Jeremy: Why are you renaming your windows domain? That’s a lot of work, and complicated… reconfiguring all the clients and servers…

Aris: The thing is, our domain is messed up: your domain is supposed to match an internet domain that exists and that you own. Our domain is set to thecompany.net which doesn’t exist; so we need to change it to thecompany.com.

Jeremy: Why don’t you just buy thecompany.net?

Aris: Um… because… *no answer*

Jeremy: Because we’re not decision-makers and we just do what we’re told… I would do the same thing I guess.

Aris: This is why we should start our own company. To do things that make sense. I don’t think that’s so much to ask.

Dovetails nicely into a conversation I had with my good friend Joel over lunch yesterday. He is the a lead software engineer for a UPS company and is the technical lead on a major project to provide a complete power management solution for a new airport that’s being built. Sadly, due to the organization of his company, somehow the regional sales force of the parent company has authority to make technical and architectural decisions. That’s just asking for trouble.

Apparently the client originally wanted “redundant servers”… so when he flew out there and did the initial implementation he setup two servers in parallel. He also added a little code into the software itself so that the servers were aware of each other and kept their configurations in sync. Now it turns out that they expected “clustering” which to them apparently means a single IP address. Now “clustering” is a sexy word that makes you sound very sophisticated and technologically savvy. However the sales team apparently doesn’t quite understand how “active-passive” is a step down from two servers essentially running “active-active”. *sigh*

Funny how working in the field of technology affords so many opportunities to pour your best into doing something that doesn’t make sense at all. :) ‘Tis the pedestrian and mundane reality of life.

Well back to class prep now… I’m rewriting a DBA class for a customer in Las Vegas so that I can teach their developers a bit about Oracle. Hopefully I can teach these guys to do a few things that DO make sense! (And hopefully I can finish up soon because I plan to go listen to some awesome live Chicago blues and do a little swing dancing tonight!)