Loading....

One of the new features in Goldengate 12c is the Integrated Replicated or Integrated Apply feature.

Note that Integrated Extract was introduced in GoldenGate 11g  to go along with what is now termed as Classic Extract.

Keep in mind that to use Integrated Replicat the target database needs to be version 11.2.0.4 or later and this feature cannot be used if the target database is non-Oracle.

Integrated Replicat is useful in case of heavy workloads where additional parallel apply processes are created and co-ordinated automatically enabling transactions to be applied in parallel without any changes being done to the replicat parameter file.

The Replicat process reads the trail file and then constructs logical change records (LCRs) which are then transmitted to the database using what is known as a Lightweight Streaming API.

A number of database apply processes are created each with their own function:

  • Receiver: Reads LCRs
  • Preparer: Computes the dependencies between the transactions (primary key, unique indexes, foreign key) , grouping transactions and sorting in dependency order.
  • Coordinator: Coordinates transactions, maintains the order between applier processes.
  • Applier:  Performs changes for assigned transactions, including conflict detection and error handling

The integrated replicat has autotuning based on workload.  Based on the number of LCRs being processed, additional parallel apply processes are added or removed .

This is controlled via two parameters in the replicat parameter file – PARALLELISM and MAX_PARALLELISM .

PARALLELISM is the minimum number of parallel apply processes. Default is 4.

MAX_PARALLELISM is the maximum number of apply servers.  Default is 30.

GoldenGate will automatically increase and decrease the number of apply servers based on workload using MAX_PARALLELISM parameter.

Integrated Replicat does not require a checkpoint table to be set up as in the case of the conventional or classic replicat which we used in earlier GoldenGate versions.

The START REPLICAT command automatically registers the integrated replicat with the target Oracle database.

Integrated Replicat applies transactions asynchronously.

Transactions that do not have inter-dependencies can be safely executed and committed out of order to achieve fast throughput. Transactions with dependencies are guaranteed to be applied in the same order as on the source.

The integrated replicat requires the source extract parameter file to contain these new parameters introduced in 12c – LOGALLSUPCOLS and UPDATERECORDFORMAT COMPACT.

From the documentation:

LOGALLSUPCOLS causes Extract to do the following with these supplementally logged columns:

  • Automatically includes in the trail record the before image for UPDATE operations.
  • Automatically includes in the trail record the before image of all supplementally logged columns for both UPDATE and DELETE operations

UPDATERECORDFORMAT

By default, when Extract is configured to generate before images, the before image is stored in a separate record from the after image in the trail.

When two records are generated for an update to a single row, it incurs additional disk I/O and processing for both Extract and Replicat. If supplemental logging is enabled on all columns, the unmodified columns may be repeated in both the the before and after records. The overall size of the trail is larger, as well. This overhead is reduced by using UPDATERECORDFORMAT.

When UPDATERECORDFORMAT is used, Extract writes the before and after images to a single record that contains all of the information needed to process an UPDATE operation.

 

Let us look at an example of configuring and using an Integrated Replicat.

We have a source table MYTAB residing in an Oracle 12c pluggable database called SALES and we will be replicating this table to another 12c target pluggable database called SALES_DR.

Using the new CREDENTIALSTORE feature we have created two user accounts – one called gg_root which connects to the root container in a 12c Container database and another called gg_sales which connects to the PDB called SALES.

 

Add supplemental logging at the table level

GGSCI (orasql-001-dev.mydomain) 1> dblogin useridalias gg_sales

Successfully logged into database SALES.

GGSCI (orasql-001-dev.mydomain) 4> ADD  TRANDATA SALES_DR.SH.MYTAB ALLCOLS

Logging of supplemental redo data enabled for table SALES.SH.MYTAB.

TRANDATA for scheduling columns has been added on table 'SALES.SH.MYTAB'.TRANDATA for all columns has been added on table 'SALES.SH.MYTAB'.

 

Register the integrated extract

GGSCI (orasql-001-dev.mydomain) 6> DBLOGIN USERIDALIAS gg_root

