Oracle Goldengate ArchiveLogOnly(ALO) Mode

Archivelog only mode

 

GoldenGate Extract Archived Log Only (ALO) Mode Template Best Practices (Doc ID 1482439.1)

redolog에서 읽는 것이 아닌 아카이브 로그에서만 읽도록 extract를 구성 (ALO MODE)

source database에 최소한의 영향/아예 무영향을 주도록 하는 방안 (Source Database Overhead 최소화)

Oracle Data Guard 전환 / 페일오버 전략

온라인 로그는 사용되지 않음

 

제약사항

• Standby 구성에서 사용하는 경우
• RAC, SI 둘다 됨
• integrated 안됨
• OGG 10.4 미만은 안됨
• OGG 12c 이전 버전에서는 ASM에 존재하는 아카이브로그 파일은 사용할 수 없고 외부에 있어야함

Extract Parameter

TRANLOGOPTIONS ARCHIVEDLOGONLY

 

Requirements

1.RAC에서 사용하는 경우

• 소스 서버에 dedicated connection이 필요 이 연결이 끊어지면 OGG Process가 주금
• archive log directory는 모든 노드에서 unique name을 가져야함 -> 아니면 out of order SCN 에러 날수있음
• 하나 이상의 RAC 인스턴스가 idle 상태인 경우 idle 노드에서 아카이브 로그 스위치 해야함

archive_lag_target 파라미터를 설정하여 사전 설정된 각ㄴ격으로 스위치 될 수 있도록 함

ex) alter system set archive_lag_target 900; (초단위)

 

alter system set archive_lag_target=900 scope=both;

2. LOGRETENTION Extract parameter는 ALO 모드일때 기본적으로 DISABLE 됨. Classic Extract에서는 활성화

 

Configuration

테스트 환경 : DG에서 ALO MODE로 추출

1. supplemental logging : table level / database level in source
2. forcelogging

3. user create

grant resource, dba, connect to SOE identified by soe ;

exec dbms_streams_auth.grant_admin_privilege('SOE');

grant become user to SOE;

 

4. tnsnames

SRCDB11 =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = srcdb11)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = srcdb11)     )   ) LISTENER_SRCDB11 =   (ADDRESS = (PROTOCOL = TCP)(HOST = srcdb11)(PORT = 1521)) SRCADG11 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xxx)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = srcadg11)     )   )

5. dblogin/add trandata

 

DBLOGIN USERID DBMGR, PASSWORD welcome1

DBLOGIN USERID DBMGR@srcdb11, PASSWORD welcome1

ADD SCHEMATRANDATA SOE

INFO SCHEMATRANDATA SOE

 

6. TRANLOGOPTIONS ARCHIVEDLOGONLY

extract
ALO 모드는 등록할때 지금 환경변수는 스탠바이를 보되 OGG 추출하기위해 바라볼 등록 접속 DB는 PRIMARY임
EXTRACT esrcdb
--SETENV (ORACLE_SID=srcdb11)
--SETENV (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1)
USERID DBMGR@SRCDB11, PASSWORD welcome1
--
CACHEMGR CACHESIZE 8G
BR BRINTERVAL 1H
DBOPTIONS ALLOWUNUSEDCOLUMN
--
TRANLOGOPTIONS ARCHIVEDLOGONLY
TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE srcdb11 /u01/app/oracle/arch1

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.*;
DBLOGIN USERID DBMGR@SRCDB11, PASSWORD welcome1
add EXTRACT esrcdb ,tranlog, BEGIN NOW
ADD EXTTRAIL ./dirdat/ee, extract esrcdb, megabytes 1024
start esrcdb

 

Extract automatically operates in ALO mode if it detects that the database is a physical standby. (STANDBY 상태라고 감지되면 자동으로 ALO MODE가 됨)

 

2025-02-18 10:17:41  WARNING OGG-00727  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Switch extract to archived log only mode on physical standby database.

2025-02-18 10:17:41  WARNING OGG-01830  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  LOGRETENTION is disabled by default in ARCHIVEDLOGONLY mode.

2025-02-18 10:17:41  INFO    OGG-02089  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Source redo compatibility version is: 11.2.0.0.0.

2025-02-18 10:17:41  INFO    OGG-00546  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Default thread stack size: 8388608.

