10.2.0.3 patch fails if database word size had ever been changed in the past

Thanks to Chris Carr for pointing out this:

If you are upgrading or patching to 10.2.0.3 and the word size is different to the word size when the database was created the upgrade will fail with an ora-600. This applies even if the wordsize has been changed in an interim upgrade i.e. 8.1.7 32bit upgraded to 9.2.0 64bit will not upgrade to 10.2.0.3 64 bit, (but will upgrade to 10.2.0.3 32 bit).

To identify whether a database was created as 32-bit or 64-bit, execute the following SQL statement:

SQL> select metadata from sys.kopm$ ;

If the output references the string ‘B023′ then it indicates that the database was created as 32-bit If the output references the string ‘B047′ then it indicates that the database was created as 64-bit

Metalink note on this is Note 412271.1.

There is a patch to fix this before applying 10.2.0.3, although it is available on Linux, Solaris, HPUX and AIX only so far, not on Windows for some reason.

And on the seventh day, God fiddled with His NLS settings

This would seem to be pretty authoritive:

“And on the seventh day God ended his work which He had made; and he rested on the seventh day from all his work which He had made. And God blessed the seventh day, and sanctified it; because that in it he had rested from all his work which God created and made” (Genesis 2:2-3).

But, only in America.*

In many other NLS territories, Saturday is the sixth day of the week, not the seventh:

SQL> alter session set NLS_TERRITORY = 'AMERICA';

Session altered.

SQL> select to_char(sysdate+3,'D Dy') from dual;

TO_CH
-----
7 Sat

SQL> alter session set NLS_TERRITORY=’UNITED KINGDOM’;

Session altered.

SQL> select to_char(sysdate+3,'D Dy') from dual;

TO_CH
-----
6 Sat

Now that’s a real pain, because different Windows PCs will inevitably end up having different registry/environment variable settings for NLS_LANG (NLS_TERRITORY forms the middle part of the NLS_LANG variable). And NLS_LANG also gets set a lot in unix .profile’s. And those all override the databases settings.

So this week when I had users reporting different results from different PCs, I had initially assumed tnsnames.ora differences were pointing them off to different databases. But really it was because they were running code like this:

select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,’D') = 6 ;

The fix is to name the day of the week rather than count to it:

select address, 'These customers want visited Saturdays'
from customer
where to_char(workdate,’Dy’) = ‘Sat’ ;

Essentially this issue makes the “D” date format unusable.

If code like the above absolutely couldn’t be changed, then a logon trigger would be the only way to set NLS_TERRITORY the same for all users:

create or replace trigger my_logon_trigger
after logon on database
begin
execute immediate 'alter session set nls_territory = ''UNITED KINGDOM'' ' ;
end ;
/

See Tom Kyte on NLS Date Format and asktom “language issue”

Bug? with wrong results from all_objects in stored plsql procedures

Wouldn’t it be nice if sql code produced the same result, regardless of whether it is inside a stored procedure or is a standalone plsql block? But that’s not what happens when the all_ views are used.

Run this code to see the anomaly for yourself:

set serverout on
declare
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
create or replace procedure af_temp as
  var1 number ;
begin
  select count(*) into var1 from all_objects ;
  dbms_output.put_line(’all_objects: ‘||var1) ;
end ;
/
exec af_temp ;
drop procedure af_temp ;

It’s the exact same plsql each time, but very different results reported. For example:

all_objects: 13501

PL/SQL procedure successfully completed.

Procedure created.

all_objects: 4929

PL/SQL procedure successfully completed.

Procedure dropped.

This behaviour appears to occur on all versions - I tested from 7.3 through 10gR2. The biggest discrepancies in results seem to be with DBA users other than SYS, but all users show some discrepancy.

This issue doesn’t occur with the dba_ views, so best to use them instead in stored plsql objects. That does mean explicitly granting select privileges on the dba_ views being used in stored procedures, which isn’t necessary for the all_ views.

So, is this a bug? I can’t see any mention of it all in metalink.

It’s not just an academic issue either: it caused this code of mine to fail to find and drop the objects it was meant to drop.

Pre 9202/9014: Advanced queuing runs away with server memory.

Using the below memory listing script, I found two databases with large PGAs. That was strange since pga_aggregate_target was unset, sort_area_size was just 64k, and there were very few sessions. Checking PGA at session level showed the QMN sessions had high memory usage (over 200mb each). Which pointed to bug 2227360 “QMN process leaking memory”

It is fixed in 9.0.1.4/9.2.0.2 and above, but I was on 9.2.0.1. So the workaround is to kill the QMN (=AQ) sessions. Sounds drastic, but oracle restarts the processes apparently cleanly, with just a message in the alert log like:

Restarting dead background process QMN0
QMN0 started with pid=9

And that is the memory then freed up - for a while at least, you have to be prepared to kill those sessions every so often as the memory leaks build up, if, that is, you can’t patch up.

I used the below script to kill the sessions. I didn’t need to use the drastic “kill -9” on this occasion, but sometimes sessions take forever to go without that.

select 'alter system kill session '''||s.sid||','||s.serial#||''' ;',
'kill -9 '||p.spid
from v$session s, v$process p
where s.paddr = p.addr
and s.program like '%QMN%'
/

Reference: Metalink Note 233101.1.