Skip to main content

Posts

Showing posts from April, 2008

A list partitiong bug

Last week, I found a not-so-nice bug in 10gR2. Just try this short test case, creating a table with two list partitions, default and null:
alter session set nls_territory=America;
alter session set nls_language=American;

select version from product_component_version;

drop table th3;

prompt Creating TH3...
create table TH3
(
DF DATE,
DT DATE,
N NUMBER
)
partition by list (DT)
(
partition THTBL_CURRENT values (NULL)
tablespace USERS,
partition THTBL_OLD values (default)
tablespace USERS
)
;

prompt Loading TH3...
insert into TH3 (DF, DT, N)
values (to_date('01-01-2000', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 1);
insert into TH3 (DF, DT, N)
values (to_date('01-01-1000', 'dd-mm-yyyy'), to_date('01-03-3000', 'dd-mm-yyyy'), 2);
insert into TH3 (DF, DT, N)
values (to_date('10-04-2008', 'dd-mm-yyyy'), to_date('10-04-2008', 'dd-mm-yyyy'), 10);
insert into TH3 (DF, DT, N)
values (to_date('11-04-2008&…

10gR2 RAC on RHEL5.1 (x86_64)

Just a few issues you should be aware of when trying to install 10gR2 RAC on RHEL 5.1:

See Metalink 465001.1 for raw device configuration for RHEL 5. It's not explicitly said, but all disks used must be partitioned! Otherwise, if you don't partition the OCR disk, the root.sh from clusterware installation will fail (fail with “Failed to upgrade Oracle Cluster Registry configuration” error, with “Failed to call clsssinit” in log.)
The ASMLib will also refuse to stamp whole disk, a partition is required.

For OS configuration, see Metalink 421308.1. However, the sysctl parameters listed there do not exist on RHEL 5.1, you will have to use:
kernel.shmmni = 4096kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default = 262144net.core.rmem_max = 262144net.core.wmem_default = 262144net.core.wmem_max = 262144net.ipv4.tcp_rmem = 4194304 4194304 4194304
net.ipv4.tcp_wmem = 262144 262144 262144
You will also need to install one more rpm pa…

dbms_alert on RAC

Not long time ago, I came across a usage of dbms_alert to manage running jobs. As the solution implemented must work also for RAC, I wanted to know whether dbms_alert works on RAC across instances.

The documentation nor Metalink does not say anything (contrary to dbms_pipe, which does NOT work on RAC). So, if they don't warn, it should work... However, Julian Dyke says, that dbms_alert does not work and is the same as dbms_pipe (sources: http://juliandyke.com/Presentations/Presentations.html#ARoughGuideToRAC, page 17, or Pro Oracle Database 10g RAC on Linux, page 426).

You know, never trust anybody, so I conducted a test case (10.2.0.3 on Linux x86_64, VMware ESX server, 2-node RAC):

You will need two simultaneous sessions, I mark them with DWH1> and DWH2> here.
DWH1> select instance_name from v$instance;

INSTANCE_NAME
----------------
DWH1

DWH2> select instance_name from v$instance;

INSTANCE_NAME
----------------
DWH2

DWH2> exec dbms_alert.register('TST');

DWH2> set …