使用 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.