Jun 22: Sigh

Whilst I might admire Alex G's new BAAG party, I have less lofty ideals. My personal battle is against people who keep offering the following sage advice to the crucial question - How do I backup my database?

As UNIX user oracle run: 

TODAY=`date "+%Y-%m-%d"`
FILE=$TODAY-$ORACLE_SID.dat

exp system/manager file=$FILE consistent=y full=y compress=n
gzip $FILE


There are so many things I could say about that web page, I wouldn't know where to begin :-(

Thanks to Alex Hyslop for the link.

Posted by Doug Burns Comments: (33) Trackbacks: (0)
Defined tags for this entry:
Related entries by tags:
How to generate more Logical Reads
How to induce log file sync waits
"People like to have one number they can believe in."
A Miracle Masterclass ...
bstat/estat Fun

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

#1 - Noons said:
2007-06-22 08:34 - (Reply)

have you seen the bit just after that one, on "how to restore the Oracle database"?

shiver me timbers...

#1.1 - Doug Burns said:
2007-06-22 09:37 - (Reply)

Yeah, I liked that one even more but I thought I'd leave it to others to pick out their own favourites. It's like an DBA-WTF bonanza!

#2 - Fuad Arshad said:
2007-06-22 13:55 - (Reply)

Hey doug even though i agree that statement is wrong look at the last update date for that website .
someone might have decided they liked exports at that time.
This page last changed on 26-Aug-2004 19:32:37 CEST by Danpb.

#2.1 - Niall Litchfield said:
2007-06-22 14:17 - (Reply)

But Fuad, exports have never been backups. Useful yes, backups no.

I have to say though that I like the fact compress=n is specified rather suggesting that the exp option has been thought about, just not quite enough.

#2.2 - Doug Burns said:
2007-06-22 15:08 - (Reply)

Fuad,

I don't agree I'm afraid.

1) Date I worked out that an export file is not a backup? I can't remember but it was in the very early 90s! I didn't need someone to tell me, I worked it out when I worked out what would be required for recovery. In fact, the fairly recent publication date makes me slightly less sympathetic because this is not new knowledge.

2) A web page could be removed if the author decided he or she doesn't 'like' exports any more.

3) Normally I'm not so aggressive against any attempt to contribute. I'm aware that an export serves a useful purpose of it's own, but I'm so *tired* of this 'export as backup' nonsense that I struggle to find any sympathy even for those who just don't understand. As Noons pointed out, look at that 'recovery' method! It's dangerous.

4) That web page is *riddled* with scary advice! I actually picked one of the more harmless items which happens to be close to my heart.

5) But, in fairness, I can see some positive intention, like Niall. For example, consistent=y is used, along with system (as opposed to sysdba). However, the page is full of such dangerous, casual advice that my mouth dropped open.

I'd like to think I'm sensitive to other's efforts, but that page is rubbish, masquerading as advice. However, I would accept that it looks like it was intended for a limited audience. However, whether it's bad advice for 6 people or 6,000? It's still bad advice.

That's why I mentioned Alex's BAAG site. It's very important, but the day to day reality I've come across is so much worse than whether we're disciplined about our approach to performance problems or not. We can't even get the basics right!

#2.2.1 - Fuad Arshad 2007-06-22 17:53 - (Reply)

I totally agree on all your comments .
but having experienced this first hand in 1998 the company i used to work with .
the only backup strategy they had was *cough *cough exports and filesystem backups . i lobbied hard against it but they just would not understand my reasoning.
I do agree with you that content needs to be updated and anyone hitting a webpage like that should always double check the facts.
who knows the guy who posted this is even there or not.
There are alot of orphan webpages on the internet.

#2.2.1.1 - Doug Burns said:
2007-06-22 18:37 - (Reply)

... but having experienced this first hand in 1998 ...

Oh, I still see it at sites today. It's quite common and I suppose as long as those who use this approach understand the implications fully, I'm quite relaxed about it. The problem I have with is the bald statement 'backup my database'.

