Oracle GoldenGate: Working With Tokens and Environment Variables

Introduction

Oracle GoldenGate contains advanced functionality that exposes a wealth of information users may leverage. In this article we shall discuss three of these, TOKENS; which is user defined data written to Oracle GoldenGate Trails, the Column Conversion Function @TOKEN; which is used to retrieve the token data from the Oracle GoldenGate Trail, and the Column Conversion Function @GETENV; which is used to get information about the Oracle GoldenGate environment. We will demonstrate the use of each as data is replicated between an Oracle 12c Multi-tenant Database and MySQL Community Server 5.7 Database.

Main Article

What are Oracle GoldenGate Tokens?

Tokens are labels used to identify user defined data stored in the Oracle GoldenGate Trail Record Header. Tokens are defined via the Extract TABLE parameter; and, must consist of a name identifying the token and the token data. The token data character string may be up to 2000 bytes in length and may be either user specified text enclosed within single quotes or the results of an Oracle GoldenGate Column Conversion Function.

When using tokens in the replication stream, the Extract Data Pump cannot be in PASSTHRU mode.

Token data may be used in the COLMAP clause of a Replicat MAP statement, within a SQLEXEC, a UserExit, or a Macro. To retrieve the token data from the Oracle GoldenGate Trail, use the Column Conversion Function @TOKEN as input to any of the previously mentioned parameters.

Demonstration Environment

We will be replicating the sample Oracle HR database EMPLOYEES table to MySQL.

The Oracle table specifications are:

CREATE TABLE “HR”.”EMPLOYEES”
( “EMPLOYEE_ID” NUMBER(6,0),
“FIRST_NAME” VARCHAR2(20 BYTE),
“LAST_NAME” VARCHAR2(25 BYTE) CONSTRAINT “EMP_LAST_NAME_NN” NOT NULL ENABLE,
“EMAIL” VARCHAR2(25 BYTE) CONSTRAINT “EMP_EMAIL_NN” NOT NULL ENABLE,
“PHONE_NUMBER” VARCHAR2(20 BYTE),
“HIRE_DATE” DATE CONSTRAINT “EMP_HIRE_DATE_NN” NOT NULL ENABLE,
“JOB_ID” VARCHAR2(10 BYTE) CONSTRAINT “EMP_JOB_NN” NOT NULL ENABLE,
“SALARY” NUMBER(8,2),
“COMMISSION_PCT” NUMBER(2,2),
“MANAGER_ID” NUMBER(6,0),
“DEPARTMENT_ID” NUMBER(4,0),
CONSTRAINT “EMP_SALARY_MIN” CHECK (salary > 0) ENABLE,
CONSTRAINT “EMP_EMAIL_UK” UNIQUE (“EMAIL”)
CONSTRAINT “EMP_EMP_ID_PK” PRIMARY KEY (“EMPLOYEE_ID”)
CONSTRAINT “EMP_DEPT_FK” FOREIGN KEY (“DEPARTMENT_ID”)
REFERENCES “HR”.”DEPARTMENTS” (“DEPARTMENT_ID”) ENABLE,
CONSTRAINT “EMP_JOB_FK” FOREIGN KEY (“JOB_ID”)
REFERENCES “HR”.”JOBS” (“JOB_ID”) ENABLE,
CONSTRAINT “EMP_MANAGER_FK” FOREIGN KEY (“MANAGER_ID”)
REFERENCES “HR”.”EMPLOYEES” (“EMPLOYEE_ID”) ENABLE
);

The MySQL table specifications are:

