Monday, December 31, 2012

Yet another thanks, and Happy Holidays!

Having taken a short (unintended) break from blogging in November, it was astonishing to see that while it took this blog 5 months to get 100 views it only took another 2 months to get an additional 50 views with only posting twice in that time!!  I can't tell you how much this motivates me to keep this blog going, but also to rededicate myself to the "art of Oracle" so that I can learn things that can be of use to the community as a whole.  So to everybody that follows what I'm doing here, or stumbles upon this site searching for something Oracle related, thank you!!!

Also, I want to wish everybody out there a Happy Holiday no matter which you choose (or not) to celebrate and here's to looking forward to ringing in a Happy New Year with all the challenges and learning we hope to encounter!!

-The Oracle EMT

Thursday, December 13, 2012

Timestamp across DB versions

So the other day I decided to write up an alert to let us know when a DB crashes, and nobody hears it, but I was struggling with joining our instances for some unknown reason due to this error message:

ORA-30175: invalid type given for an argument

I shelved it for a day or two until I had a chunk of time to dig into this a bit deeper, and with the help of an Oracle forums post I realized what my problem was.  You see, the source of my confusion resided in the fact that the below script was able to be run individually for each instance and I only had problems when starting to union them together:

select 'INST1' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from DBA_ALERT_HISTORY@inst1
where creation_time > sysdate -1
and object_type = 'INSTANCE'
union all
select 'INST2' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from
DBA_ALERT_HISTORY@inst2
where creation_time > sysdate -1
and object_type = 'INSTANCE'


Looks benign right?  Well I realized that I was joining 11g and 10g DB tables together and that the timestamp object has apparently changed a bit, so that even with the table definitions not changing our system just wouldn't let me join the tables together no matter what I did when I do a value comparison on creation_time!  Finding that post was the key to getting this changed, because when I updated the script to be:

select 'INST1' AS "SERVER", process_id, host_id, substr(reason, 0, 40) AS STATUS, creation_time from DBA_ALERT_HISTORY@inst1
where creation_time > to_timestamp(trunc(systimestamp))
and object_type = 'INSTANCE'
union all
select 'INST2' AS "SERVER", process_id, host_id, substr
DBA_ALERT_HISTORY@inst2
where creation_time > to_timestamp(trunc(systimestamp))
and object_type = 'INSTANCE'


everything was right with the world, and no more error messages were returned.  Oddly enough, or not as I've already explained, the same script with creation_time > to_timestamp(trunc(systimestamp)) -1 starts to bomb out again because the timestamp object is again having a comparison done to it.

Thursday, December 6, 2012

Troubleshooting latch contention by Tanel

In trying to find out what was holding latches in our new shiny 11.2.0.2 system, I started digging around on the web and lo and behold I ran across a really good article by Tanel Poder a few years ago not only explaining latches, and their contention, but giving us tools to be able to try and catch them in the act!

P.S.  I know I've taken a short break from the blog, but I've got some really good posts coming up over the next few weeks so stay tuned.