Restoring a tablespace to a different database

Question

Any ideas as to how we can re-create a tablespace on a different server?

We have a cold backup, so we can copy the datafiles to the target server. Can we just create a new instance on the target server with the same tablespace name and the same path structure and plug-in the original datafiles?

Just a bit more background, we just want to get a table back from cold backup without restoring the whole database, so we were looking to restore the tablespace's files into a dummy database, then exporting the table were after.

Unfortunately it is an old version of Oracle - 9.2.0.8 and we don't use RMAN. Any info would be appreciated.

Answer 1

Yes,

if you have all files placed in the same locations, you can create a pfile, make it point to the controlfile[s] and start the instance. If the backup was a clean cold backup, it should open without any problems. Don't forget to also enter the name parameters and the dump destination parameters for the database.

You should restore at least the system, undo and your_desired_tablespace_files in order to succeed. Create a new temporary tablespace.

Answer 2

No, you just can't copy and paste datafiles.

In order to do so, you must first export (with exp/imp tools) the tablespace as a transportable tablespace, then copy the datafiles, and finally import the dump file generated in the export process. But note that this wont work between different platforms (that feature is from 10g on ahead)

Instead, you can just export the whole tablespace (or schema) into a dump file and then, create same schema and tablespaces as you did, and then import it.

-- Edit -- In that case, If you still have a backup of the controlfiles, you can take them plus the needed files, put in the same locations, create a pfile, start mount, take offline all datafiles except those you want, open DB, and export the table.