who knows the guy who posted this is even there or not.

Good point. Perhaps they aren't and that's one of the dangers of the internet. All the more reason to argue against it, though.

#3 - Bill Thater 2007-06-22 14:27 - (Reply)

i guess there's hope for me yet.;-) sometimes you just have to shake your head in wonder.

#4 - Alex Gorbachev said:
2007-06-22 20:03 - (Reply)

I think that your indignation is very close to the idea of BAAG.

An export CAN be a valid backup IF it fits the recoverability requirements and recovery is exercised regularly. I agree that majority of Oracle databases have higher recoverability requirements but there are places that can live just fine with exports.

But I digress so back to the first sentence. That guy *GUESSED* that everyone's requirements are just like his. You did the same and assumed (read guessed) that the requirements must be much higher. Even though you are close to the truth - you are BOTH generallt still wrong.

Get the requirements and start from there. Export can be, in certain cases, an acceptable backup implementation but, as you stated, it's quite an old solution and most of the times nowadays is not up to the job.

#4.1 - Doug Burns said:
2007-06-22 20:51 - (Reply)

An export CAN be a valid backup IF it fits the recoverability requirements and recovery is exercised regularly.

You mean, like this statement here?

"I suppose as long as those who use this approach understand the implications fully, I'm quite relaxed about it. "

So I think you're telling me something I already said ;-)

You did the same and assumed (read guessed) that the requirements must be much higher.

No I didn't actually, you 'guessed' what I meant. It might just have been a joke, it might have been a serious point, but you don't really have enough information to go on, and yet you guess what I mean?

That's the problem with deciding what is and isn't a guess, I think it might be trickier than you think. But then, I'm just guessing about what you might think, as you were guessing what I might think (but then it could start to get a little ridiculous from here!).

In the end, even if an export is a way of getting data back ...

- so is a flat file
- so is storing it in an Access database somewhere
- so is someone sitting there and typing it all in again
- so are bits of paper

So any backup method is acceptable but, as an Oracle DBA (which is what I am) I'm sick of hearing this 'export as backup' business and I don't think any of the afore-mentioned methods is very sensible.

Maybe that's why I'll never be a true 'scientist', because even if something is logically correct and interesting, if it doesn't make practical sense to me, I find it hard to justify it.

You know, Noons hit on the real point here. It's about the recovery path and, if people are happy recreating databases then that's fine, but I'm not.

Get the requirements and start from there.

(deep sarcasm) You know, I hadn't thought of that, but I will in future - thanks for your help!

#5 - Alex Gorbachev said:
2007-06-22 22:38 - (Reply)

You see, guessing is really contagious. ;-)

About other backup methods you proposed. Well, I assume they could be used but cost, maintenance and reliability would probably be less favorable whereas export is a standard low-maintenance technology.

#5.1 - Doug Burns said:
2007-06-23 01:23 - (Reply)

but cost, maintenance and reliability would probably be less favorable

But I could say the same about an export versus a proper file backup.

And so we would go round and round in circles ;-)

#6 - Gary 2007-06-22 23:28 - (Reply)

While not disagreeing with your comments in general, it's only fair to point out that the web page you point to (BylineonOracle) is specifically for using Oracle database for Byline (http://byline.objectweb.org/) which appears to be a content management system.
Their advice may be quite appropriate for that particular application.

#6.1 - Doug Burns said:
2007-06-23 01:32 - (Reply)

which appears to be a content management system.

Yes I noticed that and I'm sure anyone who visited the link would. However ...

Their advice may be quite appropriate for that particular application.

I don't see how it's relevant. If it had said - "recreate the database using standard Oracle methods and then run our patented content loading utility", then I might see your point. But it doesn't. It says

Backup :-

exp system/manager file=$FILE consistent=y full=y compress=n

Restore from a backup :-

imp system/manager file=[filename] full=y

