Oracloid Blog

When checking for new known issues with 10.2.0.3 patchset on Metalink I discovered that an upgrade problem was added - Metalink Note 401435.1.

The issue affects all 64 platforms especially when database is upgraded from any previous 32 bit release. So far there are no one-off patch and no workaround except… well, use Data Pump or exp/imp. Typical, “workaround” approach from Oracle. In my last post about 10.2.0.3 issues I mentioned Veritas bug (now fixed with one-off), for which Oracle proposed moving files to non-Veritas storage. Do they think that companies using Veritas do it just for fun of spending an awful lot amount of money? Well, some might do but I digress.

Here is the number of useful Metalink notes if you are interested:

• Bug 5520937 - Upgrade to 10.2.0.3 can fail with ORA-600 [22635]
• Alert - ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
• Ora-600[22635] While Upgrading Database From 10.2.0.2 To 10.2.0.3
• Bug 5748280 - ORA-600 [KOKEIIX1], ORA-600 [22635] WHEN APPLYING 10.2.0.3 PATCHSET
• Bug 5755471 - ORA-00600 [22635], AT SYS.UPGRADE_SYSTEM_TYPES_FROM_920 DURING UPGRADE

You did not forget to test you backups, eh?

Update: Metalink Note 412271.1 was updated with much more details since I posted this entry and patches 5871314 and 5892355 are available. Note that the problem is there any change of bits 32->64 or 64->32 and even if change occurred before 10.2.0.3 patched. See note 412271.1 for more details.

Finally, I have some time to post few photos for those of you who haven’t seen Ottawa at night. I wish I had a better camera and wide angle lenses to capture certain scenes. Maybe next year…
You can see the the full album here and few highlights below:

Read more…

My life is much better here in Canada since few month ago when I discovered that LCBO (Liquor Control Board of Ontario - the only Liquor Store in Ontario) caries Guinness in cans. Coupled with salt and vinegar chips - it’s a perfect dinner for me.
It could be the ideal breakfast as well but I’m not sure if my colleagues and Pythian customers would understand my passion.

It looks like finally the weather is “nice” enough for skiing. Recalling our skiing trips in Austrian Alps last year I can’t stop thinking about my favorite food there - Knoblauchrahmsuppe. Yum-Yum! I think I can translate it like garlic cream soup. I had to find some local substitutes here in Canada. Nevertheless, it tasted really good just like back in Austria.

At the end of my last MySQL post I mentioned strange behavior with GROUP BY and DISTINCT. In Oracle such constructions are not valid and produce an error.

Here is the test table and statements:

create table t1 (c1 int, c2 int);

insert into t1 values (1,10);
insert into t1 values (2,20);
insert into t1 values (3,30);
insert into t1 values (4,11);
insert into t1 values (4,40);
insert into t1 values (5,50);
insert into t1 values (5,12);

select c1,count(*) cnt from t1 group by c1 order by c2;

select distinct c1 from t1 order by c2;

(more…)

I hit this issue today while creating a new 10.2.0.3 database using DBCA. Recently, I’ve started to use DBCA more often and I try to use its template management capabilities. It looks like templates is not the most robust feature of Database Configuration Assistant. Sometimes, there are issues when I want to reuse template.

Today was no exception. I saved template earlier and tried to reuse it today. However, CREATE DATABASE failed with ORA-07202: sltln: invalid parameter to sltln.

I reviewed the parameters and found that control_files is set to an empty string. Checked Metaclick and, indeed, this error is related to empty control_files parameter. After I fixed that - DBCA continue flawlessly. I can’t believe I’m getting used to the GUI!

Not that I participate there often but couple months ago I pulled out my 7 years old account there and wanted to change its password. To my surprise - I’m not able to do it. Recently Oracle-L mentioned one thread on OTN. It’s more than year old and there are quite a few other threads on this issue. It gets updated almost every day and there is absolutely no progress on email change feature. Amazing reaction from Oracle or rather no reaction.

Well, again, someone can write great database management software, others - database applications, third - operating systems.

