Tuesday, March 27, 2012

AYA Upgrade (install) OBIEE to Linux (from Windows)

This post was made when I found out how much time can one not too experienced OBI DBA loose to find out all details for performing OBIEE migration from Windows to Linux . This is nothing special but mine way of making all receipts to one place are more useful to me for any further reference.

In mine case I had Windows 2003 x86 SP2 (source server) with OBIEE 10.3.4.1 installation in standard locations:
  • c:\OracleBI
  • c:\OracleBIData
This installation has huge problems with Windows, described in one of mine previous posts, client agreed to move to Linux CentOS 5.8 i586 (target server) and upgrade to OBIEE 10.3.1.4.2 (latest 10g version in this moment).
I will assume that Linux is installed with non required but nice tools:
vnceserver
Setup VNC server on CentOS:
http://wiki.centos.org/HowTos/VNC-Server

Enable on server (GUI part)
http://bobpeers.com/linux/vnc
Desktop > Preferences > Remote Desktop

For windows vnc viewer:
http://www.realvnc.com/cgi-bin/download.cgi

rlwrap
as root:
yum install readline-devel

Download the latest version of GNU readline from http://cnswww.cns.cwru.edu/php/chet/readline/rltop.html  At the time of writing, this is readline-6.0.tar.gz

Unpack the gzipped tar file under /tmp
cd /tmp
tar -zxvf readline-6.0.tar.gz

Change to the directory this creates and run configure:
cd readline-6.0
./configure

If you do not have write access to the /usr/local/ hierarchy, then you can install the files somewhere else:
./configure --prefix=/home/my-user-name/packages

Now build the readline library
make
and install (become superuser first if installing under /usr/local)

make install
---------------------------------      
install rlwrap:
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install
-----------------------------------
The process will be done in next steps:
  • Source server backup data
  • Setup install environment on target server
  • Install JAVA on target server
  • Install OBI EE on target server
  • Install Oracle client
  • Install OBI EE client side on Windows host
  • Fix some bugs in new OBI EE installation
  • Setup BI Publisher
  • Apply backup files/settings to target server
  • Add additional fonts
  • Create OBI start/stop script (boot start) on target server

Source server backup data

On source server, of next two source directories:
  1. make zip files of catalog directory (in mine case "c:\OracleBIData\web\catalog\samplesales")
  2. backup repository file (in mine case c:\OracleBI\server\Repository\AZM.rpd)
If you have aditional BI Publisher reports you might find them in:
  • C:\OracleBI\xmlp\XMLP\Users
directory, where exact directory depend on your definition. Zip them with subdirs.

Beside that, according Oracle documentation chapter "Backing Up Business Intelligence Folders and Files", recommended backup of some other files. If you have some other custom changes (some terms change to something els i.e. "SUM" to "Total sum" or some translations to local language) find those files to edit them on target server as well.

Setup install environment on target server

In mince case I'll install OBIEE under oracle user which belongs to dba group.
groupadd -G dba:501
useradd -g dba oracle
passwd oracle
After creation, login once as oracle to create env files. Then append then to the ".bash_profile" of the oracle user important stuff. here is an example Add entries in .bash_profile (for Oracle 10g and OBIEE 10g) so complete profile for oracle user looks like:
# .bash_profile
# 

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi

# rlwrap
#---------------------------
alias sql='rlwrap sqlplus'
alias rlrman='rlwrap rman'
#===========================

# User specific environment and general
#---------------------------

if [ $USER = "oracle" ]; then
  if [ $SHELL = "/bin/ksh" ]; then
    ulimit -p 16384
    ulimit -n 65536
  else
    ulimit -u 16384 -n 65536
  fi
fi

TMP=/var/tmp 
export TMP

TMPDIR=$TMP
export TMPDIR

TEMP=$TMP; 
export TEMP

# Local Binary
PATH=$PATH:$HOME/bin
export PATH
#===========================


# Oracle general Parameters
#---------------------------
ORACLE_BASE=/oracle
export ORACLE_BASE

# Java Home
JAVA_HOME=/usr/java/jdk1.7.0_02
export JAVA_HOME
#===========================


# Oracle BIEE Parameters
#---------------------------
OBI_HOME=/oracle/product/obi10g/OracleBI
export OBI_HOME

OBI_DATA_HOME=/oracle/product/obi10g/OracleBIData
export OBI_DATA_HOME

# OBI Setup Script
PATH=$PATH:$OBI_HOME/setup
export PATH
#===========================


# Oracle DB Parameters
#---------------------------
ORACLE_HOME=/oracle/client/10g
export ORACLE_HOME

TNS_ADMIN=/oracle/client/10g/network/admin
export TNS_ADMIN

PATH=$ORACLE_HOME/bin:$PATH
export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
#===========================

Install JAVA on target server

In this case I have decided to install latest java SE 7u2 version. However SE 6 is far enough,
  1. Download jdk-7-linux-i586.rpm from page
  2. cd [download directory]
  3. rpm -ivh jdk-7-linux-i586.rpm
  4. cd /etc/alternatives/java
  5. alternatives --config java
  6. alternatives --install /usr/bin/java java /usr/java/jdk1.7.0_02 3
  7. alternatives --config java
