这篇文章主要讲解了“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 stat
LSNRCTL for Linux: Version 11.2.0.3.0
Copyright (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的DB
SFC系统DB(192.168.0.100)：SQL Server 2008
这里报错[SQL Server]Login failed for user 'RECOVER'. {28000,NativeErr = 18456}因为是分布式系统中出现2PC问题时，进行自我恢复的一个过程。
查询2PC事务异常是否存在,果然：
SQL> select * from dba_2pc_pending;
注意，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#OTGIS1027
The gateway supports the following transaction capabilities:
COMMIT_CONFIRM
READ_ONLY
SINGLE_SITE
The 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 Password
Create the Transaction Log Table
The 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:
$ isql -Urecovery_account -Precovery_account_password [-Sserver] -idg4msql_tx.sql
通过以上官方文档得知，
因为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下)：
use WHSFC CREATE TABLE HS_TRANSACTION_LOG ( GLOBAL_TRAN_ID char (64) NOT NULL, TRAN_COMMENT char (255) NULL) go grant all on HS_TRANSACTION_LOG to public go
按照官方的配置全部完成。但是没过多久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验证一下：
CREATE PUBLIC DATABASE LINK WHSFCRECO CONNECT TO RECOVER IDENTIFIED BY <PWD> USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.135)(PORT = 1521)) ) (CONNECT_DATA = (SID = dg4msql) ) (HS = OK) )';
SELECT * FROM RECOVER.HS_TRANSACTION_LOG@WHSFCRECO
--果然报错一样：
[Oracle][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Invalid object name 'RECOVER.HS_TRANSACTION_LOG'. {42S02,NativeErr = 208}解决：
在SQLServer要访问的WHSFC DB下建立schema:RECOVER，并把HS_TRANSACTION_LOG表建立到RECOVER schema下：
USE [WHSFC] GO CREATE SCHEMA [RECOVER] AUTHORIZATION [RECOVER] GO CREATE TABLE RECOVER.HS_TRANSACTION_LOG( GLOBAL_TRAN_ID char (64) NOT NULL, TRAN_COMMENT char (255) NULL) go grant all on RECOVER.HS_TRANSACTION_LOG to public go
一段时间后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 * from dba_2pc_pending;
感谢各位的阅读，以上就是“Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么”的内容了，经过本文的学习后，相信大家对Oracle Gateway for SQL Server时2PC分布式事务异常处理的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
