Re: Backup and restore just one DBF or one user

Ujang M Zainudin wrote:
> Dear all,
>
> How do i backup and restore just a single dbf datafile from an oracle
> 9i
> database.

You don't. You backup *databases*. You restore datafiles, and recover datafiles, so that the *database* as a whole is functional once more.

> Each user in the db has its own tablespace residing in its
> own
> datafile.

This really doesn't make much sense. Tablespaces are made up of data files. They don't reside inside them. And users don't own tablespaces. If you mean that you have arranged things so that each tablespace contains only the objects associated with one schema, then say so.

> What I want to achive is, to backup each user separately

You will find things become an awful lot clearer and simpler when you sort out your terminology. Because at the moment, it's pretty unclear! You don't backup users. You physically backup data files.

> and
> be
> able to restore any particular user at any point in the future.

What does that question actually mean? Do you simply mean that at any time in the future, you want to be able to restore and recover a datafile? No problem, provided only that you keep all archives produced by the database as a whole since the time of whatever backup it is that you propose restoring from.

Do you mean, alternatively, "I want to be able to recover a user's schema to any point in time that user may need, regardless of where the rest of the database has gotten to"? More problematic. Fundamentally, you can't have bits of a database at one point of time, and bits at another, even if you think of the various tablespaces as discrete and independent.

On the other hand, you could create a clone database containing nothing but system, undo and temp, and use TSPITR techniques to achieve that sort of 'partly out-of-synch' result. But it's tricky, and I can't even tell if it's what you want.

> It is not a 24 by 7 database and shutting down the db during backup or
> restore
> is not an issue. So it could be online backup or cold backup or
> exp/imp backup.
>
> I am exploring the cold backup option right now and having problem
> restoring. It is not quite right yet and i am still struggling with
> it.
>
> I am less familiar with exp and imp that my last attempt at restoring
> caused
> double entry in most of my tables. Besides, it took too much time to
> import mainly due to my lack of experience in dealing with imp.
>
> I am not familiar at all with RMAN, so i am not considering it.

Big mistake.

> Any advice or scripts that you could provide me is greatly
> appreciated.
> Thanks in advance.
>
> Regards,
> Ujang

My advice is as follows: learn to walk a little before attempting an Olympic marathon. You cannot seriously expect to be able to understand this sort of backup and recovery scenario when you dismiss the most useful backup and recovery tool at your disposal with the one-liner "I'm not familiar with RMAN, so I am not considering it"! What you are asking   in this question is either (a) "How do I recover a single data file", which is elementary stuff or (b) "How do I perform a TSPITR", which is rather advanced or (c) something else entirely but I can't work out what you might be thinking of.

You really cannot dismiss RMAN as you've done. *GET* familiar with it!

Get familiar with the backup and recovery concepts underpinning ANY Oracle database, and then you will begin to see how they can apply to you in your situation. Try to get your terminology precise and accurate, so that you can say precisely what you mean. Visit http://tahiti.oracle.com and start reading some documentation on the subject.

When you do all of that, you will soon discover that the temperature of your backup (hot or cold) makes not the slightest bit of difference to your recovery options, but that your archivelog status does. You then need to sort out in your mind whether you are wanting to perform complete or incomplete recoveries. Or whether data snapshot restoration via import would be sufficient to your needs (understanding that all transactions performed since the time that snapshot was taken with export' will be lost).

But you are going to get nowhere with muddled terminology, muddled thinking, and a dismissive attitude to RMAN... which is going to end up being the best data recovery friend you've got.

Regards
HJR Received on Tue Dec 28 2004 - 05:20:28 CST