Comments interspersed below:

-----Original Message----- From: Aaron Sherman [mailto:ajs@ajs.com] Sent: Wednesday, August 09, 2000 1:50 PM To: toasters@mathworks.com Subject: Snapshots and Oracle

I have a few snapshot questions for anyone else who's running Oracle:

1. Does a hot backup (as described on http://www.netapp.com/tech_library/3049.html) present any restoration problems? Has anyone done a restore from a hot backup?

We (NetApp) have done lots of restores in testing to ensure that they work as expected. However, I am unaware of a customer having to do this live up to this time. Generally, we consider this a good thing! However, I would strongly suggest you test it in your environment before too long so that you get a feel for how it works and differs from a more "traditional" storage solution.

2. Hot backups are as close to "live" as you're supposed to come, but it seems to me that a nice extra bit of paranoia would be to take snapshots 2-3 times per day of the live database. The image would be consistent, and Oracle would come up off of it as it would from a crash (unlike, say a tar of the data-files which would be junk). The only problem would be that you would loose any half-completed actions which were not correctly tansactionalized. Can anyone comment on this?

Being sure we're on the same page, a hot backup is where the updates have "extra" info written to the redo logs while the datafiles are being manipulated by the backup program. Theoretically enough information exists in the redo logs to completely recover any actions occurring to the datafiles during the backup. So we put the entire database into backup mode, manually take a snapshot on the filer (typically via an 'rsh' from the Oracle server system), and then take the database out of backup. This operation can certainly be repeated several times each day with little adverse effect except for the growing number of disk blocks consumed by each snapshot image. So if the rate of change of the datafiles is moderate, this procedure works just fine. Of course, this also requires correct management of the archived logs.

Following this procedure, there are no half-completed actions that need recovery. And given the short time this requires (usually 2-3 minutes) it is not a bad thing to do several times each day.

However, if your thought was to just let a few snapshots occur using the built-in (cron) mechanism on the filer, then your assumption is correct that a handful of transactions will not have made it into the datafiles. However, all logfile data will be correct since the NVRAM is flushed prior to a snapshot being taken. In this way, while the database is "inconsistent" it is in a state that Oracle should never have a problem of "warm recovering".

3. Has anyone seen roughly how much storage an Oracle snapshot takes? It seems to me that it would be larger than a filesystem snapshot because more blocks are changing. Of course, it depends on your read/write ratio, but is there a general consensus?

Again, hard to say. A Snapshot per se does not take up space. It locks down blocks currently in use and forces updates to these same blocks to be written to new locations. So the additional space occupied by a snapshot occurs after the snapshot is taken and is directly related to the rate of modification to the database. A lot of database customers have just left the default 20% setting in place and not yet found a reason to change it.

Actually, it is probably less than a filesystem snapshot since lots of user applications don't actually modify blocks within a file, they modify a whole file. Most editors, for instance, read a file into memory, allow it to be edited, and then write the file back out from the beginning. So even though we only modified a single word, the entire file is generally written to new disk blocks. Oracle, on the other hand, does modify files in-place and generally only modifies all blocks during certain maintenance operations required to restructure the internal elements.

Database logfiles, on the other hand, are getting completely modified all the time. Of course, they generally are only appended to, so a logfile in use across the snapshot will not occupy too much new space, but once it is copied to the archive directory, the space does not immediately become available. This is one reason why we recommend segregating logfiles from datafiles onto their own volume; their management and space allocation requirements are significantly different.

I've so far been very impressed with my filer. Oracle over NFS sounded outright dumb to me at first, but damn if it isn't faster than local disk.

Glad to hear you like it!

Bruce Clarke Enterprise Solutions Development Manager Network Appliance

Thanks.