Oracle Golden Gate Configuration(단방향 DDL,DML)

 

1.OGG Configuration(단방향 DDL,DML)

1-1 Create and start Manager

OGG MGR 포트 및 Trail파일 설정(Source)     GGSCI (ocp.mycorpdomain.com) 2> dblogin userid oggmgr, password oracle Successfully logged into database.     GGSCI (ocp.mycorpdomain.com) 3> edit params mgr PORT 7809     userid oggmgr password oracle     PURGEOLDEXTRACTS ./dirdat/et*, USECHECKPOINTS, MINKEEPDAYS 3 PURGEOLDEXTRACTS ./dirdat/rt*, USECHECKPOINTS, MINKEEPDAYS 3 GGSCI (ocp.mycorpdomain.com) 4>start mgr     GGSCI (ocp.mycorpdomain.com) 5> info mgr     Manager is running (IP port ocp.mycorpdomain.com.7809).

OGG MGR 포트 및 Trail파일 설정(Target) GGSCI (ocp.mycorpdomain.com) 2> dblogin userid oggmgr, password oracle Successfully logged into database.     GGSCI (ocp.mycorpdomain.com) 5> edit params mgr     PORT 7810     PURGEOLDEXTRACTS ./dirdat/et*, USECHECKPOINTS, MINKEEPDAYS 3 PURGEOLDEXTRACTS ./dirdat/rt*, USECHECKPOINTS, MINKEEPDAYS 3 GGSCI (ocp.mycorpdomain.com) 7> start mgr     GGSCI (ocp.mycorpdomain.com) 7> info mgr     Manager is running (IP port ocp.mycorpdomain.com.7810).

   

1-2 Create extract/Data-pump group(Source)

EXTRACT GGSCI (ocp.mycorpdomain.com) 8> add extract extora, tranlog, begin now     GGSCI (ocp.mycorpdomain.com) 9> edit params EXTORA -- -- Extract Parameter File -- EXTRACT EXTORA USERID oggmgr, PASSWORD oracle     TRANLOGOPTIONS OPENARCHIVEIMMEDIATE THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000 TRANLOGOPTIONS EXCLUDEUSER OGGMGR, EXCLUDEUSERID 54     EXTTRAIL ./dirdat/et REPORTCOUNT EVERY 5 MINUTES, RATE     -- Use DDL parameter to configure support for all mapped DDL operations DDL INCLUDE MAPPED DDLOPTIONS ADDTRANDATA, REPORT     -- Table/Sequence List TABLE SCOTT.*; SEQUENCE SCOTT.*;     GGSCI (ocp.mycorpdomain.com) 10> add exttrail ./dirdat/et, extract extora

Data-PUMP GGSCI (ocp.mycorpdomain.com) 11>add extract pumpora, exttrailsource ./dirdat/et     GGSCI (ocp.mycorpdomain.com) 12> edit params PUMPA -- -- Data Pump Parameter File -- EXTRACT PUMPA PASSTHRU RMTHOST 192.168.22.10, MGRPORT 7810 RMTTRAIL ./dirdat/rt REPORTCOUNT EVERY 5 MINUTES, RATE     -- DDL Option --DDL INCLUDE MAPPED --DDLOPTIONS REPORT --DDLERROR DEFAULT ABEND     -- Table/Sequence List TABLE SCOTT.*; SEQUENCE SCOTT.*;     GGSCI (ocp.mycorpdomain.com) 13> add rmttrail ./diradat/rt, extract pumpora

