golden-gate DDL

 GoldenGate配置（三）之DDL复制配置
标签： OracleGoldenGate
2014-06-22 23:23 1097人阅读 评论(0) 收藏 举报
 分类： Oracle_GoldenGate（11）  
版权声明：本文为博主原创文章，未经博主允许不得转载。
目录(?)[+]
GoldenGate配置（三）之DDL复制配置
【说明】
此篇续接上一篇：“GoldenGate配置（二）之双向复制配置”点击打开链接
环境：
Item
Source System
Target System
Platform
Red Hat Enterprise
Linux Server release 5.4
Red Hat Enterprise
Linux Server release 5.4
Hostname
gc1
gc2
Database
Oracle 10.2.0.1
Oracle 11.2.0.1
Character Set
ZHS16GBK
ZHS16GBK
ORACLE_SID
PROD
EMREP
Listener Name/Port
LISTENER/1521
LISTENER/1521
Goldengate User
ogg
ogg




DDL复制配置
gc1：执行脚本（注意：要到/u01/app/ogg目录下执行，否则会被hang住，oracle的bug）
SQL>@marker_setup.sql;  --到/u01/app/ogg目录后登陆sqlplus执行
Marker setup script
 
You will be prompted for the name of a schema forthe GoldenGate database objects.
NOTE: The schema must be created prior to runningthis script.
NOTE: Stop all DDL replication before startingthis installation.
 
Enter GoldenGate schema name:ogg
 
Marker setup table script complete, runningverification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to OGG
 
MARKER TABLE
-------------------------------
OK
 
MARKER SEQUENCE
-------------------------------
OK
 
Script complete.
SQL> alter system set recyclebin=off scope=spfile;    --关闭回收站
SQL> startup force;                                 --重启库
SQL> @ddl_setup
GoldenGateDDL Replication setup script
 
Verifyingthat current user has privileges to install DDL Replication...
 
You willbe prompted for the name of a schema for the GoldenGate database objects.
NOTE:The schema must be created prior to running this script.
NOTE: OnOracle 10g and up, system recycle bin must be disabled.
NOTE:Stop all DDL replication before starting this installation.
 
EnterGoldenGate schema name:ogg
 
You willbe prompted for the mode of installation.
Toinstall or reinstall DDL replication, enter INITIALSETUP
Toupgrade DDL replication, enter NORMAL
Entermode of installation:INITIALSETUP
 
Working,please wait ...
Spoolingto file ddl_setup_spool.txt
 
 
UsingOGG as a GoldenGate schema name, INITIALSETUP as a mode of installation.
 
Working,please wait ...
 
RECYCLEBINmust be empty.
Thisinstallation will purge RECYCLEBIN for all users.
To proceed,enter yes. To stop installation, enter no.
 
Enteryes or no:yes
SQL>@role_setup
GGS Role setup script
 
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit this scriptand then edit the params.sql script to change the gg_role parameter to thepreferred name. (Do not run the script.)
 
You will be prompted for the name of a schema forthe GoldenGate database objects.
NOTE: The schema must be created prior to runningthis script.
NOTE: Stop all DDL replication before startingthis 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 theExtract, GGSCI, and Manager processes, by using the following SQL command:
 
GRANT GGS_GGSUSER_ROLE TO <loggedUser>
 
where <loggedUser> is the user assigned tothe GoldenGate processes.
SQL> grant GGS_GGSUSER_ROLE to ogg;
SQL> @ddl_enable
Trigger altered.
 
 
gc2：执行脚本（同gc1上操作）
SQL>alter system set recyclebin=off scope=spfile;    --关闭回收站
SQL>startup force;                                        --重启库
SQL>@marker_setup
SQL>@ddl_setup
SQL>@role_setup
SQL>grant GGS_GGSUSER_ROLE to ogg;
SQL>@ddl_enable
 
gc1：配置Extract进程
GGSCI(gc1) 3> STOP EORA_1
Sending STOP request to MANAGER ...
Request Processed.
 
GGSCI(gc1) 4> EDIT PARAMS EORA_1 
添加红字部分:
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD Welcome1
EXTTRAIL ./dirdat/aa
DDL INCLUDE OBJNAME "scott.*"
TABLE scott.TCUSTMER;
TABLE scott.TCUSTORD;
 
GGSCI(gc1) 5> START EORA_1
Sending START request to MANAGER ...
EXTRACT EINI_1 starting
 
