Oracle remote access configuration

Server configuration

If you don't want to write by yourself, you can configure it through Net Manager

The localhost in the following configuration file is changed to ip address, otherwise, it cannot be accessed remotely

1. Network monitoring configuration

# listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\Administrator

When there are multiple IP addresses, they are as follows:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.226.2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.226.3)(PORT = 1521))
    )
  )

2.TNS configuration

# tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

When there are multiple IP S, they are as follows:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.226.2)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.226.3)(PORT = 1521))
    )
    (SOURCE_ROUTE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

3. Stop and start services

Stop the following services before starting

  • OracleOraDb11g_homeTNSListener
  • OracleService*

Client configuration

1. Download the ODAC compression package

2. Decompress the downloaded ODAC compressed package

3. Execute the installation command
Example:

install.bat all D:\Program\ODAC\112040x64 OracleHome true

4. Add environment variable

  • Oracle? Home: is the directory where the ODAC is installed
  • TNS admin: the directory where the file tnsnames.ora is located. It is usually in network\admin under the directory of ODAC installation

Note: TNS admin can be directly copied to the directory of ODAC installation without adding it

Add% Oracle & Home% to the PATH environment variable

5. configure TNS

Add to the configuration file tnsnames.ora

DBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <db-host>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <db-name>)
    )
  )

Explain:

  • < DB host >: IP or computer name of the database computer
  • < db name >: name of database TNS configuration

Tags: C# network Oracle Database

Posted on Thu, 26 Mar 2020 12:03:38 -0400 by Tory