数据库： ORACLE 11.2.0.4
OGG： 12.1.2.1.0
1、数据库开启归档
SQL>
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
2、打开数据库级别的补充日志
SQL>
select supplemental_log_data_min from v$database; --YES表示已经启用数据库级补充日志
alter database add supplemental log data;
alter database force logging;
3、设置数据enable_goldengate_replication 为真
alter system set enable_goldengate_replication=true;
4、创建 GoldenGate管理用户
SQL>
create user ggs identified by ggs ;
grant connect,resource ,unlimited tablespace to ggs;
grant execute on utl_file to ggs;
以上只是级别权限，在源、目标两端都要执行。
另外，在源端还需要赋予GGS用户以下权限：
SQL>
grant connect,resource to ggs;
grant select any dictionary,select any table to ggs;
grant alter any table to ggs;
grant flashback any table to ggs;
--grant execute on dbms_flahback to ggs; # 在 ORACLE 11.2.0.4上不需要执行该grant
在目标端需要授予GGS 用户以下权限：
SQL>
grant insert any table to ggs;
grant delete any table to ggs;
grant update any table to ggs;
如果对权限要求不严格，最简单的办法就是直接授予GoldenGate管理用户dba权限。
SQL> grant dba to ggs;
5、添加表级 trandata(源库)
这里的表级 trandata 就是指标表级的 supplemental log
SQL>
create table demo(id number primary key,ename varchar2(10));
GGSCI>
dblogin userid ggs,password ggs
add trandata lixia.demo
提示：
在对标添加 trandata的时候，表名可以使用通配符。例如如果要添加 lixia 用户下的所有表，
则语句可以这样写：
GGSCI> add trandata lixia.*
6、端添加 checkpoint 表(DB1和DB2都要执行)
GGSCI>
dblogin userid ggs,password ggs
edit params ./GLOBALS
checkpointtable ggs.ggs_checkpoint
GGSCI>
add checkpointtable ggs.ggs_checkpoint
7、指定 DB1 进行DDL复制的用户，要求DBA权限
GGSCI (fmsserver) 2> edit params ./GLOBALS
checkpointtable ggs.ggs_checkpoint
ggschema ggs --使用GGS用户进行DDL复制，要在数据库中给GGS用户赋予DBA权限
8、指定DB2 进行复制的用户
GGSCI (fmsserver) 2> edit params ./GLOBALS
checkpointtable ggs.ggs_checkpoint
ggschema ggs
--在数据库中为GGS用户赋予DBA权限（DB1和DB2都要执行）
SQL> grant dba to ggs;
9、如果是 ORACLE 10G的数据库还需要关闭回收站（DB1、DB2都要执行），11G可以不用关闭回收站
SQL>
alter system set recyclebin=off scope=both;
10、 源端数据库安装DDL 对象（DB1、DB2都要执行）
SQL> create tablespace ggs datafile '/app/oracle/oradata/orcl/ggs01.dbf' size 2G;
SQL> alter user ggs default tablespace ggs;
以下所有脚本都在 /app/gg2目录（OGG的安装目录）下执行，所有的脚本的goldengate schema选择GGS,
Enter Oracle GoldenGate schema name:ggs
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> @ddl_enable.sql
--验证DLL安装的状态
SQL> @marker_status.sql
Please enter the name of a schema for the GoldenGate database objects:
ggs
Setting schema name to GGS
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
11、配置DB1抽取进程执行DDL复制
GGSCI (oradb) 2> view params EIEX01
extract eiex01
userid ggs,password ggs
tranlogoptions excludeuser ggs --避免出现死循环复制
exttrail ./dirdat/ts
ddl include objname lixia.* & --注意这里必须要有与字符号('&')，表示两个
include objname test.* --include objname是属于同一个DDL配置项
--ddl include all --不建议使用 ddl include all，这样会复制所有模式下的DDL，包括SYS/SYSTEM的DDL
table lixia.demo;
table lixia.t10;
ggsci>
add extract eiex01,tranlog,begin now
add exttrail ./dirdat/ts,extract eiex01,megabytes 5
12、配置DB2抽取进程执行DDL复制
GGSCI (fmsserver) 3> edit params EIEX01
extract eiex01
userid ggs,password ggs
tranlogoptions excludeuser ggs --避免出现死循环复制
exttrail ./dirdat/tt
ddl include objname lixia.* &
include objname test.*
table lixia.demo;
table lixia.t10;
ggsci>
add extract eiex01,tranlog,begin now
add exttrail ./dirdat/tt,extract eiex01,megabytes 5
13、配置DB1 的投递进程
GGSCI (oradb) 22> view params DPMP01
extract dpmp01
passthru
rmthost 192.168.222.154,mgrport 7809
rmttrail ./dirdat/td
--exttrail ./dirdat/tt
table lixia.*;
add extract dpmp01,exttrailsource ./dirdat/ts
add rmttrail ./dirdat/td,extract dpmp01,megabytes 5
13、配置DB2 的投递进程
GGSCI (fmsserver) 14> view params DPMP01
extract dpmp01
passthru
rmthost 192.168.222.156,mgrport 7809
rmttrail ./dirdat/tt
--exttrail ./dirdat/tt
table lixia.*;
add extract dpmp01,exttrailsource ./dirdat/tt
add rmttrail ./dirdat/tt,extract dpmp01,megabytes 5
14、配置DB2复制进程支持DDL复制
GGSCI (fmsserver) 3> view params REPL
replicat repl
userid ggs,password ggs
HANDLECOLLISIONS
ASSUMETARGETDEFS
discardfile ./dirrpt/repl.dec,purge
--ddl include all
ddl include objname lixia.* &
include objname test.*
map lixia.demo,target lixia.demo;
map lixia.t10,target lixia.t10;
ggsci>
dblogin userid ggs,password ggs
add replicat repl,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/td
15、配置DB1复制进程支持DDL复制
GGSCI (oradb) 12> edit params repl
replicat repl
userid ggs,password ggs
HANDLECOLLISIONS
ASSUMETARGETDEFS
discardfile ./dirrpt/repl.dec,purge
ddl include objname lixia.* &
include objname test.*
map lixia.demo,target lixia.demo;
map lixia.t10,target lixia.t10;
ggsci>
dblogin userid ggs,password ggs
add replicat repl,checkpointtable ggs.ggs_checkpoint,exttrail ./dirdat/tt
16、重启OGG进程
16.1 关闭DB1的OGG进程
GGSCI (oradb) 13> stop *
Sending STOP request to EXTRACT DPMP01 ...
Request processed.
Sending STOP request to EXTRACT EIEX01 ...
Request processed.
REPLICAT REPL is already stopped.
GGSCI (oradb) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPMP01 00:00:00 00:00:02
EXTRACT STOPPED EIEX01 00:00:00 00:00:01
REPLICAT STOPPED REPL 00:00:00 313:58:59
GGSCI (oradb) 16> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
16.2 关闭DB2的OGG 进程
GGSCI (fmsserver) 5> stop *
EXTRACT DPMP01 is already stopped.
EXTRACT EIEX01 is already stopped.
Sending STOP request to REPLICAT REPL ...
Request processed.
REPLICAT REPL2 is already stopped.
GGSCI (fmsserver) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPMP01 00:00:00 116:11:08
EXTRACT STOPPED EIEX01 00:00:00 116:15:47
REPLICAT STOPPED REPL 00:00:00 00:00:01
REPLICAT ABENDED REPL2 00:00:00 296:26:04
GGSCI (fmsserver) 7> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y
Sending STOP request to MANAGER ...
Request processed.
Manager stopped.
17、启动OGG进程
17.1 启动DB1的OGG进程
GGSCI (oradb) 17> start mgr
GSCI (oradb) 19> start *
GGSCI (oradb) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPMP01 00:00:00 00:01:53
EXTRACT RUNNING EIEX01 00:01:58 00:00:05
REPLICAT RUNNING REPL 00:00:00 314:00:50
17.2 启动DB2的OGG进程
GGSCI (fmsserver) 9> start mgr
GGSCI (fmsserver) 11> start *
GGSCI (fmsserver) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPMP01 00:00:00 00:00:09
EXTRACT RUNNING EIEX01 00:00:00 00:00:07
REPLICAT RUNNING REPL 00:00:00 00:00:02
18、测试结果
18.1 在DB1中创建测试表
SQL> create table test.ddl_t1 (id number,name varchar2(30));
Table created.
18.2 在DB2中查看DDL复制成功
SQL> select table_name from dba_tables where owner='TEST';
TABLE_NAME
------------------------------
DDL_T1
18.3 在DB2中删除test.ddl_t1
SQL> drop table test.ddl_t1;
Table dropped.
18.4 在DB1中查看DDL复制成功，test.ddl_t1表已经被删除
SQL> select table_name from dba_tables where owner='TEST';
no rows selected
19、下面测试表的修改操作
19.1 在DB1创建 test.ddl_t1表
SQL> create table test.ddl_t1 (id number,name varchar2(30));
Table created.
19.2 在DB2中为test.ddl_t1添加字段
SQL> alter table test.ddl_t1 add(class varchar2(50));
Table altered.
19.3 在DB1中查看DDL复制成功
SQL> desc test.ddl_t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
CLASS VARCHAR2(50)
19.4 在DB1 中删除 ddl_t1的CLASS字段
SQL> alter table test.ddl_t1 drop column class;
Table altered.
19.5 在DB2中检查删除CLASS字段的DDL复制成功
SQL> desc test.ddl_t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)