Transportable tablespace refresh

 
1.check tablespace for the user which need to refresh
-------------------------------------------------------------------
 SQL> select distinct tablespace_name from dba_segments where owner='SLTC_QUOTE';
SLTC_QUOTEDAT01
SLTC_QUOTEIDX01
2.
-------------------
SQL> execute dbms_tts.transport_set_check ('SLTC_QUOTEDAT01,SLTC_QUOTEIDX01',true);
PL/SQL procedure successfully completed.
3.
---------------------------
SQL> select * from transport_set_violations;
no rows selected
4.Check datafiles of all tablespace before making it on readonly
------------------------------------------------------------------------------------- 
SQL>  select file_name, bytes/1024/1024 from dba_data_files where tablespace_name = ‘SLTC_QUOTEIDX01’;

5. Put tablespace in read only mode.
----------------------------------------------
SQL> select 'alter tablespace '||tablespace_name||' read only;' from dba_tablespaces where tablespace_name like '%QUOTE%';   
alter tablespace SLTC_QUOTEDAT01 read only;
alter tablespace SLTC_QUOTEIDX01 read only;

6. Export the tablespace using the following syntax (normal method)
Connect sqlplus
Drop directory EXP_DUMP_DEST;
Create directory EXP_DUMP_DEST as ' /gemsora13s/exp02/brkcpq'
expdp directory=EXP_DUMP_DEST dumpfile=exp_quote_db_tts.dmp logfile=exp_quote_db_tts.log TRANSPORT_TABLESPACES=SLTC_QUOTEDAT01,SLTC_QUOTEIDX01
Username: sys/password as sysdba
7) Check the floating mountpoints are mounted on gemsora13s
     If the floating mountpoints are not mounted, mount them on gemsora13s
8) Copy all SLTC_QUOTDAT01, SLTC_QUOTEIDX01 tablespaces datafiles to floating mountpoints using cp command
9) Unmount the floating mount points on gemsora13s server using the below command.
   ssh srvr108
   /orashr/oracle/scripts/shell/remote_BCumount.sh gemsora13s prdcpq_dg fltcpq oracle
10) Mount the fltcpq floating mountpoints on swbdb1s using the below command.
   /orashr/oracle/scripts/shell/remote_BCmount.sh swbdb1s prdcpq_dg fltcpq oracle
On target 
7) Offline the SLTC_QUOTE tablespaces
SQL> select 'alter tablespace '||tablespace_name||'  offline;' from dba_tablespaces where tablespace_name like '%QUOTE%';   
alter tablespace SLTC_QUOTEDAT01 offline;
alter tablespace SLTC_QUOTEIDX01 offline;
8) Drop tablespaces
Drop tablespace SLTC_QUOTEDAT01 including contents and datafiles;
Drop tablespace SLTC_QUOTEIDX01 including contents and datafiles;
Make sure all files are removed from the mount pints.
Copy all data and Index tablespaces datafiles from FLTCPQ to STGCPQ7 mountpoints.
Make sure all datafiles (data and index tablespaces) copied to stgswb7 mountpoints.
SCP dump files from /gemsora13s/exp02/brkcpq swbdb1s:/swbdb1s/exp01/stgcpq7
12. Import tablespace using the following syntax
-----------------------------------------------------------------
Please use the below command to import the sltc_quote into stgcpq7
Set the stgcpq7 environment
Impdp directory=EXP_DUMP_DIR dumpfile=dmpfilename logfile=logfilename
TRANSPORT_DATAFILES=all datafiles list

13. After import we need to exp/imp with norows to get procedures, packages etc
-----------------------------------------------------------------------------------------------------------
On Source
select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;
OWNER                          OBJECT_TYPE         STATUS    COUNT(*)
------------------------------ ------------------- ------- ----------
SLTC_QUOTE                     LOB                 VALID            1
SLTC_QUOTE                     JAVA CLASS          VALID            1
SLTC_QUOTE                     SEQUENCE            VALID            1
SLTC_QUOTE                     INDEX               VALID           63
SLTC_QUOTE                     TABLE               VALID           42
SLTC_QUOTE                     PACKAGE             VALID            1
SLTC_QUOTE                     JAVA SOURCE         VALID            1
SLTC_QUOTE                     VIEW                VALID            4
SLTC_QUOTE                     PACKAGE BODY        VALID            1
Now export and import
NOTE:= if we want the import to be finished soon we need to give statistics=none
In Source:
exp file=exp_quote_norows.dmp log=exp_quote_norows.log owner=sltc_quote rows=n
In Target:
imp file=exp_quote_norows.dmp log=imp_quote_norows.log fromuser=sltc_quote touser=sltc_quote
14.Check the count on target after import
SQL>  select owner ,object_type,status, count(*) from dba_objects where owner='SLTC_QUOTE'group by owner, object_type, status;
OWNER                          OBJECT_TYPE        STATUS    COUNT(*)
------------------------------ ------------------ ------- ----------
SLTC_QUOTE                     LOB                   VALID            1
SLTC_QUOTE                     VIEW                 VALID            4
SLTC_QUOTE                     INDEX               VALID           63
SLTC_QUOTE                     TABLE               VALID           42
SLTC_QUOTE                     PACKAGE          VALID            1
NOTE:= compile invalids if any.

15. Make all the tablesapces in source and target read write
-------------------------------------------------------------------------------------
select 'alter tablespace '||tablespace_name||' read write;'
from dba_tablespaces
where tablespace_name like 'SLTC_QUOTE%';
alter tablespace SLTC_QUOTEDAT01 read write;
alter tablespace SLTC_QUOTEIDX01 read write;