Oracle GoldenGate：配置各种问题

最新推荐文章于 2022-07-11 14:40:44 发布

薛定谔的DBA

最新推荐文章于 2022-07-11 14:40:44 发布

阅读量5.9k

点赞数

分类专栏： GoldenGate

本文链接：https://blog.csdn.net/kk185800961/article/details/51489574

版权

GoldenGate 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

使用 Oracle GoldenGate 在 Microsoft SQL Server 和 Oracle Database 之间复制事务

（一）

在源服务器配置OGG时，添加表出错！

GGSCI(MSSQL)> add trandata dbo.mytable

错误如下：

2016-05-24 13:26:39  WARNING OGG-00552  Database operation failed: SQLExecDirect error: 
if not exists (SELECT * FROM master.dbo.sysdatabases WHERE name = N'MyDB' collate database_default AND (category & 1) = 1)
begin
	exec master..sp_replicationdboption @dbname = N'MyDB' , @optname = N'publish' , @value = N'true'
end
if not exists (select * from syspublications where name = N'GoldenGate MyDB Publisher')
begin
	exec sp_addpublication 
	@publication = N'GoldenGate MyDB Publisher', 
	@description = N'GoldenGate Publisher for [MyDB] Database', 
	@sync_method = N'native', 
	@retention = 0, 
	@allow_push = N'true', 
	@allow_pull = N'true', 
	@allow_anonymous = N'false', 
	@enabled_for_internet = N'false', 
	@snapshot_in_defaultfolder = N'true', 
	@compress_snapshot = N'false', 
	@ftp_port = 21, 
	@ftp_login = N'anonymous', 
	@allow_subscription_copy = N'false', 
	@add_to_active_directory = N'false',
	@repl_freq = N'continuous', 
	@status = N'active', 
	@independent_agent = N'true', 
	@immediate_sync = N'false', 
	@allow_sync_tran = N'false', 
	@autogen_sync_procs = N'false', 
	@allow_queued_tran = N'false', 
	@allow_dts = N'false', 
	@replicate_ddl =	1, 
	@allow_initialize_from_backup = N'true', 
	@enabled_for_p2p = N'false', 
	@enabled_for_het_sub = N'false'
end.
ODBC error: SQLSTATE 01000 native database error 14037. 
[Microsoft][ODBC SQL Server Driver][SQL Server]The Distributor has not been installed correctly. 
Could not enable database for publishing[Microsoft][ODBC SQL Server Driver][SQL Server]
The replication option 'publish' of database 'MyDB' has been set to false.

2016-05-24 13:26:39  WARNING OGG-00782  Error in changing transaction logging for table: 'dbo.mytable'.
ERROR: ODBC Error occurred. See event log for details..

原因：

MS SQL Server 非企业版！

（二）

错误如下：

GGSCI (hzc as ogg_msuser@AW2014_ODBC) 5> add trandata Production.Product

2016-05-28 18:20:50  WARNING OGG-00552  Database operation failed: SQLExecDirect error: EXECUTE sys.sp_cdc_enable_db

if 0 = (select st.is_tracked_by_cdc from sys.tables as st where st.object_id = object_id(N'Production.Product'))
   AND 0 = (select st.is_replicated from sys.tables as st where st.object_id = object_id(N'Production.Product'))
BEGIN
DECLARE @capture_instance sysname = N'OracleGG_' + cast(object_id(N'Production.Product') as sysname)