Here is some output after:
[root@app-srv ~]# alternatives --config java

There are 3 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
   1           /usr/lib/jvm/jre-1.4.2-gcj/bin/java
*  2           /usr/lib/jvm/jre-1.6.0-sun/bin/java
 + 3           /usr/java/jdk1.7.0_02/bin/java

Enter to keep the current selection[+], or type selection number: 3
[root@app-srv ~]# java -version
java version "1.7.0_02"
Java(TM) SE Runtime Environment (build 1.7.0_02-b13)
Java HotSpot(TM) Server VM (build 22.0-b10, mixed mode)
[root@app-srv ~]#

Install OBI EE on target server

as root:
mkdir -p $OBI_HOME
chown -R oracle:dba $OBI_HOME
chmod -R 775 $OBI_DATA_HOME
mkdir -p $OBI_DATA_HOME
chown -R oracle:dba $OBI_DATA_HOME
chmod -R 775 $OBI_DATA_HOME
as root
Make sure that /dev/random and /dev/urandom are available
[root@oel11g ~]# cd /dev
[root@oel11g dev]# ls *random
random  urandom
as root
chmod -R 777 $JAVA_HOME
as oracle
change directory to the installation directory.
./setup.sh -console
as root
chmod -R 755 $JAVA_HOME
Change mem preferences:
vi  $OBI_HOME/oc4j_bi/bin/oc4j 
and change -Xmx512m to something more "serious". In mine case 756M was quite enough.
JVMARGS="-Djava.library.path=/usr/local/OracleBI/server/Bin:/usr/local/OracleBI/web/bin -DSAROOTDIR=/usr/local/OracleBI -DSADATADIR=/usr/local/OracleBIData -XX:MaxPermSize=128m -Xmx512m $OC4J_JVM_ARGS"
else
JVMARGS="-Djava.library.path=/usr/local/OracleBI/server/Bin:/usr/local/OracleBI/web/bin -DSAROOTDIR=/usr/local/OracleBI -DSADATADIR=/usr/local/OracleBIData -XX:MaxPermSize=128m -Xmx756m"

Install Oracle client

