Urgent from Sybase: Possible data integrity issue affecting versions of Adaptive Server Enterprise 12.5.x, 12.5.3a, and 15.0, when using block devices on UNIX & LINUX

Summary: This document describes a potential data integrity issue with Adaptive Server Enterprise (ASE) that may occur in the event of a system crash when using block devices as database devices on UNIX and LINUX environments. This issue affects ASE versions 12.5.1, 12.5.2, 12.5.3, 12.5.3a, and 15.0. Versions of ASE prior to 12.5.1 are unaffected. This issue does not affect Windows environments, nor does it affect block devices on HP-UX when used in conjunction with the ASYNC driver.

Contents

This document contains the following sections:

• Customer Alert
• Recommendation

Customer Alert

A system crash may lead to data integrity issues when using a block device for a database device. The affected versions of ASE treat a block device in the same way as a character device. ASE assumes that all completed writes have been committed to the disk. When using a block device however, some modified pages may be retained in the operating system's cache and not written to the disk. In the event of a power failure, or other system crash, the cached modifications may be lost leading to data inconsistency. This issue does not affect ASE's temporary databases, should they reside on block devices, as these are recreated during the server startup process.

This issue does not affect block devices used on HP-UX as long as they are used in conjunction with the ASYNC driver.

If your system has not crashed then you will be unaffected by this issue. If your system has crashed you should run DBCC CHECKSTORAGE or DBCC CHECKDB to identify any structural database inconsistencies.

This issue is being tracked under Sybase CR# 406473. This CR is fixed in the following EBFs, or ones that supersede them.

Notes:

• The EBFs for ASE 15.0 are not yet available. These are due to be available late May 2006.
• Currently there is no fix available for ASE 12.5.3a. The workaround should be used instead.

Once you have completed the upgrade to one of the EBFs listed above you MUST complete the following steps on all database devices mapped to block devices that were created before the upgrade was done, except the master device.

1. Turn on the dsync attribute on all applicable database devices

   sp_deviceattr dev_name, dsync, true
   go

2. Shut down the ASE server
3. Restart the ASE server

Symptoms: Check the description column in the output of sp_helpdevice for all database devices that use a block device as their physical device. The description column should contain " dsync on". If any database device mapped to a block device shows " dsync off" use sp_deviceattr to change it to " dsync on".

use master
go

sp_helpdevice dev1
go

device_name physical_name      description                                 status cntrltype device_number low      high
----------- ------------------ ------------------------------------------- ------ --------- ------------- -------- --------
dev1        /dev/dsk/c0t15d0s4 special, dsync off, physical disk, 20.00 MB 2      0         2             33554432 33564671

(1 rows affected)

Workaround: If you intend to remain on one of the affected versions of ASE, or if the EBF is not yet available for the version you are using, you must switch your database devices to either character devices or file system files to avoid the possibility of encountering this issue.

The examples below show changing from a block device to a character device. The steps required to perform the actions below are specific to a particular operating system. The examples given are based on the SUN Solaris operating system. For other operating systems the same concepts should be used but the device names, etc will differ.

OS device files used are soft links

If the device name used is a soft link to an OS defined device name e.g. /opt/sybase/data/dev1 -> /dev/dsk/c0t15d0s4, the steps to change this are:

1. Shut down the ASE server
2. Alter the symbolic link to point to the character interface of the same device e.g. /opt/sybase/data/dev1 -> /dev/rdsk/c0t15d0s4

   ls -l /opt/sybase/data/dev1
   lrwxrwxrwx 1 sybase sybase 3 Mar 1 2006 /opt/sybase/data/dev1 -> /dev/dsk/c0t15d0s4

   rm /opt/sybase/data/dev1

   ln -s /dev/rdsk/c0t15d0s4 /opt/sybase/data/dev1

3. Restart the ASE server

OS device files used are block devices

If the device name used is an OS defined block device e.g. /dev/dsk/c0t15d0s4, the steps to change this are:

1. Shut down the ASE server
2. Boot the ASE server in single-user mode using the -m parameter
3. Alter the entries in the sysdevices table to point to the character interface of the same device e.g. /dev/rdsk/c0t15d0s4

   use master
   go

   sp_configure "allow updates",1
   go

   begin tran
   go

   update sysdevices set phyname = "/dev/rdsk/c0t15d0s4" where phyname = "/dev/dsk/c0t15d0s4"
   go

   -- make sure the update is correct, commit tran if it is, otherwise rollback

   select phyname from sysdevices where phyname like "/dev/%dsk/c0t15d0s4"
   go

   commit tran
   go

   sp_configure "allow updates",0
   go

4. Shut down the ASE server
5. Restart the ASE server in multi-user mode

Recommendation

Sybase strongly recommends that either the workaround above is used or an upgrade is done to a version of ASE that has CR# 406473 fixed in it.

EBFs are obtained from the Sybase EBFs and Maintenance site.

http://downloads.sybase.com/

Follow the instructions in the EBF cover letter to install the EBF.

If you require further assistance please contact your local support center. The contact numbers can be found in the About Support section under Support & Services at the www.sybase.com website.

http://www.sybase.com/contactus/support

Copyright © 2006 Sybase, Inc. All rights reserved.