第一次安装，不管是源端还是目标端进程都正常，但是就是不同步，后来把ogg用户删掉，把ogg安装目录都删掉，并且把目标端的附加日志给删除掉（第一次安装把目标端也添加了附加日志，不知道是不是因为这里导致失败的）重新按着下面的过程安装了一次成功了

在开启ogg的时候要保证源端和目标端的数据一致，也就是初始化数据库，oracle-oracle可以用数据泵完成，oracle-mysql则要通过ogg特有的配置完成，具体步骤参考笔记：网上down的ogg文档

配置DDL的时候，也是按着步骤完成后，一切都正常，错误日志也没有报错，就是不同步，后来网上有人说目标端不需要配置ddl include all ,于是我在目标端去掉replicate的ddl include all，然后我给了源端ogg 用户dba权限，并且把进程都重启了一下，就正常了，可是我不知道是哪个原因的过，控制变量法得到的结论是这些修改不是他后来成功的原因，到现在我都不知道，错误原因在哪里。

一：首先两端都要安装好oracle数据库软件，并且创建好库，这个过程省略。。。

二：配置oracle环境变量，源端和目标端一样，

[oracle@master2 ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=wenhe2
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export GGATE=/u01/OGG_linux/ggs
alias sqlplus='rlwrap sqlplus'

注意：

1,可以通过这个语句来查看NLS_LANG

SQL> select userenv('language') nls_lang from dual;

NLS_LANG

----------------------------------------------------

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2， GGATE=/u01/OGG_linux/ggs 这个路径是你将要安装ogg软件的路径。

3，设置LD_LIBRARY_PATH，为了安装OGG所用的动态链接库。如果没有配置这个路径的话，在安装OGG的过程
中会报找不到动态链接库的错误，

二安装ogg软件，具体操作如下，源端和目标端都要安装。

源端：

[oracle@master2 ggs]$ cd /u01/OGG_linux/ggs

[oracle@master2 ggs]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

Archive:  ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
  inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
  inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
  inflating: Oracle GoldenGate 11.2.1.0.1 README.txt
  inflating: Oracle GoldenGate 11.2.1.0.1 README.doc
[oracle@master2 ggs]$ ll
总计 310956
-rw-rw-r-- 1 oracle oinstall 228556800 2012-04-23 fbo_ggs_Linux_x64_ora11g_64bit.tar
-rw-r--r-- 1 oracle oinstall  89186858 01-15 20:31 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
-rwxrwxrwx 1 oracle oinstall    220546 2012-05-02 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf
-rwxrwxrwx 1 oracle oinstall     93696 2012-05-02 Oracle GoldenGate 11.2.1.0.1 README.doc
-rwxrwxrwx 1 oracle oinstall     24390 2012-05-02 Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@master2 ggs]$ tar -xf fbo_ggs_Linux_x64_ora11g_64bit.tar

测试安装成功，只需进入命令行，就表明安装是成功的。

