Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么

这篇文章主要讲解了“Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么”吧！异常:用户一套Oracle数据库alert.log报错，一时半会找不到原因联系到我这边协助分析：
Tue Jun 23 07:43:53 2020
Errors in file /u01/app/oracle/diag/rdbms/whrma/whrma/trace/whrma_reco_7075.trc:
ORA-01017: invalid username/password; logon denied
[Oracle][ODBC SQL Server Wire Protocol driver][SQL Server]Login failed for user ‘RECOVER’. {28000,NativeErr = 18456}
ORA-02063: preceding 2 lines from WHSFC分析：Oracle中有报ORA-02063错误是dblink相关问题，而SQL server访问失败应该是使用到了Oracle gateway访问异构数据库的错误。登录Oracle数据库建立确实是这样：$
lsnrctl statLSNRCTL for Linux: Version 11.2.0.3.0Copyright (c) 1991, 2011, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.135)(PORT=1521)))
STATUS of the LISTENER
————————
Alias listener
Version TNSLSNR for Linux: Version 11.2.0.3.0 – Production
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.135)(PORT=1521)))
Services Summary…
Service “dg4msql” has 1 instance(s).
Instance “dg4msql”, status UNKNOWN, has 1 handler(s) for this service…
Service “hmdrma” has 2 instance(s).
Instance “hmdrma”, status UNKNOWN, has 1 handler(s) for this service…
Instance “hmdrma”, status READY, has 1 handler(s) for this service…
The command completed successfully

$
cd $ORACLE_HOME/dg4msql/admin/
$ ll
total 28
-rw-r–r– 1 oracle oinstall 10722 May 14 2009 dg4msql_cvw.sql
-rw-r–r– 1 oracle oinstall 746 Jun 8 2007 dg4msql_tx.sql
-rw-r–r– 1 oracle oinstall 369 Feb 4 2015 initdg4msql.ora
-rw-r–r– 1 oracle oinstall 411 Feb 4 2015 listener.ora.sample
-rw-r–r– 1 oracle oinstall 244 Feb 4 2015 tnsnames.ora.sample$
more initdg4msql.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[192.168.0.100]:3000//WHSFC
# alternate connect format is hostname/serverinstance/databasename
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER理清一下：RMA系统DB(192.168.0.135)：Oracle 11.2.0.3 通过gateway DBlink访问会访问SFC系统SQL Server的DBSFC系统DB(192.168.0.100)：SQL Server 2008
这里报错[SQL Server]Login failed for user ‘RECOVER’. {28000,NativeErr = 18456}因为是分布式系统中出现2PC问题时，进行自我恢复的一个过程。查询2PC事务异常是否存在,果然：注意，2PC分布式事务锁异常可以直接通过手动方式强制COMMIT/ROLLBACK FORCE ‘transaction_id’;方式解锁 (例如：commit force ‘6.23.17365’; 或者rollback force ‘6.23.17365’; )但为了进一步搞清楚alert.log报错原理，查看官方文档得到更详细说明：https://docs.oracle.com/cd/E11882_01/gateways.112/e12013/configsql.htm#OTGIS1027The gateway supports the following transaction capabilities:COMMIT_CONFIRMREAD_ONLYSINGLE_SITEThe transaction model is set using the
HS_TRANSACTION_MODEL initialization parameter.

