Materias Afectadas por los Acuerdos Comerciales

Overview

In this practice, you use Oracle GoldenGate transformation functions to perform string concatenation, composing a full name from a first name, a middle name, and a family name. The source CUSTOMER table contains the columns C_NAME, C_MIDDLE_NAME, and

C_FAMILY_NAME. The C_FULL_NAME column defined in the target CUSTOMER table will store the result of the string concatenation operation by the Replicat group during the replication process.

Assumptions

You have access to the SRC_USER and TRG_USER schemas on the CDB12c database. A table called CUSTOMER exists and is identically defined in the source and target schemas.

Tasks

1. You will use the Oracle GoldenGate instance located in /u03/ogg/ogg_src to configure a primary Extract group called ETRNF, which extracts all rows from the CUSTOMER table in the SRC_USER Oracle schema.

2. The trail file (./dirdat/ts) will then be read by a Data Pump called PTRNF, which will create a remote trail (./dirdat/ws) that is used to transfer the extracted data to the remote system.

3. You will then connect to the Oracle GoldenGate instance located in /u03/ogg/ogg_trg and configure a Replicat group called RTRNF, which will concatenate the name, the middle name, and the family name into the C_FULL_NAME column.

4. To test your transformation function, navigate to the ~/labs/Practice06/configs directory and connect to the source schema (src_user). Truncate the CUSTOMER table and reload it using the customer.sql script. To verify that data selection works, connect to the target schema by using sqlplus and verify that the C_FULL_NAME column contains the correct concatenation of first, middle, and family names.

This completes Practice 6-3. Continue with Practice 6-4.

Solution 6-3: Applying Data Transformation Functions

Steps

1. Select the OGG_SRC window, where GGSCI is connected to the OGG_SRC Oracle GoldenGate instance, and create the parameter file for the ETRNF Extract group:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> edit param etrnf

Extract etrnf

ExtTrail ./dirdat/ts UserIdAlias ogg_admin LOGALLSUPCOLS

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 128) UPDATERECORDFORMAT COMPACT

SOURCECATALOG ogg1

Table src_user.customer; Table src_user.account;

2. Save the parameter file. Register the ETRNF Extract with the database (ogg1). Add the Extract and its corresponding trail file:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> REGISTER EXTRACT

etrnf DATABASE CONTAINER (ogg1)

Extract ETRNF successfully registered with database at SCN 2541784.

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> add extract

etrnf, Integrated tranlog, begin now

EXTRACT added.

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> add exttrail

./dirdat/ts, Extract etrnf, megabytes 10

EXTTRAIL added.

3. Edit the parameter file for the PTRNF Data Pump Extract:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> edit param ptrnf

Extract ptrnf

RmtHost ogg_target, Mgrport 7909, Compress RmtTrail ./dirdat/ws

Passthru

SOURCECATALOG ogg1

Table src_user.customer; Table src_user.account;

4. Save the parameter file and add the Data Pump, connecting it to the ./dirdat/es trail file:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> add extract

ptrnf, exttrailsource ./dirdat/ts

EXTRACT added.

5. Add the remote trail file and start the Extract and the Data Pump:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> add rmttrail

./dirdat/ws, extract ptrnf, megabytes 10

RMTTRAIL added.

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> start etrnf Sending START request to MANAGER ...

EXTRACT ETRNF starting

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/CDB$ROOT)> start ptrnf Sending START request to MANAGER ...

EXTRACT PTRNF starting

6. Select the OGG_TRG window, where GGSCI is connected to the replication target environment. Edit the parameter file for the RTRNF Replicat group:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2)> edit param rtrnf

Replicat rtrnf AssumeTargetDefs

DiscardFile ./dirrpt/rtrnf.dsc, Purge UserIDAlias ogg_repl

Map ogg1.src_user.customer, target ogg2.trg_user.customer, COLMAP (USEDEFAULTS, C_FULL_NAME = @STRCAT(C_NAME, ' ', C_MIDDLE_NAME, ' ', C_FAMILY_NAME));

7. Save the parameter file and add the Replicat, connecting it to the ./dirdat/ws trail file. Use the NODBCHECKPOINT clause to avoid creating a checkpoint table for this practice:

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2)> add replicat rtrnf,

Integrated exttrail ./dirdat/ws

REPLICAT (Integrated) added. 8. Start the Replicat and exit GGSCI.

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2)> start rtrnf Sending START request to MANAGER ...

REPLICAT RTRNF starting

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2)> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING RTRNF 00:00:00 00:00:07

GGSCI ([HOST] as C##OGG_ADMIN@CDB12c/OGG2)> exit

9. Change the directory to ~/labs/Practice06/configs and connect to the SRC_USER schema by using sqlplus. Reload the CUSTOMER table by using the customer.sql script:

[OS prompt]$ cd ~/labs/Practice06/configs [OS prompt]$ sqlplus src_user/<password>@ogg1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 15 10:28:35 2015

Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Mar 15 2015 09:27:02 +11:00 Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @customer.sql 1 row created.

... Many lines omitted for clarity ...

Commit complete.

10. Without leaving sqlplus, connect to the TRG_USER schema and verify that the replicated rows in the CUSTOMER table have a full name, which is the result of the string concatenation of the name, middle name, and family name:

SQL> connect trg_user/<password>@ogg2 Connected.

SQL> select c_full_name from customer; C_FULL_NAME

--- Cameron Neville Cantu

Austin Kennan Alston Orson Wyatt Powers Arsenio Omar Hampton Addison Baxter Thomas Chester Nissim Mendoza Amos Jackson Greer Ferris Wayne Atkins Benedict Travis Carey Evan Quentin Mcgowan

Vladimir Buckminster Finley

... Many lines omitted for clarity ...

11. Leave the Replicat group (RTRNF) and the Extract groups ETRNF and PTRNF running because they will be used in Practice 6-4.