Manually add a host target to Grid Control

Case: I wanted to reinstall the Enterprise Manager agent on a host. I went on to delete the host in the list of targets in EM. Then after a new installation and synchronization I somehow managed to push the list of targets from GC to the agent and thereby removing the host as a target in the local targets.xml file. Result: The monitored host did never show up in the target list of hosts even after a successful installation of the agent.

Solution: Create a temporary file /tmp/newtarget with one line:


<Target TYPE="host" NAME="foo42.bar.com"/>


Where foo42.bar.com is the host name. Then add the target with:


emctl config agent addtarget -f /tmp/newtarget


AIX and large pages

I'll probably need this in the not-so-distant future. Here is the link to Noon's write-up when I need it. His blog is recommended, by the way.

Passed the OCP exam

Friday I passed the OCP exam (1Z0-043). In two months I have studied for, and taken three tests in order to achieve OCP certification. Before I started on this in January I had noe previous experience with Prometric testing or certification at all. But I knew I had read a post about it earlier and started looking up an old post by Chen Shapira about how she prepared for the OCA exam. I ordered the same book as well as another book for the test that was added as an extra requirement for the OCA level December last year. I more or less made the same experience that she did, in my words:

• Working experience with Oracle is very usefull. It helps you remember it all.
• These exam preparation books are not perfect, but the authors seem to know what is important and not. verifying against the Oracle documentation, other books, and your own experience is important, though.
• The cd-rom that come bundled with the book is not that usefull. In one of the question the answer is given, the questions seem mixed up between OCA and OCP, and overall did not impress with quality. But it managed to raise my stress level the night before the OCA exam and keeping me alert. Usefull if you've never taken a Prometric test before. But before the OCP test I skipped the test software in order to avoid confusion.
  
• Especially when reading for the OCA tests I had the feeling that I knew 95% of the stuff, but the questions in the book revealed to me which concepts that where not completely clear to me. Also one may read it too lightly and not capture important details for trick questions.
  
• Check the exam topics at the Oracle site (as of now: OCA topics and OCP topics) and compare it with the preparation book you are reading (if you do). For instance, globalization is not much of a topic for the OCA exam (except that you have to know about setting a few NLS-variables), but it is a topic for the OCP exam. In the book by Watson and Bersinic Globalization is covered in chapter 21 in the OCA part. The topic list also served as check list of what to study the last days before the exam when I had finished reading the preparation book.
• Getting used to the Prometric testing in itself is important. Pay attention to details in the questions and check the timer once in a while, if you spend too much time in the beginning you may not have enough time at the end. Now that OCA requires an extra test it may be the best opportunity to get used to these tests if you have never taken them before. Exam 051 11g SQL Fundamentals has only 65 questions to be answered in 120 minutes and a passing score of 60% (again, check oracle.com for changes).
  

One example of where statements in the preparation book does not agree with other books and documentation I've read can be found on page 812. The exam tip at the end of the page reads:

many people think that a log switch triggers a checkpoint. It doesn't. It used to, but that behaviour changed with release 8i, when incremental checkpointing was introduced...

However on page 100 in Tom Kyte's book "Expert Oracle Database Architecture" second-last paragraph:

Many things can cause a checkpoint to occur, the most common event being a redo log switch.

I'm inclined to believe that Kyte got it right. One is not allowed to report from these exams, but I think it is safe to say that the issue was somehow touched.

The main reason I went for certification was a requirement from at least one of our customers. Now that I'm through it all I am of course contend that I was pushed into it. The certification helps to identify gaps in your knowledge, gaps one may not be aware of even if you have worked with the database for years. One may question what is the use of knowing how to solve the same problem in five different tools.

Being an OCP does not equal being a good DBA, but it looks nice on the CV and quite a few of those requesting a certification do not know what it is all about.

New job

This year started off fine with a new job. I quit the company I joined just 25 months earlier, in itself a defeat. Though I gained much useful experience there, some signals convinced me to move on, and now I am quite happy that I did so. I joined Steria Norway to work as a senior system consultant, and so far it looks quite promising. The company is internationally focused and one is encouraged to build networks outside one's country, something I find interesting. There is constant pressure to improve; yesterday I finished the second exam in order to achieve my OCA certification (Last December Oracle introduced a second exam as a requirement for the OCA). And I won't stop there.

