Triggers and issues
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;”
DBMS_DDL.SET_TRIGGER_FIRING_PROPERTY (
trig_owner IN VARCHAR2, trig_name IN VARCHAR2, property IN INTEGER, setting IN BOOLEAN);
|
Schema of the trigger to set |
|
Name of the trigger to set |
|
|
|
|
|
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)
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:
- Create the Materialized view as updatable (with FOR UPDATE)
- 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..
Discussion ¬