• /
    • Archives / November 2012

Archives For November 2012

fun with dates when moving from oracle to postgresql

November 29, 2012 — 5 Comments

we are currently in the process of moving an oracle data warehouse to postgresql and had some fun on how oracle handles dates in contrast to postgresql. in oracle, if you subtract one date from another you’ll get an integer:

select to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy') diff from dual;
      DIFF
----------
         4

if you do the same in postgresql you’ll get an interval:

select to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy') diff;
  diff
--------
 4 days
(1 row)

the issue was, that a view used this in a where clause and did some calculations based on the return of the expression. obviously this failed when we created the view in postgresql. the trick was to use the to_char function on the interval:

select to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) diff;                                      
diff
------
 04

… we thought :) but the result can still not be used to do some calculations:

select to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) + 8;
ERROR:  operator does not exist: text + integer
LINE 1: ...m.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) + 8;
                                                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

another conversion was necessary to make this work:

select cast ( to_char ( to_date('05.01.2012','dd.mm.yyyy') - to_date ( '01.01.2012','dd.mm.yyyy'), 'DD' ) as int ) + 8;
 ?column?
----------
       12
(1 row)

lessons learned ? oracle is doing implicit type conversions silently in the background. postgresql does not …

another example:

oracle:

SQL> create table t ( a number );
Table created.
SQL> insert into t values ( 20120101 );
1 row created.
SQL>  select to_date(a,'yyyymmdd') from t;
TO_DATE(A,'YYYYMMDD'
--------------------
01-JAN-2012 00:00:00

postgresql:

create table t ( a number );
CREATE TABLE
insert into t values ( 20120101 );
INSERT 0 1
select to_date(a,'yyyymmdd') from t;
ERROR:  function to_date(numeric, unknown) does not exist
LINE 1: select to_date(a,'yyyymmdd') from t;
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
In quick&dirty ,
permalink

CRS-2534: Resource type ‘ora.cluster_vip_net2.type’ is not registered

November 9, 2012 — Leave a comment

if you want to manage vips in the grid infrastructure which are not on the default network and you get this: “CRS-2534: Resource type ‘ora.cluster_vip_net2.type’ is not registered” don’t panic, it is easy to fix. basically you need create the “ora.cluster_vip_net2.type”-type before adding the vip with appvipcfg:

./srvctl add network -k 2 -S x.x.x.0/255.255.255.0/igb0
./crsctl start resource ora.net2.network
./crsctl add type ora.cluster_vip_net2.type -basetype ora.cluster_vip.type
./appvipcfg create -network=2 -ip=x.x.x.x -vipname=myvip1 -user=root
./crsctl start resource vip1 -n server1
./appvipcfg create -network=2 -ip=x.x.x.x -vipname=myvip2 -user=root
./crsctl start resource vip2 -n server2
./crsctl stat res –t
./crsctl modify res 'myvip1' -attr "HOSTING_MEMBERS=server1 server2"
./crsctl modify res 'myvip2' -attr "HOSTING_MEMBERS=server1 server2"

not sure, but I think this is a bug as appvipcfg should manage this.

the above is valid for 11.2.0.3 on Solaris SPARC 64bit

In configuration ,
permalink

ouch! rootcrs.pl, more than 64 CPUs => ORA-04031 when creating the ASM instance

November 3, 2012 — Leave a comment

back from holidays and the first ouch on the first day. we tried to setup a clustered grid infrastructure on a solaris 10 t4-2 machine. business as usual, one might think. but when running the rootcrs.pl setup script asmca ( which in invoked from rootcrs.pl automatically ) reports:

...
main] [ 2012-11-02 12:58:59.282 MET ] [OracleHome.getVersion:1023] Current version from sqlplus: 11.2.0.3.0
[main] [ 2012-11-02 12:58:59.282 MET ] [UsmcaLogger.logInfo:143] Role SYSASM
[main] [ 2012-11-02 12:58:59.282 MET ] [UsmcaLogger.logInfo:143] OS Auth true
[main] [ 2012-11-02 12:59:21.347 MET ] [SQLEngine.done:2189] Done called
[main] [ 2012-11-02 12:59:21.349 MET ] [USMInstance.configureLocalASM:3033] ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT DECODE(null,'','Total...","SQLA","tmp")
...

hm. there is a support note about this behaviour ( 1416083.1 ) but there is no way to give asm parameters to rootcrs.pl or the crsconfig_params files. what we needed to do:
disable some CPUs ( so there are less than 64 available ), re-configure the cluster, adjust the asm memory parameters and then enable the cpus again. this seems to be the only workaround at the moment ( 11.2.0.4 and 12.1 should contain a fix for this ).

In configuration
permalink