Migration of the Database and the usage of TTS
If the source and Exadata target are on the same platform/endian and you are moving only tablespaces, you can use **TTS without RMAN**.
Step 1: Check TTS restrictions and self-containment
exec dbms_tts.transport_set_check('TS1,TS2', true);
select * from transport_set_violations;
Validates that the selected tablespaces are self-contained and eligible for transport without dependent objects outside the transport set.
Step 2: Put the tablespaces in READ ONLY mode
alter tablespace TS1 read only;
alter tablespace TS2 read only;
Freezes changes to the tablespaces to ensure consistent datafiles during transport.
Step 3: Export metadata using Data Pump
expdp '/ as sysdba' dumpfile=ORCL_Metadata.dmp \
logfile=ORCL_Metadata.log directory=C02_ESB_Metadata \
transport_tablespaces=AM_TS,SI_TS,PA_TS,VA_TS
Exports only the metadata definitions (tables, indexes, privileges, etc.) required to reconstruct the transported tablespaces on the target database.
Step 4: Copy datafiles and dump file to Exadata (From Source to Target)
Transfers the physical tablespace datafiles and exported metadata from the source server to the Exadata target server.
Step 5: Create the Tablespaces and User and then Drop the Tablesapces
SQL> CREATE BIGFILE TABLESPACE TS1 DATAFILE '+DATAC2' size 1G autoextend on next 1G maxsize 50G;
Tablespace created.
SQL> CREATE BIGFILE TABLESPACE TS2 DATAFILE '+DATAC2' size 1G autoextend on next 1G maxsize 50G;
Tablespace created.
------------------------------------- ---
SQL> CREATE USER "AMIT" IDENTIFIED BY VALUES '*****' DEFAULT TABLESPACE "AMIT" TEMPORARY TABLESPACE "TEMP" PROFILE "UNLIMITED";
User created.
SQL> DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "AMIT" QUOTA UNLIMITED ON "AMIT"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''AMIT'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/
PL/SQL procedure successfully completed.
SQL> GRANT "ESB_ROLE" TO "AMIT";
Grant succeeded.
SQL> GRANT DEBUG CONNECT SESSION TO "AMIT";
GRANT SELECT ANY DICTIONARY TO "AMIT";
GRANT CREATE TRIGGER TO "AMIT";
GRANT CREATE PROCEDURE TO "AMIT";
GRANT CREATE DATABASE LINK TO "AMIT";
GRANT CREATE SEQUENCE TO "AMIT";
GRANT CREATE VIEW TO "AMIT";
GRANT CREATE TABLE TO "AMIT";
GRANT RESTRICTED SESSION TO "AMIT";
GRANT CREATE SESSION TO "AMIT";
Grant succeeded.
SQL> DROP TABLESPACE TS1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> DROP TABLESPACE TS2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
----------
[oracle@orcl_rac_01 TTS_ORCL_AMIT]$ cat /import/TTS_ORCL_AMIT/TTS_ORCL_AMIT_New.par
userid="/ as sysdba"
directory=TTS_ORCL_AMIT
dumpfile=TTS_ORCL_AMIT.dmp
logfile=TTS_ORCL_AMIT.log
transport_datafiles='+DATAC2/DATAFILE/am_data_01.dbf','+DATAC2/DATAFILE/am_data_02.dbf',
[oracle@orcl_rac_01 TTS_ORCL_AMIT]$ sh TTS_ORCL_AMIT.sh
[oracle@orcl_rac_01 TTS_ORCL_AMIT]$ nohup: appending output to 'nohup.out'
[oracle@orcl_rac_01 TTS_ORCL_AMIT]$ tail -500f TTS_ORCL_AMIT.log
;;;
Import: Release 19.0.0.0.0 - Production on Tue Jul 28 04:10:14 2026
Version 19.30.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
;;; **************************************************************************
;;; Parfile values:
;;; parfile: transport_datafiles=+DATAC2/C1/5269F0A7ECC3DB4CE063338213AC77B7/
;;; _parfile: DATAFILE/am_data_01.dbf,
;;; _parfile: +DATAC2/DATAFILE/am_data_02.;;; _parfile: dbf,
;;; _parfile: +DATAC2/DATAFILE/AMIT_01.;;; _parfile: dbf,
;;;; parfile: logfile=TTS_ORCL_AMIT.log
;;; parfile: dumpfile=TTS_ORCL_AMIT.dmp
;;; parfile: directory=TTS_ORCL_AMIT_dump
;;; parfile: userid=/******** AS SYSDBA
;;; **************************************************************************
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA parfile=/import/TTS_ORCL_AMIT/TTS_ORCL_AMIT_New.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"AMIT"."XYA" created with compilation warnings
ORA-39082: Object type TRIGGER:"AMIT"."SET_ELTRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"AMIT"."ABC" created with compilation warnings
ORA-39082: Object type TRIGGER:"AMIT"."SALES_01" created with compilation warnings
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 28 error(s) at Tue Jul 28 04:16:46 2026 elapsed 0 00:06:31
Step 6: Import metadata and plug in the datafiles
impdp system/password directory=DP_DIR \
dumpfile=tts.dmp logfile=tts_imp.log \
transport_datafiles='/path/ts1.dbf','/path/ts2.dbf'
Registers the transported datafiles in the target database and imports the corresponding metadata.
Step 7: Set tablespaces back to READ WRITE mode
alter tablespace TS1 read write;
alter tablespace TS2 read write;
Opens the transported tablespaces for normal application access and DML operations after successful migration.
TTS Summary :-
Transportable Tablespaces (TTS) is a migration method that moves Oracle tablespaces by transporting their datafiles and metadata, avoiding a full database export/import and significantly reducing migration time for large databases when source and target platforms have the same endian format.
No comments:
Post a Comment