Data miration using OGG

Oracle/OGG

by 야솔아빠 2014. 7. 21. 13:09

시나리오 설명

Start the Source and Target OGG Manager processes.
Create an Extract that reads from Oracle redo logs in the Source environment.
Create an Extract Pump that will move captured transactions to the Target environment.
Create a Replicat that will be used to sync up the two sides based on a SCN number we will get prior to an Oracle Datapump export.
Start the OGG Capture process on the Source side.
Export data from the Source database.
Import data into the Target database.
Start the Replicat on the Amer environment to start after the last SCN exported from the Source source database.
Check to see if both sides are in sync.

사전준비

- terminal 을 두개 열어서 각각 source, target 용으로 사용한다.

- Oracle 10g, 11g 구동상태를 확인한다. 구동중이지 않으면 구동

[oracle@ogg-wkshp ~]$ ps -ef | grep pmon
oracle    4563     1 0 17:46 ?        00:00:00 asm_pmon_+ASM
oracle    4703     1 0 17:46 ?        00:00:00 ora_pmon_ORCL11
oracle    6199     1 0 18:45 ?        00:00:00 ora_pmon_ORCL10
oracle    6355 6055 0 18:46 pts/1    00:00:00 grep pmon
[oracle@ogg-wkshp ~]$
[oracle@ogg-wkshp ~]$

* 10g 구동방법

