Optimizing LOB Data Migration Performance in AWS DMS for Oracle

When migrating Oracle databases to Amazon RDS using AWS Database Migration Service (DMS), Large Object (LOB) columns present unique challenges that can significantly impact migration performance. Understanding your LOB data distribution is essential for choosing the right migration strategy and achieving optimal results.

Why LOB Analysis Matters

Migrating Oracle databases and underestimate LOB complexity, results in:

• Severely degraded migration performance (days instead of hours)
• Data truncation without proper configuration
• Memory exhaustion on DMS replication instances

This guidance is particularly relevant for:

• Migrations from Oracle Enterprise or Standard Edition to Amazon RDS
• Databases with CLOB, BLOB, or NCLOB columns
• Migrations requiring minimal downtime
• Large databases where Oracle Data Pump isn't efficient

AWS DMS LOB Migration Modes

Limited LOB Mode (Recommended)

• Limited LOB mode is faster than full LOB mode.
• Treats LOB columns as VARCHAR data types
• Pre-allocates memory and loads LOB data in bulk
• A limited LOB size of less than 32 K is optimal when Oracle is your source database
• Maximum value for max LOB size is 102400 KB (100 MB), however, may affect overall full load performance
• Any data exceeding the max LOB size will be truncated with a warning in DMS logs
• Primary key required for CDC operations only (not for full load)

Full LOB Mode

• Migrates the entire column regardless of LOB size
• Slowest option for LOB migration
• When using Full LOB Mode LOBS are migrated piecewise in chunks size (default 64 KB) to distribute LOB data in pieces
• Performs two-step approach: inserts empty_clob or empty_blob, then performs lookup and update
• Primary key required for both full load and CDC operations
• If no primary key exists, DMS removes the LOB column from migration

Inline LOB Mode (Maximum LOB size (KB) that DMS transfers inline in the Full LOB Mode)

• Hybrid approach combining limited and full LOB mode functionality
• Provides better performance when loading LOB objects
• DMS chooses inline or LOB lookup based on data size and task configuration
• LOBs smaller than the specified size defined in Maximum LOB size (KB) are transferred inline. LOBs larger than the specified size are replicated using full LOB mode.
• Set a value for Maximum LOB size (KB) (InlineLobMaxSize) only if you know that most of the LOBs are smaller than the value specified. The maximum value for InlineLobMaxSize is 102400 kilobytes (100 MB).
• Primary key required for CDC operations and for larger LOBs during full load

Important Note: A primary key is mandatory for tables containing LOB columns during Change Data Capture (CDC) operations. DMS uses this key to look up LOB values in the source table. This requirement only applies to CDC tasks - full-load tasks can read and copy entire LOB columns directly from source to target without restrictions.

Analyzing Your LOB Distribution

Before selecting a migration strategy, analyze your LOB data distribution for each table and each LOB column individually. If you have multiple tables with LOB columns or multiple LOB columns within a table, this analysis must be performed separately for each table and column combination.

Find Maximum LOB Size

• Find the max LOB size using Oracle system tables:

SELECT 'SELECT (MAX(LENGTH(' || COLUMN_NAME || '))/(1024)) as "Size in KB" from ' || owner || '.' || TABLE_NAME ||';' "maxlobsizeqry"
FROM dba_tab_cols
WHERE owner= 'schema_name' AND data_type in ('CLOB','BLOB','LOB');

• Find the max LOB size using per tables/column:

SELECT MAX(DBMS_LOB.GETLENGTH(lob_column_name)/1024) AS max_kb 
FROM table_name;

• If max LOB size < 64 KB → Limited LOB mode is optimal
• If max LOB size > 64 KB → Perform the LOB distribution analysis and explore Full LOB or Inline LOB mode.

Understand LOB distribution

Perform this analysis query:

