[ORACLE] OGG core 19.1 install 설치 / Oracle 11g to 19c 동기화

Oracle GoldenGate 

주요기능

1. 복제 시간 단축 (Reducing Latency) : 실시간 로그를 기반으로 변경된 데이터를 캡쳐(CDC)하여 다른 데이터베이스에 동기화
2. 일관성 유지 : commit된 트랜잭션만 이동
3. 다양한 이기종 데이터베이스 및 릴리즈 지원
4. 단순한 아키텍쳐와 구성/설정
5. 다양한 동기화 모델 및 연계 방안 제공
6. 안정성 및 성능 보장 : Direct Log Access + X-Stream 방식으로 구현

 

Test 환경

	Extract & Pump (Source)	Replicat (Target)
Oracle Database Version	11.2.0.4	19.16.0.0.0
Database Patch Version	31537652 (11.2.0.4.201020)	34133642 (19.16.0.0.220719)
OGG Version	19.1	19.1
OGG OS user	oggmig	oggmig
OGG_HOME	/u04/app/oggmig/ogg191	/u02/app/oggmig/ogg191
ORACLE_HOME	/u04/app/oracle/product/11.2.0.4/dbhome_1	/u02/app/oracle/product/19.0.0.0/dbhome_1
ORACLE_SID	SRCEM11	TRGEM19

 

OGG Media (19.1)

https://www.oracle.com/kr/middleware/technologies/goldengate-downloads.html

또는 Oracle edelivery ..

Patch File은 데이터베이스 패치파일 사용하면됨.

 

OGG 동기화 전 버전 호환성 확인 - oracle goldengate certification matrix 19c

-> 구글에 [ oracle goldengate certification matrix 19c ] 검색 시 엑셀 다운받아 확인 가능함 

 

 

1. OGG OS Setting 

1) OS User Create - source, target / root

useradd -g oinstall -G dba -u 5001 oggmig
passwd oggmig

 

2) Profile 수정 - source, target / oggmig

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u04/app/oracle/product/11.2.0.4/dbhome_1
export GI_HOME=/u01/app/19.0.0.0/grid
export OGG_HOME=/u04/app/oggmig/ogg191
export LD_LIBRARY_PATH=$ORACLE_HOME/ctx/lib:$LD_LIBRARY_PATH:$OGG_HOME:$ORACLE_HOME/lib:$GI_HOME/lib
export PATH=$PATH:$ORACLE_HOME/bin:$GI_HOME/bin:$OGG_HOME/OPatch:$OGG_HOME:$ORACLE_HOME/lib

 

3) OS Directory Create - source, target / root

-- OGG_HOME
mkdir -p /u04/app/oggmig/ogg191
chown -R oggmig:oinstall /u04/app/oggmig
mkdir -p /u02/app/oggmig/ogg191
chown -R oggmig:oinstall /u02/app/oggmig

 

 

2. Oracle Database Setting 

1) listener.ora - source, target / oracle

LISTENER_SRCEM11 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1528))
    )
  )

SID_LIST_LISTENER_SRCEM11 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = SRCEM11)
      (ORACLE_HOME = /u04/app/oracle/product/11.2.0.4/dbhome_1)
      (SID_NAME = SRCEM11)
    )
  )

-------------------------------------------------------------------

LISTENER_TRGEM19 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1528))
    )
  )

SID_LIST_LISTENER_TRGEM19 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TRGEM19)
      (ORACLE_HOME = /u02/app/oracle/product/19.0.0.0/dbhome_1)
      (SID_NAME = TRGEM19)
    )
  )

 

2) tnsnames.ora - source, target / oracle

OGG_SRCEM11 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1528))
    )
    (CONNECT_DATA =
      (SERVER=DEDICATED)
      (SERVICE_NAME = SRCEM)
      (LOAD_BALANCE=OFF)(FAILOVER=ON)
    )
  )
-------------------------------------------------------------------
OGG_TRGEM19 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ip)(PORT = 1528))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TRGEM19)
    )
  )

 

3) DB OGG User Create - source, target / oracle

create tablespace TS_OGGMIG datafile '+DATA' size 100M  autoextend on next 10m maxsize 30g;
Create user OGGMIG identified by welcome1 default tablespace TS_OGGMIG ; 

--권한부여 ( SYS 로 권한 부여 )
GRANT CREATE SESSION TO OGGMIG ;
GRANT ALTER SESSION TO OGGMIG ;
GRANT CONNECT, RESOURCE TO OGGMIG ;
GRANT ALTER ANY TABLE TO OGGMIG ;
GRANT ALTER SYSTEM TO OGGMIG ;
GRANT INSERT ANY TABLE TO OGGMIG ;
GRANT UPDATE ANY TABLE TO OGGMIG ;
GRANT DELETE ANY TABLE TO OGGMIG ;
GRANT CREATE TABLE TO OGGMIG ;
GRANT LOCK ANY TABLE TO OGGMIG ;
GRANT SELECT ANY TRANSACTION TO OGGMIG ;
GRANT SELECT ANY DICTIONARY TO OGGMIG ;
GRANT FLASHBACK ANY TABLE TO OGGMIG ;
GRANT SELECT ON DBA_CLUSTERS TO OGGMIG ;
GRANT EXECUTE ON DBMS_FLASHBACK TO OGGMIG ;
GRANT SELECT ANY TABLE TO OGGMIG ;
GRANT DBA TO OGGMIG;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('OGGMIG'); 
GRANT EXECUTE ON utl_file TO OGGMIG ;
GRANT EXECUTE ON DBMS_XSTREAM_GG TO OGGMIG ;

begin DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'OGGMIG', privilege_type => '*', grant_select_privileges=> true, do_grants => TRUE); 
End;
/

 

4) Archive log Mode 확인  - source, target / oracle

archive log list ;

-- no archivelog mode 인 경우 진행
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

 

https://docs.oracle.com/en/middleware/goldengate/core/19.1/oracle-db/preparing-database-oracle-goldengate.html#GUID-9FFB0F47-A681-4EC8-8E2E-4A03B451D916

Using Oracle GoldenGate for Oracle Database

5) supplemental log 설정 - source, target(양방향시에만) / oracle

* Supplemental Logging : redo log file에 추가적인 column을 기록하도록 설정하는 것

미설정 : 변경된 컬럼, rowid 기록

설정 : 변경된 컬럼의 모든 record 기록

 

DBA_LOG_GROUPS (Dictionary View)

LOG_GROUP_NAME	ALTER TABLE로 SUPPLEMENTAL LOGGING 걸때 생성되는 로그 그룹 이름
GENERATED	GENERATED NAME: LOG_GROUP_NAME이 시스템에서 생성됐을 때 USER NAME : 아닐때
ALWAYS	ALWAYS LOG GROUP : 행이 업데이트될 때마다 지정된 컬럼의 이전 이미지가 기록 CONDITIONAL LOG GROUP : 지정된 컬럼이 업데이트 될 때 이전 이미지가 기록
LOG_GROUP_TYPE	PK,UK,FK,ALL,USER_LOG_GROUP

• Table level : 특정 테이블만 활성화
• Database level  : DB 전체적으로 활성화 -> Redo log 발생량이 많아짐을 고려
• minimal logging : log miner가 sql을 재생성하는데 필요한 최소한의 정보만 기록

# Database 전체에 적용
-- DB 전체 테이블의 전체 컬럼 Logging
SQL>alter database add supplemental log data (ALL) columns;
-- DB 전체 테이블의 PK 구성 컬럼 Logging
SQL>alter database add supplemental log data (PRIMARY KEY) columns; 

# 특정 Table 지정하여 적용
alter table OWNER.TABLE_NAME add supplemental log data (all) columns; 
alter table OWNER.TABLE_NAME add supplemental log data (primary key) columns; 
alter table OWNER.TABLE_NAME add supplemental log data (unique) columns;

 

-> 나는 이관 대상 테이블만 특정 Table 지정으로 걸었음

 

[ 조회 ]

