Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

GoldenGate change data capture and replication of BLOB and CLOB data

  • Posted by Gavin Soorma
  • On May 22, 2014
  • 0 Comments
  • BLOB, Change Data Capture, CLOB, data column, document, GoldenGate, image, replication

We will look at an example of GoldenGate replication of a table having a BLOB column and how an INSERT and UPDATE statement on the table with BLOB data is handled by GoldenGate.

We create an APEX 4.2 application to illustrate this example where we create a form and report based on the DOCUMENTS table and upload and download documents. We will observe how changes to the BLOB column data are replicated in real-time to the target database via GoldenGate change data capture.

Thanks to ACE Director Eddie Awad’s article which made me understand how APEX handles file upload and downloads.
Read the article by Eddie.

On the source database we create the DOCUMENTS table and a sequence and trigger to populate the primary key column ID.

CREATE TABLE documents
(
   ID              NUMBER PRIMARY KEY
  ,DOC_CONTENT    BLOB
  ,MIME_TYPE       VARCHAR2 (255)
  ,FILENAME        VARCHAR2 (255)
  ,LAST_UPDATED    DATE
  ,CHARACTER_SET   VARCHAR2 (128)
);

CREATE SEQUENCE documents_seq;

CREATE OR REPLACE TRIGGER documents_trg_bi
   BEFORE INSERT
   ON documents
   FOR EACH ROW
BEGIN
   :new.id := documents_seq.NEXTVAL;
END;
/

Create the Extract and Replicat processes

GGSCI (vindi-a) 3> add extract ext9 tranlog begin now
EXTRACT added.

GGSCI (vindi-a) 4> add rmttrail /u01/app/oracle/product/st_goldengate/dirdat/xx extract ext9
RMTTRAIL added.

GGSCI (vindi-a) 5> edit params ext9
extract ext9
CACHEMGR CACHESIZE 8G
userid gg_owner@testdb password gg_owner
DDL include ALL
ddloptions  addtrandata, report
rmthost poc-strelis-vindi, mgrport 7810
rmttrail  /u01/app/oracle/product/st_goldengate/dirdat/xx
dynamicresolution
SEQUENCE GG_OWNER.*;
TABLE GG_OWNER.DOCUMENTS;

GGSCI (vindi-a) 1> start extract ext9

Sending START request to MANAGER ...
EXTRACT EXT9 starting

GGSCI (vindi-a) 2> info extract ext9

EXTRACT    EXT9      Last Started 2014-05-22 08:43   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:04:54 ago)
Process ID           17984
Log Read Checkpoint  Oracle Redo Logs
                     2014-05-22 08:38:44  Seqno 122, RBA 9039376
                     SCN 0.0 (0)

On Target GoldenGate 

GGSCI (vindi-a) 3> add replicat rep9 exttrail /u01/app/oracle/product/st_goldengate/dirdat/xx
REPLICAT added.

GGSCI (vindi-a) 4> edit params rep9
replicat rep9
assumetargetdefs
ddlerror default ignore
userid gg_owner@strelis password gg_owner
MAP GG_OWNER.DOCUMENTS ,TARGET GG_OWNER.DOCUMENTS;

GGSCI (vindi-a) 5> start replicat rep9

Sending START request to MANAGER ...
REPLICAT REP9 starting

GGSCI (vindi-a) 6> info replicat rep9

REPLICAT   REP9      Last Started 2014-05-22 08:43   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           17919
Log Read Checkpoint  File /u01/app/oracle/product/st_goldengate/dirdat/xx000000
                     First Record  RBA 0

Since we have configured DDL replication as well we see that the DOCUMENTS table has been created on the target database as well.

oracle@vind-a:/export/home/oracle $ sqlplus gg_owner/gg_owner@targetdb

SQL*Plus: Release 11.2.0.3.0 Production on Thu May 22 08:35:55 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc documents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 DOC_CONTENT                                        BLOB
 MIME_TYPE                                          VARCHAR2(255)
 FILENAME                                           VARCHAR2(255)
 LAST_UPDATED                                       DATE
 CHARACTER_SET                                      VARCHAR2(128)

We now launch APEX to create our demo application.

a1

In Application Builder click on Create

 

a2
 
Select Database
 

 

 
Click Add Page
 

 
Click Next
 

 
Accept default value
 

 
Accept default values
 

 

 
Click Create Application
 

 
Click Page 1 link
 

 
From Regions menu click Create
 

 
Select Form and click Next
 

 
Select Form on a Table or View
 

 
Select DOCUMENTS table from LOV and click Next
 

 
Enter the Page name  and click Next
 

 
Select the primary key column of the DOCUMENTS table and click Next
 

 
Primary key of the table is populated via the sequence called by the trigger
 
