(r8) DatabaseInterfaceUpgrades < Main

Tags: view all tags
---+ Database Interface Upgrades                   
_Compatibility with newer versions of popular databases is among the many features of APPX 4.3._
%TOC%
---++ Oracle                  
   * Oracle 10g R2 (10.2.0.1)
   * Oracle 11g
The Oracle connectivity is provided by the [[http://www.oracle.com/technology/tech/oci/instantclient/index.html][Oracle Instant Client]].  <br />

Quick installation steps:
   * Install Oracle Instant Client Basic.
   * Make the Oracle Instant Client library files visible to the system with the ldconfig tool or it's equivalent.
   * Set, in either the APPX Login Manager's daemon environment or the appx.env file, the APPX environment variable APPX_OCI_LIB equal to the file name of the liblcntsh.so contained in the Oracle Instant Client library files (For example APPX_OCI_LIB=libclntsh.so.10.1).

Detailed installation example:

Here are the steps I used to make connection with APPX 4.3.0 and Oracle 10gR2 with both installed on the same Red Hat Enterprise 5.1, 32 bit server. 

I'm going to create a directory to place the zipped bundled Oracle Instant Client files.  For my example, I'm going to use /usr/local/src/oracle/instantclient/.
<pre>
[root@tubes /]# <strong>mkdir -p /usr/local/src/oracle/instantclient/</strong> 
[root@tubes /]# <strong>cd /usr/local/src/oracle/instantclient/</strong>
[root@tubes instantclient]# 
</pre>
Download the Oracle Instant Client files for Linux.  APPX requires at least the Basic package, but it is recommended that you grab the SQL*Plus package too for debugging. 

For 10gR2, the current versions are, oracle-instantclient-basic-10.2.0.4-1.i386.zip  (34,687,861 bytes), and oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip (744,730 bytes) 

For 11g, the current versions are, basic.zip  (42,285,643 bytes), and sqlplus.zip (784,417 bytes) 

Place the basic and sqlplus zipped files into /usr/local/src/oracle/instantclient/, and unzip them:

<pre>
[root@tubes instantclient]# <strong>pwd</strong>
/usr/local/src/oracle/instantclient
[root@tubes instantclient]# <strong>ls -la</strong>
total 34656
drwxr-xr-x 2 root root     4096 Jun  5 15:01 .
drwxr-xr-x 3 root root     4096 Jun  5 14:59 ..
-rw-r--r-- 1 root root 34687861 Jun  5 15:01 oracle-instantclient-basic-10.2.0.4-1.i386.zip
-rw-r--r-- 1 root root   744730 Jun  5 15:01 oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
[root@tubes instantclient]# <strong>unzip oracle-instantclient-basic-10.2.0.4-1.i386.zip</strong> 
Archive:  oracle-instantclient-basic-10.2.0.4-1.i386.zip
  inflating: instantclient_10_2/BASIC_README  
  inflating: instantclient_10_2/classes12.jar  
  inflating: instantclient_10_2/genezi  
  inflating: instantclient_10_2/libclntsh.so.10.1  
  inflating: instantclient_10_2/libnnz10.so  
  inflating: instantclient_10_2/libocci.so.10.1  
  inflating: instantclient_10_2/libociei.so  
  inflating: instantclient_10_2/libocijdbc10.so  
  inflating: instantclient_10_2/ojdbc14.jar  
[root@tubes instantclient]# <strong>unzip oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip</strong>
Archive:  oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
  inflating: instantclient_10_2/SQLPLUS_README  
  inflating: instantclient_10_2/glogin.sql  
  inflating: instantclient_10_2/libsqlplus.so  
  inflating: instantclient_10_2/libsqlplusic.so  
  inflating: instantclient_10_2/sqlplus  
[root@tubes instantclient]# <strong>ls -la</strong>
total 34660
drwxr-xr-x 3 root root     4096 Jun  5 15:01 .
drwxr-xr-x 3 root root     4096 Jun  5 14:59 ..
drwxr-xr-x 2 root root     4096 Jun  5 15:01 instantclient_10_2
-rw-r--r-- 1 root root 34687861 Jun  5 15:01 oracle-instantclient-basic-10.2.0.4-1.i386.zip
-rw-r--r-- 1 root root   744730 Jun  5 15:01 oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
[root@tubes instantclient]# <strong>ls -la instantclient_10_2/</strong>
total 102424
drwxr-xr-x 2 root root     4096 Jun  5 15:01 .
drwxr-xr-x 3 root root     4096 Jun  5 15:01 ..
-rw-rw-r-- 1 root root      238 Feb 17 03:57 BASIC_README
-r--r--r-- 1 root root  1609607 Feb 17 03:57 classes12.jar
-rwxrwxr-x 1 root root    67766 Feb 17 03:57 genezi
-r--r--r-- 1 root root     1525 Feb 17 03:58 glogin.sql
-rwxrwxr-x 1 root root 19076649 Feb 17 03:57 libclntsh.so.10.1
-r-xr-xr-x 1 root root  5656445 Feb 17 03:57 libnnz10.so
-rwxrwxr-x 1 root root  1398244 Feb 17 03:57 libocci.so.10.1
-rwxrwxr-x 1 root root 72698427 Feb 17 03:57 libociei.so
-r-xr-xr-x 1 root root   120443 Feb 17 03:57 libocijdbc10.so
-r-xr-xr-x 1 root root  1434263 Feb 17 03:58 libsqlplusic.so
-r-xr-xr-x 1 root root  1069221 Feb 17 03:58 libsqlplus.so
-r--r--r-- 1 root root  1555682 Feb 17 03:57 ojdbc14.jar
-r-xr-xr-x 1 root root     8903 Feb 17 03:58 sqlplus
-rw-rw-r-- 1 root root      242 Feb 17 03:58 SQLPLUS_README
[root@tubes instantclient]# 
</pre>
Now move the unzipped directory (instantclient_10_2) that contains both the basic and SQL*Plus files from the Oracle Instant Client, into the desired permanent directory.  In my example, I'm using /usr/local/, so that the Oracle files will be in a directory /usr/local/instantclient_10_2/.
<pre>
[root@tubes instantclient]# <strong>pwd</strong>
/usr/local/src/oracle/instantclient
[root@tubes instantclient]# <strong>ls -la</strong>
total 34660
drwxr-xr-x 3 root root     4096 Jun  5 15:01 .
drwxr-xr-x 3 root root     4096 Jun  5 14:59 ..
drwxr-xr-x 2 root root     4096 Jun  5 15:01 instantclient_10_2
-rw-r--r-- 1 root root 34687861 Jun  5 15:01 oracle-instantclient-basic-10.2.0.4-1.i386.zip
-rw-r--r-- 1 root root   744730 Jun  5 15:01 oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip
[root@tubes instantclient]# <strong>ls /usr/local/</strong>
appx  appx.tgz  basic.zip  bin  etc  games  include  lib  libexec  sbin  share  src
[root@tubes instantclient]# <strong>mv instantclient_10_2/ /usr/local/</strong>
[root@tubes instantclient]# <strong>ls /usr/local/</strong>
appx  appx.tgz  basic.zip  bin  etc  games  include  instantclient_10_2  lib  libexec  sbin  share  src
[root@tubes instantclient]# 
</pre>
Now we must register the Oracle Instant Client library files with the system to make the shared library's path known.  I'm going to use ldconfig.
<pre>
[root@tubes instantclient]# <strong>ldconfig -v|grep libclntsh</strong>
[root@tubes instantclient]# <strong>ldconfig -v|grep instant</strong>
[root@tubes instantclient]# 
[root@tubes instantclient]# <strong>cat /etc/ld.so.conf</strong>
include ld.so.conf.d/*.conf
[root@tubes instantclient]# <strong>echo /usr/local/instantclient_10_2/ &gt;&gt; /etc/ld.so.conf</strong>
[root@tubes instantclient]# <strong>cat /etc/ld.so.conf</strong>
include ld.so.conf.d/*.conf
/usr/local/instantclient_10_2/
[root@tubes instantclient]# 
</pre>
Now, let's rebuild the ldconfig cache, and verify that the library's are available system wide.
<pre>
[root@tubes instantclient]# <strong>ldconfig</strong>
[root@tubes instantclient]# <strong>ldconfig -v|grep libclntsh</strong>
 libclntsh.so.10.1 -&gt; libclntsh.so.10.1
[root@tubes instantclient]# <strong>ldconfig -v|grep instant</strong>
/usr/local/instantclient_10_2:
[root@tubes instantclient]# 
</pre>
APPX needs to know the name of the Oracle libclntsh.so file on your system.  This file name changes from version to version.  We must now identify the libclntsh file name that was distributed in the Oracle Instant Client bundle.  Look inside the instantclient directory for a file that begins with libclntsh.  To show you examples of Oracle Instant Client 10.2 and Oracle Instant Client 11.1, I have installed both versions in /usr/local/.
<pre>
[joe@tubes instantclient_10_2]$ <strong>ls -la /usr/local/instantclient_1*/libclnt*</strong>
-rwxrwxrwx 1 root   root     19076649 Feb 17 03:57 /usr/local/instantclient_10_2/libclntsh.so.10.1
-rwxrwxrwx 1 root   root     35295891 Aug  1  2007 /usr/local/instantclient_11_1/libclntsh.so.11.1
[joe@tubes instantclient_10_2]$ 
</pre>
As you can see my libclntsh files are libclntsh.so.10.1, which came bundled with the Oracle Instant Client 10.2 Basic bundle, and libclntsh.so.11.1, which came bundled with the Oracle Instant Client 11.1 Basic bundle.

To let APPX know the version of the Instant client you have installed, perform just one of the following two options:

   * I can now either create a symbolic link named libclntsh.so that points to the version I want to use and set the APPX_OCI_DIR environment variable equal to the location of the Oracle Instant Client files:
<pre>
[root@tubes appx]# <strong>cd /usr/local/instantclient_10_2/</strong>
[root@tubes instantclient_10_2]# <strong>ln -s /usr/local/instantclient_10_2/libclntsh.so.10.1 /usr/local/instantclient_10_2/libclntsh.so</strong>
[root@tubes appx]# <strong>ls -la /usr/local/instantclient_10_2/libclntsh.so</strong>
lrwxrwxrwx 1 root root 47 Jun  5 15:56 libclntsh.so -&gt; /usr/local/instantclient_10_2/libclntsh.so.10.1
[root@tubes instantclient_10_2]# 
[root@tubes appx]# <strong>grep APPX_OCI_DIR /usr/local/appx/data/appx.env</strong>
[root@tubes instantclient_10_2]# 
[root@tubes appx]# <strong>echo APPX_OCI_DIR=/usr/local/instantclient_10_2/ &gt;&gt; /usr/local/appx/data/appx.env</strong>
[root@tubes appx]# <strong>grep APPX_OCI_DIR /usr/local/appx/data/appx.env</strong>
APPX_OCI_DIR=/usr/local/instantclient_10_2/
[root@tubes appx]# 
</pre>
   * Or I can set an APPX environment variable equal to the name of the file.  First let's see if it is set in the %APPXPATH%/appx.env file:

<pre>
[root@tubes appx]# 
[root@tubes appx]# <strong>grep APPX_OCI_LIB /usr/local/appx/data/appx.env</strong>
[root@tubes appx]# 
[root@tubes appx]# <strong>echo APPX_OCI_LIB=libclntsh.so.10.1 &gt;&gt; /usr/local/appx/data/appx.env</strong>
[root@tubes appx]# 
[root@tubes appx]# <strong>grep APPX_OCI_LIB /usr/local/appx/data/appx.env</strong>
APPX_OCI_LIB=libclntsh.so.10.1
[root@tubes appx]# 
</pre>
Now let's verify that we can connect to Oracle using Oracle's SQL*Plus tool.  I'm going to use the following parameters for my example, be sure and use values that are correct for your Oracle instance.  user=scott, password=password, server=tubes, port=1521, and Oracle Instance=appx.
<pre>
[root@tubes instantclient_10_2]# <strong>/usr/local/instantclient_10_2/sqlplus scott/password@tubes:1521/appx</strong>
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 4 09:17:55 2008
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&gt; <strong>show all</strong>
appinfo is OFF and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autorecovery OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the first few characters of the next SELECT statement
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
COPYTYPECHECK is ON
define "&amp;" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT ON
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
escchar OFF
FEEDBACK ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
instance "local"
linesize 80
lno 14
loboffset 1
logsource ""
long 80
longchunksize 80
markup HTML OFF HEAD "" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PREFORMAT OFF
newpage 1
null ""
numformat ""
numwidth 10
pagesize 14
PAUSE is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 1002000100
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
shiftinout INVISIBLE
showmode OFF
spool OFF
sqlblanklines OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "&gt; "
sqlnumber ON
sqlpluscompatibility 10.2.0
sqlprefix "#" (hex 23)
sqlprompt "SQL&gt; "
sqlterminator ";" (hex 3b)
suffix "sql"
tab ON
termout ON
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the first few characters of the next SELECT statement
underline "-" (hex 2d)
USER is "SCOTT"
verify ON
wrap : lines will be wrapped
SQL&gt; <strong>quit</strong>
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[root@tubes instantclient_10_2]# 
</pre>
Well, that looks good.  Let's configure our FMS settings inside APPX and make a connection!

   * In APPX, Go to System Administration, then File Management, then FMS Groups.
<img alt="oracle-fms-01.jpg" src="%ATTACHURL%/oracle-fms-01.jpg"></img>

   * Create an FMS group with an appropriate name, and specify type 5.
<img alt="oracle-fms-02.jpg" src="%ATTACHURL%/oracle-fms-02.jpg"></img>

   * File in the Server Name and Table Naming Scheme in the FMS Group Attributes of your new FMS group.  The Server Name field has a format of ORACLE-HOST-NAME:ORACLE-PORT/ORACLE-SID. The Table Naming Scheme could be picked from the suggestions shown when clicking on the Hints button.
<img alt="oracle-fms-03.jpg" src="%ATTACHURL%/oracle-fms-03.jpg"></img>

   * Under Identities, define at least a default identity.
<img alt="oracle-fms-04.jpg" src="%ATTACHURL%/oracle-fms-04.jpg"></img>

   * Enter the Oracle account to use for the Default Identity, leave Realm empty.
<img alt="oracle-fms-05.jpg" src="%ATTACHURL%/oracle-fms-05.jpg"></img>

   * Enter the Oracle account's password.
<img alt="oracle-fms-06.jpg" src="%ATTACHURL%/oracle-fms-06.jpg"></img>

Some environment variables to keep in mind. 
   * APPX
      * APPX_OCI_DIR
      * APPX_OCI_LIB
      * APPX_OCI_LIBS
      * APPX_SS_LOG
      * APPX_SS_CODE
      * APPX_SQL_CMD=/usr/local/appx/appx_sql_cmd.log
      * APPX_DBG_CODE=0xFFFFFFFF
   * Oracle
      * ORACLE_SID
      * ORACLE_HOME
      * TNS_ADMIN
Some OS tools to aid in debugging
   * strace
   * ldd

---++ ODBC                 
   * MySQL 5.0.22
   * PostgreSQL 8.1.9
   * IBM DB/2
   * Microsoft SQL-Server (2000, 2005, 2008, Express)
---++ Comments:                       
_Read what other users have said about this page or add your own comments.
%COMMENT% 
-- AlKalter - 04 Apr 2008
Attachments
Topic attachments
I	Attachment	History	Action	Size	Date	Who	Comment
jpg	oracle-fms-01.jpg	r2 r1	manage	78.2 K	2008-06-05 - 21:10	JoeOrtagus	APPX System Administration, File Management, FMS Groups.
jpg	oracle-fms-02.jpg	r1	manage	39.5 K	2008-06-05 - 21:13	JoeOrtagus	Create FMS group name, and specify type of 5.
jpg	oracle-fms-03.jpg	r1	manage	62.0 K	2008-06-05 - 21:14	JoeOrtagus	Oracle server name, port and SID followed by Table naming scheme.
jpg	oracle-fms-04.jpg	r1	manage	47.5 K	2008-06-05 - 21:16	JoeOrtagus	Specify Identity or the Oracle account to login as.
jpg	oracle-fms-05.jpg	r1	manage	46.0 K	2008-06-05 - 21:16	JoeOrtagus	Enter the Oracle user ID here.
jpg	oracle-fms-06.jpg	r1	manage	50.1 K	2008-06-05 - 21:17	JoeOrtagus	Enter the Oracle password here.
Topic revision: r8 - 2008-06-05 - JoeOrtagus
~~Edit~~
~~Attach~~