第一次安装,不管是源端还是目标端进程都正常,但是就是不同步,后来把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]$ 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@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
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
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]$ ./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).
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
至此配置算是完成了。。。。
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博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-1990267/,如需转载,请注明出处,否则将追究法律责任。