Connecting MySQL and ORACLE database - part II - Windows edition

Last post spoke about connecting MySQL database from Oracle database using Oracle Enterprise Linux. This post is almost the same but using M$ Windows. It is almost the same but without CLI.

First download latest (at the time) Connector/ODBC 5.3.2  Windows (x86, 32-bit), MSI Installer from the 
web page: : http://dev.mysql.com/downloads/connector/odbc/

Since I am connectin pre 4.1.1 mySQL database I got this message:

 I found on OTN that bulletproof Connector version that works is 3.51.27. After some search i download it from: http://ftp.nchu.edu.tw/MySQL/downloads/connector/odbc/3.51.html

Create new ODBC source:

In listener ora i add new entry for remote database where SID_NAME is equal to Data Source Name from Connector/ODBC menu (notice that here PROGRAM is hsodbc which points that this is 32 bit Oracle 10g database):

(SID_DESC =

      (SID_NAME = baza)

      (ORACLE_HOME = c:\oracle\ora10)

      (PROGRAM = hsodbc)

    )

Add in tnsnames.ora entry for a remote database:

BAZA =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SID = baza)

    )

    (HS = OK)

  )

In directory %ORACLE_HOME%\hs create init%sid%.ora. I create initbaza.ora with minimum required parameters

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = baza

HS_FDS_TRACE_LEVEL = 0

HS_FDS_TRACE_FILE_NAME=baza.trc

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

After that create user with database link to tables in MySQL database and enjoy.

Accessing data in MySQL server through ORACLE Database

Setup:
MySQL Server version mysqld-4.0.27-nt on Windows 2008 Server x32
Oracle Database 10.2.0.5.0 on Linux 2.6.32
Oracle Database 11.2.0.0.0 "Oracle 11g Gateway Connectivity - dg4odbc" on Linux 2.6.32
MySQL Connector 3.51.27

I uses particular version of MySQL Connector for two reasons:

When I used last 3.51 version which is 3.51.30 in Oracle I got only row from MySQL database. After reading on OTN and Metalink someone suggested 3.51.27 and all worked as a charm.

When I use newer MySQL ODBC connector (5.1.13 or newer) I got error "Connection using old (pre-4.1.1) authentication protol refused (client option 'secure_auth' enabled)".

Procedure:

Install ODBC connector:
[root@localhost ~]#  rpm -Uvh mysql-connector-odbc-3.51.27-0.x86_64.rpm

Check if driver exists in odbcinst.ini

odbcinst.ini is located in /etc or /usr/local/etc

vi /usr/local/etc/odbcinst.ini

[PostgreSQL]
Description             = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage               = 1

[MySQL ODBC 3.51 Driver]
DRIVER          = /usr/lib64/libmyodbc3.so
UsageCount              = 1


Create odbc.ini and enter driver and connection information

vi /usr/local/etc/odbc.ini

[baza]
Description     = ODBC for MySQL
Driver          = /usr/lib64/libmyodbc3.so
SERVER = %IP_ADDRESS_OF_MYSQL_SERVER%
PORT = 3306
USER = %USER_NAME_ON_MYSQL_SIDE%
Password = %PASSWORD_ON_MYSQL_SIDE%
Database = baza
CHARSET=latin1


Create entry in listener.ora. Listener is created in Oracle 10g home, but later for connection we will use Oracle 11g Gateway Connectivity - dg4odbc which is installed in separate 11G home.
Reason for that is that 64-bit Oracle database could not use hsodbc driver and it is needed to install Oracle 11g Gateway Connectivity - dg4odbc in separate home.
I had both database homes on same server so it was somewhat easier for me.

vi /u01/app/10.2.0/db/network/admin/listener.ora
baza =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1527))
                                                      )
   )
                 )
SID_LIST_baza =
   (SID_LIST =
        (SID_DESC =
                (PROGRAM = dg4odbc) %Using 11g executable%
                (ORACLE_HOME = /u01/app/11.2.0/db) %Using 11g HOME%
                (SID_NAME = baza)
                (ENVS=LD_LIBRARY_PATH=/usr/lib64:/usr/local/:/usr/local/etc:/usr/lib/:/u01/app/11.2.0/db/lib) %Metalink recomends using ENVS Variable in listener%
        )
   )

Create tnsnames.ora enty (In Oracle 10g Home)

vi /u01/app/10.2.0/db/network/admin/tnsnames.ora

baza=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1527)))
     (CONNECT_DATA =
        (SID = baza))
        (HS=OK)
)

Create ODBC HS ini file (In Oracle 11g Home)

/u01/app/11.2.0/db/hs/admin/initbaza.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.
                             
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = baza
# HS_FDS_TRACE_LEVEL = DEBUG
# HS_FDS_TRACE_LEVEL=user
HS_FDS_TRACE_LEVEL=0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini


