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

Extract and Replicat - GG Questions

 

1. What is the fundamental difference between Extract and Replicat?

Better Interview Answer

Extract	Replicat
Runs on source database	Runs on target database
Captures committed transactions from redo/archive logs	Applies captured transactions on target
Writes data into local trail files	Reads trail files and executes DML/DDL
Responsible for data capture	Responsible for data delivery

Interview Tip:
Extract never queries application tables directly; it reads transaction logs, which minimizes source database overhead.

---

2. What replaced Data Pump in GoldenGate Microservices?

Better Interview Answer

In GoldenGate Classic Architecture:

Extract
   ↓
Data Pump
   ↓
Remote Trail
   ↓
Replicat

In Microservices Architecture:

Extract
   ↓
Distribution Service
   ↓
Receiver Service
   ↓
Replicat

The Distribution Service performs the same function as Data Pump by moving trail files from source to target using secure HTTPS communication.

---

3. What is a Checkpoint Table?

A Checkpoint Table stores Replicat progress information inside the target database.

It contains:

• Current trail file sequence
• RBA (Relative Byte Address)
• Transaction status
• Commit position

Benefits:

• Fast recovery after restart
• Prevents duplicate transaction processing
• Prevents data loss

Common Interview Question

Is Checkpoint Table mandatory?

Answer:

• Classic Replicat → Recommended
• Coordinated Replicat → Mandatory
• Parallel Replicat → Mandatory

---

4. Classic Extract vs Integrated Extract

Interview Table

Classic Extract	Integrated Extract
Reads redo logs directly	Uses LogMiner server
Runs outside DB	Integrated with Oracle DB
Limited support for new features	Supports TDE, RAC, CDB/PDB
Lower scalability	Higher scalability
Legacy deployments	Recommended for Oracle 12c+

Diagram

Classic

Redo Logs
    ↓
 Extract

Integrated

Redo Logs
    ↓
 LogMiner Server
    ↓
 Integrated Extract

Interview Favorite Question

How do you check Integrated Extract?

SELECT capture_name,status
FROM dba_capture;

---

5. Types of Replicat

Complete Answer

1. Classic Replicat

Trail
  ↓
Single Thread
  ↓
Target DB

• Sequential apply
• Low throughput

---

2. Integrated Replicat

Trail
  ↓
Inbound Server
  ↓
Parallel Apply

Uses Oracle Database Inbound Server.

Advantages:

• Dependency tracking
• Parallel processing
• Better performance

---

3. Coordinated Replicat

Trail
  ↓
Coordinator
  ↓
Multiple Threads

• Multiple worker threads
• User-controlled parallelism

---

4. Parallel Replicat

Newest and fastest.

Trail
   ↓
Mapper
   ↓
Master
   ↓
Apply Servers

Best for:

• High-volume OLTP systems
• Zero downtime migrations

---

6. Replicat hits ORA-00001

Better Senior-Level Answer

By default:

Replicat ABENDS

Investigation:

VIEW REPORT rep1

or

INFO ALL

Check:

ggserr.log
discard file
report file

Possible causes:

• Duplicate records already exist
• Missing transactions
• Out-of-sync target

Temporary workaround:

REPERROR (1, DISCARD)

or

HANDLECOLLISIONS

(only during migration/cutover phases)

Interview Trick

Q: Is HANDLECOLLISIONS recommended permanently?

A: No. Only during initial load synchronization.

---

7. How do you troubleshoot Replicat lag?

Step-by-Step Senior DBA Answer

Check Lag

LAG REPLICAT REP1

or

SEND REPLICAT REP1 STATUS

Check Long Running Transactions

SEND EXTRACT EXT1 SHOWTRANS

---

Check Database Wait Events

SELECT event,total_waits
FROM v$system_event;

---

Common Causes

Missing Indexes

UPDATE target_table
WHERE primary_key = :1

Without PK:

FULL TABLE SCAN

Every update becomes slow.

---

Large Transactions

10 million rows in one commit

Replicat waits until complete transaction arrives.

---

Insufficient Parallelism

Use:

PARALLELISM 8

or

APPLY_PARALLELISM 8

---

8. Explain Zero Downtime Migration using GoldenGate

Step 1

Start Extract

ADD EXTRACT EXT1, INTEGRATED TRANLOG
START EXTRACT EXT1

---

Step 2

Capture SCN

SELECT current_scn FROM v$database;

Example:

SCN = 123456789

---

Step 3

Export Source

expdp system/password flashback_scn=123456789

---

Step 4

Import into Target

impdp system/password

---

Step 5

Start Replicat

START REPLICAT REP1, AFTERCSN 123456789

(AFTERCSN is the commonly used parameter.)

---

Step 6

Monitor Lag

LAG EXTRACT EXT1
LAG REPLICAT REP1

---

Step 7

Cutover

Stop Application
Verify Lag = 0
Point Application to Target
Start Application

---

Q9. What are Trail Files?

Trail files are GoldenGate's proprietary files that store captured transactional data between Extract and Replicat.

Example:

Source DB
   ↓
Extract
   ↓
Trail File (aa000001)
   ↓
Replicat
   ↓
Target DB

---

Q10. Difference between CSN and SCN?

• SCN (System Change Number) → Oracle database commit number.
• CSN (Commit Sequence Number) → Generic GoldenGate commit identifier.

For Oracle databases, CSN is typically derived from SCN.

---

Q11. What is Supplemental Logging and why is it required?

GoldenGate needs enough column information in redo logs to uniquely identify rows.

Enable:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Without supplemental logging:

OGG-xxxxx
Key column missing
Replicat cannot apply updates

This is one of the most frequently asked Oracle GoldenGate interview questions for Oracle DBA, OCI Migration, and Zero-Downtime Migration roles.

Q12: If Extract is stopped for 2 hours, will transactions be lost?

Answer: No.

Oracle Redo Logs and Archive Logs retain the changes. When Extract starts again, it resumes from its last checkpoint and generates the missing trail records from the logs, provided the required archived logs are still available. This is why archive log retention is critical for GoldenGate environments.

Fast-Connect in OCI