Truncated Header in the Result Set

A brief and side-tracked intro first: My SQL 2005 upgrade effort that I was so excited about? It got pushed back... well not really the upgrade itself, but my participation... long story short, I couldn't get involved because the US client didn't agree with Europe's timeline for the upgrade - too aggressive. Now moving on to the real topic.

Since the SQL 2005 effort is a mere thought-bubble for now, I got something here about oracle.

Yesterday, I was approaced by a developer with a rather odd problem that they are having. One of their vb codes started spitting out errors because of a column header in a result set of a query not being displayed properly. Here's my attempt to demonstrate the reported problem.

A part of their codes looks like this:

SELECT DISTINCT SUBSTR(SS.LONG_COLUMN_NAME, 1, 4) FROM
SOME_VIEW SS


Now, the result of this query should look something like this:

SUBSTR(SS.LONG_COLUMN_NAME, 1, 4)
--------------------------------
VALUE1
VALUE2
VALUE3

They told me it has been like this every since. It looks like the name of the column (don't ask me why they are not using alias), is being used in other part of the program. But yesterday, they found out that the result given by the query above started to look like this:

SUBSTR(SS.LONG_COLUMN_NAME, 1,
------------------------------
VALUE1
VALUE2
VALUE3

I was told that there were no changes done on the code since 2006. I checked objects involved and the last DDL change done was 2006 as well. The last oracle patch applied on the database was Oct 2007 and the error started happening Nov 18 2007 at 2pm.

Here's something. The database was bounced on Nov 18 2007 12am to increase SGA of the database. Related? Doesn't look like it! The size of SGA has for sure nothing to do with how Oracle displays result set. But this is just too coincidental. So I poked around some more. I posted questions on OraFAQ forum (believe me, this site is just very nice) and alas, I started to see light.

The behavior described above could be affected by database server, client version and the database parameter cursor_sharing. Database server version wasn't changed. Client version upgrade was done on one server but the behavior is shown on any client on any machine so, I thought this couldn't be it. Lastly, the db paramenter... CURSOR_SHARING. By the sound of it, it doesn't look like connected to how oracle would display headers on result sets... but I am running out of suspects..... everyone has been acquited... I only got CURSOR_SHARING left to be placed under a light bulb. I did some tests...

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> select substr(rpad(dummy,35,dummy),1,25)||'1234567890' from dual;

SUBSTR(RPAD(DUMMY,35,DUMMY),1,
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXX1234567890

SQL> alter session set cursor_sharing=similar;

Session altered.

SQL> select substr(rpad(dummy,35,dummy),1,25)||'1234567890' from dual;

SUBSTR(RPAD(DUMMY,35,DUMMY),1,
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXX1234567890

SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> select substr(rpad(dummy,35,dummy),1,25)||'1234567890' from dual;

SUBSTR(RPAD(DUMMY,35,DUMMY),1,25)||'1234567890'
--------------------------------------------------------------------------------
XXXXXXXXXXXXXXXXXXXXXXXXX1234567890

Holy Molly! Just look at the last statements. The column header was not truncated when cursor_sharing is set to exact... mmmm-hhhmmm that's right. That's gotta be it. Later within the day, I found out from one of the other dbas that the value of this parameter was changed from FORCE to EXACT by another dba without us knowing or fully aware of. The same time when the database bounce was done. So we finally pinned down the culprit and placed behind bars. Case closed.

Next time, I'd try to explain more on CURSOR_SHARING. This is an interesting and tricky parameter. Be cautious. That's all I can say for now.

Ciao!

Appalled for Upgrade

It's SQL Server's time for this blog. I've been doing SQL Server database management for about 6yrs now and I wish I have started with this earlier on. Over this years, I've gained a lot of knowledge and experiences towards managing SQL server databases... but no, I am not yet an expert. I still have gazillions of tons of rice to eat (to have a lot of rice to eat is a popular expression in Filipino to indicate there are still a lot of things to learn). Through the years, I've gained more than enough confidence about SQL Server. It doesn't make me feel nervous anymore to manage and handle actual production servers but I still hold on to paranoia in terms of being too careful moving around a production environment. It pays to be extra careful.

Over the past few months our SQL Server environments have been pretty quiet. A little bit of hitches here and there but nothing very serious and urgent. Nothing new. No adventure.

Until I am approached by our Europe client. They are planning a SQL Server 2005 upgrade and they want my involvement! Now that's what I call action. SQL Server 2005 is very new to me and I have little experience on it.... much less with the actual upgrade. I just hope that my US clients will allow me to participate full time on this but that's a different story... anywho, I'm excited!

I've started reading about SQL Server 2005 and the upgrade process and I am just appalled by how SQL 2005 will be a lot different from SQL 2000 and lower versions. Just the fact that one might be required to rewrite many DTS packages to be migrated to a SQL 2005 environment is beyond me. Appalled is the word nothing less! Ok, I am exaggerating.

The application is not a big one but big enough to pose some challenges and this is really giving me a rush on my adrenaline and a tingle in my spine.

This journie might soon be filled with SQL Server ahahs and uh-ohs. Let's wait for the next chapters... Tata for now!

Archiving not possible

As I started to check my emails this morning, I got series of email alerts from of our non-production 8i database. Connection is not possible. I went ahead and checked the alert log and I got tons of the following:

Wed Nov 14 19:46:47 2007
ARC1: Beginning to archive log# 2 seq# 54993
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log# 2 seq# 54993
ARCH: Archival stopped, error occurred. Will continue retrying

I tried connecting from my sqlplus and got this error message:

ORA-00257: archiver error. Connect internal only, until freed.

The archive log folder is just 19% used, so I got puzzled. The alert log doesn't stop generating the same archive errors and quite fast...no pause. Since this is a non-production database, I tried to shut it down by issuing shutdown immediate.

With my nose sticking to the monitor, I patiently monitored the alert log but it doesn't even look like it is trying to shutdown. Minutes passed, I decided to issue a shutdown abort through svrmgrl (I can't connect to another sqlplus session since the database is in the process of shutting down). I restarted the database and was relief started to engulf when I see that the database opened beautifully.

I checked the alert log and it recovered quite nicely. The archive log errors are gone. Looks like the database has proceeded to its normal operation.

But what happened? It looks like the archive log folder got full and after it was cleaned up by our automatic archive log backup (fires up when a certain threshold in the archive log folder is reached), the database couldn't recognize that the the folder is now available with more than enough free spaces.

I tried to browse more from the internet and found this possible solution:

SQL> alter system set log_archive_dest_1='' scope=both;

System altered.

I wish I was able to see this before resolving to database bounce so I could have seen if this will work.

Maybe next time, I'll be able to try this... not that I am looking forward to another database problem... but for sure, there's never a dull moment with oracle databases :)


Sudden Goodbye

This is not completely about an Oracle experience but somehow, it is connected. I have just started in my new company and have just started learning Oracle again. I got lucky as the Oracle DBA lead is a nice person and teaches me with details, willingness and patience. With him around, I am inspired to learn and be a good and effective Oracle DBA. With him around, I am encouraged to ask questions… no matter how silly it could be. In short, I am already getting really comfortable with him as my mentor. But yesterday, My afternoon turned dark when he suddenly broke the big news… he’s resigning. I may be overacting by I was really sad to the point of having a lump in my throat. I know I am sad for selfish reasons…. I am sad because he’s one of the persons (if not the only one) in my team that has really made me feel I am supported, understood and guided. He’s generous about sharing his knowledge. I have been learning from him quite a lot. So now that he’s leaving, some of my enthusiasm about Oracle chipped off.

I have to stop being childish.

He’s really a knowledgeable Oracle DBA and he does have lots of future ahead of him. He’s moving on with his career… finding new and better opportunities. I do wish him luck… and myself too.

Two Homes Are Better Than One

One good practice that was passed to me by the Oracle gurus I am currently working with is keeping 2 Oracle homes for each version of Oracle installed on a server. At first glance one would think it is just a waste of resource and additional maintenance for dbas. It’s definitely not. It’s even a bright idea. It’s one of my favorites best practices I’ve learned so far 

So how does it work and how does it help in terms of better database administration?

Let’s take a look on a 10g installation for instance. Oracle Home being on $ORACLE_BASE/product/1020a. Another oracle home is created on the same oracle version.. let’s say: $ORACLE_BASE/product/1020b. Databases are running off from 1020a home. So when an upgrade or patch installation is needed, the binaries can be patched on the other oracle home first. At this time, databases are still up and running. Once the binary patches are already done, the databases can now be moved from 1020a home to 1020b in this case. This is the only time that the databases will need to be shutdown. After that, the database part of the patch installation can be done… then normal database operation can already proceed. The previous oracle home where databases used to run off is now empty and ready for the same patch installation. This can actually be left un-patched for a period of time so just in case the new patch installation would cause any problem or bug, the databases can be just easily switched back to the previous state of the environment.

And what advantages would this approach amount to?

Downtime can be radically reduced down to just a some minutes instead of hours. The risk of is also reduced in case the system needs to be reverted back to the old version as the databases only need to be moved back to the original Oracle home. No rolling back of patches where databases are currently housed. You can easily compare the two homes to see what has changed on the different versions… and so on and so forth. I’m sure there are other advantages that can be explored using this approach. This only disadvantage is that the extra home requires additional maintenance (though minimal) and the additional space it requires (let’s say around 4G). Comparing the advantages and disadvantages, there is no doubt the advantages compensates way more than enough the disadvantages.

Sweet, I’d say.

Database Not Shutting Down

Tuesday afternoon and my Oracle DBA mentor is giving me a lecture from head to foot. Ok, I am exaggerating.. he is just simply explaining me an alternative way of dealing with our problem with one database yesterday. He’s being really nice in fact. Always patient to explain and teach me things about Oracle.

Now, let’s do some flashback before I narrate here the lessons I learned.

Monday morning and I got an report that there’s a connectivity problem with one of our QA databases… tap tap tap on my laptop… I logged in to the server, check the database processes… pmon running, smon running, listener running… I can connect to sqlplus… from telnet… what’s could be wrong? I tried to log in from OEM and true enough there’s connectivity problem… I can’t connect remotely. Back to telnet, sqlplus… I decided to bounce the database by issuing shutdown immediate. One minute passed.. another minute…more minutes passed… nothing is happening.. . the database is not shutting down! I opened another telnet session and as if possessed, I issued a kill command on my sqlplus session that is trying to do the shutdown immediate. I opened another sqlplus session and tried to log in as sysdba. Alas, I can’t connect! What?! It is telling me that the database is in the middle of shutting down immediate and is not accepting any connection. Oh no. Oh no. Oh no. What to do? I tried to search, I tried to register on forums, fired my questions away but no one is replying to me. Tap tap tap on my laptop once more… poking around on the server…reading the alert.log, checking the smon trace file… until the assassin in me is trying to take over again. Kill the smon, kills the smon… that’s all I could think of. The assassin in me won… I killed smon. I noticed a pmon trace file was generated… I read it and all it was telling me is that smon was terminated improperly… my dirty work is recorded! I knew it. There’s no turning back now. I tried to start up the database again and with my luck still working, the database started beautifully. I ran a called backup and started panic in my tummy when the backup is not finishing… I tried to check for logs and noticed that this particular database takes 7 hours to finish its cold backup… 7 hours!! I suffered 7 long hours before I finally was able to breath when the cold backup finished and the database once again started up again smoothly… I’m saved.

Now, moving back to present. Here’s what my mentor told me that I could have done instead… (here’s where the lesson part takes place  )

Here’s how he started his lessons: “Here’s the better way for 8i…” Smooth, huh? He didn’t reprimand me at all :-D That’s the coolest! Anyway’s here goes the meat of this blog:

When an 8i database seemed to be hanging the first thing that should be done is to issue a shutdown abort on sqlplus as sysdba.

If you got too excited and issued shutdown immediate and it didn’t work, log in to svrmgrl and use connect internal, then startup force. Though, this should be the last ditch to get things working.

But the novice me asks this: Will killing smon hurt the db?

Here’s the answer I got: No, it shouldn’t. But I’d reserve a kill command as the last resort – what you did is really no different than if someone pulled the plug on the server – the DB will just go through its crash recover on restart.

Nice 

So there’s my Oracle 8i lesson for the day :-D

Trapped on a 10.2.0.3 Patchset Post-Installation Instruction

I have been trying to install 10.2.0.3 patchset on top of our 10.2.0.2 database. Along with the this patchset, I am also trying to install CPUApr2007 and 3 other interim patches. I am encountering a show-stopper though. Today is Memorial day in the US, so my Oralce DBA guru is out of the office. I don’t have anyone else to quickly ask about what I am encountering… I am frustrated. None from the oracle forums I registered is giving me some help. I decided to blog my frustration away…

Oracle documentation really helps a lot with all your hows and whats… but sometimes the way they document things is also annoying… many times they are confusing with the way they are writing their instructions… then again, perhaps it’s just me.

From the 10.2.0.3 patchset readme file, one of the post installation instruction is this:

“29: If you are using the Oracle Recovery Manager catalog, enter the following command:
$rman catalog username/password@alias
RMAN> UPGRADE CATALOG; “

Now, what’s confusing about this? I don’t know if “… using Oracle Recovery Manager catalog…” refers to databases that are being backed up by rman and is registered on the catalog database OR just refers to the “catalog database” itself.

On our 10.2.0.2 Oracle Home, I have 3 databases… 2 of them are normal rdbms and one is an rman catalog database… now, I have to stop and wait for tomorrow till my senior Oracle DBA tells me what to do. Afraid to break anything, I didn’t want to go ahead and do the try-and-see-what-happens-next resolution.

I wish they could just say in the instruction something like this: “If your database is being managed by RMAN or is an RMAN catalog database, run the following blah blah blah” or they could just elaborate more… what’s wrong with those Oracle people documenting these readme files? Or what’s wrong with me??