gc2：配置extract
GGSCI(gc2) 3> stop EORA_1          --关闭EORA_1进程
Sending STOP request to MANAGER ...
Request Processed.
GGSCI(gc2) 4> EDIT PARAMS EORA_1   --添加红字部分
-- Change Capture parameter file to capture
-- TCUSTMER and TCUSTORD changes
EXTRACT EORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD Welcome1
EXTTRAIL ./dirdat/aa
DDL INCLUDE OBJNAME "scott.*"
TABLE scott.TCUSTMER;
TABLE scott.TCUSTORD;
GGSCI(gc2) 5> START EORA_1
Sending START request to MANAGER ...
EXTRACT EINI_1 starting
 
gc1:配置Replicat进程
GGSCI(gc1) 9> stop RORA_1
Sending STOP request to REPLICAT RORA_1 ...
Request processed.
GGSCI(gc1) 10> info all
Program    Status      Group       Lag          Time Since Chkpt
MANAGER    RUNNING                                          
EXTRACT    RUNNING     EORA_1      00:00:00      00:00:07   
EXTRACT    RUNNING     PORA_1      00:00:00      00:00:02   
REPLICAT    STOPPED     RORA_1     00:00:00      00:00:31    
GGSCI(gc1) 11> EDIT PARAMS RORA_1   --添加红色部分
DDLERROR DEFAULT IGNORE RETRYOP
--
-- Change Delivery parameter file to apply
-- TCUSTMER and TCUSTORD Changes
--
REPLICAT RORA_1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg, PASSWORD Ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.tcustmer, TARGET scott.tcustmer;
MAP scott.tcustord, TARGET scott.tcustord;
~
"dirprm/rora_1.prm" 16L, 458C written
GGSCI(gc1) 12> start RORA_1
Sending START request to MANAGER ...
REPLICAT RORA_1 starting
GGSCI(gc1) 13> info all
Program    Status      Group       Lag          Time Since Chkpt
MANAGER    RUNNING                                           
EXTRACT    RUNNING     EORA_1      00:00:00      00:00:06   
EXTRACT    RUNNING     PORA_1      00:00:00      00:00:02   
REPLICAT    RUNNING     RORA_1     00:00:00      00:00:02    
 
gc2:配置Replicate进程
配置过程，同gc1
 
验证ddl:create操作
gc1：
SQL>create table test1 as select * from emp;
Table created.
gc2：
SQL>select * from test1;
     EMPNOENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO
---------- ---------- --------- ---------------------- ---------- ---------- ----------
      7369SMITH      CLERK           7902 17-DEC-80           800                    20
      7499ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      ......
      7902FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934MILLER     CLERK           7782 23-JAN-82          1300                    10
14 rows selected.
gc2:
SQL>create table test2 as select * from emp;
Table created.
gc1:
SQL>select * from test2;
     EMPNOENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO
---------- ---------- --------- ---------------------- ---------- ---------- ----------
      7369SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      ......
      7902FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934MILLER     CLERK           7782 23-JAN-82          1300                    10
14 rows selected.
 
 
验证ddl:alter操作
gc1:
SQL>alter table test1 rename column mgr to manager;
Table altered.
gc2:
SQL>select * from test1;
     EMPNOENAME      JOB          MANAGER HIREDATE            SAL       COMM    DEPTNO
---------- ---------- --------- ---------------------- ---------- ---------- ----------
      7369SMITH      CLERK           7902 17-DEC-80           800                    20
      7499ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      ......
gc2:
SQL>alter table test1 rename column manager to mgr;
Table altered.
gc1:
SQL>select * from test1;
     EMPNOENAME      JOB              MGR HIREDATE            SAL       COMM    DEPTNO
---------- ---------- --------- ---------------------- ---------- ---------- ----------
      7369SMITH      CLERK           7902 17-DEC-80           800                    20
      7499ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566JONES      MANAGER         7839 02-APR-81          2975                    20
      ......
 
验证ddl:drop操作
gc2:
SQL>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TCUSTMER                       TABLE
TCUSTORD                       TABLE
TEST1                         TABLE
TEST2                          TABLE
8 rows selected.
gc1:
SQL> drop table test1;
Table dropped.
gc2:
SQL>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TCUSTMER                       TABLE
TCUSTORD                       TABLE
TEST2                          TABLE
7 rows selected.
gc1:
SQL>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TCUSTMER                       TABLE
TCUSTORD                       TABLE
TEST2                          TABLE
7 rows selected.
gc2:
SQL>drop table test2;
Table dropped.
SQL>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TCUSTMER                       TABLE
TCUSTORD                       TABLE
6 rows selected.