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
Monday, December 31, 2012
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, substrDBA_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.
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, substrDBA_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.
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.
Subscribe to:
Posts (Atom)