Oracle GoldenGate 11G synchronization configuration

Keywords: Oracle

 

Item

Source System

Target System

Platform

CENTOS7 - 64bit

CENTOS7 - 64bit

Database

Oracle 11.2.0.4

Oracle 11.2.0.4

Character Set

AL32UTF8

AL32UTF8

ORACLE_SID

cd

sz

Listener Name/Port

LISTENER/1521

LISTENER/1521

Goldengate User

ogg

ogg

 

1. Install OGG and create a directory (both source and target libraries have this operation)

[oracle@localhost oracle]$ mkdir ogg
[oracle@localhost oracle]$ cd ogg/
[oracle@localhost ogg]$ unzip V34339-01.zip
[oracle@localhost ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar 
[oracle@localhost ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (localhost.localdomain) 1> create subdirs

Creating subdirectories under current directory /dbfile/oracle/ogg

Parameter files                /dbfile/oracle/ogg/dirprm: already exists
Report files                   /dbfile/oracle/ogg/dirrpt: created
Checkpoint files               /dbfile/oracle/ogg/dirchk: created
Process status files           /dbfile/oracle/ogg/dirpcs: created
SQL script files               /dbfile/oracle/ogg/dirsql: created
Database definitions files     /dbfile/oracle/ogg/dirdef: created
Extract data files             /dbfile/oracle/ogg/dirdat: created
Temporary files                /dbfile/oracle/ogg/dirtmp: created
Stdout files                   /dbfile/oracle/ogg/dirout: created

The purpose of each catalog is as follows:

name purpose
         dirprm         Store configuration information of OGG parameters
         dirrpt         Store progress report file
         dirchk         Store checkpoint files
         dirpcs         Store process status file
         dirsql         Store SQL script files
         dirdef         Store the data definition file generated by DEFGEN tool
         dirdat         Store the Trail file, that is, the log file captured by the Capture process
         dirtmp         When the memory required by a transaction exceeds the allocated memory, it is stored in this directory by default

2. Configure Oracle database for Goldengate

2.1 create OGG users in the source library (cd) and empower them

create tablespace OGG datafile '/dbfile/oracle/oradata/cd/OGG.DBF' size 2G;
create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on OGG;
grant CONNECT, RESOURCE to OGG;
grant CREATE SESSION, ALTER SESSION to OGG;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to OGG;
grant ALTER ANY TABLE to OGG;
grant FLASHBACK ANY TABLE to OGG;
grant EXECUTE ON DBMS_FLASHBACK to OGG;
grant SELECT ON DBA_CLUSTERS to OGG;
grant SELECT ANY TRANSACTION to OGG;

2.2 create a test table in the source library, insert data, and add primary key constraints

create table SCOTT.TO_BASE
(
  mo_number       VARCHAR2(30) not null,
  creater         VARCHAR2(20),
  createdate      DATE default SYSDATE
);
alter table SCOTT.TO_BASE
  add constraint PK_C_MO_BASE primary key (MO_NUMBER);

insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('111', 'LEO', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss'));

insert into SCOTT.TO_BASE (MO_NUMBER, CREATER, CREATEDATE) values ('222', 'TOM', to_date('08-11-2021 18:51:23', 'dd-mm-yyyy hh24:mi:ss'));

commit;

2.3 create OGG users in the target database (sz) and empower them

create tablespace OGG datafile '/dbfile/oracle/oradata/sz/OGG.DBF' size 3G;
create user OGG identified by "OGG" default tablespace OGG temporary tablespace TEMP quota unlimited on OGG;
grant CONNECT, RESOURCE to OGG;
grant CREATE SESSION, ALTER SESSION to OGG;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to OGG;
grant CREATE TABLE to OGG;
grant INSERT ANY TABLE to OGG;
grant UPDATE ANY TABLE to OGG;
grant DELETE ANY TABLE to OGG;

2.4 create a test table structure in the target library (just an empty table)

create table SCOTT.TO_BASE
(
  mo_number       VARCHAR2(30) not null,
  creater         VARCHAR2(20),
  createdate      DATE default SYSDATE
);
alter table SCOTT.TO_BASE
  add constraint PK_C_MO_BASE primary key (MO_NUMBER);

2.5 enable supplementary log mode and forced record mode for source database

SQL> alter database add supplemental log data;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
YES

2.5 source library open archive log

[oracle@localhost ~]$ mkdir /db/arch
SQL> alter system set log_archive_dest_1='location=/db/arch';
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter system archive log current;
SQL> archive log list;

2.6 adding table level TRANDATA to source database

GGSCI (localhost.localdomain) 2> dblogin userid OGG,password OGG
Successfully logged into database.

GGSCI (localhost.localdomain) 3> add trandata SCOTT.TO_BASE

Logging of supplemental redo data enabled for table SCOTT.TO_BASE.

GGSCI (localhost.localdomain) 4> info trandata SCOTT.TO_BASE

Logging of supplemental redo log data is enabled for table SCOTT.TO_BASE.

Columns supplementally logged for table SCOTT.TO_BASE: MO_NUMBER.

2.7 configuring source library MGR management process

GGSCI (localhost.localdomain) 5> edit params mgr

PORT 7809
PURGEOLDEXTRACTS ./dirdat, USECHECKPOINTS

GGSCI (localhost.localdomain) 6> start mgr

Manager started.

GGSCI (localhost.localdomain) 7> info mgr

Manager is running (IP port localhost.localdomain.7809).

2.8 configure MGR management process of target library

GGSCI (localhost.localdomain) 2> edit params mgr

PORT 7809
PURGEOLDEXTRACTS /dbfile/oracle/ogg/dirdat, USECHECKPOINTS

GGSCI (localhost.localdomain) 3> start mgr

Manager started.

GGSCI (localhost.localdomain) 4> info mgr

Manager is running (IP port localhost.localdomain.7809).

Parameter Description:

PORT 7809: OGG management process monitoring port.

PURGEOLDEXTRACTS: clears unneeded trail files.

/ogg/dirdat: where the trail file is stored.

USECHECKPOINTS: use checkpoint queues.

Only the above parameters are used in this experiment. See the following table for other MGR parameters, which are extracted from the official documents of OGG.

Manager parameters: General

name purpose
        CHARSET         Speci  fies a multibyte character set for the process to us

instead of the operating system default when reading the

parameter file.
        COMMENT         Allows insertion of comments in a parameter file.
        SOURCEDB         Specifies a data source name as part of the login
        USERID         Provides login information for Manager when it needs to

access the database.
        SYSLOG         Filters the types of Oracle GoldenGate messages that are

written to the system logs.

Manager parameters: Port management

name purpose
        DYNAMICPORTLIST         Specifies the ports that Collector can dynamically allocate.
        PORT         Establishes the TCP/IP port number on which Manager listens

for requests.

Manager parameters: Process management

name purpose
        AUTORESTART         Specifies processes to be restarted by Manager after a
        AUTOSTART         Specifies processes to be started when Manager starts.
        BOOTDELAYMINUTES         Determines how long after system boot time Manager delays

until performing main processing activities. This parameter

supports Windows.
        UPREPORT         Determines how often process heartbeat messages are reported.

Manager parameters: Event management

name purpose
        DOWNCRITICAL         Reports processes that stopped gracefully or abnormally.
        DOWNREPORT         Controls the frequency for reporting stopped processes.
        LAGCRITICAL         Specifies a lag threshold that is considered critical and

generates a warning to the error log.
        LAGINFO         Specifies a lag threshold at which an informational message

is reported to the error log.
        LAGREPORT         Sets an interval for reporting lag time to the error log.

Manager parameters: Maintenance

name purpose
        CHECKMINUTES         Determines how often Manager cycles through maintenance
        PURGEDDLHISTORY         Purges rows from the Oracle DDL history table when they are

no longer needed.
        PURGEDDLHISTORYALT         Purges rows from the alternate Oracle DDL history table that

keeps track of partition IDs that are associated with a

table ID.
        PURGEMARKERHISTORY         Purges Oracle marker table rows that are no longer needed.
        PURGEOLDEXTRACTS         Purges trail data that is no longer needed.
        PURGEOLDTASKS         Purges Extract and Replicat tasks after a specified period

of time.
        STARTUPVALIDATIONDELAY[CSECS]         Sets a delay time after which Manager checks that processes

are still running after startup.

2.9 configuring initialization data process

Add a file named Eini_ Extract process for 1:

GGSCI (localhost.localdomain) 16> add extract eini_1,sourceistable
EXTRACT added.


GGSCI (localhost.localdomain) 17> info extract *,tasks

EXTRACT    EINI_1    Initialized   2021-11-08 19:44   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE


GGSCI (localhost.localdomain) 18> edit params eini_1

-- GoldenGate Initial Data Capture
-- for SCOTT.TO_BASE
EXTRACT EINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID OGG, PASSWORD OGG
RMTHOST 192.168.1.102, MGRPORT 7809
RMTTASK REPLICAT, GROUP RINI_1
TABLE SCOTT.TO_BASE;

Parameter introduction:

EXTRACT EINI_1: This is an extract process with the name Eini_ one

SETENV: the environment variable must be set the same as the database character set, otherwise it may be garbled

USERID: database OGG user

PASSWORD: PASSWORD of the database user OGG

RMTHOST: the address of the target side. If resolution has been set in the / etc/hosts file, the host name can be written

MGRPORT: the port on which the target MGR management process listens

Rmttask replicate: the group and name of the target replicate application process

TABLE: the name of the TABLE on the source side to initialize the data

Edit the capture process Eini_ After 1, you also need to configure the replicate application process on the target side. The name should be the same as the capture process Eini on the source side_ 1. The rmttask replicate parameter configuration is the same, that is, rmttask replicate Rini needs to be configured at the target end_ 1.

 

2.10 configure the replicate process at the target end

GGSCI (localhost.localdomain) 5> add replicat rini_1,specialrun
REPLICAT added.


GGSCI (localhost.localdomain) 6> info replicat *,tasks

REPLICAT   RINI_1    Initialized   2021-11-08 19:49   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN


GGSCI (localhost.localdomain) 7> edit params rini_1

-- GoldenGate Initial Load Delivery
REPLICAT RINI_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
ASSUMETARGETDEFS
USERID OGG, PASSWORD OGG
DISCARDFILE ./dirrpt/RINIaa.dsc, PURGE
MAP scott.*, TARGET scott.*;

2.11 initialization data

Start the Initial Load process Eini in the source library_ 1, and then Rini in the target library_ 1 the process will start automatically:

GGSCI (localhost.localdomain) 19> start extract eini_1

Sending START request to MANAGER ...
EXTRACT EINI_1 starting

GGSCI (localhost.localdomain) 20> info extract eini_1

EXTRACT    EINI_1    Initialized   2021-11-08 19:44   Status RUNNING
Checkpoint Lag       Not Available
Log Read Checkpoint  Not Available
                     First Record         Record 0
Task                 SOURCEISTABLE

Start the source side capture process Eini_ After 1, under normal conditions (if the configuration is OK), the source side data has been transmitted to the target side. You can VIEW the source side capture process Eini through the VIEW command_ 1. Working status of the.

GGSCI (localhost.localdomain) 21> view report eini_1

Finally, you can see that two pieces of test data have been inserted

Output to RINI_1:

From Table SCOTT.TO_BASE:
       #                   inserts:         2
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0


REDO Log Statistics
  Bytes parsed                    0
  Bytes output                  222

You can also view logs in the target library

GGSCI (localhost.localdomain) 9> view report rini_1

After initial loading, extract the process EINI_1 and replication process RINI_1. It stops automatically. Usually, initialization data is only done once. You can view the status of the process through the INFO command.

Source library

GGSCI (localhost.localdomain) 22> info extract eini_1

EXTRACT    EINI_1    Last Started 2021-11-08 19:51   Status STOPPED
Checkpoint Lag       Not Available
Log Read Checkpoint  Table SCOTT.TO_BASE
                     2021-11-08 19:51:50  Record 2
Task                 SOURCEISTABLE

Target library

GGSCI (localhost.localdomain) 11> info replicat rini_1

REPLICAT   RINI_1    Initialized   2021-11-08 19:49   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:11:32 ago)
Log Read Checkpoint  Not Available
Task                 SPECIALRUN

2.12 source side configuration capture process

GGSCI (localhost.localdomain) 24> edit params pora_1

-- Data Pump parameter file to read the local trail of SCOTT.TO_BASE changes
EXTRACT PORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
PASSTHRU
RMTHOST 192.168.1.102, MGRPORT 7809
RMTTRAIL ./dirdat/ra
TABLE SCOTT.TO_BASE;

2.13 add data pump extraction group

GGSCI (localhost.localdomain) 26> add extract pora_1,exttrailsource ./dirdat/la
EXTRACT added.


GGSCI (localhost.localdomain) 27> info extract pora_1

EXTRACT    PORA_1    Initialized   2021-11-08 20:07   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File ./dirdat/la000000
                     First Record  RBA 0

2.14 add GoldenGate remote path to source library

GGSCI (localhost.localdomain) 28> add rmttrail ./dirdat/ra,extract pora_1,megabytes 5
RMTTRAIL added.

2.15 start the source end capture process EORA_ one

GGSCI (localhost.localdomain) 29> start extract pora_1

Sending START request to MANAGER ...
EXTRACT PORA_1 starting


GGSCI (localhost.localdomain) 30> info extract pora_1

EXTRACT    PORA_1    Last Started 2021-11-08 20:09   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:02:10 ago)
Log Read Checkpoint  File ./dirdat/la000000
                     First Record  RBA 0


GGSCI (localhost.localdomain) 31> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     PORA_1      00:00:00      00:00:05

 

Posted by Holoverse on Mon, 08 Nov 2021 13:42:29 -0800

Hot Keywords

©2024 Programmer Group