Oracle GoldenGate - Truncate Statement

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

The page 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 page assumes that the source and target databases have already been configured as described here.

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 page.
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 page.

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

<DDL>
TRUNCATE TABLE t1
</DDL>

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.