Oracle Goldengate core Install & ADG Mode

 

Oracle Goldengate ADG mode

• Classic 추출만 가능
• ADD SCHEMATRANDATA
• Minimal supplemental logging
• DDL capture
• standby db에서 extract paramer에 TRANLOGOPTIONS MINEFROMACTIVEDG 사용
  • 이 옵션은 v$database의 db_role 의 값이 primary냐 standby냐에 따라 extract를 adg모드로 작동하도록 하는 파라미터
• Standby DB에 적용된 데이터만 추출
• Primary databas랑 redo thread 수가 동일해야 함
• extract 추가 후 인스턴스를 추가할 수 없음
• DDL Trigger는 Primary에 적용
• OGG 10gR2 이상에서 지원
• DBLOGREADER 지원하지 않음

-------------------------------------
-- 사전작업 / SOURCE 
-------------------------------------
-- supplemental

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
alter database force logging;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';

-------------------------------------
-- 설치
-------------------------------------
-- ======================================================
-- 1. OS user - DBMGR
-- ======================================================

-- source 
/usr/sbin/useradd -g oinstall -G dba,asmdba,asmadmin,backupdba,dgdba,kmdba,racdba,asmoper,vboxsf ogg

-- OGG_HOME
mkdir -p /home/ogg/app/product
chown -R ogg:oinstall /home/ogg/app

-- dir
mkdir -p /acfs/ogg/oggdir/dir*
chown -R ogg:oinstall /acfs/ogg

-- source
-- target 
useradd -g oinstall -G dba,vboxsf ogg
passwd welcome1

-- OGG_HOME
mkdir -p /home/ogg/app/product
chown -R ogg:oinstall /home/ogg/app

 

-- ****************************************************************
-- ** tnsnames.ora / SOURCE, TARGET - oracle
-- ****************************************************************
OGG_SRCADG11 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER=DEDICATED)
      (SERVICE_NAME = srcadg11)
      (LOAD_BALANCE=OFF)(FAILOVER=ON)
    )
  )
-------------------------------------------------------------------
OGG_TRGDB19 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = trgdb19)
    )
  )

 

-- ======================================================
-- 2. DB user
-- ======================================================
## DBMGR/welcome1

create tablespace TS_DBMGR datafile '/u01/app/oracle/oradata/TRGDB19/datafile/ts_dbmgr.dbf' size 100M  autoextend on;
create tablespace TS_DBMGR datafile '+GRP_DATA' size 100M  autoextend on next 10m maxsize 30g;
Create user DBMGR identified by welcome1 default tablespace TS_DBMGR ;

--권한부여 ( SYS 로 권한 부여 )

GRANT CREATE SESSION TO DBMGR ;
GRANT ALTER SESSION TO DBMGR ;
GRANT CONNECT, RESOURCE TO DBMGR ;
GRANT ALTER ANY TABLE TO DBMGR ;
GRANT ALTER SYSTEM TO DBMGR ;
GRANT INSERT ANY TABLE TO DBMGR ;
GRANT UPDATE ANY TABLE TO DBMGR ;
GRANT DELETE ANY TABLE TO DBMGR ;
GRANT CREATE TABLE TO DBMGR ;
GRANT LOCK ANY TABLE TO DBMGR ;
GRANT SELECT ANY TRANSACTION TO DBMGR ;
GRANT SELECT ANY DICTIONARY TO DBMGR ;
GRANT FLASHBACK ANY TABLE TO DBMGR ;
GRANT SELECT ON DBA_CLUSTERS TO DBMGR ;
GRANT EXECUTE ON DBMS_FLASHBACK TO DBMGR ;
GRANT SELECT ANY TABLE TO DBMGR ;
GRANT DBA TO DBMGR;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE ('DBMGR'); 
GRANT EXECUTE ON utl_file TO DBMGR ;
GRANT EXECUTE ON DBMS_XSTREAM_GG TO DBMGR ;

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

-- ======================================================
-- 3. Engine Install 
-- ======================================================
-- -------------------------------------------------
-- Inst , Response File 작성 - DBMGR
-- -------------------------------------------------
-- source
unzip /media/sf_01.oracle/5.OGG/12.2/12201_fbo_ggs_Linux_x64_shiphome.zip -d $OGG_HOME

cd /home/ogg/app/product/fbo_ggs_Linux_x64_shiphome/Disk1/response
cp oggcore.rsp oggcore_11g_src.rsp
vi oggcore_11g_src.rsp
/u01/app/oraInventory


