2

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?

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.