-- Create analysis table (run on development/standby instance)
CREATE TABLE dms_lob_analysis AS
SELECT /*+ FULL(t) PARALLEL(t, 6) */
WIDTH_BUCKET(DBMS_LOB.GETLENGTH(lob_column_name), 1024, 65536000, 1000) bucket, 
MIN(DBMS_LOB.GETLENGTH(lob_column_name)) min_bytes,
MAX(DBMS_LOB.GETLENGTH(lob_column_name)) max_bytes,
ROUND(AVG(DBMS_LOB.GETLENGTH(lob_column_name))) avg_bytes,
COUNT(*) row_count,
ROUND(100*RATIO_TO_REPORT(COUNT(*)) OVER (), 2) pct_rows
FROM schema_name.table_name
WHERE DBMS_LOB.GETLENGTH(lob_column_name) > 1
GROUP BY WIDTH_BUCKET(DBMS_LOB.GETLENGTH(lob_column_name), 1024, 65536000, 1000)
ORDER BY 1;

View Distribution Results

-- Analyze cumulative distribution
SELECT bucket, min_bytes, max_bytes, avg_bytes,
ROUND(total_bytes/1024/1024) total_mb, row_count, pct_rows,
ROUND((SUM(row_count) OVER (ORDER BY bucket)/(SELECT SUM(row_count) FROM dms_lob_analysis) * 100),2) cumulative_pct_rows
FROM dms_lob_analysis
ORDER BY bucket;

Ensure to run it in your standby or development instance before running on your production.

Migration Strategy Examples

Scenario 1: Mostly Small LOBs

Column Maximum LOB Size: 10MB

Perform LOB distribution analyses: Yes

Distribution: 85% of rows < 32 KB, 15% > 1 MB

Strategy: Inline LOB mode with 32 KB threshold

{
    "TargetMetadata": {
    "SupportLobs": true,
    "FullLobMode": true,
    "InlineLobMaxSize": 32,
    "LimitedSizeLobMode": false,
    "LobChunkSize": 64,

Scenario 2: Large Uniform LOBs

Column Maximum LOB Size: 150MB

Perform LOB distribution analyses: Yes

Distribution: Most LOBs > 100 MB

Strategy: Full LOB mode with optimized settings

Considerations: Increase replication instance memory, monitor source performance

{
    "TargetMetadata": {
        "TargetSchema": "",
        "SupportLobs": true,
        "FullLobMode": true,
        "LobChunkSize": 64,
        "LimitedSizeLobMode": false,
        "LobMaxSize": 32,
        "InlineLobMaxSize": 0,
        "LoadMaxFileSize": 0,

Performance Optimization

Key DMS Parameters

• MaxFullLoadSubTasks: Increase from 8 to 49 for parallel processing
• StreamBufferSettings: Increase the specific parameter from the default of 8MB for LOB-heavy tables
• InlineLobMaxSize: Set based on distribution analysis
• LobChunkSize: Optimize for network and memory constraints

Target RDS Optimizations

• Disable archive logging during migration
• Disable Multi-AZ temporarily
• Increase redo log size to prevent frequent switches
• Create indexes after full load

Common Issues and Solutions

Data Truncation Warning

[SOURCE_UNLOAD]W: The value of column 'DATA' was truncated to length 32768

Solution: Increase MaxLobSize or switch to full LOB mode

Missing Primary Key Error

Column 'B' was removed: LOB column and table has no primary key

Solution: Add primary key or use limited LOB mode only

Performance Issues

Symptoms: Migration estimates of multiple days
Solutions:

• Analyze LOB distribution thoroughly
• Increase MaxFullLoadSubTasks appropriately
• Use parallel load with range partitioning
• Right-size replication instance

Best Practices Summary

• Always analyze LOB distribution before migration
• Test strategies on development instances first
• Monitor replication instance metrics during migration
• Consider primary key requirements for full and inline modes
• Optimize target RDS settings during migration

Conclusion

Understanding LOB data distribution is critical for Oracle migration success with AWS DMS. Proper analysis and configuration can reduce migration time from days to hours while ensuring data integrity. The key is matching your LOB distribution pattern to the appropriate DMS configuration, optimizing both source and target settings, and monitoring performance throughout the process.

References

Setting LOB support for source databases in an AWS DMS task

Specifying task settings for AWS Database Migration Service tasks

Achieve a high-performance migration to Amazon RDS for Oracle from on-premises Oracle with AWS DMS