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
