GoldenGate单向复制配置(支持DDL复制)

版本说明

                   Source               Target
Oracle             11.2.0.4.0           11.2.0.4.0
GoldenGate         12.2.0.1.1           12.2.0.1.1
OS                 CentOS 6.5           CentOS 6.5

不支持DDL验证

源端运行DDL语句

SQL> create table test(id number);

Table created.

目标端报错

[oracle@acarsorcl-ogg ogg]$ tail -n 500 ggserr.log

2016-07-18 15:45:25  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, repa.prm:  Wildcard MAP resolved (entry skylink.*): map "SKYLINK"."TEST" target skylink."TEST".
2016-07-18 15:45:25  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, repa.prm:  Table SKYLINK.TEST does not exist.
2016-07-18 15:45:25  ERROR   OGG-00199  Oracle GoldenGate Delivery for Oracle, repa.prm:  Table skylink.TEST does not exist in target database.
2016-07-18 15:45:25  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, repa.prm:  PROCESS ABENDING.

配置OGG支持DDL

1、赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数（源端操作）

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> grant execute on utl_file to ogg;

Grant succeeded.

GGSCI (acarsorcl-primary) 17> edit param ./GLOBALS
GGSCI (acarsorcl-primary) 1> view param ./GLOBALS

    ggschema ogg

2、运行相关sql脚本（源端操作）

[oracle@acarsorcl-primary ~]$ cd /u01/ogg

[oracle@acarsorcl-primary ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 18 15:56:33 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @marker_setup.sql
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:ogg
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.

SQL> @ddl_setup.sql
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:ogg
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 OGG 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 OGG
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
----------------------------------------------------------------------
NONE
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/diag/rdbms/orcl/orcl/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.

SQL> @role_setup.sql
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:ogg
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 ogg; 
Grant succeeded.
SQL> @ddl_enable.sql
Trigger altered.

SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.

SQL> @ddl_pin.sql ogg
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

3、修改extract和pump进程参数，添加ddl include all参数，重启进程（源端操作）

GGSCI (acarsorcl-primary) 18> edit param exta

GGSCI (acarsorcl-primary) 1> view param exta

    EXTRACT exta
    SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    setenv(ORACLE_SID=orcl)
    ddl include all
    USERID skylink, PASSWORD skylink
    EXTTRAIL /u01/ogg/dirdat/la
    dynamicresolution
    GETTRUNCATES
    TABLE skylink.*;

GGSCI (acarsorcl-primary) 19> edit param dpea

GGSCI (acarsorcl-primary) 1> view param dpea

    EXTRACT DPEA
    SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    ddl include all
    passthru
    rmthost acarsorcl-ogg,mgrport 7809,compress
    rmttrail /u01/ogg/dirdat/ra
    GETTRUNCATES
    dynamicresolution
    TABLE skylink.*;

GGSCI (acarsorcl-primary) 20> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEA        00:00:00      00:00:06    
EXTRACT     RUNNING     EXTA        00:00:00      00:00:06    


GGSCI (acarsorcl-primary) 21> stop ext *

Sending STOP request to EXTRACT DPEA ...
Request processed.

Sending STOP request to EXTRACT EXTA ...
Request processed.


GGSCI (acarsorcl-primary) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     DPEA        00:00:00      00:00:02    
EXTRACT     STOPPED     EXTA        00:00:00      00:00:01    


GGSCI (acarsorcl-primary) 23> start ext *

Sending START request to MANAGER ...
EXTRACT DPEA starting

Sending START request to MANAGER ...
EXTRACT EXTA starting


GGSCI (acarsorcl-primary) 24> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPEA        00:00:00      00:00:09    
EXTRACT     RUNNING     EXTA        00:00:00      00:00:09    

4、修改replicat进程的参数，添加ddl include all和ddlerror default ignore retryop maxretries 3 retrydelay 5参数，从启进程（目标端操作）

GGSCI (acarsorcl-ogg) 9> edit param repa    

GGSCI (acarsorcl-ogg) 1> view param repa

    REPLICAT repa
    SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    setenv(ORACLE_SID=orcl)
    ddl include all
    ddlerror default ignore retryop maxretries 3 retrydelay 5
    USERID skylink, PASSWORD skylink
    reperror default,abend
    discardfile /u01/ogg/dirrpt/repa.dsc,append,megabytes 10
    assumetargetdefs
    dynamicresolution
    GETTRUNCATES
    map skylink.* target skylink.*;

GGSCI (acarsorcl-ogg) 14> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting


GGSCI (acarsorcl-ogg) 15> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPA        00:00:00      00:19:12   

验证

1、源端删除test

SQL> conn skylink/skylink
Connected.
SQL> drop table test;
Table dropped.

2、源端创建test表

SQL> create table test(id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

3、查看目标端

SQL> conn skylink/skylink
Connected.
SQL> select * from test;

    ID
----------
     1