oracle如何检测处理异常事物,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

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:

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.

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.

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

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 

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;

至此，问题解决。