✅ RMAN Cross-Platform Tablespace Transport Over Network (SSC → Exadata)

Starting with Oracle Database 12.2, it’s now possible to transport tablespaces across platforms directly over the network using RMAN’s FROM SERVICE clause and the RESTORE FOREIGN DATAFILE command. This functionality enables database administrators to migrate tablespaces from a source environment (e.g., SSC) to a target environment (e.g., Exadata) without manually copying backup files, greatly simplifying and speeding up the process.

This article presents a detailed, step-by-step migration guide based on a real production migration.

🧭 Migration Methods

1. No Archive Log Mode
   • Requires full downtime.
   • Tablespaces must be placed in READ ONLY mode before migration.
2. Archive Log Mode
   • Datafiles are restored first while the application stays online.
   • Final recovery and READ ONLY conversion are performed just before the cutover.
   • Downtime is reduced significantly.

📚 Reference: Oracle Support Doc ID 2307383.1 — “RMAN Cross Platform Transport Over Network”

🛠️ Step 1: Prepare the Environment

Before executing any commands, ensure the following substitutions are made in your scripts:

• DG_GROUP: +DATAC10
• DB_NAME: ILGUAT
• HOST_NAME: exat3c10a-vip, exat4c10b-vip
• PORT: 1523
• PASSWORD: ILGUAT123#

Listener Setup (on Exadata)

bash. ./asmsrvctl add listener -listener ILGUAT -endpoints 1523 srvctl start listener -listener ILGUAT srvctl status listener -listener ILGUAT

On both nodes, update tnsnames.ora:

bash. ./home19cvi $ORACLE_HOME/network/admin/tnsnames.ora

Add entries:

LISTENER_ILGUAT1 = (ADDRESS = (PROTOCOL = TCP)(HOST = exat3c10a-vip)(PORT = 1523))
LISTENER_ILGUAT2 = (ADDRESS = (PROTOCOL = TCP)(HOST = exat4c10b-vip)(PORT = 1523))

LISTENERS_ILGUAT = (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = exat3c10a-vip)(PORT = 1523))
  (ADDRESS = (PROTOCOL = TCP)(HOST = exat4c10b-vip)(PORT = 1523))
)

ILGUAT_MAIN = (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = exat3c10a-vip)(PORT = 1523))
  (ADDRESS = (PROTOCOL = TCP)(HOST = exat4c10b-vip)(PORT = 1523))
  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ILGUAT_MAIN))
)

ILGUAT_SSC = (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = usdbtc1d1z1-vip)(PORT = 1522))
  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ILGUAT))
)

ALTER SYSTEM SET local_listener='LISTENER_ILGUAT1' SID='ILGUAT1' SCOPE=BOTH;
ALTER SYSTEM SET local_listener='LISTENER_ILGUAT2' SID='ILGUAT2' SCOPE=BOTH; ALTER SYSTEM SET remote_listener='LISTENERS_ILGUAT' SCOPE=BOTH;

🔍 Step 2: Pre-Migration Checks on SSC

Check Segment Allocation

Run the following to verify which tablespaces are used by non-Oracle-managed users:

sqlSELECT owner, tablespace_name, COUNT(*)<strong>FROM</strong> dba_segments <strong>WHERE</strong> owner <strong>IN</strong> ( <strong>SELECT</strong> username <strong>FROM</strong> dba_users <strong>WHERE</strong> oracle_maintained = 'N' ) <strong>GROUP</strong> <strong>BY</strong> owner, tablespace_name <strong>ORDER</strong> <strong>BY</strong> owner, tablespace_name;

Validate Tablespace Transportability

Generate a script to run DBMS_TTS.TRANSPORT_SET_CHECK:

sqlEXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘DATA_1M,DATA_256K,…’, TRUE, TRUE);<strong>SELECT</strong> * <strong>FROM</strong> TRANSPORT_SET_VIOLATIONS;

📁 Step 3: Setup on Exadata

• Create folders: /home/oracle/ILGUAT/SCRIPT, /LOG, /PARFILE
• Create DB Link:

