I just asked about backing up our database server. (There's a new backup 
procedure coming in.) For my trouble, they asked if I could just script 
the database backup to put the files onto a different server.

This is no use at all; I need to backup the entire server, including the 
OS, so that if there's a problem I can just shove in a tape and put 
everything back to normal, rather than manually installing the OS, 
device drivers, Oracle, etc.

But here's the worrying this: The database admin from HQ sent me a copy 
of "the backup script we use here".

And guess what? It isn't a backup. It's an export.

For those of you who aren't Oracle experts:

- A *backup* is where you take the database files and copy them.

- An *export* is where you run a special program that extracts the data 
from the database and puts it into a (binary) flat file.

An export allows you to *recreate* the database, but it isn't "the 
database itself". It contains all the _data_ from the database, but none 
of the _metadata_.

In particular, to use an export file, you "import" it. This is 
equivilent to running lots of SQL "insert" statements. In particular, 
importing data causes triggers to fire. The documentation says you need 
to think about whether triggers should be disabled before the import begins.

How great would that be? You "restore" your "backup" and the system 
modifies it as you restore it!

The thing is, exports are really designed for transferring data from DB 
to DB, possibly running incompatible versions of Oracle. It's not really 
a good idea for "backups". (Although some of the official Oracle 
documentation suggests that is it.)

Now a *real* backup includes all the database configuration metadata and 
so forth. And best of all, if you follow best practices, you don't have 
to shut the database down to back it up, and you can do point-in-time 
recovery. Basically you can rewind the database to any second in time, 
not just to the instant when a "backup" was taken.

Export, on the other hand, runs as a normal database transaction. In 
particular, since it is usually a very *long* transaction, it can cause 
problems with overflowing transaction logs and so forth.

In summary:

- Backups
   - Run while the database is in use. (Slows it down slightly, obviously.)
   - Recover the database to any point in its history, not just when a 
"backup" happened.
   - The restored database is *identical* to the original, down to the 
bit-pattern level.

- Exports
   - Slower than backups. (Produce smaller files though.)
   - Contain "logical" structures only. No undo data, no transaction 
logs, nothing.
   - Running an export is a transaction. The undo buffer has to be large 
enough to copy.
   - Running an import is a transaction. Applicable triggers will fire.
   - Point-in-time recovery is impossible.
   - Can move data from DB to DB. (This is the designed purpose, actually.)

If you do an export and your server goes down, once the server OS is 
back and Oracle is installed, you have to create an empty database, 
recreate all the user account info, and then import all the data. Then 
you need to put back any triggers you're supposed to have, possibly move 
tables into the correct tablespaces, and probably recreate your indexes. 
(Alternatively, keep the indexes in the export and watch it go even slower.)

To recover using a backup, once the OS and Oracle are back, you copy the 
files back to their original locations, and start Oracle. It will 
complain about the files being inconsistent. Tell it to perform a 
recover. (Usually takes a few seconds.) Now open the database. You are done.



I am SORELY TEMPTED to tell this admin what an idiot he is...

...but on the other hand, it's been a while since I did any serious 
Oracle stuff. If I don't get all of my facts straight, it will be *me* 
who looks stupid. I'd hate to write a long email explaining what a 
smart-arse I am and then discover that I got some small technical detail 
wrong and actually the other guy was right all along.

Oh the frustrations of working here! >_<

Post a reply to this message

Invisible wrote:
> I'd hate to write a long email explaining what a 
> smart-arse I am and then discover that I got some small technical detail 
> wrong and actually the other guy was right all along.

This is how you manage these things:

Pretend you're not as smart as the person you're talking to, and you 
don't know as much. (I'll make it short and over-the-top to illustrate, 
but of course if you turn it into two pages you don't need to make it 
sound smarmy.) Assume that the guy is not stupid, but that he has 
requirements you aren't aware of.

