Transportable Tablespaces (TTS) Migration - without RMAN

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: 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 6: 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.

Oracle GoldenGate Extract and Replicat Parameter Files

 The provided configuration is a typical Oracle GoldenGate unidirectional replication setup where:

• Extract Process captures changes from the source database.
• Replicat Process applies those changes to the target database.
• DDL (CREATE, ALTER, DROP, etc.) and DML (INSERT, UPDATE, DELETE) operations are replicated.

Source GoldenGate Extract Parameter file

============================================

EXTRACT EXA_ORCL

USERIDALIAS ORCL_S DOMAIN OracleGoldenGate

EXTTRAIL AA

--- End of auto generated Parameter File ---

DDL INCLUDE MAPPED  --- Mandatory

DDLOPTIONS REPORT   --- Mandatory

TABLE AMIT.*;               

TABLE SIVA.*;

TABLE PAYAL.*;

TABLE VAISH.*;

Target GoldenGate REplicat Parameter file:-

============================================

REPLICAT REP_ORCL

USERIDALIAS ORCL_T DOMAIN OracleGoldenGate

--- End of auto generated Parameter File ---

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT IGNORE RETRYOP  --- This is optional, if you face any issue

OVERRIDEDUPS                     --- This is optional, if you face any issue

INSERTMISSINGUPDATES

MAP AMIT.*,TARGET AMIT.*;

MAP SIVA.*,TARGET SIVA.*;

MAP PAYAL.*,TARGET PAYAL.*;

MAP VAISH.*,TARGET VAISH.*;

Key Points:-

1. Extract captures changes from Oracle redo logs.
2. EXTTRAIL stores captured transactions in trail files.
3. TABLE defines which tables/schemas are captured.
4. Replicat applies changes on target.
5. MAP defines source-to-target object mapping.
6. DDL INCLUDE MAPPED replicates DDL for mapped objects.
7. DDLOPTIONS REPORT logs DDL actions in report files.
8. OVERRIDEDUPS handles duplicate record situations.
9. INSERTMISSINGUPDATES converts missing-row updates into inserts.
10. USERIDALIAS provides secure credential management instead of hardcoded passwords.

This configuration implements a schema-level Oracle-to-Oracle GoldenGate replication for the schemas AMIT, SIVA, PAYAL, and VAISH, including both DML and DDL replication.

Goldengate Configuration

 

SQL> COLUMN CURRENT_SCN FORMAT 99999999999999999999

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

 

SQL> SELECT scn_to_timestamp(12161785205359) FROM dual;

 

SQL> create tablespace gg_tbs datafile '+DATA/ORCL/gg_tbs01.dbf' size 2G;

SQL> create user ggadmin identified by ggadmin default tablespace gg_tbs;

sql> grant connect,resource to ggadmin;

SQL> alter user ggadmin quota unlimited on gg_tbs;

SQL> grant dba to ggadmin; 

SQL> exec dbms_goldengate_auth.grant_admin_privilege ('GGADMIN');

SQL> alter database add supplemental log data (all) columns;---------------> CDB level and also in pdb level

SQL> select supplemental_log_data_min from v$database;    

SQL> alter system set STREAMS_POOL_SIZE=1G scope=BOTH;---------------------> CDB level

SQL> alter system set enable_goldengate_replication=TRUE;------------------> CDB level

 

 

/home/oracle/@sequence.sql -- Run

GRANT EXECUTE on ggadmin.updateSequence TO ggadmin;-----------------------> in PDB level

 

ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;-----------no need to run this one 

ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;-------------> source is pdb then only we need to run this one 

ALTER SESSION SET CONTAINER=ORCL24;

 

Source: 

 

SQL> alter database add supplemental log data (all) columns;---------------> CDB level and also in pdb level

SQL> select supplemental_log_data_min from v$database;    

SQL> alter system set STREAMS_POOL_SIZE=1G scope=BOTH;-------------------> CDB level

SQL> alter system set enable_goldengate_replication=TRUE;----------------> CDB level

Repeat this process for Target DB