안녕하세요.
‘수상한 김토끼’ 입니다.
이 포스팅은 Oracle Database 19c에 OGG를 설치하고 소스-타겟 복제를 설정하는 과정을 정리 한 내용입니다.
설치과정은 크게 동기화를 수행할 DB생성과 OGG를 설치하고 구성하는 2개의 과정으로 진행됩니다.
1장(https://with-kami.duckdns.org/oracle-database/ogg/161)에서 설치를 완료하였으니 소스와 타겟을 연동하는 작업을 진행해 보겠습니다.
우선 SQLPlus에 접속 후 다음 SQL을 수행하여 소스 DB서버에 연동 대상 스키마와 테이블을 생성해 줍니다.
CREATE USER TEST IDENTIFIED BY test;
GRANT CONNECT, RESOURCE TO TEST;
alter user test quota unlimited on users;
conn test
CREATE TABLE TEST.TEST
(ID NUMBER(10),
LASTNAME VARCHAR2(20),
FIRSTNAME VARCHAR2(20),
CONSTRAINT ID_PK PRIMARY KEY (ID)
);[oracle@ogg-1 ~]$ ss SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 12 10:58:52 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> CREATE USER TEST IDENTIFIED BY test; User created. SQL> GRANT CONNECT, RESOURCE TO TEST; Grant succeeded. SQL> alter user test quota unlimited on users; User altered. SQL> conn test Enter password: Connected. SQL> CREATE TABLE TEST.TEST 2 (ID NUMBER(10), 3 LASTNAME VARCHAR2(20), 4 FIRSTNAME VARCHAR2(20), 5 CONSTRAINT ID_PK PRIMARY KEY (ID) 6 ); Table created. SQL>
연동할 테이블을 작성하였으니 OGG에 해당 테이블을 등록해 주겠습니다.
ggsci명령으로 OGG에 접속 후 ogg유저로 로그인을 진행합니다.
dblogin userid ogg, password ogg[oracle@ogg-1 ~]$ gs Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI (ogg-1) 1> dblogin userid ogg, password ogg Successfully logged into database. GGSCI (ogg-1 as ogg@ogg1) 2>
로그인이 되었으니 test 테이블을 ogg에 등록 해 줍니다.
add trandata test.testGGSCI (ogg-1 as ogg@ogg1) 2> add trandata test.test 2024-07-12 11:03:43 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TEST. 2024-07-12 11:03:43 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TEST. 2024-07-12 11:03:43 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TEST. 2024-07-12 11:03:44 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TEST ***** Oracle Goldengate support native capture on table TEST.TEST. Oracle Goldengate marked following column as key columns on table TEST.TEST: ID. GGSCI (ogg-1 as ogg@ogg1) 3>
다음으로 extract(ext01) 설정을 진행 해 줍니다.
edit param ext01
extract ext01
Userid ogg, Password ogg
DiscardFile ./dirout/ext01.dec, append, megabytes 50
DisCardRollover at 00:01
ReportCount Every 1 Records, Rate
ReportRollover at 00:01
WARNLONGTRANS 1H, CHECKINTERVAL 10m
ExtTrail ./dirdat/ea
TABLE test.*;GGSCI (ogg-1 as ogg@ogg1) 3> edit param ext01 extract ext01 Userid ogg, Password ogg DiscardFile ./dirout/ext01.dec, append, megabytes 50 DisCardRollover at 00:01 ReportCount Every 1 Records, Rate ReportRollover at 00:01 WARNLONGTRANS 1H, CHECKINTERVAL 10m ExtTrail ./dirdat/ea TABLE test.*; GGSCI (ogg-1 as ogg@ogg1) 4>
다음 명령으로 설정한 ext01을 시작합니다.
add extract ext01, tranlog, begin now
add exttrail ./dirdat/ea, extract ext01, megabytes 50GGSCI (ogg-1 as ogg@ogg1) 4> add extract ext01, tranlog, begin now EXTRACT added. GGSCI (ogg-1 as ogg@ogg1) 5> add exttrail ./dirdat/ea, extract ext01, megabytes 50 EXTTRAIL added. GGSCI (ogg-1 as ogg@ogg1) 6>
extract 설정이 완료 되었으니 다음 명령으로 타겟DB로 데이터를 넘겨줄 pump에 대한 설정을 진행 해 줍니다.
edit param pmp01
extract pmp01
PassThru
RmtHost 10.0.0.113, MGRPORT 7810
RmtTrail ./dirdat/ea
ReportCount Every 1 Records, Rate
ReportRollover at 00:01
Table test.*;GGSCI (ogg-1 as ogg@ogg1) 6> edit param pmp01 extract pmp01 PassThru RmtHost 10.0.0.113, MGRPORT 7810 RmtTrail ./dirdat/ea ReportCount Every 1 Records, Rate ReportRollover at 00:01 Table test.*; GGSCI (ogg-1 as ogg@ogg1) 7>
생성한 pump(pmp01)을 등록하고 기동 해 줍니다.
add extract pmp01, exttrailsource ./dirdat/ea
add exttrail ./dirdat/ea, extract pmp01, megabytes 50
start pmp01GGSCI (ogg-1 as ogg@ogg1) 8> add extract pmp01, exttrailsource ./dirdat/ea EXTRACT added. GGSCI (ogg-1 as ogg@ogg1) 9> add exttrail ./dirdat/ea, extract pmp01, megabytes 50 EXTTRAIL added. GGSCI (ogg-1 as ogg@ogg1) 10> start pmp01 Sending START request to MANAGER ... EXTRACT PMP01 starting GGSCI (ogg-1 as ogg@ogg1) 11>
이후 info all, info * 등의 명령어로 소스DB OGG의 상태를 확인해 봅니다.
GGSCI (ogg-1 as ogg@ogg1) 13> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT01 00:08:54 00:00:01 EXTRACT RUNNING PMP01 00:00:00 00:00:07 GGSCI (ogg-1 as ogg@ogg1) 14>
소스 DB는 정상적으로 동작하는 걸 확인했으니 타겟DB 설정을 진행하겠습니다.
우선 SQLPlus를 통해 소스 DB와 동일한 테이블과 유저를 생성합니다.
[oracle@ogg-2 ~]$ ss SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 12 11:18:58 2024 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> CREATE USER TEST IDENTIFIED BY test; User created. SQL> GRANT CONNECT, RESOURCE TO TEST; Grant succeeded. SQL> alter user test quota unlimited on users; User altered. SQL> conn test Enter password: Connected. SQL> CREATE TABLE TEST.TEST 2 (ID NUMBER(10), 3 LASTNAME VARCHAR2(20), 4 FIRSTNAME VARCHAR2(20), 5 CONSTRAINT ID_PK PRIMARY KEY (ID) 6 ); Table created. SQL>
타겟 DB구성이 끝났으니, ggsci를 통해 replicate에 대한 설정을 진행합니다.
edit param rep01
Replicat rep01
Userid ogg, Password ogg
DiscardFile ./dirout/rep02.dec, append, megabytes 50
DiscardRollover at 00:01
ReportCount Every 1 Records, Rate
ReportRollover at 00:01
MAP test.test, Target test.test;GGSCI (ogg-2 as ogg@ogg2) 2> edit param rep01 Replicat rep01 Userid ogg, Password ogg DiscardFile ./dirout/rep02.dec, append, megabytes 50 DiscardRollover at 00:01 ReportCount Every 1 Records, Rate ReportRollover at 00:01 MAP test.test, Target test.test; GGSCI (ogg-2 as ogg@ogg2) 3>
설정한 replicate(rep01)을 등록하고 시작 해 줍니다.
add replicat rep01, exttrail ./dirdat/ea nodbcheckpoint
start rep01GGSCI (ogg-2 as ogg@ogg2) 3> add replicat rep01, exttrail ./dirdat/ea nodbcheckpoint REPLICAT added. GGSCI (ogg-2 as ogg@ogg2) 4> start rep01 Sending START request to MANAGER ... REPLICAT REP01 starting GGSCI (ogg-2 as ogg@ogg2) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP01 00:00:00 00:00:00 GGSCI (ogg-2 as ogg@ogg2) 6>
여기까지 OGG의 기본 구성인 Oracle Database의 소스 DB와 타겟DB에 대한 동기화 구성이 완료 되었습니다.
소스DB의 test 테이블에 데이터를 변경하고 commit; 을 주면 데이터가 동기화를 확인해 보실 수 있습니다.