oracle 11g goldengate与oracle 11g数据同步

简介:

1.下载,安装goldengate软件(两个节都需要安装)

glodengate下载地址:http://pan.baidu.com/s/1pLtVTJt 密码:exob

[oracle@oracleogg ~]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@oracleogg ~]$ tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/glodengate/

[oracle@oracleogg ~]$ cd /u01/glodengate/

[oracle@oracleogg glodengate]$ vim ~/.bash_profile 

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1

export ORACLE_SID=oracle11g

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/glodengate/lib    --必须要设置,否则出错

[oracle@oracleogg glodengate]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


GGSCI (oracleogg) 1> create subdirs    --安装glodengate

Creating subdirectories under current directory /u01/glodengate

Parameter files                /u01/glodengate/dirprm: already exists

Report files                   /u01/glodengate/dirrpt: created

Checkpoint files               /u01/glodengate/dirchk: created

Process status files           /u01/glodengate/dirpcs: created

SQL script files               /u01/glodengate/dirsql: created

Database definitions files     /u01/glodengate/dirdef: created

Extract data files             /u01/glodengate/dirdat: created

Temporary files                /u01/glodengate/dirtmp: created

Stdout files                   /u01/glodengate/dirout: created

GGSCI (oracleogg) 2> 

[oracle@oracleogg glodengate]$ ls -lF | grep '/$'    --查看glodengate创建的目录

drwxr-x--- 2 oracle oinstall     4096 Apr 23  2012 cfg/

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirchk/     --检查点文件

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirdat/     --extract文件

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirdef/

drwxr-x--- 2 oracle oinstall     4096 Apr 23  2012 dirjar/

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirout/

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirpcs/

drwxr-x--- 2 oracle oinstall     4096 Apr 23  2012 dirprm/    --参数文件目录

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirrpt/     --日志文件

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirsql/     --sql脚本文件

drwxrwxr-x 2 oracle oinstall     4096 Nov 19 19:36 dirtmp/     --临时文件

drwxr-x--- 7 oracle oinstall     4096 Apr 23  2012 UserExitExamples/

[oracle@oracleogg glodengate]$ 


2.获取语法帮助信息

(1).必须要ggsci根目录进入ggsci终端,否则获取不到帮助信息

(2).帮助语法: help <command> <object>   比如:help add rmttrail

GGSCI (oracleogg) 3> help add rmttrail

ADD RMTTRAIL 

Use ADD RMTTRAIL to create a trail for online processing on a remote 

system and: 

* assign a maximum file size. 

* associate the trail with an Extract group. 

In the parameter file, specify a RMTHOST entry before any RMTTRAIL 

entries to identify the remote system and TCP/IP port for the Manager 

process. 

Syntax: 

 

ADD RMTTRAIL <trail name>, EXTRACT <group name>  

[, MEGABYTES <n>] 

[SEQNO <n>] 

 

<trail name> 

The fully qualified path name of the trail. The actual trail name can 

contain only two characters. Oracle GoldenGate appends this name with a 

six-digit sequence number whenever a new file is created. For example, 

a trail named ./dirdat/tr would have files named 

./dirdat/tr000001, ./dirdat/tr000002, and so forth.  

 

<group name> 

The name of the Extract group to which the trail is bound. Only one 

Extract process can write data to a trail. 

 

MEGABYTES <n> 

The maximum size, in megabytes, of a file in the trail. The default is 

100.  

 

SEQNO <n> 

Specifies that the first file in the trail will start with the 

specified trail sequence number. Do not include any zero padding. For 

example, to start at sequence 3 of a trail named "tr," specify SEQNO 3. 

The actual file would be named /ggs/dirdat/tr000003. This option can be 

used during troubleshooting when Replicat needs to be repositioned to a 

certain trail sequence number. It eliminates the need to alter Replicat 

to read the required sequence number. 

 

Example: 

ADD RMTTRAIL c:\ggs\dirdat\aa, EXTRACT finance, MEGABYTES 200 

GGSCI (oracleogg) 4> 


3.在source端修改支持supplemental log日志

[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 20 04:18:26 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database add supplemental log data;       

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

YES

SQL> 


4.在source端和target端创建表空间,用户和测试表(只有source端有测试数据)

source节点:

SQL> create tablespace wuhan datafile '/u01/oracle/oradata/orac11g/wuhan.dbf' size 100m;

Tablespace created.

SQL> create user gguser identified by system default tablespace wuhan quota unlimited on wuhan;

User created.

SQL> grant dba,resource,connect to gguser;

Grant succeeded.

SQL> conn gguser/system

Connected.

SQL> create table t (a number,b char(10));

Table created.

SQL> insert into t values(1,'a');

1 row created.

SQL> insert into t values(2,'b');

1 row created.

SQL> insert into t values(3,'c');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

   A    B

---------- ----------

   1    a

   2    b

   3    c

SQL> 


target节点:

SQL> create tablespace wuhan datafile '/u01/app/oracle/oradata/oracleogg/wuhan.dbf' size 100m;

Tablespace created.

SQL> create user gguser identified by system default tablespace wuhan quota unlimited on wuhan;

User created.

SQL> grant dba,resource,connect to gguser;

Grant succeeded.

SQL> conn gguser/system

Connected.

SQL> create table t (a number,b char(10));

Table created.

SQL> 


5.source端和target端启动mgr进程(两个节点都需要做)

aource节点:

GGSCI (oracle11g) 3> edit params mgr        --设置mgr的端口

-- port                  --这个是注释

PORT 7809        --指定的端口

GGSCI (oracle11g) 4> start mgr      --启动mgr

Manager started.

GGSCI (oracle11g) 6> info mgr      --查看mgr的状态

Manager is running (IP port oracle11g.7809).

GGSCI (oracle11g) 7> 


6.配置glodengate需要同步的表(source节点)

GGSCI (oracle11g) 7> dblogin userid gguser, password system

Successfully logged into database.

GGSCI (oracle11g) 8> add trandata gguser.t            --也可以使用通配符(add trandata gguser.*)

2016-11-20 05:32:50  WARNING OGG-00869  No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table GGUSER.T.

GGSCI (oracle11g) 9> info trandata gguser.*

Logging of supplemental redo log data is enabled for table GGUSER.T.

Columns supplementally logged for table GGUSER.T: A, B.

GGSCI (oracle11g) 10> 


7.初使化数据(将source的数据导入到target节点中)

source节点:

GGSCI (oracle11g) 10> add extract einiaa, sourceistable

EXTRACT added.

GGSCI (oracle11g) 11> edit params einiaa

EXTRACT EINIAA

USERID GGUSER, PASSWORD "system"

RMTHOST 192.168.3.90, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINIAA

TABLE gguser.t;

GGSCI (oracle11g) 12>


target节点:

GGSCI (oracleogg) 10> add replicat riniaa, specialrun

REPLICAT added.

GGSCI (oracleogg) 11> edit params riniaa

REPLICAT RINIAA

ASSUMETARGETDEFS

USERID gguser, PASSWORD "system"

DISCARDFILE ./dirrpt/RINIAA.dsc, PURGE

MAP gguser.*, TARGET gguser.*;

GGSCI (oracleogg) 12>


8.启动

source节点:

GGSCI (oracle11g) 12> start extract einiaa

Sending START request to MANAGER ...

EXTRACT EINIAA starting


GGSCI (oracle11g) 13> view report einiaa


2016-11-20 05:56:55  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.

***********************************************************************

                 Oracle GoldenGate Capture for Oracle

    Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:42:16

 

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2016-11-20 05:56:55

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Fri Nov 22 03:15:09 UTC 2013, Release 2.6.32-431.el6.x86_64

Node: oracle11g

Machine: x86_64

                         soft limit   hard limit

Address Space Size   :    unlimited    unlimited

Heap Size            :    unlimited    unlimited

File Size            :    unlimited    unlimited

CPU Time             :    unlimited    unlimited


Process id: 8706

Description: 


***********************************************************************

**            Running with the following parameters                  **

***********************************************************************

2016-11-20 05:56:55  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

EXTRACT EINIAA

USERID GGUSER, PASSWORD ********


2016-11-20 05:56:55  INFO    OGG-03500  WARNING: NLS_LANG environment variable does not match database character set, or not set. Using database character se

t value of AL32UTF8.

RMTHOST 192.168.3.90, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINIAA

TABLE gguser.t;


2016-11-20 05:56:56  WARNING OGG-00869  No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee u

niqueness.  KEYCOLS may be used to define the key.

Using the following key columns for source table GGUSER.T: A, B.


2016-11-20 05:56:56  INFO    OGG-01815  Virtual Memory Facilities for: COM

    anon alloc: mmap(MAP_ANON)  anon free: munmap

    file alloc: mmap(MAP_SHARED)  file free: munmap

    target directories:

    /u01/glodengate/dirtmp.


CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE:                               64G

CACHEPAGEOUTSIZE (normal):                8M

PROCESS VM AVAIL FROM OS (min):         128G

CACHESIZEMAX (strict force to disk):     96G


Database Version:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE11.2.0.1.0Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production


Database Language and Character Set:

NLS_LANG         = ".AL32UTF8" 

NLS_LANGUAGE     = "AMERICAN" 

NLS_TERRITORY    = "AMERICA" 

NLS_CHARACTERSET = "AL32UTF8" 


Processing table GGUSER.T


***********************************************************************

*                   ** Run Time Statistics **                         *

***********************************************************************


Report at 2016-11-20 05:57:15 (activity since 2016-11-20 05:56:56)

Output to RINIAA:

From Table GGUSER.T:

       #                   inserts:         3

       #                   updates:         0

       #                   deletes:         0

       #                  discards:         0

REDO Log Statistics

  Bytes parsed                    0

  Bytes output                  252

GGSCI (oracle11g) 14> 


target节点:

GGSCI (oracleogg) 14> view report riniaa    --查看日志

[oracle@oracleogg glodengate]$ sqlplus gguser/system

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 19 22:01:32 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from t;

   A    B

---------- ----------

   1    a

   2    b

   3    c

SQL> 


8.配置用户数据实时更新

source节点:

GGSCI (oracle11g) 14> add extract eoraaa, tranlog, begin now, threads 1       

EXTRACT added.

GGSCI (oracle11g) 15> info extract *

EXTRACT    EORAAA    Initialized   2016-11-20 06:17   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:00:30 ago)

