I found a funny presentation about Oracle filesystem stuff. It explains inode locking: Oracle Filesystems

A friend has pointed me to a very informative document from HP about the optimal settings of vxfs (HP OnlineJFS) filesystem-related parameters for Oracle: HP-UX JFS mount options for Oracle Database environments

Oracle gathers a wealth of information in the Automatic Workload Repository (AWR). We can query this data to get very important IO access time information.

• Create a working table because depending of the size of the AWR, the queries could take a long time and cause load on the database.

drop table mdecker.filestatistics;
create table mdecker.filestatistics as select t.snap_id, t.begin_interval_time, filename, file#, tsname, phyrds, singleblkrds, readtim, singleblkrdtim, phyblkrd from
dba_hist_filestatxs f ,
dba_hist_snapshot t
where
t.snap_id = f.snap_id and (
begin_interval_time between to_date('05.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('09.11.2007 17:00','DD.MM.YYYY HH24:MI')
OR
begin_interval_time between to_date('12.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('16.11.2007 17:00','DD.MM.YYYY HH24:MI') );

• Create a summary table

create table mdecker.filestatistics_summary as
select snap_id, begin_interval_time, sum(phyrds) phyrds, sum(singleblkrds) singleblkrds from mdecker.filestatistics
group by snap_id, begin_interval_time
order by snap_id

• Query the summary table

select snap_id,
y.begin_interval_time,
phyrds as prev_phyrds,
lead(phyrds) over(order by snap_id) current_physrds,
lead(phyrds) over(order by snap_id) - phyrds,
begin_interval_time as prev_interval_time,
lead(begin_interval_time) over(order by snap_id) current_time,
lead(begin_interval_time) over(order by snap_id) - begin_interval_time,
extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as sec,
extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as min,
(extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time),
(lead(phyrds) over(order by snap_id) - phyrds)/
((extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time))
from filestatistics_summary y

I have come a across a very nasty bug on Oracle RAC on HP-UX Itanium after upgrading to 10.2.0.4. The problem might also occur on Solaris and Linux x86-64. On one of the RAC instances of a 2 node Cluster, the number of open file descriptors of the oracle racgimon process is increasing by 1 every 60 seconds. This means that if your ulimit of open files for a process is set high and the HP-UX Kernel parameter nfiles is also set high, it might take weeks to months until the racgimon process finally hits the limit. If that happens, it can cause instability of the node because no more filedescriptors can be opened system-wide.

How to check, whether my installation suffers from this bug?

It is very easy: do an lsof -p and look for dozens of open filedescriptors of file hc_SID.dat.

– or –

Check logfile $ORACLE_HOME/log//racg/imon_.log:

2008-10-07 13:05:50.879: [ RACG][82] [29827][82][ora.DBNAME.DBNAME1.inst]: GIMH: GIM-00104: Health
check failed to connect to instance.
GIM-00090: OS-dependent operation:mmap failed with status: 12
GIM-00091: OS failure message: Not enough space
GIM-00092: OS failure occurred at: sskgmsmr_13

Is there a patch?
Good news is, there is. The bug is tracked via BugID 6931689 and there is patch #7298531 available to fix the problem with metalink note 739557.1.

From time to time it is necessary to remove sensitive data, e.g. credit card information, birth dates, names, etc. from tables. One way to accomplish this can be done is with parallel DML:

• The first step is to seed the random number generator.

begin dbms_random.seed(‘oiw3284js’);
end;
/

• Then, parallel DML has to be enabled as it is disabled per default. Note the different locking mechanism when DML is enabled.

ALTER SESSION ENABLE PARALLEL DML;

• Next, the update statement is executed. Charactar columns can be set with dbms_random.string whereas number values can be set with dbms_random.value.

UPDATE /*+ PARALLEL(C,8) */ CONTACT C
set EMAIL_ADDR  = dbms_random.string(‘A’, nvl(length(EMAIL_ADDR), 0)),
FAX_PH_NUM  = dbms_random.string(‘A’, nvl(length(FAX_PH_NUM), 0)),
FST_NAME    = dbms_random.string(‘A’, nvl(length(FST_NAME), 0)),
HOME_PH_NUM = dbms_random.string(‘A’, nvl(length(HOME_PH_NUM), 0)),
LAST_NAME   = dbms_random.string(‘A’, nvl(length(LAST_NAME), 0)),
WORK_PH_NUM = dbms_random.string(‘A’, nvl(length(WORK_PH_NUM), 0)),
–nicht verwendet in query
X_BC_NUMBER      = CASE WHEN nvl(length(X_BC_NUMBER), 0) > 0 THEN to_char(round(dbms_random.value(4917888006015004, 7081430000510616), 0)) ELSE NULL END,
X_ACCOUNT_HOLDER = dbms_random.string(‘A’,
nvl(length(X_ACCOUNT_HOLDER), 0)),
X_ACCOUNT_NUMBER = dbms_random.string(‘A’,
nvl(length(X_ACCOUNT_NUMBER), 0)),
X_PHOTO_URL      = CASE WHEN
nvl(length(X_PHOTO_URL), 0) > 0
THEN ‘https://www.myurl.de/’ || to_char(round(dbms_random.value(1111111111111111,  9999999999999999),0))
ELSE NULL END;

commit;

The professional solution to data anonymizing is the new ORACLE ENTERPRISE MANAGER 10 DATA MASKING PACK.

On HP-UX, you can use glance to collect a huge range of OS Statistics and write them in a defined time interval into a file for later analysis. A collection of available metrics is available on the system in /opt/perf/paperdocs/gp/C/metrics.txt or /opt/perf/paperdocs/gp/C/metrics.pdf.

This is an example of IO Monitoring for Filesytems:

glance_filesystem.sh:

nohup glance -aos ./filesystem_advisor.conf -j 60 > glance_output_filesystem_$$.txt 2>/dev/null &

filesystem_advisor.conf:

headersprinted=headersprinted
if headersprinted != 1 then {
print "DATE       TIME     FILESYSTEM                   FIR     LIR     LRBR     LRR     LWBR      LWR     PIR     PRBR     PRR     PWBR     PWR"
headersprinted = 1
}
filesystem loop
{
print GBL_STATDATE|12," ",
GBL_STATTIME|9," ",
FS_DIRNAME|24,
FS_FILE_IO_RATE|8,
FS_LOGL_IO_RATE|8,
FS_LOGL_READ_BYTE_RATE|9,
FS_LOGL_READ_RATE|8,
FS_LOGL_WRITE_BYTE_RATE|9,
FS_LOGL_WRITE_RATE|8,
FS_PHYS_IO_RATE|8,
FS_PHYS_READ_BYTE_RATE|9,
FS_PHYS_READ_RATE|8,
FS_PHYS_WRITE_BYTE_RATE|9,
FS_PHYS_WRITE_RATE|8
}

Used Metrics:

FS_FILE_IO_RATE (FIR)

The number of file system related physical IOs per second directed to this file system during the interval. This value is similar to the values returned by the vmstat -d command except that vmstat reports all IOs and does not break them out by file system. Also, vmstat reports IOs from the kernel’s view, which may get broken down by the disk driver into multiple physical IOs. Since this metric reports values from the disk driver’s point of view, it is more accurate than vmstat.

FS_LOGL_IO_RATE (LIR)

The number of logical IOs per second directed to this file system during the interval. Logical IOs are generated by calling the read() or write() system calls.

FS_LOGL_READ_BYTE_RATE (LRBR)

The number of logical read KBs per second from this file system during the interval.

FS_LOGL_READ_RATE (LRR)

The number of logical reads per second directed to this file system during the interval. Logical reads are generated by calling the read() system call.

FS_LOGL_WRITE_BYTE_RATE (LWBR)

The number of logical writes KBs per second to this file system during the interval.

FS_LOGL_WRITE_RATE (LWR)

The number of logical writes per second directed to this file system during the interval. Logical writes are generated by calling the write() system call.

FS_PHYS_IO_RATE (PIR)

The number of physical IOs per second directed to this file system during the interval.

FS_PHYS_READ_BYTE_RATE (PRBR)

The number of physical KBs per second read from this file system during the interval.

FS_PHYS_READ_RATE (PRR)

The number of physical reads per second directed to this file system during the interval. On Unix systems, physical reads are generated by user file access, virtual memory access (paging), file system management, or raw device access.

FS_PHYS_WRITE_BYTE_RATE (PWBR)

The number of physical KBs per second written to this file system during the interval.

FS_PHYS_WRITE_RATE (PWR)

The number of physical writes per second directed to this file system during the interval.