Cost Cutting Series — Part 1
Low Cost CDC Approach
In this economy today, cost cutting is one of the major topics that gets discussed in management meetings. While cost cutting is very crucial, it is also essential for organizations to keep an eye on the future as well and not go back 10 years in the name of cost cutting. In that context, in “Part 1” of this Cost Cutting Series, I thought it would be appropriate to discuss a very important topic. CDC(Change Data Capture).
Use Case
The use case that I will be taking up today is syncing up two Oracle databases using CDC without leveraging any third party tools for CDC.
The technology stack includes
- Oracle Database
- Informatica PowerCenter (not essential but just to demonstrate)
Traditional Approaches
There are various third party tools that offer CDC.
- Leveraging Informatica’s PowerExchange CDC for Oracle in conjunction with Informatica’s own Data Integration Hub is one option organizations use.
- Oracle Data Guard is another.
- Golden Gate Data Replication.
- Real time streaming tech like Kafka, MQs et al.
- and I am sure there are many more
A lot of the above tools involve heavy investments in software and hardware infrastructure. So what is my alternative?
Low Cost Alternative
This is where Oracle stands out. Out of the box, Oracle comes with a concept called SCN (Acronym for System Change Number). To put it in a very plain and simple language, SCN is incremented every time a commit transaction happens in the DB. Thomas Kyte from the famous “asktom” explains SCN rather eloquently as follows and I quote.
The system change number (SCN) is Oracle’s clock — every time we commit, the clock increments
The good news is, it always keeps going up no matter what. and whats more, Oracle provides a pseudo column called ORA_ROWSCN which can be used in SQL queries (including WHERE clauses).
Now all you brilliant minds must be starting to design this in your mind. The possibilities are just endless !
Enough talking. Let us get right to business. How can this be achieved you ask?
Steps
Step 1 — Create a Table T. Notice a rather unfamiliar clause ROWDEPENDENCIES is used. More on this later. Also notice there is no TIMESTAMP column in this table — at all !
Step 2 — Store the max SCN from the table in a local variable using ORA_ROWSCN pseudo column
Step 3 — Perform an update on the table. Then get all the records that were updated using ORA_ROWSCN in the WHERE clause.
Step 4 — Perform an insert on the table. Then get all the records that were inserted using ORA_ROWSCN in the WHERE clause. Like so:
Here is the complete SQL file.
Caveats
So naturally, you ask what are the downsides? Well, there are some limitations like
- ORA_ROWSCN cannot be used like in views (more on that can be read from Oracle documentation which is fairly comprehensive).
- If ROWDEPENDENCIES is not enabled on the table, then the SCN is tracked at a BLOCK level not ROW level. That means, whenever data changes in the table, all the rows in the block get the same SCN in the absence of ROWDEPENDENCIES. That means, you would be propagating more (unchanged) rows than you may want.
Design Flow
That is it ! Simple isn’t it ? Now here is how this can be leveraged in a real life design.
Summary
To summarize, I have tried to explain how Oracle’s own SCN can be leveraged to identify inserts/updates/deletes and propagate them real time to another database. If you are excited to know about this, please take the time to drop in a comment. If there is other feedback,feel free to let me know as well. Until next time ! Adios amigos !
