Oracle 19c | GoldenGate Pre-Requisites for Integrated Extract and Integrated Replicat | Tutorial Part 3
Hi All,
Configuring Integrated Extract and Integrated Replicat Using Oracle GoldenGate 19c on Linux 8.2
Introduction:
Oracle GoldenGate provides very fast replication of heterogeneous databases by reading transaction logs and writing the changes to one or more target databases. Oracle GoldenGate 11g introduced the Integrated Extract feature; Integrated Replicat was delivered with Oracle GoldenGate 12c.
In integrated capture mode, the Oracle GoldenGate Extract process interacts directly with the database log mining server which mines or reads the database redo log files and captures the changes in the form of Logical Change Records (LCRs.) LCRs are written to the GoldenGate trail files.
In integrated mode, the Replicat process leverages the apply processing functionality that is available within the Oracle database. In this mode, Replicat operates as follows:
Reads the Oracle GoldenGate trail.
Performs data filtering, mapping, and conversion.
Constructs logical change records (LCR) that represent source database DML transactions (in committed order). DDL is applied directly by Replicat.
Attaches to a background process in the target database known as a database inbound server by means of a lightweight streaming interface.
Transmits the LCRs to the inbound server, which applies the data to the target database.
This tutorial guides you, how to configure data capture using Integrated Extract and how to configure data delivery using Integrated Replicat.
Demo Scenario:
There is Two Virtual Machines are running a 64 bit version of Linux (OL 8.2 and 7.3) which runs the Oracle RDBMS 19c (12.2.0.3). Two instances of the Oracle GoldenGate 19c software runs on the two virtual machines, one which simulates the replication source(OGGSRC) and the second which simulates the replication target (OGGTRG).
The Oracle 19c RDBMS instance OGGSRC contains three users/schemas:
C##OGGUSER - Password: ogguser:
The Oracle GoldenGate user. This user has DBA privileges and its schema does not contain replication objects. Oracle GoldenGate uses this user to perform replication from the source schema to the target schema.
C#OGGSRC - Password: oggsrc:
The replication source schema. This user only has connect and resource privileges and its schema defines all replication source objects.
C##OGGTRG - Password: oggtrg:
The replication target shcema. This user only has connect and resource privileges and its schema defines all replication target objects.
This demo contains the below:
Creating the necessary database schemas and objects to prepare the environment for replication.
Configuring an Integrated Extract to ensure data capture for the oggsrc schema.
Configuring a data pump Extract to provide data capture for the oggsrc
Configuring an Integrated Replicat to ensure data delivery to the oggtrg schema.
Generating same database activity inserting and updating rows in the replication source schema (oggsrc.)
Verifying that the data is replicated as expected.
Generating some statistics from the Extract and Replicat processes.
Hardware and Software Requirements:
Hardware:
Two Oracle Linux Hosta: it can be two virtual machines.
2 CPUs (each virtual machine)
6GB RAM (each virtual machine)
Softwares:
Oracle GoldenGate on Oracle Linux-64_x86 ( Oracle Software Delivery Cloud ) version 19.1.0.0.4, part number V983658-01.zip. This is the part number for Linux 64_x86.
Oracle 19c Database installed on the hosts both virtual machines.
Oracle DDL and DML files needed for this tutorial and downloaded OBE_DDL_FILES.zip.
Oracle 19c GoldenGate Setup Prerequisites:
1. Install Oracle 19c Database on Source and Target VMs
Two database instance OGGSRC and OGGTRG is created and running on both VMs.
Database datafiles are stored in the /oradata directory.
The SYS and SYSTEM users share the same password "oracle"
2. The Oracle GoldenGate software is installed in the following Source and Target:
The Oracle Goldengate replication source environment resides in /softwares/goldengate/19.1.0.0.4/oggsrc
The Oracle Goldengate replication target environment resides in /softwares/goldengate/19.1.0.0.4/oggtrg
The instance parameter STREAMS_POOL_SIZE should be set to at least 512M and the instance must be running in ARCHIVELOG mode.
In addition, this tutorial assumes that the Oracle GoldenGate software is installed in the two directories /softwares/goldengate/19.1.0.0.4/oggsrc and /softwares/goldengate/19.1.0.0.4/oggtrg.
TNS entry across th virtual machines:
[oracle@srlabgg1 admin]$ tnsping oggtrg
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-DEC-2020 19:51:30
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.202)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oggtrg)))
OK (10 msec)
[oracle@srlabgg2 admin]$ tnsping oggsrc
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 22-DEC-2020 07:21:00
Copyright (c) 1997, 2020, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.3.0.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.58.201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oggsrc)))
OK (10 msec)
1. Creating and Preparing the Database Users and Schemas for Replication:
You must create three users on the Source and Target database:
The user ogguser. This user has a heightened level of privileges to allow the Oracle GoldenGate software to perform replication.
The user oggsrc. This is the unprivileged replication source user.
The user oggtrg. This is the unprivileged replication target user.
Additionally, you must download and unzip the SQL scripts used for this tutorial to create database objects and data to be replicated between the replication source (OGGSRC) and the replication target (OGGTRG).
2. Creating database tablespaces and users:
Select the OGGSRC window. Using sqlplus connect to the Oracle database as sysdba. Verify that the database is running in ARCHIVELOG mode.
The database must be running ARCHIVELOG mode. You specified ARCHIVELOG mode when you created the database using DBCA. If you did not create the database in ARCHIVELOG mode, do the following:
SQL> select log_mode from v$database;
SQL> shut immedaite;
SQL> startup mount;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> select log_mode from v$database;
Create two tablespaces, SRCDATA to host the replication source database(OGGSRC) objects, and TRGDATA to host the replication target database(OGGTRG) objects.
on OGGSRC:
create tablespace srcdata datafile '/oradata/OGGSRC/oggsrcplug/srcdata01.dbf' size 50M autoextend on extent management local uniform size 256k;
on OGGTRG:
create tablespace trgdata datafile '/oradata/OGGTRG/oggtrgplug/srcdata01.dbf' size 50M autoextend on extent management local uniform size 256k;
3. Create an Oracle GoldenGate user that can connect to the source and target databases for transactional data (OGGUSER). An Oracle GoldenGate user requires a database user with at least the following privileges:
on OGGSRC:
SQL> create user C##OGGUSER identified by ogguser default tablespace USERS temporary tablespace TEMP container=all;
SQL> grant DBA to C##OGGUSER;
on OGGTRG:
SQL> create user C##OGGUSER identified by ogguser default tablespace USERS temporary tablespace TEMP container=all;
SQL> grant DBA to C##OGGUSER;
on OGGSRC:
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##OGGUSER') container all;
on OGGTRG:
SQL> EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('C##OGGUSER') container all;
To learn about DBMS_GOLDENGATE_AUTH syntax, enter the following commands:
SQL> set pages 100
SQL> desc dbms_goldengate_auth
SQL> SELECT text FROM all_source WHERE name='DBMS_GOLDENGATE_AUTH';
4. Create the C##OGGSRC and C##OGGTRG database users. The default tablespace for C##OGGSRC is SRCDATA. The default tablespace for C##OGGTRG is TRGDATA.
These two users only need the CONNECT and RESOURCE privileges. Oracle 12c removed the unlimited quota privilege from the RESOURCE role, so you must now grant quota unlimited to the user.
on OGGSRC:
SQL> create user C##OGGSRC identified by oggsrc123 default tablespace SRCDATA temporary tablespace TEMP container=all;
SQL> grant CONNECT,RESOURCE to C##OGGSRC container=all;
SQL> alter user C##OGGSRC quota unlimited on SRCDATA container=all;
on OGGTRG:
SQL> create user C##OGGTRG identified by oggtrg123 default tablespace TRGDATA temporary tablespace TEMP container=all;
SQL> alter user C##OGGTRG quota unlimited on TRGDATA container=all;
SQL> grant CONNECT,RESOURCE to C##OGGTRG container=all;
5. You must enable minimal supplemental logging by executing the following commands:
on OGGSRC:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
on OGGTRG:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
6. Creating Database Objects and Loading Data:
select the OGGSRC terminal, launch sqlplus connecting to the replication source user (C##OGGSRC):
Run the the oracle_table_creation.sql script to create the required database objects. Make sure three tables have been created:
SQL> @oracle_table_creation.sql >> Following three tables will be created.
ECONOMIC_ENTITY
GDP_BY_YEAR
GDP_GROWTH_BY_YEAR
SQL> select table_name from user_tables;
Run the the economic_entity.sql script to populate the ECONOMIC_ENTITY table.
SQL> @economic_entity.sql
SQL> select count(*) from economic_entity;
on OGGTRG:
select the OGGTRG terminal, launch sqlplus connecting to the replication target user (C##OGGTRG):
Run the the oracle_table_creation.sql script to create the required database objects in the replication target schema. Make sure three tables have been created:
SQL> @oracle_table_creation.sql >> Following three tables will be created.
ECONOMIC_ENTITY
GDP_BY_YEAR
GDP_GROWTH_BY_YEAR
SQL> select table_name from user_tables;
Run the the economic_entity.sql script to populate the ECONOMIC_ENTITY table
SQL> @economic_entity.sql
SQL> select count(*) from economic_entity;
2. Preparing the Oracle GoldenGate Environment for Replication:
Oracle GoldenGate Software Command Interface (GGSCI) to set the parameters for the Oracle GoldenGate manager processes, and you create a password wallet to avoid typing passwords in clear (and in the various scripts.) Later, you use the Oracle GoldenGate TRANDATA option to enable additional logging, needed by the software to uniquely identify each row that has been changed in the database.
Configuring the Oracle GoldenGate Manager Process:
Create two additional terminal windows and name them GGSCI_SRC and GGSCI_TRG. Drag them so that they are aligned with the existing window on your Gnome workspace. You should have five active windows aligned as shown below:
OGGSRC
OGGTRG
GGSCI_SRC
GGSCI_TRG
ROOT
Select the GGSCI_SRC terminal:
IMPORTANT! GGSCI for Oracle will not start if the LD_LIBRARY_PATH environment variable is either undefined or if it does not point to the ${ORACLE_HOME}/lib directory.
Make sure LD_LIBRARY_PATH is correctly set before launching GGSCI. The preferred way to accomplish this is by adding the line:
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
to the file ~/.bashrc and source that file. Change directory to the directory where the Oracle GoldenGate software has been installed (replication source, /softwares/goldengate/19.1.0.0.4/oggsrc) and launch ggsci. Enter the info mgr command to verify that the manager is running.
./ggsci
info all
info mgr
Select the GGSCI_SRC window. Edit the parameter file for the manager. You can choose to edit the file using gedit instead of vi. In this case, enter the command set editor gedit before entering the command edit param mgr. You specify the list of TCP/IP ports available to the manager process, you request to purge the old extracts and you instruct the manager process to automatically start all Extracts whenever the manager process starts. Additionally, the manager will try to restart failed processes three times, every minute until all retries are attempted. The parameters used are:
DynamicPortList
PurgeOldExtracts
Autostart
AutoRestart
edit param mgr
PORT 7809
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3
Select the GGSCI_SRC window. Stop and restart the manager to force it to re-read the newly defined parameters.
stop mgr
start mgr
info mgr detail
Select the GGSCI_TRG terminal:
Change directory to the directory where the Oracle GoldenGate software has been installed (replication target, /softwares/goldengate/19.1.0.0.4/oggtrg) and launch ggsci. Enter the info mgr command to verify that the manager is running.
./ggsci
info all
info mgr
Select the GGSCI_TRG terminal. Add the same parameters you just added for the replication source. In this case, however, you set the auto start and auto restart parameters for Replicats, rather than Etxracts.
edit param mgr
Port 7909
DynamicPortList 20100-20199
PurgeOldExtracts ./dirdat/pe*, UseCheckPoints, MinKeepHours 2
Autostart Replicat R*
AUTORESTART Replicat *, WaitMinutes 1, Retries 3
Select the GGSCI_SRC window. Stop and restart the manager to force it to re-read the newly defined parameters.
stop mgr
start mgr
info mgr detail
Post a Comment: