Oracle 到Oracle 数据库的单向复制
1. 环境需求：
两台装有Oracle 软件的虚拟机服务器，并配置好监听及安装好数据库
|
|
Linux |
Oracle |
OGG |
IP |
SID |
|
OGG1 |
CentOS 6.5 |
11.2.0.4 |
12.2.0.2 |
192.168.1.211 |
ogg |
|
OGG2 |
CentOS 6.5 |
11.2.0.4 |
12.2.0.2 |
192.168.1.212 |
ogg |
2. 配置准备
2.1 创建操作系统用户
useradd ogg -g oinstall
新建安装目录
[root@ogg1 ~]# mkdir -p /u01/ogg
[root@ogg1 ~]# chown -R ogg.oinstall /u01/ogg
[root@ogg1 ~]# chmod 775 /u01/ogg/
2.2 设置用户的环境变量，尤其要给它指定lib 库的位置以及ggsci 位置：
export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export GG_HOME=/u01/ogg
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$GG_HOME:$PATH
2.3 正式安装
[root@ogg1 soft]# unzip 122022_fbo_ggs_Linux_x64_shiphome.zip
[ogg@ogg1 ~]$ cd /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/
[ogg@ogg1 Disk1]$ ./runInstaller
安装步骤略，将安装目录设置为/u01/ogg
3. 复制准备：实现单表复制
检查相关参数是否开启
Select LOG_MODE , FORCE_LOGGING, SUPPLEMENTAL_LOG_DATA_min from v$database;
3.1 开启参数（需开启归档模式和强制归档）
SQL> show parameter golde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> alter system set enable_goldengate_replication=true;
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 archive log current;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 536874104 bytes
Database Buffers 289406976 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL> alter database force logging;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
System altered.
源端和目标端分别设置
源端：
create user ogg_source identified by oracle default tablespace users;
grant alter session to ogg_source;
grant create session to ogg_source;
grant connect to ogg_source;
grant resource to ogg_source;
grant select any dictionary to ogg_source;
grant select any table to ogg_source;
grant flashback any table to ogg_source;
grant alter any table to ogg_source;
目标
：
create user ogg_target identified by oracle default tablespace users;
grant alter session to ogg_target;
grant create session to ogg_target;
grant connect to ogg_target;
grant resource to ogg_target;
grant select any dictionary to ogg_target;
grant select any table to ogg_target;
grant flashback any table to ogg_target;
grant alter any table to ogg_target;
grant insert any table to ogg_target;
grant update any table to ogg_target;
grant delete any table to ogg_target;
3.2 Ogg 配置
问题解决：无法登陆，是oracle_sid 设置问题
GGSCI (ogg1) 5> dblogin userid ogg_source ,password oracle
ERROR: Unable to connect to database using user ogg_source. Please check privileges.
Unable to initialize database connection because of error ORA-12162: TNS:net service name is incorrectly specified.
GGSCI (ogg1) 6> exit
[ogg@ogg1 ~]$ export ORACLE_SID=ogg
[ogg@ogg1 ~]$ /u01/ogg/ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Jun 30 2017 14:42:26
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (ogg1) 1> dblogin userid ogg_source ,password oracle
Successfully logged into database.
1. 配置需要抽取的表或者用户
Add trandata luc.*
2. 配置全局参数
./GLOBALS 必须大写
事务配置及检查点表
>
EDIT PARAMS
./GLOBALS
GGSCHEMA ogg_source
CHECKPOINTTABLE ogg_source.CHKPTAB
3. 配置MGR 进程
Edit params mgr
>port 7809
4. 配置Extract 脚本
配置参数，使用本地的方式。
Edit params EXT_1
EXTRACT EXT_1
USERID ogg_source, PASSWORD oracle
EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss
TABLE N1.*;
添加抽取进程，从数据库日志中抓取数据。
> ADD EXTRACT EXT_1, TRANLOG, BEGIN NOW
EXTRACT added.
配置本地队列, 然后启动
> ADD EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT EXT_1
> start EXT_1
Sending START request to MANAGER ...
EXTRACT EXT_1 starting
配置完成之后，就可以查看是否能够正常抽取了，可以看到是没有正常启动。状态现在还是STOPPED
> info EXT_1
EXTRACT EXT_1 Initialized 2016-11-11 16:16 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:22 ago)
Log Read Checkpoint Oracle Redo Logs
2016-11-11 16:16:04 Seqno 0, RBA 0
5. 配置Pump 脚本
> edit params dpump_1
EXTRACT dpump_1
PASSTHRU
RMTHOST 10.127.2.32, MGRPORT 1530
RMTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss
TABLE n1.*;
> ADD EXTRACT dpump_1,EXTTRAILSOURCE /home/oracle/ogg/ogg_work/dirdat/ss
EXTRACT added.
> ADD RMTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss, EXTRACT dpump_1
RMTTRAIL added.
配置完成之后，启动PUMP 进程。
> start dpump_1
Sending START request to MANAGER ...
EXTRACT DPUMP_1 starting 查看DUMP 进程的信息如下：
> info dpump_1
EXTRACT DPUMP_1 Last Started 2016-11-11 16:24 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 53479
Log Read Checkpoint File /home/oracle/ogg/ogg_work/dirdat/ss000000000
First Record RBA 0
6. 配置Application 脚本
配置投递队列参数，将数据应用到目标库，这里有个映射关系，就是源库的n1.* 和目标库的n1.* 是对应的。
> edit params rep_1
REPLICAT REP_1
USERID ogg_target, PASSWORD oracle
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP n1.*,TARGET n1.*; 添加投递队列
> ADD REPLICAT REP_1, EXTTRAIL /home/oracle/ogg/ogg_work/dirdat/ss,CHECKPOINTTABLE ogg_target.CHKPTAB
REPLICAT added.
> start REP_1
Sending START request to MANAGER ...
REPLICAT REP_1 starting
启动成功后的状态是这样的。
> INFO REP_1
REPLICAT REP_1 Last Started 2016-11-11 17:02 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Process ID 69571
Log Read Checkpoint File /home/oracle/ogg/ogg_work/dirdat/ss000000000
First Record RBA 0
报错执行：
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'ogg_target',
grant_privileges => true);
END;
/
循环插入 1-100
begin
for i in 1 .. 100 loop
insert into luc.test values (i,’uuu’);
end loop;
end;
/
插入大量数据测试是否同步：
insert into luc.test select level,level||’obj’ from dual connect by level<500000;