"""
Thanks. I looked over your backup plans, and I'd like to understand more 
why your method is superior. I've been using backup method B, which as 
you know saves all the metadata and makes it quick to restore in the 
event of a disaster. I thought this was why we were making the backups, 
but perhaps I'm misinformed.

You're suggesting that we switch to backup method E, which has the 
advantage of letting us restore to later versions of oracle but would 
seem to have the disadvantage that it would take much more effort to 
recover from a disaster. For example, since method E does not save the 
user account information, we would still need to export that 
independently at the time of making backups with method E or risk 
needing to recreate that information while the company waits.

Perhaps the best approach would be to take backup method E each month, 
and backup method B each day (since B allows rolling back to any 
previous point in time), and save the monthly of each indefinitely. This 
would seem to be the best of both worlds.

Please clarify how you would like me to handle the following situations 
using backup method E:
1) Saving user account information in a secure way,
2) Which triggers should be enabled if data needs to be restored,
3) Whether indecies should be included in the exports,
4) How often we should do exports to insure we don't overflow 
transaction buffer pools,
5) ... anything else you think is problematic ...
"""

The first part says "Oh, have we changed what we're doing? Is that why 
you're changing stuff that works?"

The second says "I know there are advantages to your way of doing it, 
but there are disadvantages, and you job is to weigh them and figure out 
whether it's worth the risk to get the advantages."

The third part says "Here's a way to save face and not look like an 
idiot by reversing yourself. We can still make backups the way you want, 
and just not use them. And three months from now, when the system 
becomes unusable for an hour in the middle of the day because of your 
backups, you can tell me to stop doing it that way, as if I hadn't told 
you that in the first place three months ago."

The fourth is "Please put in writing all the solutions to the problems 
you're causing, so I don't get blamed. If you don't know what I'm 
talking about, understand that I know more about this than you, and 
hence maybe you should take my advice."

But if you do it politely, careful of his ego and humble in your 
criticism, it works pretty well. If you say "please explain to this poor 
ignorant peon why you're making such a boneheaded decision", you not 
only look better in the event that you're wrong, but you haven't ticked 
him off by being right. Especially if it goes back and forth a couple 
times, with him changing his story a little each time until he has been 
agreeing with you all along. :-)

-- 
Darren New / San Diego, CA, USA (PST)

Post a reply to this message

Darren New wrote:
> You're suggesting that we switch to backup method E, which has the 
> advantage of letting us restore to later versions of oracle but would 
> seem to have the disadvantage that it would take much more effort to 
> recover from a disaster. For example, since method E does not save the 
> user account information, we would still need to export that 
> independently at the time of making backups with method E or risk 
> needing to recreate that information while the company waits.

And here, you can add something like "Has Oracle improved this feature? 
Have they eliminated the need to reconstruct all the metadata before 
restoring an E backup? If so, could you be so kind as to point me 
towards the new manuals that describe this so I can learn how these new 
features work?"

Stuff like that.

Hope this helps. I know you probably weren't asking for advice, but ... 
you know me by now. ;-)


-- 
Darren New / San Diego, CA, USA (PST)

Post a reply to this message

"Darren New" <dne### [at] sanrrcom> wrote in message 
news:491b16b4@news.povray.org...

> I thought this was why we were making the backups, but perhaps I'm 
> misinformed.
>

I'd leave that out. It could easily be interpreted as sarcastic and with a 
condecending tone. The rest is fine.

Post a reply to this message

Gail wrote:
> 
> "Darren New" <dne### [at] sanrrcom> wrote in message 
> news:491b16b4@news.povray.org...
> 
>> I thought this was why we were making the backups, but perhaps I'm 
>> misinformed.
>>
> 
> I'd leave that out. It could easily be interpreted as sarcastic and with 
> a condecending tone. The rest is fine.

As I said, it's shorter than it should be, and over-the-top to make a 
point.   (It's not as over-the-top as I had expected it to be when I 
started. I guess my anti-smarmy now kicks in without me having to 
consciously rewrite things to get rid of it. :-)

Perhaps something more along the lines of "I was under the impression 
the only reason we were making backups is to deal with disaster 
recovery. Perhaps I am unaware of additional planned uses for this 
data"? That sort of thing, to express that "if what you want is disaster 
recovery, this is what you need, but what else do you want?"

-- 
Darren New / San Diego, CA, USA (PST)

Post a reply to this message

Darren New wrote:

> And here, you can add something like "Has Oracle improved this feature? 
> Have they eliminated the need to reconstruct all the metadata before 
> restoring an E backup? If so, could you be so kind as to point me 
> towards the new manuals that describe this so I can learn how these new 
> features work?"
> 
> Stuff like that.

