Wednesday, September 01, 2004

Current File Metadata, who needs it?

It's been a while since I complained about something not working correctly in Windows 2003.

Normally, when files are written to, you would expect the last modified time in the filesystem to be updated.

Like for example when Oracle does a checkpoint, or log switch it goes out and updates the header of every datafile to indicate the SCN and other information.

So, if you are forcing log switches on a regular basis, the dates of your database files should be more or less up to date (at least to the last log switch).

The other day, one of the sanity checks I have in our backup scripts started complaining that all the database files were too old to be from the right backup. Manually inspecting the files in explorer showed last modified times that were days out of date. But not all the files, and not all the old files had the same last modified times.

This (non-updating last modified times) has always been the case with Oracle tempfiles, but this was the first time we had seen anything like it on a real datafile.

After a bunch of investigation and testing (and some panicked thinking along the lines of 'what if the writes aren't really happening, but Oracle is not getting an error back), it appears that Oracle 9i and newer on Windows 2003 updates the files in some way that isn't reflected in the last modified time. All versions on Windows NT and Windows 2000 updated the file times, as does Oracle 8i on Windows 2003.

So it looks like with 9i and 10g, Oracle writes to the files in some way that doesn't update the filesystem. And then in some situation it is doing something that causes the time to get updated (closing the file, perhaps?)

I'm sure all this is a good optimization from a performance perspective, since it eliminates the work necessary to maintain the last modified times. But it's pretty scary when you can't tell by the dates on your backup files that they were all copied at the same time.

No comments: