配置部署Oracle Goldengate Version 11.2.1.0.1

配置部署oracle Goldengate Version 11.2.1.0.1 

配置用户环境变量

vi .bash_profile

添加：

export OGG_HOME=/data0/temp/ogg

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;

export LD_LIBRARY_PATH

查看数据是否开启归档

sql> arcHive log list； --Enabled

Configuring logging properties

Oracle GoldenGate relies on the redo logs to capture the data and metadata that it

needs to replicate source transactions. The Oracle redo logs on the source system must

be configured properly before you start Oracle GoldenGate processing. Because redo

volume is increased as the result of this required logging, you might want to wait until

just before you start Oracle GoldenGate processing to enable the logging.

This section addresses the following logging levels:

■ Enabling database-level supplemental logging

■ Enabling schema-level supplemental logging

■ Enabling table-level supplemental logging

查看数据库日志打开情况

SQL>  Select SUPPLEMENTAL_LOG_DATA_MIN,

  2   SUPPLEMENTAL_LOG_DATA_PK,

  3  SUPPLEMENTAL_LOG_DATA_UI,

  4   SUPPLEMENTAL_LOG_DATA_FK,

  5   SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEME SUP SUP SUP SUP

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

NO       NO  NO  NO  NO

打开附加日志并切换日志（保证Online redo log和Archive log一致）

alter database add supplemental log data ;

alter database add supplemental log data (primary key, unique,foreign key) columns;

alter system switch logfile;

再次检查日志打开情况：

SQL>  Select SUPPLEMENTAL_LOG_DATA_MIN,

  2   SUPPLEMENTAL_LOG_DATA_PK,

  3  SUPPLEMENTAL_LOG_DATA_UI,

  4   SUPPLEMENTAL_LOG_DATA_FK,

 SUPPLEMENTAL_LOG_DATA_ALL from v$database;

  5  

SUPPLEME SUP SUP SUP SUP

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

YES      YES YES YES NO

注：确保最小附加日志,pk,uk,fk附加日志打开。而all columns的附加日志关闭；

如果all columns的附加日志打开的话，则需要使用以下语句予以关闭：

alter database drop supplemental log data (ALL) columns;

如果出现问题，可以通过以下语句进行回退：

alter database drop supplemental log data ;

alter database drop supplemental log data (primary key, unique,foreign key) columns;

alter system switch logfile;

数据库开启强日志模式：

SQL>alter database force logging;

--注：这里source 库已是dataguard primary DB 所以强制日志模式已经打开：

查看：

SQL> select FORCE_LOGGING from v$database;

FOR

---

YES

SQL> 

关闭recyclebin 

--注在system一级,不加DEFERRED参数是不允许修改的,

SQL> alter system set recyclebin=off DEFERRED;

System altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE

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

recyclebin                           string      on

SQL>

退出，重新登录查看

