recovery options

[joeythelips]

Hi,

I have an oracle 8i database which contains 4 different schemas each with its own tablespace and datafiles.

A request from the business is that one schema is refreshed every night to be the same as its original starting position.

my plan was to do a full truncate of all the tables in the schema and then import via a cron job in unix every night.

However, i was wondering if there was a quicker way to do this?

Note that the db is running in noarchivelog mode.

Thanks

Joe

 

[KenCunningham]

Usually in this scenario, I make a copy of all datafiles, control files etc to another area/disk whilst the database is shutdown and in the 'original' state, then copy these back each night, overwriting the 'dirty' version with the original. This works particularly well for training environments, but requires you have the necessary disk space. HTH.

 

[alexhu]

You could make a copy of the tables and then copy these back each night using either the copy command or 'create table as select from......' after dropping the 'dirty' ones

Alex

 

[joeythelips]

hi ken,

that was kind of what i was thinking of, but would i not overwrite the entire database with that approach?

i just want to 'refresh' one schema?

Joe

 

[joeythelips]

alex,

thanks for your help.

unfortunately i don't think this would cover my needs as

1, i have a couple of tables with long datatypes.
2, i would also need to recreate some constraints.

Joe

 

[alexhu]

Does 8i have transportable tablespaces or is that 9i only?

Alex

 

[KenCunningham]

Sorry Joe, I misread or misinterpreted the question. You're quite correct, my suggestion would overwrite the whole caboodle - not clever!!

 

[joeythelips]

thanks ken. looks like import/export is my best option?

alex, i think transportable tablespaces are available in 8i. how would that work then?