怎么理解Oracle数据库的单向复制

这篇文章主要讲解了“怎么理解Oracle数据库的单向复制”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么理解Oracle数据库的单向复制”吧！

1.       环境需求：

两台装有Oracle软件的虚拟机服务器，并配置好监听及安装好数据库

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 必须大写

事务配置及检查点表

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;

感谢各位的阅读，以上就是“怎么理解Oracle数据库的单向复制”的内容了，经过本文的学习后，相信大家对怎么理解Oracle数据库的单向复制这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！