Transportable Tablespace on 11g ASM to ASM

My current working environment always do the data refresh from production and they are using datapump to move the data from production to SIT/UAT and Pre Prod.

some environment is small so using datapump is an option but some is quite big ~2TB. when doing the import, it usually takes 12hours to do the import. So my task is to look for another way to make it faster. I decided to use TTS.

Once the export and scp to target database is completed. it only took 90mins to import the new tablespaces and move the datafiles to ASM.

The only disadvantage of doing this is that i need more space for both and target.

Next i will do a test in 12c which is much better than 11g.

1. Check if some limitation don’t allow to export the source schema

    SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('INDEX_TBS,DATA_TBS01,DATA_TBS02', TRUE);

    PL/SQL procedure successfully completed.

    SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

    VIOLATIONS
    --------------------------------------------------------------------------------
    ORA-39908: Index DDL_AUTOINSTALL.META_QUEUE_AK1 in tablespace DBASPACE enforces
    primary constraints  of table XXX_RGN_RT.META_QUEUE in tablespace DATA_TBS02.


    SQL> alter index DDL_AUTOINSTALL.META_QUEUE_AK1 rebuild tablespace DATA_TBS02 online;

    Index altered.

    SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('INDEX_TBS,DATA_TBS01,DATA_TBS02', TRUE);

    PL/SQL procedure successfully completed.

    SQL>  SELECT * FROM TRANSPORT_SET_VIOLATIONS;

    no rows selected

    SQL>

Continue reading

Advertisements