'11gR2'에 해당되는 글 1건

 

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

   

   

블로그 이미지

운명을바꾸는자

IT와 함께 살아가는 삶

,