Allowed values for DDL OBJTYPE - GoldenGate

Does anyone have experience with filtering object types using DDL EXCLUDE OBJTYPE clause. I'm interested in filtering out all constraints but regular DDL EXCLUDE ALL OBJTYPE 'CONSTRAINT' doesn't do the job. Also, documentation only covers TABLE, INDEX and SEQUENCE. Are these the only ones supported here? 

Hi.
All DDL object types not documented unsupported should be supported.
SQL> select distinct(object_type) from dba_objects where object_type='CONSTRAINT';
no rows selected
It's not filtering because CONSTRAINT isn't a valid object type in Oracle. Try something like:
DDL INCLUDE ALL EXCLUDE INSTR ‘ADD CONSTRAINT’
Good luck,
-joe

Related

Changes in OGG 11.2 for DDL syntax

Hello people, after upgrading from 11.1 to 11.2 my EXTRACTS abended during DDL changes. Comenting the last 2 lines in EXTRACT & REPLICATS in the below statement solves the problem, therefore I assume I need a different syntax for these. Can you please help?
DDL INCLUDE MAPPED &
EXCLUDE OBJTYPE 'TRIGGER' &
  EXCLUDE OBJTYPE 'INDEX' &
  EXCLUDE OBJTYPE 'CONSTRAINT' &
  EXCLUDE INSTRWORDS 'ALTER TABLE "DROP"' &
  EXCLUDE INSTR 'DROP TABLE'
Thank you in advance.
Try adding USEANSISQLQUOTES to your parameter file and see if that works.  Also, use DDLOPTIONS REPORT and show us the statements that are causing it to abend.

Filter CTAS operations at replicate level

Hello,I need to filter CREATE TABLE AS SELECT operations at replicate level. I think that I need to use a option in DDL option like this.DDL INCLUDE MAPPED EXCLUDE INSTR  'CREATE PUBLIC SYNONYM' EXCLUDE INSTR 'DROP PUBLIC SYNONYM' Would work if I add to this option: DDL INCLUDE MAPPED  EXCLUDE INSTR 'AS SELECT'I would like to do it in a more concrete way. Because this filter might not replicate other DDL operations like:  Create view as select ...or may be others. Do you know if there is another way to do it? Many thanksArturo
Hi ArturoAs long as you have a consistent string you can exclude instr. Obviously if you just choose 'as select', anything with this phrase will be excluded. Turn on DDLOPTIONS REPORT in the extract and you will see the exact string that replicat will use, then figure out an accurate way to match the string. See the installation guide chapter 13 Configuring DDL Support , and review chapter How DDL is evaluated for processing. Note that there seem juicy stuff like PL/SQL DDL filtering but that does not work for Integrated Extract only for classic extract using DDL trigger tables. CheersKee Gan
Hi Kee Gan,Thanks for the feedback.What about to use this option:DDL INCLUDE MAPPED EXCLUDE OPTYPE CREATE OBJTYPE 'TABLE'  INSTRWORDS  'AS SELECT'  RegardsArturo
Hi Arturo,That will not work as it will drop anything to do with CREATE or TABLE or words with 'AS SELECT'. This is NOT an 'and' operation, it is all of the above. CheersKee Gan
Hi,Past some time, but testing this DDL filter, I found that work for me. DDL INCLUDE MAPPED EXCLUDE OPTYPE CREATE OBJTYPE 'TABLE'  INSTRWORDS  'AS SELECT' Filter, CTAS operations, but other statements like CREATE TABLE are replicated, so is my requeriment. Source: create table emp7 (id number, col1 varchar2(30));Target log.Table created.2017-07-20 17:09:25  INFO    OGG-00489  DDL is of mapped scope, after mapping new operatio [create table "SCOTT"."EMP7" (id number, col1 varchar2(30))  (size 59)].2017-07-20 17:09:25  INFO    OGG-00487  DDL operation included [INCLUDE MAPPED], optype [CEATE], objtype [TABLE], objowner [SCOTT], objname [EMP7].Table created. Source: create table emp8  as select * from emp7; Target:2017-07-20 17:10:07  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [create table "SCOTT"."EMP8"  as select * from emp7  (size 51)].2017-07-20 17:10:07  INFO    OGG-00488  DDL operation excluded [EXCLUDE OPTYPE CREATE OBJTYPE 'TABLE' INSTRWORDS 'AS SELECT '], optype [CREATE], objtype [TABLE], objowner [SCOTT], objname [EMP8] Table excluded.This test is using OGG  12.2.0.1.161018 on both source and target. ThanksArturo
Hi Arturo,Cool, well done. I can't think of any sql statement that has 'as select' other than used with a create table. If so you are safe.CheersKee Gan