Successfully logged into database CDB$ROOT.

 

GGSCI (orasql-001-dev.mydomain) 7> REGISTER EXTRACT myext1 DATABASE  CONTAINER (sales)

Extract MYEXT1 successfully registered with database at SCN 3669081.

 

Add the Integrated Extract and Data Pump

GGSCI (orasql-001-dev.mydomain) 8> ADD EXTRACT myext1 INTEGRATED TRANLOG, BEGIN NOW

EXTRACT added.

 

GGSCI (orasql-001-dev.mydomain) 9> ADD EXTTRAIL ./dirdat/xx EXTRACT myext1

EXTTRAIL added.

 

GGSCI (orasql-001-dev.mydomain) 10> ADD EXTRACT mydp1 EXTTRAILSOURCE ./dirdat/xx BEGIN NOW

EXTRACT added.

GGSCI (orasql-001-dev.mydomain) 11> ADD RMTTRAIL ./dirdat/rx EXTRACT mydp1

RMTTRAIL added.

 

Edit the Integrated Extract Parameter File

 

GGSCI (orasql-001-dev.mydomain) 11> edit params myext1

 

EXTRACT myext1

SETENV (ORACLE_SID='condb2')

USERIDALIAS gg_root

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

EXTTRAIL ./dirdat/xx

SOURCECATALOG sales

TABLE sh.mytab;

 

Edit the Data Pump Parameter File


GGSCI (orasql-001-dev.mydomain) 12> edit params mydp1

 

EXTRACT mydp1

SETENV (ORACLE_SID='condb2')

USERIDALIAS gg_owner

RMTHOST orasql-001-test, MGRPORT 7809

RMTTRAIL ./dirdat/rx

SOURCECATALOG sales

TABLE sh.mytab;

 

On the target, add the Integrated Replicat

GGSCI (orasql-001-test.mydomain) 1> DBLOGIN USERID C##ggadmin@sales_dr, PASSWORD welcome1

Successfully logged into database SALES_DR.

 

GGSCI (orasql-001-test.mydomain) 2> ADD REPLICAT myrep1 INTEGRATED EXTTRAIL ./dirdat/rx

REPLICAT (Integrated) added.

 

GGSCI (orasql-001-test.mydomain) 5> edit params myrep1

 

REPLICAT myrep1

SETENV (ORACLE_SID='condb2')

DBOPTIONS INTEGRATEDPARAMS(parallelism 6)

USERID C##GGADMIN@sales_dr, PASSWORD welcome1

ASSUMETARGETDEFS

MAP sales.sh.mytab, TARGET sales_dr.sh.mytab;

 

Note:

The parameter DBOPTIONS INTEGRATEDPARAMS(parallelism 6) denotes that this for this integrated replicat,  we are specifying that the minimum number of parallel apply processes will be 6.

 

Start the Integrated Extract, Data Pump and Integrated Replicat via the START EXTRACT and START REPLICAT commands.

 

Check the status of the Replicat process as well as the database server apply processes.

GGSCI (orasql-001-test.mydomain) 6> info replicat myrep1

 

REPLICAT   MYREP1    Last Started 2014-01-15 13:25   Status RUNNING

INTEGRATED

Checkpoint Lag       00:00:00 (updated 00:02:09 ago)

Process ID           20828

Log Read Checkpoint  File ./dirdat/rx000000

First Record  RBA 0

 

SQL> select REPLICAT_NAME,SERVER_NAME from DBA_GOLDENGATE_INBOUND;

REPLICAT_NAME        SERVER_NAME
-------------------- --------------------
MYREP1               OGG$MYREP1


SQL> select  APPLY_NAME,QUEUE_NAME,status from dba_apply;

APPLY_NAME           QUEUE_NAME           STATUS
-------------------- -------------------- --------
OGG$MYREP1           OGGQ$MYREP1          ENABLED


SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ;

APPLY_NAME                     STATE
------------------------------ ---------------------
OGG$MYREP1                     IDLE