OBIEE must have 10g client installed. In this case Administrator type of installation is the best option.
as oracle
install oracle client (full)
./runInstaller -ignoreSysPrereqs
Bounce OBI services. This how correct services should look like (this doesn't mean that OBI is running correctly):
[oracle@app-srv ~]$ ps -ef|grep [j]ava
oracle   29400 29398  0 21:30 pts/1    00:00:12 /usr/java/jdk1.7.0_02/bin/java -Djava.library.path=/oracle/product/obi10g/OracleBI/server/Bin:/oracle/product/obi10g/OracleBI/web/bin -DSAROOTDIR=/oracle/product/obi10g/OracleBI -DSADATADIR=/oracle/product/obi10g/OracleBIData -XX:MaxPermSize=128m -Xmx1024m -jar /oracle/product/obi10g/OracleBI/oc4j_bi/j2ee/home/oc4j.jar -config /oracle/product/obi10g/OracleBI/oc4j_bi/j2ee/home/config/server.xml -userThreads
oracle   29590     1  0 21:32 pts/1    00:00:02 /usr/java/jdk1.7.0_02/bin/java -classpath /oracle/product/obi10g/OracleBI/web/javahost/lib/core/sautils.jar:/oracle/product/obi10g/OracleBI/web/javahost/lib/core/sawconnect.jar:/oracle/product/obi10g/OracleBI/web/javahost/lib/core/javahost.jar:/oracle/product/obi10g/OracleBI/web/javahost/lib/core/xalan-2.4.1.jar:/oracle/product/obi10g/OracleBI/web/javahost/lib/core/xercesImpl-2.6.2.jar:/oracle/product/obi10g/OracleBI/web/javahost/lib/core/xml-apis-2.6.2.jar -Xms128M -Xmx256M -Djava.awt.headless=true -Djava.util.logging.config.file=/oracle/product/obi10g/OracleBI/web/javahost/config/logconfig.txt -Doracle.bi.presentation.coreconfigdir=/oracle/product/obi10g/OracleBI/web/config -Doracle.bi.presentation.dataconfigdir=/oracle/product/obi10g/OracleBIData/web/config -Doracle.bi.rootdir=/oracle/product/obi10g/OracleBI -Doracle.bi.presentation.rootdir=/oracle/product/obi10g/OracleBI/web -Doracle.bi.tempdir=/oracle/product/obi10g/OracleBIData/tmp -Doracle.bi.javahostdir=/oracle/product/obi10g/OracleBI/web/javahost -Doracle.bi.presentation.cordaroot=/oracle/product/obi10g/OracleBI/corda50 com.siebel.analytics.javahost.standalone.Main /Config /oracle/product/obi10g/OracleBI/web/javahost/config/config.xml
[oracle@app-srv ~]$

Install OBI EE client side on Windows host

Because in OBIEE 10g there is no Admin Tool GUI part, this has to be installed on any Windows box. If you are upgrading, open .rpd (AZM.rpd) file with new Admin Tool and just save it under same name. Open and save is the only thing you need to do!

Transfer new rpd to $OBI_HOME/server/Repository directory on target server.

Fix some bugs in new OBI EE installation

There are several known bugs which must be fixed before continue installation. If you try to login to Answers through browser:
http://app-srv:9704/analytics/saw.dll?Answers
You'll get message "Unable to login"
This is because if you look in NQServer.log you'll find a cause:
tail /oracle/product/obi10g/OracleBI/server/Log/NQServer.log
 [nQSError: 47007] Invalid repository file: /oracle/product/obi10g/OracleBI/server/Repository/samplesales.rpd.
On Linux there is no preinstalled repository file (.rpd) (on Windows there is!).
[oracle@app-srv ~]$ ls -la $OBI_HOME/server/Repository
total 8
drwxr-x---  2 oracle dba 4096 Mar 17 20:55 .
drwxr-x--- 12 oracle dba 4096 Mar 17 20:55 ..
[oracle@app-srv ~]$
Next is to make some changes in $OBI_HOME/server/Config/NQSConfig.INI. Before that make a bakups of all files here for your safety.
vi $OBI_HOME/server/Config/NQSConfig.INI
[ REPOSITORY ]
Star    =       samplesales.rpd, DEFAULT;
[ REPOSITORY ]
Star    =       AZM.rpd, DEFAULT;
I reccomend to make some aditional changes in this file as well (if you are using racle OCI connection, file cache is uselles and slower then Oracle DB cache):
[ CACHE ]
# Vadas 01.07.2011 ENABLE = YES;
ENABLE = NO;
# Vadas 01.07.2011 DATA_STORAGE_PATHS = "/usr/local/OracleBIData/cache" 500 MB;
DATA_STORAGE_PATHS = "/usr/local/OracleBIData/cache" 1 MB;
# Vadas 01.07.2011 MAX_ROWS_PER_CACHE_ENTRY = 100000;  // 0 is unlimited size
MAX_ROWS_PER_CACHE_ENTRY = 1;  // 0 is unlimited size
# Vadas 01.07.2011 MAX_CACHE_ENTRIES = 1000
MAX_CACHE_ENTRIES = 1;
And one that is for mine country:
[ GENERAL ]
# Vadas SORT_ORDER_LOCALE = "English-usa";
SORT_ORDER_LOCALE = "Croatian";
Beside all, probably another error is shown in:
[oracle@app-srv Config]$ tail $OBI_DATA_HOME/web/log/sawserver.out.log
        saw.subsystem.security.checkAuthenticationImpl
        saw.threadPool
        saw.threads

Odbc driver returned an error (SQLDriverConnectW).
State: HY000.  Code: 10058.  [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 12008] Unable to connect to port 9703 on machine LOCALHOST.
[nQSError: 12010] Communication error connecting to remote end point: address = LOCALHOST; port = 9703.
[nQSError: 12002] Socket communication error at call=: (Number=-1) Unknown error 4294967295 (HY000)
---------------------------------------
[oracle@app-srv Config]$
and is connected to previously shown problem with missing rpd.

Now it is time to unpack all files from initially backuped catalog directory. In mine case that was under samplesales directory:
cd $OBI_DATA_HOME/web/catalog/samplesales
unzip samplesales.zip

bounce OBI services

If you setup all in .bashprofile (like in mine case) then you do not need edit $OBI_HOME/setup/user.sh file. Otherwise do so as stated there

If you login to Dashboard through WEB and get error like:
500 Internal Error: - servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details. 
Then problem is with port, which is fixed as:
1. vi $OBI_HOME/oc4j_bi/j2ee/home/applications/analytics/analytics/WEB-INF/web.xml
2. Replace the port as 9703 instead of 9710
3. run next statements
run-sa.sh stop 
wait for 10 seconds 
run-saw.sh stop 
wait for 10 seconds 
run-sa.sh start 
run-saw.sh start. 
Now click on the application Dashboard. It will work fine.

If you have upgrade oc4j then do not forget to change password after first start.
[oracle@app-srv ~]$ oc4j -start
Starting OC4J from /usr/local/OracleBI/oc4j_bi/j2ee/home ...
Mar 16, 2012 9:28:37 PM com.evermind.server.XMLDataSourcesConfig parseRootNode
INFO: Legacy datasource detected...attempting to convert to new syntax.
12/03/16 21:28:37 Set OC4J administrator's password (password text will not be displayed as it is entered)
Enter password:
Confirm password:
The password for OC4J administrator "oc4jadmin" has been set.
12/03/16 21:29:03 The OC4J administrator "oc4jadmin" account is activated.
Before making any change to next file do make a backup.
cd $OBI_DATA_HOME/web/config/
mkdir originals
cp -p *.* originals
vi $OBI_DATA_HOME/web/config/instanceconfig.xml 


   AnalyticsWeb


  
    1500
    2500
    5000000
    3000
  
300000
2000000
   AnalyticsWeb

Setup BI Publisher

When you start to setup BI publisher settings you might find next bugs (errors).

Missing Admin tab in Publisher

On first use in Publisher WEB page you'll find that Admnin tab is missing.
vi $OBI_HOME/xmlp/XMLP/Admin/Configuration/xmlp-server-config.xml


change to

Superuser setting

Alse check whether you have SUPERUSER_PASSWORD and SUPERUSER_USERNAME set (Administrator in default case).


Check for Administrator role

Check whether you the Administrator Role mapping is enabled there.
vi $OBI_HOME/xmlp/XMLP/Admin/Security/principals.xml


In XML Publisher (http://app-srv:9704/xmlpserver/servlet/report), under Admin tab make changes that reflect your environment:

For OCI database connection DWH check existence in $TNS_NAMES/tnsnames.ora file.

Admin>JDBC-add proper connection

Admin>Server Configuration

  • System Temporary Director->/var/tmp
  • Output Formats->Check all except MHTML

Admin>Runtime Configuration

  • Enable multithreading->true
  • Thread count-> nr of cores (4)
  • Enable scalable feature of XSLT processor->True
  • Enable XSLT runtime optimization->True
  • CSV delimiter->TAB(\t)

Add additional fonts

If you wish to install own fonts, place them (as root) in
cp -p arial*.ttf $JAVA_HOME/jre/lib/fonts
cd $JAVA_HOME/jre/lib/fonts
chown oracle:dba arial*.ttf
chmod 755  arial*.ttf
Admin>Font Mappings interface
Under RTF Templates set 4 times "Arial" for each font

Bounce OBI services and test some Answers or ready made BI Publisher report.

Create OBI start/stop script (boot start) on target server

Last, create start/stop/statu script which will run on boot as well as on demand. here is source:
#!/bin/sh 
#
# File   : obiee
# Purpose: Start and stop Oracle Business Intelligence 10g components
#          on CentOS 5.8 x86
#
# Author : Damir Vadas, TEB Informatika 2012
#               
#          cp -p obiee /etc/init.d
#          chmod +x /etc/init.d/obiee
#          ln -s /etc/init.d/obiee /etc/rc.d/rc5.d/S50obiee
#          ln -s /etc/init.d/obiee /etc/rc.d/rc5.d/K50obiee
#          ln -s /etc/init.d/obiee /home/oracle/obiee 
#        

if [ "$USER" != "oracle" ]; then
  OBI_HOME=/oracle/product/obi10g/OracleBI
  OBI_DATA_HOME=/oracle/product/obi10g/OracleBIData
fi

case "$1" in 
start) 
echo "Starting Oracle BI services in 3 steps ..." 
echo `date`
echo "Pokrecem oc4j (1/3) ... Trajanje 20-ak sek"
if [ "$USER" = "oracle" ]; then 
  oc4j -start > /dev/null 2>&1 &
else
  su - oracle -c "oc4j -start > /dev/null 2>&1 &"
fi
sleep 15
echo "--------------------------------------------------"
echo "Pokrecem nqsserver server (2/3) ... Trajanje cca 1-1.5 minuta."
if [ "$USER" = "oracle" ]; then 
  run-sa.sh start 
else
  su - oracle -c "run-sa.sh start" 
fi
sleep 15
echo "--------------------------------------------------"
echo "Pokrecem saw server (3/3) ... Trajanje cca 1 minuta."
if [ "$USER" = "oracle" ]; then 
  run-saw.sh start
else
  su - oracle -c "run-saw.sh start"
fi
sleep 15
echo "--------------------------------------------------"
echo `date`
;; 
stop)
echo `date`
echo "--------------------------------------------------"
echo "Spustam saw server (1/3)"
if [ "$USER" = "oracle" ]; then 
  run-saw.sh stop
