Oracle – How to Copy a Tablespace to Another Database Without Using Data Pump in Oracle 11G

exportoracleoracle-11g-r2rman

I have a database on a legacy system that has some data that needs to be exported to another environment. Everything I need is contained in one tablespace. Unfortunately, I am unable to use Data Pump due to previously botched patching (thousands of invalid objects, including streams packages, which can't be recompiled).

Can I use RMAN or other methods to back up this tablespace, move it to another network (by physical media transfer – networks are completely segregated) and restore it to an existing database?

Best Answer

This is the bare minimum assuming the simplest scenario.

1. Original export/import:

Source database:

  • export everything from the tablespace

exp userid=user/password tablespaces=example file=example.dmp

Destination:

  • copy example.dmp from source
  • create empty tablespace at the desired location, create users manually, then:

imp userid=user/password full=y file=example.dmp

or 2. Transportable tablespace with original export/import:

Source database:

  • put tablespace into read only mode:

alter tablespace example read only;

  • export metadata for transporting the tablespace

exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=example file=example_tts.dmp

Note that you get an EXP-00044 error here if you do not connect with SYSDBA.

Destination database:

  • copy example_tts.dmp from source
  • copy the datafile itself from source to the desired location (example01.dbf in below example)
  • create users manually, then:

imp userid=\'/ as sysdba\' transport_tablespace=y datafiles=/oradata/S112/example01.dbf file=example_tts.dmp

  • finally make the tablespace writable:

alter tablespace example read write;

More details can be found in the documentation:

Original Export

Original Import

Related Question