异构GoldenGate 12c 单向复制配置(支持DDL复制)

1.开始配置OGG支持DDL复制(在source端操作)
    1.1 赋予权限
        SQL> conn /as sysdba
        已连接。
        SQL> grant execute on utl_file to ggs;
    
    1.2 修改全局配置文件添加ggschema
        GGSCI (WIN-GM5PVS1CILH) 18> edit param ./GLOBALS
        GGSCI (WIN-GM5PVS1CILH) 19> view param ./GLOBALS
        ggschema ggs
        GGSCI (WIN-GM5PVS1CILH) 20>

    1.3 运行相关sql脚本 及修改参数

        C:\Users\Administrator>cd C:\ora11g\product\ogg_src
        C:\ora11g\product\ogg_src>sqlplus /nolog
        SQL> conn /as sysdba
        已连接。

        
        #第一个sql脚本
        SQL> @marker_setup.sql
        
        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:ggs

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

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

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

        Script complete.
        SQL>

        
        #禁用 recyclebin 。官网的解释如下：If the recyclebin is enabled, the Oracle GoldenGate DDL trigger session receives implicitrecycle bin DDL operations that cause the trigger to fail.
        
        SQL> show parameter recyclebin;

        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ------------------------------
        recyclebin                           string      on

        SQL> alter system set recyclebin=off scope=spfile;

        系统已更改。

        SQL> show parameter recyclebin; #需重启实例后改参数才生效

        NAME                                 TYPE        VALUE
        ------------------------------------ ----------- ------------------------------
        recyclebin                           string      off
        SQL>

        
        #第二个sql脚本 注意：此脚本要求ggs必须是独立表空间且是自动扩展的
        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 databaseobjects.
        NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Ora
        cle 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:ggs

        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.

        WARNING: Tablespace TBS_GGS does not have AUTOEXTEND enabled.

        Using GGS 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 GGS

        CLEAR_TRACE STATUS:

        Line/pos             Error
        -------------------- -----------------------------------------------------------
        ------
        No errors            No errors
        .........
        SUCCESSFUL installation of DDL Replication software components

        Script complete.
        SQL>        
        
        #第三个sql
        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 scri
        pt 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:ggs
        已写入 file role_setup_set.txt

        PL/SQL 过程已成功完成。


        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 ggs;

        授权成功。
        SQL>        
        
        
        #其他sql脚本
        SQL> @ddl_enable.sql
        SQL> @?/rdbms/admin/dbmspool.sql
        SQL> @ddl_pin.sql ggs
        SQL>        
        
        
2.source端修改extract进程的params文件，添加"ddl include all"参数，重启extract进程        
    2.1 查看添加后的参数
        GGSCI (WIN-GM5PVS1CILH) 40> view param eora_t1
        extract eora_t1
        setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
        ddl include all
        userid ggs@orcl_w,password ggs
        exttrail dirdat/sp
        table scott.*;

    2.2 重启 EXTRACT 进程
        GGSCI (WIN-GM5PVS1CILH) 41> stop extract eora_t1

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


        GGSCI (WIN-GM5PVS1CILH) 42> start extract eora_t1

        Sending START request to MANAGER ('GGSMGR') ...
        EXTRACT EORA_T1 starting

    2.3 查看进程状态
        GGSCI (WIN-GM5PVS1CILH) 43> info all

        Program     Status      Group       Lag at Chkpt  Time Since Chkpt

        MANAGER     RUNNING
        EXTRACT     RUNNING     EORA_T1     00:00:00      00:00:01
        EXTRACT     RUNNING     PORA_T1     00:00:00      00:00:06


        GGSCI (WIN-GM5PVS1CILH) 44> info extract eora_t1

        EXTRACT    EORA_T1   Last Started 2016-12-14 11:07   Status RUNNING
        Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
        Process ID           2804
        Log Read Checkpoint  Oracle Redo Logs
                             2016-12-14 11:07:41  Seqno 7, RBA 18905600
                             SCN 0.1013586 (1013586)


        GGSCI (WIN-GM5PVS1CILH) 45>    
        
        
3. target端修改replicat进程的params文件，添加"ddl include all"和"ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程        

    3.1 查看添加后的参数        
        GGSCI (Oracle02 as ggt@orcl) 11> edit param rora_t1
        GGSCI (Oracle02 as ggt@orcl) 12> view param rora_t1

        replicat rora_t1  
        setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)  
        ddl include all
        ddlerror default ignore retryop maxretries 3 retrydelay 5 
        userid ggt,password ggt  
        handlecollisions  
        assumetargetdefs  
        discardfile dirrpt/rora_t1.dsc,purge  
        map scott.* ,target scott.*;

    3.2 重启 REPLICAT 进程
        GGSCI (Oracle02 as ggt@orcl) 13> stop replicat rora_t1
        REPLICAT RORA_T1 is already stopped.

        GGSCI (Oracle02 as ggt@orcl) 14> start replicat rora_t1

        Sending START request to MANAGER ...
        REPLICAT RORA_T1 starting

    3.3 查看进程状态
        GGSCI (Oracle02 as ggt@orcl) 15> info all
        Program     Status      Group       Lag at Chkpt  Time Since Chkpt

        MANAGER     RUNNING                                           
        REPLICAT    RUNNING     RORA_T1     00:00:00      00:00:02    

        GGSCI (Oracle02 as ggt@orcl) 16> info replicat rora_t1
        REPLICAT   RORA_T1   Last Started 2016-12-14 11:13   Status RUNNING
        Checkpoint Lag       00:16:48 (updated 00:00:00 ago)
        Process ID           27403
        Log Read Checkpoint  File dirdat/rp000000006
                             2016-12-14 10:57:03.302367  RBA 4347
        GGSCI (Oracle02 as ggt@orcl) 17>         
        
4.测试                
        SQL> conn scott/scott
        已连接。
        SQL> select table_name from user_tables;

        TABLE_NAME
        ------------------------------
        T1
        SALGRADE
        BONUS
        EMP
        DEPT

        SQL> create table t2 as select object_id,object_name from dba_objects;
        表已创建。
        SQL>        
            
        #在目标主机验证    
        SQL> conn scott/scott@orcl_L
        已连接。
        SQL> select table_name from user_tables;

        TABLE_NAME
        ------------------------------
        T2
        T1
        SALGRADE
        BONUS
        EMP
        DEPT

        已选择6行。

        SQL>