SELECT supplemental_log_data_min "Minimum",
supplemental_log_data_pk  "Primary key",
supplemental_log_data_ui  "Unique Key",
supplemental_log_data_fk  "Foregin Key",
supplemental_log_data_all "All",
FORCE_LOGGING 
FROM v$database;
--
select *
from DBA_LOG_GROUPS; 
--
select *
from DBA_LOG_GROUP_COLUMNS;

 

6) Force logging on - source, target(양방향시에만) / oracle

- 강제 Redo Log 생성하는 것으로 , 사용자가 nologging 옵션으로 작업하는 경우에도 리두 생성함.

ADG에서는 필수, OGG에서는 옵션 사항이나 docs에서는 켜는 것을 권고

alter database force logging;
# ALTER DATABASE DISABLE FORCE NOLOGGING;
alter system switch logfile ;

-- 조회 
SELECT FORCE_LOGGING
  FROM V$DATABASE;

 

7) Enable Goldengate Replicate Parameter - source, target / oracle

- Redo Log File은 변경사항을 물리적으로 적용하도록 설계되었기 때문에 변경내역을 SQL로 변환할 수 있는 충분한 정보가 없다 -> Supplemental Logging 설정을 하면 가능하게 됨.
해당 파라미터는 모든 데이터 유형과 작업에 필요한 Supplemental Logging을 사용하기 위해 enable로 설정해야한다 (권고)

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';

 

8) Streams Pool Size 설정 - source, target / oracle

- streams pool : ORALCE SGA 중 일부분 / ogg, advanced queuing, datapump 등에서 사용하는 메모리

• intergrated Replicat을 사용하는 경우 streams pool을 반드시 설정해야 함 (non-intergrated는 X)
  extract, replicat process가 streams pool 메모리를 사용할 수 있도록 등록
• 산정 방법

Set the STREAMS_POOL_SIZE initialization parameter for the database to the following value:
(1GB * # of integrated Replicats) + 25% head room
For example, on a system with three integrated Replicat process the calculation would be as follows:
(1GB * 3) * 1.25 = 3.75GB STREAMS_POOL_SIZE = 3750M

 

alter system set streams_pool_size=4G scope=both sid='*';

 

 

3. Installation Oracle Golden Gate

1) 사용 Directory 생성 - source, target / oggmig

# source
mkdir -p /u04/app/oggmig/ogg191
mkdir -p /u04/app/oggmig/oraInventory
chown -R oggmig:oinstall /u04/app/oggmig
chmod -R 775 /u04/app/oggmig
# target
mkdir -p /u02/app/oggmig/ogg191
mkdir -p /u02/app/oggmig/oraInventory
chown -R oggmig:oinstall /u02/app/oggmig
chmod -R 775 /u02/app/oggmig

 

2) Engine Unzip, rsp file, loc file 작성  - source, target / oggmig

cd /NFS/EMLEE/99.media/ogg/19
unzip /NFS/EMLEE/99.media/ogg/19/191004_fbo_ggs_Linux_x64_shiphome.zip

# rsp file 작성 (Silent용)
cd /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response
## source
cp oggcore.rsp oggcore_11g_src.rsp
vi oggcore_11g_src.rsp
-- SOFTWARE_LOCATION=/u04/app/oggmig/ogg191
-- INVENTORY_LOCATION=/u04/app/oggmig/oraInventory
## target
cp oggcore.rsp oggcore_19c_trg.rsp
vi oggcore_19c_trg.rsp
-- SOFTWARE_LOCATION=/u02/app/oggmig/ogg191
-- INVENTORY_LOCATION=/u02/app/oggmig/oraInventory

# loc file 작성
cd /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response
## source
vi oraInst_src.loc

inventory_loc=/u04/app/oggmig/oraInventory
inst_group=oinstall

##target
vi oraInst_trg.loc

inventory_loc=/u02/app/oggmig/oraInventory
inst_group=oinstall

 

3) Core (Silent Mode) 설치 - source, target / oggmig

cd /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1

-- src
./runInstaller -silent -responseFile /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore_11g_src.rsp \
 -invPtrLoc /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response/oraInst_src.loc

