Oracle GoldenGate 21c has many enhancements. In this article, we are going to see about “Auto Capture for Tables” or “Automatic Extract of tables” which is one of the major among them.
Prior to OGG 21c, for example, if we need to capture the changes of a table, below needs to be followed,
1. Add supplemental log for the table
2. Add trandata for the table
3. Add the table entry as below in the Extract process parameter file,
TABLE schema.table_name;
If it is a CDB database, then the entry would be like below,
SOURCECATALOG pdb_name TABLE schema.table_name;
Suppose, if you need to capture all the tables of a schema, then below needs to be followed,
Add the table entry as below in the Extract process parameter file,
TABLE schema.*;
If it is a CDB database, then the entry would be like below,
SOURCECATALOG pdb_name TABLE schema.*;
We use wildcard (*) to achieve it. Now, if we need to remove a table from replication (from Capture),
1. If it is a table level replication, we will remove that parameter itself or comment it out in the Extract process parameter file.
2. If it is a schema level replication, we will be using TABLEEXCLUDE to exclude it from the capture.
You may think, why I am explaining all these scenarios and steps which are known to everyone. Let’s get in to the new feature “Auto Capture for Tables” so that you will get a clear picture.
What is Auto Capture for Tables?
From Oracle GoldenGate 21c, a new feature called Auto Capture mode is introduced. An additional column “LOGICAL_REPLICATION” is added to the dictionary table/views DBA_TABLES or CDB_TABLES. By default the value is ENABLED.
Apart from the LOGICAL_REPLICATION, there is also a parameter which needs to be added to the Extract process parameter file as mentioned in the Oracle Documentation,
TRANLOGOPTIONS [INTEGRATEDPARAMS (ENABLE_PROCEDURAL_REPLICATION Y] [, ...]) (ENABLE_AUTO_CAPTURE N]
With the above parameter it didn’t succeed. But, below one worked,
TRANLOGOPTIONS ENABLE_AUTO_CAPTURE
Not sure if that is a documentation bug. Will open an SR with Oracle for that 🙂
There is also another flaw in the documentation I believe. The ALLKEYS option for the LOGICAL REPLICATION didn’t work. It was throwing an error.
It worked fine after removing the ALLKEYS option.
Coming to the point, in Auto Capture mode, when you add supplemental log and Trandata for the table, the changes for that table will be automatically captured by the Extract process. You don’t need to specify the below parameters in the Extract process anymore.,
SOURCECATALOG TABLE SCHEMA.TABLE_NAME;
Wow! It’s really interesting right? Yes, it is really.
As I mentioned, by default the LOGICAL_REPLICATION is ENABLED, but with the below requisites,
The database version should be 21c or higher.
The table should have sufficient ID or scheduling-key supplemental log data at table or schema level.
The table should have primary key (PK), unique identifier (UI), foreign key (FK) supplemental log data, and ALLKEYS supplemental log data. ALLKEYS is required in addition to PK, UI and FK because it logs all unique keys at the schema-wide supplemental logging level in the absence of a primary key.
When I tested this out, I haven’t enabled supplemental log for the table, Adding the Trandata at the OGG level itself took care of the supplemental log too.
Let us see the working of Auto Capture for Tables with an example.
I have an Extract Process EMICRO and below is the parameter file,
You can see that there are parameters like SOURCECATALOG or TABLE. To enable the auto_capture mode, I have added the below parameter to it,
TRANLOGOPTIONS ENABLE_AUTO_CAPTURE
The Extract process is registered with two PDBs ORCLPDB1 and ORCLPDB2.
Now, I am going to create a table under the schema SOURCE which is in ORCLPDB1 container.
Add supplemental log and Trandata to the table
If you see the above output, everything is enabled after executing the ADD TRANDATA command. It takes care of everything. Even enabling logical replication is taken care by the TRANDATA. It means, for some reason, if we disable the LOGICAL_REPLICATION, we need to re-add the TRANDATA once again and doing so will automatically enable the LOGICAL_REPLICATION.
We haven’t added any parameters for replication like SOURCECATALOG or TABLE in the Extract process. Let us insert a record in to the table.
You can see the INSERT record is captured by the Extract Process EMICRO.
If you need to remove this table from replication, then either of the one should be done,
delete trandata ORCLPDB1.SOURCE.EMPLOYEE
or
alter table SOURCE.EMPLOYEE disable LOGICAL REPLICATION;
Let us now remove the table from replication using the second option.
The LOGICAL REPLICATION is disabled for the table, but still the trandata is enabled.
Let us try to INSERT a row to the table now and check if it is getting captured.
It shows only one INSERT captured which is the previous one. It should show Total inserts as 2. So, the record is not captured.
Let us enable the LOGICAL REPLICATION now and see if the record is getting captured.
Once again, inserting a record to the table,
In the below STATS output, we could see that the record is not been captured.
The reason is, as I mentioned earlier, we need to re-add the TRANDATA. Let’s do that now,
Let us perform a UPDATE operation,
The record is captured by the Extract Process successfully.
We may think on how to check which are all tables are enabled for Auto Capture? We do have a command for that which is below,
list tables PDB.SCHEMA.* auto_capture
or
we can also check if the LOGICAL_REPLICATION is enabled or disabled for that table.
I would say this is one of the best enhancements for a Capture process in OGG 21c. The reason is, whenever we need to remove or add a table to the replication, we need to modify the parameter and restart the process in order to take effect. Yes, you are right, in SCHEMA level replication, we can avoid it when we add a new table to the replication, But still, if you want to remove a table from the replication, then there is no other option I believe. Restarting the process is a downtime to the replication. With this AUTO CAPTURE feature, we can handle it more easily.”
Cheers 🙂
Loading ...
Total Users : 1769537