Dirk-Jan's blog

During the installation of Oracle 11gR2 on Red Hat Enterprise Linux 5 I stumbled upon a incorrect default setting for ASM disk discovery when using ASM lib. The discovery path is set to ‘/dev/raw/sd*’, which is incorrect because ASM lib creates the devices under ‘/dev/oracleasm/disks’.

Another thing to be aware of is the ownership of the disks. You need to run ‘/etc/init.d/oracleasm configure’ in order to configure the owner. If you just hit return the ownership of the disks is set to root. Because you will start the Oracle installation as user oracle the disks will not appear in the discovery step during the installation.

Incorrect ownership:

# ls -l /dev/oracleasm/disks/*
brw------- 1 root root 8, 65 Jan 20 15:27 /dev/oracleasm/disks/VOL1
brw------- 1 root root 8, 49 Jan 20 15:27 /dev/oracleasm/disks/VOL2
brw------- 1 root root 8, 33 Jan 20 15:27 /dev/oracleasm/disks/VOL3

Configure Oracle ASM Library:

# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: oinstall
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

Correct ownership:

# ls -l /dev/oracleasm/disks/
total 0
brw-rw---- 1 oracle oinstall 8, 33 Jan 20 15:27 VOL1
brw-rw---- 1 oracle oinstall 8, 49 Jan 20 15:27 VOL2
brw-rw---- 1 oracle oinstall 8, 65 Jan 20 15:27 VOL3
[root@porad001 ~]#

Hope it saves you some time!

If you need to get rid of some table rows, in my case a Sun IDM logging table, you can use the following steps.

1. Create a backup in case you have done some thing wrong u can always revert back.

create table waveset.log_backup as select * from waveset.log;

2. Check if these are the records you want to keep and rest all delete.

select * from waveset.log_backup where repomod between'26-MAR-09' and '01-MAY-09';

3. Delete logging from timestamp type collumn ‘REPOMOD’ between dates:

delete from waveset.log_backup where repomod between'26-MAR-09' and '01-MAY-09';

4. if this works do the same on the real table instead of the backup table.

Optionally you can repeat the same trick on the waveset.syslog table, which also can grow to huge proportions.

For a Tomcat installation I needed to connect to a Oracle RAC database backend. I figured out the correct connection string, shown below:

jdbcUrl="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac01)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=rac02)(PORT=1521))(LOAD_BALANCE=on)(FAILOVER=on)(CONNECT_DATA=(SERVICE_NAME=orcl)))"

If you want to know what SQL statements are actually being executed by an application because something goes wrong and there is no clear error message explaining what’s happening you can use SQL Trace. SQL Trace can be enabled on the current session, the session of another user or the complete database.

To enable SQL Trace on the current session

alter session set sql_trace = true;

You can find the output of the trace in the udump directory of the database (see the value of parameter user_dump_dest). You can chose to give the tracefile another name, see the next example:

alter session set sql_trace = true;
alter session set tracefile_identifier = customname;

When done tracing you’ll need to stop the trace:

alter session set sql_trace = false;

To enable SQL trace on another session
You’ll first need to know which session to trace, by looking up the sid and serial#:

select s.sid,
s.serial#,
s.osuser,
s.program
from v$session s;

Start the trace on sid number 8 and serial 13607:

alter system set timed_statistics = true;
execute dbms_system.set_sql_trace_in_session(8, 13607, true);

Carry out the to trace actions and stop the trace:

execute dbms_system.set_sql_trace_in_session(8,13607, false);

You can find the output again in the udump destination.

Trace the complete database

Start SQL Trace on the complete database:

alter system set sql_trace = true scope=memory;

Stop SQL Trace on the complete database:

alter system set sql_trace = false scope=memory;