CREATE TABLE #ggsTabKeys (db sysname, name sysname, owner sysname, column_name sysname, key_seq int, pk_name sysname)
INSERT INTO #ggsTabKeys EXEC sp_pkeys 'Product', 'Production'
IF 0 = (SELECT COUNT(*) FROM #ggsTabKeys)
BEGIN
    INSERT INTO #ggsTabKeys
    SELECT TOP (1) DB_NAME(), '', '', name, 1, '' FROM sys.columns sc
    WHERE sc.object_id = OBJECT_ID(N'Production.Product')
    AND is_computed = 0
    AND max_length > 0
    ORDER BY max_length
END
IF 0 = (select COUNT(*) from #ggsTabKeys)
BEGIN
    INSERT INTO #ggsTabKeys
    SELECT TOP (1) DB_NAME(), '', '', name, 1, '' FROM sys.columns sc
    WHERE sc.object_id = OBJECT_ID(N'Production.Product')
    AND is_computed = 0
    AND max_length > 0
    ORDER BY max_length
END
DECLARE @cols NVARCHAR(max)
SELECT  @cols =
REPLACE(REPLACE(REPLACE(
STUFF(( SELECT
         ',' + QUOTENAME( t.column_name)
        FROM    #ggsTabKeys AS t
        FOR XML PATH('')
       ), 1, 1, '')
       ,'<','<'),'>','>'),'&','&')
execute sys.sp_cdc_enable_table
    @source_schema = N'Production'
  , @source_name = N'Product'
  , @role_name = NULL
  , @captured_column_list = @cols
  , @capture_instance = @capture_instance
IF EXISTS(SELECT OBJECT_ID('tempdb..#ggsTabKeys'))
BEGIN
  DROP TABLE #ggsTabKeys
END
end
. ODBC error: SQLSTATE 37000 native database error 22901. [Microsoft][SQL Server Native Client 11.0][SQL Server]正在直接执行 SQL；
无游标[Microsoft][SQL Server Native Client 11.0][SQL Server]无法更新元数据来指示已对数据库 AdventureWorks2014 启用了变更数据捕获。
执行命令 'SetCDCTracked(Value = 1)' 时失败。
返回的错误为 15517: '无法作为数据库主体执行，因为主体 "dbo" 不存在、无法模拟这种类型的主体，或您没有所需的权限。'。
请使用此操作和错误来确定失败的原因并重新提交请求[Microsoft][SQL Server Native Client 11.0][SQL Server]未针对数据库 'AdventureWorks2014' 启用变更数据捕获。
请确保已设置正确的数据库上下文，然后重试该操作。若要报告数据库已启用变更数据捕获这一情况，
请查询 sys.databases 目录视图中的 is_cdc_enabled 列.

2016-05-28 18:20:50  WARNING OGG-00782  Error in changing transaction logging for table: 'Production.Product'.
ERROR: ODBC Error occurred. See event log for details..

原因及解决：

某些存储过程使用了具有WITHEXECUTE AS 的选项,使其在当前库具有了某个架构，当在别的地方执行时，由于没有这个架构，所以就报错

ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2014] TO [sa]

（三）

在数据库中执行ogg目录中的脚本

SQL> @/u01/app/oracle/gg/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_orcluser
（一直等待中……）

解决：进入ogg目录连接数据库再执行脚本

[oracle@server1 ~]$ cd /u01/app/oracle/gg
[oracle@server1 gg]$ sqlplus ogg_orcluser/ogg_orcluser
SQL> @marker_setup.sql

（四）

linux中的oracle 用户执行ogg目录中的命令（gg 目录权限： oracle:oinstall）

错误如下：

[oracle@server1 gg]$ ./replicat paramfile dirprm/inload.prm
./replicat: error while loading shared libraries: libnnz11.so: 
cannot open shared object file: No such file or directory

原因及解决：俩文件未找到。

[oracle@server1 gg]$ ldd ggsci
		…………
        libnnz11.so => not found
        libclntsh.so.11.1 => not found
		…………

文件查看是存在的！

ls -ald $ORACLE_HOME/lib/libnnz11.so
ls -ald $ORACLE_HOME/lib/libclntsh.so.11.1

变量输出为空：

echo $LD_LIBRARY_PATH

设置变量：

[oracle@server1 gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_BASE/gg

此时回到gg目录执行：
[oracle@server1 ~]$ cd /u01/app/oracle/gg
[oracle@server1 gg]$ ldd ggsci
[oracle@server1 gg]$ ./replicat paramfile dirprm/inload.prm

（五）

启动复制线程错误：

GGSCI (server1) 45> start msrep
Sending START request to MANAGER ...
ERROR: opening port for MGR MGR (Connection timed out).

解决：

查看linux /etc/hosts，发现本地服务器名绑定了一个错误的ip ，改正确的！

[oracle@server1 gg]$ cat /etc/hosts
192.168.100.50  server1
127.0.0.1       server1

（六）

未解决错误：（求解）

[oracle@server1 gg]$  ./replicat paramfile dirprm/inload.prm 
2016-05-28 20:38:11  ERROR   OGG-00303  Problem at line 37.  
Expecting file, table, or record definition: TimeZone: Asia/Shanghai.
2016-05-28 20:38:11  ERROR   OGG-01668  PROCESS ABENDING.

（七）

未解决错误：（求解）

GGSCI (hzc as ogg_msuser@AW2014_ODBC) 24> start msext

Sending START request to MANAGER ('GGSMGR') ...
EXTRACT MSEXT starting

#错误日志：D:\gg\dirrpt\MSEXT.rpt
2016-05-29 10:39:45  ERROR   OGG-01232  Receive TCP params error: TCP/IP error 10054 
	(远程主机强迫关闭了一个现有的连接。), endpoint: server1:7819.
2016-05-29 10:39:45  ERROR   OGG-01668  PROCESS ABENDING.