else
  su - oracle -c "run-saw.sh stop"
fi
tail $OBI_DATA_HOME/web/log/javahost.out.log
sleep 5
echo "--------------------------------------------------"
echo "Spustam nqsserver server (2/3)"
if [ "$USER" = "oracle" ]; then 
  run-sa.sh stop
else
  su - oracle -c "run-sa.sh stop"
fi
sleep 15
echo "--------------------------------------------------"
echo "Spustam oc4j (3/3)"
if [ "$USER" = "oracle" ]; then 
  oc4j -shutdown -port 23971 -password oc4jadmin
else
  su - oracle -c "oc4j -shutdown -port 23971 -password oc4jadmin"
fi
sleep 3
kill -9 `ps auwwwx | grep [o]c4j | awk '{print $2}' | xargs`
echo `date`
;;
status)
echo "----- JAVA proceses ------------------------------"
echo `ps -ef|grep [j]ava`
echo "=================================================="
echo " "
echo "------ NQSERVER ----------------------------------"
ps -ef|grep [n]qsserver
tail -n 5 $OBI_HOME/server/Log/NQServer.log
echo "=================================================="
echo " "
echo "------ SAW SERVER --------------------------------"
ps -ef|grep [s]awserver
tail -n 5 $OBI_DATA_HOME/web/log/sawserver.out.log
echo "=================================================="
echo " "
;;
*) echo "Usage: $0 start|stop|status" 
exit 1 
;; 
esac 
exit 0 

The end

Hope this helps someone as me once...

Cheers!

Monday, March 12, 2012

Mixed authentication in Apex



Some time ago, I have described how to authenticate from Oracle Forms (or any other app) to Apex without prompting for username and password. In this scenario I was using one table for users (xe_users) which was holding all users that are allowed to login to Apex application. All about this solution can be read in one of mine previous postings on Apex theme, HROUG 2010.

