More DST and Database Systems

Thursday, January 25, 2007

I gathered some notes in an earlier post on how the new Daylight Savings Time change effected my Oracle database systems. Now I had to do the research for MySQL and MS SQL Server.

Found this forum thread which references this link. Whether you need to perform any actions is dependent upon whether your OS includes zoneinfo files or not (e.g. Microsoft Windows, HP-UX). My database installations appear to use the system to obtain the time information. I don’t believe there is a need for me to populate the MySQL time zone tables using the mysql_tzinfo_to_sql utility.

mysql> SELECT @@global.time_zone, @@session.time_zone;
| @@global.time_zone | @@session.time_zone |
| SYSTEM             | SYSTEM              |
1 row in set (0.02 sec)

MySQL documentation about timezones can be found here for 4.1 and here for 5.0.

MS SQL Server
Found this link for Microsoft products, but SQL Server does not appear to be listed as an affected product. An MSDN forum posting has a response saying SQL Server is not affected because it uses time information from the OS. This reason is also mentioned on Chad Boyd’s blog post.

DST and my Oracle environment

Wednesday, January 24, 2007

How does the new Daylight Savings Time change effect my Oracle database systems? Not sure why I didn’t ask that question a little sooner, but fortunately I still time to research it and address it well before March 11. There’s an overwhelming amount of information on Metalink.

Start Here
402742.1– USA 2007 DST Changes: Frequently Asked Questions for Oracle RDBMS

Other Important Notes
359145.1 – Impact of 2007 USA daylight saving changes on the Oracle database
357056.1 – Impact of changes to daylight saving time (DST) rules on the Oracle database
396670.1– Usage of utltzuv2.sql before updating time zone files in Oracle 9
396671.1– Usage of utltzuv2.sql before updating time zone files in Oracle 10
396387.1– Workarounds when Database time zone patches are not available for your patchset

There are potentially three areas to be patched, the Oracle database, the Oracle JVM, and the Oracle Client.

Oracle Database
There is a required pre-install script called utltzuv2.sql that needs to be executed to determine what data is affected. The data needs to be saved off before the DST patch is applied. There is a utltzuv2.sql script for Oracle 9.2.0.x (Patch 5548107) and Oracle (Patch 5601428). See Metalink Note 359145.1 for more information.

Regardless whether any data is affected, it’s probably a good idea to apply the patch. Recent threads on the Oracle-L seem to say you can apply the patch (using opatch) while the database is running and there is no need to restart the database.  I have found this to be true (no database restart) on my Solaris and HP-UX servers, but that does not appear to be the case for my Windows servers.

For my databases on Solaris and HP-UX PA-RISC, I am applying the version 4 timezone files patch (Patch 5632264).

Oracle JVM
Metalink Note 397770.1explains how to determine if the Oracle JVM is installed in your database. If the Oracle JVM is not installed or you have the most current patchsets (e.g.,, installed, then no patching is necessary. This information is detailed in Metalink Note 359145.1.

If you have an database with the Oracle JVM installed, then Patch 5075503 can be applied.

Oracle Client
Metalink Note 396426.1 explains the effects on the client software of the timezone patches.

OTN Techblog
Chris Foot Blog – 2007 Daylight Saving Time Changes
Chris Foot Blog – 2007 Daylight Saving Time Changes Update
Oracle-L Archive – Oracle and DST changes
Daylight Saving Time 2007 Forum on Metalink Now Open
OTN – Oracle Database Daylight Saving Time Update Guide
Chris Foot Blog – DST Deadline – One Week and Counting

2007 Daylight Savings Time E-Business Customer Webcast

MySQL – Restore from Compressed Backup

Monday, January 15, 2007

Copied a compressed database backup from the Production server to the Development server. The database backup file was compressed using bzip2 while piping from mysqldump.

mysqldump -u root -p –databases perfparse | bzip2 -z1 > perfparse.bkp.bz2

bzip2 Options

z = compression
1 = fastest compression (compared to 9 = best compression)

Used bunzip2 to uncompress while piping to mysql due to limited disk space.

bunzip2 < perfparse.bkp.bz2 | mysql -uroot -p

Very basic information, but I do use this blog for referring myself and others back to my notes.

Compressing mysqldump output
bzip2 Home

MySQL Falcon Storage Engine

Tuesday, January 9, 2007

Saw Sean Hull’s post regarding the new MySQL storage engine called Falcon. For now it is only available in a “forked release of MySQL 5.1”. Additional documentation can be found in the MySQL 5.1 Reference Manual.

Two New MySQL Storage Engines
Understanding the Falcon Transaction Storage Engine
New Falcon White Papers ready!

Oracle9i Client for SUSE Linux

Monday, January 8, 2007

This is the first time I have had to install the Oracle9i Client software on Linux for a development team. The version of Linux is SUSE Linux Enterprise Server 9 (SLES 9). SUSE publishes an installation guide for installing both the Oracle9i and Oracle10g Client software on SLES 9.

One of the steps requires you to download and install (as root) an rpm called orarun which simplifies the pre-installation tasks for the OS. Metalink Note 266049.1 provides a good description on what the orarun package provides such as:

-> sets the Oracle environment variables for each user, like ORACLE_HOME and PATH

-> sets the recommended kernel parameters, e.g. SHMMAX

-> provides for automated start/stop of Oracle processes at system startup/shutdown

Before installing, LD_ASSUME_KERNEL environment variable needs to be set.


I didn’t understand why this variable needed to be set, but searching Google I found the information from Red Hat and Novell.

The Client base installation for Linux x86 was version I installed the patch on top of that without any problems.