• Home
  • About
  •  

    Oracle GoldenGate – Truncate Statements

    Oracle GoldenGate – Standalone Truncate

    This post describes Oracle GoldenGate support for standalone TRUNCATE statements. Full DDL support is also available for the TRUNCATE statement. This will be covered in a later post. The two options are mutually exclusive.

    The post is based on Oracle GoldenGate version 11.2.1.0.1. For a basic configuration I used two Linux VMs (OEL5U6) running single instance Oracle 11.2.0.3 databases. I created both databases using DBCA.

    This post assumes that the source and target databases have already been configured as described in a previous post: Oracle GoldenGate – Basic Configuration.

    This configuration uses following hosts and databases.

      Source Target
    Hostname vm4 vm5
    Database Name NORTH SOUTH

    The configuration includes the following on both nodes:

    • Creation of a GoldenGate schema owner called GG01.
    • Specification of GGSCHEMA as GG01 in GoldenGate parameters
    • Creation of GOLDENGATE tablespace which is default tablespace for GG01

    The GoldenGate process names are:

      Source Target
    Extract ex1 -
    Data Pump dp1 -
    Replicat - rep1

    TRUNCATE statement.

    Oracle is optimized to perform insertion very efficiently. As a trade-off, consequently delete operations are relatively slow. In order to provide faster deletions of an entire table the TRUNCATE statement in Oracle 7.0.

    The TRUNCATE statement has two basic formats:

    TRUNCATE TABLE 
    
    TRUNCATE TABLE  REUSE STORAGE;
    

    Both statements delete all rows in a table. The rows are not physically deleted; instead the pointers are adjusted so that the table appears to be empty.

    TRUNCATE TABLE  releases all extents except for the first one. 
    
    TRUNCATE TABLE  REUSE STORAGE does not release any extents. 
    

    If the table will subsequently be re-populated with a similar amount of data, then the REUSE STORAGE option is more efficient as it reduces the amount of extent maintenance required.

    In both cases the data object ID is modified when the table is truncated; the object ID remains unchanged. For example:

    CREATE TABLE t1 AS
    SELECT object_id,owner,object_name,object_type
    FROM dba_objects
    WHERE ROWNUM < 1000;
    

    Initially both the object ID and data object ID will be identical:

    SELECT object_id,data_object_id FROM dba_objects
    WHERE owner = 'US01'
    AND object_name = 'T1'
    AND object_type = 'TABLE';
    
    OBJECT_ID DATA_OBJECT_ID
    ---------- --------------
         76842          76842
    

    The table is now truncated using:

    TRUNCATE TABLE t1;
    

    The object ID will be unchanged, but a new data object ID will have been allocated:

    SELECT object_id,data_object_id FROM dba_objects
    WHERE owner = 'US01'
    AND object_name = 'T1'
    AND object_type = 'TABLE';
    
    OBJECT_ID DATA_OBJECT_ID
    ---------- --------------
         76842          76843
    

    GoldenGate TRUNCATE Support

    Oracle GoldenGate supports the DDL TRUNCATE statement. There are two, mutually exclusive options:

    • Standalone TRUNCATE support. This supports the TRUNCATE TABLE statement, but does not support truncation of table partitioning. It is controlled by the GETTRUNCATES parameter and is discussed in this post.
    • Full DDL support. This includes support for TRUNCATE TABLE and ALTER TABLE TRUNCATE PARTITION. It is controlled by the DDL parameter and is out of scope for this post.

    Standalone TRUNCATE Support

    Presumably stand-alone support for the TRUNCATE statement was added before full DDL support was introduced.

    By default TRUNCATE commands are ignored by both the extract and replicat processes. To configure stand-alone TRUNCATE support, the GETTRUNCATES parameter must be added to the parameters of the extract, data pump and replicat processes.

    Note that the GETTRUNCATES parameter must appear in the parameter file BEFORE the TABLE parameter for extract processes and BEFORE the MAP parameter for replicat processes. If the GETTRUNCATES parameter appears after the TABLE/MAP parameters it will be ignored for those tables.

    For example consider the following table in the US03 schema:

    CREATE TABLE t1
    (
      object_id NUMBER,
      owner     VARCHAR2(30),
      object_name VARCHAR2(128),
      object_type VARCHAR2(19)
    );
    

    As DDL support has not been configured, the table must be manually created in both the source and target schemas.

    GoldenGate extract is initially configured in the source database as follows:

    The parameters for the extract process (ex1) for the source database are:

    EXTRACT ex1
    USERID gg01, PASSWORD gg01
    EXTTRAIL /home/oracle/goldengate/dirdat/ex
    TABLE us03.t*;
    

    The parameters for the data pump process (dp1) for the source database are:

    EXTRACT dp1
    USERID gg01, PASSWORD gg01
    RMTHOST vm5, MGRPORT 7809
    RMTTRAIL /home/oracle/goldengate/dirdat/rt
    TABLE us03.t*;
    

    The parameters for the replicat process (rep1) for the target database are:

    REPLICAT rep1
    USERID gg01, PASSWORD gg01
    ASSUMETARGETDEFS
    DISCARDFILE /home/oracle/goldengate/discards, PURGE
    MAP US03.*, TARGET US03.*;
    

    In the source database, the table can be populated as follows

    INSERT INTO t1
    SELECT object_id,owner,object_name,object_type
    FROM dba_objects
    WHERE ROWNUM < 1000;
    
    COMMIT;
    

    In this example, 999 rows are added to the table in the source database:

    SQL> SELECT COUNT(*) FROM t1;
    
      COUNT(*)
    ----------
           999
    

    These rows will be propagated to same table in the target database;

    The table is truncated using:

    SQL> TRUNCATE TABLE t1;
    

    In the source database, the table will be empty:

    SQL> SELECT COUNT(*) FROM t1;
    
      COUNT(*)
    ----------
             0
    

    However, in the target database, the rows will still remain:

    SQL> SELECT COUNT(*) FROM t1;
    
      COUNT(*)
    ----------
           999
    

    The tables are now inconsistent.

    Note that the table in the target database can be truncated manually to bring the tables back into line again.

    Redo is generated when the table is truncated. However, the extract process does not add the truncate operation to the GoldenGate trail by default.

    The DDL statement is included in the online redo log in operation 24.1.

    The online redo log dump does not include the DDL. However, the RedoAnalyzer does report the DDL which, in this case is stored as a null-terminated string in element 7 (base 0) of the change.

    REDO RECORD - Thread:1 RBA: 0x000051.00000018.0010 LEN: 0x0180 VLD: 0x05
    SCN: 0x0000.001b9724 SUBSCN:  1 04/05/2013 08:29:58
    CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:24.1
    
    TRUNCATE TABLE t1
    
    

    To specify that truncate operations should be included in the GoldenGate trail add the GETTRUNCATES parameter to each of the parameter files:

    The parameters for the extract process (ex1) for the source database are:

    EXTRACT ex1
    USERID gg01, PASSWORD gg01
    EXTTRAIL /home/oracle/goldengate/dirdat/ex
    GETTRUNCATES
    TABLE us03.t*;
    

    The parameters for the data pump process (dp1) for the source database are:

    EXTRACT dp1
    USERID gg01, PASSWORD gg01
    RMTHOST vm5, MGRPORT 7809
    RMTTRAIL /home/oracle/goldengate/dirdat/rt
    GETTRUNCATES
    TABLE us03.t*;
    

    The parameters for the replicat process (rep1) for the target database are:

    REPLICAT rep1
    USERID gg01, PASSWORD gg01
    ASSUMETARGETDEFS
    DISCARDFILE /home/oracle/goldengate/discards, PURGE
    GETTRUNCATES
    MAP US03.*, TARGET US03.*;
    

    When GETTRUNCATES is specified in the extract parameter file, an additional record is included in the GoldenGate trail when a TRUNCATE operation is detected in the online redo log:

    After the extract and replicat processes have been restarted, the statement

    TRUNCATE TABLE t1;
    

    will truncate all rows from T1 in both the source and target databases.

    The TrailAnalyzer shows that truncate operation has been captured by the extract process.

    # Header: Type=47 (G) Flag=1 Len=81 (4 bytes)
    <47>
      # Body
      # Header: Type=48 (H) Flag=0 Len=42 (4 bytes)
      <47_48>
        # Row Header (42 bytes)
        # Flags - 0x64 Standalone Truncate
        # Timestamp 2013:04:05 06:31:08 000000 ms
        # Object US03.T1
      
      # Header: Type=54 (T) Flag=0 Len=23 (4 bytes)
      <47_54>
        # Row Metadata (23 bytes)
        # Header: Type=4C (L) Flag=0 Len=7 (4 bytes)
        <47_54_4C>
          # Commit SCN (7 bytes)
          1804678
        
        # Header: Type=36 (6) Flag=0 Len=8 (4 bytes)
        <47_54_36>
          # Transaction ID (8 bytes)
          3.0.1082
        
      
    
    # Header: Type=5A (Z) Flag=1 Len=81 (4 bytes)
      
      # Header: Type=54 (T) Flag=0 Len=23 (4 bytes)
      <47_54>
        # Row Metadata (23 bytes)
        # Header: Type=4C (L) Flag=0 Len=7 (4 bytes)
        <47_54_4C>
          # Commit SCN (7 bytes)
          1804678
        
        # Header: Type=36 (6) Flag=0 Len=8 (4 bytes)
    

    Note that the above record does not include a Row Data section.

    A similar record is propagated to the target server by the data pump process.

    End of post

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    HTML tags are not allowed.