OGG(Oracle Golden Gate) DDL Replication

Oracle/OGG

by 야솔아빠 2014. 7. 11. 16:51

Source DB 의 DDL 변경을 Target DB 로 동기화 시키는 예제

환경

- source DB : 10g

- target DB : 11gR2

1. source DB system 설정

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

[oracle@ogg-wkshp ~]$ cd $GG
[oracle@ogg-wkshp 10gogg]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 9 19:07:51 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> ALTER SYSTEM SET RECYCLEBIN=OFF;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2095896 bytes
Variable Size             318768360 bytes
Database Buffers          889192448 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL>

Turn off the Oracle Recycle B in. Support for the Recycle Bin being “on” is only for
Oracle 11g or above, as long as you are using GoldenGate version 11.2.x

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2095896 bytes
Variable Size             318768360 bytes
Database Buffers          889192448 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> GRANT EXECUTE ON UTL_FILE TO OGGUSER;

Grant succeeded.

Run the marker_setup script. This script installs support fo r the GoldenGate marker
system required for DDL support.

SQL> @marker_setup

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogguser

Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGGUSER

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

Run the ddl_setup script. You will be prompted for t he name of the DDL
schema from the previous step (ogguser).

SQL> @ddl_setup

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogguser

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using OGGUSER as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGGUSER

CLEAR_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

CREATE_TRACE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

TRACE_PUT_LINE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

INITIAL_SETUP STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos   Error
---------- -----------------------------------------------------------------
No errors No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
-----------------------------------
ENABLED

STAYMETADATA IN TRIGGER
-----------------------------------
OFF

DDL TRIGGER SQL TRACING
-----------------------------------
0

DDL TRIGGER TRACE LEVEL
-----------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/ORCL10/udump/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

Run the role_setup script. This script drops and creates the role needed for
DDL synchronization. You will be prompted for the name of the DDL schema
from the previous step (ogguser).

SQL> @role_setup

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ogguser
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO OGGUSER;

Grant succeeded.

Run the ddl_enable script to enable the DDL trigger.

SQL> @ddl_enable

Trigger altered.

Make the ddl_pin script part of the database startup. It must be invoked with the
GoldenGate DDL user name. This script improves the performance of the DDL trigger
and requires the Oracle dmbs_shared_pool system package.

SQL> @ddl_pin ogguser

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exit

Soruce DB의 OGG configuration

[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) 3> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
EXTRACT     ABENDED     EXT01       00:00:00      28:54:58
EXTRACT     ABENDED     PMP01       00:00:00      20:45:50

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

ddl_lab.oby 파일내용

DBLOGIN USERID ogguser, PASSWORD Oracle1
ADD EXTRACT EDDL, TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/ed, EXTRACT EDDL, MEGABYTES 100
ADD EXTRACT PDDL, EXTTRAILSOURCE ./dirdat/ed
ADD RMTTRAIL ./dirdat/pd, EXTRACT PDDL, MEGABYTES 100

mgr parameter 를 수정한다.

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

PORT 9010

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

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

PORT 9010
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5
PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5

obey 파일을 수행하여 extract, pump process 를 등록

GGSCI (ogg-wkshp.us.oracle.com) 11> obey dirprm/ddl_lab.oby

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

Successfully logged into database.

GGSCI (ogg-wkshp.us.oracle.com) 13> ADD EXTRACT EDDL, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (ogg-wkshp.us.oracle.com) 14> ADD EXTTRAIL ./dirdat/ed, EXTRACT EDDL, MEGABYTES 100

EXTTRAIL added.

GGSCI (ogg-wkshp.us.oracle.com) 15> ADD EXTRACT PDDL, EXTTRAILSOURCE ./dirdat/ed

EXTRACT added.

GGSCI (ogg-wkshp.us.oracle.com) 16> ADD RMTTRAIL ./dirdat/pd, EXTRACT PDDL, MEGABYTES 100

RMTTRAIL added.

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

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

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED
EXTRACT     STOPPED     EDDL        00:00:00      00:00:16
EXTRACT     ABENDED     EXT01       00:00:00      29:21:45
EXTRACT     STOPPED     PDDL        00:00:00      00:00:14
EXTRACT     ABENDED     PMP01       00:00:00      21:12:37

eddl, pddl parameter 설정

GGSCI (ogg-wkshp.us.oracle.com) 19> view param eddl
ERROR: PARAM file EDDL does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 20> edit param eddl

GGSCI (ogg-wkshp.us.oracle.com) 21> view param eddl

EXTRACT EDDL
EXTTRAIL ./dirdat/ed
USERID ogguser, PASSWORD Oracle1
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA REPORT
TABLE app.*;

GGSCI (ogg-wkshp.us.oracle.com) 22> view param pddl
ERROR: PARAM file PDDL does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 23> edit param pddl

GGSCI (ogg-wkshp.us.oracle.com) 24> view param pddl

EXTRACT PDDL
RMTHOST localhost, MGRPORT 9020, COMPRESS
RMTTRAIL ./dirdat/pd
PASSTHRU
TABLE app.*;