truncating and recreating target tables

On our source database we have a weekly job that truncates and loads data into a 5 tables. During this weekend run, the job ran perfectly on the source. However the corresponding tables in the target database doubled, instead of being truncated and reloaded.
any configratuin required to support this ?
Can I delete the duplicate rows on the target directly ? This may be risky since some of the columns in the dupllcate rows are different and I am not sure which one to retain.
Can truncate / drop there table on the Golden Gate target and import from the prod. Will this cause any issue. 
This has run correctly before? If so, what changed in the setup? GoldenGate supports truncation in one of two ways: full DDL, and truncates only.
Since this runs as "special run," you could also shell script this procedure and for sure get the truncates done on the target, and then do the data load by invoking the command line versions of extract and replicat.
If it has been working though, I would investigate why the truncate operation on the target failed.
For reference:
Oracle GoldenGate supports the synchronization of TRUNCATE statements as part of thefull DDL synchronization feature or as standalone functionality that is independent of
full DDL synchronization. The standalone TRUNCATE feature supports the replication of
TRUNCATE TABLE, but no other TRUNCATE options. The full DDL feature supports TRUNCATE
TABLE, ALTER TABLE TRUNCATE PARTITION, and other DDL. To avoid errors from duplicate
operations, only one of these features can be active at the same time. The GETTRUNCATES
parameter controls the standalone TRUNCATE feature. 
This is the first time we are running this job since we implemented GG sometime last week. I do not see any other reason of this because, it has impacted only those tables that are truncated and data being loaded. I randomly checked few other tables and the counts seems to be matching. 
In my repwell.prm, I have :
-- Enable replication of truncate table (not truncate partition!)
--
gettruncates
-- DDL support
--
ddl
Is there is a different option that I should be using ?
The job is setup through the application. Sice the app server is pointing to the source DB, I am expecting the job truncates and loades data in the tables in question and these activities are propogated to the target DB.
For now if I want to drop and recreate the tables on the target, will it impact the GG operation ? 
The randomly checked other tables that seem to be matching - these were also populated where truncate was used? Or "seems" to be matching means you are comparing millions of records between the two, and the counts seem to be correct, but you don't really know for sure?
These tables are all in the same schema? If not, maybe it's a lack of permissions. Is your replicat user running with DBA role granted to it, or was "delete on owner.table" used for each table?
What parameters are you using for Replicat?
Where in the parameter file is truncates being used?
GETTRUNCATES and IGNORETRUNCATES are table-specific. One parameter remains in effect forall subsequent TABLE or MAP statements, until the other parameter is encountered.>
The default is ignore. 
No , the randomly checked tables are NOT part of the truncate tables. The tables are in the same schema.
I have a db user schema called ggs and it has DBA role. I guess this the user you are refering to us replicate user.
What is happening is the job is being from the application console pointing to the source database. This job truncates a few tables, then inserts and subsequently updates these tables. However not all actions are being propagated to the target. It seems like the target is getting the data (for these tables) only during insert/updates (or both) resulting in duplicates. My assumption is that the truncate is not happening. It could also be that the truncating is happening and the issue could be during insert/update. I am not sure how to ascertain this.
The tables parameter file is as follows: The tables in question are highlighted
assumetargetdefs
reperror 1, discard -- unique constraint violation, duplicate record
reperror 1403, ignore -- record not found
-- Enable replication of truncate table (not truncate partition!)
--
gettruncates
-- DDL support
--
ddl
-- Use MAP to establish a relationship between one or more source and target tables
--
-- mapexclude welligent.iep_student_documents;
mapexclude welligent.report_requests;
mapexclude mv_delivery_of_services4;
map welligent.audit_log, target welligent.audit_log, keycols (shisid, tablename, columnname, moddate);
map welligent.billing_claims_deleted, target welligent.billing_claims_deleted, keycols (radid);
map welligent.cg_ref_codes, target welligent.cg_ref_codes, keycols (rv_domain, rv_low_value);
map welligent.contract_invoice_exceptions, target welligent.contract_invoice_exceptions, keycols (invoice_id,invoice_item_id);
map welligent.daily_log, target welligent.daily_log, reperror (1, ignore);
map welligent.daily_log_deleted, target welligent.daily_log_deleted, keycols (date_delete);
map welligent.esignature, target welligent.esignature, keycols (signed_date);
map welligent.iep_access_log, target welligent.iep_access_log, keycols (shisid, pid, iep_id);
map welligent.iep_tests_arrangements, target welligent.iep_tests_arrangements, keycols (iep_tests_id);
map welligent.lausd_no_print_ieps_data, target welligent.lausd_no_print_ieps_data, keycols (shisid);
map welligent.out_iep_participants, target welligent.out_iep_participants, keycols (welligent_id);
**map welligent.out_inactive_ieps,                target welligent.out_inactive_ieps,             keycols (welligent_id);**
map welligent.out_student_dis,                  target welligent.out_student_dis,               keycols (welligent_id);+*
map welligent.out_student_eligibilities,        target welligent.out_student_eligibilities,     keycols (welligent_id);+*
map welligent.out_student_rsp_goals,            target welligent.out_student_rsp_goals,         keycols (welligent_id);+*
map welligent.out_students,                     target welligent.out_students,                  keycols (welligent_id);*map+ welligent.provider_recent_items, target welligent.provider_recent_items, reperror (1, ignore);
map welligent.report_log, target welligent.report_log, keycols (repdate);
map welligent.student_period_enrollment, target welligent.student_period_enrollment, keycols (period_id, doe_school_number, shisid), reperror (1,ignore);
map welligent.user_log, target welligent.user_log, reperror (1,ignore);
map welligent.*, target welligent.*; 
It looks like you are using both GETTRUNCATES and DDL.
The rule is to use one or the other. GETTRUNCATES will only do truncates and no other DDL. The DDL (ALL) will do all DDL, including truncates.
As it appears you have duplicated data (the inserts ran twice), that seems to fit what you have in your parameter file: both DDL options, which is not correct.
TruncatesTRUNCATE statements can be replicated as follows:
● As part of the Oracle GoldenGate full DDL support, which supports TRUNCATE TABLE,
ALTER TABLE TRUNCATE PARTITION, and other DDL as documented in the Oracle GoldenGate
Windows and UNIX Administrator’s Guide.
● As standalone TRUNCATE support. This support enables you to replicate TRUNCATE TABLE,
but no other DDL. The GETTRUNCATES parameter controls the standalone TRUNCATE
feature. For more information, see the Oracle GoldenGate Windows and UNIX
Reference Guide.
To avoid errors from duplicate operations, only one of these features can be active at the
same time. 
Then can I comment out gettrunactes and use DDL(ALL). Is the syntax for DDL(ALL) is correct. 
The ALL in parentheses was just to specify which option. You can include DDL via scope (mapped, unmapped, and other). "ALL" is the catchall, so to speak, for all three. Some examples:
DDL INCLUDE MAPPED
DDL INCLUDE ALL
From the Reference guide (with example of include, exclude, and by object type and name):
DDL &INCLUDE UNMAPPED &
OPTYPE alter &
OBJTYPE 'table' &
OBJNAME 'users.tab*' &
INCLUDE MAPPED OBJNAME "*" &
EXCLUDE MAPPED OBJNAME "temporary.tab*" 
I will go with DDL inclide mapped 
How did that turn out? 
doing the job run tonight. Takes about hours to run the job. 
There was some the process invloving this and I could test this. Probably I will get a change in future to test this. Thank you for your help.

