Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to achieve OGG two-way DML replication". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "OGG two-way DML replication how to achieve" it!

Environment interpretation: hostname:slient,db_name:test as the source library and hostname:one,db_name:onemo as the target library

This time, you only need to configure a reverse operation, that is, one is the source side and slient is the target side.

Configuration steps:

1. Source: check whether the database is in archive mode. It is recommended to do so in archive mode:

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 9

Next log sequence to archive 11

Current log sequence 11

SQL >

Archived

two。 Source library: add additional logs to uniquely identify a row of records and turn on the minimum switch at the database level.

Syntax: alter database add supplemental log data

SQL > select NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database

NAME FOR SUPPLEME

-

ONEMO NO YES

3. Source-side test table

The test data is using the following table BONUS of the scott user. To ensure that the log information of the replicated table is complete, the related table must be logging, and be sure to turn nologing into logging.

SQL > conn scott/tiger

Connected.

SQL > select * from BONUS

No rows selected

SQL >

SQL > desc BONUS

Name Null? Type

-

ENAME VARCHAR2 (10)

JOB VARCHAR2 (9)

SAL NUMBER

COMM NUMBER

SQL > conn / as sysdba

Connected.

SQL >

-- View the force_logging property of table BONUS

Syntax: alter table schema.table_name logging

SQL > select OWNER,TABLE_NAME,STATUS,LOGGING from dba_tables where owner='SCOTT' and table_name='BONUS'

OWNER TABLE_NAME STATUS LOG

SCOTT BONUS VALID YES

4. Source: log in to the database GGSCI (one) 1 > dblogin userid ogg,password ogg with the schema of goldengate

Successfully logged into database.

GGSCI (one as ogg@onemo) 2 >

GGSCI (one as ogg@onemo) 7 > add trandata scott.BONUS

2017-10-27 04:57:07 WARNING OGG-06439 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

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

TRANDATA for scheduling columns has been added on table 'SCOTT.BONUS'.

TRANDATA for instantiation CSN has been added on table 'SCOTT.BONUS'.

GGSCI (one as ogg@onemo) 8 >

5. Source-side configuration crawl process

GGSCI (one as ogg@onemo) 9 > add extract ext_rev, tranlog, begin now,threads 1

EXTRACT added.

GGSCI (one as ogg@onemo) 10 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED EXT_REV 00:00:00 00:00:05

REPLICAT RUNNING REP_DEMO 00:00:00 00:00:06

6. Source side: adding queue files

GGSCI (one as ogg@onemo) 12 > add EXTTRAIL / u01/app/oracle/ogg/dirdat/rv, extract ext_rev,megabytes 100

EXTTRAIL added.

GGSCI (one as ogg@onemo) 13 >

7. The parameter extract of the crawling process edited at the source end

GGSCI (one as ogg@onemo) 30 > edit param ext_rev

EXTRACT ext_rev

Setenv (ORACLE_SID=onemo)

Setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

Userid ogg,password ogg

Exttrail / u01/app/oracle/ogg/dirdat/rv

Dynamicresolution

TABLE scott.bonus

GGSCI (one as ogg@onemo) 31 >

8. The source database starts the extact crawling process:

GGSCI (one as ogg@onemo) 28 > start ext_rev

Sending START request to MANAGER...

EXTRACT EXT_REV starting

GGSCI (one as ogg@onemo) 29 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT_REV 00:08:37 00:00:03

REPLICAT RUNNING REP_DEMO 00:00:00 00:00:01

GGSCI (one as ogg@onemo) 30 >

9. The source database configures the datapump process to transfer the crawling data to the target host. Responsible for TCPIP communication

GGSCI (one as ogg@onemo) 32 > add extract dpe_rev, exttrailsource / u01/app/oracle/ogg/dirdat/rv

EXTRACT added.

Output: how the target host writes also defines the output of the datapumo process.

GGSCI (one as ogg@onemo) 34 > add rmttrail / opt/ogg/ogg_home/dirdat/tv,EXTRACT dpe_rev,MEGABYTES 100

RMTTRAIL added.

GGSCI (one as ogg@onemo) 35 >

10. Configure datapump process parameters on the source side

GGSCI (one as ogg@onemo) 39 > edit param DPE_REV

Extract dpe_rev

Setenv (ORACLE_SID=onemore)

Setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)

Passthru

-- REPORT AT 01:59

-- reportrollover at 02:00

Rmthost 192.168.56.20,mgrport 7809, compress

Rmttrail / opt/ogg/ogg_home/dirdat/tv

