# .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 GGATE=/u01/OGG_linux/ggs
alias sqlplus='rlwrap sqlplus'
NLS_LANG
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
2, GGATE=/u01/OGG_linux/ggs 这个路径是你将要安装ogg软件的路径。
3,设置LD_LIBRARY_PATH,为了安装OGG所用的动态链接库。如果没有配置这个路径的话,在安装OGG的过程
中会报找不到动态链接库的错误,
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
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;
四:修改源端数据库配置
源端:解释:
备注: 在oracle中可以通过rowid来定位某条记录,但是目标端的数据库和源端数据库的数据库可能完全不一样,所以无法通过rowid来确定源端数据库的逻辑变化,这时附加日志supplemental log便登上了表演的舞台。数据库在开启附加日志功能后,对于源端的修改操作,oracle会同时追加能够唯一标示记录的列到redo log。这样目标端数据库就可以知道源端发生了哪些具体的变化。
四:配置ogg
源端:
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.
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.
5,GGSCI (master2) 3>info mgr
Manager is running (IP port
WebServer.7839).
tranlog:表示数据抓取的来源是数据库的redo数据。
begin now:表示我们在启动这个抓取进程的就去抓取数据。
threads 1:表示我们数据库有多少个redo threads,单实例基本上是1或者不设,rac就自己设置了,
配置过RAC的基本都了解。
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
GGSCI (gg1) 31> add extract dpe_liu, exttrailsource ./dirdat/h1 ##h1是源端设置的trails 文件的格式
EXTRACT added.
10
输出:目标主机怎么写,也是定义datapumo进程的输出。
GGSCI (gg1) 33> add rmttrail ./dirdat/h1, EXTRACT dpe_liu,MEGABYTES 100
RMTTRAIL added.
分析:
exttrailsource:抓取进程的来源,因为这个进程不否则到数据库中取抓取,所以抓取的来源
是通过抓取进程已经生成好的trail文件中的内容。
创建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 ;
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]$ ggsciGGSCI ( master2 ) 1> edit param ./GLOBALSGGSCI ( master2) 2> view param ./GLOBALSggschema ogg
2:运行相关的sql脚本
[oracle@master2 ~]$ cd $GGATE[oracle@master2 ~]$ sqlplus / as sysdbaConnected.SQL> @marker_setup.sqlMarker setup scriptYou 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:ogg13. 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 OGG16.17. MARKER TABLE18. -------------------------------19. OK20.21. MARKER SEQUENCE22. -------------------------------23. OK24.25. Script complete.26.27. SQL> show parameter recyclebin;28.29. NAME TYPE VALUE30. ------------------------------------ ----------- ------------------------------31. recyclebin string on32.关闭回收站:SQL> alter system set recyclebin=off;System altered.SQL> show parameter recyclebin;NAME TYPE VALUE------------------------------------ ----------- ------------------------------recyclebin string OFF42. SQL> @ddl_setup.sql43. Oracle GoldenGate DDL Replication setup script44. 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:ogg50.51. Working, please wait ...52. Spooling to file ddl_setup_spool.txt53.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:yes63. ————————其他输出省略————————64.65. STATUS OF DDL REPLICATION66. ---------------------------------------------------------------------------------------67. SUCCESSFUL installation of DDL Replication software components68.69. Script complete.70.71. SQL> @role_setup.sql72. GGS Role setup script73. This script will drop and recreate the role GGS_GGSUSER_ROLE74. 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:ogg81. Wrote file role_setup_set.txt82. PL/SQL procedure successfully completed.83.84. Role setup script complete85.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.sql94. Trigger altered.95.96. SQL> @?/rdbms/admin/dbmspool.sql #?就是 $ORACLE_HOME97. 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_liuEXTRACT ext_liusetenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)ddl include alluserid ogg,password oggexttrail ./dirdat/h1,megabytes 100TABLE scott.*;9. GGSCI (master2) 2> stop extract ext_liu10. Sending STOP request to EXTRACT ext_liu ...11. Request processed.12.13. GGSCI (master2) 3> start extract ext_liu14. Sending START request to MANAGER ...15. EXTRACT ext_liu starting16.17. GGSCI (master2) 4> info extract ext_liu18. EXTRACT ext_liu Last Started 2012-06-20 15:42 Status RUNNING19. Checkpoint Lag 00:00:00 (updated 00:00:10 ago)20. Log Read Checkpoint Oracle Redo Logs21. 2012-06-20 15:42:58 Seqno 3, RBA 5004441622. SCN 0.567478 (567478)
在target端操作:
target端修改replicat进程的params文件,添加ddlerror default ignore retryop maxretries 3 retrydelay 5" 参数,重启replicat进程 注意: 说明目标端不是必须要配置DDL include all语句,可有可无
1. [root@ master1 ~]# su - oracle2. [oracle@ master1 ~]$ cd $GGATE3. [oracle@master1 ogg]$ ggsci4. GGSCI (master1) 1 > edit params rep_liu5. GGSCI (master1) 2 > view params rep_liuREPLICAT rep_liuSETENV (NLS_LANG =AMERICAN_AMERICA.AL32UTF8)ddlerror default ignore retryop maxretries 3 retrydelay 5DDL include all ##可有可无USERID ogg,PASSWORD ogghandlecollisionsREPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDnumfiles 5000assumetargetdefsDISCARDFILE ./dirrpt/rep_liu.dsc, APPEND, MEGABYTES 1000ALLOWNOOPUPDATESMAP scott.*, TARGET scott.*;GGSCI (master1) 3 > stop replicat rep_liuSending STOP request to REPLICAT rep_liu...Request processed.GGSCI (master1) 4 > start replicat rep_liuSending START request to MANAGER ...REPLICAT rep_liu startingGGSCI (master1) 5 > info replicat rep_liuREPLICAT rep_liu Last Started 2012-06-20 15:50 Status RUNNINGCheckpoint Lag 00:00:00 (updated 00:00:00 ago)Log Read Checkpoint File ./dirdat/pa000000First Record RBA 4780973