1.分别在windows2008、linux平台部署oracle 11.2.0.4
2.分别在windows2008、linux平台部署gg。
2.1 windows平台：
gg的安装目录位
C:\ora11g\product\ogg_src
需设置环境变量 ORACLE_HOME and ORACLE_SID
2.2 linux平台：
gg的安装目录位
[oracle@Oracle02 ~]$ ls -l /u01/app/oracle/product/
drwxrwxr-x. 3 oracle oinstall 4096 12月 13 10:21 11.2.0
drwxr-xr-x. 26 oracle oinstall 4096 12月 13 11:24 ogg_src
[oracle@Oracle02 ~]$
需设置环境变量 export LD_LIBRARY_PATH=$ORACLE_HOME/lib
需要安装readline-devel-6.0-4.el6.x86_64.rpm（在linux平台 ）
3.启动ogg的mgr管理进程，source和target端做相同的操作
3.1 启动wind的ogg的mgr
C:\ora11g\product\ogg_src>ggsci.exe
GGSCI (WIN-GM5PVS1CILH) 1> start mgr
Manager started.
GGSCI (WIN-GM5PVS1CILH) 2> info mgr
Manager is running (IP port WIN-GM5PVS1CILH.7809, Process ID 232).
3.2 启动linux的ogg的mgr
[oracle@Oracle02 product]$ cd ogg_src
[oracle@Oracle02 ogg_src]$ ./ggsci
GGSCI (Oracle02) 1> start mgr
MGR is already running.
GGSCI (Oracle02) 2> info mgr
Manager is running (IP port Oracle02.7809, Process ID 21213).
GGSCI (Oracle02) 3>
4准备工作，在source和target端都配置
4.1配置tnsnames.ora文件
orcl_w =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
)
(CONNECT_DATA =
(SID =orcl )
)
)
orcl_L =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
)
(CONNECT_DATA =
(SID =orcl )
)
)
4.2 调整数据库
#确定数据库运行在归档模式，，
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
#开启数据库附加日志
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
NO
SQL> alter database add supplemental log data;
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
#打开force logging
SQL> alter database force logging;
SQL>
4.3 创建单独用于复制的数据库账号
SQL> create tablespace tbs_ogg;
SQL> create user ggs identified by ggs default tablespace tbs_ogg temporary tablespace temp account unlock;
SQL> grant connect,resource to ggs;
SQL> grant select any table to ggs;
SQL> grant select any dictionary to ggs;
SQL> create tablespace tbs_ogg;
SQL> create user ggt identified by ggt default tablespace tbs_ogg temporary tablespace temp account unlock;
SQL> grant connect,resource to ggt;
SQL> grant select any table to ggt;
SQL> grant select any dictionary to ggt;
SQL>
5.准备测试用户和表
SQL> alter user scott identified by scott account unlock;
SQL> grant connect,resource,select_catalog_role to scott;
SQL>
SQL> conn scott/scott
SQL> create table t1 as select * from dba_objects;
SQL> alter table t1 add constraint pk_t1 primary key(object_id);
SQL> select count(*) from t1; //source端
COUNT(*)
----------
86045
SQL>
SQL> select count(*) from t1; //target端，只复制表定义，不填充数据
COUNT(*)
----------
0
SQL>
6.初始化加载数据
注：在异构数据库平台，这个功能显得非常的有用！而在oracle-oracle的数据复制条件下，oracle推荐使用expdp/impdp工具
6.1 source端添加extract进程
GGSCI (WIN-GM5PVS1CILH) 2> info mgr
Manager is running (IP port WIN-GM5PVS1CILH.7809, Process ID 232).
GGSCI (WIN-GM5PVS1CILH) 3> add extract einig1,sourceistable //sourceistable代表直接从表中读取数据
EXTRACT added.
GGSCI (WIN-GM5PVS1CILH) 4> edit params einig1 //einig1代表extract initial load group 1缩写
GGSCI (WIN-GM5PVS1CILH) 5> view params einig1 //查看设置后的参数
extract einig1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ggs,password ggs
rmthost 192.168.0.21,mgrport 7809
rmttask replicat,group rinig1
table scott.t1;
GGSCI (WIN-GM5PVS1CILH) 6>
6.2 target端添加replicat进程
GGSCI (Oracle02) 2> info mgr
Manager is running (IP port Oracle02.7809, Process ID 21213).
GGSCI (Oracle02) 3> add replicat rinig1,specialrun //rinig1代表replicat initial load group 1缩写
REPLICAT added.
GGSCI (Oracle02) 4> edit params rinig1 //rinig1的名字必须同source端定义的group名字相同
GGSCI (Oracle02) 5> view params rinig1 //查看设置后的参数
replicat rinig1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
assumetargetdefs
userid ggt,password ggt
discardfile ./dirrpt/rinig1.dsc,purge
map scott.*,target scott.*;
GGSCI (Oracle02) 6>
6.3.source端启动extract进程，查看日志输出
GGSCI (WIN-GM5PVS1CILH) 6> start extract einig1
Sending START request to MANAGER ...
EXTRACT EINIG1 starting
GGSCI (WIN-GM5PVS1CILH) 7>
6.4 target端验证
SQL> conn scott/scott
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
81000
SQL>
在这期间碰到几个坑，详见《OGG初始化加载数据时遇到的问题汇总》
7.配置wind,linux间的实时同步复制
7.1 在source上配置extract进程，进程的名字不能超过8个字符
GGSCI (WIN-GM5PVS1CILH) 2> edit params eora_t1
GGSCI (WIN-GM5PVS1CILH) 3> view params eora_t1
extract eora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ggs@orcl_w,password ggs
exttrail dirdat/sp
table scott.*;
GGSCI (WIN-GM5PVS1CILH) 4>
7.2 开启scott用户下所有表的附加日志 (前面已经开始了数据库的附加日志，此处应该可以省略)
GGSCI (WIN-GM5PVS1CILH) 4> dblogin userid ggs, password ggs
Successfully logged into database.
GGSCI (WIN-GM5PVS1CILH as ggs@orcl) 5> add trandata scott.*
7.3 添加extract进程，添加trail文件，文件名前缀不能超过2个字符
GGSCI (WIN-GM5PVS1CILH) 1> add extract eora_t1,tranlog,begin now
EXTRACT added.
GGSCI (WIN-GM5PVS1CILH) 2> add exttrail dirdat/sp,extract eora_t1,megabytes 100
EXTTRAIL added.
GGSCI (WIN-GM5PVS1CILH) 6> start extract eora_t1
Sending START request to MANAGER ...
EXTRACT EORA_T1 starting
GGSCI (WIN-GM5PVS1CILH) 7>
7.4：添加pump进程
GGSCI (WIN-GM5PVS1CILH) 2> edit params pora_t1
GGSCI (WIN-GM5PVS1CILH) 3> view params pora_t1
extract pora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
passthru
rmthost 192.168.0.21,mgrport 7809
rmttrail dirdat/rp
table scott.*;
GGSCI (WIN-GM5PVS1CILH) 4>
GGSCI (WIN-GM5PVS1CILH) 1> add extract pora_t1,exttrailsource dirdat/sp //这里sp文件名同前面extract进程参数文件中定义的trail文件名一致
EXTRACT added.
GGSCI (WIN-GM5PVS1CILH) 2>
GGSCI (WIN-GM5PVS1CILH) 3> add rmttrail dirdat/rp,extract pora_t1,megabytes 100 //这里rp文件名同前面pora_t1进程参数文件中定义的trail文件名一致
RMTTRAIL added.
GGSCI (WIN-GM5PVS1CILH) 4>
GGSCI (WIN-GM5PVS1CILH) 5> start extract pora_t1
Sending START request to MANAGER ...
EXTRACT PORA_T1 starting
GGSCI (WIN-GM5PVS1CILH) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA_T1 00:00:00 00:00:07
EXTRACT RUNNING PORA_T1 00:00:00 00:00:00
GGSCI (WIN-GM5PVS1CILH) 7>
7.5 在target端添加检查表，配置replicat进程
GGSCI (Oracle02) 5> edit params ./GLOBALS
checkpointtable ggt.chkpt
GGSCI (Oracle02 as ggt@orcl) 2> add checkpointtable
No checkpoint table specified. Using GLOBALS specification (ggt.chkpt)...
Successfully created checkpoint table ggt.chkpt.
GGSCI (Oracle02 as ggt@orcl) 3>
SQL> select tname from tab;
TNAME
------------------------------
CHKPT
CHKPT_LOX
SQL>
#配置replicat进程
GGSCI (Oracle02 as ggt@orcl) 3> edit params rora_t1
GGSCI (Oracle02 as ggt@orcl) 4> view params rora_t1
replicat rora_t1
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ggt,password ggt
handlecollisions
assumetargetdefs
discardfile dirrpt/rora_t1.dsc,purge
map scott.* ,target scott.*;
GGSCI (Oracle02 as ggt@orcl) 5>
GGSCI (Oracle02 as ggt@orcl) 5> add replicat rora_t1,exttrail dirdat/rp
REPLICAT added.
GGSCI (Oracle02 as ggt@orcl) 6>
GGSCI (Oracle02 as ggt@orcl) 6> start replicat rora_t1
Sending START request to MANAGER ...
REPLICAT RORA_T1 starting
GGSCI (Oracle02 as ggt@orcl) 7>
GGSCI (Oracle02 as ggt@orcl) 7> info replicat rora_t1
REPLICAT RORA_T1 Last Started 2016-12-13 17:26 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:00 ago)
Process ID 23078
Log Read Checkpoint File dirdat/rp000000002
2016-12-13 17:08:29.494703 RBA 1518
GGSCI (Oracle02 as ggt@orcl) 8>
GGSCI (Oracle02 as ggt@orcl) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RORA_T1 00:00:00 00:00:01
GGSCI (Oracle02 as ggt@orcl) 9>
8.测试
8.1数据同步
C:\Users\Administrator>sqlplus scott/scott
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
87135
SQL> desc t1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> insert into t1 (object_id,object_name) values (87136,'ogg_test');
已创建 1 行。
SQL> commit;
提交完成。
SQL> conn scott/scott@orcl_L
已连接。
SQL> select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
87136
SQL>
8.2 删除测试
SQL> conn scott/scott
已连接。
SQL> delete from t1 where object_id > 1000;
已删除85048行。
SQL> commit;
提交完成。
SQL> select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
1000
SQL> conn scott/scott@orcl_L
已连接。
SQL> select max(object_id) from t1;
MAX(OBJECT_ID)
--------------
87136