Desc: for ddl include mapped objname oggsch.* in Extract

hi, I just want to know that command description. as i didn't find that paramter using in Extract in any documents. I have also enable the ddl & dml.
ddl include mapped objname oggsch.*
table crispadm.TEST1;
table crispadm.TEST2;
table crispadm.TEST3;
table crispadm.TEST4;
table crispadm.TEST5;
Regards,
AMSII 
This parameter Valid for Extract and Replicat,
Syntax DDL [
{INCLUDE | EXCLUDE}
[, MAPPED | UNMAPPED | OTHER | ALL]
[, OPTYPE <type>]
[, OBJTYPE ‘<type>’]
[, OBJNAME <name>]
[, INSTR ‘<string>’]
[, INSTRCOMMENTS ‘<comment_string>’]
[, STAYMETADATA]
[, EVENTACTIONS (<action specification>)
]
[...]
Do not use DDL for:
● an Extract data pump
● a VAM-sort Extract (Teradata source databases)
These process types do not permit mapping or conversion of DDL and will propogate DDL records automatically in PASSTHRU mode (see page 305). DDL that is performed on a source table of a certain name (for example ALTER TABLE TableA...) will be applied by Replicat with the same table name (ALTER TABLE TableA). It cannot be mapped as ALTER TABLE TableB.
MAPPED | UNMAPPED |
OTHER | ALL
INCLUDE | EXCLUDE Use INCLUDE and EXCLUDE to identify the beginning of an inclusion or
exclusion clause.
◆ An inclusion clause contains filtering criteria that identifies the DDL that this parameter will affect.
◆ An exclusion clause contains filtering criteria that excludes specific DDL from this parameter.
The inclusion or exclusion clause must consist of the INCLUDE or EXCLUDE keyword followed by any valid combination of other options of
the parameter that is being applied.
If you use EXCLUDE, you must create a corresponding INCLUDE clause.
For example, the following is invalid:
DDL EXCLUDE OBJNAME “hr.*”
However, you can use either of the following:
DDL INCLUDE ALL, EXCLUDE OBJNAME “hr.*”
DDL INCLUDE OBJNAME “fin.*” EXCLUDE “fin.ss”
An EXCLUDE takes priority over any INCLUDEs that contain the same criteria. You can use multiple inclusion and exclusion clauses.
MAPPED | UNMAPPED |
OTHER | ALL
Use MAPPED, UNMAPPED, OTHER, and ALL to apply INCLUDE or EXCLUDE based on the DDL operation scope.
◆ MAPPED applies INCLUDE or EXCLUDE to DDL operations that are of MAPPED scope. MAPPED filtering is performed before filtering that is specified with other DDL parameter options.
◆ UNMAPPED applies INCLUDE or EXCLUDE to DDL operations that are of UNMAPPED scope.
◆ OTHER applies INCLUDE or EXCLUDE to DDL operations that are of OTHER scope.
◆ ALL applies INCLUDE or EXCLUDE to DDL operations of all scopes.
OBJNAME <name> :
Use OBJNAME to apply INCLUDE or EXCLUDE to the fully qualified name of an object, for example owner.table_name. You can use a wildcard only for
the object name.
Example:
DDL INCLUDE OBJNAME accounts.*
Do not use OBJNAME for the Oracle USER object, because OBJNAME expects owner.object, whereas USER only has a schema.
When using OBJNAME with MAPPED in a Replicat parameter file, the value for OBJNAME must refer to the name specified with the TARGET
clause of the MAP statement. For example, given the following MAP statement, the correct value is OBJNAME fin2.*.
MAP fin.exp_*, TARGET fin2.*;
For detailed information about how to use Oracle GoldenGate DDL support, see the Oracle
GoldenGate Windows and UNIX Administrator’s Guide.
HTH
Annamalai 
An easy way to determine which DDL operations are being captured and which aren't is to use the DDLOPTIONS REPORT parameter and that will show you the exact DDL in the report file and whether or not it was written to the trail file, and why it was or was not written to the trail file based on your DDL INCLUDE / EXCLUDE parameter settings.

DDL filter

Oracle 11gR2 linux
OGG 11.1.1.1.5
Is there an easy way to filter out only some DDL statements rather than create exclusions for everything you don't want?
For example, I want to include the 'ALTER TABLE.....ADD COLUMN' statement but I do not want any other ALTER statements to be replicated. Would I have to create exclusions for all other possible 'ALTER' statements?
Thanks. 
Hi,
This is the general options available with DDL exclude.
DDL EXCLUDE OPTYPE <type>] [, OBJTYPE ‘<type>’] [, OBJNAME “<name>”] [, INSTR ‘<string>’]
For your requirement,
DDL INCLUDE ALL OPTYPE ALTER OBJTYPE TABLE INSTR ‘ADD COLUMN’
DDL EXCLUDE MAPPED OPTYPE ALTER
You can test it, to see how it behaves.
An EXCLUDE takes priority over any INCLUDEs that contain the same criteria.
Best Regards,
Renny

Categories

Resources