![](/i/fill.gif) |
![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
"Darren New" <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
Gail wrote:
>
> "Darren New" <dne### [at] san rr com> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
"Invisible" <voi### [at] dev null> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
>> 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
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |
|
![](/i/fill.gif) |
| ![](/i/fill.gif) |
|
![](/i/fill.gif) |