What else; two weeks ago I gave another thought about using Facebook; understanding that social networking on the net is important I concluded that I should give it another try, and reactivated the account after more than a year off. What matters is probably what one publishes there, and not being there or not. This post has been an exception to my rule; I should get back to writing about the database.

Side effect

A couple of months ago I had an incident on a live production system. The obvious error was to execute a DDL statement at 9 am that should have been performed during off-business hours. On the other hand I guess you cannot avoid any ad hoc change on a live system forever. That will require you to be aware of any problem and issue that may strike your system. Say you have an issue right now, users are complaining, or to put it bluntly, someone is losing money. You think you have the solution, but before you actually apply it you want to know if it will actually work or make matters worse. Most people will run to their test system, at least to check that the syntax is right. I had verified it long time ago and knew it would work. However, there is one problem with test systems. Though we strive to make them as similar to the original production system as possible, they never are. Even if you can afford the seemingly waste of hardware and space, something is likely to be missing in the test system. In my case it was the users... With Oracle 11g and Real Application Testing (which I have not tried yet) the load can be better mirrored in the test system, but I can't imagine it will behave as real users.

The mistake I did was to change a rather unimportant index that was supposed to speed up a query from one specific app. What I forgot in the rush was that we had other pl/sql packages that depended on the underlying table; at the moment the DDLs were executed several connections had exactly one failed transaction. The package state became invalid and the transactions failed with ORA-4061. Slightly embarrassing, and it reinforced three principles:

• Pick the right moment for any change.
• When someone is pressing on for an immediate change, take a break.
  
• There is no substitute for a peer review.

We have redundancy on every level, from mirrored disks to a standby database in another city. But no second DBA.

Connection Manager and ORA-12529

A simple configuration with Oracle Connection Manager (CMAN) did not work, I constantly received ORA-12529 when trying to connect with sqlplus to a database through CMAN. After checking cman.ora over and over and browsing all the trace files I finaly dived into one and found a problem with host name resolving. Turned out that the host name defined in /etc/hosts on the database server was not known outside the server. The database, I assume, did a lookup on the ip number in local /etc/hosts and sent the host name to CMAN when registering. The fact that I had used ip-numbers in the local listener.ora and tnsnames.ora did not change that behavior. A properly DNS would have saved me this, the solution was to define the database server name in local host file on server where CMAN is running.

This means that receiving ORA-12529 does not necessarily mean that there is something wrong with your rules. 12529 seems to be a kind of 'catch all error' when rule filtering fails. Look for nprffilter: entry in the trace file and start reading from there. I found this:

snlinGetAddrInfo: entry
snlinGetAddrInfo: Invalid IP address string FOO
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: exit
snlinGetAddrInfo: entry
snlinGetAddrInfo: Name resolution failed for FOO
snlinFreeAddrInfo: entry
snlinFreeAddrInfo: exit
snlinGetAddrInfo: exit
nprffilter: Unable to resolve to IP
nprffilter: exit
nsglbfok: no rule match or an error from nprffilter
nsglbfok: exit

(Real hostname replaced and timestamps removed). Looking for 12529 or whatever code you receive in the trace-file and start reading backwards is also an option.

Multi-column function based index

Today I was looking at an SQL statement with a slow response. The statement does a lookup on a person using 'upper(lastname) and upper(firstname)'. There is a functioned-based index (fbi) on the lastname column, but by checking the statistics it turned out to have a rather low selectivity and the CBO went for a full table scan. I don't know why it has not occurred to me before, but today it dawned upon me that it may be possible to create an index like this:

create index foo_idx on foo(bar(a), bar(b)) ;

There is an example in Tom Kyte's last book, but I guess the tidbit got lost on its way to or inside my hippocampus. I have always thought about the fbi as a way to index the result of a function (which could take many arguments, but only return one value). By looking at the fbi as an index on a shadow-column it becomes obvious that more than one column can be indexed this way, just like any other multi-columned index.

Point is of course that each of lastname and firstname has a very low density (at least in this part of the world), but the combination of them is not too bad.

Or may be it was something else that went right, one should always supply a proof these days. I reapplied the old index with the same slow response as a result. Also I gathered statistics again every time an index was created, and the results where consistent.