If you can explain to me how that's going to help them recover from a corrupt or deleted data file, I'm all ears!

As far as I'm concerned it's terrible advice. I don't care what application they're using the backup and recovery advice for the underlying database is deeply flawed.

Bear in mind I said

It's quite common and I suppose as long as those who use this approach understand the implications fully, I'm quite relaxed about it.

in an earlier comment. I know people use exports as backups, but let's be clear that it will require database recreation which is way more difficult than the import part. So is it that people always seem to neglect mentioning that part? Is it because they haven't thought it through properly?

#7 - Noons said:
2007-06-23 14:16 - (Reply)

I guess the problem is the use of the words "backup" and "recovery" in the context of exp/imp. Or vicky-the-versa.

I know: we have all seen the odd site here and there that uses exp/imp only and are happy with it. Heck, I used to be a remote dba in a site that did just this! No matter how much I explained that was not a backup/recovery strategy, they insisted it was enough for their purposes.

All they did run was a mickey-mouse size Peoplesoft HR installation with a few GB db. They never ran nightly jobs and it only took half an hour or so to do a exp consistent=y at midnight. And the db was on a huge SAN: not exactly the easiest way to lose data. Although it can happen, of course.

It was right for them, I suppose. I was never convinced and never managed to convince them to change. Their new inhouse dba has now got them using archivelog recovery. They still do the exp/imp for "logical recovery" of the odd table here and there. Whatever that means, if it keeps them happy...

But the thing that strikes me as downright scary is the imp advice in this web site. I can't see one single instance where that would work as "recovery":

1- lost part of db? Well, you just created duplicates for what's left, buddy!

2- lost the entire db? Well, how can you run imp against a non-existent db, in the first place? It needs the exp/imp dict views to run!...

Then again, you folks should see the instructions we got the other day to move a SQL Server: suffice to say we couldn't even login as admin after we finished! This from a company with a HUGE name in the accounting industry.

and then they complain when I start ranting...

:-)

#8 - joel garry 2007-06-23 15:15 - (Reply)

I don't disagree it is explictly a bad backup strategy. The rants are appropriate.

But I have to point out, they don't say "recover," they say "restore."

I think in the backup docs near the beginning somewhere it is explicit about what backup means for that doc, which of course is different then the usage in the general computing world, and we forget that. People even maintain that exports are not backups, which they are according to the docs - logical, not physical.

So then, physical backups are not logical. :-)

#8.1 - Niall Litchfield said:
2007-06-23 16:39 - (Reply)

But it isn't a restore either, at least it's missing the rather large part where you create a new database first with the appropriate tablespaces of the right sizes that you just lost. Rather a key step.

Until I got really happy with the flashback stuff I used always to run a backup and an export for the logical corruption problems (PITR). But the export was never a backup.

#8.1.1 - joel garry 2007-06-25 07:22 - (Reply)

Of course you are right, the tablespace creation sizes or placement in the export could be way off, though you don't necessarily have to create a new db, that depends on the circumstances - refreshing a test db on a different platform is but one obvious case. But the imp won't work without uncompressing the file anyways! (Note the gzip in the export :-)

#9 - Daniel Fink said:
2007-06-24 16:36 - (Reply)

My favorite is " My database is running really slow and I think the tablespace may be running out of space. How do I check?"

No wonder we keep getting older, bitterer and more twisted...

#9.1 - Doug Burns said:
2007-06-24 17:12 - (Reply)

Well spotted. I wondered how long it would be before someone mentioned that ;-)

Oh, hold on? Maybe that approach is correct for this particular application?

Maybe I didn't "Get the requirements and start from there."

Or maybe it's just more shockingly bad advice on that web page. It would be nice if people focussed on that as much as nit-picking.

No wonder we keep getting older, bitterer and more twisted...

Precisely

#9.2 - Bill Thater 2007-06-25 12:29 - (Reply)

hey, i resemble that remark.;-)

#10 - Ben 2007-06-24 18:38 - (Reply)

