Enabling Oracle to Access Multiple SQL servers at the same time (1)

1. on a Windows machine with Oracle9i Standard Edition or Oracle9i EntERPrise Edition installed (IP: 192.168.0.2), You must select the Transparent Gateway (Oracle Transparent Gateway) to access the Microsoft SQL Server database.

The initpubs. ora and initnorthwind. ora configuration files are newly written under ORACLE9I_HOME \ tg4msql \ admin.

The content of initpubs. ora is as follows:

HS_FDS_CONNECT_INFO="SERVER=SQLSERVER_HOSTNMAE;DATABASE=pubs" HS_DB_NAME=pubsHS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

The content of initnorthwind. ora is as follows:

HS_FDS_CONNECT_INFO="SERVER=sqlserver_hostname;DATABASE=Northwind" HS_DB_NAME=Northwind HS_FDS_TRACE_LEVEL=OFF HS_FDS_RECOVERY_ACCOUNT=RECOVER HS_FDS_RECOVERY_PWD=RECOVER

The contents of listener. ora under $ ORACLE9I_HOME \ network \ admin are as follows:

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS_LIST =    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521)) ) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = test9) (ORACLE_HOME = d:\oracle\ora92) (SID_NAME = test9) ) (SID_DESC= (SID_NAME=pubs) (ORACLE_HOME=d:\Oracle\Ora92) (PROGRAM=tg4msql) ) (SID_DESC= (SID_NAME=northwind) (ORACLE_HOME=d:\Oracle\Ora92) (PROGRAM=tg4msql) ) )

Restart the TNSListener service on the gateway-enabled Windows machine (IP: 192.168.0.2) (when you add an accessible SQL Server database following this step, the TNSListener service must be restarted ).

2. Configure tnsnames. ora on the server of Oracle8i and Oracle9i and add the following content:

pubs = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521)) ) (CONNECT_DATA = (SID = pubs) ) (HS = pubs) ) northwind = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.2)(PORT = 1521)) ) (CONNECT_DATA = (SID = northwind) ) (HS = northwind)   )

After saving tnsnames. ora, run the following command:

tnsping pubs tnsping northwind

A similar prompt is displayed, that is, success:

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521) (CONNECT_DATA = (SID = pubs) (HS = pubs )) OK20 ms) Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.2) (PORT = 1521 ))) (CONNECT_DATA = (SID = northwind) (HS = northwind) OK20 milliseconds)

Set the database parameter global_names to false.

Setting global_names = false does not require that the created database link be the same as the global name of the target database. Global_names = true is required, which is inconvenient.