Oracle Gateways setup for MS SQL using Microsoft drivers for Linux

This post outlines the procedure involved in setting up a database link between Oracle 11gR2 and MS SQL Server using Oracle's Database Gateways for ODBC (DG4ODBC) using the freeware odbc drivers available from Microsoft.

The software components involved are:

·         unixODBC Driver Manager 2.3.0 (http://www.unixodbc.org)

·         Microsoft SQL Server ODBC Driver 1.0 for Linux

·         Oracle 11gR2 DG4ODBC (shipped with Oracle Server)

·         MS SQL Server 2005 (or later versions 2008, 2012)

1.     Install the unixODBC driver manager and ODBC driver as per the instructions below as 'root' user.

http://www.microsoft.com/en-us/download/details.aspx?id=28160#instructions

2.     Setup /etc/odbc.ini as below (as root user)

[mssql]

Description=Microsoft SQL Server ODBC Driver V1.0 for Linux

Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

Threading=1

UsageCount=1

AutoTranslate=No

#Had to use the above AutoTranslate setting to avoid a single quote in MS SQL records being #allocated an extra byte at Oracle end.

Database=MSSQL_Database_Name

UserName=MSSQL_DB_User

Password=xxxxxx

Port=1433

Server=MSSQLServer

Trace=Yes

TraceFile=/u00/odbctrace.log

3.     Setup Oracle listener.ora ($ORACLE_HOME/network/admin/listener.ora) as below

*************************
# listener.ora Network Configuration File: xxxx/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=Oracle_Home_Path)
(ENV="LD_LIBRARY_PATH=/usr/lib64:Oracle_Home_Path/lib")
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux_Host)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u00/app/oracle
*************************

4.     Add the below entry to your tnsnames.ora (Oracle_Home_Path/network/admin/tnsnames.ora) as below

# tnsnames.ora Network Configuration File: Oracle_Home_Path/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG4ODBC =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=Linux_Host)(PORT=1521))

(CONNECT_DATA=(SID=DG4ODBC))

(HS=OK)

)

5.     Setup HS Gateways as below (Oracle_Home_Path/hs/admin/initDG4ODBC.ora)

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = mssql
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

6.     Setup a database link as below

CREATE PUBLIC DATABASE LINK linkname
CONNECT TO "MSSQL_DB_User" IDENTIFIED BY "xxx" USING 'DG4ODBC';

7.     Add the below env variables to the .profile or .bash_profile of Oracle user

export LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:Oracle_Home_Path/lib
export ODBCINI=/etc/odbc.ini
export NLS_LANG=Language_Territory.Characterset

DG4ODBC setup on Oracle 11g Linux to access Microsoft SQL Server database tables

Had a requirement recently to configure HS gateways connectivity between an Oracle 11g on Linux 64bit and MS SQL 2005 database and here's the simple configuration that worked for me. 

As always, it's better to start testing this type of configuration with basic settings first and then move onto other more complex stuff later.

·                    Install the third party ODBC drivers on the Linux Oracle 11g Server, in my case, I've    used datadirect ODBC drivers CONNECT64 FOR ODBC RELEASE 7.1.0 ON UNIX/LINUX

Installation is self-explanatory as you uncompress the tar file and run the script.

·                    Setup the odbc.ini file with the below basic settings

[ODBC Data Sources]

DB2 Wire Protocol=DataDirect 7.1 DB2 Wire Protocol

Informix Wire Protocol=DataDirect 7.1 Informix Wire Protocol

Oracle Wire Protocol=DataDirect 7.1 Oracle Wire Protocol

Sybase Wire Protocol=DataDirect 7.1 Sybase Wire Protocol

Teradata=DataDirect 7.1 Teradata

SQL Server Wire Protocol=DataDirect 7.1 SQL Server Wire Protocol

MySQL Wire Protocol=DataDirect 7.1 MySQL Wire Protocol

PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol

Greenplum Wire Protocol=DataDirect 7.1 Greenplum Wire Protocol

Salesforce=DataDirect 7.1 Salesforce

Sybase IQ Wire Protocol=DataDirect 7.1 Sybase IQ Wire Protocol

Progress OpenEdge Wire Protocol=DataDirect 7.1 Progress OpenEdge Wire Protocol

Apache Hive Wire Protocol=DataDirect 7.1 Apache Hive Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_71

Trace=1

TraceFile=/tmp/odbctrace.out

TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

