We are exploring options to transfer data from an Oracle database to a cloud, managed ,, non-Oracle database (e.g. Postgres). This would be a one-way Oracle => Postgres transfer. One-time data transfer followed by incremental (daily) sync to capture changes.
The datasets in scope are represented by complex Oracle views that join up to a dozen local tables and use PL/SQL functions on some of the columns.
One option is to migrate all the underlying table/data and rebuild the views in Postgres. This could be a significant development effort.
Another option is to materialize the views on the Oracle side and treat it as a single table and migrate it over. But some MV tables contain millions of rows so it takes 1-2 hours to transfer them to the cloud DB. Change Data Capture is one option. ETL tools like Golden Gate, Informatica, etc. are also options.
Since the underlying tables do not have a clean way to determine changes and the view is complex, how can CDC be implemented efficiently? After the initial full transfer, the idea is to only transfer daily changes.
Any ideas appreciated.