Run mgr

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

Manager started.

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

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EDDL        00:00:00      00:36:37
EXTRACT     ABENDED     EXT01       00:00:00      29:58:06
EXTRACT     STOPPED     PDDL        00:00:00      00:36:35
EXTRACT     ABENDED     PMP01       00:00:00      21:48:58

2. target DB system 설정

[oracle@ogg-wkshp ~]$ source 11gR2.env
[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
REPLICAT    ABENDED     REP01       00:00:00      21:57:00

ddl_lab.oby parameter 파일 생성

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

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

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

DBLOGIN USERID ogguser, PASSWORD Oracle1
ADD CHECKPOINTTABLE
ADD REPLICAT RDDL, EXTTRAIL ./dirdat/pd

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

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

PORT 9020

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

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

PORT 9020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS

GGSCI (ogg-wkshp.us.oracle.com) 1> view param rddl
ERROR: PARAM file RDDL does not exist.

GGSCI (ogg-wkshp.us.oracle.com) 2> edit param rddl

GGSCI (ogg-wkshp.us.oracle.com) 6> view param rddl

REPLICAT RDDL
ASSUMETARGETDEFS
DISCARDFILE ./dirout/RDDL.DSC, PURGE
USERID ogguser, PASSWORD Oracle1
DDLOPTIONS REPORT
MAP app.*, TARGET app.*;

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

GGSCI (ogg-wkshp.us.oracle.com) 8> start rddl

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

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

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     RDDL        00:00:00      00:00:06
REPLICAT    STOPED     REP01       00:00:00      00:00:07

테스트

target DB 에서 아래 쿼리를 수행 --> categories 테이블이 없음을 확인

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

SQL> select count(*) from categories;
select count(*) from categories
*
ERROR at line 1:
ORA-00942: table or view does not exist

source DB 시스템에서 extract, pump 를 기동

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

GGSCI (ogg-wkshp.us.oracle.com) 8> start eddl

Sending START request to MANAGER ...
EXTRACT EDDL starting

GGSCI (ogg-wkshp.us.oracle.com) 9> start pddl

Sending START request to MANAGER ...
EXTRACT PDDL starting

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

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EDDL        00:00:00      00:00:06
EXTRACT     ABENDED     EXT01       00:00:00      120:56:37
EXTRACT     RUNNING     PDDL        00:00:00      00:00:16
EXTRACT     STOPPED     PMP01       00:00:00      00:00:43

source DB 에서 아래 쿼리를 수행 --> categories 테이블이 없음을 확인

[oracle@ogg-wkshp 10gogg]$ sqlplus app/app

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 14 18:49:31 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 count(*) from categories;
select count(*) from categories
*
ERROR at line 1:
ORA-00942: table or view does not exist

source DB 에서 categories 테이블 생성

[oracle@ogg-wkshp Lab7]$ sqlplus app/app

SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 14 19:09:40 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> CREATE TABLE categories (
categories_id number(11) NOT NULL,
categories_image varchar2(64),
parent_id number(11) NOT NULL,
sort_order number(3),
date_added timestamp,
last_modified timestamp,
PRIMARY KEY (categories_id)
using index
);

target DB 에서 categories 테이블 확인

[oracle@ogg-wkshp 11gR2ogg]$ sqlplus app/app

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 14 19:11:44 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> select count(*) from categories;

COUNT(*)
----------
0

source DB 의 categories 테이블에 데이터 insert

아래 쿼리를 source DB 에서 수행

INSERT INTO categories VALUES (13,'subcategory_cartoons.gif',3,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (14,'subcategory_thriller.gif',3,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (15,'subcategory_drama.gif',3,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (16,'subcategory_memory.gif',1,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (17,'subcategory_cdrom_drives.gif',1,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (18,'subcategory_simulation.gif',2,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (19,'subcategory_action_games.gif',2,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (20,'subcategory_strategy.gif',2,0,timestamp'2011-04-04 08:31:00',NULL);
INSERT INTO categories VALUES (21,'category_gadgets.png',0,4,timestamp'2011-04-04 08:31:00',NULL);
COMMIT;

target DB 에서 categories 테이블 확인

SQL> select count(*) from categories;

COUNT(*)
----------
9

source table 변경 및 데이터 입력

SQL> alter table categories add (createdt date);

Table altered.

SQL> alter table categories modify categories_image varchar2(255);

Table altered.

SQL> INSERT INTO categories VALUES (22,'TEST.gif',0,4,timestamp'2011-04-04 08:31:00',NULL, sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL>

target table 확인

SQL> select count(*) from categories;

COUNT(*)
----------
        10

SQL> desc categories;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CATEGORIES_ID                             NOT NULL NUMBER(11)
CATEGORIES_IMAGE                                   VARCHAR2(255)
PARENT_ID                                 NOT NULL NUMBER(11)
SORT_ORDER                                         NUMBER(3)
DATE_ADDED                                         TIMESTAMP(6)
LAST_MODIFIED                                      TIMESTAMP(6)
CREATEDT                                           DATE

SQL>