. NET programs connect to the Oracle database remotely

The. NET remote connection Oracle database looks pretty simple, but there are still a lot of problems. Let me share the experience of tossing this thing today.

There are many ways to access Oracle database, I use OleDbConnection, this is nothing to say, the code is as follows:

1. Connection Code

_OleDbConnection　=　new　OleDbConnection(connectionString);
_OleDbConnection.Open();

2. Connection string

The connection string for a remote connection can be written like this

Provider=msdaora;host=192.168.1.1;data Source=mytest;user id=system;password=xxx

Provider = MSDAORA; Indicates that the provider of OLE DB is an Oracle database

Host is the IP address of the server where the remote Oracle database resides, plus a port=xxxx if the port is not the default 1521;

Data source is the name of the Oracle database to access

The user ID and password are not mentioned.

To be able to successfully run the above code, we must install the Oracle client on the machine where the code is running, otherwise we will report

The Oracle (tm) client and networking components were not found this error.

3. Host firewall settings

My Oracle is installed on Windows 2003 server, and for security reasons, you must set up a firewall

I opened the 1521 port under Windows 2003 and found that I could not connect to the database, grabbed a look at it, and the Oracle client connected to a random port 1197 after connecting 1521.

After checking the data, Oracle's network listener only acts as a mediator, and when the customer connects it, it looks for the corresponding database instance process according to the configuration and assigns a new database connection, which is passed to the client by the network listener, The client then ceased to deal with network listener, but with the oracle.exe process. This new connection port is unpredictable and will be blocked by the firewall.

To solve this problem, the online approach is to use a shared socket, this method should be a more general approach, how to do this detailed

Open Oracle Service port connection on Windows Server 2003 System firewall 1521 TNS timeout