Oracle11gR2_使用GoldenGate配置单向同步

来源：互联网发布：mediaworld.it 编辑：程序博客网时间：2024/05/19 18:42

Oracle11gR2_使用GoldenGate配置单向同步

这里测试开启DDL同步，忽略初始化过程的单向同步。Oracle测试模式为test。

os：oel 6.5

db：11.2.0.4

ogg：11.2.1.0.1

本测试在源端没有使用pump进程。

1、安装GoldenGate

1.1 解压安装文件

源端和目标端都使用oracle用户进行下列操作：

创建ogg目录并安装：

mkdir -p /u01/ogg
tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/ogg

1.2 添加环境变量

在源端和目标端编辑oracle用户环境变量，在文件~/.bash_profile中加入下列行：

export PATH=/u01/ogg:$PATH
export LD_LIBRARY_PATH=/u01/ogg:$LD_LIBRARY_PATH

并使其生效：
source ~/.bash_profile

1.3 使用ggsci创建目录

在源端和目标端都执行：

[oracle@oraclesrc ogg]$ ggsci
GGSCI (oraclesrc) 1> create subdirs

2、配置源端数据库强制归档

查看源端数据库归档情况：

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;

LOG_MODE     SUPPLEME FOR
------------ -------- ---
NOARCHIVELOG NO       NO

修改归档模式：

SQL>shutdown immediate
SQL>startup mount
SQL>alter database archivelog;
SQL>alter database open;
SQL>alter database force logging;
SQL>alter database add supplemental log data;

3、开启DDL同步

配置DDL同步需要在源端执行以下操作

3.1 创建存放DDL信息的user

SQL> create user ogg identified by ogg default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> grant create table,create sequence to ogg;

Grant succeeded.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

在目标端同样创建一次该用户，用来存放同步相关信息。

3.2 执行脚本

进入OGG目录执行以下脚本：

[oracle@oraclesrc ~]$ cd /u01/ogg

SQL> @marker_setup.sql

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script complete.

SQL> @ddl_setup.sql

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ogg

Working, please wait ...
Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using OGG as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

CLEAR_TRACE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

CREATE_TRACE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

TRACE_PUT_LINE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

INITIAL_SETUP STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDL IGNORE TABLE
-----------------------------------
OK

DDL IGNORE LOG TABLE
-----------------------------------
OK

DDLAUX PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO PACKAGE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

SYS.DDLCTXINFO PACKAGE BODY STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDL HISTORY TABLE
-----------------------------------
OK

DDL HISTORY TABLE(1)
-----------------------------------
OK

DDL DUMP TABLES
-----------------------------------
OK

DDL DUMP COLUMNS
-----------------------------------
OK

DDL DUMP LOG GROUPS
-----------------------------------
OK

DDL DUMP PARTITIONS
-----------------------------------
OK

DDL DUMP PRIMARY KEYS
-----------------------------------
OK

DDL SEQUENCE
-----------------------------------
OK

GGS_TEMP_COLS
-----------------------------------
OK

GGS_TEMP_UK
-----------------------------------
OK

DDL TRIGGER CODE STATUS:

Line/pos             Error
-------------------- -----------------------------------------------------------------
No errors            No errors

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK

DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED

STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF

DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0

DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
0

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orasrc/orasrc/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL> @role_setup.sql

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ogg
Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

SQL> grant GGS_GGSUSER_ROLE to ogg;

Grant succeeded.

SQL> @ddl_enable.sql;

Trigger altered.

至此DDL同步配置完成。

4、配置OGG进程

4.1 配置Manager进程

在源端和目标端配置Manager进程并启动：

注意当前目录为ogg。

[oracle@oraclesrc 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 (oraclesrc) 1> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     STOPPED

GGSCI (oraclesrc) 2> edit params mgr

输入：
PORT 7809

这里输入了端口号，保存退出。

GGSCI (oraclesrc) 3> start manager

Manager started.

4.2 配置源数据库的extract进程

测试登录数据库：

GGSCI (oraclesrc) 4> dblogin userid ogg@orasrc,password ogg
Successfully logged into database.

配置extract进程

GGSCI (oraclesrc) 5> add extract ext1,tranlog,begin now
EXTRACT added.

GGSCI (oraclesrc) 6> add rmttrail /u01/ogg/dirdat/lt,extract ext1
EXTTRAIL added.

GGSCI (oraclesrc) 7> edit params ext1

加入如下行：

extract ext1

userid ogg@orasrc, password ogg

rmthost 172.26.181.103, mgrport 7809

rmttrail /u01/ogg/dirdat/lt

ddl include mapped objname test.*;

table test.*;

GGSCI (oraclesrc) 8> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     EXT1        00:00:00      00:03:26

4.3 配置目标数据库的replicat进程

测试连接：

GGSCI (oracledest) 3> dblogin userid ogg@oradest,password ogg
Successfully logged into database.

GGSCI (oracledest) 4> add checkpointtable ogg.checkpoint

Successfully created checkpoint table ogg.checkpoint.

GGSCI (oracledest) 5> add replicat rep1,exttrail /u01/ogg/dirdat/lt,checkpointtable ogg.checkpoint
REPLICAT added.

GGSCI (oracledest) 6> edit params rep1

添加如下行：

replicat rep1
ASSUMETARGETDEFS
userid ogg@oradest,password ogg
discardfile /u01/ogg/dirdat/rep1_discard.txt,append,megabytes 10
DDL
map test.*,target test.*;

5、启动同步

源数据库：

GGSCI (oraclesrc) 1> start extract ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (oraclesrc) 2> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT1        00:16:47      00:00:00

目标数据库：

GGSCI (oracledest) 7> start replicat rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (oracledest) 8> info all

Program     Status      Group       Lag at Chkpt Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REP1        00:00:00      00:00:01

6、测试效果

此时源库和目标库test用户数据如下：

SQL> conn test/test
Connected.
SQL> select * from t1;

no rows selected

在源库插入数据并创建新表：

SQL> insert into t1 values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> create table t2 as select * from t1;

Table created.

在目标库查看数据同步情况：

SQL> select * from t1;

        C1
----------
         1

SQL> select * from t2;

        C1
----------
         1

DML及DDL同步正常。

7、注意事项

实际部署生产系统时，可能需要在目标端replicat参数中添加以下行，来禁止triggers和constraint deferred：

DBOPTIONS SUPPRESSTRIGGERS（For Oracle10.2.0.5 and later patches to 10.2.0.5, and for Oracle 11.2.0.2 and later 11gR2 versions）

DBOPTIONS DEFERREFCONST（For Oracle9.2.0.7 and later）

在更早的Oracle版本下，就需要手动禁止trigger和constraint deferred了。

另外还需要禁止带有DML的JOB。

最后还需要在源端配置pump进程。

0 0