Oracle Gateway for SQL Server時2PC分散式事務異常處理

guocun09發表於2020-06-29
OracleGatewaySQLServer分散式

異常:

使用者一套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

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:

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:

  1. Create a Recovery Account and Password

  2. 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.

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

  1. 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.

  2. 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".

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:

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;


至此，問題解決。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25583515/viewspace-2700445/，如需轉載，請註明出處，否則將追究法律責任。

相關文章