[oracle@ogg-wkshp ~]$ source 10g.env
[oracle@ogg-wkshp ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jul 20 18:48:09 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup

* 11g 구동방법

[oracle@ogg-wkshp ~]$ source 11gR2.env
[oracle@ogg-wkshp ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 20 18:49:05 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> startup

Lab2(Data migration) 을 위한 환경설정 파일 구동

- 관련 테이블 clear

- Lab을 위한 SQL 생성

[oracle@ogg-wkshp ~]$ cd Lab2

[oracle@ogg-wkshp Lab2]$ ./prep_ogg.sh 2

1. source system 설정

source DB 테스트용 초기데이터 생성

- categories, categories_description, products, .. 등 14개의 테이블 생성 및 초기데이터 구축

- user : euro

[oracle@ogg-wkshp ~]$ source 10g.env

[oracle@ogg-wkshp ~]$ cd $SQL

[oracle@ogg-wkshp dirsql]$ sqlplus euro/euro

SQL*Plus: Release 10.2.0.5.0 - Production on Sun Jul 20 19:21:33 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @database.sql

source OGG 의 parameter 초기화

- OGG 관련 parameter 를 등록하기 위해, Lab2 에서 자동으로 생성된 parameter 를 삭제한다.

[oracle@ogg-wkshp ~]$ source 10g.env

[oracle@ogg-wkshp ~]$ cd $SQL

[oracle@ogg-wkshp 10gogg]$ cd dirprm/
[oracle@ogg-wkshp dirprm]$ ls -altr
total 32
-rwxr-x--- 1 oracle oinstall   53 Feb 5 2013 jagent.prm
drwxr-xr-x 17 oracle oinstall 4096 Jul 10 21:53 ..
-rw-r--r-- 1 oracle oinstall 355 Jul 20 18:46 setup_lab2.oby
-rw-r--r-- 1 oracle oinstall 116 Jul 20 18:46 peuro.prm
-rw-r--r-- 1 oracle oinstall   84 Jul 20 18:46 mgr.prm
-rw-r--r-- 1 oracle oinstall 187 Jul 20 18:46 eeuro.prm
drwxr-x--- 2 oracle oinstall 4096 Jul 20 18:46 .
[oracle@ogg-wkshp dirprm]$ rm setup_lab2.oby peuro.prm mgr.prm eeuro.prm
[oracle@ogg-wkshp dirprm]$ ls -altr
total 16
-rwxr-x--- 1 oracle oinstall   53 Feb 5 2013 jagent.prm
drwxr-xr-x 17 oracle oinstall 4096 Jul 10 21:53 ..
drwxr-x--- 2 oracle oinstall 4096 Jul 20 19:35 .

source DB 의 OGG 설정

[oracle@ogg-wkshp ~]$ source 10g.env
[oracle@ogg-wkshp ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb 5 2013 08:12:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED

GGSCI (ogg-wkshp.us.oracle.com) 1> view param dirprm/migration.oby
ERROR: PARAM file dirprm/migration.oby does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 2> edit param dirprm/migration.oby

*miggration.oby 내용

DBLOGIN USERID ogguser, PASSWORD Oracle1
ADD EXTRACT EEURO, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/ea, EXTRACT EEURO, MEGABYTES 50
ADD EXTRACT PEURO, EXTTRAILSOURCE ./dirdat/ea
ADD RMTTRAIL ./dirdat/pa, EXTRACT PEURO, MEGABYTES 50
ADD TRANDATA euro.*

GGSCI (ogg-wkshp.us.oracle.com) 3> view param dirprm/migration.oby

DBLOGIN USERID ogguser, PASSWORD Oracle1
ADD EXTRACT EEURO, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/ea, EXTRACT EEURO, MEGABYTES 50
ADD EXTRACT PEURO, EXTTRAILSOURCE ./dirdat/ea
ADD RMTTRAIL ./dirdat/pa, EXTRACT PEURO, MEGABYTES 50
ADD TRANDATA euro.*

migration.oby 실행

GGSCI (ogg-wkshp.us.oracle.com) 4> obey dirprm/migration.oby

...

GGSCI (ogg-wkshp.us.oracle.com) 11> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
EXTRACT     STOPPED     EEURO       00:00:00      00:00:27
EXTRACT     STOPPED     PEURO       00:00:00      00:00:25

GLOBALS parameter 확인

GGSCI (ogg-wkshp.us.oracle.com) 3> view params ./GLOBALS

GGSCHEMA OGGUSER
ENABLEMONITORING
CHECKPOINTTABLE GGS_CHECKPOINT

mgr parameter 등록

GGSCI (ogg-wkshp.us.oracle.com) 7> edit param mgr

GGSCI (ogg-wkshp.us.oracle.com) 8> view param mgr

PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

eeuro parameter 등록 (bold 채 참고하여 등록)

GGSCI (ogg-wkshp.us.oracle.com) 9> view param eeuro
ERROR: PARAM file EEURO does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 10> edit param eeuro

GGSCI (ogg-wkshp.us.oracle.com) 11> view param eeuro

EXTRACT EEURO
EXTRAIL ./dirdat/ea
USERID ogguser, PASSWORD Oracle1
STATOPTIONS RESETREPORTSTATS
REPORT AT 12:00
REPORTROLLOVER AT 12:00
REPORTCOUNT EVERY 60 SECONDS, RATE
TABLE euro.*;

peuro parameter 등록 (bold 채 참고하여 등록)

GGSCI (ogg-wkshp.us.oracle.com) 12> view param peuro
ERROR: PARAM file PEURO does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 13> edit param peuro

GGSCI (ogg-wkshp.us.oracle.com) 14> view param peuro

EXTRACT PEURO
RMTHOST localhost, MGRPORT 15000, COMPRESS
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE euro.*;

start manager

GGSCI (ogg-wkshp.us.oracle.com) 15> start mgr

Manager started.

GGSCI (ogg-wkshp.us.oracle.com) 16> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     STOPPED     EEURO       00:00:00      01:01:55
EXTRACT     STOPPED     PEURO       00:00:00      01:01:53

2. target system 설정

target OGG parameter 초기화

[oracle@ogg-wkshp 11gR2ogg]$ cd
[oracle@ogg-wkshp ~]$ source 11gR2.env

[oracle@ogg-wkshp ~]$ cd $GG
[oracle@ogg-wkshp 11gR2ogg]$ cd dirprm/
[oracle@ogg-wkshp dirprm]$ ls -altr
total 36
-rwxr-x--- 1 oracle oinstall 53 Feb 5 2013 jagent.prm
-rw-r--r-- 1 oracle oinstall 874 Jul 1 21:23 reconf.prm.oldway
-rw-r--r-- 1 oracle oinstall 217 Jul 20 18:45 setup_lab2.oby
-rw-r--r-- 1 oracle oinstall 131 Jul 20 18:45 ramer.prm
-rw-r--r-- 1 oracle oinstall 84 Jul 20 18:45 mgr.prm
drwxr-xr-x 2 oracle oinstall 4096 Jul 20 18:45 .
drwxr-xr-x 20 oracle oinstall 4096 Jul 21 00:25 ..

[oracle@ogg-wkshp dirprm]$ rm setup_lab2.oby ramer.prm mgr.prm

[oracle@ogg-wkshp dirprm]$ ls -altr
total 24
-rwxr-x--- 1 oracle oinstall 53 Feb 5 2013 jagent.prm
-rw-r--r-- 1 oracle oinstall 874 Jul 1 21:23 reconf.prm.oldway
drwxr-xr-x 20 oracle oinstall 4096 Jul 21 00:25 ..
drwxr-xr-x 2 oracle oinstall 4096 Jul 21 00:27 .

migration.oby 입력 (맨 아래 bold 채 내용을 입력)

[oracle@ogg-wkshp dirprm]$ cd

[oracle@ogg-wkshp ~]$ cd $GG
[oracle@ogg-wkshp 11gR2ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb 5 2013 08:11:55

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED

GGSCI (ogg-wkshp.us.oracle.com) 2> edit param dirprm/migration.oby

GGSCI (ogg-wkshp.us.oracle.com) 3> view param dirprm/migration.oby

DBLOGIN USERID ogguser, PASSWORD Oracle1
ADD CHECKPOINTTABLE
ADD REPLICAT RAMER, EXTTRAIL ./dirdat/pa

GLOBALS parameter 확인 (없으면 등록)

GGSCI (ogg-wkshp.us.oracle.com) 4> sh cat ./GLOBALS

GGSCHEMA OGGUSER
ENABLEMONITORING
CHECKPOINTTABLE GGS_CHECKPOINT

migration.oby 파일 수행

GGSCI (ogg-wkshp.us.oracle.com) 5> obey dirprm/migration.oby

GGSCI (ogg-wkshp.us.oracle.com) 6> DBLOGIN USERID ogguser, PASSWORD Oracle1

Successfully logged into database.

GGSCI (ogg-wkshp.us.oracle.com) 7> ADD CHECKPOINTTABLE

No checkpoint table specified, using GLOBALS specification (GGS_CHECKPOINT)...

Successfully created checkpoint table GGS_CHECKPOINT.

GGSCI (ogg-wkshp.us.oracle.com) 8> ADD REPLICAT RAMER, EXTTRAIL ./dirdat/pa

REPLICAT added.

GGSCI (ogg-wkshp.us.oracle.com) 9> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
REPLICAT    STOPPED     RAMER       00:00:00      00:00:10

mgr parameter 등록 (아래 bold 채 내용을 입력)

GGSCI (ogg-wkshp.us.oracle.com) 10> view param mgr
ERROR: PARAM file MGR does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 11> edit param mgr

GGSCI (ogg-wkshp.us.oracle.com) 12> view param mgr

PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

ramer parameter 등록(아래 bold 채 내용을 등록)

GGSCI (ogg-wkshp.us.oracle.com) 13> view param ramer
ERROR: PARAM file RAMER does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 14> edit param ramer

GGSCI (ogg-wkshp.us.oracle.com) 15> view para ramer
ERROR: Invalid command.

GGSCI (ogg-wkshp.us.oracle.com) 16> view param ramer

REPLICAT RAMER
ASSUMETARGETDEFS
DISCARDFILE ./dirout/RAMER.DSC, PURGE
USERID ogguser, PASSWORD Oracle1
MAP euro.*, TARGET amer.*;

* The ASSUMETARGETDEFS statement means that we’re assuming that the target table
structure is the same as the source table structure, which it is.

manager 기동

GGSCI (ogg-wkshp.us.oracle.com) 17> start mgr

Manager started.

GGSCI (ogg-wkshp.us.oracle.com) 18> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     RAMER       00:00:00      00:11:25

3. Start Extracts and Export Data
* 중요 : source system 에서 수행

eeuro 기동

- 에러가 났다..ㅠㅠ

[oracle@ogg-wkshp ~]$ source 10g.env
[oracle@ogg-wkshp ~]$ cd $GG
[oracle@ogg-wkshp 10gogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb 5 2013 08:12:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> start eeuro

Sending START request to MANAGER ...
EXTRACT EEURO starting

GGSCI (ogg-wkshp.us.oracle.com) 2> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     STOPPED     EEURO       00:00:00      01:41:08
EXTRACT     STOPPED     PEURO       00:00:00      01:41:06

GGSCI (ogg-wkshp.us.oracle.com) 2> info eeuro

EXTRACT    EEURO     Initialized   2014-07-20 23:17   Status STOPPED
Checkpoint Lag       00:00:00 (updated 01:45:26 ago)
Log Read Checkpoint Oracle Redo Logs
                     2014-07-20 23:17:59 Seqno 0, RBA 0
                     SCN 0.0 (0)

GGSCI (ogg-wkshp.us.oracle.com) 3> exit

[oracle@ogg-wkshp 10gogg]$ ls -altr

[oracle@ogg-wkshp 10gogg]$ less ggserr.log

* SHIFT+g 를 눌러서 맨 아래 로그를 확인

2014-07-21 00:59:02 INFO    OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EEURO starting.
2014-07-21 00:59:03 INFO    OGG-00992 Oracle GoldenGate Capture for Oracle, eeuro.prm: EXTRACT EEURO starting.
2014-07-21 00:59:03 INFO    OGG-03035 Oracle GoldenGate Capture for Oracle, eeuro.prm: Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-07-21 00:59:03 ERROR   OGG-00303 Oracle GoldenGate Capture for Oracle, eeuro.prm: Unrecognized parameter (EXTRAIL).
2014-07-21 00:59:03 ERROR   OGG-01668 Oracle GoldenGate Capture for Oracle, eeuro.prm: PROCESS ABENDING.

eeuro.prm 파일 수정

[oracle@ogg-wkshp 10gogg]$ cd dirprm/
[oracle@ogg-wkshp dirprm]$ ls -altr
total 32
-rwxr-x--- 1 oracle oinstall 53 Feb 5 2013 jagent.prm
-rw-rw-rw- 1 oracle oinstall 254 Jul 20 23:15 migration.oby
-rw-rw-rw- 1 oracle oinstall 83 Jul 20 23:27 mgr.prm
-rw-rw-rw- 1 oracle oinstall 185 Jul 20 23:35 eeuro.prm
-rw-rw-rw- 1 oracle oinstall 101 Jul 21 00:18 peuro.prm
drwxr-x--- 2 oracle oinstall 4096 Jul 21 00:18 .
drwxr-xr-x 17 oracle oinstall 4096 Jul 21 01:00 ..
[oracle@ogg-wkshp dirprm]$ vi eeuro.prm

EXTRACT EEURO
EXTTRAIL ./dirdat/ea
USERID ogguser, PASSWORD Oracle1
STATOPTIONS RESETREPORTSTATS
REPORT AT 12:00
REPORTROLLOVER AT 12:00
REPORTCOUNT EVERY 60 SECONDS, RATE
TABLE euro.*;

다시 eeuro 재기동

[oracle@ogg-wkshp dirprm]$ cd ..
[oracle@ogg-wkshp 10gogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb 5 2013 08:12:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> start eeuro

Sending START request to MANAGER ...
EXTRACT EEURO starting

GGSCI (ogg-wkshp.us.oracle.com) 2> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     RUNNING     EEURO       01:51:20      00:00:01
EXTRACT     STOPPED     PEURO       00:00:00      01:51:19

peuro 기동(pump process)

GGSCI (ogg-wkshp.us.oracle.com) 3> start peuro

Sending START request to MANAGER ...
EXTRACT PEURO starting

GGSCI (ogg-wkshp.us.oracle.com) 4> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
EXTRACT     RUNNING     EEURO       00:00:00      00:00:07
EXTRACT     RUNNING     PEURO       00:00:00      01:51:57

SCN 기록

- 14개의 table을 생성하였고, 각각 테이블에 초기 데이터를 구축한 상태다.

- 초기 데이터 구축 후 data 유입은 없으며, SCN을 기록한다. (ex : 1489962)

GGSCI (ogg-wkshp.us.oracle.com) 5> exit
[oracle@ogg-wkshp 10gogg]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 21 01:18:45 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
FROM DUAL;
2
GET_SYSTEM_CHANGE_NUMBER
------------------------
1489962

SQL> grant execute on DBMS_FLASHBACK to euro;

Grant succeeded.

SQL> exit

export data from source database.

- 빨간색의 SCN 번호는 실습자의 값으로 입력해야 함.

[oracle@ogg-wkshp 10gogg]$ cd dirsql/
[oracle@ogg-wkshp dirsql]$ ls -altr
total 96
-rw-r--r-- 1 oracle oinstall 32186 Apr 20 2011 reset_database.sql
-rw-r--r-- 1 oracle oinstall 1524 Apr 21 2011 getcount.sql
-rw-r--r-- 1 oracle oinstall 7850 Jan 24 2013 gentrans.sql
-rw-r--r-- 1 oracle oinstall 39070 Jan 24 2013 database.sql
drwxrwxr-x 2 oracle oinstall 4096 Jul 20 19:27 .
drwxr-xr-x 17 oracle oinstall 4096 Jul 21 01:00 ..
[oracle@ogg-wkshp dirsql]$ exp euro/euro flashback_SCN=1489962 file=export.dat owner=euro

Export: Release 10.2.0.5.0 - Production on Mon Jul 21 01:21:15 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user EURO
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user EURO
About to export EURO's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export EURO's tables via Conventional Path ...
. . exporting table                     CATEGORIES         21 rows exported
. . exporting table         CATEGORIES_DESCRIPTION         21 rows exported
. . exporting table                      CUSTOMERS         12 rows exported
. . exporting table                 CUSTOMERS_INFO         12 rows exported
. . exporting table                 CUSTOMERS_LKUP         36 rows exported
. . exporting table                      NEXT_CUST          1 rows exported
. . exporting table                     NEXT_ORDER          1 rows exported
. . exporting table                         ORDERS          1 rows exported
. . exporting table                ORDERS_PRODUCTS          1 rows exported
. . exporting table          ORDERS_STATUS_HISTORY          2 rows exported
. . exporting table                   ORDERS_TOTAL          3 rows exported
. . exporting table                       PRODUCTS         28 rows exported
. . exporting table           PRODUCTS_DESCRIPTION         28 rows exported
. . exporting table         PRODUCTS_TO_CATEGORIES         28 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@ogg-wkshp dirsql]$ ls -altr
total 156
-rw-r--r-- 1 oracle oinstall 32186 Apr 20 2011 reset_database.sql
-rw-r--r-- 1 oracle oinstall 1524 Apr 21 2011 getcount.sql
-rw-r--r-- 1 oracle oinstall 7850 Jan 24 2013 gentrans.sql
-rw-r--r-- 1 oracle oinstall 39070 Jan 24 2013 database.sql
drwxr-xr-x 17 oracle oinstall 4096 Jul 21 01:00 ..
drwxrwxr-x 2 oracle oinstall 4096 Jul 21 01:21 .
-rw-r--r-- 1 oracle oinstall 57344 Jul 21 01:21 export.dat

[oracle@ogg-wkshp dirsql]$
[oracle@ogg-wkshp dirsql]$
[oracle@ogg-wkshp dirsql]$ env | grep GG11
GG11=/u01/app/oracle/product/11gR2ogg
[oracle@ogg-wkshp dirsql]$ cp export.dat $GG11/dirsql
[oracle@ogg-wkshp dirsql]$ ls -altr $GG11/dirsql
total 92
-rw-r--r-- 1 oracle oinstall 1524 Apr 21 2011 getcount.sql
-rw-r--r-- 1 oracle oinstall 448 Jul 2 2012 reset_database.sql
-rw-r--r-- 1 oracle oinstall 6064 Jul 2 2012 database.sql
drwxr-xr-x 20 oracle oinstall 4096 Jul 21 00:25 ..
-rw-r--r-- 1 oracle oinstall 57344 Jul 21 01:24 export.dat
drwxr-xr-x 2 oracle oinstall 4096 Jul 21 01:24 .

source DB 의 데이터 변경사항 발생

- gentrans.sql 파일을 보면 일부 테이블에 대해 랜덤하게 변경이 발생하도록 되어 있음.

- 즉, export 데이터 이후 변경사항이 발생하고 있음

[oracle@ogg-wkshp dirsql]$ vi gentrans.sql
[oracle@ogg-wkshp dirsql]$ sqlplus euro/euro

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 21 01:51:02 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @gentrans.sql
How many orders?(Default = 1500)

1500

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ogg-wkshp dirsql]$ 1500

extract process에서 변경사항을 capture 하였는지 확인

[oracle@ogg-wkshp dirsql]$ cd ..
[oracle@ogg-wkshp 10gogg]$ ggscit
bash: ggscit: command not found
[oracle@ogg-wkshp 10gogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 10g on Feb 5 2013 08:12:25

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> stats eeuro, total

Sending STATS request to EXTRACT EEURO ...

Start of Statistics at 2014-07-21 02:01:42.

Output to ./dirdat/ea:

Extracting from EURO.CUSTOMERS to EURO.CUSTOMERS:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                   1380.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1380.00

Extracting from EURO.CUSTOMERS_INFO to EURO.CUSTOMERS_INFO:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                   1380.00
        Total updates                                    120.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Extracting from EURO.NEXT_ORDER to EURO.NEXT_ORDER:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                      0.00
        Total updates                                   1500.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Extracting from EURO.ORDERS to EURO.ORDERS:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                   1500.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Extracting from EURO.ORDERS_PRODUCTS to EURO.ORDERS_PRODUCTS:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                  19552.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               19552.00

Extracting from EURO.PRODUCTS to EURO.PRODUCTS:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                      0.00
        Total updates                                  19552.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               19552.00

Extracting from EURO.ORDERS_STATUS_HISTORY to EURO.ORDERS_STATUS_HISTORY:

*** Total statistics since 2014-07-21 01:58:25 ***
        Total inserts                                   1500.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

End of Statistics.

4. target system 에서 import 및 확인

source 시스템에서 export 한 데이터를 import 한다.

* target system 에서 명령 수행

[oracle@ogg-wkshp ~]$ source 11gR2.env
[oracle@ogg-wkshp ~]$ cd $GG
[oracle@ogg-wkshp 11gR2ogg]$ cd dirsql
[oracle@ogg-wkshp dirsql]$ ls -altr
total 92
-rw-r--r-- 1 oracle oinstall 1524 Apr 21 2011 getcount.sql
-rw-r--r-- 1 oracle oinstall 448 Jul 2 2012 reset_database.sql
-rw-r--r-- 1 oracle oinstall 6064 Jul 2 2012 database.sql
drwxr-xr-x 20 oracle oinstall 4096 Jul 21 00:25 ..
-rw-r--r-- 1 oracle oinstall 57344 Jul 21 01:24 export.dat
drwxr-xr-x 2 oracle oinstall 4096 Jul 21 01:24 .

[oracle@ogg-wkshp dirsql]$ imp amer/amer file=export.dat full=y ignore=y

Import: Release 11.2.0.3.0 - Production on Mon Jul 21 02:09:00 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by EURO, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing EURO's objects into AMER
. . importing table                   "CATEGORIES"         21 rows imported
. . importing table       "CATEGORIES_DESCRIPTION"         21 rows imported
. . importing table                    "CUSTOMERS"         12 rows imported
. . importing table               "CUSTOMERS_INFO"         12 rows imported
. . importing table               "CUSTOMERS_LKUP"         36 rows imported
. . importing table                    "NEXT_CUST"          1 rows imported
. . importing table                   "NEXT_ORDER"          1 rows imported
. . importing table                       "ORDERS"          1 rows imported
. . importing table              "ORDERS_PRODUCTS"          1 rows imported
. . importing table        "ORDERS_STATUS_HISTORY"          2 rows imported
. . importing table                 "ORDERS_TOTAL"          3 rows imported
. . importing table                     "PRODUCTS"         28 rows imported
. . importing table         "PRODUCTS_DESCRIPTION"         28 rows imported
. . importing table       "PRODUCTS_TO_CATEGORIES"         28 rows imported
Import terminated successfully without warnings.
[oracle@ogg-wkshp dirsql]$

[oracle@ogg-wkshp dirsql]$ sqlplus amer/amer

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 21 02:20:49 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @getcount.sql
CATEGORIES => 21
CATEGORIES_DESCRIPTION => 21
CUSTOMERS => 12
CUSTOMERS_INFO => 12
CUSTOMERS_LKUP => 36
NEXT_CUST => 1
NEXT_ORDER => 1
ORDERS => 1
ORDERS_PRODUCTS => 1
ORDERS_STATUS_HISTORY => 2
ORDERS_TOTAL => 3
PRODUCTS => 28
PRODUCTS_DESCRIPTION => 28
PRODUCTS_TO_CATEGORIES => 28

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@ogg-wkshp dirsql]$

replicat process 구동

- import 이후, 변경된 1500건 data 를 처리

[oracle@ogg-wkshp dirsql]$ cd ..
[oracle@ogg-wkshp 11gR2ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb 5 2013 08:11:55

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> start ramer, afterscn 1489962

Sending START request to MANAGER ...
REPLICAT RAMER starting

GGSCI (ogg-wkshp.us.oracle.com) 2> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     RAMER       00:00:00      01:47:21

GGSCI (ogg-wkshp.us.oracle.com) 3> exit
[oracle@ogg-wkshp 11gR2ogg]$ ls -altr
[oracle@ogg-wkshp 11gR2ogg]$ less ggserr.log

*에러내용

2014-07-21 02:24:58 ERROR OGG-00012 Oracle GoldenGate Delivery for Oracle, ramer.prm: Command line error:invalid startup syntax: AFTERSCN.
2014-07-21 02:24:58 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, ramer.prm: PROCESS ABENDING.

다시 replicat 수행

[oracle@ogg-wkshp 11gR2ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb 5 2013 08:11:55

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (ogg-wkshp.us.oracle.com) 1> start ramer, aftercsn 1489962

Sending START request to MANAGER ...
REPLICAT RAMER starting

GGSCI (ogg-wkshp.us.oracle.com) 2> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    RUNNING     RAMER       00:00:00      00:00:00

GGSCI (ogg-wkshp.us.oracle.com) 3> stats ramer, total

Sending STATS request to REPLICAT RAMER ...

Start of Statistics at 2014-07-21 02:29:15.

Replicating from EURO.CUSTOMERS to AMER.CUSTOMERS:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                   1380.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1380.00

Replicating from EURO.CUSTOMERS_INFO to AMER.CUSTOMERS_INFO:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                   1380.00
        Total updates                                    120.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Replicating from EURO.NEXT_ORDER to AMER.NEXT_ORDER:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                      0.00
        Total updates                                   1500.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Replicating from EURO.ORDERS to AMER.ORDERS:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                   1500.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

Replicating from EURO.ORDERS_PRODUCTS to AMER.ORDERS_PRODUCTS:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                  19552.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               19552.00

Replicating from EURO.PRODUCTS to AMER.PRODUCTS:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                      0.00
        Total updates                                  19552.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                               19552.00

Replicating from EURO.ORDERS_STATUS_HISTORY to AMER.ORDERS_STATUS_HISTORY:

*** Total statistics since 2014-07-21 02:28:22 ***
        Total inserts                                   1500.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                1500.00

End of Statistics.

GGSCI (ogg-wkshp.us.oracle.com) 4> exit

데이터 확인 (target)

[oracle@ogg-wkshp 11gR2ogg]$ cd dirsql
[oracle@ogg-wkshp dirsql]$ sqlplus amer/amer

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 21 02:29:58 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @getcount.sql
CATEGORIES => 21
CATEGORIES_DESCRIPTION => 21
CUSTOMERS => 1392
CUSTOMERS_INFO => 1392
CUSTOMERS_LKUP => 36
NEXT_CUST => 1
NEXT_ORDER => 1
ORDERS => 1501
ORDERS_PRODUCTS => 19553
ORDERS_STATUS_HISTORY => 1502
ORDERS_TOTAL => 3
PRODUCTS => 28
PRODUCTS_DESCRIPTION => 28
PRODUCTS_TO_CATEGORIES => 28

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@ogg-wkshp dirsql]$

데이터 확인 (source)

[oracle@ogg-wkshp 10gogg]$ cd dirsql
[oracle@ogg-wkshp dirsql]$ sqlplus euro/euro

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 21 02:31:25 2014

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @getcount.sql
CATEGORIES => 21
CATEGORIES_DESCRIPTION => 21
CUSTOMERS => 1392
CUSTOMERS_INFO => 1392
CUSTOMERS_LKUP => 36
NEXT_CUST => 1
NEXT_ORDER => 1
ORDERS => 1501
ORDERS_PRODUCTS => 19553
ORDERS_STATUS_HISTORY => 1502
ORDERS_TOTAL => 3
PRODUCTS => 28
PRODUCTS_DESCRIPTION => 28
PRODUCTS_TO_CATEGORIES => 28

PL/SQL procedure successfully completed.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ogg-wkshp dirsql]$