Data … as usual

All things about data by Laurent Leturgez

Monthly Archives: September 2009

Load ACFS on CentOS 5

Leave a comment Posted by on September 22, 2009

In my last post, I wrote that acfs was not compatible on CentOS.
So I decided to search a solution … and I found it.

acfsload, the program who loads the acfs driver makes an OS version check which fails.
This program uses a perl module osds_acfslib.pm which is located on the $ORACLE_HOME/lib directory of the Grid Infrastructure.

In this perl module, you can find at lines 280/281 the perl code that makes this version check. This check is only an “rpm -qa | grep release” !

So if you are on a CentOS 5, you just have to complete this line by adding a centos check :

[root@linux1 lib]# cd /u01/app/oracle/product/11.2.0/grid/lib
[root@linux1 lib]# cp -p osds_acfslib.pm osds_acfslib.pm.ORIG
.../...
[root@linux1 lib]# diff osds_acfslib.pm osds_acfslib.pm.ORIG
281,281
< ($release =~ /redhat-release-5/) || ($release =~ /centos-release-5/))
---
> ($release =~ /redhat-release-5/))

When you have done this add-on, you must finish what the installer have failed ie. copy the acfs kernel modules at the right place and regenerate
kernel module dependencies.

NB: Be careful of your kernel version, in this example, I was using a 2.6.18-92.1.22.el5 kernel.

[root@linux1 lib]# mkdir /lib/modules/2.6.18-92.1.22.el5/extra/usm
[root@linux1 lib]# cp /u01/app/oracle/product/11.2.0/grid/install/usm/EL5 /i386/2.6.18-8/2.6.18-8.el5-i686/bin/*.ko /lib/modules/2.6.18-92.1.22.el5/extra/usm/
[root@linux1 lib]# chmod 744 /lib/modules/2.6.18-92.1.22.el5/extra/usm/*
[root@linux1 lib]# depmod

Now you can execute the acfsload and load the acfs modules without warning:

[root@linux1 lib]# /u01/app/oracle/product/11.2.0/grid/bin/acfsload start -s

You can check everything is loaded with the acfsdriverstate command:

[root@linux1 lib]# cd /u01/app/oracle/product/11.2.0/grid/bin
[root@linux1 bin]# ./acfsdriverstate -orahome /u01/app/oracle/product/11.2.0/grid loaded
ACFS-9203: TRUE

Note, on the next reboot, you have to load the acfs modules, and mount the acfs filesystem.
You can do this by writing you own init shell script based on the chkconfig format, and load it on the desired runlevel.

Advertisement
ASM / ADVM, Linux, Oracle

Acfs is not compatible on CentOS

Leave a comment Posted by on September 15, 2009 
[root@linux1 bin]# ./acfsload start -s
ADVM/ACFS is not supported on centos-release-5-2.el5.centos

Yuk 😦

I’m searching for a solution

ASM / ADVM, Linux, Oracle

Generate a random date in Oracle

Leave a comment Posted by on September 10, 2009

This morning, I had to generate a random date using DBMS_RANDOM package. As the documentation mentions this package can only generate alphanumeric and numeric value, I have to search a little bit.

So the trick for generating a random date is to choose two dates between the random process will choose a date.

Those dates will be translated to Julian format. As the Julian format is the number of days since January 1, 4712 BC, you can use the numeric random generation procedure of DBMS_RANDOM package.

Let’s see how we can generate a random date between 2009 Jan, 1st and 2009 Dec, 31st :

SQL> create table t (d date);
Table created.

SQL> select to_char(to_date('2009/01/01','YYYY/MM/DD'),'J') "20090101",
2 to_char(to_date('2009/12/31','YYYY/MM/DD'),'J') "20091231"
3 from dual;

2009010 2009123
------- -------
2454833 2455197

SQL> begin
2 for i in 1..5 loop
3 insert into t values(to_date(trunc(dbms_random.value(2454833,2455197)),'J'));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.

SQL> select * from t;
D
----------------------
2009-NOV-30 12AM:00:00
2009-MAY-31 12AM:00:00
2009-SEP-01 12AM:00:00
2009-DEC-28 12AM:00:00
2009-MAY-03 12AM:00:00
Development, PL/SQL

Event for crashing an oracle session

Leave a comment Posted by on September 8, 2009

Thanks to Grimm for this information relayed by Laurent Schneider.

This is a tricky method for crashing a session without disconnecting the user :

Session 1 :

SQL> begin
2 while 2>1 loop
3 null;
4 end loop;
5 end;
6 /

Session 2 (logged as user SYS or as user who has privilege to execute the packageDBMS_SYSTEM.SET_EV) :

SQL> exec dbms_system.set_ev(sid,serial#,10237,4,'');
PL/SQL procedure successfully completed.

Session 1 :

ERROR at line 1:
ORA-01013: user requested cancel of current operation

The session is not killed, user is still connected –

If you want to perform other queries, administrator have to invalidate this event (exec dbms_system.set_ev(sid,serial#,10237,0,”);) otherwise you will encountered some “ORA-00604: error occurred at recursive SQL level 1”

SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit
Internals, Oracle