Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the method of 2PC distributed transaction exception handling when Oracle Gateway for SQL Server". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the method of 2PC distributed transaction exception handling when Oracle Gateway for SQL Server"?

Exception:

If a user reports an error in a set of Oracle database alert.log, he will not find the reason and contact me to assist in the analysis:

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 last NativeErr = 18456}

ORA-02063: preceding 2 lines from WHSFC

Analysis:

The ORA-02063 error reported in Oracle is a dblink-related problem, and the failure of SQL server access should be the error of using Oracle gateway to access heterogeneous databases. Logging in to the Oracle database establishment is indeed like this:

$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

Let's get this straight:

RMA system DB (192.168.0.135): Oracle 11.2.0.3 accesses the DB of the SFC system SQL Server through gateway DBlink access

SFC system DB (192.168.0.100): SQL Server 2008

The error [SQL Server] Login failed for user 'RECOVER'. is reported here. {28000 century NativeErr = 18456} because it is a process of self-recovery when there is a 2PC problem in a distributed system.

Query whether the 2PC transaction exception exists, and sure enough:

SQL > select * from dba_2pc_pending

Note that 2PC distributed transaction lock exceptions can be manually forced to unlock in COMMIT/ROLLBACK FORCE 'transaction_id'; mode (for example, commit force' 6.23.17365locks; or rollback force '6.23.17365locks;)

However, in order to further understand the principle of alert.log error reporting, check the official documentation for more details:

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

I know from the above official documents.

Because when recovering 2PC problems, you need to set up a RECOVER account on the SQL SERVER side and visit trascation log table (HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG in the gateway configuration file) to solve the 2PC problem.

Check that SQL Server DB did not set up a RECOVER account, so an error was reported: [SQL Server] Login failed for user 'RECOVER'. {28000 last NativeErr = 18456}

And follow the instructions in the document to establish a trascation log table in SQL Server DB, and execute the contents in dg4msql_tx.sql (because the SQLSERVER configured by gateway is WHSFC DB, here, the HS_TRANSACTION_LOG table is also established under WHSFC DB):

Use WHSFCCREATE TABLE HS_TRANSACTION_LOG (GLOBAL_TRAN_ID char (64) NOT NULL, TRAN_COMMENT char (255) NULL) gogrant all on HS_TRANSACTION_LOG to publicgo

It's all done in accordance with the official configuration. But it wasn't long before alert.log reported it wrong again:

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

Notice that this time, it is different from the previous error report, it is Invalid object name 'RECOVER.HS_TRANSACTION_LOG'.. {42S02 ~ NativeErr = 208}

But we have already established the HS_TRANSACTION_LOG in accordance with the requirements of the document in SQLSERVER. Why still save it?

Notice that here we mean RECOVER.HS_TRANSACTION_LOG, the RECOVER before the table name. Is it dbname or schemaname in SQL Server? It seems to be a name that comes with gateway by default, but neither DBname nor schema exists for SQL Server. So there will be an error Invalid object name, further analysis because gateway is configured with SQLServer DBname:WHSFC, so here RECOVER. It should be schemaname, but the default schema in SQLServer2008 is dbo, so you can't find this table.

Create a DBlink using your RECOVER account and query table to verify:

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-- reported the same error: [Oracle] [ODBC SQL Server Wire Protocol driver] [Microsoft SQL Server] Invalid object name 'RECOVER.HS_TRANSACTION_LOG'. {42S02 ~ NativeErr = 208}

Resolve:

Create schema:RECOVER under the WHSFC DB to be accessed by SQLServer, and create the HS_TRANSACTION_LOG table under RECOVER schema:

USE [WHSFC] GOCREATE SCHEMA [RECOVER] AUTHORIZATION [RECOVER] GOCREATE TABLE RECOVER.HS_TRANSACTION_LOG (GLOBAL_TRAN_ID char (64) NOT NULL, TRAN_COMMENT char (255) NULL) gogrant all on RECOVER.HS_TRANSACTION_LOG to publicgo

After a while, Oracle automatically cleans up the distributed transaction through the RECO process and records it in 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)

The query dba_2pc_pending also has no record:

SQL > select * from dba_2pc_pending

Thank you for reading, the above is the content of "what is the method of exception handling of 2PC distributed transactions when Oracle Gateway for SQL Server". After the study of this article, I believe you have a deeper understanding of what is the method of exception handling of 2PC distributed transactions when Oracle Gateway for SQL Server, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.