Thursday, October 10, 2013

Can Oracle GoldenGate DDL replication support interval partition?

We have a fast-growing table that requires keeping only two months' worth of data. We plan to take advantage of the interval partition feature in Oracle 11g, in which Oracle automatically creates an interval partition as data for that partition is inserted. Therefore, we will not worry about data load failure situation due to the possibility of forgetting to add new partitions by DBA.

In our environment, this table will also be in Oracle GoldenGate replication configuration.   So I try to do tests to confirm whether the GG DDL replication supports the following two operations:

1. Create interval partition

2. Drop partition

I used a test GG environment, in which  a two-way GG replication with DDL replication enabled  is set up between two databases: WESTDB and EASTDB; and the tables are in different schemas called west and east respectively.

The following are the testing steps:

1. create a table named interval_tab in the source db
 denis@WESTDB>> CREATE TABLE west.interval_tab
   2  ( prod_id        NUMBER(6)
   3   , cust_id        NUMBER
   4   , time_id        DATE
   5  )
   6  PARTITION BY RANGE (time_id)
   7  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   8   ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2013', 'MM-DD-YYYY')),
   9     PARTITION p1 VALUES LESS THAN (TO_DATE('2-1-2013', 'MM-DD-YYYY')),
  10     PARTITION p2 VALUES LESS THAN (TO_DATE('3-1-2013', 'MM-DD-YYYY')),
  11     PARTITION p3 VALUES LESS THAN (TO_DATE('4-1-2013', 'MM-DD-YYYY')) );

 Table created.

Verify it is replicated in target:
 denis@EASTDB>> desc east.interval_tab;
  Name                                                                          Null?    Type
  ----------------------------------------------------------------------------- -------- ---------------------
  PROD_ID                                                                                NUMBER(6)
  CUST_ID                                                                                NUMBER
  TIME_ID                                                                                DATE

2. create indexes and contraint At source:
 denis@WESTDB>> create unique index west.interval_tab_pk on west.interval_tab(prod_id, time_id) local;

 Index created.

 denis@WESTDB>> create index west.interval_tab_ix1 on west.interval_tab(cust_id) local;

 Index created.

 denis@WESTDB>> alter table west.interval_tab add constraint interval_tab_pk primary key (prod_id, time_id) using index;

 Table altered.

Verify index and constraints creation are replicated at target:
 denis@EASTDB>> @tabix
 Enter value for tabowner: east
 Enter value for tabname: interval_tab

 TABLE_NAME           INDEX_NAME           COLUMN_NAME             COL_POS UNIQUENES
 -------------------- -------------------- -------------------- ---------- ---------
 INTERVAL_TAB         INTERVAL_TAB_IX1     CUST_ID                       1 NONUNIQUE
 INTERVAL_TAB         INTERVAL_TAB_PK      PROD_ID                       1 UNIQUE
 INTERVAL_TAB                              TIME_ID                       2 UNIQUE


 denis@EASTDB>> select owner,table_name,  constraint_name from dba_constraints where table_name='INTERVAL_TAB';

 OWNER           TABLE_NAME           CONSTRAINT_NAME
 --------------- -------------------- --------------------
 EAST            INTERVAL_TAB         INTERVAL_TAB_PK

3. Insert data at source that will cause new interval partition created automatically

Before the insert, check the current partitions:
 denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER      TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ---------------- -------------------- -------------------- ---------------------------------------------
 WEST             INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
                  HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST             INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
                                                                         HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
Perform the following insert at source and check the partitions:

insert into interval_tab values (1004,1, sysdate);
 denis@WESTDB>> insert into west.interval_tab values (1004,1, sysdate);

 1 row created.

 denis@WESTDB>> commit;

 Commit complete.

 denis@WESTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER                    TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ------------------------------ -------------------- -------------------- ---------------------------------------------
 WEST                           INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 WEST                           INTERVAL_TAB         SYS_P81              TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 5 rows selected.

4. Check partitions at target database
 denis@EASTDB>> select table_owner, table_name, partition_name, high_value from dba_tab_partitions where table_name='INTERVAL_TAB';

 TABLE_OWNER            TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ---------------------- -------------------- -------------------- ---------------------------------------------
 EAST                   INTERVAL_TAB         P0                   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
          HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                   INTERVAL_TAB         SYS_P123             TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 5 rows selected.


 denis@EASTDB>> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='INTERVAL_TAB_IX1';

 INDEX_OWNER                    INDEX_NAME           PARTITION_NAME
 ------------------------------ -------------------- --------------------
 EAST                           INTERVAL_TAB_IX1     P0
 EAST                                                P1
 EAST                                                P2
 EAST                                                P3
 EAST                                                SYS_P123

 5 rows selected.

5. Drop a partition at source alter table west.interval_table drop partition P0;
 denis@WESTDB>> alter table west.interval_tab drop partition P0;

 Table altered.


 Verified, it is replicable


 denis@EASTDB>> /

 TABLE_OWNER                    TABLE_NAME           PARTITION_NAME       HIGH_VALUE
 ------------------------------ -------------------- -------------------- ---------------------------------------------
 EAST                           INTERVAL_TAB         P1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         P2                   TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         P3                   TO_DATE(' 2013-04-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 EAST                           INTERVAL_TAB         SYS_P123             TO_DATE(' 2013-11-01 00:00:00', 'SYYYY-MM-DD
           HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')


 4 rows selected.


In conclusion, the tests confirmed that GG DDL replication support the interval partition automatic creation and drop partition operations. It is worth noting that when we use DBMS_METADATA package to get the definitions of interval partitioned table and its associated local indexes, we won't see the interval partitions that are created automatically. To verify the existance of the partitions, we shall use dba_tab_partitions and dba_ind_partitions views.

P.S.

Note from Oracle docs:

You cannot explicitly add a partition to an interval-partitioned table unless you first lock the partition, which triggers the creation of the partition. The database automatically creates a partition for an interval when data for that interval is inserted. In general, you only need to explicitly create interval partitions for a partition exchange load scenario.

update Apr 9, 2014 -

I may have misunderstood the relationship of DDL replication and interval partition creation.  DDL replication may have no relationship with interval partition creation at all. Even without DDL replication enabled. Interval partition will be created due to the data insertion DML statement.