[oracle@gg1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02

GGSCI (gg1) 1>

GGSCI (gg1) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

注意：

表示安装成功。

目标端和源端一样，省略。

三：创建ogg专用的schema，表空间，并且授权,

SQL>create tablespace ogg_data datafile '/u01/app/oracle/oradata/ogg_data.dbf' size 1000M autoextend on;

SQL> create user ogg identified by ogg default tablespace ogg_data temporary tablespace TEMP ;

grant connect,resource to ogg;

grant create session,alter session to ogg;

grant select any dictionary,select any table to ogg;

grant alter any table to ogg;

grant flashback any table to ogg;

grant execute on dbms_flashback to ogg;

四：修改源端数据库配置

源端：

添加附加日志来唯一标识一行记录，要在数据库级别打开最小开关。为了减少整个数据库添加附加日志，以及减少归档量，

goldengate建议复制哪些对象，就添加哪些表的附加日志（我们到时候是拿scott用户下的表来实验）。

检查：

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

打开：

SQL> alter database add supplemental log data;

Database altered.

再次查看：

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

切换日志组，使附加日志开关生效。

SQL> alter system switch logfile;

System altered.

解释：
备注：在oracle中可以通过rowid来定位某条记录，但是目标端的数据库和源端数据库的数据库可能完全不一样，所以无法通过rowid来确定源端数据库的逻辑变化，这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后，对于源端的修改操作，oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。

四：配置ogg

源端：

[oracle@master2 ggs]$ cd /u01/OGG_linux/ggs

[oracle@master2 ggs]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

1，GGSCI (master2) 1> create subdirs

Creating subdirectories under current directory /u01/OGG_linux/ggs

Parameter files /u01/OGG_linux/ggs/dirprm: already exists

Report files /u01/OGG_linux/ggs/dirrpt: created

Checkpoint files /u01/OGG_linux/ggs/dirchk: created

Process status files /u01/OGG_linux/ggs/dirpcs: created

SQL script files /u01/OGG_linux/ggs/dirsql: created

Database definitions files /u01/OGG_linux/ggs/dirdef: created

Extract data files /u01/OGG_linux/ggs/dirdat: created

Temporary files /u01/OGG_linux/ggs/dirtmp: created

Stdout files /u01/OGG_linux/ggs/dirout: created,

2，登录数据库

GGSCI (master2) 3> dblogin userid ogg,password ogg

Successfully logged into database.

3，添加需要同步的用户到ogg

开启scott用户下所有表的附加日志

GGSCI (WebServer) 3> add trandata scott.*

2013-03-08 11:02:33 WARNING OGG-00869 No unique key is defined for table 'BONUS'.
All viable columns will be used to represent the key, but may not guarantee
uniqueness. KEYCOLS may be used to
define the key.

Logging of supplemental redo data enabled
for table SCOTT.BONUS.

Logging of supplemental redo data enabled
for table SCOTT.DEPT.
Logging of supplemental redo data enabled
for table SCOTT.EMP.

2013-03-08 11:02:34 WARNING OGG-00869 No unique key is defined for table
'SALGRADE'. All viable columns will be used to represent the key, but may not
guarantee uniqueness. KEYCOLS may be
used to define the key.

Logging of supplemental redo data enabled
for table SCOTT.SALGRADE.

4，GGSCI (WebServer) 4> info trandata scott.*

Logging of supplemental redo log data is
enabled for table SCOTT.BONUS.

Columns supplementally logged for table
SCOTT.BONUS: ENAME, JOB, SAL, COMM.

Logging of supplemental redo log data is
enabled for table SCOTT.DEPT.
Columns supplementally logged for table
SCOTT.DEPT: DEPTNO.

Logging of supplemental redo log data is
enabled for table SCOTT.EMP.

Columns supplementally logged for table
SCOTT.EMP: EMPNO.
Logging of supplemental redo log data is
enabled for table SCOTT.SALGRADE.

Columns supplementally logged for table
SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

3，GGSCI (master2) 3>edit params mgr

PORT 7839

4，GGSCI (master2) 3> start mgr

5，GGSCI (master2) 3>info mgr

Manager is running (IP port
WebServer.7839).

6，添加抓取进程起名字为ext_liu

GGSCI (master2) 3>add extract ext_liu, tranlog, begin now

EXTRACT added.

参数分析：
tranlog：表示数据抓取的来源是数据库的redo数据。
begin now：表示我们在启动这个抓取进程的就去抓取数据。
threads 1：表示我们数据库有多少个redo threads，单实例基本上是1或者不设，rac就自己设置了，
配置过RAC的基本都了解。

7，编辑刚才添加的抓取进程

GGSCI (master2) 3>edit params ext_liu

EXTRACT ext_liu

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

userid ogg,password ogg

exttrail ./dirdat/h1,megabytes 100

TABLE scott.*;

分析：

setenv (ORACLE_SID=ogg)：指定oracle的sid，单机环境下可以不设或者bashprofile文件中设置了默认的

也可以不设，但是集群环境就要指定了，这里我们强烈建议指定。

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)：源端数据库的字符集，一定要跟数据库中匹配。

userid ogg,password ogg：告诉抓取进程是通过这个用户登陆数据库的。