Log Read Checkpoint  Oracle Redo Logs

                     2016-11-20 06:17:37  Thread 1, Seqno 0, RBA 0

                     SCN 0.0 (0)

GGSCI (oracle11g) 16> edit params eoraaa

EXTRACT EORAAA

USERID gguser, PASSWORD "system"

RMTHOST 192.168.3.90, MGRPORT 7809

RMTTRAIL ./dirdat/aa

TABLE gguser.t;

GGSCI (oracle11g) 18> add rmttrail ./dirdat/aa, extract eoraaa, megabytes 10

RMTTRAIL added.

GGSCI (oracle11g) 19> info rmttrail *

       Extract Trail: ./dirdat/aa

             Extract: EORAAA

               Seqno: 0

                 RBA: 0

           File Size: 10M

GGSCI (oracle11g) 20> start extract eoraaa

Sending START request to MANAGER ...

EXTRACT EORAAA starting

GGSCI (oracle11g) 21> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EORAAA      00:00:00      00:00:09      

GGSCI (oracle11g) 22> info extract eoraaa, detail

EXTRACT    EORAAA    Initialized   2016-11-20 06:17   Status STOPPED

Checkpoint Lag       00:00:00 (updated 00:10:50 ago)

Log Read Checkpoint  Oracle Redo Logs

               2016-11-20 06:17:37  Thread 1, Seqno 0, RBA 0

               SCN 0.0 (0)

  Target Extract Trails:

  Remote Trail Name              Seqno        RBA     Max MB

  ./dirdat/aa                   0          0        10

  Extract Source                Begin             End             

  Not Available                 * Initialized *   2016-11-20 06:17

