GoldenGate单向复制配置示例
一：环境介绍
--source端
ip地址：192.168.123.10
数据库版本：11.2.0.1.0 32 bit
操作系统版本：centos 4.5 32 bit
ogg版本：fbo_ggs_Linux_x86_ora11g_32bit.tar
--target端
ip地址：192.168.123.11
数据库版本：11.2.0.1.0 32 bit
操作系统版本：centos 4.5 32 bit
ogg版本：fbo_ggs_Linux_x86_ora11g_32bit.tar
二：安装 GG 软件
2.1 安装GG
[root@oracle ~]# su - oracle
[oracle@oracle ~]$ ls
awrrpt_1_259_276.html fbo_ggs_Linux_x86_ora11g_32bit.tar oradiag_oracle
[oracle@oracle ~]$ mkdir /u01/GG
[oracle@oracle ~]$ tar -xvf fbo_ggs_Linux_x86_ora11g_32bit.tar -C /u01/GG //将软件解压到/u01/GG
2.2 添加环境变量
在/home/oracle/.bash_profile文件里添加如下内容：
export PATH=/u01/GG:$PATH
export LD_LIBRARY_PATH=/u01/GG:$LD_LIBRARY_PATH
export GGATE=/u01/GG
注意:本实验的GG 和Oracle 使用的是相同的用户，所以把GG 的变量加上就可以了。加载刚刚设置的环境变量：
[oracle@oracle ~]$ source /home/oracle/.bash_profile
2.3 使用ggsci工具，创建必要的目录
[oracle@oracle ~]$ cd /u01/GG //要在安装目录下登陆，否则可能报错
[oracle@oracle GG]$ 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 11g on Apr 23 2012 08:09:25
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (oracle.11g) 1> create subdirs
Creating subdirectories under current directory /u01/GG
Parameter files /u01/GG/dirprm: already exists
Report files /u01/GG/dirrpt: created
Checkpoint files /u01/GG/dirchk: created
Process status files /u01/GG/dirpcs: created
SQL script files /u01/GG/dirsql: created
Database definitions files /u01/GG/dirdef: created
Extract data files /u01/GG/dirdat: created
Temporary files /u01/GG/dirtmp: created
Stdout files /u01/GG/dirout: created
GGSCI (oracle.11g) 2>
以上就是GG 的安装，在source 和target database 都执行
三：配置Source database
GoldenGate通过抓取源端数据库重做日志进行分析，将获取的数据应用到目标端，实现数据同步。因此，源数据库需要必须处于归档模式，并启用附加日志和强制日志。
3.1 归档模式、附加日志、强制日志 --查看 SQL> select log_mode,supplemental_log_data_min,force_logging from v$database; LOG_MODE SUPPLEME FOR ------------ -------- --- ARCHIVELOG NO NO SQL> --修改 1）archivelog SQL>shutdown immediate SQL>startup mount SQL>alter database archivelog; SQL>alter database open; 2） force logging SQL>alterdatabase force logging; 3）supplemental log data SQL>alterdatabase add supplemental log data; 3.2 启用DDL 支持 3.2.1禁用Recycle Bin 如果启用DDL 支持，必须关闭recycle bin。 SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on SQL> --11g（如果是10g，则需要重启） SQL> alter system set recyclebin=off scope=spfile; System altered. 3.2.2 创建存放DDL 信息的user并赋权 SQL> create user ggs identified by 123456; SQL> grant connect,resource to ggs; SQL> grant execute on utl_file to ggs; SQL> 3.2.3 用SYSDBA权限的用户执行如下脚本： [oracle@oracle GG]$ sqlplus /nolog SQL> conn /as sysdba Connected. SQL> -----脚本1 SQL> @/u01/GG/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:ggs ---输入创建的用户 Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. SQL> -----脚本2 SQL> @/u01/GG/ddl_setup.sql; Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. 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:ggs ---输入创建的用户 Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. ...................... STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete. SQL> ------脚本3 SQL> @/u01/GG/role_setup.sql; 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:ggs 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. SQL> ------脚本4：赋权 SQL> grant GGS_GGSUSER_ROLE to ggs; ------脚本5 SQL> @/u01/GG/ddl_enable.sql; Trigger altered. SQL>
四:测试GG
4.1 在Source 和 Target database上创建测试用户 --source database SQL> create user sender identified by 123456; SQL> grant connect,resource,dba to sender; SQL> --target database SQL> create user receiver identified by 123456; SQL> grant connect,resource,dba to receiver; SQL> 4.2 在Source 和Target 上配置Manager --source [oracle@oracle GG]$ cd /u01/GG //此过程最好在安装目录下执行 [oracle@oracle GG]$ 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 11g on Apr 23 2012 08:09:25 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (oracle.11g) 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED GGSCI (oracle.11g) 3> edit params mgr //配置管理进程的端口号 PORT 7809 GGSCI (oracle.11g) 4> start manager Manager started. GGSCI (oracle.11g) 5> 以上是在Source 库上执行的，在Target 库上执行同样的操作。 4.3 配置SourceDB 的复制队列 4.3.1 先连接到数据库，测试连接： GGSCI (oracle.11g) 5> dblogin userid ggs, password 123456; Successfully logged into database. GGSCI (oracle.11g) 6> 4.3.2 添加一个抽取： GGSCI (oracle.11g) 6> add extract ext1,tranlog, begin now EXTRACT added. GGSCI (oracle.11g) 7> add exttrail /u01/GG/dirdat/lt, extract ext1 EXTTRAIL added. --修改ext1的参数 GGSCI (oracle.11g) 8> edit params ext1 extract ext1 userid ggs, password 123456 rmthost 192.168.0.11, mgrport 7809 rmttrail /u01/GG/dirdat/lt ddl include mapped objname sender.*; table sender.*; GGSCI (oracle.11g) 10> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED EXT1 00:00:01 00:04:23 GGSCI (oracle.11g) 11> 4.4 配置TargetDB 同步队列 4.4.1 创建用户ggt SQL> create user ggt identified by 123456; SQL> grant connect,resource,dba to ggt; --要赋予dba权限，否则后期启动进程时会报： --OCI Error describe for query (status = 942-ORA-00942: table or view does not exist), --SQL<SELECT db.name, i.instance_name, i.version FROM v$database db, v$instance i>. SQL> grant execute on utl_file to ggt; SQL> 4.4.2 在Target 端添加checkpoint表： --添加参数文件./GLOBAL GGSCI (oracle.11g) 4> edit params ./GLOBAL GGSCHEMA ggs CHECKPOINTTABLE ggt.checkpoint --登陆 GGSCI (oracle.11g) 2> dblogin userid ggt , password 123456 Successfully logged into database. --添加checkpoint表 GGSCI (oracle.11g) 3> add checkpointtable ggt.checkpoint Successfully created checkpoint table ggt.checkpoint. GGSCI (oracle.11g) 4> 4.4.3 创建同步队列 GGSCI (oracle.11g) 4> add replicat rep1,exttrail /u01/GG/dirdat/lt, checkpointtable ggt.checkpoint REPLICAT added. GGSCI (oracle.11g) 5> edit params rep1 replicat rep1 ASSUMETARGETDEFS userid ggt , password 123456 discardfile /u01/GG/dirdat/rep1_discard.txt,append, megabytes 10 DDL map sender.*, target receiver.*; 添加如上内容 4.5开启同步 4.5.1 Source DB： GGSCI (oracle.11g) 14> start extract ext1 Sending START request to MANAGER ... EXTRACT EXT1 starting GGSCI (oracle.11g) 15> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXT1 00:27:51 00:00:10 GGSCI (oracle.11g) 16> 4.5.2 Target DB GGSCI (oracle.11g) 2> start replicat rep1 Sending START request to MANAGER ... REPLICAT REP1 starting GGSCI (oracle.11g) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REP1 00:00:00 00:00:01 GGSCI (oracle.11g) 4>
五.验证
在Source DB上的sender 用户下创建一张表，然后看这张表是否同步到了Target DB的receiver用户下
--Source DB：
SQL> conn sender/123456;
Connected.
SQL> create table gg_test as select * from sys.all_users;
SQL>
--Target DB：
SQL> conn receiver/123456;
Connected.
SQL> select count(*) from gg_test;
COUNT(*)
----------
41
SQL>
数据同步过来了，因为启用了DDL的支持，所以这里把表给复制过来了。
--在Source DB上在插入一些记录，在验证下GG的同步情况：
SQL> insert into gg_test select * from sys.all_users;
41 rows created.
SQL> commit;
Commit complete.
SQL>
--Target DB 验证：
SQL> select count(*) from gg_test;
COUNT(*)
----------
82
SQL>
同步正常，以上就是Oracle to Oracle 下的一个GG 单向复制示例。