[주의사항] - Trail File명이 혼돈하지 않고 적절하게 사용할 수 있도록 주의 해야 한다. (EXTRACT(./diradat/et) 파일을 기반으로 Data-PUMP(./diardata/rt)를 생성해야 함으로 파일 명을 혼돈하지 말고 사용할 것.

   

1-3 Create Chekpoint Table(Target)

GGSCI (ocp.mycorpdomain.com) 14> add checkpointtable oggmgr.ogg_checkpoint

Oggmgr 유저에 해당 테이블 생성 확인

   

1-4 Create Replicat Group(Target)

GGSCI (ocp.mycorpdomain.com) 15> add replicat repora, exttrail ./dirdat/rt, checkpointtable oggmgr.ogg_checkpoint     GGSCI (ocp.mycorpdomain.com) 16> edit params REPORA replicat repora     userid oggmgr, password oracle     assumetargetdefs     DDL INCLUDE MAPPED DDLOPTIONS REPORT DDLERROR DEFAULT ABEND     checkpointsecs 1     map scott.*, target scott.*;

[주의사항] -Replicat 설정시에도 Source DB의 Data-Pump를 이용해야 되기 때문에 추가시 파일 명을 일치시켜주어야 합니다.(./dirdat/rt)

   

1-5 OGG EXTRACT,PUMP,REPLICAT Start(Source / Target)

OGG EXTRACT,PUMP (Source)     GGSCI (ocp.mycorpdomain.com) 1> Start EXTRACT EXTORA GGSCI (ocp.mycorpdomain.com) 2> Start EXTRACT PUMPORA   GGSCI (ocp.mycorpdomain.com) 3> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING                                            EXTRACT     RUNNING     EXTORA      00:00:00      00:00:07     EXTRACT     RUNNING     PUMPORA     00:00:00      00:00:03     GGSCI (ocp.mycorpdomain.com) 2> info EXTRACT EXTORA     EXTRACT EXTORA Last Started 2013-04-07 22:22 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:02 ago) Log Read Checkpoint Oracle Redo Logs 2013-04-08 22:41:56 Seqno 31, RBA 8829440 SCN 0.1297938 (1297938)     GGSCI (ocp.mycorpdomain.com) 3> info EXTRACT PUMPORA     EXTRACT PUMPORA Last Started 2013-04-07 23:11 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint File ./dirdat/et000002 2013-04-07 23:43:11.000000 RBA 21412

OGG REPLICAT (Target)     GGSCI (ocp.mycorpdomain.com) 1> Start REPLICAT REPORA GGSCI (ocp.mycorpdomain.com) 2> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING                                            REPLICAT    RUNNING     REPORA     00:00:00      00:00:00     GGSCI (ocp.mycorpdomain.com) 4> info REPLICAT REPORA     REPLICAT REPORA Last Started 2013-04-07 22:36 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint File ./dirdat/rt000000 2013-04-07 23:43:10.988221 RBA 21512

   

1-6 OGG TEST Setting 확인

OGG-Checkpoint table(Target)     REPORA        4120505024        0        21512        2013-04-07 23:43:10.988221        2013-04-07 오후 10:26:50        2013-04-07 오후 11:43:16        /ogg        1282955        7.33.914        1282955        7.33.914        1

파일 생성 확인 --Source DB ocp@orcl : /ogg/dirdat> ls et000000 et000001 et000002 --Target DB ocp@orcl : /ogg/dirdat> ls rt000000

   

2.OGG TEST / 동작확인(DML / DDL)

   - 데이터를 동일하게 이관하여 TEST진행(SCOTT 유저의 기본 테이블을 이용하여 OGG TEST)

OGG TEST(DML/DDL)     --Source DB select * from scott.emp; delete from scott.emp;     SET DEFINE OFF; Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 800, 20); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) Values (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 300, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) Values (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 500, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2975, 20); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) Values (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1250, 1400, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2850, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2450, 10); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO) Values (7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 10); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) Values (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1500, 0, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1100, 20); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 950, 30); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3000, 20); Insert into SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO) Values (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1300, 10); COMMIT;     Alter table scott.emp add new_test char(10) Alter table scott.emp drop column