Select Existing trigger and click Next
 

 
Select columns to display on the form and click Next
 

 
Change the label of the Create button to Upload and hide other buttons – click Next
 

 
Select the current page as the page to branch to and click Next
 

 
Click Create
 

 
Click Edit Page
 

 
Select the P1_DOC_CONTENT item and click Edit from the menu
 

 
In the Settings section of the page add the table column names against the columns as shown above
 
Click on Apply Changes
 

 

 
Click on Run
 

 
Enter workspace or application login credentials
 

 
Click the Browse button and select the file to upload
 

 
Click Upload
 

 
In GoldenGate we check the extract and replication stats and we can see the capture and apply of the change we just made
 

GGSCI (vind-a) 3> stats extract ext9 latest

Sending STATS request to EXTRACT EXT9 ...

Start of Statistics at 2014-05-22 09:01:44.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         0.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /u01/app/oracle/product/st_goldengate/dirdat/xx:

Extracting from GG_OWNER.DOCUMENTS_SEQ to GG_OWNER.DOCUMENTS_SEQ:

*** Latest statistics since 2014-05-22 08:59:16 ***
        Total updates                                      1.00
        Total discards                                     0.00
        Total operations                                   1.00

Extracting from GG_OWNER.DOCUMENTS to GG_OWNER.DOCUMENTS:

*** Latest statistics since 2014-05-22 08:59:16 ***
        Total inserts                                      1.00
        Total updates                                      1.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

 
Connect to the target database and see if the record has been inserted.
 
Note the colums like MIME_TYPE, LAST_UPDATED, FILENAME etc are automatically populated.
 

SQL> col filename format a60
SQL> col mime_type format a30
SQL> set linesize 120
SQL> select id,filename,mime_type from documents;

        ID FILENAME                                                     MIME_TYPE
---------- ------------------------------------------------------------ ------------------------------
         1 Consultant Profile - Gavin Soorma.doc                        application/msword

Note the size of the document

SQL> select id,filename,dbms_lob.getlength(doc_content)  from documents;

        ID FILENAME                                                     DBMS_LOB.GETLENGTH(DOC_CONTENT)
---------- ------------------------------------------------------------ -------------------------------
         1 Consultant Profile - Gavin Soorma.doc                                                 532992

We will now add a new page to the application. Click Create Page

 

Select Form and click Next

 

 

Select Form on a Table with Report and click Next

 

Change the Region Title to Edit Documents and click Next

 

 

Select the table and click Next

 

Give a name for the tab for the new page we are creating  and click Next

 

 

Select the columns to include in the report and click Next

 

 

Accept default and click Next

 

 

Change the Region Title and click Next

 

Select Primary key column and click Next

 

 

Select the columns to include in the form and click Next

 

 

Click Create

 

 

Click Run Page

 

 

Click the Edit icon

 

 

Click the Edit Page link at the bottom of the page

 

 

In the Settings section of the page add the table column names as shown

Click Apply Changes and then Run

We will now download the document from the table, edit the document and upload it back into the database again

 

 

Click on the Download link and save the document

 

Open the document and we will make some changes

 

 

We will delete the “Technical Skills” table from the document, save it and then upload it back again

 

 

 

Click on Browse and upload the document which we just downloaded and edited

 

 Click Apply Changes

 

Oracle GoldenGate has applied this change and we can see that the size of the document has reduced in the target database from 532992 bytes to 528896 bytes as we had deleted some lines from the document.

Connect to the target database and issue the query

Previous:

SQL> select id,filename,dbms_lob.getlength(doc_content)  from documents;

        ID FILENAME                                                     DBMS_LOB.GETLENGTH(DOC_CONTENT)
---------- ------------------------------------------------------------ -------------------------------
         1 Consultant Profile - Gavin Soorma.doc                                                 532992


Current:
SQL> select id,filename,dbms_lob.getlength(doc_content)  from documents;

        ID FILENAME                                                     DBMS_LOB.GETLENGTH(DOC_CONTENT)
---------- ------------------------------------------------------------ -------------------------------
         1 Consultant Profile - Gavin Soorma.doc                                                 528896

We can see that the Replicat process which had earlier applied the INSERT statement when the document was uploaded to the database the first time has now applied some UPDATE statements as well

GGSCI (vind-a) 1> stats replicat rep9 latest

Sending STATS request to REPLICAT REP9 ...

Start of Statistics at 2014-05-22 10:08:47.

Replicating from GG_OWNER.DOCUMENTS to GG_OWNER.DOCUMENTS:

*** Latest statistics since 2014-05-22 08:59:20 ***
        Total inserts                                      1.00
        Total updates                                      3.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   4.00

 

0 Comments

Leave Reply

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