Posted on

Using Oracle Transportable Tablespaces to refresh Schemas/Tablespaces in Databases

If there is a requirement to refresh large schemas/tablespaces within a  databases regularly it is worth considering using transportable tablespaces (TTS). This method is ideal for moving large data quickly thus minimising downtime. The time taken will depend on the size of the data files being moved and the amount of DDL contained, but generally speaking the operation will not take much longer than the time to move the data files. Interestingly TTS forms the basis for the new pluggable databases to be delivered in 12c, there is a “plugged_in” column in dba_tablespaces which will be set to “yes” after using TTS.

There are some limitations which can be found in the link below, but in the most cases we are able to use TTS.

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11394

http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm#BRADV05432

If we are refreshing using point in time data, where there is no requirement for  real time data, we would use RMAN backups to create our TTS sets. This means there is no effect on our source system i.e. no need to put the tablespaces into read only mode for the duration of the copy.

rman tts

TTS Example

I recently transported sits_data and sits_indx in STARDUST(target) from STARTEST(source) using an RMAN catalog and a recent backup of the source db. RMAN will handle the creation of an auxiliary database for you to facilitate the point in time recovery of the desired tablespaces.

 

Assumptions:

  • You are already using an RMAN catalog to backup the source system.
  • The target system already exists.
  • There is adeququite space for another copy of the data files.
  • The Tablespaces do not exist on the target system( i.e. dropped or renamed)
  • TTS_IMP directory is pointing to /u22/oradata/STARDUST
  • tablespaces have already been checked using
    exec dbms_tts.TRANSPORT_SET_CHECK(‘tablespace(s)

Execution:

        1. login to source oracle server as oracle.
        2. source the environment file for STARTEST – orastartest
        3. connect to rman –  rman target=/ catalog=recman/xxx@rmantest
        4. issue:
          RMAN> transport tablespace sits_data,sits_indx
          TABLESPACE DESTINATION ‘/u22/oradata/STARDUST’
          auxiliary destination ‘/b01/backup/TTS’
          until time ‘sysdate-2/24’;
          This will create a dump file and import script which will be used to import the ddl into the target db.
        5. source the environment file for STARDUST – orastardust
        6. copy import line from import script in /u22/oradata/STARDUST
        7. e.g. impdp / directory=TTS_DIR dumpfile= ‘dmpfile.dmp’ transport_datafiles= /u22/oradata/STARDUST/sits_data01.dbf, /u22/oradata/STARDUST/sits_data02.dbf, /u22/oradata/STARDUST/sits_data03.dbf, /u22/oradata/STARDUST/sits_data04.dbf,/u22/oradata/STARDUST/sits_data05.dbf, /u22/oradata/STARDUST/sits_data06.dbf, /u22/oradata/STARDUST/sits_data07.dbf, /u22/oradata/STARDUST/sits_data08.dbf, /u22/oradata/STARDUST/sits_data09.dbf, /u22/oradata/STARDUST/sits_data10.dbf, /u22/oradata/STARDUST/sits_data11.dbf, /u22/oradata/STARDUST/sits_data12.dbf, /u22/oradata/STARDUST/sits_indx01.dbf, /u22/oradata/STARDUST/sits_indx02.dbf, /u22/oradata/STARDUST/sits_indx04.dbf, /u22/oradata/STARDUST/sits_indx05.dbf, /u22/oradata/STARDUST/sits_indx06.dbf, /u22/oradata/STARDUST/sits_indx03.dbf
        8. The tablespacess are now available in STARDUST.
        9. alter tablespace sits_data read write;
        10. alter tablespace sits_indx read write;