Data … as usual

All things about data by Laurent Leturgez

Category Archives: Oracle

Delete ADR "problems"

Leave a comment Posted by on November 5, 2009

When you are using adrci to interface adr you can easily purge incidents, health monitor reports etc…

… but you can’t purge problems:

adrci> help purge
Usage: PURGE [[-i  |  ] |
[-age  [-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]]]:
Purpose: Purge the diagnostic data in the current ADR home. If no
option is specified, the default purging policy will be used.
Options:
[-i id1 | id1 id2]: Users can input a single incident ID, or a
range of incidents to purge.
[-age ]: Users can specify the purging policy either to all
the diagnostic data or the specified type. The data older than
ago will be purged
[-type ALERT|INCIDENT|TRACE|CDUMP|HM|UTSCDMP]: Users can specify what type of
data to be purged.
Examples:
purge
purge -i 123 456
purge -age 60 -type incident

No trace of problem purge 😦

If you want to delete those problems, you have to know about a SQL interface directly in adrci. (The “tables” represents XML files mappings, these files are located in the repository).

So, I paste above a little example of a DELETE in adrci interface:

adrci> delete from problem;
 3 Rows Deleted
 adrci> commit;
 Transaction Committed
 adrci> show problem
  
 ADR Home = /u01/app/oracle/diag/rdbms/racdb/racdb1:
 ***********************************************************
 0 rows fetched

Be careful, if you delete problems, you will loose the problem memory of your database.

Administration, Oracle

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.

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

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

Newer posts →