[mssql]

Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

AnsiNPW=1

Database=sqldatabase

EnableQuotedIdentifiers=1

HostName=Linux Hostname

LogonID=mssqluser

Password=mssqlpassword

PortNumber=1433

·         Update the below settings into the .bash_profile or .profile

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:$ORACLE_HOME/lib

ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

At this point, you can test the odbc connectivity as user 'oracle' as below:

/opt/Progress/DataDirect/Connect64_for_ODBC_71/samples/demo/demoodbc -uid -pwd  mssql

• Update the listener.ora with the below settings

*********************

SID_LIST_LISTENER = 

   (SID_LIST = 

      (SID_DESC = 

       (SID_NAME=DG4ODBC) 

      (ORACLE_HOME=ORACLE_HOME_PATH) 

      (ENV="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:ORACLE_HOME_PATH/lib"

      (PROGRAM=dg4odbc) 

      ) 

   ) 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

ADR_BASE_LISTENER = /u00/app/oracle

*********************

• Add the below entry to the tnsnames.ora as below

*********************

DG4ODBC = 

   (DESCRIPTION= 

      (ADDRESS=(PROTOCOL=tcp)(HOST=Linux Hostname)(PORT=1521)) 

      (CONNECT_DATA=(SID=DG4ODBC)) 

      (HS=OK) 

   )

*********************

• Setup the init.ora of the Gateway, there is a sample file under $ORACLE_HOME/hs/admin directory.

***********************

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = mssql

HS_FDS_TRACE_LEVEL = on

HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set =

***********************

• Configure the Oracle database link

create database link connect to “sqluser"  identified by "pwd" using 'DG4ODBC';

• Restart the listener

Now you're ready to access MS SQL from your Oracle instance , say as below

select count(*) from sqltable@dblink;

or select count(*) from sqldatabase.sqltable@dblink etc.

PSU 11.2.0.3.2

Was applying PSU April 2012 on a 11.2.0.3 server recently, so that's 11.2.0.3.2 patch on a Linux x86_64bit server running 11.2.0.3.1.

Didn't realize there wasn't enough space on Oracle home file system, ran the opatch prereq check as below;

opatch prereq CheckConflictAgainstOHWithDetail -ph ./ came back OK, succeeded with the below message.

Prereq "checkConflictAgainstOHWithDetail" passed.

Then went on to apply the patch using opatch apply, it failed due to disk space errors after running for a few min's.

Stack Description: java.lang.RuntimeException: /usr/bin/ar: /.../.../.../...//lib/libgeneric11.a: No space left on device

Oops...released some space and then tried to rerun opatch again...

This time around it failed to apply a few archives as below

Archive not applied /.../.../.../.../13696216/13696216/files/lib/libserver11.a/ksfd.o to /u00/app/oracle/product/11.2.0/dbhome_1/lib/libserver11.a... '' 

Archive not applied /.../.../.../.../13696216/13696216/files/rdbms/lib/libperfsrv11.a/qcodfdef_PERF.o to /u00/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/libperfsrv11.a... '' 

Archive not applied /.../.../.../.../13696216/13696216/files/lib/libordsdo11.a/mdopp.o to /u00/app/oracle/product/11.2.0/dbhome_1/lib/libordsdo11.a... ''

Not looking good, then it finally ended with similar messages as below...

    OUI-67124:ApplySession failed in system modification phase... 'Verification of patch failed: Error reading files to verify "/.../13696216/13696216/files/lib/libordsdo11.a/mdopp.o" == "/.../.../.../.../.patch_storage/verify/archive/lib/libordsdo11.a/mdopp.o".  Can't verify.'

[Jun 28, 2012 7:21:33 PM]    3) OUI-67124:

                             NApply restored the home. Please check your ORACLE_HOME to make sure:

                               - files are restored properly.

                               - binaries are re-linked correctly.

                             (use restore.[sh,bat] and make.txt (Unix only) as a reference. They are located under

                             "/.../.../.../...//.patch_storage/NApply/2012-06-28_19-17-23PM"

Then tried to use the restore.sh as suggested, that didn’t work either. This is a worry now as the patch status is in a state of limbo.

Finally, restored those ../rdbms/lib/libserver11.a etc (4 in total) files from the backup, ran the opatch again and it succeeded.

Challenge is not over yet, tried to bring up the database for running the post psu sql scripts and now it fails with the below errors....

SQL> startup

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2228784 bytes

Variable Size             402656720 bytes

Database Buffers         1191182336 bytes

Redo Buffers                7344128 bytes

ORA-00201: control file version 11.2.0.0.0 incompatible with ORACLE version

10.2.0.3.0

ORA-00202: control file: '/.../.../.../control01.ctl'

This test env was upgraded recently to 11g and the compatible was still at 10.2.0.3.

So did a shutdown abort, removed the compatible entry from the pfile and restarted the instance, everything looks OK finally.

This db had PSU 11.2.0.3.1 applied at the time of 11g upgrade and worked OK so it seems 11.2.0.3.2 doesn’t work with compatible at 10.2.0.3, which is another finding from this exercise.

This will be an issue if you’ve just upgraded to 11.2.0.3 from 10.2 and tried to apply this PSU with keeping the compatible at 10.2 for having that ability to downgrade.

I think Oracle should also include some space checks into the prereq section as that will save a lot of time if you happen to have a nearly full Oracle home directory and do not realise it before running opatch.

11gR2 RAC VIP and SCAN addresses not reachable

Restart a node in a 11.2.0.3 (GI and DB both) cluster and the node vip is not reachable from the external network, restart the other nodes too and now port 1521 on all the cluster vip addresses and scan addresses is not reachable.

This is a rather serious issue even in a dev cluster and all the cluster services are actually up and running including SCAN vip addresses, SCAN listeners, local listeners etc.

However the port 1521 on the node vip addresses is reachable from the other nodes of the cluster or from the same subnet to be more specific.

This took a while to troubleshoot involving security and sysadmin teams and finally the issue was due to a weird mac address entry at the firewall arp cache and clearing the cache resolved it and the root cause is in the Oracle clusterware due to a strange Oracle bug 13440962 which says

"After upgrading to 11.2.0.3, after vip failover, the ip address is

not pingable from a different subnet on Linux."

And the fix is to run the below:

After vip failover, run command

/sbin/arping -U -c 3 -I  public NIC for vip   vip ip address

to update the ARP table of router.


There's a one-off patch 13440962 available for Linux x86-64 platform but not sure if this is included in any recent PSU's.

sqlplus hangs....

Came across this bug today where sqlplus hangs while everything else on this old Linux 3 vm (an app server) appears to be running. Could this be due to some missing libraries.... or a memory leak...load appears normal.

strace shows infinite 'times(null)' system calls and it turns out to be a bug in the Oracle client oci in 10.2.0.1 and apparently this bug# 4612267 presents itself if the server uptime is more than 248 days and in this case, the uptime is exactly 249 days.

This is a legacy system so it's untouched for a long time, a good reminder to upgrade and patch systems regularly.

Finally, a simple restart of the app server fixed the issue and gave us another 248 days to think about the patch ;-)