exttrail ./dirdat/h1,megabytes 100：与我们上面配置抓取进程的设置要一致，路径和大小嘛。

TABLE SCOTT.*：最重要的，告诉抓取进程抓取的是哪些用户哪些表的数据。

8,添加源端的队列文件。
GGSCI (gg1) 15> add EXTTRAIL ./dirdat/h1, extract ext_liu, megabytes 100
EXTTRAIL added.

分析：
./dirdat：表示trail文件的目录
h1：trail文件的前缀
extract ext_demo：值指定给那个进程用的（ext_demo）。
megabytes 100：文件大小是100m

9，配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯。
GGSCI (gg1) 31> add extract dpe_liu, exttrailsource ./dirdat/h1 ##h1是源端设置的trails 文件的格式
EXTRACT added.

10

,配置datapump进程参数：

GGSCI (gg1) 34> edit param dpe_liu

extract dpe_liu

dynamicresolution

passthru

rmthost 192.168.9.25, mgrport 7839, compress

rmttrail ./dirdat/h1

numfiles 5000

TABLE scott.*;

输出：目标主机怎么写，也是定义datapumo进程的输出。
GGSCI (gg1) 33> add rmttrail ./dirdat/h1, EXTRACT dpe_liu,MEGABYTES 100
RMTTRAIL added.

分析：
exttrailsource：抓取进程的来源，因为这个进程不否则到数据库中取抓取，所以抓取的来源
是通过抓取进程已经生成好的trail文件中的内容。

10，开启添加的进程

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT STOPED DPE_LIU 00:00:00 00:00:02

EXTRACT STOPED EXT_LIU 00:00:00 00:00:04

GGSCI (master2) 4> start DPE_LIU

GGSCI (master2) 4> start EXT_LIU

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXT_LIU 00:00:00 00:00:02

EXTRACT RUNNING DPE_LIU 00:00:00 00:00:04

目标端：

创建ogg的用户和表空间，并授权，注意授权和源端有区别，

SQL>create tablespace ogg_data datafile '/u01/app/oracle/oradata/ogg_data .dbf' size 1000M autoextend on;

SQL> create user ogg identified by ogg default tablespace ogg_data temporary tablespace TEMP ;

grant connect to ogg ;

grant alter any table to ogg ;

grant alter session to ogg ;

grant create session to ogg ;

grant flashback any table to ogg ;

grant select any dictionary to ogg ;

grant select any table to ogg ;

grant resource to ogg ;

grant insert any table to ogg ;

grant update any table to ogg ;

grant delete any table to ogg ;

grant create any index to ogg ;

grant select any transaction to ogg ;

GGSCI (master2) 4> create subdirs

GGSCI (master2) 4> edit params mgr

port 7839

启动配置的管理进程

GGSCI (master2) 4> start mgr

Manager started.

查看是否正在运行的管理进程，如果不是running请查看自己的原因。

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

查看刚刚设置的端口号有没有问题

GGSCI (master1) 4> info mgr

Manager is running (IP port master2.7839).

为replicat进程创建checkpoint表：

GGSCI (master1) 4> dblogin userid ogg ,password ogg

Successfully logged into database.

目标端添加检查点表，操作完成后，在oracle数据库 ogg用户下生成了一个名字叫rep_liu_ckpt的表。

GGSCI (master2) 1> edit params ./GLOBALS

GGSCI (master2) 2> view params ./GLOBALS

checkpointtable ogg.rep_liu_ckpt

GGSCI (master1) 4> add checkpointtable ogg.rep_liu_ckpt

Successfully created checkpoint table ogg.rep_demo_ckpt.

配置目标端replicate进程：

GGSCI (master2) 4> add replicat rep_liu ,exttrail ./dirdat/h1,checkpointtable ogg.rep_liu_ckpt

REPLICAT added.

删除：GGSCI (master1) 23> delete replicat REP_LIU

Deleted REPLICAT REP_DEMO.

分析：

exttrail：表示这个进程获取数据的来源是什么地方，是我们源端rmttrail所设置过的。

配置目标端replicate参数：

GGSCI (gg2) 5> edit param rep_liu

