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>


2. Check the count on source

    SQL> select count (*) from dba_segments where tablespace_name in ('INDEX_TBS','DATA_TBS01','DATA_TBS02');

      COUNT(*)
    ----------
          6600

    SQL>

3. Create dump directory on source (available space should be more than the size of all TBS)

	SQL> mkdir -p /backup/rmantts/aux
	SQL> create directory rmantts as '/backup/rmantts';

4. Perform the export

    RUN
    {
    TRANSPORT TABLESPACE 'INDEX_TBS','DATA_TBS01','DATA_TBS02'
    AUXILIARY DESTINATION '/backup/rmantts/aux'
    TABLESPACE DESTINATION '/backup/rmantts'
    DATAPUMP DIRECTORY rmantts
    UNTIL SCN 10870248157521;
    }

5. Compress and scp dumps to target


6. Drop the tablespaces to be replace on target database.

     SQL> drop tablespace DATA_TBS01 including contents and datafiles;
     SQL> drop tablespace DATA_TBS02 including contents and datafiles;
     SQL> drop tablespace INDEX_TBS including contents and datafiles;

7. Execute the import..
-make sure that the directory is created where the dump is located.

impdp \"/ as sysdba\" directory=RMANTTS dumpfile= 'dmpfile.dmp' transport_datafiles= /backup/rmantts/o1_mf_index_ky_c81zxfrk_.dbf, /backup/rmantts/o1_mf_index_ky_c81zyk73_.dbf, /backup/rmantts/o1_mf_index_ky_c821ctft_.dbf, /backup/rmantts/o1_mf_index_ky_c81y0hdr_.dbf, /backup/rmantts/o1_mf_index_ky_c81xzs7v_.dbf, /backup/rmantts/o1_mf_index_ky_c81txwbg_.dbf, /backup/rmantts/o1_mf_index_ky_c81tx66g_.dbf, /backup/rmantts/o1_mf_index_ky_c81txfmv_.dbf, /backup/rmantts/o1_mf_index_ky_c81txw5q_.dbf, /backup/rmantts/o1_mf_index_ky_c821do77_.dbf, /backup/rmantts/o1_mf_index_ky_c8217cqg_.dbf, /backup/rmantts/o1_mf_index_ky_c821d4nc_.dbf, /backup/rmantts/o1_mf_index_ky_c821gk0o_.dbf, /backup/rmantts/o1_mf_index_ky_c8216k1t_.dbf, /backup/rmantts/o1_mf_index_ky_c820o8hl_.dbf, /backup/rmantts/o1_mf_index_ky_c820gk98_.dbf, /backup/rmantts/o1_mf_index_ky_c821c4kv_.dbf,/backup/XXXNSIT3/rmantts/o1_mf_index_ky_c8212gng_.dbf, /backup/rmantts/o1_mf_index_ky_c8215l14_.dbf, /backup/rmantts/o1_mf_index_ky_c820nknl_.dbf, /backup/rmantts/o1_mf_index_ky_c81tx9qx_.dbf, /backup/rmantts/o1_mf_index_ky_c81txrpk_.dbf, /backup/rmantts/o1_mf_data_gbl_c81zngt2_.dbf, /backup/rmantts/o1_mf_data_gbl_c81zhofh_.dbf, /backup/rmantts/o1_mf_data_gbl_c81zmyyt_.dbf, /backup/rmantts/o1_mf_data_gbl_c81zl2q3_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xp6qd_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xvz6o_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xybcm_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfkp_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx648_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx9pl_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txro9_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw3h_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw88_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txflv_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx65g_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txn3b_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx2ho_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw4q_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw9g_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw9z_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xpsxf_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xnbv0_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx9t5_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw71_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txwcs_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx9sq_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx2kz_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfo5_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txn5o_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txrrd_.dbf, /backup/rmantts/o1_mf_data_gbl_c81xpz97_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txrrv_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx67c_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txwcc_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx67s_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx66w_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txwbw_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfnq_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfn9_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx2kj_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw6m_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfly_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx65j_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txw9k_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txfmf_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx65y_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx9rd_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txn3v_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx2j5_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txrqj_.dbf, /backup/rmantts/o1_mf_data_gbl_c81txn4b_.dbf, /backup/rmantts/o1_mf_data_gbl_c81tx2jm_.dbf, /backup/rmantts/o1_mf_data_rgn_c81zb807_.dbf, /backup/rmantts/o1_mf_data_rgn_c81zbw65_.dbf, /backup/rmantts/o1_mf_data_rgn_c81zddtf_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xs9hs_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xsj6y_.dbf, /backup/rmantts/o1_mf_data_rgn_c81y9pj0_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xrqh9_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txn2c_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx2g7_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx9qt_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txrpg_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txw65_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx9s9_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txrqy_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txn4r_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx2k1_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xlpow_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xpdn4_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xh274_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xk1sg_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xl19b_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xht71_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xslwk_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xj666_.dbf, /backup/rmantts/o1_mf_data_rgn_c81xg30k_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txn3d_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx2hq_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txw4s_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txrq1_.dbf, /backup/rmantts/o1_mf_data_rgn_c81txw58_.dbf, /backup/rmantts/o1_mf_data_rgn_c81tx9rv_.dbf  