OGG 동작 확인 --Source DB GGSCI (ocp.mycorpdomain.com) 5> stats EXTRACT EXTORA     Sending STATS request to EXTRACT EXTORA ...     Start of Statistics at 2013-04-08 22:51:04.     DDL replication statistics (for all trails):     *** Total statistics since extract started *** Operations 2.00 Mapped operations 2.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00     Output to ./dirdat/et:     *** Total statistics since 2013-04-07 22:22:48 ***     No database operations have been performed.     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 22:22:48 ***     No database operations have been performed.     Extracting from SCOTT.EMP to SCOTT.EMP:     *** Total statistics since 2013-04-07 22:22:48 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 22:22:48 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     Extracting from OGGMGR.GGS_MARKER to OGGMGR.GGS_MARKER:     *** Total statistics since 2013-04-07 22:22:48 ***     No database operations have been performed.     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 22:22:48 ***     No database operations have been performed.     End of Statistics.     GGSCI (ocp.mycorpdomain.com) 6>     GGSCI (ocp.mycorpdomain.com) 8> stats EXTRACT PUMPORA     Sending STATS request to EXTRACT PUMPORA ...     Start of Statistics at 2013-04-08 22:51:27.     DDL replication statistics (for all trails):     *** Total statistics since extract started *** Operations 2.00     Output to ./dirdat/rt:     *** Total statistics since 2013-04-07 23:11:14 ***     No database operations have been performed.     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 23:11:14 ***     No database operations have been performed.     Extracting from SCOTT.EMP to SCOTT.EMP:     *** Total statistics since 2013-04-07 23:11:14 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 23:11:14 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     End of Statistics.    --Target DB GGSCI (ocp.mycorpdomain.com) 2> stats REPLICAT REPORA     Sending STATS request to REPLICAT REPORA1 ...     Start of Statistics at 2013-04-08 22:51:55.     DDL replication statistics:     *** Total statistics since replicat started *** Operations 2.00 Mapped operations 2.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00     Replicating from SCOTT.EMP to SCOTT.EMP:     *** Total statistics since 2013-04-07 23:24:16 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     *** Daily statistics since 2013-04-08 00:00:00 ***     No database operations have been performed.     *** Hourly statistics since 2013-04-08 22:00:00 ***     No database operations have been performed.     *** Latest statistics since 2013-04-07 23:24:16 *** Total inserts 56.00 Total updates 0.00 Total deletes 56.00 Total discards 0.00 Total operations 112.00     End of Statistics.

   

[주의사항] Vmware 환경이다 보니, Source DB -> Target DB로 작업 내용(DML/DDL)이 넘어가는데 시간이 걸리는 것을 확인할 수 있다.(1~3(s))

   

   

3.OGG Logfile List / 자주사용 되는 명령어

3-1 OGG Logfile

• 구성시 OGG 로그를 Tail 로 열어 두고 작업하는 것이 에러를 찾는데 좋은 방법이 됩니다.

     

/OGG/ggserr.log

/$ORACLE_BASE/diag/…/Trace/ggs_ddl_trace.log

   

3-2 OGG 기타 명령어

EXTTRAIL / REPLICAT의 설정 시 좀더 명확하게 옵션을 줄수 있다. setenv (NLS_LANG = "KOREAN_KOREA.KO16KSC5601") setenv (ORACLE_SID = "orcl")

EXTTRAIL / REPLICAT 적용이 후, 사용되고 있는 파일 명 변경 한다. Alter REPLICAT REPORA, exttrail ./dirdat/sa Alter EXTTRAIL EXTORA, exttrail ./dirdat/si

OGG의 경우 Seqno 및 RBA,SCAN 이 틀어지는 경우가 종종 발생한다 / 해당 번호에 맞게 변경시 사용한다. ALTER REPLICAT REPORA, EXTSEQNO 0, EXTRBA 0 ALTER EXTTRAIL EXTORA, EXTSEQNO 0, EXTRBA 0

   

• 컬럼 mapping(COLMAP) / 세부 옵션의 경우 추가적으로 정리할 예정입니다.

     

[참고자료]

http://h391106.tistory.com/277

http://gssdba.wordpress.com/category/oracle-golden-gate/

http://mwguru.tistory.com/21

http://setijoagus.wordpress.com/2010/03/27/install-oracle-goldengate-2/

http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-6-configuring-data-pump-process/

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html