Current directory       /u01/glodengate

Report file           /u01/glodengate/dirrpt/EORAAA.rpt

Parameter file         /u01/glodengate/dirprm/eoraaa.prm

Checkpoint file        /u01/glodengate/dirchk/EORAAA.cpe

Process file          /u01/glodengate/dirpcs/EORAAA.pce

Stdout file           /u01/glodengate/dirout/EORAAA.out

Error log            /u01/glodengate/ggserr.log

GGSCI (oracle11g) 23> 


target节点:

GGSCI (oracleogg) 14> edit params ./GLOBALS   --创建GLOBALS参数后必须退出

CHECKPOINTTABLE system.ggchecktable 

GGSCI (oracleogg) 2> dblogin userid system, password system

Successfully logged into database.

GGSCI (oracleogg) 3> add checkpointtable

No checkpoint table specified, using GLOBALS specification (system.ggchecktable)...

Successfully created checkpoint table system.ggchecktable.

GGSCI (oracleogg) 4> add replicat roraaa, exttrail ./dirdat/aa

REPLICAT added.

GGSCI (oracleogg) 5> edit params roraaa

REPLICAT RORAAA

USERID gguser, PASSWORD "system"

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORAAA.DSE, PURGE

MAP gguser.t, TARGET gguser.t;

GGSCI (oracleogg) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    RUNNING     RORAAA      00:00:00      00:00:05  

GGSCI (oracleogg) 9> 


9.验证数据实时更新

source节点:

SQL> select * from t;

   A    B

---------- ----------

   1    a

   2    b

   3    c

   4    d

SQL> insert into t values(5,'e');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

   A    B

---------- ----------

   1    a

   2    b

   3    c

   4    d

   5    e

SQL> 


target节点:

SQL> select * from t;

    A    B

---------- ----------

    4    d

    5    e

    1    a

    2    b

    3    c

SQL> 










本文转自 z597011036 51CTO博客,原文链接:http://blog.51cto.com/tongcheng/1874641,如需转载请自行联系原作者
目录
相关文章
|
6月前
|
SQL API 数据处理
实时计算 Flink版产品使用合集之DataStream方式是否可以实现oracle--&gt;的数据同步
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
6月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之和Oracle数据同步必须是使用主库吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
实时计算 Flink版产品使用合集之和Oracle数据同步必须是使用主库吗
|
3月前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用问题之Oracle数据库是集群部署的,怎么进行数据同步
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
4月前
|
SQL Oracle 关系型数据库
关系型数据库Oracle GoldenGate
【7月更文挑战第7天】
60 3
关系型数据库Oracle GoldenGate
|
4月前
|
SQL 监控 Oracle
关系型数据库Oracle GoldenGate
【7月更文挑战第11天】
34 1
|
5月前
|
Oracle 关系型数据库 数据库
Oracle 11G数据库安装步骤及截图操作2
Oracle 11G数据库安装步骤及截图操作
60 0
|
5月前
|
Oracle 安全 关系型数据库
Oracle 11G数据库安装步骤及截图操作1
Oracle 11G数据库安装步骤及截图操作
101 0
|
6月前
|
Oracle 关系型数据库 Java
java操作多数据源将oracle数据同步达梦数据库
java操作多数据源将oracle数据同步达梦数据库
|
Oracle 关系型数据库 数据库
|
11天前
|
存储 Oracle 关系型数据库
Oracle数据库的应用场景有哪些?
【10月更文挑战第15天】Oracle数据库的应用场景有哪些?
122 64

热门文章

最新文章

推荐镜像

更多