8. Once import is completed. lets move the datafile to ASM.

    RMAN > RUN
    {
    ALLOCATE CHANNEL c1 DEVICE TYPE disk;
    ALLOCATE CHANNEL c2 DEVICE TYPE disk;
    ALLOCATE CHANNEL c3 DEVICE TYPE disk;
    ALLOCATE CHANNEL c4 DEVICE TYPE disk;
    ALLOCATE CHANNEL c5 DEVICE TYPE disk;
    ALLOCATE CHANNEL c6 DEVICE TYPE disk;
    ALLOCATE CHANNEL c7 DEVICE TYPE disk;
    ALLOCATE CHANNEL c8 DEVICE TYPE disk;
    backup as copy datafile 4,10,11,12,13,14,15,16,17,18,19,20,21,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,48,49,50,53,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128 format '+DATA';
    release channel c1;
    release channel c2;
    release channel c3;
    release channel c4;
    release channel c5;
    release channel c6;
    release channel c7;
    release channel c8;
    }

9. Set the datafiles of 3 tablespace to offline

SQL> alter database datafile 4,10,11,12,13,14,15,16,17,18,19,20,21,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,48,49,50,53,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128 offline;


Database altered.

SQL>

10. Using rman perform switch datafile to copy

-for below you need to perform for all datafiles.
    RMAN> switch datafile 11 to copy;
    datafile 11 switched to datafile copy "+DATA/ncxxsit7/datafile/data_gbl.266.899679703"

    RMAN>

11. On same rman session perform recover datafile

-once all switch datafile switch copy completed.
    RMAN> recover  datafile 4,10,11,12,13,14,15,16,17,18,19,20,21,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,48,49,50,53,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128;

    Starting recover at 28-DEC-15
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=329 device type=DISK
    allocated channel: ORA_SBT_TAPE_1
    channel ORA_SBT_TAPE_1: SID=352 device type=SBT_TAPE
    channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_2
    channel ORA_SBT_TAPE_2: SID=380 device type=SBT_TAPE
    channel ORA_SBT_TAPE_2: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_3
    channel ORA_SBT_TAPE_3: SID=402 device type=SBT_TAPE
    channel ORA_SBT_TAPE_3: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_4
    channel ORA_SBT_TAPE_4: SID=428 device type=SBT_TAPE
    channel ORA_SBT_TAPE_4: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_5
    channel ORA_SBT_TAPE_5: SID=480 device type=SBT_TAPE
    channel ORA_SBT_TAPE_5: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_6
    channel ORA_SBT_TAPE_6: SID=655 device type=SBT_TAPE
    channel ORA_SBT_TAPE_6: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_7
    channel ORA_SBT_TAPE_7: SID=678 device type=SBT_TAPE
    channel ORA_SBT_TAPE_7: Veritas NetBackup for Oracle - Release 7.5 (2012091610)
    allocated channel: ORA_SBT_TAPE_8
    channel ORA_SBT_TAPE_8: SID=703 device type=SBT_TAPE
    channel ORA_SBT_TAPE_8: Veritas NetBackup for Oracle - Release 7.5 (2012091610)

    starting media recovery
    media recovery complete, elapsed time: 00:00:01

    Finished recover at 28-DEC-15

    RMAN>

12. Now set the datafile on ASM online

    SQL > alter database datafile 4,10,11,12,13,14,15,16,17,18,19,20,21,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,48,49,50,53,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,109,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128 online;

    Database altered.

    SQL>

13. Set tablespace to read write.

    SQL> alter tablespace DATA_TBS01  read write;
    SQL> alter tablespace DATA_TBS02  read write;
    SQL> alter tablespace INDEX_TBS read write;

14. Now check no objects..

    SQL> select count (*) from dba_segments where tablespace_name in ('INDEX_TBS','DATA_TBS01','DATA_TBS02');

      COUNT(*)
    ----------
          6600

    SQL>
    Status    Tablespace Name                TS Type         Ext. Mgt.  Seg. Mgt.     Tablespace Size    Used (in bytes) Pct. Used
    --------- ------------------------------ --------------- ---------- ---------- ------------------ ------------------ ---------
    ONLINE    AUDITTBS                       PERMANENT       LOCAL      AUTO              524,288,000          1,441,792         0
    ONLINE    DATA_TBS01                     PERMANENT       LOCAL      AUTO        1,114,124,124,160    601,737,068,544        54
    ONLINE    DATA_TBS02                     PERMANENT       LOCAL      AUTO          599,230,103,552    323,989,258,240        54
    ONLINE    DBASPACE                       PERMANENT       LOCAL      AUTO              104,857,600          1,048,576         1
    ONLINE    GGS_DATA                       PERMANENT       LOCAL      AUTO              209,715,200         15,204,352         7
    ONLINE    INDEX_TBS                      PERMANENT       LOCAL      AUTO          287,097,192,448    171,164,401,664        60
    ONLINE    SYSAUX                         PERMANENT       LOCAL      AUTO            5,242,880,000      4,528,013,312        86
    ONLINE    SYSTEM                         PERMANENT       LOCAL      MANUAL          3,670,016,000      2,574,450,688        70
    ONLINE    TEMP                           TEMPORARY       LOCAL      MANUAL         45,235,568,640      6,339,690,496        14
    ONLINE    UNDOTBS1                       UNDO            LOCAL      MANUAL         49,640,243,200        507,576,320         1
    ONLINE    USERS                          PERMANENT       LOCAL      AUTO            4,294,967,296        109,379,584         3

Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s