Today post will cover a little different approach that come recently on mine desk. A task was to enable mixed authentication for Oracle platform. Task demands may be shortly described as: User need to authenticate in Apex app either with Oracle credentials or one associated with real Apex app.
  1. First problem is that we do not know Oracle real user credentials and do not want to save permanently any of those values. So we need to check login from Apex session and for that we need another session.
  2. Second, if user doesn't exists as real Oracle user, placed credentials has to be checked against Apex classic authentication and if they are OK, user may pass login.

The Solution

So, solution may be explained in next simplified steps:
  1. Create interface that will check Oracle credentials
  2. Create interface that will perform mixed authentication checking (against wished rules)
All should fit in existing custom authentication Apex model (mentioned before). And in mine case all objects are set in one schema (TOOLS) to facilitate security policy and maintenance.

Java part

To be able to create and run another session from current session in PL/SQL, probably the best way in Oracle is java. This is true because java is officially supported by default in any Oracle database after 9i. For that I have (with great help of mine colleague Goran Hafner) created java source directly in Oracle database. Here is the code:
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "CheckConnection" AS
/* $Header: CheckConnection.java 1.1 03/06/2012 11:10 damirv $ */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/* ------------------------------------------------------------------------------ 
* Filename: CheckConnection.java
* Purpose : Methods that make new session to database and check availability to select data 
*           with passed query string
*
* Date    : 22.02.2012.
* Author  : Damir Vadas
*           with GREAT help of Mr. Goran Hafner
*
* Remarks : 
*
* Changes (DD.MM.YYYY):
*         06.03.2012. Damir Vadas
*                     Added two overload methods for RAC (non generic) connections  
* ------------------------------------------------------------------------------ */

public class CheckConnection {

  private static PreparedStatement pstmt;
  private static Connection conn;

  /* Check connectivity to other database in new session withe elemenatl SELECT based query   */
  public static int doCheckDbConnection (String p_user_name, String p_password, String p_host, String p_db_name, String p_port) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_host+":"+p_port+":"+p_db_name, p_user_name, p_password);  
      String sql = "select 1 from dual ";
      // System.out.println("Try to run query ... "+sql);
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      pstmt.close();
      try {
        conn.close();
      }
      catch (SQLException ignore) { }
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }

  /* overload method for RAC environmentor any kind of non generic connection string */
  /* example of p_conn for RAC with 4 nodes (vip1-4) all on 1521 port and service name "RAC_TAF_LB":
   *             jdbc:oracle:thin:@(DESCRIPTION = (FAILOVER=ON)
   *                                              (LOAD_BALANCE=ON)
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))
   *                                              (ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))
   *                                              (CONNECT_DATA =(SERVER = DEDICATED)
   *                                                             (SERVICE_NAME = RAC_TAF_LB)
   *                                                                            (FAILOVER_MODE=(TYPE=session) 
   *                                                                            (METHOD=basic)
   *                                                             )
   *                                              )
   *                               )
   */  
  
  public static int doCheckDbConnection (String p_user_name, String p_password, String p_conn) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_conn, p_user_name, p_password);  
      String sql = "select 1 from dual ";
      // System.out.println("Try to run query ... "+sql);
      pstmt = conn.prepareStatement(sql);
      pstmt.execute();
      pstmt.close();
      try {
        conn.close();
      }
      catch (SQLException ignore) { }
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }


  /* Check SELECT based query in own session (caller session-good for checkink uncatchable PLSQL exceptions like dblink based queries)   */
  public static int doCheckSQL (String p_sql, String p_user_name, String p_password, String p_host, String p_db_name, String p_port) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_host+":"+p_port+":"+p_db_name, p_user_name, p_password);  
      // System.out.println("Try to run query ... "+p_sql);
      pstmt = conn.prepareStatement(p_sql);
      pstmt.execute();
      pstmt.close();
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }

  /* overload method for RAC environmentor any kind of non generic connection string */
  public static int doCheckSQL (String p_sql, String p_user_name, String p_password, String p_conn) {
    // System.out.println("Try to make connection ...");
    try {
      conn = DriverManager.getConnection("jdbc:oracle:thin:@"+p_conn, p_user_name, p_password);  
      // System.out.println("Try to run query ... "+p_sql);
      pstmt = conn.prepareStatement(p_sql);
      pstmt.execute();
      pstmt.close();
      // System.out.println("OK!");
      return 1;
    }
    catch(SQLException e) {
      // System.out.println("e.getMessage(): " + e.getMessage());
      return -1;
    }
  }
}
/
Beside in java commented part there is whole description and examples, interesting part of this code is that it support classic JDBC connection (5 parameters methods) as well as RAC or specific part of connections (3 parameters methods).
First method doCheckDbConnection, is intended to create Oracle session with passed credentials and perform elemental query (to be able to make this query create session privilege is enough and this privilege is least an Oracle user might have to create connection).
  1. doCheckDbConnection (String p_user_name, String p_password, String p_host, String p_db_name, String p_port)
  2. doCheckDbConnection (String p_user_name, String p_password, String p_conn)
