In the Oracle 10g release, Automatic Storage Management (ASM) was introduced, which provided a simplified way for DBAs to manage the storage of Oracle-related files.  However, ASM did make maintenance of the files more complicated as the files could not be directly accessed using system commands.  For the most part, this was not a problem, but it does hinder the use of another Oracle feature: transportable tablespaces.

For those unfamiliar with the concept, transportable tablespaces make moving tablespaces between databases much easier.  Instead of doing a full export / import of all the data in a tablespace, one simply exports the metadata related to tablespace and all of the objects contained therein.  Then a quick datafile copy using system commands and an import of the metadata completes the operation.  Below is an example of how this process is modified with ASM being used on both the source and destination databases.  First, the setup:

CREATE TABLESPACE tts
   DATAFILE '+DATA_01/TEST/DATAFILE/tts.dbf';

CREATE TABLE test
   (num NUMBER)
  TABLESPACE tts;

INSERT INTO test
SELECT rownum
  FROM all_objects
  WHERE rownum <= 100;

 

 

The first step is to make sure that the tablespace is self-contained (i.e. that no objects are part of both the TTS tablespace and another tablespace that is not being transported):

EXEC DBMS_TTS.TRANSPORT_SET_CHECK('TTS', TRUE);

SELECT * FROM transport_set_violations;

ALTER TABLESPACE tts READ ONLY;

No rows should be returned from the SELECT.  If data is returned, then there is a tablespace containment violation, which will prevent the tablespace from being transported.  If not, we can perform the export after the tablespace is put into read only mode:

expdp username/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=tts.dmp
   TRANSPORT_TABLESPACES=tts

This will create a small dump file that contains only the metadata for the TTS tablespace, but we still need the datafile itself.  In order to extract the datafile from the source ASM, we need to create a directory into ASM and use the DBMS_FILE_TRANSFER package (Note: the XML DB interface or RMAN could also be used):

CREATE DIRECTORY dgroup AS '+DATA_01/TEST/DATAFILE';

BEGIN
   DBMS_FILE_TRANSFER.COPY_FILE('DGROUP','tts.dbf',
          'DATA_PUMP_DIR','tts.dbf');
END;
/

Now both the dumpfile and the datafile are located in the datapump directory.  Copy both of these files to the destination database server.  Then we need to load the datafile into the destination ASM:

CREATE DIRECTORY dgroup AS '+DATA_01/TEST2/DATAFILE';

BEGIN
   DBMS_FILE_TRANSFER.COPY_FILE('DATA_PUMP_DIR','tts.dbf',
          'DGROUP','tts.dbf');
END;
/

Now we need to import the metadata about the tablespace and its contents:

impdp username/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=tts.dmp
   TRANSPORT_DATAFILES=+DATA_01/TEST2/DATAFILE/tts.dbf

From there, make the tablespace writeable (do not forget to do this on both the source and destination databases) and everything is done:

ALTER TABLESPACE tts READ WRITE;

DESCRIBE test

Name                                      Null?    Type
----------------------------------------- -------- ----------------
NUM                                                NUMBER

SELECT tablespace_name, file_name 
  FROM dba_data_files 
 WHERE tablespace_name = 'TTS';

TABLESPACE_NAME      FILE_NAME
-------------------- -----------------------------------------------
TTS                  +DATA_01/TEST2/DATAFILE/tts.316.726608243

 

 

 

 

 

 

 

 

Comment