select status, object_id, object_type, owner||’.’||object_name “OWNER.OBJECT” from dba_objects where object_name like ‘%DIM_MERCHANT_MV%’ order by 4,2;

Ignore the error if the Materialized View already exists in the target database.

Also check if the schema on the source database still has the create materialized view privilege.

TRIGGERS

To enable the firing of triggers in the destination databasae by tables that are being replicated by goldendate , no matter which schema the trigger is defined in, we need to add instructions to the GG replicat file.

e.g.

Trigger cross_schema_ddl.trans_details_sbi_trig which works on fdn_central.trans_details_settle_batch_id whenever rdr_central.trans_details column settle_batch_id is changed/created.

Changing to TRUE will DISABLE trigger firing by GoldenGate but not by other database access.

DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY (

trig_owner IN VARCHAR2, trig_name IN VARCHAR2, property IN INTEGER, setting IN BOOLEAN);

trig_owner

Schema of the trigger to set

trig_name

Name of the trigger to set

fire_once

If TRUE, the trigger is set to fire once. By default, the fire_once parameter is set to TRUE for DML and DDL triggers.

If FALSE, the trigger is set to always fire unless apply_server_only property is set to TRUE, which overrides fire_once property setting.

property

DBMS_DDL.fire_once to set the fire_once property of the trigger

DBMS_DDL.apply_server_only to indicate whether trigger fires only in the context of SQL apply processes maintaining a logical standby database or Streams apply processes

setting

Value of property being set

DML triggers created on a table have their fire-once property set to TRUE. In this case, the triggers only fire when the table is modified by an user process, and they are automatically disabled inside Oracle processes maintaining either a logical standby database (SQL Apply) or Oracle processes doing replication (Streams Apply) processes, and thus do not fire when a SQL Apply or a Streams Apply process modifies the table. There are two ways for a user to fire a trigger as a result of SQL Apply or a Streams Apply process making a change to a maintained table: (a) setting the fire-once property of a trigger to FALSE, which allows it fire both in the context of a user process or a SQL or Streams Apply process, or (b) by setting the apply-server-only property to TRUE and thus making the trigger fire only in the context of a SQL Apply or a Streams Apply process and not in the context of a user process.

FIRE_ONCE=TRUE, APPLY_SERVER_ONLY=FALSE

This is the default property setting for a DML trigger. The trigger only fires when user process modifies the base table.

FIRE_ONCE=TRUE or FALSE, APPLY_SERVER_ONLY=TRUE

The trigger only fires when SQL Apply or Streams Apply process modifies the base table. The trigger does not fire when a user process modifies the base table.Thus the apply-server-only property overrides the fire-once property of a trigger.

CONSTRAINTS ISSUES

To ensure CFEDWH and CFEBIDEMO constraints are the same:

To disable the constraints

Run the following on CFEDWH

set lines 256set pages 0set head offSELECT ‘alter table ‘||owner || ‘.’ ||table_name||’ DISABLE NOVALIDATE constraint ‘||constraint_name||’;’ FROM dba_constraints WHERE OWNER = ‘ACQ’ and constraint_name not like ‘SYS_%’ and constraint_name not like ‘BIN%’;

example ======= Integrated Replicat low watermark: 483311260 (All source transactions prior to this scn have been applied) Please note down the above low watermark scn. 483311260 connect the source DB Convert the SCN to timestmap select scn_to_timestamp (483311260) from dual; Alter the extract to capture from this timestamp or csn value. you need to use alter etrollover to capture into new trail file..