DML同步参考Oracle GoldenGate for Oracle 11g(Solaris 10 x86-64) to Oracle 10g(CentOS 4.8) configuration
关闭源端数据库的recyclebin,如果你是Oracle11g,可以不用关闭
-
[oracle@gg01 ~]$ sqlplus / AS sysdba
-
-
SQL*Plus: Release 10.2.0.1.0 - Production ON Sun Jan 15 12:49:39 2012
-
-
Copyright (c) 1982, 2005, Oracle. ALL rights reserved.
-
-
-
Connected TO:
-
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.1.0 - Production
-
WITH the Partitioning, OLAP AND DATA Mining options
-
-
SYS@gg01:~>SHOW parameter recyclebin
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
recyclebin string ON
-
SYS@gg01:~>ALTER system SET recyclebin=off;
-
-
System altered.
-
-
SYS@gg01:~>SHOW parameter recyclebin
-
-
NAME TYPE VALUE
-
------------------------------------ ----------- ------------------------------
-
recyclebin string OFF
创建DDL复制用户
-
SYS@gg01:~>CREATE USER ggadmin IDENTIFIED BY ggadmin DEFAULT tablespace users;
-
-
USER created.
-
-
SYS@gg01:~>GRANT dba TO ggadmin;
-
-
GRANT succeeded.
编辑ogg的globals参数,指定DDL用户
-
GGSCI (gg01) 1> edit params ./GLOBALS
-
GGSCI (gg01) 3> VIEW params ./GLOBALS
-
-
ggschema ggadmin
安装DDL对象,运行marker_setup.sql脚本,提示输入OGG用户,这里是之前定义的ggadmin
-
SYS@gg01:~>@marker_setup
-
-
Marker setup 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:ggadmin
-
-
-
Marker setup TABLE script complete, running verification script...
-
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
-
Setting schema name TO GGADMIN
-
-
MARKER TABLE
-
-------------------------------
-
OK
-
-
MARKER SEQUENCE
-
-------------------------------
-
OK
-
-
Script complete.
运行ddl_setup.sql脚本,提示输入用户:ggadmin
创建DDL复制角色,运行role_setup.sql脚本
-
SYS@gg01:~>@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:ggadmin
-
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.
-
SYS@gg01:~>
开启DDL功能,运行ddl_enable.sql脚本
-
SYS@gg01:~>@ddl_enable
-
-
TRIGGER altered.
验证DDL脚本的安装,运行marker_status.sql脚本
-
SYS@gg01:~>@marker_status
-
Please enter the name OF a schema FOR the GoldenGate DATABASE objects:
-
ggadmin
-
Setting schema name TO GGADMIN
-
-
MARKER TABLE
-
-------------------------------
-
OK
-
-
MARKER SEQUENCE
-
-------------------------------
-
OK
-
SYS@gg01:~>
配置DDL复制,修改extract参数
-
GGSCI (gg01) 5> VIEW params eorajj
-
-
EXTRACT EORAJJ
-
USERID system, PASSWORD oracle
-
RMTHOST gg02, MGRPORT 7809
-
RMTTRAIL /u01/app/oracle/goldengate/dirdat/jj
-
-
DDL INCLUDE ALL
-
--DDLERROR RESTARTSKIP 100000 SKIPTRIGGERERROR 100000
-
DDLOPTIONS ADDTRANDATA,REPORT
-
-
TABLE scott.*;
-
SEQUENCE scott.*;
-
-
-
GGSCI (gg01) 6>
修改replicat参数
-
REPLICAT RORAJJ
-
USERID system, PASSWORD oracle
-
HANDLECOLLISIONS
-
ASSUMETARGETDEFS
-
DISCARDFILE /u01/app/oracle/goldengate/dirrpt/RORAJJ.DSC, PURGE
-
-
DDL INCLUDE MAPPED
-
--DDLERROR DEFAULT IGNORE RETRYOP
-
DDLOPTIONS REPORT
-
-
MAP scott.*, TARGET scott.*;
分别启动源端extract和目标端replicat进程
-
GGSCI (gg01) 15> START EXTRACT eorajj
-
-
Sending START request TO MANAGER ...
-
EXTRACT EORAJJ starting
-
-
-
GGSCI (gg01) 16> info ALL
-
-
Program STATUS GROUP Lag TIME Since Chkpt
-
-
MANAGER RUNNING
-
EXTRACT RUNNING EORAJJ 00:00:00 00:14:39
-
GGSCI (gg02) 7> START replicat rorajj
-
-
Sending START request TO MANAGER ...
-
REPLICAT RORAJJ starting
-
-
-
GGSCI (gg02) 8> info ALL
-
-
Program STATUS GROUP Lag TIME Since Chkpt
-
-
MANAGER RUNNING
-
REPLICAT RUNNING RORAJJ 00:00:00 00:00:01
先验证DML
源端
-
SCOTT@gg01:~>INSERT INTO bonus VALUES('ZWC','10','1000',10);
-
-
1 ROW created.
-
-
SCOTT@gg01:~>commit;
-
-
Commit complete.
-
-
SCOTT@gg01:~>
目标段
-
SCOTT@gg02:~>SELECT * FROM bonus WHERE ename='ZWC';
-
-
ENAME JOB SAL COMM
-
---------- --------- ---------- ----------
-
ZWC 10 1000 10
-
-
SCOTT@gg02:~>
验证DDL
-
SCOTT@gg01:~>CREATE TABLE t_zwc(tid INTEGER PRIMARY KEY,tname VARCHAR(10));
-
-
TABLE created.
-
-
SCOTT@gg01:~>DESC t_zwc
-
Name NULL? TYPE
-
----------------------- -------- ----------------
-
TID NOT NULL NUMBER(38)
-
TNAME VARCHAR2(10)
-
-
SCOTT@gg01:~>SELECT tname FROM tab;
-
-
TNAME
-
------------------------------
-
DEPT
-
EMP
-
BONUS
-
SALGRADE
-
T_ZWC
-
-
5 ROWS selected.
目标端查看
-
SCOTT@gg02:~>SELECT tname FROM tab;
-
-
TNAME
-
------------------------------
-
DEPT
-
EMP
-
BONUS
-
SALGRADE
-
T_ZWC
-
-
SCOTT@gg02:~>SET linesize 50
-
SCOTT@gg02:~>DESC t_zwc
-
Name NULL? TYPE
-
----------------------- -------- ----------------
-
TID NOT NULL NUMBER(38)
-
TNAME VARCHAR2(10)
验证存储过程
-
SCOTT@gg01:~>CREATE OR REPLACE PROCEDURE ddl_test
-
2 IS
-
3 BEGIN
-
4 NULL;
-
5 END ddl_test;
-
6 /
-
-
PROCEDURE created.
目标端
-
SCOTT@gg02:~>SELECT DISTINCT name FROM user_source WHERE TYPE='PROCEDURE';
-
-
NAME
-
------------------------------
-
DDL_TEST
-
-
SCOTT@gg02:~>SET pages 100
-
SCOTT@gg02:~>SELECT text FROM user_source;
-
-
TEXT
-
--------------------------------------------------------------------------------
-
PROCEDURE "DDL_TEST"
-
IS
-
BEGIN
-
NULL;
-
END ddl_test; /* GOLDENGATE_DDL_REPLICATION */
验证sequence
-
SCOTT@gg01:~>CREATE SEQUENCE seq_ddl START WITH 1 INCREMENT BY 2 maxvalue 99999;
-
-
SEQUENCE created.
目标端
-
SCOTT@gg02:~>SELECT sequence_name,max_value,increment_by FROM user_sequences WHERE sequence_name=UPPER('seq_ddl');
-
-
SEQUENCE_NAME MAX_VALUE INCREMENT_BY
-
--------------- ---------- ------------
-
SEQ_DDL 99999 2