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資料庫建立確實是這樣：
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
# 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報錯原理 ，檢視官方文件得到更詳細說明：
9.5 Configure Two-Phase Commit
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:
for information about the two-phase commit process.
To enable the
COMMIT_CONFIRM transaction mode, perform the following tasks:
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.
9.5.1 Create a Recovery Account and Password
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 valueRECOVER 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
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_ACCOUNTto the user name of the SQL Server user account you set up for recovery. -
HS_FDS_RECOVERY_PWDto the password of the SQL Server user account you set up for recovery.See Also:
for information about editing the initialization parameter file. For information aboutHS_FDS_RECOVERY_ACCOUNTandHS_FDS_RECOVERY_PWD, see .
-
9.5.2 Create the Transaction Log Table
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:
for information about theHS_FDS_TRANSACTION_LOG initialization parameter.Create the transaction log table in the user account you created in . 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;
至此，問題解決。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25583515/viewspace-2700445/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Server分散式事務處理(MS DTC)SQLServer分散式
- 一次ORACLE分散式事務鎖異常處理分析Oracle分散式
- SQL Server分散式事務處理(MS DTC)-續SQLServer分散式
- Oracle分散式事務典型案例處理Oracle分散式
- SQL Server 異常程式碼處理SQLServer
- SQL SERVER 事務處理（一）SQLServer
- sql server 事務處理（二）SQLServer
- 分散式事務處理方案，微服事務處理方案分散式
- Laravel 分散式事務處理Laravel分散式
- 分散式事務故障處理分散式
- 分散式事務(1)---2PC和3PC理論分散式
- ORACLE懸疑分散式事務問題處理Oracle分散式
- 分散式事務-2PC和3PC分散式
- SQL Server的怪闢：異常與孤立事務SQLServer
- 老生常談——利用訊息佇列處理分散式事務佇列分散式
- springcloud分散式事務處理 LCNSpringGCCloud分散式
- 在 SQL Server 中使用 Try Catch 處理異常SQLServer
- dx幫忙::跨異構平臺的分散式事務處理.分散式
- PHP分散式事務-兩段式提交 2PC(二)PHP分散式
- SQL Server事務日誌的處理方法SQLServer
- SQL SERVER 的分散式事務診斷工具DTCTesterSQLServer分散式
- oracle異常處理Oracle
- Oracle 處理異常Oracle
- PL SQL異常處理.SQL
- 阿里是如何處理分散式事務的阿里分散式
- 異常處理與異常函式函式
- 揭祕GBase 8c分散式事務處理核心技術之2PC協議分散式協議
- Spring分散式事務XA事務(兩段提交2PC)實現Spring分散式
- SQL Server事務日誌過大的處理SQLServer
- oracle分散式事務Oracle分散式
- .net 2.0 訪問Oracle －－與Sql Server的差異，注意事項，常見異常OracleSQLServer
- Spring Cloud Gateway-自定義異常處理SpringCloudGateway
- Spring Cloud Gateway的全域性異常處理SpringCloudGateway
- oracle 異常處理 exceptionOracleException
- 【轉】Oracle 異常處理Oracle
- SpringCloud Alibaba Seata處理分散式事務SpringGCCloud分散式
- 分散式系列七: 分散式事務理論分散式
- SQL Server Insert時開啟顯式事務SQLServer