Actually, EXP.EXE is deprecated now in favour of the new data pump 
feature. (But I don't know what version of Oracle they have, and hence 
whether that feature is available to them.)

> Hope this helps. I know you probably weren't asking for advice, but ... 
> you know me by now. ;-)

If you don't know my by now... wowo...

...GOD DAMNIT!! NOW I HAVE THAT SONG IN MY HEAD! >_<

Post a reply to this message

Darren New wrote:

> This is how you manage these things:
> 
> Pretend you're not as smart as the person you're talking to, and you 
> don't know as much. (I'll make it short and over-the-top to illustrate, 
> but of course if you turn it into two pages you don't need to make it 
> sound smarmy.) Assume that the guy is not stupid, but that he has 
> requirements you aren't aware of.
> 
> The first part says "Oh, have we changed what we're doing? Is that why 
> you're changing stuff that works?"
> 
> The second says "I know there are advantages to your way of doing it, 
> but there are disadvantages, and you job is to weigh them and figure out 
> whether it's worth the risk to get the advantages."
> 
> The third part says "Here's a way to save face and not look like an 
> idiot by reversing yourself. We can still make backups the way you want, 
> and just not use them. And three months from now, when the system 
> becomes unusable for an hour in the middle of the day because of your 
> backups, you can tell me to stop doing it that way, as if I hadn't told 
> you that in the first place three months ago."
> 
> The fourth is "Please put in writing all the solutions to the problems 
> you're causing, so I don't get blamed. If you don't know what I'm 
> talking about, understand that I know more about this than you, and 
> hence maybe you should take my advice."
> 
> But if you do it politely, careful of his ego and humble in your 
> criticism, it works pretty well. If you say "please explain to this poor 
> ignorant peon why you're making such a boneheaded decision", you not 
> only look better in the event that you're wrong, but you haven't ticked 
> him off by being right. Especially if it goes back and forth a couple 
> times, with him changing his story a little each time until he has been 
> agreeing with you all along. :-)

In fairness, as I understand it the guy is an SQL Server expert, not an 
Oracle expert.

Given that the "solution" I've been offered doesn't actually solve the 
problem of interest anyway, I think I shall just quietly leave things as 
they are. Pointing out what we could be doing differently is likely to 
get me yelled at - especially as Oracle's own documentation hints that 
exporting is a way to "backup" a small database.

(I really love the way it describes "small" as "less than, say, 50 GB". 
I'd like to know in which universe 50 GB can be considered "small"...)

Post a reply to this message

Invisible wrote:

> I am SORELY TEMPTED to tell this admin what an idiot he is...

Amusing thing: I deleted the email instead of sending it. Yet Outlook 
still claims I replied to the email. Neat, eh?

Post a reply to this message

"Invisible" <voi### [at] devnull> wrote in message 
news:491bff7c$1@news.povray.org...
>
> In fairness, as I understand it the guy is an SQL Server expert, not an 
> Oracle expert.

No fairness.
SQL Server doesn't have the dump feature that he's proposing you use (well, 
you can do it, but it's non-trivial). SQL Server backups are full database 
backups and are online backups (can be done while DB is in use) and restore 
with the DB in exactly the state that it was at the time of the backup

Post a reply to this message

>> In fairness, as I understand it the guy is an SQL Server expert, not 
>> an Oracle expert.
> 
> No fairness.
> SQL Server doesn't have the dump feature that he's proposing you use 
> (well, you can do it, but it's non-trivial). SQL Server backups are full 
> database backups and are online backups (can be done while DB is in use) 
> and restore with the DB in exactly the state that it was at the time of 
> the backup

...so, rather like real Oracle backups then.

Presumably the guy did a little researching on Oracle and found *a* way 
to make a copy of the data, and that's what we're using.

Using a logical backup rather than a physical backup does take up less 
disk space. However, it takes more time to backup and restore (and these 
operations are logged transactions, and triggers can fire, etc.) Myself 
I'd prefer a real physical backup...

Presumably the guy just isn't aware of the tradeoff. Or maybe he is, and 
he seriously thinks the extra complication is justified for the disk 
savings?

Post a reply to this message