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

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

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

1. IT Story/DB 2013. 4. 8. 23:30

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

'1. IT Story > DB' 카테고리의 다른 글

Oracle Datapump를 이용한 Expdp/Impdp백업!! (0)	2013.04.15
Active DataGuard 11gR2 DGMGRL (0)	2013.04.09
Active DataGuard 11gR2 Configuration (0)	2013.04.09
Active DataGuard 11gR2 개념 및 Installation (0)	2013.04.09
Oracle Golden Gate 개념 및 Installaton (2)	2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption) (0)	2013.03.31
Oracle 10g CRS 제거 (0)	2013.03.31
Oracle 11g RAC with ASM TEST (2)	2013.03.31