Another Interesting Issue

On the same database of which i’ve already blogged i’ve encountered another interesting issue. Some times the database get down without messages on alert.log. Database is used by two applications, on log of one of this applications there was:

java.sql.SQLException: ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01114: IO error writing block to file 201 (block # 188712)
ORA-27070: skgfdisp: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.

I’ve also opened a SR on Metalink but with only suggestion that it was an hardware problem. The strange things were two:

1. On alert.log there was nothing
2. The file indicated by the message in application log (#201) did not exist on the database

After a while we was able to reproduce the problem, it was a query with a group by on a large data set. After a couple of test my intuition was that the problem were the TEMPORARY tablespace, so i’ve created a new TEMPORARY tablespace, i’ve setted it as new default temporary tablespace ad re-tried the test with success. It is clear that there is a bug that cause Oracle db (9.2.0.1) to crash with particular corruption on TEMPORARY tablespace.

Undo Tablespace Corruption

Some time ago i’ve encountered a problem with a database of a customer. It is Oracle 9.2.0.1 on Windows 2000 with Oracle Fail Safe.

On Alert.log we found:

KCF: write/open error block=0x4351 online=1
file=2 O:\ORACLE\ORADATA\GEOP\UNDOTBS01.DBF
error=27070 txt: 'OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 2) The system cannot find the file specified.'
Automatic datafile offline due to write error on
file 2: O:\ORACLE\ORADATA\GEOP\UNDOTBS01.DBF
Tue Jul 10 02:08:42 2007
Errors in file o:\oracle\admin\geop\udump\geop_ora_844.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:\ORACLE\ORADATA\GEOP\UNDOTBS01.DBF'
ORA-00372: file 2 cannot be modified at this time
ORA-01110: data file 2: 'O:\ORACLE\ORADATA\GEOP\UNDOTBS01.DBF'

where the two lines

ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'O:\ORACLE\ORADATA\GEOP\UNDOTBS01.DBF'

were repeated thousands of times.

at same time in windows event viewer:

Event Type: Warning
Event Source: Ftdisk
Event Category: None
Event ID: 50
Date: 07/07/2007
Time: 07:55:15
User: N/A
Computer: GEOCALL2
Description:
{Lost Delayed-Write Data} The system was attempting to transfer file data from buffers to \Device\HarddiskVolume5. The write operation failed, and only some of the data may have been written to the file.
Data:
0000: 00 00 04 00 02 00 56 00 ......V.
0008: 00 00 00 00 32 00 04 80 ....2..€
0010: 00 00 00 00 00 00 00 00 ........
0018: 00 00 00 00 00 00 00 00 ........
0020: 00 00 00 00 00 00 00 00 ........
0028: 0e 00 00 c0 ...À


and repeated messages


Event Type: Warning
Event Source: Disk
Event Category: None
Event ID: 51
Date: 07/10/2007
Time: 02:21:01
User: N/A
Computer: GEOCALL2
Description:
An error was detected on device \Device\Harddisk3\DR3 during a paging operation.
Data:
0000: 04 00 22 00 01 00 72 00 .."...r.
0008: 00 00 00 00 33 00 04 80 ....3..€
0010: 2d 01 00 00 0e 00 00 c0 -......À
0018: 00 00 00 00 00 00 00 00 ........
0020: 00 00 00 00 00 00 00 00 ........
0028: 04 00 00 00 03 00 00 00 ........
0030: 00 00 00 00 2a 00 00 00 ....*...
0038: 00 08 00 00 00 00 00 00 ........
0040: 2a 00 02 4f db 2f 00 00 *..OÛ/..
0048: 08 00 ..


I’ve to say that this Oracle installation is not very lucky, messages of disk problems in event viewer sometimes returns, but hardware vendor tell us that there are no problems on the hardware.

Another thing that i’ve to remember is to read with my eyes the alert.log. In fact i was called by a collaborator and i did not see the line

Automatic datafile offline due to write error on


Immediately i’ve thought a corruption on the file, i’ve created a new UNDO tablespace, i’ve changed UNTOTBS parameter to point to the new tablespace. Then we tried to remove the old tablespace but we got a message that a rollback segment was “active”. In V$TRANSACTION there was no records. I was not able to understand why Oracle was telling us that. The database could be opened but the application on one step was still given an error message by Oracle stating that old undo tablespace datafile were not available. So we decided to restore from backup the tablespace, we recovered. After that i onlined the tablespace, i made a “select count(*)” from a table (the table used by application that had given the error). After that i’ve been able to drop tablespace with datafile.

Conclusion

My description has been confused but the conclusion, and the lesson i’ve learned is that UNDO TABLESPACE may contain data needed to the integrity of the database. I think that is the case of “delayed block cleanout”. If there are active trasactions that is obvious and it is visible by V$TRANSACTION system view, but in the case of “delayed block cleanout” i think that information is not easily available.

Windows or *NIX?

I’ve found very interesting the discussion on comp.databases.oracle.server started from a migration question and continued on a comparison between Windows and Linux or Unix, at least as Oracle platforms. The battle ended with yet another no-winner contest. What the posters say is for me very interesting. The OS religious wars are really a no-sense, but a lot of things in this world are no-sense, also me writing here.

So, i’m back to what Tom has already said: there is no a better os, you must choose that with wich YOU are more comfortable, there are no other scientific and absolute motivations to prefer one over another. I feel better with Linux, but i’ve learned a lot of things about Windows that make me better.

Personally i’ve to say that Windows has good performance, but Oracle threaded architecture on Windows it seems not so good as multi-process architecture on Unix.

Pro-Unix i want remember always a more consolidated architecture. (Unix is here from 1970 🙂 )

To RAC or not to RAC

Today i’ve re-read mogens nogard (his name is unwritable for me, but also for him)  post on high avalability of last month. It is very interesting what mogens says.  I’ve recently read the book “Oracle Insights” from witch i desume that mogens is a step above others.

I completely agree with the quote “Complexity is the enemy of availability” , i’m conservative. So i think that technologies pushed to simplify management may became a boomerang.  On the other hand i think that RAC, with 10g standard edition has reason to be. It give us a little scalability at a competitive cost.

What mogens says in a comment is the real point:

“For political reasons you might have to implement all sorts of things, and I still haven’t found an effective way of preventing that from happening.”

Marketing and politics really drive our (mine) managers so i have to implement things that for me give no advantages.

however i’ve to say that my little experience with RAC, not about HA, is at last good.  What remains are all the bugs that we can encounter, with RAC as with Standalone instance.

RAC Virtual IP

At first installation of Oracle 10g RAC which i’ve seen, made by an Oracle consultant about two years ago (version 10.1.0.2.0 on Linux Suse EL on x86_64 platform) , the network configuration of Operating System were as this:

eth0: 10.0.100.1 private interconnection interface
eth1:198.168.33.1:”public” interface

apart from the fact that also with 10.2 release VIPCA complains that “public” interface has not a pubblic class IP, i’ve noticed another problem that i don’t know how to resolve: virtual IP is being created on first interface without possibility to change it, that is “eth0” that is the private interface. In a correct configuration such interface would be connected to a switch with other private interfaces of other nodes of the cluster, separated from “public” network to which would be connected “public” interface. With such architecture i would have:

eth0: 10.0.100.1 private interconnection interface
eth0:1 192.168.33.3 RAC VIRTUAL IP
eth1:198.168.33.1:”public” interface

VIP on eth0, on network card attached on private switch, not reachable from clients connected to public switch. NO GOOD. The only workaround that i’ve found is to reconfigure operating system (in new installations) to put private ip on lower interfaces (eth0) and public IP on higher interfaces.

Do anybody know a remedy for my first installation? Note, in that installation all interfaces are connected to the same switch and so all goes well. Something is not as high available as it may be, there are not redundant network interfaces.

hwclock and date

We use RAC with Linux, Standard Edition RAC, 10g with 2 nodes with no more than 2 CPUs per machine. Today i’ve encountered a systemistic anomaly. Some time ago i’ve noticed that time in a RAC instation was different between the two nodes. SO is Linux Suse EL 9. System administrator did not configured a sincronization mechanism for time so during a downtime period i manually setted time of one server “equal” to that of the other with “date” command. This morning someone rebooted (it really was a black-out) the machines and in /var/log/messages of one machine i’ve noticed a 20 minutes lag back in time:

5:45 ..
5:49 ..
6:02 ..
shutdown
5:41 ..

And so i’ve remebered what i’ve already noticed with my test of RAC installation on VMWARE virtual machines
System Clock is not Hardware Clock. When linux boots, it sets system time equals to hardware time and then it may diverge. So the moral is; use hwclock