Let's keep this in context. For someone to ask a backup question, and look for the answer in a FAQ, and accept the answer unquestionably - then the answer given is absolutely the right one.

#10.1 - Doug Burns said:
2007-06-24 18:41 - (Reply)

Ha-ha!

Excellent point :-)

#11 - Sidhu said:
2007-06-27 05:06 - (Reply)

Doug

Good ones. I really like the backup/restore one ;-) BTW I wrote a small script here using which he will not even need to gzip it later, it will directly write (and read) to .Z file ;-)

Sidhu

#12 - Noons said:
2007-06-27 09:06 - (Reply)

and now, (drumroll), lets have a look at something much clearer:

http://oracleebusinesssuite.blogspot.com/2007/06/how-to-recover-control-file-in-oracle.html

tada!

multiplexed controlfiles, wazzat den?

note the Oracle-sponsored blog.
Life is easy depending on which arse you kiss...

#12.1 - Doug Burns said:
2007-06-27 16:51 - (Reply)

Heh-heh ;-)

Not sure what you mean by Oracle-sponsored though? You mean it's on blogs.oracle.com?

#13 - Noons said:
2007-06-28 02:10 - (Reply)

have you ever tried to include the "oracle" prefix in a URL you publish?
it ain't pretty...

#13.1 - Gary 2007-06-28 03:21 - (Reply)

You are asking a man who's hostname is oracledoug.com ?

#13.1.1 - Noons said:
2007-06-28 13:42 - (Reply)

of course!

¦)


narh, just pulling legs.





look, that site is included in the orablogs feed and is how I get to see most of these Oracle-related blogs.

unfortunately...





I wish the feeds could be a little bit more discriminatory.

But knowing Oracle mktng, that will mean mine will be removed; and this sort of crap site will go on, as long as they "toe the line" and say whatever marketing wants.

and then they wonder why the blog community has largely ignored the Oracle blogs...


then again, filtering blogs is hardly a good way of promoting anything. who knows what the solution is?

Maybe just knock on these blogs and say: "you are wrong, fella!" is the way?

THEN they'll call us "nazis"...

#13.1.1.1 - Brian Duff said:
2007-06-28 22:00 - (Reply)

First of all - Orablogs isn't, and never has been sponsored or endorsed or in any way controlled by Oracle.

Secondly, I have no connection whatsoever to Oracle marketing (perish the thought). I'm a software engineer on the JDeveloper team, and I set up and maintain Orablogs primarily to give something back to the Oracle community.

I've spent hundreds of dollars of my own money maintaining the servers, and I've resisted numerous requests to put advertising and other revenue generators on the site in order to maintain its neutrality. At times I've been within a hair's width of pulling the whole site down. It's frustrating and surprisingly unrewarding running a site like this.

But others both within and outside Oracle have always convinced me that it's a valuable resource that was instrumental in fostering the early Oracle blogging community several years ago and that it should keep running.

I fundamentally believe in free speech. Every Oracle related blog (regardless of how "right" or "wrong" I or others believe it is) that someone sends to me for inclusion is considered with equal weight.

#13.1.1.1.1 - Noons said:
2007-06-29 08:40 - (Reply)

'sOK, Brian. I meant OraNA. I never got my blog in orablogs, despite many attempts at submitting it. Mostly met with no reply whatsoever to my emails.

#13.1.1.2 - Doug Burns said:
2007-06-28 23:18 - (Reply)

Maybe just knock on these blogs and say: "you are wrong, fella!" is the way?

I reckon that's probably the only way.

I'm guilty myself of laughing from the sidelines and it would probably be more productive to try to get the material corrected. At the same time, there's no guarantee it won't fall on deaf ears.

#13.1.1.2.1 - Noons said:
2007-06-29 07:13 - (Reply)

Ah well, we'll be called "blog nazis" then.
Better than have all this rubbish going on unchecked...


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications
BBCode format allowed