0

I am finding a resolution to record DML and DDL changes made to specified Oracle schemas or tables dynamically, which meaning that schemas and tables monitored can be changed in application run time.

In a word, I am going to achieve an Oracle database probe, not for synchronizing databases.

Updated

For example, I set a monitor to a table test for database db. I want to retrieve all changes made to test, such as drop/add/modify a column or insert/update/delete records and so on, I need to analyze and send all changes to a blockchain such as table test added a column field1,that's why I want to get all executed SQL for the monitored tables.

I have read Oracle docs about data guard and streams.

Data guard doc says:

SQL Apply (logical standby databases only)

Reconstitutes SQL statements from the redo received from the primary database and executes the SQL statements against the logical standby database.

Logical standby databases can be opened in read/write mode, but the target tables being maintained by the logical standby database are opened in read-only mode for reporting purposes (providing the database guard was set appropriately). SQL Apply enables you to use the logical standby database for reporting activities, even while SQL statements are being applied.

Stream doc says:

Oracle Streams provides two ways to capture database changes implicitly: capture processes and synchronous captures. A capture process can capture DML changes made to tables, schemas, or an entire database, and DDL changes. A synchronous capture can capture DML changes made to tables. Rules determine which changes are captured by a capture process or synchronous capture.

And before this, I have already tried to get SQL change by analyzing redo log with oracle LogMinner and finally did it.

The Oracle stream seems to be the most appropriate way of achieving my purpose, but it implements steps are too complicated and manually. And in fact, there is an open-source for MySQL published by Alibaba which named canal, canal pretends itself as a slave so that MySQL will dump binlog and push it to canal service, and then canal reconstitutes the original SQL from binlog.

I think Oracle standby database is like MySQL slave so that the probe can be implemented in a similar way. So I want to use the data guard way, but I don't want to analyze the redo log myself since it needs root privilege to shut down the database and enable some functions, however, in production I only have a read-only user. I want to use logical standby database, but the problem is that I didn't see how to get the Reconstitutes SQL statements described above.

So, are there any pros can make some suggestions?

Anyway thanks a lot.

Community
  • 1
  • 1
ZanXus
  • 1
  • 2
  • The proper way to do this depends on *why* you want to do it. For example: if you're trying to record changes for an audit trail, then triggers might be the best way. If you're trying to synchronize databases, then Golden Gate might be the best way. You might get better advice if you explain exactly what you're trying to accomplish. – Jon Heller Apr 23 '19 at 00:43
  • Thanks for your reply dude, I updated my question.For example, I set a monitor to a table test for database db. I want to retrieve all changes made to test, such as drop/add/modify a column or insert/update/delete records and so on, I need to analyze and send all changes to a blockchain such as table test added a column field1,that's why I want to get all executed SQL for the monitored tables.I am not going to synchronize databases. – ZanXus Apr 23 '19 at 02:33
  • Since this is my first time to use Oracle database and my English is poor so I don't really understand what the audit trail exactly is. Would you mind describing it in another simple way? – ZanXus Apr 23 '19 at 02:47

0 Answers0