By default, the gateway runs in
COMMIT_CONFIRM transaction mode. When the SQL Server database is updated by a transaction, the gateway becomes the commit point site. The Oracle database commits the unit of work in the SQL Server database after verifying that all Oracle databases in the transaction have successfully prepared the transaction. Only one gateway instance can participate in an Oracle two-phase commit transaction as the commit point site.See Also:Oracle Database Heterogeneous Connectivity User’s Guide for information about the two-phase commit process.To enable the
COMMIT_CONFIRM transaction mode, perform the following tasks:Create a Recovery Account and PasswordCreate the Transaction Log TableThe log table, called
HS_TRANSACTION_LOG, is where two-phase commit transactions are recorded. Alternatively users can specify a different table name by setting a gateway initialization parameter
HS_FDS_TRANSACTION_LOG parameter. This table needs to be in the same schema as the recovery account.For the gateway to recover distributed transactions, a
recovery account and password must be set up in the SQL Server database. By default, both the
user name of the account and the password are
RECOVER. The name of the account can be changed with the gateway initialization parameter
HS_FDS_RECOVERY_ACCOUNT. The account password can be changed with the gateway initialization parameter
HS_FDS_RECOVERY_PWD.Note:Oracle recommends that you do not use the default value
RECOVER for the user name and password. Moreover, storing plain-text as user name and password in the initialization file is not a good security policy. There is now a utility called
dg4pwd that should be used for encryption. Refer to
Section 4.2.3, ‘Encrypting Initialization parameters’ in the
Oracle Database Heterogeneous Connectivity User’s Guide for further details.Set up a user account in the SQL Server database. Both the user name and password must be a valid SQL Server user name and password.In the initialization parameter file, set the following gateway initialization parameters:HS_FDS_RECOVERY_ACCOUNT to the user name of the SQL Server user account you set up for recovery.HS_FDS_RECOVERY_PWD to the password of the SQL Server user account you set up for recovery.See Also:”Customize the Initialization Parameter File” for information about editing the initialization parameter file. For information about
HS_FDS_RECOVERY_ACCOUNT and
HS_FDS_RECOVERY_PWD, see
Appendix C, “Initialization Parameters”.When configuring the gateway for two-phase commit, a
table must be created in the SQL Server database for logging transactions. The gateway uses the transaction log table to check the status of failed transactions that were started at the SQL Server database by the gateway and registered in the table.Note:Updates to the transaction log table cannot be part of an Oracle distributed transaction.Note:The information in the transaction log table is required by the recovery process and must not be altered. The table must be used, accessed, or updated only by the gateway.The table, called
HS_TRANSACTION_LOG, consists of two columns,
GLOBAL_TRAN_ID, data type
CHAR(64) NOT NULL and
TRAN_COMMENT, data type
CHAR(255).You can use another name for the log table, other than
HS_TRANSACTION_LOG, by specifying the other name using the
HS_FDS_TRANSACTION_LOG initialization parameter.See Also:Appendix C, “Initialization Parameters” for information about the
HS_FDS_TRANSACTION_LOG initialization parameter.Create the transaction log table in the user account you created in
“Create a Recovery Account and Password”. Because the transaction log table is used to record the status of a gateway transaction, the table must reside at the database where the SQL Server update takes place. Also, the transaction log table must be created under the owner of the recovery account.Note:To utilize the transaction log table, users of the gateway must be granted privileges on the table.To create a transaction log table use the
dg4msql_tx.sql script, located in the directory
$ORACLE_HOME/dg4msql/admin where
$ORACLE_HOME is the directory under which the gateway is installed. Use isql to execute the script, as follows:通过以上官方文档得知，因为2PC问题恢复时需要用过在SQL SERVER端建立RECOVER账号，访问trascation log table(gateway配置文件中的HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG) ，解决2PC问题。
检查SQL Server DB没有建立RECOVER账号所以报错：[SQL Server]Login failed for user ‘RECOVER’. {28000,NativeErr = 18456}并按照文档中说明在SQL Server DB中建立trascation log table，执行dg4msql_tx.sql中内容(因为gateway配置的SQLSERVER是WHSFC DB，这里也把HS_TRANSACTION_LOG表建立到WHSFC DB下)：
按照官方的配置全部完成。但是没过多久alert.log又有报错了：Tue Jun 23 23:31:55 2020
Errors in file /u01/app/oracle/diag/rdbms/whrma/whrma/trace/whrma_reco_7075.trc:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name ‘RECOVER.HS_TRANSACTION_LOG’. {42S02,NativeErr = 208}
ORA-02063: preceding 2 lines from WHSFC
Errors in file /u01/app/oracle/diag/rdbms/whrma/whrma/trace/whrma_reco_7075.trc:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name ‘RECOVER.HS_TRANSACTION_LOG’. {42S02,NativeErr = 208}
ORA-02063: preceding 2 lines from WHSFC注意到这次，和之前的报错不一样了，是报Invalid object name ‘RECOVER.HS_TRANSACTION_LOG’. {42S02,NativeErr = 208}但我们已经建立了按照文档在SQLSERVER中要求建立了HS_TRANSACTION_LOG为什么还是保存呢？注意到这里是说RECOVER.HS_TRANSACTION_LOG, 表名前的RECOVER.是SQL Server中的dbname还是 schemaname？看样子是gateway默认自带的一个名字，但是SQL Server无论是DBname还是schema都不存在的。所以会报错Invalid object name了，进一步分析因为gateway中配置了SQLServer DBname:WHSFC，所以这里RECOVER.应该是schemaname,但是像SQLServer2008中默认的schema是dbo，所以就找不到这个table了；
使用RECOVER账号建立一个DBlink，查询table验证一下：解决：在SQLServer要访问的WHSFC DB下建立免费主机域名schema:RECOVER，并把HS_TRANSACTION_LOG表建立到RECOVER schema下：
一段时间后Oracle通过RECO进程自动清理了分布式事务并在alert.log中记录：Thu Jun 25 10:54:55 2020
DISTRIB TRAN WHRMA.6f4758bb.6.23.173265
is local tran 6.23.173265 (hex=06.17.2a4d1))
delete pending forced rollback tran, scn=12519027806551 (hex=b62.d0349157)
DISTRIB TRAN
WHRMA.6f4758bb.2.24.250810
is local tran 2.24.250810 (hex=02.18.3d3ba))
delete pending forced rollback tran, scn=12519027892235 (hex=b62.d035e00b)
DISTRIB TRAN
WHRMA.6f4758bb.4.2.253750
is local tran 4.2.253750 (hex=04.02.3df36))
delete pending forced rollback tran, scn=12519534153370 (hex=b62.ee62ce9a)
DISTRIB TRAN
WHRMA.6f4758bb.8.22.258215
is local tran 8.22.258215 (hex=08.16.3f0a7))
delete pending forced rollback tran, scn=12519534165133 (hex=b62.ee62fc8d)
Thu Jun 25 10:58:39 2020
DISTRIB TRAN
WHRMA.6f4758bb.5.15.251070
is local tran 5.15.251070 (hex=05.0f.3d4be))
delete pending forced rollback tran, scn=12519570537961 (hex=b62.f08dfde9)
Thu Jun 25 11:02:51 2020
DISTRIB TRAN
WHRMA.6f4758bb.11.11.171034
is local tran 11.11.171034 (hex=0b.0b.29c1a))
delete pending forced rollback tran, scn=12519570552088 (hex=b62.f08e3518)
DISTRIB TRAN
WHRMA.6f4758bb.10.21.251539
is local tran 10.21.251539 (hex=0a.15.3d693))
delete pending forced rollback tran, scn=12519570598077 (hex=b62.f08ee8bd)查询dba_2pc_pending也没有记录了：SQL>select*fromdba_2pc_pending;感谢各位的阅读，以上就是“Oracle Gateway for SQL Server时2PC分布式事务异常处理的免费主机域名方法是什么”的内容了，经过本文的学习后，相信大家对Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是云技术，小编将为大家推送更多相关知识点的文章，欢迎关注！

相关推荐: 如何理解ERROR中的Unknown storage engine ‘MyISAM’

如何理解ERROR中的Unknown storage engine ‘MyISAM’，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。如下：enforce_storage_engine：建表进行引擎检…