Enhanced Oracle LOB Support

Oracle supports replication of LOBs (Large Objects) in distributed environments, but with important considerations depending on the replication technology used:
✅ LOB Replication Support in Oracle Distributed Environments

1. Multimaster Replication (Legacy Advanced Replication)
   • CLOBs, BLOBs, and NCLOBs are supported in Oracle Advanced Replication.
   • However, Oracle has deprecated Advanced Replication since Oracle 12c, and it is not recommended for new development.
   • If used, only SECUREFILE LOBs (with certain options disabled) are reliably supported in replication without corruption or unexpected behavior.
2. Oracle GoldenGate (Current Preferred Method)
   • GoldenGate in Oracle 19c, 21c, and 23c fully supports replication of:
     
     • BLOB (Binary Large Object): This data type stores unstructured binary data. Think of it as a container for any kind of file, such as images, audio, video, or even compiled code. Oracle treats it as a stream of bytes without any character set interpretation.
     • CLOB (Character Large Object): Designed for storing single-byte character data, a CLOB holds large amounts of text using the database character set. This is ideal for storing documents, long text fields, or source code.
     • NCLOB (National Character Large Object): Similar to CLOB, but NCLOBs are specifically for storing multi-byte national character set data (like Unicode). This allows you to store text in various languages that require more than one byte per character.
     • XMLTYPE stored as LOB: While XMLTYPE is a specific data type for storing XML documents, it can be physically stored in the database as a LOB (either CLOB or BLOB). This approach is often chosen when dealing with very large XML documents, as it allows for more efficient storage and manipulation of large text or binary XML content.
   • Key features:
     • Uses trail files and LOB chunking to capture and replicate LOBs efficiently.
     • Supports both inline and out-of-row LOB storage.
     • Offers LOB caching and truncation optimizations.
   • Note: LOBs can be replicated in near real-time in both 1) homogeneous (Oracle-to-Oracle) and 2) heterogeneous (Oracle-to [non-Oracle]) environments.
3. Materialized Views with LOBs
   • Oracle does not recommend using materialized views to replicate LOBs, as there are restrictions.
   • LOB replication through materialized views can lead to issues like 1) incomplete refreshes, 2) data corruption, or 3) unsupported syntax for complex LOB operations.

🔒 Key Considerations for LOB Replication:

• Ensure network bandwidth is sufficient due to large object sizes.
• Use LOB truncation and compression options if appropriate.
• Monitor replication lag when transferring large volumes of LOB data.
• Always test replication of LOB operations (INSERT, UPDATE, DELETE) across sites under your replication setup.

📌 Conclusion: Oracle supports LOB replication in distributed environments, especially using Oracle GoldenGate, which is the preferred and supported technology for modern deployments. Advanced Replication (Multimaster) also supported LOBs historically, but it is deprecated in favor of newer, more robust solutions like GoldenGate.

The Oracle Database has been able to store large objects since Oracle8 added the capability to store multiple LOB columns in each table. Oracle Database 10g essentially removed the space limitation on large objects. Oracle Database 11g greatly improved the performance of query and insert operations when used with LOBs through the introduction of SecureFiles. SecureFiles serve as a place to securely store LOBs in the Oracle Database instead of in filesystems while delivering performance similar to that experienced when LOBs are stored in filesystems. Transparent Data Encryption, a security feature described below and later in the book, is supported for SecureFiles LOB data.

• CLOB and NCLOB
  The CLOB and NCLOB datatypes can store up to 4 GB of character data prior to Oracle Database 10g. Starting with Oracle Database 10g, the limit has been increased to 128 TBs, depending on the block size of the database. The NCLOB datatype stores the NLS data. Oracle Database 10g and later releases implicitly perform conversions between CLOBs and NCLOBs.

[1] Multimaster replication: Advanced replication features allowing updates on tables in remote sites as if every site were the master site.