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

This article introduces the relevant knowledge of "how to install the ODBC driver of Oracle database under Ubuntu16.04". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

There are many ways to connect remote Oracle database under Ubuntu 16.04. this paper introduces how to realize client remote access to Oracle database by ODBC.

1. To connect to a remote database through ODBC, the client needs an ODBC management program. Under the Linux system, you can check the environment and configuration of ODBC with the following command:

(1) check whether the ODBC manager is installed on the system:

Csyy@server02:~$ dpkg-l | grep odbc

Desired=Unknown/Install/Remove/Purge/Hold

| | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend |

| | / Err?= (none) / Reinst-required (Status,Err: uppercase=bad) |

| | / Name Version Architecture Description |

+ +-=

Ii libodbc1:amd64 2.3.7 amd64 ODBC library for Unix

Ii msodbcsql17 17.4.2.1-1 amd64 ODBC Driver for Microsoft (R) SQL Server (R)

Ii odbcinst 2.3.7 amd64 Helper program for accessing odbc ini files

Ii odbcinst1debian2:amd64 2.3.7 amd64 Support library for accessing odbc ini files

Ii unixodbc 2.3.7 amd64 Basic ODBC tools

Ii unixodbc-dev 2.3.7 amd64 ODBC libraries for UNIX (development files)

If the ODBC hypervisor is not found, install it with the following command:

Sudo apt-get install unixodbc

(2) check the ODBC environment configuration:

Csyy@server02:~$ odbcinst

*

* unixODBC-odbcinst *

*

* Purpose: *

* An ODBC Installer and Uninstaller. *

* Updates system files, and increases/decreases usage counts but *

* does not actually copy or remove any files. *

* Syntax: *

* odbcinst Action Object Options *

* Action: *

*-I install *

*-u uninstall *

*-Q query *

*-j print config info *

*-c call SQLCreateDataSource *

*-m call SQLManageDataSources *

*-- version version *

* Object: *

*-d driver *

*-s data source *

* Options: *

*-f file name of template.ini follows this (valid for-I) *

*-r get template.ini from stdin, not a template file *

*-n Driver or Data Source Name follows *

*-v turn verbose off (no info, warning or error msgs) *

*-l system dsn *

*-h user dsn *

* Returns: *

* 0 Success *

*! 0 Failed *

* Please visit; *

* http://www.unixodbc.org *

* pharvey@codebydesign.com *

*

Csyy@server02:~$ odbcinst-j

UnixODBC 2.3.7

DRIVERS.: / etc/odbcinst.ini

SYSTEM DATA SOURCES: / etc/odbc.ini

FILE DATA SOURCES..: / etc/ODBCDataSources

USER DATA SOURCES..: / home/csyy/.odbc.ini

SQLULEN Size.: 8

SQLLEN Size.: 8

SQLSETPOSIROW Size.: 8

In terms of the configuration environment, the configuration file for the ODBC driver is / etc/odbcinst.ini, the configuration file for the system DSN is / etc/odbc.ini, the configuration file for the file DSN is / etc/ODBCDataSources, and the configuration file for the user DSN is $HOME/.odbc.ini.

Download and install the oracle ODBC driver:

Go to the official website https://www.oracle.com/database/technologies/instant-client/ linux-x86-64-downloads.html to download the required installation packages below.

Oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm

Carry out installation

Sudo alien-I oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm

The rpm package is used here to install the Oracle for ODBC driver, or it can be installed through the zip package.

The installation directory for the Oracle for ODBC driver is / usr/share/oracle/11.2/client64.

3. For the installation of Oracle client, please refer to:

Http://blog.itpub.net/81227/viewspace-2668066/

4. Configure the ODBC of Oracle 11gR2:

Root@server02:~# cd / usr/share/oracle/11.2/client64

Root@server02:/usr/share/oracle/11.2/client64#. / odbc_update_ini.sh /

Odbc_update_ini.sh parameter description:

Parameter 1:unixODBC installation path.

Parameter 2: the absolute path of the driver installation (optional). The default is the path where the script runs.

Parameter 3: driver name (optional), default is Oracle 11g ODBC driver.

Parameter 4: data source name (optional).

When the run is complete, driver configuration information about Oracle for ODBC is added to the / etc/odbcinst.ini file named Oracle 11g ODBC driver;, as well as an DSN entry to $HOME/.odbc.ini and / etc/odbc.ini named OracleODBC-11g.

Root@server02:/usr/share/oracle/11.2/client64# cat / etc/odbcinst.ini

[Oracle 11g ODBC driver]

Description = Oracle ODBC driver for Oracle 11g

# Driver = / usr/share/oracle/11.2/client64/libsqora.so.11.1

Driver = / usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

Setup =

FileUsage =

CPTimeout =

CPReuse =

The driver's dynamic library is misconfigured, not in the / usr/share directory, but in the / usr/lib directory.

Root@server02:/usr/share/oracle/11.2/client64# cat / etc/odbc.ini

