OGG(oracle goldengate)实现oracle到oracle数据DDL同步

最新推荐文章于 2024-05-16 11:10:43 发布

落俗zp

最新推荐文章于 2024-05-16 11:10:43 发布

阅读量1.3k

点赞数

分类专栏： Oracle GoldenGate安装使用

本文链接：https://blog.csdn.net/weixin_41229271/article/details/101217231

版权

Oracle GoldenGate安装使用专栏收录该内容

5 篇文章 1 订阅

订阅专栏

注：本篇文章是在已经配置过DML同步或者源端数据库已经开启归档、添加了附加日志等基础上记录的，如未设置，请参考另外一篇：https://blog.csdn.net/weixin_41229271/article/details/101196319

然后再进行下面的步骤。

源端在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.


---------------------------2:
SQL> @ddl_setup.sql

ORA-20783:
ORA-20783:
Oracle GoldenGate DDL Replication setup:
*** Please move OGG to its own tablespace
ORA-06512: 在 line 34
解决：建立ogg管理用户单独的表空间
http://blog.itpub.net/7199859/viewspace-1062113/
重新执行：
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
------------------------------------------------------------------------------------------------------------------------
NONE

LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/oracle/product/12.2.0.1/dbhome_1/rdbms/log/ggs_ddl_trace.log

Analyzing installation status...


VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005

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

Script complete.



-----------------------------3:
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
已写入 file role_setup_set.txt

PL/SQL 过程已成功完成。


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.


根据提示对ogg赋予ggs_ggsuser_role角色权限：
grant ggs_ggsuser_role to ogg;
SQL> grant ggs_ggsuser_role to ogg;

授权成功。

SQL> @ddl_enable.sql

触发器已更改

 执行dbmspool包将在数据库中创建DBMS_SHARED_POOL包，之后ddl_pin包需要用到：
SQL> @?/rdbms/admin/dbmspool.sql

会话已更改。


程序包已创建。


授权成功。


会话已更改。


执行ddl_pin.sql通过dbms_shared_pool.keep存储过程将DDLReplication相关对象keep在共享池中，以保证这些对象不要RELOAD，提升性能:
SQL> @ddl_pin.sql ogg

PL/SQL 过程已成功完成。


PL/SQL 过程已成功完成。


PL/SQL 过程已成功完成。

源端数据库创建测试用户

create user stat identified by stat;
grant connect,resource,dba to stat;、

抽取进程配置

GGSCI (source) 41> edit param ex_ddl
extract ex_ddl
DYNAMICRESOLUTION
SETENV (ORACLE_SID = "source")
SETENV (ORACLE_HOME = "/oracle/product/12.2.0.1/dbhome_1")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,password 123456
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
DBOPTIONS  ALLOWUNUSEDCOLUMN
WARNLONGTRANS 3h,CHECKINTERVAL 20000m
exttrail /home/oracle/oggs/dirdat/e1
NUMFILES 6000
EOFDELAYCSECS 30
GETTRUNCATES
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576 
BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
NOCOMPRESSDELETES
ddl include all
table stat.*;


GGSCI (source) 19>  add extract ex_ddl,tranlog,begin now
GGSCI (source) 20>  add exttrail /home/oracle/oggs/dirdat/e1,extract ex_ddl

投递进程配置

GGSCI (source) 44> edit param pm_ddl
extract pm_ddl
SETENV (ORACLE_SID = "source")
SETENV (ORACLE_HOME = "/oracle/product/12.2.0.1/dbhome_1")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,password 123456
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 6000
EOFDELAYCSECS 30
RMTHOST 192.168.17.129, MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
RMTTRAIL /home/oracle/oggt/dirdat/p1
GETTRUNCATES
PASSTHRU
DYNAMICRESOLUTION
GETUPDATEBEFORES
--NOCOMPRESSDELETES
ddl include all
table stat.*;


GGSCI (source) 23>  add extract pm_ddl,exttrailsource /home/oracle/oggs/dirdat/e1
GGSCI (source) 24>  add rmttrail /home/oracle/oggt/dirdat/p1,extract pm_ddl

应用进程配置

GGSCI (target) 22> edit param re_ddl
replicat re_ddl
DYNAMICRESOLUTION
SETENV (ORACLE_SID = "target")
SETENV (ORACLE_HOME = "/oracle/product/12.2.0.1/dbhome_1")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, password 123456
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND
discardfile /home/oracle/oggt/dirrpt/RE_DDL.dsc,APPEND,MEGABYTES 10
DISCARDROLLOVER AT 06:00
GETTRUNCATES
NUMFILES 5000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
GETUPDATEBEFORES
ddl include all 
map stat.*, target stat.*;

GGSCI (target) 1> add replicat re_ddl, exttrail /home/oracle/oggt/dirdat/p1,CHECKPOINTTABLE ogg.checkpoint

测试结果

无主键
创建新表》》同步成功
插入数据》》同步成功
添加新列》》同步成功
修改数据》》
2019-04-08 13:33:48  WARNING OGG-06439  No unique key is defined for table AAA. All viable columns will be used to represent the key, but may not guar
antee uniqueness. KEYCOLS may be used to define the key.
2019-04-08 13:33:48  WARNING OGG-01154  SQL error 1403 mapping STAT.AAA to STAT.AAA OCI Error ORA-01403: no data found, SQL <UPDATE "STAT"."AAA" x SET
 x."AAA" = :a3,x."BBB" = :a4,x."CCC" = :a5 WHERE x."AAA" is NULL AND x."BBB" is NULL AND x."CCC" is NULL AND ROWNUM = 1>.

 
有主键
创建新表》》同步成功
插入数据》》同步成功
添加新列》》同步成功
修改数据》》出现同样错误


现创建表后添加附加日志

有主键表
创建新表》》同步成功
GGSCI (source) 123> add trandata stat.AAA
插入数据》》同步成功
添加新列》》同步成功
插入数据》》同步成功
修改数据》》同步成功
删除数据》》同步成功
删除某列》》同步成功
删除整表》》同步成功

无主键表
创建新表》》同步成功
GGSCI (source) 125> add trandata stat.BBB
插入数据》》同步成功
添加新列》》同步成功
插入数据》》同步成功
修改数据》》同步成功
删除数据》》同步成功
删除某列》》同步成功
删除整表》》同步成功
删除附加日志》》修改数据后同步失败，报错相同


下面修改数据库
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> alter database add supplemental log data (unique) columns;
SQL> alter database add supplemental log data (foreign key) columns;
SQL> alter database add supplemental log data (all) columns;

SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_ui, supplemental_log_data_fk,supplemental_log_data_all FROM v$database;
将五个参数设置成YES后不单独add表附加日志也可以同步数据。

修改数据库设置后，有无主键的表上述操作均无问题

添加表主键》》同步成功
添加主键后修改数据》》同步成功

删除主键》》同步成功
删除主键后修改数据》》同步成功

落俗zp

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
OGG(oracle goldengate)实现oracle到oracle数据DDL同步

注：本篇文章是在已经配置过DML同步或者源端数据库已经开启归档、添加了附加日志等基础上记录的，如未设置，请参考另外一篇：https://blog.csdn.net/weixin_41229271/article/details/101196319然后再进行下面的步骤。源端在ogg安装目录下登陆数据库SQL> @marker_setup.sqlMarker setup scr...
复制链接

扫一扫