-- trg 
./runInstaller -silent -responseFile /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore_19c_trg.rsp \
 -invPtrLoc /NFS/EMLEE/99.media/ogg/19/fbo_ggs_Linux_x64_shiphome/Disk1/response/oraInst_trg.loc

 

4) 실행 및 subdirectory 생성 - source, target / oggmig

$ ggsci
GGSCI> CREATE SUBDIRS

 

 

4. OGG Configuration

1) .GLOBALS Parameter 설정 - source, target / oggmig

edit params ./GLOBALS
-----------------------------------
CHECKPOINTTABLE OGGMIG.CHKPT
-----------------------------------
## 그 외 parameter
SYSLOG NONE # OGG 모든 로그에 대해 SYSTEM LOG (/var/log)를 생성하지 않도록 함
ENABLEMONITORING # OGG 모니터링 API 사용 설정을 위한 PARAMETER , OGG Management Pack License가 별도 필요
ENABLE_HEARTBEAT_TABLE
HEARTBEATTABLE GG_HEARTBEAT # OGG 12.2 NEW FEATURE , HEARTBEATTABLE 사용

 

2) DB 관련 Module 생성 - source, target / oggmig

DB에 OGG 사용 유저로 로그인하여 CHECKPOINTTABLE을 생성하는 작업
HEARTBEATTABLE을 이용하는 경우 add HeartBeatTable 과정을 source,target에서 별도로 진행해야 함

GGSCI> DBLOGIN USERID OGGMIG, PASSWORD welcome1
GGSCI> ADD CHECKPOINTTABLE OGGMIG.CHKPT
info CHECKPOINTTABLE

 

3) Manager Parameter 설정 - source, target / oggmig

edit param mgr

PORT 7840
DYNAMICPORTLIST 7841-7845

ACCESSRULE, PROG *, IPADDR *, ALLOW

--PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPFILES 10

LAGINFOSECONDS 10
LAGCRITICALSECONDS 10
LAGREPORTMINUTES 1

-----------------------------------
start mgr
info mgr
-----------------------------------
- Manager Process : OGG_HOME의 모든 Process를 관리하는 Process

## 그 외 parameter
purgeddlhistory minkeepdays 3, maxkeepdays 5, frequencyminutes 30 / purgemarkerhistory minkeepdays 3, maxkeepdays 5, frequencyminutes 30
 # ddlhistory table 및 markerhistory table의 Purge 주기 설정, 11.2이상의 Intergrated Capture 사용의 경우 필요없는 설정
AUTOSTART ER * 
AUTORESTART ER *, RETRIES 5, WAITMINUTES 2, RESETMINUTES 120
 # 관리되는 모든 Process에 대해 자동으로 Start하거나 비정상종료 시 자동 재실행 설정

 

4) Character Set 확인 - source, target / oggmig

간혹 다를경우 지정 안해주면 오류나는 경우가 있음

-- extract prm file에는 source db charset
-- replicat prm file에는 target db charset

col PARAMETER for a30
col value for a30
select * from nls_database_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');
--> 
SETENV (NLS_LANG='[NLS_LANGUAGE]_[NLS_TERRITORY].[NLS_CHARACTERSET]')

 

5) Integrated Extract Parameter 설정 - source / oggmig

EXTRACT esrc11
--
SETENV (ORACLE_SID=SRCEM11)
SETENV (ORACLE_HOME=/u04/app/oracle/product/11.2.0.4/dbhome_1)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--
USERID oggmig@OGG_SRCEM11, PASSWORD welcome1
--
CACHEMGR CACHESIZE 10G
BR BRINTERVAL 1H
DBOPTIONS ALLOWUNUSEDCOLUMN
--
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 3072)
TRANLOGOPTIONS INTEGRATEDPARAMS (parallelism 1)
--
DDL INCLUDE MAPPED
--
--EOFDELAYCSECS 10
--FLUSHCSECS    10
--NOCOMPRESSDELETES
--
STATOPTIONS RESETREPORTSTATS, REPORTFETCH
DISCARDFILE ./dirout/esrc11.dsc, APPEND, MEGABYTES 2000
REPORTCOUNT EVERY 10 MINUTES, RATE
REPORT         AT 00:00
REPORTROLLOVER AT 00:01
EXTTRAIL ./dirdat/ec

