oracle goldengate集成模式实现ddl复制

最新推荐文章于 2021-04-10 17:18:54 发布

TPCloud

最新推荐文章于 2021-04-10 17:18:54 发布

阅读量2.1k

点赞数

分类专栏： Oracle 文章标签： oracle 11.2.0.4 goldengate integrated mode ddl

本文链接：https://blog.csdn.net/tpc4289/article/details/89308607

版权

Oracle 专栏收录该内容

31 篇文章 2 订阅

订阅专栏

在oracle goldengate 11.2版本中，推出了一种新的抽取模式-integrated mode

集成模式相对于传统的抽取模式具有速度快，支持类型更多等优点，官网列出有点如下：

1，Because integrated capture is fully integrated with the database, no additional setup is required to work with Oracle RAC, ASM, and TDE。

2，Integrated capture uses the database logmining server to access the Oracle redo stream, with the benefit of being able to automatically switch between different copies of archive logs or different mirrored versions of the online logs. Thus integrated capture can transparently handle the inavailability of a log file caused by disk corruption, hardware failure, or operator error, assuming that additonal copies of the archived and online logs are available。

3，Integrated capture enables faster filtering of tables。

4，Integrated capture handles point-in-time recovery and RAC integration more efficiently.

5，Integrated capture features integrated log management. The Oracle Recovery Manager (RMAN) automatically retains the archive logs that are needed by Extract.

要利用集成模式实现ddl抽取复制功能，oracle数据库版本建议为11.2.0.3（需要包含对于补丁）及以上数据库版本。本人使用的数据库版本为11.2.0.4，ogg版本为12.3.0.1.2，基本流程如下：

一、安装软件

    #cd /oracle/app/ogg

    #unzip 123012_fbo_ggs_Linux_x64_shiphome.zip

    #chown -R oracle:install ./

    #su - oracle

    $cd /oracle/app/ogg/fbo_ggs_Linux_x64_shiphome/Disk1

    $./runInstaller

二、配置环境

2.1，oracle环境变量

[root@oggdb1 ogg]# su - oracle
[oracle@oggdb1 ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_SID=oggdb
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/db11g
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export LANG=en_US.UTF-8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/oracle/app/ogg/soft:$LD_LIBRARY_PATH

2.2，配置用户

SQL> select force_logging,supplemental_log_data_min from v$database;

FOR SUPPLEME
--- --------
NO  NO

SQL> 
SQL> 
SQL> 
SQL> 
SQL> alter database force logging;

Database altered.

SQL> alter database add supplemental log data;

Database altered.

SQL> select force_logging,supplemental_log_data_min from v$database;

FOR SUPPLEME
--- --------
YES YES

SQL> alter system set enable_goldengate_replication=TRUE scope=both;

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oracle/app/oracle/oradata/oggdb/system01.dbf
/oracle/app/oracle/oradata/oggdb/sysaux01.dbf
/oracle/app/oracle/oradata/oggdb/undotbs01.dbf
/oracle/app/oracle/oradata/oggdb/users01.dbf

SQL> create tablespace dt_ogg_dat datafile '/oracle/app/oracle/oradata/oggdb/dt_ogg_dat.dbf' size 10M autoextend on next 10M maxsize 31G;

Tablespace created.

SQL> create temporary tablespace dt_ogg_temp tempfile '/oracle/app/oracle/oradata/oggdb/dt_ogg_temp.dbf' size 10M autoextend on next 10M maxsize 31G;

Tablespace created.


SQL> create user goldengate identified by goldengate default tablespace dt_ogg_dat temporary tablespace dt_ogg_temp;

User created.

SQL> grant connect,resource to goldengate;

Grant succeeded.

SQL> grant dba to goldengate;

Grant succeeded.



SQL> exec dbms_streams_auth.grant_admin_privilege('GOLDENGATE');

PL/SQL procedure successfully completed.



SQL>exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE (grantee=>'GOLDENGATE', privilege_type=>'capture',grant_select_privileges=>true, do_grants=>TRUE);

PL/SQL procedure successfully complete

三、配置源端同步进程

3.1，配置源端抽取进程


GGSCI (oggdb1) 18> dblogin userid goldengate,password goldengate


GGSCI (oggdb1) 19> register extract ext_a database


GGSCI (oggdb1) 20> add extract,integrated tranlog,begin now


GGSCI (oggdb1) 21> add exttrail ./dirdat/ea,extract ext_a


GGSCI (oggdb1) 22> alter exttrail ./dirdat/ea,extract ext_a,megabytes 100


GGSCI (oggdb1) 23> edit param ext_a

GGSCI (oggdb1) 24> edit param ext_a


extract ext_a
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_HOME="/oracle/app/oracle/product/db11g")
setenv (ORACLE_SID="oggdb")
USERID goldengate,password goldengate
ddl include all
ddloptions report
logallsupcols
updaterecordformat compact
nocompressupdates
exttrail ./dirdat/ea
table tpc.*;

3.2，配置远端dump进程

GGSCI (oggdb1) 12> add extract ext_dp,exttrailsource ./dirdat/ea begin now

GGSCI (oggdb1) 13> add rmttrail ./dirdat/ad,extract ext_dp,megabytes 100

GGSCI (oggdb1) 17> edit param ext_dp


extract ext_dp
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_HOME="/oracle/app/oracle/product/db11g")
passthru
rmthost 192.168.26.11, mgrport 7809
rmttrail ./dirdat/ad

table tpc.*;