2025-02-18 10:17:41  INFO    OGG-01515  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Positioning to begin time 2025 M02 18 10:06:49.

2025-02-18 10:17:41  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  EXTRACT ESRCDB started.

2025-02-18 10:17:41  INFO    OGG-01056  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Recovery initialization completed for target file ./dirdat/ee000000000, at RBA 53086154, CSN 14556831527901.

2025-02-18 10:17:41  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Output file ./dirdat/ee is using format RELEASE 12.2.

2025-02-18 10:17:41  WARNING OGG-01438  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Checkpoint marked as from graceful shutdown, but records found after checkpoint in trail ./dirdat/ee.  Expected EOF Seqno 0, RBA 0.  Found Seqno 0, RBA 53086154.

2025-02-18 10:17:41  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, esrcdb.prm:  Rolling over remote file ./dirdat/ee000000000.

2025-02-18 10:17:42  WARNING OGG-00947  Oracle GoldenGate Manager for Oracle, mgr.prm:  Lag for EXTRACT ESRCDB is 00:10:52 (checkpoint updated 00:00:00 ago).

 

info esrcdb, showch

GGSCI (srcadg11 as DBMGR@srcdb11) 59> info esrcdb, showch

EXTRACT    ESRCDB    Last Started 2025-02-18 14:35   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:07 ago)
Process ID           32526
Log Read Checkpoint  Oracle Redo Logs
                     2025-02-18 14:33:06  Seqno 799, RBA 84480
                     SCN 3389.1187388672 (14556831554816)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Redo Log

  Startup Checkpoint (starting position in the data source):
    Thread #: 1
    Sequence #: 799
    RBA: 78864
    Timestamp: 2025-02-18 14:32:56.000000
    SCN: 3389.1187388662 (14556831554806)
    Redo File: /u01/app/oracle/arch1/1_799_1165855696.dbf

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Thread #: 1
    Sequence #: 799
    RBA: 78864
    Timestamp: 2025-02-18 14:32:56.000000
    SCN: 3389.1187388662 (14556831554806)
    Redo File: /u01/app/oracle/arch1/1_799_1165855696.dbf

  Current Checkpoint (position of last record read in the data source):
    Thread #: 1
    Sequence #: 799
    RBA: 84480
    Timestamp: 2025-02-18 14:33:06.000000
    SCN: 3389.1187388672 (14556831554816)
    Redo File: /u01/app/oracle/arch1/1_799_1165855696.dbf

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 8
    RBA: 1466
    Timestamp: 2025-02-18 14:40:57.575089
    Extract Trail: ./dirdat/ee
    Seqno Length: 9
    Flip Seqno Length: No
    Trail Type: EXTTRAIL

Header:
  Version = 2
  Record Source = A
  Type = 10
  # Input Checkpoints = 1
  # Output Checkpoints = 1

File Information:
  Block Size = 2048
  Max Blocks = 100
  Record Length = 2048
  Current Offset = 0

Configuration:
  Data Source = 3
  Transaction Integrity = 1
  Task Type = 0

Status:
  Start Time = 2025-02-18 14:35:43
  Last Update Time = 2025-02-18 14:40:57
  Stop Status = A
  Last Result = 400

 

COMPLETEARCHIVEDLOGONLY : 완전히 아카이브로그가 옮겨지고 나서 복제 시작 / 원래는 옮겨지기 시작할 때 부터 읽음

 

PUMP

pump

EXTRACT psrcdb

USERID DBMGR@srcdb11, PASSWORD welcome1

RMTHOST 192.168.0.XXX, MGRPORT 7840
RMTTRAIL /home/ogg/app/product/dirdat/ee

TABLE SOE.* ;


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

start psrcdb


info esrcdb detail
info psrcdb detail
send esrcdb status

 

REPLICAT

replicat
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
reperror(1403 discard)
MAP SOE.*, TARGET SOE.* ;


dblogin USERID DBMGR  PASSWORD welcome1

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

START REPLICAT rtrgdb aftercsn 14556831548838

 

 

 

동기화 테스트

DDL TEST (SOURCE)

 

DG

로그스위치 안돼서 동기화 안되고있음

 

동기화 안된거 맞음

 

log switch

 

 

 

로그스위치 하니까 테이블 생성 동기화 됨

 

DML TEST

 

마찬가지로 LOG SWITCH 날리면 동기화 됨