ITPub博客

首页 > 数据库 > Oracle > OGG单向复制

OGG单向复制

原创 Oracle 作者:jlove小斯 时间:2019-02-16 15:10:13 0 删除 编辑

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
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;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31464898/viewspace-2636171/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2017-05-31

  • 博文量
    3
  • 访问量
    2187