Dynamicresolution

Table scott.bonus

~

"dirprm/dpe_rev.prm" [New] 10L, 265C written

GGSCI (one as ogg@onemo) 40 >

GGSCI (one as ogg@onemo) 40 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPPED DPE_REV 00:00:00 00:05:17

EXTRACT RUNNING EXT_REV 00:00:00 00:00:02

REPLICAT RUNNING REP_DEMO 00:00:00 00:00:07

-- start DPE_REV

GGSCI (one as ogg@onemo) 41 > start DPE_REV

Sending START request to MANAGER...

EXTRACT DPE_REV starting

GGSCI (one as ogg@onemo) 42 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPE_REV 00:00:00 00:05:34

EXTRACT RUNNING EXT_REV 00:00:00 00:00:07

REPLICAT RUNNING REP_DEMO 00:00:00 00:00:04

GGSCI (one as ogg@onemo) 43 >

11. The target side creates a checkpoint table for the replicat process

[ogg@slient ogg_home] $pwd

/ opt/ogg/ogg_home

[ogg@slient ogg_home] $ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operating system character set identified as UTF-8.

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

GGSCI (slient) 1 > dblogin userid ogg,password ogg

Successfully logged into database.

GGSCI (slient as ogg@test) 2 > add checkpointtable ogg.rep_bouns_ckpt

Successfully created checkpoint table ogg.rep_bouns_ckpt.

GGSCI (slient as ogg@test) 3 >

twelve。 The target side configures the target side replicate process

GGSCI (slient as ogg@test) 4 > add replicat rep_rev,exttrail / opt/ogg/ogg_home/dirdat/tv,checkpointtable ogg.rep_bouns_ckpt

REPLICAT added.

GGSCI (slient as ogg@test) 5 >

13. Edit destination replicate parameters

GGSCI (slient as ogg@test) 5 > edit param rep_rev

Replicat rep_rev

Setenv (oracle_sid=test)

Setenv (nls_lang = "american_america.zhs16gbk")

Userid ogg,password ogg

-- report at 01:59

-- reportrollover at 02:00

Reperror default,abend

Discardfile / opt/ogg/ogg_home/dirrpt/repb.dsc,append, megabytes 10

Assumetargetdefs

-- allownoopupdates

Dynamicresolution

-- insertallrecords

Map scott.bonus,target scott.bonus

~

~

"dirprm/rep_rev.prm" [New] 13L, 356C written

GGSCI (slient as ogg@test) 6 >

GGSCI (slient as ogg@test) 6 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEA 00:00:00 00:00:07

EXTRACT RUNNING EXTA 00:00:00 00:00:10

REPLICAT STOPPED REP_REV 00:00:00 00:02:57

14. The target side starts and checks whether the replicate process is running

GGSCI (slient as ogg@test) 7 > start REP_REV

Sending START request to MANAGER...

REPLICAT REP_REV starting

GGSCI (slient as ogg@test) 8 > info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING DPEA 00:00:00 00:00:02

EXTRACT RUNNING EXTA 00:00:00 00:00:03

REPLICAT RUNNING REP_REV 00:00:00 00:00:02

GGSCI (slient as ogg@test) 9 >

15. Test the data on the source side and the target side

-- check the source and target libraries before testing:

Source database:

SQL > show user

USER is "SCOTT"

SQL >

SQL > select * from bonus

ENAME JOB SAL COMM

--

Wang sales 1000. 1

SQL >

Target Library:

SQL > show user

USER is "SCOTT"

SQL > select * from BONUS

No rows selected

SQL >

Start testing:

Source database:

SQL > insert into bonus values ('li','manager',10000,0.2)

1 row created.

SQL > commmit

SQL > select * from bonus

ENAME JOB SAL COMM

--

Li manager 10000. 2

Wang sales 1000. 1

Check the target library:

SQL > select * from bonus

ENAME JOB SAL COMM

--

Li manager 10000. 2

Check it out later:

Source database:

SQL > select * from bonus

ENAME JOB SAL COMM

--

Li manager 10000. 2

Li manager 10000. 2

Li manager 10000. 2

Wang sales 1000. 1

Target Library:

SQL > select * from bonus

ENAME JOB SAL COMM

--

Li manager 10000. 2

Li manager 10000. 2

Li manager 10000. 2

Li manager 10000. 2

In this way, copy and copy back and forth between the two libraries (without preventing the log from growing, shutting down the target database, source database related extract, replicate, etc.)

At this point, I believe you have a deeper understanding of "how to achieve OGG two-way DML replication". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.