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.

Google Online Preview   Download