Best practices for SCA deployments in a Oracle GoldenGate replication topology
PeterDaniels
Posts: 89 Bronze 3
I recently ran into some issues with production deployment of a SCA patch script. The deployment script was "rejected" by our ops DBAs because it contained a mixture of DDL and DML that affected tables that are currently replicated via Oracle GoldenGate recplication. The issue is that the DML would be executed on the replication source/publisher and target/subscriber DBs, but when GG replication fired back up, it would try to apply the same inserts on the replication target/subscriber DB.
So, the ops DBAs currently have a policy of disallowing DML and DDL in the same deployment script, which is being slightly amended by allowing DDL to be mixed with DML only on tables that are not replicated. So, we are removing some of our master/static data tables from replication and removing some DML from our deployment scripts. That's not an ideal solution. Also note that for some of our master data tables, they are managed by an admin UI AND via deployment scripts, so we really need replication up for those tables to capture changes made thru the admin UI.
My sense is that some combination of softening ops DBA policy, GG configuration options, and environmentally sensitive guard clauses in my SQL will provide a pathway to a more optimal solution.
I wanted to pose this discussion to see how others may have handled similar situations.
Thanks in advance for your participation.
-Peter
So, the ops DBAs currently have a policy of disallowing DML and DDL in the same deployment script, which is being slightly amended by allowing DDL to be mixed with DML only on tables that are not replicated. So, we are removing some of our master/static data tables from replication and removing some DML from our deployment scripts. That's not an ideal solution. Also note that for some of our master data tables, they are managed by an admin UI AND via deployment scripts, so we really need replication up for those tables to capture changes made thru the admin UI.
My sense is that some combination of softening ops DBA policy, GG configuration options, and environmentally sensitive guard clauses in my SQL will provide a pathway to a more optimal solution.
I wanted to pose this discussion to see how others may have handled similar situations.
Thanks in advance for your participation.
-Peter