CREATE TABLE `EMPLOYEES` (
`EMPLOYEE_ID` decimal(6,0) NOT NULL,
`FIRST_NAME` varchar(20) DEFAULT NULL,
`LAST_NAME` varchar(25) NOT NULL,
`EMAIL` varchar(25) NOT NULL,
`PHONE_NUMBER` varchar(20) DEFAULT NULL,
`HIRE_DATE` date NOT NULL,
`JOB_ID` varchar(10) NOT NULL,
`SALARY` decimal(8,2) DEFAULT NULL,
`COMMISSION_PCT` decimal(2,2) DEFAULT NULL,
`MANAGER_ID` decimal(6,0) DEFAULT NULL,
`DEPARTMENT_ID` varchar(45) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `OGG_TOKENS` (
`EMPLOYEE_ID` decimal(6,0) NOT NULL,
`OGG_TOKEN_NAME` varchar(200) NOT NULL,
`OGG_TOKEN_DATA` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`EMPLOYEE_ID`, `OGG_TOKEN_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Token Example

In my source Integrated Extract, I will create the token tkn-example-text and replicate the data downstream to MySQL. In the Integrated Extract parameter file, I add the token to the TABLE statement:

extract epdborcl
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
exttrail ./dirdat/ea
logallsupcols
updaterecordformat compact
reportcount every 60 seconds, rate
table pdborcl.tpc.*;
table pdborcl.hr.countries;
table pdborcl.hr.departments;
table pdborcl.hr.job_history;
table pdborcl.hr.jobs;
table pdborcl.hr.locations;
table pdborcl.hr.regions;
table pdborcl.hr.employees,
tokens (tkn-example-test = ‘Example token data set on Integrated Extract’)
;

When data for the employees table is captured, the token id and data will be written to the Oracle GoldenGate Trail. Using logdump and setting the option usertoken detail, we can see the token data:

Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    60  (x003c)   IO Time    : 2016/11/21 11:27:22.001.435
IOType     :   134  (x86)     OrigNode   :   255  (xff)
TransInd   :     .  (x00)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        272       AuditPos   : 11324432
Continued  :     N  (x00)     RecCount   :     1  (x01)
2016/11/21 11:27:22.001.435 GGSUnifiedUpdate     Len    60 RBA 2568
Name: PDBORCL.HR.EMPLOYEES  (TDR Index: 1)
After  Image:                                             Partition 12   GU b
0000 001c 0000 000a 0000 0000 0000 0000 00cf 000a | ………………..
000a 0000 0000 0000 0000 003c 0000 000a 0000 0000 | ………..<……..
0000 0000 00cf 000a 000a 0000 0000 0000 0000 00d2 | ………………..
User tokens:   62 bytes
tkn-example-test    : Example token data set on Integrated Extract

To send data downstream to the MySQL GoldenGate instance, I create an Extract Data Pump with the following settings:

extract pmysql
rmthost 192.168.120.46, mgrport 15000
rmttrail ./dirdat/om
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
table pdborcl.hr.*;

We can verify the data delivery to the target GoldenGate instance by viewing the Remote GoldenGate Trail with logdump:

Hdr-Ind    :     E  (x45)     Partition  :     .  (x0c)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    60  (x003c)   IO Time    : 2016/11/21 11:27:22.001.435
IOType     :   134  (x86)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :        272       AuditPos   : 20475408
Continued  :     N  (x00)     RecCount   :     1  (x01)
2016/11/21 11:27:22.001.435 GGSUnifiedUpdate     Len    60 RBA 3903
Name: PDBORCL.HR.EMPLOYEES  (TDR Index: 1)
After  Image:                                             Partition 12   GU s
0000 001c 0000 000a 0000 0000 0000 0000 00cf 0007 | ………………..
000a 0000 0000 0000 0098 9680 0000 000a 0000 0000 | ………………..
0000 0000 00cf 0007 000a 0000 0000 0000 00a7 d8c0 | ………………..
User tokens:   62 bytes
tkn-example-test    : Example token data set on Integrated Extract

To apply the data to the MySQL OGG_TOKENS table, I use the COLMAP parameter and @TOKEN Column Conversion Function in the Replicat parameter file:

replicat ro12hr
targetdb hr@localhost, userid ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
reportcount every 60 seconds, rate
insertupdates
map pdborcl.hr.employees, target hr.OGG_TOKENS,
colmap (usedefaults,
OGG_TOKEN_NAME = ‘tkn-example-test’,
OGG_TOKEN_DATA = @token(‘tkn-example-test’)
);
noinsertupdates
map pdborcl.hr.*, target hr.*;

To verify the token information is inserted into the target tables, we can use MySQL Workbench to query the target, which returns:

# EMPLOYEE_ID, OGG_TOKEN_NAME, OGG_TOKEN_DATA
‘208’, ‘tkn-example-test’, ‘Example token data set on Integrated Extract’

This simple demonstration is not very useful for more than showing how to set and retrieve a token. Tokens become valuable when you want to record things about the Oracle GoldenGate environment that are useful when creating history tables, monitor details about the replication environment, or record details about the database and operating system environment. To obtain this information we use the @GETENV Column Conversion Function.

 

@GETENV Column Conversion Function

The @GETENV Column Conversion Function is used to obtain information about the Oracle GoldenGate environment. The information returned by @GETENV may be used as input to SQLEXEC queries and Stored Procedures, the COLMAP option of TABLE and MAP, TOKENS, and the UserExit GET_ENV_VALUE function.

There are too many options available for us to cover in this short article; however, the Oracle GoldenGate Windows and Unix Reference Guide provides an in-depth list of all supported function options and their use.

For our demonstration, we shall use @GETENV to do the following: (1) record any lag in each replication group, (2) get the current Julian timestamp in each replication group and use that information to compute lag, (3) get each replication group name, type, and process id, and (4) get details about the source Oracle GoldenGate environment, database environment, server, and operating system.

In the target MySQL database, create two tables for this data:

CREATE TABLE OGG_LAG_DATA (
ROW_TS timestamp(6) NOT NULL,
EXT_NAME varchar(8) NULL,
EXT_TYPE varchar(50) NULL,
EXT_PID varchar (50) NULL,
EXT_LAG_SEC bigint NULL,
DP_NAME varchar(8) NULL,
DP_TYPE varchar(50) NULL,
DP_PID varchar (50) NULL,
DP_LAG_SEC bigint NULL,
REP_NAME varchar(8) NULL,
REP_TYPE varchar(50) NULL,
REP_PID varchar (50) NULL,
REP_LAG_SEC bigint NULL,
SRC_COMMIT_TS timestamp(6) NULL,
EXT_JTS bigint NULL,
EXT_LAG_JTS bigint NULL,
DP_JTS bigint NULL,
DP_LAG_JTS bigint NULL,
REP_JTS bigint NULL,
REP_LAG_JTS bigint NULL,
PRIMARY KEY (ROW_TS)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE OGG_ENV_DATA (
ROW_TS timestamp(6) NOT NULL,
SOURCE_SERVER varchar(100) NULL,
SOURCE_OS_TYPE varchar(100) NULL,
SOURCE_OS_VERSION varchar(100) NULL,
SOURCE_HARDWARE varchar(100) NULL,
SOURCE_GG_VERSION varchar(100) NULL,
SOURCE_DB_NAME varchar(100) NULL,
SOURCE_DB_INSTANCE varchar(100) NULL,
SOURCE_DB_TYPE varchar(100) NULL,
SOURCE_DB_VERSION varchar(100) NULL,
TARGET_DB_NAME varchar(100) NULL,
TARGET_DB_VERSION varchar(100) NULL,
PRIMARY KEY (ROW_TS)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To record information about the source environment, modify the Integrated Extract and Extract Data Pump configuration:

extract epdborcl
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
exttrail ./dirdat/ea
logallsupcols
updaterecordformat compact
reportcount every 60 seconds, rate
table pdborcl.tpc.*;
table pdborcl.hr.countries;
table pdborcl.hr.departments;
table pdborcl.hr.job_history;
table pdborcl.hr.jobs;
table pdborcl.hr.locations;
table pdborcl.hr.regions;
table pdborcl.hr.employees,
tokens (
tkn-ext-group = @GETENV (‘GGENVIRONMENT’, ‘GROUPNAME’),
tkn-ext-type = @GETENV (‘GGENVIRONMENT’, ‘GROUPTYPE’),
tkn-ext-pid = @GETENV (‘GGENVIRONMENT’, ‘PROCESSID’),
tkn-ext-lag = @GETENV (‘LAG’, ‘SEC’),
tkn-ext-jts = @GETENV (‘JULIANTIMESTAMP’)
);

In the EPDBORCL Integrated Extract parameter file, we will use @GETENV to set tokens for information about the GoldenGate operating environment, processing lag in seconds, and the current system time when the Extract processed its latest record; in Julian Timestamp format. We’ll use this timestamp downstream in the Replicat as an alternative method for computing lag.

extract pmysql
rmthost 192.168.120.46, mgrport 15000
rmttrail ./dirdat/om
userid c##ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
table pdborcl.hr.employees,
tokens (
tkn-dp-group = @GETENV (‘GGENVIRONMENT’, ‘GROUPNAME’),
tkn-dp-type = @GETENV (‘GGENVIRONMENT’, ‘GROUPTYPE’),
tkn-dp-pid = @GETENV (‘GGENVIRONMENT’, ‘PROCESSID’),
tkn-dp-lag = @GETENV (‘LAG’, ‘SEC’),
tkn-dp-jts = @GETENV (‘JULIANTIMESTAMP’)
);
table pdborcl.hr.*;

In the PMYSQL Extract Data Pump, we use the same settings as the Integrated Extract to gather information about its operating environment.

In the target MySQL GoldenGate instance, configure the Replicat to apply the token data, return information about its operating environment, and perform lag calculations from the Julian Timestamps recorded by the Integrated Extract and Extract Data Pump.

replicat ro12hr
targetdb hr@localhost, userid ggadmin, password AACAAAAAAAAAAAHAAIFBOIYAMCGIMARE, encryptkey default
reportcount every 60 seconds, rate
insertupdates
insertdeletes
map pdborcl.hr.employees, target hr.OGG_LAG_DATA,
colmap (usedefaults,
ROW_TS = @date (‘yyyy-mm-dd hh:mi:ss.ffffff’, ‘JTS’, @getenv (‘JULIANTIMESTAMP’) ),
EXT_NAME = @token (‘tkn-ext-group’),
EXT_TYPE = @token (‘tkn-ext-type’),
EXT_PID = @token (‘tkn-ext-pid’),
EXT_LAG_SEC = @token (‘tkn-ext-lag’),
DP_NAME = @token (‘tkn-dp-group’),
DP_TYPE = @token (‘tkn-dp-type’),
DP_PID = @token (‘tkn-dp-pid’),
DP_LAG_SEC = @token (‘tkn-dp-lag’),
REP_NAME = @GETENV (‘GGENVIRONMENT’, ‘GROUPNAME’),
REP_TYPE = @GETENV (‘GGENVIRONMENT’, ‘GROUPTYPE’),
REP_PID = @GETENV (‘GGENVIRONMENT’, ‘PROCESSID’),
REP_LAG_SEC = @GETENV (‘LAG’, ‘SEC’),
SRC_COMMIT_TS = @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’),
EXT_JTS = @token (‘tkn-ext-jts’),
EXT_LAG_JTS = @datediff (‘SS’, @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’),
@date (‘yyyy-mm-dd hh:mi:ss.ffffff’, ‘JTS’, @token (‘tkn-ext-jts’) )
),
DP_JTS = @token (‘tkn-dp-jts’),
DP_LAG_JTS = @datediff (‘SS’, @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’),
@date (‘yyyy-mm-dd hh:mi:ss.ffffff’, ‘JTS’, @token (‘tkn-dp-jts’) )
),
REP_JTS = @GETENV (‘JULIANTIMESTAMP’)
REP_LAG_JTS = @datediff (‘SS’, @GETENV (‘GGHEADER’, ‘COMMITTIMESTAMP’),
@date (‘yyyy-mm-dd hh:mi:ss.ffffff’, ‘JTS’, @getenv (‘JULIANTIMESTAMP’) )
)
);
map pdborcl.hr.employees, target hr.OGG_ENV_DATA,
colmap (usedefaults,
ROW_TS = @date (‘yyyy-mm-dd hh:mi:ss.ffffff’, ‘JTS’, @getenv (‘JULIANTIMESTAMP’) ),
SOURCE_SERVER = @GETENV (‘GGFILEHEADER’, ‘HOSTNAME’),
SOURCE_OS_TYPE = @GETENV (‘GGFILEHEADER’, ‘OSTYPE’),
SOURCE_OS_VERSION = @GETENV (‘GGFILEHEADER’, ‘OSVERSION’),
SOURCE_HARDWARE = @GETENV (‘GGFILEHEADER’, ‘HARDWARETYPE’),
SOURCE_GG_VERSION = @GETENV (‘GGFILEHEADER’, ‘GGVERSIONSTRING’),
SOURCE_DB_NAME = @GETENV (‘GGFILEHEADER’, ‘DBNAME’),
SOURCE_DB_INSTANCE = @GETENV (‘GGFILEHEADER’, ‘DBINSTANCE’),
SOURCE_DB_TYPE = @GETENV (‘GGFILEHEADER’, ‘DBTYPE’),
SOURCE_DB_VERSION = @GETENV (‘GGFILEHEADER’, ‘DBVERSIONSTRING’),
TARGET_DB_NAME = @GETENV (‘DBENVIRONMENT’, ‘DBNAME’),
TARGET_DB_VERSION = @GETENV (‘DBENVIRONMENT’, ‘DBVERSION’)
);
noinsertupdates
noinsertdeletes
map pdborcl.hr.*, target hr.*;

In the Replicat, @TOKEN is used to retrieve tokens in the Remote Extract Trail set by the Oracle Integrated Extract and Extract Data Pump. The @DATE Column Conversion Function converts the current server timestamp, in Julian Timestamp format, into a MySQL timestamp. which is then applied to the ROW_TS column of the target tables.

We use @GETENV to return information about the Replicat operating environment, retrieve the source record commit timestamp from the GoldenGate Trail record header, retrieve information about the source server and database from the GoldenGate Trail file header, and retrieve information about the target database environment.

@DATEDIFF computes the difference in seconds between the source record commit timestamp and the Julian Timestamp tokens recorded for each record by Integrated Extract, Extract Data Pump, and Replicat. @DATE is used to convert the Julian Timestamp to the designated timestamp format.

INSERTUPDATES and INSERTDELETES tells the Replicat to convert any update or delete operations against the source EMPLOYEES table into insert operations for the target OGG_LAG_DATA and OGG_ENV_DATA tables. These settings are toggled off via NOINSERTUPDATES and NOINSERTDELETES before the wildcard MAP statement; which ensures all source insert, update, and deletes are applied correctly to the remaining target HR tables.

We can use MySQL Workbench to review the test results:

select ROW_TS, EXT_NAME, EXT_PID, EXT_LAG_SEC, EXT_JTS, EXT_LAG_JTS from OGG_LAG_DATA;
# ROW_TS, EXT_NAME, EXT_PID, EXT_LAG_SEC, EXT_JTS, EXT_LAG_JTS
‘2016-11-21 14:56:38.970890’, ‘EPDBORCL’, ‘17253’, ‘0’, ‘212346515057710776’, ‘0’
‘2016-11-21 14:56:38.972303’, ‘EPDBORCL’, ‘17253’, ‘0’, ‘212346515057710839’, ‘0’
‘2016-11-21 14:56:38.974375’, ‘EPDBORCL’, ‘17253’, ‘0’, ‘212346515057710839’, ‘0’
‘2016-11-21 14:56:38.976066’, ‘EPDBORCL’, ‘17253’, ‘0’, ‘212346515057710839’, ‘0’

select ROW_TS, DP_NAME, DP_PID, DP_LAG_SEC, DP_JTS, DP_LAG_JTS from OGG_LAG_DATA;
# ROW_TS, DP_NAME, DP_PID, DP_LAG_SEC, DP_JTS, DP_LAG_JTS
‘2016-11-21 14:56:38.970890’, ‘PMYSQL’, ‘17763’, ’98’, ‘212346515155563697’, ’98’
‘2016-11-21 14:56:38.972303’, ‘PMYSQL’, ‘17763’, ’98’, ‘212346515155603197’, ’98’
‘2016-11-21 14:56:38.974375’, ‘PMYSQL’, ‘17763’, ’98’, ‘212346515155603197’, ’98’
‘2016-11-21 14:56:38.976066’, ‘PMYSQL’, ‘17763’, ’98’, ‘212346515155603197’, ’98’

select ROW_TS, REP_NAME, REP_PID, REP_LAG_SEC, REP_JTS, REP_LAG_JTS from OGG_LAG_DATA;
# ROW_TS, REP_NAME, REP_PID, REP_LAG_SEC, REP_JTS, REP_LAG_JTS
‘2016-11-21 14:56:38.970890’, ‘RO12HR’, ‘768’, ‘3141’, ‘212346518198970890’, ‘3141’
‘2016-11-21 14:56:38.972303’, ‘RO12HR’, ‘768’, ‘3141’, ‘212346518198972303’, ‘3141’
‘2016-11-21 14:56:38.974375’, ‘RO12HR’, ‘768’, ‘3141’, ‘212346518198974375’, ‘3141’
‘2016-11-21 14:56:38.976066’, ‘RO12HR’, ‘768’, ‘3141’, ‘212346518198976066’, ‘3141’

select ROW_TS, SOURCE_SERVER, SOURCE_OS_TYPE, SOURCE_OS_VERSION, SOURCE_HARDWARE from OGG_ENV_DATA;
# ROW_TS, SOURCE_SERVER, SOURCE_OS_TYPE, SOURCE_OS_VERSION, SOURCE_HARDWARE
‘2016-11-21 14:44:46.198673’, ‘centos0ra12’, ‘Linux’, ‘#1 SMP Thu Mar 31 16:04:38 UTC 2016’, ‘x86_64’
‘2016-11-21 14:47:03.848363’, ‘centos0ra12’, ‘Linux’, ‘#1 SMP Thu Mar 31 16:04:38 UTC 2016’, ‘x86_64’
‘2016-11-21 14:47:03.849590’, ‘centos0ra12’, ‘Linux’, ‘#1 SMP Thu Mar 31 16:04:38 UTC 2016’, ‘x86_64’
‘2016-11-21 14:47:03.850831’, ‘centos0ra12’, ‘Linux’, ‘#1 SMP Thu Mar 31 16:04:38 UTC 2016’, ‘x86_64’

select ROW_TS, SOURCE_GG_VERSION from OGG_ENV_DATA;
# ROW_TS, SOURCE_GG_VERSION
‘2016-11-21 14:44:46.198673’, ‘12.2.Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO’
‘2016-11-21 14:47:03.848363’, ‘12.2.Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO’
‘2016-11-21 14:47:03.849590’, ‘12.2.Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO’
‘2016-11-21 14:47:03.850831’, ‘12.2.Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO’

select ROW_TS, SOURCE_DB_NAME, SOURCE_DB_INSTANCE, SOURCE_DB_TYPE, SOURCE_DB_VERSION from OGG_ENV_DATA;
# ROW_TS, SOURCE_DB_NAME, SOURCE_DB_INSTANCE, SOURCE_DB_TYPE, SOURCE_DB_VERSION
‘2016-11-21 14:44:46.198673’, ‘ORCL’, ‘orcl’, ‘ORACLE’, ‘12.1.Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production\nPL/SQL Release 12.’
‘2016-11-21 14:47:03.848363’, ‘ORCL’, ‘orcl’, ‘ORACLE’, ‘12.1.Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production\nPL/SQL Release 12.’
‘2016-11-21 14:47:03.849590’, ‘ORCL’, ‘orcl’, ‘ORACLE’, ‘12.1.Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production\nPL/SQL Release 12.’
‘2016-11-21 14:47:03.850831’, ‘ORCL’, ‘orcl’, ‘ORACLE’, ‘12.1.Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production\nPL/SQL Release 12.’

select ROW_TS, TARGET_DB_NAME, TARGET_DB_VERSION from OGG_ENV_DATA;
# ROW_TS, TARGET_DB_NAME, TARGET_DB_VERSION
‘2016-11-21 14:44:46.198673’, ‘hr’, ‘MySQL\nServer Version: 5.7.16\nClient Version: 5.6.14\nHost Connection: Localhost via UNIX socket\nProto’
‘2016-11-21 14:47:03.848363’, ‘hr’, ‘MySQL\nServer Version: 5.7.16\nClient Version: 5.6.14\nHost Connection: Localhost via UNIX socket\nProto’
‘2016-11-21 14:47:03.849590’, ‘hr’, ‘MySQL\nServer Version: 5.7.16\nClient Version: 5.6.14\nHost Connection: Localhost via UNIX socket\nProto’
‘2016-11-21 14:47:03.850831’, ‘hr’, ‘MySQL\nServer Version: 5.7.16\nClient Version: 5.6.14\nHost Connection: Localhost via UNIX socket\nProto’

 

Summary

In this article we presented the Oracle GoldenGate @TOKEN, @TOKENS, and @GETENV Column Conversion functions and demonstrated their use by replicating data between an Oracle Multi-tenant Database and MySQL Community Server Database.

Add Your Comment