[OracleODBC-11g]

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

BindAsFLOAT = F

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = Oracle 11g ODBC driver # corresponds to the driver configuration name in / etc/odbcinst.ini

DSN = OracleDSN

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

FetchBufferSize = 64000

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = OracleDATA # corresponds to the local service name configured in tnsnames.ora

SQLGetData extensions = F

Translation DLL =

Translation ption = 0

DisableRULEHint = T

UserID =

After you configure the ODBC configuration file, you can connect to the Oracle database through isql.

Command: isql [- v]

Here are some problems encountered in the actual installation process.

Question 1: about the path of the driver libsqora.so.11.1.

After the default configuration is installed, the files in the configuration file are in the / usr/share/oracle/11.2/client64 path, but this file is not actually found in this directory.

Driver = / usr/share/oracle/11.2/client64/libsqora.so.11.1

Look for this file through the find command and find it in the / usr/lib/oracle/11.2/client64/lib directory, so modify the configuration as follows.

Driver = / usr/lib/oracle/11.2/client64/lib/libsqora.so.11.1

Question 2: execute the isql command for the first time and report "ORA-12162" and "ORA-12545" errors.

Root@server02:~# isql OracleODBC-11g OracleUser OraclePassword-v

[S1000] [unixODBC] [Oracle] [ODBC] [Ora] ORA-12162: TNS:net service name is incorrectly specified

[ISQL] ERROR: Could not SQLConnect

Check the Oracle documentation and interpret it as an incorrect Oracle connection string.

ORA-12162: TNS:net service name is incorrectly specified

Cause: The connect descriptor corresponding to the net service name in TNSNAMES.ORA or in the directory server (Oracle Internet Directory) is incorrectly specified.

Action: If using local naming make sure there are no syntax errors in the corresponding connect descriptor in the TNSNAMES.ORA file. If using directory naming check the information provided through the administration used for directory naming.

ORA-12162: incorrect TNS:net service name specified

Reason: the connection descriptor corresponding to the network service name in the TNSNAMES.ORA or Oracle directory server is specified incorrectly.

Action: if you use local naming, make sure that there are no syntax errors in the corresponding connection descriptor in the TNSNAMES.ORA file. If you use directory naming, check the information provided through management for directory naming.

Check the Oracle TNS configuration carefully and no configuration errors are found.

Search the Internet again, it is said that "the reason behind the weird fault is unexpectedly such a basis: ORACLE_SID is not specified!" .

Confirm the current ORACLE_HOME and ORACLE_SID environment variables for the system:

Root@server02:~# echo $ORACLE_HOME

/ usr/lib/oracle/11.2/client64

Root@server02:~# echo $ORACLE_SID

It can be seen that only the ORACLE_HOME environment variable is set at this time, but ORACLE_SID is empty at this time, is this the real cause of the problem?

Generally speaking, the Oracle client does not need to set the ORACLE_SID environment variable.

Let's try it first.

Root@server02:~# export ORACLE_SID=orcl

Root@server02:~# isql OracleODBC-11g OracleUser OraclePassword-v

[S1000] [unixODBC] [Oracle] [ODBC] [Ora] ORA-12545: Connect failed because target host or object does not exist

[ISQL] ERROR: Could not SQLConnect

As soon as a "ORA-12162" problem is solved, there is another "ORA-12545" error.

ORA-12545: Connect failed because target host or object does not exist

Cause: The address specified is not valid, or the program being connected to does not exist.

Action: Ensure the ADDRESS parameters have been entered correctly; the most likely incorrect parameter is the node name. Ensure that the executable for the server exists (perhaps "oracle" is missing.) If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the host name to a numeric IP address and try again.

ORA-12545: connection failed because the target host or object does not exist

Reason: the specified address is invalid or the connected program does not exist.

Action: make sure that the address parameters are entered correctly; the most likely incorrect parameter is the node name. Make sure the executable file for the server exists (possibly "Oracle" is missing). If the protocol is TCP/IP, edit the TNSNAMES.ORA file, change the hostname to a numeric IP address, and try again.

The problem goes back to the connection string again, indicating that the cause of the problem is that the connection string is not specified or the target host or object in the connection string does not exist.

Back to the configuration of ODBC, the root cause of the problem is that after the system is installed, there will be multiple ODBC DSN configuration files: / etc/odbc.ini (global configuration) and ~ / .odbc.ini (user configuration), and the DataSource of the same name causes configuration confusion. When modifying the configuration of the ODBC data source, the configuration parameters are modified directly in the global configuration file, and the original DSN name is referenced, resulting in the configuration conflict between the global configuration file (configuration is correct) and the user configuration file (configuration error, no ServiceName parameter specified), and the priority is that the user configuration is higher than the global configuration, which leads to the connection failure of ODBC.

The problem is solved after correcting the configuration parameters.

This is the end of the introduction of "how to install the ODBC driver of Oracle database under Ubuntu16.04". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.