Other two override methods have ability to perform custom query that might not be catch in classic Oracle PLSQL code-i.e. db_link tables:


  • doCheckSQL (String p_sql, String p_user_name, String p_password, String p_host, String p_db_name, String p_port)
    1. doCheckSQL (String p_sql, String p_user_name, String p_password, String p_conn)
    All four exposed public methods return "1" for successful completion and "-1" for any kind of error. All methods are wrapped in exception handler so result must always pass mentioned values.

    Publish java class

    After recompiling class next step is to publish java class through PLSQL code. This is the way how Oracle handle java into PLSQL code. Here is the code for that step:
    CREATE OR REPLACE PACKAGE CHECKCONNECTION AS
    /* $Header: TOOLS.JAVA_PKG.pks 1.1 03/06/2011 11:30 damirv $ */
    
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : CHECKCONNECTION.pks
     PURPOSE : PLSQL published method for java class CheckConnection
    
     Date    : 22.02.2012.
     Author  : Damir Vadas
    
     Remarks : 
    
       Changes (DD.MM.YYYY, Name, CR/TR#):
    -------------------------------------------------------------------------------------------------------------------- */
    
    FUNCTION doCheckDbConnection(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2, Param5 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckDbConnection(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckDbConnection(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckDbConnection(java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2, Param5 VARCHAR2, Param6 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL(Param1 VARCHAR2, Param2 VARCHAR2, Param3 VARCHAR2, Param4 VARCHAR2) 
      return NUMBER 
    AS
      LANGUAGE java 
        NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    end;
    /
    
    This interface has only specification part. Be aware that Java is case sensitive language so it's declaration must follow this rule.

    The PLSQL wrapper

    Last step dealing with Java is to create PLSQL wrapper. This code will be called from all other PLSQL parts.
    CREATE OR REPLACE PACKAGE "JAVA_PKG" AUTHID CURRENT_USER AS
    /* $Header: TOOLS.JAVA_PKG.pks 1.1 03/06/2011 11:40 damirv $ */
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : JAVA_PKG
     PURPOSE : Handle with all kind of Java based classes wrapped in this package
    
     Date    : 22.02.2012.
     Author  : Damir Vadas
    
     Remarks : Granted to public
               Might have somne aditional privileges to run. 
               I.E. "dbms_java.grant_permission"
               
               Each user must have correct aditional privileges to query host ina way: 
    
               BEGIN
                  SYS.DBMS_JAVA.GRANT_PERMISSION(
                     grantee           => 'TOOLS'
                    ,permission_type   => 'SYS:java.net.SocketPermission'
                    ,permission_name   => 'servername1'
                    ,permission_action => 'resolve'
                     ,key               => KEYNUM
                    );
                END;
                /
               
                DECLARE
                  KEYNUM NUMBER;
                BEGIN
                  SYS.DBMS_JAVA.GRANT_PERMISSION(
                     grantee           => 'TOOLS'
                    ,permission_type   => 'SYS:java.net.SocketPermission'
                    ,permission_name   => '192.168.0.11:1521'
                    ,permission_action => 'connect,resolve'
                     ,key               => KEYNUM
                    );
                END;
                / 
    
    
       Changes (DD.MM.YYYY, Name, CR/TR#):
                29.02.2012  Damir Vadas
                            ORA-29532 , wrong Java call in plsql interface
                06.03.2012  Damir Vadas
                            Added two methods for RAC or non standard connection strings
    -------------------------------------------------------------------------------------------------------------------- */
    
    FUNCTION doCheckDbConnection (p_user_name IN VARCHAR2,
                                  p_password  IN VARCHAR2,
                                  p_host      IN VARCHAR2,
                                  p_db_name   IN VARCHAR2,
                                  p_port      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckDbConnection"         
    -- For each connection (different server or port, next two statements has to be run as sys. 
    -- Example for teb-ref server and port 1521 :
    -- exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.net.SocketPermission','teb-ref', 'resolve' );
    -- exec dbms_java.grant_permission( 'TOOLS', 'SYS:java.net.SocketPermission', '192.168.168.109:1521', 'connect,resolve' );
    
    --SQL> select java_pkg.doCheckDbConnection ('USER1','user1','teb-ref','edis','1521') from dual;
    --
    --XE_AUTH_MIX_PKG.DOCHECKDBCONN
    --------------------------------
    --                             1
    --
    --SQL>
    
    FUNCTION doCheckDbConnection (p_user_name IN VARCHAR2,
                                  p_password  IN VARCHAR2,
                                  p_conn      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckDbConnection" for non standard or RAC connection
    -- Example for 4 nodes (vip 1-4) all on 1521 port, with RAC_TAF_LB oracle service        
    -- SQL> select java_pkg.doCheckDbConnection ('USER1','user1','(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))') from dual;
    --
    -- JAVA_PKG.DOCHECKDBCONNECTION('USER1','user1','(DESCRIPTION=(FAILOVER=ON)(LO
    ----------------------------------------------------------------------------------
    --                                                                               1
    --
    -- SQL>
    --------------------------------------------------------------------------------------------------------------------------
    FUNCTION doCheckSQL (p_sql       IN VARCHAR2,
                         p_user_name IN VARCHAR2,
                         p_password  IN VARCHAR2,
                         p_host      IN VARCHAR2,
                         p_db_name   IN VARCHAR2,
                         p_port      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckSQL"
    -- Good for checking DB links which cannot be trapped in PLSQL exception part!
    
    --SQL> select java_pkg.doCheckSQL ('select 1 from dual@geaprod','USER1','user1','teb-ref','edis','1521') from dual;
    --
    --JAVA_PKG.DOCHECKSQL('SELECT1
    ------------------------------
    --                           1
    --
    --SQL> select java_pkg.doCheckSQL ('select 1 from dual@hact','USER1','user1','teb-ref','edis','1521') from dual;
    --
    --JAVA_PKG.DOCHECKSQL('SELECT1
    ------------------------------
    --                          -1
    --
    --SQL> 
    
    FUNCTION doCheckSQL (p_sql       IN VARCHAR2,
                         p_user_name IN VARCHAR2,
                         p_password  IN VARCHAR2,
                         p_conn      IN VARCHAR2
             ) RETURN NUMBER;
    -- wrapper for "CheckConnection.doCheckSQL" for non standard or RAC connection
    -- example is similar like  previous overload "doCheckDbConnection(p_user_name, p_password,p_conn)" 
    
    END JAVA_PKG;
    /
    
    Be aware that:
    • This package has AUTHID Clause, what bring us to ability to grant it to PUBLIC, without compromising any security
    • To be able to run this package, additional SYS.DBMS_JAVA.GRANT_PERMISSION has to be granted to invoker.
    PAckage body is very easy-it just run previously defined published PLSQL package.
    CREATE OR REPLACE PACKAGE BODY "JAVA_PKG" 
    AS
    FUNCTION doCheckDbConnection (
                                  p_user_name   IN VARCHAR2,
                                  p_password    IN VARCHAR2,
                                  p_host        IN VARCHAR2,
                                  p_db_name     IN VARCHAR2,
                                  p_port        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckDbConnection (java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckDbConnection (
                                  p_user_name   IN VARCHAR2,
                                  p_password    IN VARCHAR2,
                                  p_conn        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckDbConnection (java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL (
                         p_sql         IN VARCHAR2,
                         p_user_name   IN VARCHAR2,
                         p_password    IN VARCHAR2,
                         p_host        IN VARCHAR2,
                         p_db_name     IN VARCHAR2,
                         p_port        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
    
    FUNCTION doCheckSQL (
                         p_sql         IN VARCHAR2,
                         p_user_name   IN VARCHAR2,
                         p_password    IN VARCHAR2,
                         p_conn        IN VARCHAR2
            ) RETURN NUMBER
    AS
      LANGUAGE JAVA
      NAME 'CheckConnection.doCheckSQL(java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';
      
    END JAVA_PKG;
    /
    

    The XE_AUTH_MIX_PKG package

    In mentioned previous example of automated Apex authentication, I was using XE_AUTH_PKG, so now this code should be placed in additional XE_AUTH_MIX_PKG package. It's intention is to perform mixed authentication:
    CREATE OR REPLACE PACKAGE "XE_AUTH_MIX_PKG" AS
    /* $Header: xe_auth_pkg.pks 1.1 03/06/2012 11:40 damirv $ */
    /*--------------------------------------------------------------------------------------------------------------------
     NAME    : xe_auth_mix_pkg
     PURPOSE : Autentification module against XE_USERS table with some kind of DB authentification-mix of both!
    
     Date    : 21.02.2012.
     Author  : Damir Vadas
    
     Remarks : This package use XE_AUTH_PKG and all XE_* based objects and their intention is to make extension for 
               core xe_auth authentication ..
               Internaly uses java source TOOLS."CheckConnection" which is heavily used in this pkg
    
               Use JAVA_PKG to check db connectivity and credentials.
    
     Changes (DD.MM.YYYY, Name, CR/TR#):
              06.03.2012, Damir Vadas
                          Added two overload functions that deal with non standard connection string (RAC etc)
    -------------------------------------------------------------------------------------------------------------------- */
    
    -- Very dangerous in real WEB because each check_password open new DB connection to retrieve correct credentials
    -- Function return:
    --           0 if user is not in XE_USERS table
    --           1 if authentication is OK (rather DB side or against xe_users auth)
    --          -1 if authentication failed (rather DB side or against xe_users auth)
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER;
    -- set serveroutput on size 123456;
    -- 
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int := XE_AUTH_MIX_PKG.check_password(p_ID=>1, p_PASSWORD=>'user1',p_HOST=>'teb-ref',p_DB_NAME=>'edis',p_PORT=>'1521');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER;
    -- set serveroutput on size 123456;
    -- 
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int := XE_AUTH_MIX_PKG.check_password(p_ID=>1, p_PASSWORD=>'user1',p_CONN=>'(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER 
             ) RETURN PLS_INTEGER;
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int :=   xe_auth_pkg.check_password(p_USERNAME=>'USER!',p_PASSWORD=>'user1',p_HOST=>'teb-ref',p_DB_NAME=>'edis',p_PORT=>'1521');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER;
    -- declare
    --   l_int PLS_INTEGER;
    -- begin
    --   xe_global_pkg.init_app_global(1);
    --   l_int :=   xe_auth_pkg.check_password(p_USERNAME=>'USER!',p_PASSWORD=>'user1',p_CONN=>'(DESCRIPTION = (FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip4)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = RAC_TAF_LB)(FAILOVER_MODE=(TYPE=session) (METHOD=basic))))');
    --   dbms_output.put_line('rezultat provjere korisnika='||l_int);
    -- end;
    -- /
     
    END XE_AUTH_MIX_PKG;
    /
    
    As you see in comment part there are out of the box examples of using this PLSQL methods so I will not explain them separately.
    And here is body part of that package:
    CREATE OR REPLACE PACKAGE BODY "XE_AUTH_MIX_PKG" AS
    FUNCTION check_password_core ( p_ID       IN XE_USERS.ID%TYPE
                                  ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                                  ,p_CONN     IN VARCHAR2
                                  ,p_HOST     IN VARCHAR2
                                  ,p_DB_NAME  IN VARCHAR2
                                  ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
      l_username      XE_USERS.USERNAME%TYPE;
      l_salt          XE_USERS.SALT%TYPE;
      l_hash_password XE_USERS.PASSWORD%TYPE;
      l_password      XE_USERS.PASSWORD%TYPE;
      l_temp          PLS_INTEGER;
    BEGIN
      -- get basic data (must be present in XE_UERS table)
      SELECT upper(USERNAME)  , PASSWORD  ,SALT
        INTO l_username, l_password,l_salt
        FROM XE_USERS
       WHERE ID=p_ID;
      dbms_output.put_line('l_username='||l_username);
      dbms_output.put_line('l_password='||l_password);
      dbms_output.put_line('l_salt='||l_salt);
      
      -- check db credentials for that username and sent p_password
      SELECT COUNT(*)
        INTO l_temp 
        FROM dba_users
       WHERE username=l_username;
      IF l_temp=0 THEN
        -- non db user -must have credentials in XE_USERS tabale!  
        l_hash_password := XE_CRYPTO_PKG.HASH_PASSWORD(l_username,p_password,l_salt);
        dbms_output.put_line('l_hash_password='||l_hash_password);
        IF l_hash_password=l_password THEN
          RETURN common_pkg.OK;
        ELSE
          RETURN common_pkg.not_ok;
        END IF;
      ELSE
        IF p_CONN is null THEN
          return JAVA_PKG.doCheckDbConnection (l_username,p_PASSWORD,p_HOST,p_DB_NAME,p_PORT);
        ELSE
          return JAVA_PKG.doCheckDbConnection (l_username,p_PASSWORD,p_CONN);
        END IF; 
      END IF;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN common_pkg.NOTHING_TO_DO;
      WHEN common_pkg.busy_resource THEN
        RETURN common_pkg.BUSY_RES;
      WHEN OTHERS THEN
        l_temp := auto_log_error_xe_auth_f ('fnc "XE_AUTH_MIX_PKG.check_password_core", when others p_ID:"'||p_ID||'", l_username:"'||l_username||'", p_PASSWORD:"'||p_PASSWORD||'", p_DB_NAME:"'||p_DB_NAME||'", '||p_PASSWORD||'", p_PORT:"'||p_PORT||'", p_CONN:"'||p_CONN||'"');
        RETURN l_temp;
    END check_password_core;
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      return check_password_core (p_ID=>p_ID, P_PASSWORD=>P_PASSWORD,p_CONN=>null, p_HOST=>p_HOST, p_DB_NAME=>p_DB_NAME, p_PORT=>p_PORT);
    END check_password;
    
    
    FUNCTION check_password ( p_ID       IN XE_USERS.ID%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      return check_password_core (p_ID=>p_ID, P_PASSWORD=>P_PASSWORD,p_CONN=>p_CONN,p_HOST=>null, p_DB_NAME=>null, p_PORT=>null);
    END check_password;
    
    -- **********************************************************************************************************
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_HOST     IN VARCHAR2
                             ,p_DB_NAME  IN VARCHAR2
                             ,p_PORT     IN NUMBER
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      RETURN check_password_core (p_ID=>XE_AUTH_PKG.get_id_from_username(upper(p_USERNAME)), p_PASSWORD=>p_PASSWORD, p_CONN=>null, p_HOST=>p_HOST, p_DB_NAME=>p_DB_NAME, p_PORT=>p_PORT);
    END check_password;
    
    FUNCTION check_password ( p_USERNAME IN XE_USERS.USERNAME%TYPE
                             ,p_PASSWORD IN XE_USERS.PASSWORD%TYPE
                             ,p_CONN     IN VARCHAR2
             ) RETURN PLS_INTEGER
    IS
    BEGIN
      RETURN check_password_core (p_ID=>XE_AUTH_PKG.get_id_from_username(upper(p_USERNAME)), P_PASSWORD=>P_PASSWORD,P_CONN=>P_CONN, p_HOST=>null, p_DB_NAME=>null, p_PORT=>null);
    END check_password;
    END XE_AUTH_MIX_PKG;
    /
    
    Similarly like in Java part, there are overload function which are used in the same situations like those mentioned there ... based on JDBC connection type.

    The End

    Hope this will help someone.

    Cheers!
  • Zagreb u srcu!

    Copyright © 2009-2014 Damir Vadas

    All rights reserved.


    Sign by Danasoft - Get Your Sign