NLS_LENGTH_SEMANTICS & ORA-01450

NLS_LENGTH_SEMANTICS setting allows us to specify the length of a column in 'characters' or 'bytes' and 'byte' is it's default value.

Had one index creation failure today during a OBIEE install with ORA-01450 and this is in a Unicode AL32UTF8 database with 'nls_length_semantics=char' set.

A quick check of dba_tab_columns reveal the data_length being 4x the size of the column that's being defined due to the UNICODE characterset and the nls_length_semantics value of 'CHAR'.

SQL> select COLUMN_NAME, DATA_TYPE,DATA_LENGTH, CHAR_LENGTH,CHAR_USED      from dba_tab_columns

     where table_name='MDS_COMPONENTS';

COLUMN_NAME             DATA_TYPE       DATA_LENGTH     CHAR_LENGTH  C
-------------------------- ----------- ----------- ----------------------
COMP_COMMENT             VARCHAR2         4000               4000    C
COMP_ID                  VARCHAR2         3200                800    C
COMP_VALUE               VARCHAR2         4000               4000    C
COMP_LOCALNAME           VARCHAR2         3200                800    C
COMP_PREFIX              VARCHAR2          508                127    C
COMP_NSID                  NUMBER           22                  0
.....
.....

10 rows selected.

and a desc mds_components shows the columns being defined as VARCHAR2 (800 CHAR) due to the above settings. 

COMP_PREFIX VARCHAR2(127 CHAR)

COMP_LOCALNAME VARCHAR2(800 CHAR)

COMP_VALUE VARCHAR2(4000 CHAR)

COMP_ID VARCHAR2(800 CHAR)

