MATERIALIZED VIEWS

Use FOR UPDATE when creating the MV or

If the tables are being replicated exclude the materialized view from replication e.g. MAPEXCLUDE .

impdp fails on materialized vies

RA-31685: Object type failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type failed due to insufficient privileges. Failing sql is:
ORA-31685: Object type MATERIALIZED_VIEW:”ACQ”.”DIM_MERCHANT_MV” failed due to insufficient privileges. Failing sql is:
CREATE MATERIALIZED VIEW …..

Oracle Doc ID 1563217.1

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.

SQLEXEC “begin dbms_ddl.set_trigger_firing_property(‘CROSS_SCHEMA_DDL’,’TRANS_DETAILS_SBI_TRIG’,FALSE); end;”

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 256
set pages 0
set head off
SELECT ‘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%’;

And run the output from above on CFEBIDEMO

Run the following on CFEDWH

set lines 256
set pages 0
set head off
SELECT ‘alter table ‘||owner || ‘.’ ||table_name||’ ‘ || substr(status,1,length(status)-1) || ‘ ‘ || decode(VALIDATED,’VALIDATED’,’ VALIDATE ‘, ‘NOT VALIDATED’, ‘ NOVALIDATE ‘) || 
‘ constraint ‘||constraint_name||’;’ FROM dba_constraints WHERE OWNER = ‘ACQ’ and constraint_name not like ‘SYS_%’ and constraint_name not like ‘BIN%’;

And run the output from above on CFEBIDEMO

RMAN WILL NOT DELETE ARCHIVE LOGS

Refer RMAN-08137: WARNING: Archived Log Not Deleted, Needed For Standby Or Upstream Capture Proces (Doc ID 1993799.1)

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=16snt5y8wp_4&_afrLoop=192052942692042

Caused by an extract not being deleted cleanly.

SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,’999999999999999′)as REQ_SCN ,to_char(OLDEST_SCN,’999999999999999′)as OLDEST_SCN FROM DBA_CAPTURE;

CAPTURE_NAME CAPTURE_TY STATUS REQ_SCN OLDEST_SCN
———————– —————— ———– ————- ——————-
OGG$CAP_CFEAQEXT LOCAL DISABLED 201737048 201737048

SQL>

We can see that CFEAQEXT extract is still holding archive logs in the database.

To clean up :

$ GG

$ ggsci

ggsci> dblogin useridalias ggadmindc

Successfully logged into database.

ggsci> unregister extract CFEAQEXT database

2016-10-31 10:32:56 INFO OGG-01750 Successfully unregistered EXTRACT CFEAQEXT from database.

One should now be able to delete the  archive using rman.

rman target /

RMAN> delete archivelog all completed before ‘sysdate-1’;

GOLDENGATE ERROR ORA-01732

Error in file is:

2016-11-03 16:22:51  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, cfeaqrep.prm:  OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732),

SQL .

2016-11-03 16:22:51  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, cfeaqrep.prm:  Aborted grouped transaction on ‘ACQ.TRANS_DETAILS_RECOGN_MV’, Database error 1732