Note: Because we had configured PARALLELISM to be 6 via the DBOPTIONS INTEGRATEDPARAMS(parallelism 6) in the replicat parameter file, we will see 6 apply server processes which are ready to run.

At this stage they are IDLE and have not received or applied any messages or LCRs.


SQL> select server_id,TOTAL_MESSAGES_APPLIED from V$GG_APPLY_SERVER
  2  where apply_name= OGG$MYREP1';

 SERVER_ID TOTAL_MESSAGES_APPLIED
---------- ----------------------
         4                      0
         2                      0
         6                      0
         5                      0
         3                      0
         1                      0

6 rows selected.

Populate the base table and monitor the extract process

We now insert a million rows into our target table and see that the extract has processed those newly added rows.

GGSCI (kens-orasql-001-test.mydomain) 1> stats extract myext1

Sending STATS request to EXTRACT MYEXT1 ...

Start of Statistics at 2014-01-20 16:21:26.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         1.00

Output to ./dirdat/ex:

Extracting from SALES.SH.MYTAB to SALES_DR.SH.MYTAB:

*** Total statistics since 2014-01-20 16:19:54 ***
        Total inserts                                1000000.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                             1000000.00

Monitor the Integrated Replicat

GGSCI (orasql-001-dev.mydomain) 2> info replicat myrep1

REPLICAT   MYREP1    Last Started 2014-01-20 16:12   Status RUNNING
INTEGRATED
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Process ID           4794
Log Read Checkpoint  File ./dirdat/rx000000
                     2014-01-20 16:22:51.183273  RBA 54918866


GGSCI (orasql-001-dev.mydomain) 1> stats replicat myrep1

Sending STATS request to REPLICAT MYREP1 ...

Start of Statistics at 2014-01-20 17:47:25.


Integrated Replicat Statistics:

        Total transactions                                 5.00
        Redirected                                         0.00
        DDL operations                                     0.00
        Stored procedures                                  0.00
        Datatype functionality                             0.00
        Event actions                                      0.00
        Direct transactions ratio                          0.00%

Replicating from SALES.SH.MYYTAB to SALES_DR.SH.MYTAB:

*** Total statistics since 2014-01-20 16:20:05 ***
        Total inserts                                1000000.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                             1000000.00

Monitor the status of the Database Apply Server Processes

SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ;

APPLY_NAME                     STATE
------------------------------ ---------------------
OGG$MYREP1                     APPLYING


SQL>  select server_id,TOTAL_MESSAGES_APPLIED from V$GG_APPLY_SERVER
  2  where apply_name='OGG$MYREP1';


 SERVER_ID TOTAL_MESSAGES_APPLIED
---------- ----------------------
         4                      0
         2                 388462
         6                      0
         5                      0
         3                      0
         1                 611543



SQL> select apply_name,state,TOTAL_MESSAGES_DEQUEUED,  TOTAL_MESSAGES_SPILLED
  2  from  V$GG_APPLY_READER;

APPLY_NAME                     STATE
------------------------------ ------------------------------------
TOTAL_MESSAGES_DEQUEUED TOTAL_MESSAGES_SPILLED
----------------------- ----------------------
OGG$MYREP1                     IDLE
                1000005                      0


SQL>  select APPLY_NAME,TOTAL_APPLIED, TOTAL_RECEIVED from V$GG_APPLY_COORDINATOR;

APPLY_NAME                     TOTAL_APPLIED TOTAL_RECEIVED
------------------------------ ------------- --------------
OGG$MYREP1                                 5              5



SQL> select apply_name,state from V$GG_APPLY_COORDINATOR ;

APPLY_NAME                     STATE
------------------------------ ---------------------
OGG$MYREP1                     IDLE
Please follow and like us:

Last Update: May 4, 2017  

January 21, 2014 204 Gavin Soorma
Total 0 Votes:
0

Tell us how can we improve this post?

+ = Verify Human or Spambot ?

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?

Leave a Comment

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

*
*

Social media & sharing icons powered by UltimatelySocial
Back To Top

Add A Knowledge Base Question !

You will receive an email when your question will be answered.

+ = Verify Human or Spambot ?