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.]


“The West Wing” & 2008

December 11, 2006

Yes, it’s years old, and yes, much has been written about it, but in terms of non-HBO quality T.V., “The West Wing” has to take the cake. It’s so captivating, and motivating. And here I am now thinking I want to volunteer on Hillary’s campaign. What’s crazy is that I supported for Bush in the last two presidental elections. (Come on folks, like we wanted Al (B)ore and John Kerry in the White House.)

Where are the exciting candidates these days? Certainly not in the Republican camp.

We have John McCain, who’s (a) ancient; (b) tired; and (c) did I mention ancient? Some 70 years old! I’m not agist. No, really, I’m not. But I think our leaders should be sage-like, be wise, be presidental – and have an iPod. Or at least know how to use email.

[Aside: I once interned briefly for Mario Cuomo, who had the amusing quirk of having his secretary Mary print out his emails every day so he could read them at his desk. He wrote speeches long-hand. Not technically saavy, in other words. Paradoxically, he was into fantasy basketball too. I remember him asking for help to set up his Yahoo account for this, if I recall correctly, but I suspect he used pen and pad for this hobby as well. For the record, he’s a genius and a truly sincere and kind man.]

Then there’s Giuliani, who’s great, and unelectable.

Where are the rising stars?

For the Democrats, Hillary and Obama. Both more attractive in terms of youth and vitality, and both relatively unmarred from major scandal. Hillary of course will forever be shadowed by her health care fiasco, but given more recent moderate leanings, tinged with the right kind of liberalism, well, I have hope.

OS{DBA,OPER} Settings Ignored During Oracle10gR2 Installation on Solaris (x86)

December 8, 2006

As part of my work to deploy Oracle10gR2 on our Solaris 10 (x86) hosts, I ran across an annoying bug in which the OUI ignores the setting of the OSDBA and OSOPER groups the user has specified during installation, and compiles into the Oracle binaries the default name, dba, instead. This bug is significant, of course, because OS-based authentication is essential for connecting to and starting idle instances; if you aren’t a member of the OSDBA group on a given host, you won’t be able to start up an instance on that host.

For many environments, however, the bug doesn’t introduce a true problem: most people tend to use the default dba group anyhow, and thus things are kosher from the get-go.

But in mixed database environments – those which support multiple database vendors – an Oracle OSDBA group name other than dba is often selected in order to distinguish the name of the group from possible DBA groups in the other environments. In these cases, though, the bug introduces a mismatch between what you use as your OSDBA group, and what the OUI actually compiles into the Oracle binaries.

At my company, for instance, we use both Sybase and Oracle, and thus set our OSDBA group name to oradba, in order to distinguish it from the Sybase-related groups. Changing the OSDBA group to dba is not an option. Because of this, in the presense of this bug, installing Oracle10gR2 on Solaris (x86) out-of-the-box left us unable to start new instances. Therefore, it was imperative we fix the bug.

A search on Metalink revealed a variation on the issue (in which it happened under different circumstances but with the same result, namely an inability to connect as a sysdba using OS group-based authentication), but the specified fix was unacceptable: “Change the user’s group to dba.”

After a little digging, I discovered the error was in the $ORACLE_HOME/rdbms/lib/config.s file, a small piece of assembler code in which the OSDBA group is hard-coded. By modifying this file, recompiling the appropriate Oracle binary (namely config.o), and then relinking to the Oracle executables, we were able to resolve the issue. The patch we used to correct the bug follows. (We ran into a different problem during compilation, this one related to the NOKPIC flag options in the A patch for it is included below too.)

The patch we applied to config.s:

 .LV13:        .string "oradba"
 .LV12:        .string "oradba"

The patch we applied to


The command we used to relink:

make -f config.o ioracle

With the new executable and library in place, we were able finally to connect as sysdba via OS-group.

README.TXT 20061207

December 7, 2006

This week’s New York Observer leads with an article on the series of recent White House memo leaks to the New York Times. Good journalism or political placement?

Speaking of the Times, they front a piece on the relatively sparse calendar of the Supreme Court. In other words, they’re being more productive. Yep, this is definitely a problem.

Clipse are (finally) back. And it seems to have been worth the wait.

Speaking of rap-hop, Jay-Z’s first week sales impressed – some 600k in this day and age of bootlegging and illegal downloads. Second week? Not so impressive.

Bloomberg: “We’re not telling you what to eat, you just can’t eat this.”

December 6, 2006

From an A.P. article, regarding New York City’s new ban on restaurants’ use of trans fats in their menus:

Mayor Michael Bloomberg, who banned smoking in bars and restaurants during his first term, said the changes could save lives.”We’re not trying to take away anybody’s ability to go out and have the kind of food that they want in the quantities that they want, but we are trying to make that food safer,” he said.

Many New Yorkers also were all for the ban, saying health concerns were more important than fears of Big Brother supervising their stomachs.

He’s not trying to take away anybody’s ability to go out and have the kind of food they want? Huh?

But I hear no complaints: New Yorkers it seems are becoming increasingly obsequious.

I am all in favor of disseminating information. Requiring a restaurant, say, to provide an ingredient list and the nutrition facts for every meal it serves is a reasonable piece of legislation. Yes, it’s government regulation, and, yes, its enactment may end up in higher prices for consumer. (Though printing fees are going to have to come from somewhere. Although I think forcing these data to be posted on menus is a bit excessive; simply requiring they be available upon request would be sufficient.)

But it wouldn’t take away my choice of what to eat.

But banning outright something that people (i) are generally aware is bad for them; (ii) can freely choose not to ingest; and (iii) have many alternative choices for – well, it’s not only a personal violation, it’s also poor logic. In this case, truly, let the market decide.

Does anyone really believe this is going to make Americans more healthy? Not alone. But if further laws like this follow, it could lead down a dangerous road.

Then there’s the hypocracy: meanwhile bartenders routinely serve quantities of alcohol that land people unconscious in their stairwell, the hospital, and even the grave. (And no, the solution is not prohibition. But we already knew that.)

And I won’t even go into the side issue of whether we can trust the science behind the conclusions on trans fat to begin with. Remember, we once thought lobotomies were a good idea.

So, no, I have a more radical suggestion: AVOID EATING FOOD THAT IS BAD FOR YOU. I would rather pay someone else’s hospital bill through federal health insurance than have the freedom to indulge in my own whims of choice – healthy to my body or not – taken away.

Parternalism is the new fascism.

Read It!

December 6, 2006

I’m often frustrated when I send email to someone only to have him either (i) ignore portions of it completely (which is often a symptom of what I call the trigger-happy reply, in which a user hits the reply button before even finishing the email); or (ii) respond with a message indicating that person didn’t carefully enough read what I wrote.

Folks. Slow down. Read your emails. Read them in full.  Understand what’s being said. Then reply. Scratch that: Read in full, understand, and then reply. (Or use the reply to ask for clarification if understanding is elusive.)

And for goodness sake, try to use correct grammar!  No ugly shorthand and bastardized punctuation (“how r u 2day???!?!?!?”), at the least.  I’m not saying I’m perfect, but I try (and correct mistakes when I discover them).  And this isn’t a complaint about typos: typos happen.  This is a call for folks to write in a way an educated, literate professional would communicate, that is, by being conscious as much as you can about the spelling and grammar in your dispatches.

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.