(OCI Error ORA-01732: data manipulation operation not legal on this view (status = 1732),

Issue is that for Replicat, the materialized view must be updateable.

Options:

  1. Create the Materialized view as updatable (with FOR UPDATE)
  2. If the base tables that compose the Materialized view are being replicated, then exclude the materialized view from replication using the mapexclude parameter: MAPEXCLUDE .

TEMP SPACE ISSUE

Import from live fails for central if TEMP is less than 215G

UNDO SPACE ISSUE

Import from live fails for central if UNDO is less than 380G

GOLDENGATE TABLESPACE FULL

Shutdown all goldengate processes including the mgr.

alter table ggs_admin.ggs_ddl_hist enable row movement;

alter table ggs_admin.ggs_ddl_hist shrink space cascade;

alter table ggs_admin.ggs_ddl_hist disable row movement;

As soon as the database starts logging archive execute

alter tablespace goldengate autoextend on maxsize 64g;

I did not wait for the shrink operations to commit.

ORA-01466: unable to read data – table definition has changed

Tried an export of the ACQ schema from cfedwh for GG to cfebidemo but it kept failing with errors similar to this title.

The issue was that I had taken the SCN for the export, ETL had been processed by development and THEN I tried the expoprt.

As the tables had changed since the scn export was having issues.

Solution:

Take a new current_scn value form the database and immediately run the export.

Error reading LCR from data source. Status 509, data source type 0.

e.g.

2016-12-13 13:45:34 ERROR OGG-02171 Oracle GoldenGate Delivery for Oracle, cfeaqrep.prm: Error reading LCR from data source. Status 509, data source type 0. 
2016-12-13 13:45:34 ERROR OGG-02198 Oracle GoldenGate Delivery for Oracle, cfeaqrep.prm: Incompatible record (logical EOF) in trail file /backup/oracle/OGG/dirdat/cfedwh/AQ000000002, rba 110,658,340. 
2016-12-13 13:45:39 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, cfeaqrep.prm: PROCESS ABENDING.

Logdump >open /backup/oracle/OGG/dirdat/cfedwh/AQ000000002 
Current LogTrail is /backup/oracle/OGG/dirdat/cfedwh/AQ000000002 
Logdump >count 
LogTrail /backup/oracle/OGG/dirdat/cfedwh/AQ000000002 has 147666 records 
Total Data Bytes 238186214 
Avg Bytes/Record 1613 
GGSPKUpdate 147657 
DDL 4 
Metadata Records 4 
Others 1 
After Images 147661 

Average of 10 Transactions 
Bytes/Trans ….. 24527418 
Records/Trans … 14766 
Files/Trans ….. 1


logdump>open /backup/oracle/OGG/dirdat/cfedwh/AQ000000002 
logdump>pos 110658340 
logdump>ghdr on 
logdump>detail data on 
logdump>usertoken detail 
logdump>ggstoken detail 
logdump>n

Bad record found at RBA 110658340, format 5.50 Header token) 
000a ffff | ….


It seems, that the record is corrupted, follow the note.:
Resync / Recover Data ( Tables , Trails ) Trails Are Missing, Damaged, Incompatible, Corrupt or Suspect for Extract or Replicat ( Doc ID 1614665.1 )

TRY AGAIN

select scn_to_timestamp (529512856) from dual;

info cfeaqext, showch

ALTER EXTRACT cfeaqext  SCN 529512856

ALTER EXTRACT cfeaqext  ETROLLOVER

info cfeaqext, showch

alter replicat CFEAQREP

ALTER REPLICAT CFEAQREP EXTSEQNO 133

ALTER REPLICAT CFEAQREP EXTRBA 0

start replicat CFEAQREP after scn value ok…

ALTER REPLICAT CFEAQREP EXTSEQNO 3

ALTER REPLICAT CFEAQREP EXTRBA 0

ADD EXTRACT cfeaqext integrated tranlog, begin now.

register extract cfeaqext database.

ADD EXTTRAIL /backup/oracle/OGG/dirdat/cfedwh/AQ  EXTRACT cfeaqext  MEGABYTES 250.

start extract cfeaqext.

ADD EXTRACT cfeaqext integrated tranlog, begin now.

register extract cfeaqext database.

ADD EXTTRAIL /backup/oracle/OGG/dirdat/cfedwh/AQ  EXTRACT cfeaqext  MEGABYTES 250.

start extract cfeaqext.

info cfeaqext  showch.

ALTER EXTRACT cfeaqext  SCN 529512856.

ALTER EXTRACT cfeaqext  ETROLLOVER.

info cfeaqext  showch.

start extract cfeaqext.

add replicat cfeaqrep integrated exttrail /backup/oracle/OGG/dirdat/cfedwh/AQ.

start replicat cfeaqrep aftercsn 529512856.

start replicat cfeaqrep aftercsn 529512856.

info cfeaqrep  detail.

info cfeaqrep.

start  replicat cfeaqrep aftercsn  529512856.

THEN

You can find the last applied csn by info replicat nnnn, DETAIL

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..