Transportable Table Space Refresh

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_QUOTEDAT01'; 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 ------------------------------ ------------------ ------- ---------SLTC_QUOTE LOB VALID SLTC_QUOTE VIEW VALID SLTC_QUOTE INDEX VALID SLTC_QUOTE TABLE VALID SLTC_QUOTE PACKAGE VALID

COUNT(*) 1 4 63 42 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;