ORACLE-L: On ZFS & Snapshots

December 12, 2006

I posted the following to oracle-l earlier today, in reference to a discussion I’d started about using Solaris 10’s ZFS filesystem (particularly its snapshotting ability) in tandem with an online Oracle instance for use in refreshing a copy of that instance on a development server.

ZFS Snapshot-Based Refreshes

The goal is to find a fast way to periodically update a pre-prod environment using a copy of the current production database, and to do so with minimal production outage and without requiring a long restore, recover operation.

So we have a production environment, running on ZFS, from which we take a base snapshot and populate the target server. (This is effectively an entire copy of the filesystem, and will thus take some time, but will only need to be performed once.) Note that the copy of the snapshot is never opened by Oracle – it’s merely a backup of the production database as of a point in time.

Going forward, we periodically snapshot production (without needing to bring it down) and applying that snapshot incrementally to the most recent target snapshot copy. If we do this frequently enough we end up with a series of small updates to the copy.

And all of this, for our use case, is to simplify the occasional rebuilding of the pre-production database. To do the rebuild, we simply clone the latest ZFS snapshot on the target, freeing us from the need to restore (duplicate) from RMAN, and allowing us to avoid any production down time. It’s just a clone of the most recent snapshot on the target.

But, of course, that clone is inconsistent, in that the snapshot was taken when the datafiles may have been in an inconsistent state (SCN wise). Because we took the datafile snapshot first, though, we can recover them using the snapshotted logs, allowing us to open the database. Voila!

(In case it’s not clear, pre-production will diverge from production at times, hence the need for the refresh. This is an operational requirement here.)

I haven’t yet heard an argument as to why this wouldn’t work, and I’m fairly convinced, indeed, that it will.


I posited that a hot backup via BEGIN BACKUPEND BACKUP would not allow one to recover the associated datafile(s) to a point in time between those calls.

I found this note in the Oracle documentation that led me to that hypothesis (emphasis mine):

Like any other backup, an online backup is composed of image
copies of all the datafiles that make up a tablespace. The point to
remember is that as these files are being backed up they may also be
in the process of being written to by the detached process DBWR. Some
characteristics of an online backup are

    o  users are allowed normal access to all online tablespaces;
    thus, users can access the tablespace being backed up.

    o  when used for recovery the backup can only be used to return
    to the most recent state of the database, not to a previous

    o  only the database files comprising a tablespace are backed
    up; the log files are being archived and the control file
    does not need to be backed up if there had been no
    structural change to the database since the last control file

Yet if every change during the hot backup copies the changed block to the log, I’m not exactly clear why PITR wouldn’t be possible. It’s a lot more data in the log, sure, but the implication is that every SCN reflected in the redo stream is associated with the block that changed, indeed, the entire block change.

So perhaps I am misinterpreting that statement.

[Note that the statement The point to remember is that as these files are being backed up they may also be in the process of being written to by the detached process DBWR does not apply to the ZFS scenario I outlined above, hence my conclusion we could do without ever having the bring the datafiles into backup mode.]


Directory Permissions and Mount Points

December 6, 2006

Here’s a good interview question for those looking to hire a UNIX systems administrator. For anyone versed in that OS, the answer should be immediately apparent.

Q: Explain the following:> groups
staff donahuea> ls -ld /var/opt
drwxr-xr-x   3 root     root         512 Dec  4 10:17 /var> ls -ld /fs
drwxrwsr-x   5 root     root         512 Dec  4 11:53 /> ls -ld /
drwxr-sr-x  34 root     root        1024 Nov 29 13:18 /> cd ..
..: Permission denied.

A: /var/opt is a filesystem mount point, and the underlying directory for that mount point lacks the read and execute permissions for other users:> df -kFufs | grep /var/opt
/dev/dsk/c0t0d0s4    32275533   32025 31920753     1%    /var/opt> sudo umount /var/opt> ls -ld /var/opt
drwx------   3 root     root         512 Dec  4 10:13 /var/opt

Another relevant question here is, how can I look at the permissions of the underlying directory without having to unmount the filesystem which uses it as a mount point?

It’s not easy, but, on Solaris at least, my colleague and I discovered a way. He’s already posted a small note on it here.

Contextual grep

December 6, 2006

One of the things I’ve always wanted in the UNIX grep utility is the ability to print the context of matched lines, that is, to print the lines before and/or after the matching line as well as the line itself. This is particularly useful in searching for multiline log or configuration entries. diff has this option (-u). Why not grep?

Well, actually, it does. GNU grep contains the following options, which allow one to print the context of matched searches:

Context control:
  -B, --before-context=NUM  print NUM lines of leading context
  -A, --after-context=NUM   print NUM lines of trailing context
  -C, --context=NUM         print NUM lines of output context
  -NUM                      same as --context=NUM
      --colour[=WHEN]       use markers to distinguish the matching string
                            WHEN may be `always', `never' or `auto'.
  -U, --binary              do not strip CR characters at EOL (MSDOS)
  -u, --unix-byte-offsets   report offsets as if CRs were not there (MSDOS)

[The gnu command here is actually a simple wrapper that looks for a GNU version of the specified program and runs it if found. Otherwise the non-GNU version is executed.]

This is yet another example of why it’s useful to review the options of commonly used commands.  I’ve been dealing with UNIX for years, and yet I learn something new every day.