-- TABLE
--example TABLE SCHEMA_NAME.TABLE_NAME  ;

 

6) Extract Process 등록 - source / oggmig

DBLOGIN USERID OGGMIG@OGG_SRCEM11, PASSWORD welcome1
REGISTER EXTRACT esrc11 DATABASE
ADD EXTRACT esrc11, INTEGRATED TRANLOG,  BEGIN NOW
ADD EXTTRAIL ./dirdat/ec, extract esrc11, megabytes 1024

start esrc11

 

7) Pump Extract Parameter 설정 - source / oggmig

EXTRACT psrc11

USERID OGGMIG@OGG_SRCEM11, PASSWORD welcome1

RMTHOST [ip], MGRPORT 7840
RMTTRAIL dirdat/rt
PASSTHRU

NOTCPSOURCETIMER

TABLE SCHEMA.* ;

 

8) Pump Process 등록 - source / oggmig

DBLOGIN USERID OGGMIG@OGG_SRCEM11, PASSWORD welcome1
register extract psrc11 database 
add extract psrc11, exttrailsource ./dirdat/ec
add rmttrail /u02/app/oggmig/ogg191/dirdat/rt, extract psrc11, megabytes 2000

start psrc11

 

동기화 시작 시점 확인

-> Source DB에서 동기화시킬 시점의 SCN을 확인

SQL> select current_scn from v$database ;

         CURRENT_SCN
--------------------
      11056234997641

 

-> 또는 Datapump 등 초기적재 시 사용한 시점의 SCN 확인 (flashback_scn parameter 사용)

expdp ~ ... flashback_scn=11056234997641

 

9) Replicat Parameter 설정 - target / oggmig

REPLICAT rep01
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
--
USERID oggmig@OGG_TRGEM19, PASSWORD welcome1
--
--ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
--
DDL INCLUDE MAPPED
DDLERROR DEFAULT ABEND
--
ALLOWNOOPUPDATES
--
--EOFDELAYCSECS 10
STATOPTIONS RESETREPORTSTATS, REPORTDETAIL
--
DISCARDFILE ./dirrpt/rep01.dsc, APPEND, MEGABYTES 2000
--
report at 00:00
reportrollover at 00:01
REPORTCOUNT EVERY 1 HOURS, RATE
-- MAP/TARGET LIST
MAP "SCHEMA"."TABLE1" , TARGET "SCHEMA"."TABLE1" , COLMAP(USEDEFAULTS);
MAP "SCHEMA"."TABLE2" , TARGET "SCHEMA"."TABLE2" , COLMAP(USEDEFAULTS);
...

 

10) Replicat Process 등록 - target / oggmig

- aftercsn에 확인했던 동기화 시점의 scn을 기록

dblogin USERID oggmig@OGG_TRGEM19  PASSWORD welcome1
add replicat rep01  exttrail ./dirdat/rt, checkpointtable OGGMIG.CHKPT
START REPLICAT rast01 , aftercsn 11056234997641

 

 

동기화 확인

[ DDL 발생 ]

# source
SQL> CREATE TABLE TEST.LAST_TAB 
( COL1 timestamp,
 COL2 VARCHAR2(10),
 COL3 NUMBER
 );
 
ALTER TABLE TEST.LAST_TAB ADD Supplemental log data ( ALL  ) COLUMNS ; 

# target OGG
stop rep01
edit params rep01

MAP "TEST"."LAST_TAB" , TARGET "TEST"."LAST_TAB" , COLMAP(USEDEFAULTS);

start rep01

-- source
SQL>INSERT INTO TEST.LAST_TAB 
SELECT SYSDATE,
       'after mig',
       CURRENT_SCN
  FROM V$DATABASE;

COMMIT;  


* target 에서 데이터 확인
SELECT * FROM TEST.LAST_TAB;