REPLICAT rep_liu

SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8 )

USERID ogg,PASSWORD ogg

REPORTCOUNT EVERY 30 MINUTES, RATE

REPERROR DEFAULT, ABEND

numfiles 5000

assumetargetdefs

DISCARDFILE ./dirrpt/rep_liu.dsc, APPEND, MEGABYTES 1000 ##注意此处和replicate 的名字相对应

ALLOWNOOPUPDATES

MAP scott.*, TARGET scott.*;

启动并查看replicate进程是否运行：

GGSCI (master2) 4> start rep_liu

GGSCI (master2) 4> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP_LIU 00:00:00 01:22:45

至此配置算是完成了。。。。

ogg的日常操作

1、启动manager：start manager

2、启动extract：start extract extn

3、启动replicat：start replicat repn

4、停止manager：stop manager

5、停止 extract：stop extract extn

6、停止replicat：stop replicat repn

7、删除extract：delete extract extn

8、删除replicat：delete extract repn

9、查看ogg状态：info all

10、修改manager参数：edit params mgr

11、修改extract参数：edit params extn

12、修改replicat参数：edit params repn

13、修改全局参数：edit params ./GLOBAL

以下继续配置支持DDL操作的流程：

在source端操作:

1：赋予ogg用户相应的权限，修改全局配置文件添加ggschema参数

SQL> conn /as sysdba

Connected.


	     SQL> grant execute on utl_file to ogg;  



	  Grant succeeded.  



	      



	    [oracle@master2 ~]$ cd $GGATE   



	     [oracle@master2 ogg]$ ggsci  



	     GGSCI (master2 ) 1> edit param ./GLOBALS  



	     



	    GGSCI (master2) 2> view param ./GLOBALS  



	     ggschema ogg

2：运行相关的sql脚本

[oracle@master2 ~]$ cd $GGATE

[oracle@master2 ~]$ sqlplus / as sysdba

Connected.

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

13. Marker setup table script complete, running verification script...

14. Please enter the name of a schema for the GoldenGate database objects:

15. Setting schema name to OGG

16.

17. MARKER TABLE

18. -------------------------------

19. OK

20.

21. MARKER SEQUENCE

22. -------------------------------

23. OK

24.

25. Script complete.

26.

27. SQL> show parameter recyclebin;

28.

29. NAME TYPE VALUE

30. ------------------------------------ ----------- ------------------------------

31. recyclebin string on

32.

关闭回收站：

SQL> alter system set recyclebin=off;

System altered.

SQL> show parameter recyclebin;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

recyclebin string OFF

42. SQL> @ddl_setup.sql

43. Oracle GoldenGate DDL Replication setup script

44. Verifying that current user has privileges to install DDL Replication...

45. You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

46. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

47. NOTE: The schema must be created prior to running this script.

48. NOTE: Stop all DDL replication before starting this installation.

49. Enter Oracle GoldenGate schema name:ogg

50.

51. Working, please wait ...

52. Spooling to file ddl_setup_spool.txt

53.

54. Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

55. Check complete.

56. Using OGG as a Oracle GoldenGate schema name.

57. Working, please wait ...

58. RECYCLEBIN must be empty.

59. This installation will purge RECYCLEBIN for all users.

60. To proceed, enter yes. To stop installation, enter no.

61.

62. Enter yes or no:yes

63. ————————其他输出省略————————

64.

65. STATUS OF DDL REPLICATION

66. ---------------------------------------------------------------------------------------

67. SUCCESSFUL installation of DDL Replication software components

68.

69. Script complete.

70.

71. SQL> @role_setup.sql

72. GGS Role setup script

73. This script will drop and recreate the role GGS_GGSUSER_ROLE

74. To use a different role name, quit this script and then edit the params.sql script to change th e gg_role parameter to the preferred name. (Do not run the script.)

75.

76. You will be prompted for the name of a schema for the GoldenGate database objects.

77. NOTE: The schema must be created prior to running this script.

78. NOTE: Stop all DDL replication before starting this installation.

79.

80. Enter GoldenGate schema name:ogg

81. Wrote file role_setup_set.txt