#
# Environment variables required for the non-Oracle system
#
# set <envvar>=<value>

After that you only need to create database link in Oracle 10g database. Put username and password in quotation marks if columns in MySQL datavase are declared with a case sensitive or binary collation

create database link baza
connect to "exuser"
identified by "userex"
using 'baza';


If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation

Using Microsoft Query to retrieve data from Oracle 10G in Excel

Configuration:

Windows 7 x64 Enterprise

Office Professional x64 (Excel 2010)

Microsoft Query (part of excel x64)

Step 1:

• Download Instant Client Package - Basic: All files required to run OCI, OCCI, and JDBC-OCI applications (instantclient-basic-windows.x64-11.2.0.4.0.zip (54,956,947 bytes)) from the website http:// www.oracle.com/technetwork/topics/winx64soft-089540.html

• Create a home directory for example: C: \ ORACLE \ ORACLI11

• unpack the zip file in the directory C: \ ORACLE \ ORACLI11

Step 2:

• Download Instant Client Package - ODBC: Additional libraries for enabling ODBC applications

• (instantclient-odbc-windows.x64-11.2.0.4.0.zip (1,358,385 bytes))

• unpack the files from the zip file in the folder C: \ ORACLE \ ORACLI11

• Start the Command Prompt as Administrator (Run As Administratr)

• In the directory C: \ ORACLE \ ORACLI11 run file odbc_install.exe

• Copy sqlnet.ora and tnsnames in the folder C: \ ORACLE \ ORACLI11

• Create Enviroment a variable TNS_ADMIN and set the value as C: \ ORACLE \ ORACLI11

• After that, should the ODBC to appear: 

Step 3:

• Start MS Excel

• Choose "Tab Data"

• Choose "From other sources"

• Choose "Microsoft Query"

• "Choose Data Source" than "New Data Source" ("Select the default table ..." is  gray for now) 

• Choose "Connect" 

 Service Name: tnsnames.ora entry for the selected database 

• "Select the default table..." is no longer gray and gives a choice of a table 

 • In the "Query Wizard" choose the columns that will be used: 

• In the "Filter Data" select what row would you like to include

• In the "Sort Order" choose columns to sort data: 

• And finally choose to return the data to Microsoft Excel:

Oracle Reports 10G: Windows registry REPORTS_PATH can be extended up to 1024 characters.

On some of test servers I usually use built in report server and to add more applications (paths where rdf's are) I extend REPORTS_PATH variable in registry. Problem is when more application accumulate there are more entries in registry variable.
Maximum is 1024 characters for REPORTS_PATH variables. When that point is reached new reports servers are created.

Procedure to create a report server (Oracle Forms and Reports services server, but can be used on all Oracle Midtier servers):

• Open command prompt (elevated command prompt):

                           rwserver server=%server_name% start

• Shut down the opmn:

                          %OracleMiddleTierHome%\opmn\bin\opmnctl stopall

• Add new server target (command prompt will close automatically if everything is ok):

                           %OracleMiddleTierHome%\bin\addNewServerTarget.bat %server_name%

• Open command prompt (elevated command prompt):

                          %OracleMiddleTierHome%\dcm\bin\dcmctl.bat updateconfig -ct opmn -v -d
                          %OracleMiddleTierHome%\ dcm\bin\dcmctl.bat resyncinstance -v -d

• Start the opmn

                         %OracleMiddleTierHome%\opmn\bin\opmnctl startall


Add into the configuration source directory where reports are (use Enterpise manager) (I also sometimes change the number of engines):

                         %OracleMiddleTierHome%\reports\conf\rep_razees.conf

   <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1" maxEngine="2" minEngine="1" engLife="50" maxIdle="30" callbackTimeOut="90000">
      <property name="sourceDir" value="C:\ORACLE\APLI10\DS10G\RAZ_EES_RSRV"/>
      <!--property name="tempDir" value="your reports temp directory"/-->
      <!--property name="keepConnection" value="yes"/-->
   </engine>

How to Fix: The trust relationship between this workstation and the primary domain failed

Some time trust between workstation and primary domain faile so I cant log on as domain user or map network drive or share. When I try to map shared folder from such server or workstation I usually get error: windows error 0x80004005.
There are few solutions but most popular is to remove server or workstation from domain, restart and join again.
Microsoft gave us some tools to do that without removing server from a domain.

1. Use Powershell: Start Windows Powershell as administrator than write:

Reset-ComputerMachinePassword -Server <Name of any domain controller> -Credential <domain admin account>

2. Use netdom: Start command prompt as administrator than write:

netdom resetpwd /Server:<name of any domain controller> /UserD:<domain admin account> /PasswordD:<password>

I usually reboot server after to be completely sure that server joined domain.