sqlCREATE PUBLIC DATABASE LINK ILGUAT_SSC<strong>CONNECT</strong> <strong>TO</strong> <strong>system</strong> IDENTIFIED <strong>BY</strong> ILGUAT123# <strong>USING</strong> 'ILGUAT_SSC';

• Create directory object:

sqlCREATE DIRECTORY MIG_LOG_DIR AS'/home/oracle/ILGUAT/LOG';

• Configure RMAN:

sqlCONFIGURE DEVICE TYPE DISK PARALLELISM 5 BACKUP TYPE TO BACKUPSET;CONFIGURE SNAPSHOT CONTROLFILE NAME <strong>TO</strong> '+DATAC10/ILGUAT/snapcf_ILGUAT.f';

🔐 Step 4: Configure Password Verification

Create a custom PASSVERIFY function and link it to the DEFAULT profile. This ensures secure passwords are enforced during and after migration.

🔄 Step 5: Cleanup and Permissions (SSC)

• Alter users with SYSTEM local temp TBS
• Grant CREATE DATABASE LINK to all application users
• Purge recycle bin:

sqlPURGE DBA_RECYCLEBIN;

• Recompile invalid objects:

sql@$ORACLE_HOME/rdbms/admin/utlrp.sql

📦 Step 6: Tablespace Read-Only and Restore

Set tablespaces to READ ONLY on SSC:

sqlALTERTABLESPACE DATA_1M READ ONLY;<strong>ALTER</strong> <strong>TABLE</strong>SPACE INDX_1M READ <strong>ONLY</strong>; ...

Restore foreign datafiles from SSC using RESTORE FOREIGN DATAFILE with FROM SERVICE and specify format:

sqlRESTORE FOREIGN DATAFILE 12,13,14 FORMAT ‘+DATAC10’FROM SERVICE ILGUAT_SSC;

🛠️ Step 7: Import Metadata with Data Pump

Prepare a parfile:

iniNETWORK_LINK=ILGUAT_SSCTRANSPORT_DATAFILES='+DATAC10/ILGUAT/DATAFILE/*'
TRANSPORTABLE=ALWAYS
PARALLEL=10
DIRECTORY=MIG_LOG_DIR
LOGFILE=TRANSPORT_TABLESPACES.log

Run import:

bashimpdp \”/ as sysdba\” PARFILE=TRANSPORT_TABLESPACES.par

✅ Step 8: Post-Migration Adjustments

• Reset users’ default tablespace to USERS if it was temporarily changed
• Recompile any invalid objects and verify component statuses:

sqlSELECT comp_name, status, version FROM dba_registry;

Copy roles, system privileges, quotas, and object privileges from SSC using dynamic SQL scripts

🧪 Step 9: Validation

Object Count Validation

Compare object counts between SSC and Exadata using:

sqlSELECT owner, COUNT(*) FROM dba_objects GROUPBY owner;

Repeat per object type and resolve any mismatch using impdp with EXCLUDE=STATISTICS

🔁 Step 10: Import Other Components

• DB Links (INCLUDE=DB_LINK)
• Public Synonyms
• Scheduler and DBMS Jobs
• ACLs (if applicable)

Each imported using separate impdp parfiles with appropriate includes.

🚀 Step 11: Cutover and Cleanup

• Restart Exadata database:

bashsrvctl stop database -d ILGUATsrvctl start database -d ILGUAT

• Unlock and set DBSNMP password
• Disable SSC database and listener

📊 Step 12: Gather Statistics

Run post-migration stats collection:

sql<strong>EXEC</strong> DBMS_STATS.GATHER_DICTIONARY_STATS; <strong>EXEC</strong> DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; <strong>EXEC</strong> DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA'); <strong>EXEC</strong> DBMS_STATS.GATHER_DATABASE_STATS(degree => 10);

Clean up:

sqlDROP DATABASE LINK ILGUAT_SSC;<strong>DROP</strong> DIRECTORY MIG_LOG_DIR; rm -rf /home/oracle/ILGUAT.

🎉 Migration Complete!

This end-to-end RMAN cross-platform migration significantly reduces operational overhead and ensures a smooth transition to Exadata with minimal downtime. Always validate each step with logs and test results before final production cutover.

Tags: