Gavinsoorma.com.au
[pic]
Oracle GoldenGate 18c and GoldenGate Cloud Services Workshop
Hands-on Lab Exercise 8
Filtering and Mapping Data
Use the WHERE clause to limit the number of rows that is being replicated by GoldenGate
On the source database PDB1 truncate the MYOBJECTS table.
[oracle@rac01 goldengate]$ sqlplus demo/oracle@pdb1
SQL> truncate table myobjects;
Table truncated
On the target database PDB2 drop the MYOBJECTS table and recreate it with a new different structure.
SQL> conn demo/oracle@pdb2
Connected.
SQL> drop table myobjects;
Table dropped.
SQL> create table myobjects
(object_id number not null, table_name varchar2(128) not null,
owner varchar2(128) not null, tablespace_name varchar2(64) not null);
Table created
Add a Primary Key constraint to the MYOBJECTS table.
SQL> alter table myobjects add constraint pk_myobjects primary key (object_id);
Table altered.
SQL> desc myobjects
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(128)
OWNER NOT NULL VARCHAR2(128)
TABLESPACE_NAME NOT NULL VARCHAR2(64)
Stop and edit the replicat process. Note the SQLEXEC and COLMAP clause usage.
GGSCI (rac01.localdomain) 2> stop rep3
Sending STOP request to REPLICAT REP3 ...
Request processed.
GGSCI (rac01.localdomain) 3> edit params rep3
REPLICAT rep3
USERIDALIAS oggadmin_pdb2
DBOPTIONS INTEGRATEDPARAMS (max_sga_size 1024, parallelism 2)
MAP pdb1.demo.myobjects TARGET pdb2.demo.myobjects &
WHERE (object_type='TABLE' AND owner='DEMO'),&
SQLEXEC (id lookup,&
QUERY "select tablespace_name from all_tables where table_name in (select object_name from all_objects where object_type ='TABLE' and owner='DEMO')"), &
COLMAP (USEDEFAULTS, table_name=object_name,tablespace_name=@GETVAL(lookup.tablespace_name));
GGSCI (rac01.localdomain) 4> start rep3
Sending START request to MANAGER ...
REPLICAT REP3 starting
GGSCI (rac01.localdomain) 5> info rep3
REPLICAT REP3 Last Started 2019-01-20 18:01 Status RUNNING
INTEGRATED
Checkpoint Lag 00:00:00 (updated 00:03:36 ago)
Process ID 10053
Log Read Checkpoint File /acfs_oh/app/goldengate/dirdat/ogg2/rt000000027
2019-01-20 11:04:47.191071 RBA 1538
On the source database PDB1 insert rows into the MYOBJECTS table. Note the number of rows which are inserted.
SQL> conn demo/oracle@pdb1
Connected.
SQL> insert into myobjects
select * from all_objects;
68210 rows created.
SQL> commit;
Commit complete.
Note the number of rows which are processed by the replicat as compared to the source extract.
GGSCI (rac01.localdomain) 11> stats rep3 latest
Sending STATS request to REPLICAT REP3 ...
Start of Statistics at 2019-01-20 18:04:41.
Integrated Replicat Statistics:
Total transactions 1.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 1.00
Datatype functionality 0.00
Event actions 0.00
Direct transactions ratio 100.00%
Replicating from PDB1.DEMO.MYOBJECTS to PDB2.DEMO.MYOBJECTS:
*** Latest statistics since 2019-01-20 18:04:06 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (rac01.localdomain) 12>
Note the number of rows in the target database MYOBJECTS table and also note that TABLESPACE_NAME column has been populated on the target table.
Target database (PDB2)
SQL> col table_name format a20
SQL> col tablespace_name format a20
SQL> col owner format a20
SQL> select * from myobjects;
OBJECT_ID TABLE_NAME OWNER TABLESPACE_NAME
---------- -------------------- -------------------- --------------------
74388 MYOBJECTS DEMO USERS
-----------------------
Oracle GoldenGate 18c and Oracle Goldengate Cloud Workshop Lab Exercise 8
2
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- det nsw au staff portal
- au access parent portal
- immigrer au canada
- immigrer au canada gratuitement
- immigrer au quebec
- comment immigrer au canada
- immigrer au canada site officiel
- comment immigrer au canada gratuitement
- immigrer au canada quebec
- immigrer au canada pour travailler
- comment immigrer au quebec
- au holiday 2020