注意：配置完成进程后，启动进程进行测试查看。如果启动报错，在ggserr.log日志文件中提示如下报错，则需要打补丁： ext_a.prm: Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later.

去官网下载对于补丁包：p17030189_112040_Generic.zip

解压，然后进行补丁更新：

[oracle@oggdb2 17030189]$ /oracle/app/oracle/product/db11g/OPatch/opatch apply
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/oracle/product/db11g
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/db11g/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /oracle/app/oracle/product/db11g/cfgtoollogs/opatch/17030189_Apr_16_2019_16_47_31/apply2019-04-16_16-47-31PM_1.log

Applying interim patch '17030189' to OH '/oracle/app/oracle/product/db11g'
Verifying environment and performing prerequisite checks...
All checks passed.
Backing up files...

Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...

Verifying the update...
Patch 17030189 successfully applied
Log file location: /oracle/app/oracle/product/db11g/cfgtoollogs/opatch/17030189_Apr_16_2019_16_47_31/apply2019-04-16_16-47-31PM_1.log

OPatch succeeded.



[oracle@oggdb1 17030189]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 16 17:04:53 2019

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> @postinstall.sql

PL/SQL procedure successfully completed.


Function created.


PL/SQL procedure successfully completed.












Check /oracle/app/oracle/cfgtoollogs/postinstall/postinstall_OGGDB_2019Apr16_17_05_07.log for errors

Session altered.


PL/SQL procedure successfully completed.


Procedure created.


Grant succeeded.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.





1 row selected.





1 row selected.

old   1: ALTER SESSION SET CURRENT_SCHEMA = &username
new   1: ALTER SESSION SET CURRENT_SCHEMA = SYS

Session altered.

old   1: SELECT 'Calling &sf on ' || SYSTIMESTAMP FROM dual
new   1: SELECT 'Calling ?/rdbms/admin/prvtlmcb.plb on ' || SYSTIMESTAMP FROM dual
Calling ?/rdbms/admin/prvtlmcb.plb on 16-APR-19 05.05.07.270710 PM +08:00

1 row selected.


Function created.

No errors.

Grant succeeded.


Grant succeeded.


Grant succeeded.


Procedure created.

No errors.

Grant succeeded.


Grant succeeded.


Grant succeeded.


Package body created.

No errors.

Procedure created.

No errors.

PL/SQL procedure successfully completed.


Trigger created.


PL/SQL procedure successfully completed.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Function created.

No errors.

Function created.

No errors.

Function created.

No errors.

Function created.

No errors.

Session altered.


PL/SQL procedure successfully completed.


Commit complete.


14-APR-19 09.10.22.575818 PM
APPLY                          SERVER
11.2.0.4                                0
Patchset 11.2.0.2.0
PSU

16-APR-19 05.05.08.000000 PM
APPLY
                                 17030189

Patch 17030189 applied



2 rows selected.

四、配置目标端进程

[oracle@oggdb2 soft]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec  8 2017 21:13:00
Operating system character set identified as UTF-8.

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



GGSCI (oggdb2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           


GGSCI (oggdb2) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           


GGSCI (oggdb2) 3> edit param mgr



PORT 7809
dynamicportlist 7840-7850
autorestart extract *,retries 5,waitminutes 3
purgeoldextracts ./dirdat/*,usecheckpoints,minkeepfiles 100




GGSCI (oggdb2) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.


GGSCI (oggdb2 as goldengate@oggdb) 4> add checkpointtable goldengate.checkpt

Successfully created checkpoint table goldengate.checkpt.



GGSCI (oggdb2 as goldengate@oggdb) 6> add replicat rep01, exttrail ./dirdat/da checkpointtable goldengate.checkpt
REPLICAT added.




GGSCI (oggdb2 as goldengate@oggdb) 16> edit param rep01


replicat rep01
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_HOME="/oracle/app/oracle/product/db11g")
setenv (ORACLE_SID="oggdb")
userid goldengate,password goldengate
ddl include mapped
ddlerror 24344,ignore
ddlerror 955,ignore
reperror default,discard
discardfile ./dirrpt/rep_discard.dsc,append
assumetargetdefs
handlecollisions
allownoopupdates

map tpc.*,target tpc.*;
~

五、启动进程，然后在目标端建立相应表，查看目标端，数据是否同步。

在源端创建表和插入数据：

[oracle@oggdb1 ~]$ sqlplus tpc/tpc

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 16 18:35:06 2019

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> 
SQL> create table tpc_user(user_id number primary key,user_name varchar2(30),sex varchar(10),address varchar(50));

Table created.


SQL> insert into tpc_user(user_id,user_name,sex,address) values(1,'tpc','man','hunan changsha');

1 row created.

SQL> commit;


SQL> insert into tpc_user(user_id,user_name,sex,address) values(2,'jlz','man','hunan changsha');

1 row created.

SQL> commit;

Commit complete.

在目标端进行测试：

[oracle@oggdb2 soft]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 17 08:56:43 2019

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> desc tpc.tpc_user;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER
 USER_NAME                                          VARCHAR2(30)
 SEX                                                VARCHAR2(10)
 ADDRESS                                            VARCHAR2(50)

SQL> select * from tpc.tpc_user;

   USER_ID USER_NAME                      SEX        ADDRESS
---------- ------------------------------ ---------- --------------------------------------------------
         1 tpc                            man        hunan changsha
         2 jlz                            man        hunan changsha

SQL>

至此，整个数据同步配置完成，ddl测试通过。