Overview
In this practice, you use the Oracle GoldenGate Event Marker system to trace the records that match specific conditions, which are evaluated when the Replicat process scans the exttrail file, before updating the target database with the inserted or changed data.
Assumptions
The GDP_GROWTH_BY_YEAR table exists in both SRC_USER and TRG_USER schemas.
Tasks
1. Select the OGG_SRC window. In the GGSCI session that is connected to the OGG_SRC Oracle GoldenGate instance, create the parameter file for the GUEVT Extract group:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > edit params guevt
Extract guevt
Exttrail ./dirdat/gu UserIdAlias ogg_admin LOGALLSUPCOLS
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 128) UPDATERECORDFORMAT COMPACT
SOURCECATALOG ogg1 Table src_user.*;
2. Exit from the editor after saving the parameter file. In GGSCI, add the newly created Extract:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > REGISTER EXTRACT guevt DATABASE CONTAINER (ogg1)
Extract GUEVT successfully registered with database at SCN 2816097.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add extract guevt, Integrated Tranlog, begin now
EXTRACT added.
3. Connect the Extract to the ExtTrail, setting the exttrail file size to 10 MB:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add exttrail ./dirdat/gu, Extract guevt, megabytes 10
EXTTRAIL added.
4. Add the pgevt Data Pump Extract, which physically transfers the captured data to the target host:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > edit params pgevt
Oracle University and Error : You are not a Valid Partner use only
Extract pgevt
SETENV (ORACLE_SID='CDB12c') UserIdAlias ogg_admin
RMTHOST ogg_target, MGRPORT 7909 rmttrail ./dirdat/gg
passthru
SOURCECATALOG ogg1 table src_user.*;
5. Connect the Data Pump to the local exttrail file:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add extract pgevt, exttrailsource ./dirdat/gu
EXTRACT added.
6. Connect the remote trail file to the Data Pump, setting the remote trail file size to 10 MB:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > add rmttrail ./dirdat/gg, Extract pgevt, megabytes 10
RMTTRAIL added.
7. Start the Extract and Data Pump processes, and check their status:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > start extract guevt
Sending START request to MANAGER ...
EXTRACT GUEVT starting
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > start extract pgevt
Sending START request to MANAGER ...
EXTRACT PGEVT starting
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info all Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING GUEVT 00:00:00 00:41:39 EXTRACT RUNNING PGEVT 00:00:00 00:00:06
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) >
8. Select the OGG_TRG window, where GGSCI is connected to the OGG_TRG Oracle GoldenGate instance, and enter the edit params revtc command to define the parameters for the new Replicat group:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > edit param revtc
Oracle University and Error : You are not a Valid Partner use only
9. Define the parameters for the revtc Replicat group. The focus is the syntax for the FILTER option of the MAP command.
Replicat revtc
map ogg1.src_user.gdp_by_year, target ogg2.trg_user.gdp_by_year;
map ogg1.src_user.gdp_growth_by_year, target ogg2.trg_user.gdp_growth_by_year,
WHERE (ENTITY_ID < 80 AND GDP_VALUE > 4.00), EVENTACTIONS (TRACE /home/oracle/eu_trace.trc );
map ogg1.src_user.economic_entity, target ogg2.trg_user.economic_entity;
10. Add the new Replicat, linking its input to the local exttrail file. Then start the new REVTC Replicat, and make sure it is running:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > add replicat revtc, Integrated exttrail ./dirdat/gg
REPLICAT (Integrated) added.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > start rep revtc Sending START request to MANAGER ...
REPLICAT REVTC starting
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > INFO ALL
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REVTC 00:00:00 00:00:07 11. Select the SRC_USER window where sqlplus is running. Run the
gdp_growth_by_year_2010.sql file to generate activity in the GDP_GROWTH_BY_YEAR table and to trigger the tracing of those countries that experienced a growth of more than 4% during the year 2010:
SQL> @gdp_growth_by_year_2010.sql 1 row created.
...many lines omitted for clarity...
12. Open an additional shell window. At the OS prompt, make sure that you are in the default directory (/home/oracle). Verify that the trace file has been created:
[OS prompt]$ pwd /home/oracle
[OS prompt]$ ls -lh *.trc
-rw-r---. 1 oracle oinstall 65K Mar 15 21:30 eu_trace.trc
Oracle University and Error : You are not a Valid Partner use only
13. Use an editor of your choice to edit the eu_trace.trc file to see what information Oracle GoldenGate has dumped into the trace file.
14. Select the OGG_TRG window, where GGSCI runs connected to the Oracle GoldenGate OGG_TRG instance. Stop and delete the REVTC Replicat to clean the environment for the next practice:
GGSCI GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > stop replicat revtc
Sending STOP request to REPLICAT REVTC … Request processed.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) > delete replicat revtc
2015-03-15 21:31:58 INFO OGG-02529 Successfully unregistered REPLICAT REVTC inbound server OGG$REVTC from database.
Deleted REPLICAT REVTC.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2) >
15. Select the OGG_SRC window and stop and delete the GUEVT and PGEVT Extract groups:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > info all Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING GUEVT 00:00:00 00:00:03 EXTRACT RUNNING PGEVT 00:00:00 00:00:01
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > stop er * Sending STOP request to EXTRACT GUEVT ...
Request processed.
Sending STOP request to EXTRACT PGEVT ...
Request processed.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > delete er * ! Deleted EXTRACT GUEVT.
Deleted EXTRACT PGEVT.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) >
16. Unregister the GUEVT Extract from the ogg1 database:
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) > UNREGISTER EXTRACT guevt DATABASE
Successfully unregistered EXTRACT GUEVT from database.
GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT) >
This completes Practice 5-3. Continue with Practice 5-4.