---+ APPX 5 connectivity to Oracle on Linux _How to configure APPX 5 to store data inside Oracle on Linux._ <em>%TOC%<br /></em> ---++ Overview Release 5.0.0 or higher of the APPX Utility allows APPX to connect to Oracle via Oracle's Instant Client instead of the Oracle Client Interface. This allows for an easier configuration because the files can be placed on the server by hand instead of making use of an Oracle install routine to place the Client. This document will show you were to get the Oracle Instant Client, where you might place the Instant Client Files from Oracle, and how to make APPX aware of the Oracle Instant Client files so that you can store APPX data inside Oracle. This document will focus on the Linux platform, specifically the Red Hat Enterprise product. ---++ Oracle Instant Client ---+++ Downloading APPX requires a 32 bit Oracle interface for all Oracle connections. Even if your OS is 64 bit, even if your Oracle is 64 bit, APPX requires you to connect to Oracle via the 32 bit version of the Oracle Instant Client. You can download the 32 bit version of Oracle Instant Client <a href="http://www.oracle.com/technology/software/tech/oci/instantclient/index.html" title="Download 32 bit Oracle Instant Client">here</a>. On my test server here I have APPX installed on Red Hat Enterprise 5 64 bit OS. Oracle is installed on a secondary server - Red Hat Enterprise 4 32 bit. The connection from APPX to Oracle should work fine to Oracle 10.2.0.4 and above (including 11). I downloaded the 32 bit version of Oracle's Instant Client 10.2.0.4, selecting two file bundles, _Instant Client Package - Basic_ and _Instant Client Package - SQL*Plus_. I chose the rpm, but the zip packages work just as well. The rpms deposited the Oracle Instant client files as shown below: ---+++ Installing <verbatim style="padding-left: 30px;">[root@appx502 10.2.0.4-32bit]# rpm -ivh oracle-instantclient-basic-10.2.0.4-1.i386.rpm oracle-instantclient-sqlplus-10.2.0.4-1.i386.rpm Preparing... ########################################### [100%] 1:oracle-instantclient-ba########################################### [ 50%] 2:oracle-instantclient-sq########################################### [100%] [root@appx502 10.2.0.4-32bit]# [root@appx502 10.2.0.4-32bit]# rpm -qlp oracle-instantclient-basic-10.2.0.4-1.i386.rpm oracle-instantclient-sqlplus-10.2.0.4-1.i386.rpm /usr/lib/oracle/10.2.0.4/client/bin/genezi /usr/lib/oracle/10.2.0.4/client/lib/libclntsh.so.10.1 /usr/lib/oracle/10.2.0.4/client/lib/libnnz10.so /usr/lib/oracle/10.2.0.4/client/lib/libocci.so.10.1 /usr/lib/oracle/10.2.0.4/client/lib/libociei.so /usr/lib/oracle/10.2.0.4/client/lib/libocijdbc10.so /usr/lib/oracle/10.2.0.4/client/lib/ojdbc14.jar /usr/bin/sqlplus /usr/lib/oracle/10.2.0.4/client/bin/sqlplus /usr/lib/oracle/10.2.0.4/client/lib/glogin.sql /usr/lib/oracle/10.2.0.4/client/lib/libsqlplus.so /usr/lib/oracle/10.2.0.4/client/lib/libsqlplusic.so [root@appx502 10.2.0.4-32bit]#</verbatim> ---+++ Testing The location isn't terribly important, only that you know where the library files libclntsh, libnnz10, libocci, libociei and the executable file sqlplus are located. Let's test the Oracle Instant Client with SQL*Plus. You can't test the Oracle Instant Client with just any SQL*Plus. You must use a version of SQL*Plus that was developed to use the Instant Client library files. To perform the SQL*Plus connection we need five pieces of information from your Oracle DBA. My example values are listen in parenthesis below: * Oracle SID (customer) * TCP port number that Oracle is configured to listen (TCP 1521) * Resolvable DNS name or IP address of the Oracle server (10.50.0.6) * Oracle user ID (larry) * Oracle password (yacht) I'll use the Oracle Easy Connect syntax to log in. ---++++ Failure without LD_LIBRARY_PATH <verbatim style="padding-left: 30px;">[root@appx502 ~]# [root@appx502 ~]# /usr/lib/oracle/10.2.0.4/client/bin/sqlplus larry/yacht@10.50.0.6:1521/customer /usr/lib/oracle/10.2.0.4/client/bin/sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory [root@appx502 ~]# </verbatim> Notice the failure to find libsqlplus.so in the text above. You can resolve this on Linux with LD_LIBRARY_PATH. An example is below. ---++++ Success with LD_LIBRARY_PATH <verbatim style="padding-left: 30px;">[root@appx502 ~]# export LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.4/client/lib [root@appx502 ~]# [root@appx502 ~]# /usr/lib/oracle/10.2.0.4/client/bin/sqlplus larry/yacht@10.50.0.6:1521/customer SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 8 13:06:38 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options [root@appx502 ~]# </verbatim> ---++ APPX We can configure APPX now that we know that we can connect to Oracle using the Instant Client library files via the special Instant Client version of SQL*Plus (you did perform that test right?). In order for APPX to successfully connect to Oracle via the Oracle Instant Client, it must have visibility of certain environment variables prior to the start of APPX. Setting these environment variables in the commonly used appx.env file will not work. Setting them in the shell would work for command line APPX sessions. You should consider setting them inside the service environment variable file so that all connections to APPX via TCP (even the text based ones via appx -c) will be able to connect to Oracle via the Oracle Instant Client. ---+++ Configuration Since the environment variables needed to enable APPX make use of the Oracle Instant Client must be present _prior_ to the start of APPX, I've decided to place them in the APPX Login Manager environment variable file. ---++++ Environment variables I'm going to set two environment variables, the first is for the OS (LD_LIBRARY_PATH) and the second is for APPX (APPX_OCI_LIB). These will be placed inside my APPX Login Manager environment variable file /usr/local/appx/services/appx-8060.env). <verbatim style="padding-left: 30px;">[appx@appx502 services]$ pwd /usr/local/appx/services [appx@ocportal services]$ [appx@ocportal services]$ cat appx-8060.env # Appx connection manager environment variables # # The entries in this file will become # environment variables in the engines # spawned by this service # # blank lines are ignored # # anything following a '#' is treated as a comment # # letter case IS important in this file # -------------------------------------------------- APPX_KEYMAP = Windows APPXPATH = /usr/local/appx/data LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.4/client/lib/ # APPX_SQL_CMD=/usr/local/appx/appx_sql_cmd.txt # APPX_DBG_CODE=0xFFFFFFF # APPX_OCI_DIR=/usr/lib/oracle/10.2.0.4/client/lib/ APPX_OCI_LIB=libclntsh.so.10.1 [appx@appx502 services]$ </verbatim> The two environment varialbes needed for for the Oracle connection are LD_LIBRARY_PATH and APPX_OCI_LIB. ---++++ FMS settings We need to create an FMS group of type 5 (Oracle) that will point to the Oracle server. To do this we will need the same five pieces of Oracle configuration data that we used when we performed a test connection to Oracle using the Instant Client version of SQL*Plus. The values that I'm using in this example are listed below in parenthesis. * Oracle SID (customer) * TCP port number that Oracle is configured to listen (TCP 1521) * Resolvable DNS name or IP address of the Oracle server (10.50.0.6) * Oracle user ID (larry) * Oracle password (yacht) ---+++++ FMS Group Creation After creating the FMS group, we will define an APPX DMO PROSPECT to make use of this FMS group, finally performing a CREATE FILE from within APPX to create the file in Oracle. These steps are documented in screen shots below. <img alt="001.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/001.png" title="Fig. 1" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/001.png" title="Figure 1"> Fig. 1 </a> First we need to go to our FMS seetings inside APPX. <img alt="002.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/002.png" title="Fig. 2" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/004.png" title="Figure 2"> Fig. 2 </a> Go into APPX ADD mode and create an FMS group with a meaningful name of a type 5 for your Oracle data. <img alt="003.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/003.png" title="Fig. 3" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/003.png" title="Figure 3"> Fig. 3 </a> Now you need to fill out at least Server Name and Table Naming Scheme. You can select the HINTS button to see other suggested formats for the Table Naming Scheme if you wish. <img alt="004.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/004.png" title="Fig. 4" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/004.png" title="Figure 4"> Fig. 4 </a> I'm going to create a default identity that all my APPX connections to Oracle will use that don't have a specific matching identity. <img alt="005.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/005.png" title="Fig. 5" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/005.png" title="Figure 5"> Fig. 5 </a> My default identity is Oracle user ID larry. Any APPX user without a specified Oracle Identity will connect to Oracle with this user ID. <img alt="006.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/006.png" title="Fig. 6" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/006.png" title="Figure 6"> Fig. 6 </a> Figure 6 shows the entry of the default identity password. This is the password for my Oracle user ID larry. ---+++++ Define Data file to point to Oracle We're now finished with the creation of the FMS group. In the six screen shots above, you can see the creation of the FMS type 5 group named oracle10. We now are going to define APPX DMO PROSPECT to make use of this FMS group so that the data can be stored in Oracle. This will be documented in the following seven screen shots. <img alt="007.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/007.png" title="Fig. 7" /> <a href=".com/wiki/pub/Main/APPX500ToOracleOnLinux/007.png" title="Figure 7">Fig. 7</a> <img alt="008.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/008.png" title="Fig. 8" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/008.png" title="Figure 8">Fig. 8</a> <img alt="009.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/009.png" title="Fig. 9" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/009.png" title="Figure 9"> Fig. 9 </a> <img alt="010.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/010.png" title="Fig. 10" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/010.png" title="Figure 10"> Fig. 10 </a> <img alt="011.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/011.png" title="Fig. 11" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/011.png" title="Figure 11"> Fig. 11 </a> <img alt="012.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/012.png" title="Fig. 12" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/012.png" title="Figure 12">Fig. 12</a> <img alt="013.png" src="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/013.png" title="Fig. 13" /> <a href="http://wiki.appx.com/wiki/pub/Main/APPX500ToOracleOnLinux/013.png" title="Figure 13">Fig. 13</a> -- Main.JoeOrtagus - 2010-07-07
This topic: Main
>
APPX500ToOracleOnLinux
Topic revision: r30 - 2010-07-08 - JoeOrtagus
Copyright © 2008-2024 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki?
Send feedback