Re: ARCHIVELOG + backup question

Richard Shea wrote:
> Hi - I want to take a backup of all the tables belonging to user X so
> that I can quickly and easily restore them. I will need to do this
> repeatedly over a short period of time and it's only the data (hmmm,
> sequences ?) that will change (it's for a dev env). Database is running
> as NOARCHIVELOG. I have complete control of server (ie can close down or
> do whatever/whenever) but I don't want to restore over other users.
> What's the best way to do this ?
> + I've looked at copying tablespaces (at OS level) but NOARCHIVELOG
> precludes this ... or can I go into ARCHIVELOG 'mode' just long enough
> for the backup and restore ? If so I can someone tell me how ?
> + I've looked at copying entire database (at OS level) but I assume a
> restore must restore the entire database ?
> + I've looked at using IMP but would have to drop the user every time
> - bit boring !
> If anyone can suggest a nice and simple "trash the current data and
> replace it with this" option then I'd appreciate it.
> thanks
> richard shea rshea_at_NOTTHISBITopendoor.co.nz

I you do not wish to use the archive mode then an export of the user can be made. Let say the user is called Xuser, the application owner. Xuser in your example owns all the objects such as tables, indexes etc. If your database is not big (no dataware house) you can use the export feature. Depending on what platform you are using the following must be done. (Unix platform) $ export $ORACLE_SID=<instance name> $ exp system/<password> file=exp_xuser.dmp log=exp_xuser.log consistent=yes user=xuser The consistent=yes retains the consistency of the export by making a snapshot from the point in time the export runs. Any modifications to the database for examples updates (from that point in time) will not be exported. After completion you can zip or compress the file. You can test the validity of this backup by creating another database on the same machine. $ export $ORACLE_SID=<new instance name> * test whether you are certain that you have the right database by using   sqlplus. create the user xuser with sqlplus create necessary user   which you granted access for objects of the xuser schema import the   object of the user xuser $ imp system/<password> file=exp_xuser.dmp   log=exp_xuser.log full=no fromuser=xuser touser=xuser. Depending on   the size of the database it may take some time. This time has of   course have to be measured in order to know what the downtime of the   database (in case of crash) would be. The export can be automated of   course on a daily basis. However keep in mind that this form of backup   cannot recover a database to a point in time of a media failure. You   have to use archive log mode for this in it take a bit more   administrative tasks such as keeping the archived log files.

--
Ir. A.P. Djuly
Gabon Straat 5
2622 Delft
djuly_at_wanadoo.nl

Ir. A.P. Djuly
Gabon Straat 5
2622 Delft
djuly_at_wanadoo.nl

Ir. A.P. Djuly
Gabon Straat 5
2622 Delft
djuly_at_wanadoo.nl

Posted via dBforums
http://dbforums.com