Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, B.

Asked: June 02, 2016 - 2:21 pm UTC

Last updated: September 04, 2024 - 1:48 pm UTC

Version: 11.2.0.4 and above

Viewed 10K+ times! This question is

You Asked

Chris & Connor,

We are working on a new project and were wondering as to what options are currently available / provided by Oracle for Change Data Capture, besides the DIY MViews ( MVIEW logs mostly ), triggers etc, does Oracle 11.2.0.4 and above provide an out of the box solution ?

I have heard that Oracle CDC is being deprecated, is there some option available that will track all the data changes made to a table ?

Thanks

BC
MT, MI


and Chris said...

Yes, CDC is desupported as of 12c:

http://docs.oracle.com/database/121/UPGRD/deprecated.htm#UPGRD60014

If you're just looking for something to help you see all the changes to your data, check out Flashback Data Archive. This extends flashback query to enable you look back at a table over time.

To use it simply create an archive, then alter your tables to use it!

create flashback archive <archive> tablespace <tablespace> retention 1 year;

alter table <table> flashback archive <archive>;


To view state of the table at a point in time in the past, use standard flashback (as of) queries. Or you can see all the modifications between two times using "versions between" queries:

select * from tab as of timestamp sysdate - 7;

select * from tab versions between timestamp sysdate - 7 and sysdate;


This was part of a separately licensed option. But as of 11.2.0.4 it's now part of the database license!

For further reading, see:

https://oracle-base.com/articles/12c/flashback-data-archive-fda-enhancements-12cr1

If you're looking for something to help you transfer the changes to other databases, GoldenGate is our preferred solution.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

BC, June 02, 2016 - 3:57 pm UTC

Thank you so much for the detailed explanation about the options and license.

I had thought about Flashback Data Archive and created a ticket with Oracle Support to determine if it is an option in our current license.

You guys are the best, thank you

BC
MT, MI

Is Flashback Data Archive completely transparent?

Matthew McPeak, June 02, 2016 - 9:25 pm UTC

Is Flashback Data Archive as transparent as CDC was?

For example, we used CDC to monitor tables that are owned by a 3rd party application (and have not broken that application by doing so, so far).

But, if we move those tables into a FDA, isn't that more invasive / less transparent to the application? E.g., might we not break their patching process with ORA-55610 errors due to limited DDL support for tables in FDAs?

Personally, I find Oracle's desupport of CDC outrageous. It's like them saying "we have a better product for indexing so we're going to deprecate the CREATE INDEX command and charge you for the new, improved way".


Chris Saxon
June 03, 2016 - 4:01 am UTC

Is Flashback Data Archive as transparent as CDC was?

Possibly not as much as so, although that's dependent on the version of the database. Conversely, I see you've omitted the large number of restrictions placed upon you if you opt for the CDC route :-)


Options for Autonomous Database

John, July 03, 2022 - 4:52 pm UTC

While Flashback Data Archive is a perfect option for this kind of functionality, it is sadly not available for Autonomous Database. Are there any alternative options for this type of database?
Connor McDonald
July 05, 2022 - 12:25 am UTC

FDA is *coming* for autonomous, but is not there yet (as I write this).

In the interim, perhaps a trigger based solution but one that still allows automation of the process

https://github.com/connormcd/audit_utility

CDC to GoldenGate

oj, August 27, 2024 - 11:48 am UTC

We have a CDC process that works well on 11g but looking to migrate to 19c or possibly 23ai. What would be the equivalent to CDC in the new world ?

I get GoldenGate capture changes - but will need something a bit more sophisticated to read those changes and process them in the way we want.

With existing CDC process, we simply EXTEND our subscription window, then get all the changes from the subscription views, and process those changes in the PL/SQL stored procedure.

How would we replace that? Is it a combination Goldengate to capture the changes and then something else is needed to read the changes and process them, e.g. Informatica / AbInitio / Oracle Data Integrator ?
Chris Saxon
September 02, 2024 - 2:14 pm UTC

You can build transformation processes with GoldenGate. You can also call stored procedures, so you may be able to reuse your existing code.

https://docs.oracle.com/en/middleware/goldengate/core/23/coredoc/administer-use-sqlexec-execute-commands-stored-procedures-and-queries.html

LogMiner

IFTIKHAR, September 03, 2024 - 5:50 am UTC

Can we use LogMiner?
Chris Saxon
September 03, 2024 - 12:35 pm UTC

To do what?

LogMiner

IFTIKHAR, September 04, 2024 - 6:13 am UTC

i have read that LogMiner can be for Change Data Capture (CDC) as an alternate to GoldenGate.
Chris Saxon
September 04, 2024 - 1:48 pm UTC

You can use it to query redo logs to see what DML & DDL changes are in them, which could form the basis of a CDC process:

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html

Note from 19c you have to specify which log files to mine; the ability for it to automatically find changes without you specifying the log file was desupported in this version.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library