-- target
unzip /media/sf_01.oracle/5.OGG/19/191004_fbo_ggs_Linux_x64_shiphome.zip -d /home/ogg/app/product
cd /home/ogg/app/product
cd /home/ogg/app/product/fbo_ggs_Linux_x64_shiphome/Disk1/response
cp oggcore.rsp oggcore_19c_trg.rsp

vi oggcore_19c_trg.rsp
-- SOFTWARE_LOCATION=/home/ogg/app/product
-- INVENTORY_LOCATION=/u01/app/oraInventory

 

-- -------------------------------------------------
-- Core 설치
-- -------------------------------------------------
cd /home/ogg/app/product/fbo_ggs_Linux_x64_shiphome/Disk1

tar -xvf jdk-21_linux-x64_bin.tar.gz /media
export JAVA_HOME=/home/ogg/jdk-21.0.2
export JRE_HOME=$JAVA_HOME/jre

-- src
./runInstaller -silent -responseFile /home/ogg/app/product/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore_11g_src.rsp \
 -invPtrLoc /u01/app/oraInventory/oraInst.loc
 
 
 
export PATH=$PATH:$ORACLE_HOME/bin:$OGG_HOME/OPatch:$OGG_HOME:$JAVA_HOME/bin:$JRE_HOME/bin

-- trg 
./runInstaller -silent -responseFile /home/ogg/app/product/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore_19c_trg.rsp \
 -invPtrLoc /u01/app/oraInventory/oraInst.loc


-- -------------------------------------------------
-- Patch
-- -------------------------------------------------
-- TRG
mv /home/ogg/app/product/OPatch /home/ogg/app/product/OPatch_old
cd /media/sf_01.oracle/4.Opatch/1.opatch
unzip 45.p6880880_190000_Linux-x86-64.zip -d /home/ogg/app/product

export ORACLE_HOME=$OGG_HOME
export LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0.0/dbhome_1/lib

unzip p36798117_1924000OGGRU_Linux-x86-64.zip -d $OGG_HOME

/home/ogg/app/product/OPatch/opatch apply -oh /home/ogg/app/product -local /home/ogg/app/product/36798117


/u04/app/DBMGR/ogg191/OPatch/opatch rollback -id 26849940 -oh /u04/app/DBMGR/ogg191 -ph /NFS/EMLEE/99.media/ogg/12/26849940 -local
/u02/app/oggtrg/ogg191/OPatch/opatch rollback -id 35326279 -oh /u02/app/oggtrg/ogg191 -ph /db_sync/SYNC/ugens/media/35326279 -local


ogg@trgdb19:/home/ogg/app/product/36798117# opatch lspatches
36798117;


OPatch succeeded.

 

-- ======================================================
-- 5. global, mgr parameter - DBMGR
-- ======================================================
-- -------------------------------------------------
-- GLOBAL parameter 
-- -------------------------------------------------

cd $OGG_HOME

./ggsci

edit params ./GLOBALS

GGSCHEMA DBMGR
CHECKPOINTTABLE DBMGR.CHKPT


-- -------------------------------------------------
-- Manager parameter 
-- -------------------------------------------------
edit param mgr

PORT 7840
DYNAMICPORTLIST 7841-7845

ACCESSRULE, PROG *, IPADDR *, ALLOW

LAGINFOSECONDS 10
LAGCRITICALSECONDS 10
LAGREPORTMINUTES 1

 

-- ======================================================
-- 6. db 연동 - src, trg
-- ======================================================

-- -------------------------------------------------
-- Checkpoint Table 생성 
-- -------------------------------------------------
GGSCI> DBLOGIN USERID DBMGR@SRCDB11, PASSWORD welcome1
GGSCI> ADD CHECKPOINTTABLE DBMGR.CHKPT

-- trandata
-- ggsci에서 primary db로 @접속해서 add trandata 해야함
exec dbms_streams_auth.grant_admin_privilege('dbmgr');

dblogin userid dbmgr@SRCDB11 password welcome1
ADD SCHEMATRANDATA SOE
INFO SCHEMATRANDATA SOE

2025-02-17 14:20:50  INFO    OGG-01788  SCHEMATRANDATA has been added on schema SOE.

2025-02-17 14:20:50  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema SOE.

GGSCI (srcadg11 as dbmgr@srcdb11) 6>

2025-02-17 14:20:51  INFO    OGG-01785  Schema level supplemental logging is enabled on schema SOE.

2025-02-17 14:20:51  INFO    OGG-01980  Schema level supplemental logging is enabled on schema SOE for all scheduling columns.

