Configuring Oracle Database for Push-based Incremental Caching
Data sources and derived tables must be prepared for use in automatically updated incrementally maintained caches and view subscriptions. The caching databases used must set permissions so that tables and indexes can be dynamically created for subscription-related tables.
“Change” messaging from the change-data capture (CDC) service to the specified data source topic is required; without it, no view updates are possible. Refer to
Installing and Configuring Oracle GoldenGate for information on making the CDC service compatible for use with the TDV Software Change Management Service.
Push-based incremental caching requires an Oracle account that grants the following permissions to connect with and use the targeted Oracle database as a subscription store:
• Create subscription-related tables and indexes.
• Introspect required tables. Both READ and SELECT access are required.
• Execute regular SQL queries against data sources and tables of interest.
• Grant EXECUTE,INSERT, UPDATE, and DELETE permissions against those tables.
• Execute flashback queries against data sources and tables of interest.
Oracle databases used to store incrementally cached views and views involving joins of monitored tables must be configured with cursor_sharing set to EXACT. Other settings for cursor_sharing result in degradation of performance.
To configure Oracle data sources
1. Log on to the Oracle database machine as SYSDBA.
2. Turn on supplemental logging at the database level using commands like the following:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER SYSTEM SWITCH LOGFILE;
Or, log in to the database server as the schema owner for the tables that are to be monitored and change the supplemental logging for individual tables.
SQL> ALTER TABLE <tableName> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
3. Make sure that the Oracle databases have archivelog and flashback enabled. To enable flashback, get the system change number (SCN) value using:
SELECT dbms_flashback.get_system_change_number FROM dual;