0x1A

0x1A, better know as the end of file character.
Now also known as the cause of me waisting several hours on analyzing a 500MB raw trace file trying to figure out why the tkprof report did not seemed to be correct.

Still wondering why the application I was tracing had an EOF character in the value of a varchar2 type bind variable.

Just because its printed, doesn’t mean its true

That statement is often written by Jonathan Lewis and today I was reminded on how true it is.
I had a discussion today with two of my colleagues who wanted to increase the number of arch processes on a dataguard system. As reason they pointed to metalink note 468817.1 – “RFS: possible network disconnect while taking rman backup on primary site”, which makes the following statement:

“In a Data Guard Configuration, during Scheduled RMAN Backup no Redo is transported to the Standby Server as the ARCH Process is blocked (as expected ie. RMAN would utilize 1 ARCn Process and the other ARCn for local Archiving ) which means the Standby stays out of sync (assuming max_arch_processes=2) until the ArchiveLog is manually copied across and registered to the Standby Database after which the Standby Database resumes applying the ArchiveLogs.”

The first thing that drew my attention was the part about rman utilizing an ARCn process.
While the ARCn processes are indeed responsible for archiving the online redo log files, it is the sessions own server process that does this when issuing an “alter system archive log current” command. When rman forces a log file to be archived, the same thing happens. This can easily be verified by looking to the “creator” column in the v$archived_log view. A strace of the rman server process would also prove that it is this process which reads the archived redo logs and streams them to the rman client to be written in a backup piece.

1 – 0 for me

The second thing I noticed was that, according to the note, the standby would remain out of sync until the archivelog was manually copied across and registered to the standby db.
Even if the rman process was using an ARCn process, leaving no processes to copy the archivelog over (with max_arch_processes=2), the standby db would normally be able to pickup the synchronization again after rman would have released the ARCn process again.

2 – 0 for me
and end of discussion.

So, “just because its printed, doesn’t mean its true”, would also apply on metalink notes.
Luckily there is a feedback link at the end of the note, so I hope it will soon be removed or modified.

And yes, I can get a little bit competitive in discussions.
How did you guess?

Knowing the history of an application

The company I work for has many hospitals as client, and most of them use the same set of applications. One of those applications (which will be kept unnamed) is always giving us performance problems. Through time we discovered many design problems in its database structure, such as not using primary keys or foreign keys, storing dates in separate year, month, day fields,….
Also the developers seemed to be very into using cryptic names for the table and field names (all table names are like on150, on010, pt010, on070, zv054, … with field names as tgcde, innr1, crcmg, …), which is really handy when you have to tune a query. Another nice design decision they made was to include a column in each table storing the table name, and then include this column as leading column in almost every single indexes (including in the unique indexes).

A couple of days ago we were asked again to tune 3 custom queries, which started to take hours (days) to run after the application had been upgraded. Unlike other occasions, this time we did manage to receive a part of the db layout from the application vendor, including short descriptions for the tables and columns.

From the moment I looked at the documenation, things fell into place.
You see, I’m old enough to still had a cobol course at college, and the documentation layout remembered me very much to cobol key-sequenced files.
Apparently, when they switched to using oracle they just took the files and recreated them as tables. Without making any further change in the db design. Neither did their developers made the mentally switch towards “set oriented” programming (read: sql) to retreive data, but instead continued to “read” from the tables as they were still using files (In the email conversation I had with their customer support, they where actually still speaking of files instead of tables). Even the mystery of the column storing the table name was explained, as each “file” layout in the documentation had the sentence: “number of record formats in this file” next to it… .

Of course, knowing why the application has such a dreadful design does not help me much to rewrite the sql statements. But at least it stopped me from willing to kill the developers, as I now know that they just don’t realize that they are no longer working with key-sequenced files, but with a relational database instead.

Ok, I admit… I still want to torture them. But just a little bit

dba 2.0

After reading the prodlife blog about the dba 2.0 presentation on oracle open world and Tom Kyte’s blog, I must concur that an oracle dba indeed need management tools to administer an environment (that is a mid-size or large environment). Databases are growing bigger and bigger and maintenance windows are disappearing as snow for the sun, so having tools to help you with managing the databases are very welcome.
This being said, I hate the ongoing trend in oracle presentations and courses to solely focus on managing databases via these tools. In fact they don’t show / learn you anymore how to manage oracle, but how to operate a particular management tool (read: grid control).
What are you going to do when the grid control is not reachable or when you need to manage a database that is not registered in grid control?

I am a consultant dba, what means that I manage database for various customers on different sites. Sometimes this management is done on site, but often it is done via remote access (sometimes having limited bandwith or requiring hopping from one server to another) . So in most occasions the only tool that I can use is sqlplus. And you know what? I love sqlplus! True, sqlplus is not a very convenient tool for browsing through records or to quickly update a single record, but it is available on every platform and combined with a repository of scripts (the true treasure box of an oracle dba) it is a very powerfull tool.
Using scripts also force you (unless you are just copying the scripts from the internet) to investigate in which views oracle is storing which data and how to join the different views together, giving you a better understanding of how oracle works.

It is as Tom wrote on his blog “Having the ability and knowledge to command line when necessary, along with using the tools day to day is the way forward I think”, but is is such a shame that currently often the only focus is on the tool.