COMP_COMMENT VARCHAR2(4000 CHAR)

However more interestingly an 'alter system set nls_length_semantics=BYTE scope=both' doesnt actually come into effect until the instance is restarted, though it allows 'both' in scope.

Anyway, changing the nls_length_semantics=byte finally fixed the issue with the index failure.

Deploying OEM 12c Management agents

OEM 12c agents can be deployed using a few options and here's the simple procedure for the 'silent install' option using the response file method.

• Extract the Management agent software from the OMS for your platform.

                (asssuming the OMS_HOME is "/u01/app/middleware/oms")

             /u01/app/middleware/oms/bin/emcli login -username=sysman
             /u01/app/middleware/oms/bin/emcli sync
             /u01/app/middleware/oms/bin/emcli get_supported_platforms

Here's the command used to extract the binaries for Linux x86_64 bit (by default OMS host will have the agent binaries for the platform on which OMS is rnning)

/u01/app/middleware/oms/bin/emcli get_agentimage -destination="/tmp/agent_Linux_x86_64" -platform="Linux x86-64" -version=12.1.0.1.0

This will generate the "12.1.0.1.0_AgentCore_226.zip" zip file under /tmp.

• Copy this zip file to a temporary location to the Host where the agent need to be installed.
• unzip the file and update the response file as below.

               OMS_HOST="OMS Server"
               EM_UPLOAD_PORT="4889"
               AGENT_REGISTRATION_PASSWORD="xxxxxx"
               #AGENT_INSTANCE_HOME=
               AGENT_PORT=3872
               b_startAgent=true
               ORACLE_HOSTNAME="Host where Management Agent need to be installed"
               s_agentHomeName="agent12c"

• Deploy the agent as below

/tmp/agentDeploy.sh AGENT_BASE_DIR=/u00/app/oracle/product/11.2.0/agent12c RESPONSE_FILE=/tmp/agent.rsp

Pls ensure AGENT_BASE_DIR is NOT a parent directory of any other Oracle software installation directories. This will cause serious issues if you try to uninstall in future for any reason as the entire 'agent_base_dir' directory will be removed after the deinstallation of the agent software.

Once the agent installation is complete, it'll prompt you to run the root.sh and that'll complete the agent setup and the uploads will commence immediately.


 

OEM 12c, Advanced Installation

Just finished an advanced install of OEM 12c (Bundle Patch 1) 12.1.0.1 and it's been much easier than I expected.

For those of you planning to install OEM12c, there's new Bundle patch version (12.1.0.1) available on otn download page which is the newest patchset available after the October 2011 product release.

*This post was first written in Dec, 2012 however the new OEM12c Release 2 is already out in Feb, 2013 and if you're planning to install or upgrade now, it's recommended to go to the newest release ...
"Oracle Enterprise Manager Cloud Control 12c Release 2 Plug-in Update 1 (12.1.0.2) New!"

Here's a quick summary of the installation steps that I've followed.

• Install Oracle Database Server 11.2.0.3(with PSU January 2012)
• Create a database for EM repository
• Download the OEM 12c (12.1.0.1) software enterprise-manager/downloads and unpack into a temporary folder
• After the usual 'support credentials' and 'search for updates' section, the installer will prompt you to choose the type of installation 'Simple or Advanced'.
• Once you select 'Advanced' and provide the 'middleware home' location say '/u01/app/middleware', it moves into "select plug-in's" section
• In this section by default, 'Oracle Database, Oracle Fusion Middleware, Oracle Exadata, Oracle MOS(My Oracle Support) are selected.
• Next section is the Weblogic Configuration where you set a password for weblogic admin user and Node Manager accounts and also provide the OMS Instance Base location which is '/u01/app/middleware/gc_inst'
• Next Section is the Database details, usual Host, Service, port and sys password for repository setup
• After this point, the installer will perform will perform a few db configuration checks and might ask you to disable the 'automatic statistics collection job' and also increase the open cursors etc.
• Next section will ask you choose the 'sysman' and agent registration passwords and also the tablespace/datafile details in the repository database.
• The final screen is the port configuration section where  you can go with the default ports or modify as per  your standards.

The important one's here are the EM upload http and https ssl   ports which are             usually 4889     and 1159 and also the Management agent port 3872.

Once you review the summary page and continue, the install will begin and after the usual root.sh, the OEM 12c installation finished within an hour and OEM 12c is ready for monitoring the managed targets.