2025-02-17 14:20:51  INFO    OGG-10462  Schema SOE have 11 prepared tables for instantiation.

 

-- =====================

-- OGG DDL TRIGGER

-- =====================
edit params ./GLOBALS
GGSSCHEMA DBMGR

cd /home/ogg/app/product
sqlplus sys/welcome1@SRCDB11 as sysdba
GRANT EXECUTE ON utl_file TO DBMGR;
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
grant ggs_ggsuser_role to dbmgr;
@ddl_enable.sql
@ddl_status.sql

 

 

-- ======================================================
-- 7. EXTRACT , PUMP 등록 - src
-- ======================================================
** EXTRACT PARAMETER
EXTRACT esrcdb
SETENV (ORACLE_SID=srcadg11)
SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1)
USERID DBMGR, PASSWORD welcome1
--
CACHEMGR CACHESIZE 8G
BR BRINTERVAL 1H
DBOPTIONS ALLOWUNUSEDCOLUMN
--
TRANLOGOPTIONS MINEFROMACTIVEDG
EXTTRAIL ./dirdat/ee
--
STATOPTIONS RESETREPORTSTATS, REPORTFETCH
DISCARDFILE ./dirout/esrcdb.dsc, APPEND, MEGABYTES 2000
REPORTCOUNT EVERY 10 MINUTES, RATE
REPORT         AT 00:00
REPORTROLLOVER AT 00:01
--
TABLE SOE.*;

** PUMP PARAMETER
EXTRACT psrcdb

USERID dbmgr@OGG_SRCADG11, PASSWORD welcome1

RMTHOST 192.168.0.51, MGRPORT 7840
RMTTRAIL dirdat/ee
PASSTHRU

NOTCPSOURCETIMER

TABLE SOE.* ;

-- -------------------------------------------------
-- esrcadg EXTRACT -> trgdb19
-- -------------------------------------------------
-- extract 
-- ggsci에서 standby db로 접속해서 등록해야함.
DBLOGIN USERID DBMGR@OGG_SRCADG11, PASSWORD welcome1
add EXTRACT esrcdb ,tranlog, BEGIN NOW

ADD EXTTRAIL ./dirdat/ee, extract esrcdb, megabytes 1024
start esrcdb

-- pump
DBLOGIN USERID DBMGR@OGG_SRCADG11, PASSWORD welcome1
add extract psrcdb, exttrailsource ./dirdat/ee
add rmttrail /home/ogg/app/product/dirdat/ee, extract psrcdb, megabytes 2000

start psrcdb

-- REGISTER 이후 각노드의 REDO SWHITCH 필요..
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM CHECKPOINT;
 

REPLICAT rtrgdb
--
SETENV (ORACLE_SID = 'trgdb19')
SETENV (ORACLE_HOME = '/u01/app/oracle/product/19.0.0.0/dbhome_1')
USERID DBMGR, PASSWORD welcome1
--
--ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
--
DDL INCLUDE MAPPED
DDLERROR DEFAULT ABEND
--
ALLOWNOOPUPDATES
--
--EOFDELAYCSECS 10
STATOPTIONS RESETREPORTSTATS, REPORTDETAIL
--
DISCARDFILE ./dirrpt/rtrgdb.dsc, APPEND, MEGABYTES 2000
--
report at 00:00
reportrollover at 00:01
REPORTCOUNT EVERY 1 HOURS, RATE
-- MAP/TARGET LIST
MAP SOE.*, TARGET SOE.* ;


-- ======================================================
-- 9. Replicat process 등록 - oggtrg
-- ======================================================
dblogin USERID DBMGR  PASSWORD welcome1

add replicat rtrgdb exttrail ./dirdat/ee, checkpointtable DBMGR.CHKPT

START REPLICAT rtrgdb 

-- ======================================================
-- 10. 동기화 확인
-- ======================================================
* 동기화 확인을 위해 DDL 발생시킴
-- source
drop table SOE.LAST_TAB purge ;
SQL> CREATE TABLE SOE.LAST_TAB 
( COL1 timestamp,
 COL2 VARCHAR2(10),
 COL3 NUMBER
 );
 
ALTER TABLE SOE.LAST_TAB ADD Supplemental log data ( ALL  ) COLUMNS ; 

-- source
SQL>INSERT INTO SOE.LAST_TAB 
SELECT SYSDATE,
       'TEST1',
       CURRENT_SCN
  FROM V$DATABASE;

COMMIT;  


* target 에서 데이터 확인
SELECT * FROM SOE.LAST_TAB;
alter system switch logfile ;