82. PL/SQL procedure successfully completed.

83.

84. Role setup script complete

85.

86. Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

87. GRANT GGS_GGSUSER_ROLE TO <loggedUser>

88. where <loggedUser> is the user assigned to the GoldenGate processes.

89.

90. SQL> grant ggs_ggsuser_role to ogg;

91. Grant succeeded.

92.

93. SQL> @ddl_enable.sql

94. Trigger altered.

95.

96. SQL> @?/rdbms/admin/dbmspool.sql #?就是 $ORACLE_HOME

97. Package created.

98. Grant succeeded.

99. View created.

100. Package body created.

101.

102. SQL> @ddl_pin.sql ogg ###要加上空格 ogg,否则需要自己输入ogg.


	



	    103. PL/SQL procedure successfully completed.  



	    104. PL/SQL procedure successfully completed.  



	    105. PL/SQL procedure successfully completed.

3：source端修改extract进程的params文件，添加"ddl include all"参数，重启extract进程


	    1. GGSCI (master2) 1> view params ext_liu  



	   EXTRACT ext_liu



	setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)



	ddl include all 



	userid ogg,password ogg



	exttrail ./dirdat/h1,megabytes 100



	TABLE scott.*;



	    



	    9.GGSCI (master2) 2> stop extract ext_liu  



	    10. Sending STOP request to EXTRACT ext_liu  ...  



	    11. Request processed.  



	    12.  



	    13. GGSCI (master2) 3> start extract ext_liu  



	    14. Sending START request to MANAGER ...  



	    15. EXTRACT ext_liu  starting  



	    16.  



	    17. GGSCI (master2) 4> info extract ext_liu  



	    18. EXTRACT    ext_liu  Last Started 2012-06-20 15:42   Status RUNNING  



	    19. Checkpoint Lag       00:00:00 (updated 00:00:10 ago)  



	    20. Log Read Checkpoint  Oracle Redo Logs  



	    21.                      2012-06-20 15:42:58  Seqno 3, RBA 50044416  



	    22.                      SCN 0.567478 (567478)

在target端操作：

target端修改replicat进程的params文件，添加ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数，重启replicat进程注意：说明目标端不是必须要配置DDL include all语句，可有可无


	    1. [root@master1  ~]# su - oracle  



	    2. [oracle@master1 ~]$ cd $GGATE   



	    3. [oracle@master1 ogg]$ ggsci  



	    4. GGSCI (master1)  1> edit params rep_liu



	    5. GGSCI (master1)  2> view params rep_liu



	    REPLICAT rep_liu



	SETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8)



	ddlerror default ignore retryop maxretries 3 retrydelay 5



	

 DDL include all                              ##可有可无

	USERID ogg,PASSWORD ogg



	handlecollisions



	REPORTCOUNT EVERY 30 MINUTES, RATE



	REPERROR DEFAULT, ABEND



	numfiles 5000



	assumetargetdefs



	DISCARDFILE ./dirrpt/rep_liu.dsc, APPEND, MEGABYTES 1000



	ALLOWNOOPUPDATES



	MAP scott.*, TARGET scott.*;



	    



	     



	   



	    



	   



	     GGSCI (master1)  3> stop replicat rep_liu



	     Sending STOP request to REPLICAT rep_liu...  



	     Request processed.  



	      



	     GGSCI (master1)  4> start replicat rep_liu



	     Sending START request to MANAGER ...  



	     REPLICAT rep_liu starting  



	     



	  GGSCI (master1)  5> info replicat rep_liu



	     REPLICAT   rep_liu Last Started 2012-06-20 15:50   Status RUNNING  



	     Checkpoint Lag       00:00:00 (updated 00:00:00 ago)  



	     Log Read Checkpoint  File ./dirdat/pa000000  



	                         First Record  RBA 4780973

至此，oracle-oracle的ogg搭建配置已完成，通过测试可以知道，在oracle源端scott用户下创建表，可以在一定的延迟（3秒以内）同步到目标端。

ITPub博客

oracle---oracle的单向ogg搭建流程（DML+DDL）

最新文章