In one of my previous posts I mentioned SYSTEM_PRIVILEGE_MAP view. Taking this thread further, I looked into another nice view - V$ENABLEDPRIVS - showing the privileges enabled for the session at the moment. It should be pretty useful if you decide to add some diagnostics into your application. You might also find it very helpful to call from PL/SQL. As you know roles are not enabled in PL/SQL, so use this view to see what system privileges are active.

While working this out I noticed one anomaly that should probably be considered as a security hole. In a nutshell, definer-rights PL/SQL procedures are executed with owner rights. What I figured it that calling a PL/SQL procedure with definer-rights (i.e. AUTHID DEFINER by default) as SYS user keeps SYS permissions at run-time. In the end I’ll show how to grant DBA role for yourself.

(more…)

Noons, posted quite an entertaining essay - no moore (part 2). While reading it, I was agreeing all the way though, but something inside me was protesting. I couldn’t put my finger on it, so I started reading again from the top, and finally the thought materialized. I probably can’t pull it out as well as Noons can, but here I go. Oh… before you proceed you might want to read this, this and this post — especially the comments. That’s how it all started.

(more…)

Continuing MySQL saga…
This is actually a follow up on my previous post. Developers tried to rewrite all statements and even overdid it. As we say in Russia - “teach fool how to pray and he will break his forehead”. Note, I had to rename columns/tables as I write so sorry for possible typos

Query converted by developers from IN subqueries to joins:

SELECT distinct t.col_1, t.col_2, t.col_3
   FROM t LEFT JOIN t1 ON t.col_3 = t1.col_3
          LEFT JOIN t2 ON t.col_3 = t2.col_3
   WHERE TIMEDIFF(now(),t.col_date) >= '00:05:00'
     AND t1.col_3 is not null
     AND t2.col_3 is not null
     AND t.col_4 = 'string'
   ORDER BY t.col_5 desc
   LIMIT 0,6;

My first take was “smart” use of TimeDiff function in the WHERE clause. I was slightly surprised by the way it was compared - “>= '00:05:00'” but more about this later. I checked selectivity of that condition and found that it was very poor - returning large percent of total rows in the table. Column col_4 contained only 2 values either.

Next, I looked at ORDER BY - created index on col_5 but MySQL didn’t pick it up. Luckily, couple days ago Paul Moen posted about ORDER BY and LIMIT optimization so I knew it should work - MySQL is not that bad in the end. ;-) One more careful look and you notice DISTINCT keyword. Bingo! Obviously, MySQL couldn’t and shouldn’t use index in that case. Interesting that t.col_3 is the primary key so distinct was introduced during conversion from IN subqueries to joins. Well, there is no silver bullet so join is no good here.

In the end query was rewritten as

SELECT 1.col_1, t.col_2, t.col_3
    FROM t
    WHERE TIMEDIFF(now(),t.col_date) >= '00:05:00'
      AND t.col_4 = 'string'
      AND EXISTS (select 1 from t1 where t.col_3=t1.col_3)
      AND EXISTS (select 1 from t2 where t.col_3=t2.col_3)
   ORDER BY t.col_5 DESC
   LIMIT 6;

Note that DISTINCT disappeared and joins were converted to EXISTS conditions. Very handy! Index created on two columns to cover also t.col_4 = 'string':

create index col_4_col_5 on t (col_4,col5);

Result was very positive and response was down to less than 10 ms.

I noticed later that condition with TimeDiff is very tricky and string comparison in the end is not always appropriate. I would rather use AddTime and date comparison.

Just now I figured what kept me uneasy about this statement - DISTINCT causes grouping and ORDER BY column is not part of the select list! In this particular case there is no problem as primary key is in the select list. However, I can’t figure out how MySQL would group and sort if there are several col_5 values per distinct row. I can’t see the deterministic result. In another example today, I witnessed that I can use a column in ORDER BY that is not part of GROUP BY list. Seems like the same issue. If I lost you with this - stay tuned. I must be blogging about it soon.