[oracle@dkdb_primary ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 13 18:50:42 2015

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

Connected to:

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> show parameter recyclebin

NAME                                 TYPE        VALUE

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

recyclebin                           string      OFF

SQL> 

安装goldengate在source端和target端上安装

[oracle@dkdb_primary ogg]$ ./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 (dkdb_primary) 1> CREATE SUBDIRS

Creating subdirectories under current directory /data0/temp/ogg

Parameter files                /data0/temp/ogg/dirprm: already exists

Report files                   /data0/temp/ogg/dirrpt: created

Checkpoint files               /data0/temp/ogg/dirchk: created

Process status files           /data0/temp/ogg/dirpcs: created

SQL script files               /data0/temp/ogg/dirsql: created

Database definitions files     /data0/temp/ogg/dirdef: created

Extract data files             /data0/temp/ogg/dirdat: created

Temporary files                /data0/temp/ogg/dirtmp: created

Stdout files                   /data0/temp/ogg/dirout: created

GGSCI (dkdb_primary) 2> 

GGSCI (dkdb_primary) 3> info all 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED         

安装oracle sequence 支持

这里的用户和创建DDL同步所需用户一样。（登陆源端和目标端执行创建用户）：

SQL> create tablespace urogg  datafile '/opt/oracle/database/oradata/dkhlstd/dkhlstd/urogg01.dbf' size 100M autoextend on NOLOGGING;

Tablespace created.

SQL> create user urogg identified by test_oracle1_P default tablespace urogg  temporary tablespace temp;

User created.

赋权：

GRANT CONNECT, RESOURCE, DBA TO urogg;

GRANT CREATE TABLE,CREATE SEQUENCE TO urogg;--这里是用于用户安装DDL执行SQL脚本时用；

编辑GLOBALS 参数 

进入./ggsci在所有节点执行 

EDIT PARAMS ./GLOBALS  并添加

GGSCHEMA urogg                    --指定的进行DDL复制的数据库用户 

保存退出，并进入$OGG_HOME 登陆sqlplus  / as sysdba 执行脚本

SQL> @sequence.sql

Please enter the name of a schema for the GoldenGate database objects:

urogg

Setting schema name to UROGG

source 端：

SQL> GRANT EXECUTE on urogg.updateSequence TO urogg;

Grant succeeded.

SQL> ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Table altered.

SQL> 

target端：

SQL> GRANT EXECUTE on urogg.replicateSequence TO urogg;

Grant succeeded.

编辑target 端 GLOBALS

GGSCI (dkdb_ogg) 10> edit params ./GLOBALS

--GGSCHEMA urogg

CHECKPOINTTABLE urogg.checktable --添加checkpointtable

添加checkpointtable

GGSCI (dkdb_ogg) 16> ADD CHECKPOINTTABLE DKOGG.CHECKTABLE

Successfully created checkpoint table urogg.checktable.

添加表级附加日志：

GGSCI (dkdb_primary) 1> dblogin userid urogg,passWord test_oracle1_P

Successfully logged into database.

GGSCI (dkdb_primary) 5> show user

Parameter settings:

SET SUBDIRS    ON

SET DEBUG      OFF

Current directory: /data0/temp/ogg

Using subdirectories for all process files

Editor:  vi

Reports (.rpt)                 /data0/temp/ogg/dirrpt

Parameters (.prm)              /data0/temp/ogg/dirprm

Stdout (.out)                  /data0/temp/ogg/dirout

Replicat Checkpoints (.cpr)    /data0/temp/ogg/dirchk

Extract Checkpoints (.cpe)     /data0/temp/ogg/dirchk

Process Status (.pcs)          /data0/temp/ogg/dirpcs

SQL Scripts (.sql)             /data0/temp/ogg/dirsql

Database Definitions (.def)    /data0/temp/ogg/dirdef

GGSCI (dkdb_primary) 3> add trandata TEST_D.*

这里会出现警告：

WARNING OGG-00869 、WARNING OGG-01387 

2015-05-11 20:09:36  WARNING OGG-00869  No unique key is defined for table 'T_APPDETAIL'.

 All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.

检查确认：

GGSCI (dkdb_primary) 4> info trandata TEST_D.*

Logging of supplemental redo log data is enabled for table TEST_D.api_SWITCH.

Columns supplementally logged for table TEST_D.API_SWITCH: API_NAME, ISVALID, API_COMMENT, API_ID.

注：add trandata TEST_D.* 后面没有;号

否则报错如下：

GGSCI (dkdb_primary) 2> add trandata test_desk.*;

ERROR: No viable tables matched specification.

创建 manager 进程：

GGSCI (dkdb_primary) 4> edit param mgr

PORT 7801

DYNAMICPORTLIST 7802-7820

PURGEOLDEXTRACTS /opt/ogg/dirdat/ *, USECHECKPOINTS, MINKEEPDAYS 7                          

--配置DDL参数区域

--trail file 保留7天

PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10,FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10

--配置一般日志信息区域

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

--##注 ./dirdat

--添加replicat进程

ADD REPLICAT repdr1, EXTTRAIL ./dirdat/sa

ADD REPLICAT repdr2, EXTTRAIL ./dirdat/sa

--启动replicat进程

GGSCI (dkdb_ogg) 65> start replicat  repdr, aftercsn 1442477

Sending START request to MANAGER ...

REPLICAT REPDR starting

--启动replicat进程方式

--指定SCN号启动

start replicat  repdr, aftercsn 1442477

--指定rba号启动

start replicat  repdr1, extseqno 5, RBA 2535303

start replicat  repdr2, extseqno 5, RBA 2535303

--指定具体时间启动

alter REPDR1,begin yyyy-mm-dd

start repdr1 

GGSCI (dkdb_ogg) 66> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

REPLICAT    RUNNING     REPDR       00:00:00      00:00:01  

 create table WIDGET_BAK as select * from P_WIDGET where 1=2     空表

 DML 测试：

 update table P_WIDGET set state=6

 commit;

在standby 端查看：

SQL> select state from test_desk.P_WIDGET;

     STATE

----------

         6

         6

         6 

查看source（primary）端 extract 进程

里面会出现一个警告：WARNING OGG-00869

查看target端 ：

[root@dkdb_ogg ~]# su - oracle

[oracle@dkdb_ogg ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed May 13 16:59:48 2015

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

Connected to:

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 state from test_desk.P_WIDGET;

     STATE

----------

         6

         6

      ......

         6

34 rows selected.

到这里说明DML已经测试成功！

DDL 测试：

--##Source 端（primary_db）：

创建表

SQL> CONN test_desk/pwd4test

Connected.

SQL> create table WIDGET_BAK as select * from P_WIDGET where 1=2;

--##Standby_db端查看

SQL>  CONN test_desk/pwd4test

Connected.

SQL> select count(*) from WIDGET_BAK;

  COUNT(*)

----------

         0

SQL> select count(*) from P_WIDGET;

  COUNT(*)

----------

        34

SQL> 

--##OGG_Target端 

view report repdr 进程日志

2015-05-13 14:04:18  INFO    OGG-00482  DDL found, operation [create table WIDGET_BAK as select * from P_WIDGET where 1=2  (size 80)].

2015-05-13 14:04:18  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [create table TEST_D."WIDGET_BAK" as select * from QN_DESKTOP_

WIDGET where 1=2  (size 92)].

2015-05-13 14:04:18  INFO    OGG-00487  DDL operation included [include mapped], optype [CREATE], objtype [TABLE], objowner [TEST_D], objname [WIDGET_BAK]

.

2015-05-13 14:04:18  INFO    OGG-01407  Setting current schema for DDL operation to [TEST_D].

2015-05-13 14:04:18  INFO    OGG-00484  Executing DDL operation.

2015-05-13 14:04:18  INFO    OGG-00483  DDL operation successful.

2015-05-13 14:04:18  INFO    OGG-01408  Restoring current schema for DDL operation to [urogg].

这里说明已经同步成功，进入库查看

SQL> show user

USER is "TEST_D"

SQL> select count(*) from WIDGET_BAK;

  COUNT(*)

----------

         0

SQL> select count(*) from P_WIDGET;

  COUNT(*)

----------

        34

SQL> 

查看replicat进程：

GGSCI (dkdb_ogg) 16> info replicat repdr detail

REPLICAT   REPDR     Last Started 2015-05-13 12:00   Status RUNNING

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

Log Read Checkpoint  File ./dirdat/sa000000

                     2015-05-13 14:04:12.382273  RBA 43978

  Extract Source                          Begin             End             

  ./dirdat/sa000000                       2015-05-13 09:12  2015-05-13 14:04

  ./dirdat/sa000000                       2015-05-13 09:12  First Record    

  ./dirdat/sa000000                       2015-05-13 09:12  2015-05-13 09:12

  ./dirdat/sa000000                       2015-05-13 09:12  First Record    

  ./dirdat/sa000000                       * Initialized *   2015-05-13 09:12

  ./dirdat/sa000000                       * Initialized *   First Record    

Current directory    /data0/temp/ogg

Report file          /data0/temp/ogg/dirrpt/REPDR.rpt

Parameter file       /data0/temp/ogg/dirprm/repdr.prm

Checkpoint file      /data0/temp/ogg/dirchk/REPDR.cpr

Checkpoint table     urogg.checktable

Process file         /data0/temp/ogg/dirpcs/REPDR.pcr

Stdout file          /data0/temp/ogg/dirout/REPDR.out

Error log            /data0/temp/ogg/ggserr.log

GGSCI (dkdb_ogg) 17>