Oracle E-Business R12 form and plsql library manual compilation

Introduction:

In this post we will see how to compile the form and pl sql library in Oracle E-Business R12 manually.

Cookbook:

Identify the requirement, i.e. which form or pl sql library needs to compiled, following are the two examples.

a- to compile the form manually

/appstest/apps/oraapp/tech_st/10.1.2/bin/frmcmp_batch module=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/forms/US/APXIISIM.fmb userid=APPS/***** output_file=/appstest/apps/oraapp/apps_st/appl/ap/12.0.0/forms/US/APXIISIM.fmx module_type=form batch=yes compile_all=special

b- to compile the pl sql library

/appstest/apps/oraapp/tech_st/10.1.2/bin/frmcmp_batch module=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/resource/POXBWVRP.pll userid=APPS/**** output_file=/appstest/apps/oraapp/apps_st/appl/au/12.0.0/resource/POXBWVRP.plx module_type=library batch=yes compile_all=special

Test Cases for Oracle E-Bussines R12 project

Introduction:
This article is related to the test cases for Oracle E-Business R12 project with respect to infrastructure i.e. Database tier and application tier.

Cookbook:
After completion of the Oracle E-Business R12 project, the environment needs to be handed over to the oniste engineers. The following document is a kind of template to do the test cases to make it sure that the infrastructure is working properly as agreed, the test features and tests can be changed with respect to each site and concerns.

Oracle 9i Application Server R2 X-Server change

Introduction:
For Oracle 9i Application Server R2, there is requirement for the X Server, reports needs to have the X Server to run successfully, in this article we will see how to change the X Server from one machine to other machine.

Cookbook:
The X Server can easily be changed for the reports server, also there are many X Server available, the one is VNC X Server.

For Oracle 9i Application Server R2, there are 2 configuration files which needs to be altered if the X Server loaction has changed.

Follwing are the 2 files.
- reports.sh
- oc4j.properties

The location for the reports.sh is $ORACLE_HOME/bin/ and for the oc4.properties is $ORACLE_HOME/j2ee/OC4J_BI_Forms/config/.

Once the above files have been altered, then reports services needs to be restarted either from the Enterprise Manager or from the command line.

From the command line, we can start the reports servecis as follows..

$ORACLE_HOME/bin/rwserver.sh server=rep_machinename

Oracle APPS 12 (12.1.1) installation on Oracle Enterprise Linux R4 ( update 7) on Vmware server

Introduction:
This article is related to the installation of Oracle APPS 12 (12.1.1) on Oracle Enterprise Linux R4 on Vmware Serv er.

Cookbook:
We used the following software.

1- Oracle APPS 12 (12.1.1)
this can be downloaded from http://edelivery.oracle.com
2- Oracle Enterprise Linux R4 (update 7)
3- Vmware Server

Following are the steps which we followed. Also this article is not related to the installation of Vmware Server and Oracle Enterprise Linux R4. We will concentrate only on APPS 12 installation.

1- Following rpm(s) needs to be installed on Oracle Enterprise Linux R4, we installed only the bold ones, as only those were missing for this release.

glibc-2.3.4-2.25
glibc-common-2.3.4-2.25
binutils-2.15.92.0.2-21
compat-libstdc++-296-2.96-132.7.2
gcc-3.4.6-3
gcc-c++-3.4.6-3
libgcc-3.4.6-3
libstdc++-3.4.6-3
libstdc++-devel-3.4.6-3
openmotif21-2.1.30-11.RHEL4.6**
pdksh-5.2.14-30.3
setarch-1.6-1
make-3.80-6.EL4
gnome-libs-1.4.1.2.90-44.1
sysstat-5.0.5-11.rhel4
compat-db-4.1.25-9
control-center-2.8.0-12.rhel4.5
xscreensaver-4.18-5.rhel4.11
libaio-0.3.105-2libaio-devel-0.3.105

Following web site can be used to download any missing rpms.
http://www.filewatcher.com

2- Following kernel parameters needs to be set in /etc/sysctl.conf file.

kernel.semmsl 256*
kernel.semmns 32000*
kernel.semopm 100*
kernel.semmni 142*
kernel.shmall 2097152
kernel.shmmax Half the size of the physical memory (in bytes), and at least 2147483648
kernel.shmmni 4096
kernel.msgmax 8192
kernel.msgmnb 65535
kernel.msgmni 2878
fs.file-max 65536
net.ipv4.ip_local_port_range 10000 65000**
net.core.rmem_default 262144
net.core.rmem_max 262144
net.core.wmem_default 262144
net.core.wmem_max 262144

3- Following OS utilities should exist.
ar
gcc
g++
ld
ksh
make
X Display Server

4- we also need to create 2 OS users i.e. oracle to own oracle database and applmgr to own apps tier.

5- For this test installation we can create only one directory i.e. /d01/oracle, this would contain the db tier and apps tier.

6- After creating the stage from the APPS 12 media, we can run the rapidwiz installer and following screens show the steps to be followed. And hopefull it would go very smooth, without any problms.

This last screen shows that APPS 12 has been installed successfully, we can use the sysadmin username with the same password and can run any request to check the forms and concurrent manager.

Oracle 10g R2 Database 10.2.0.4 - Part 3 of the series Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC

Introduction:
This article is the last of the series "Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC". This last part is very easy and we will fetch the data from informix into Oracle and will see that all the configurations which was done is working properly.

Cookbook:
1- Install the Oracle 10g R2 (10.2.0.1).
2- Path Oracle 10g R2 (10.2.0.1) to 10.2.0.4. For Sun Sparc 64 bit platform, we need to down load the patch p6810189_10204_Solaris-64.zip.
3- After applying the patch, now we need to change the tnsnames.ora file of Oracle 10g R2 (10.2.0.4) database and need to add the following entry.

DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

4- we can test this tns entry by tnsping, following is the successfull test.

bash-2.05$ tnsping DG4ODBC

TNS Ping Utility for Solaris: Version 10.2.0.4.0 - Production on 30-DEC-2009 16:26:41

Copyright (c) 1997, 2007, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=oragateway_host)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (0 msec)
bash-2.05$

5- Now we can open the SQL session and fetch the data from the informix database.
SQL> select * from "emp"@dg4odbc;
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1Tyler Bennett E10297 32000 D101 1Tyler Bennett E10297 32000 D101 1John Rappl E21437 47000 D050 1George Woltman E00127 53500 D101 1Adam Smith E63535 18000 D202 0David McClellan E04242 41500 D101 1Rich Holcomb E01234 49500 D202 1Nathan Adams E41298 21900 D050 0Richard Potter E43128 15900 D101 0
first_na last_name emp_id hire_date salary dept exempt interests-------- ---------- ------ --------- ---------- ---- ---------- ----------David Motsinger E27002 19250 D202 0Tim Sampair E03033 27000 D101 1Kim Arlich E10001 57000 D190 1Timothy Grove E16398 29900 D190 1
26 rows selected.

The important point is the table name is case sensitive and should be enclosed.

Conculsion:
We have seen in these 3 articles how to fetch data from informix database into Oracle database, this was the testing, any one who wants to implement this into production needs to see the compatibility of the products.

Oracle Database Gateways 11g (11.1.0.6.0) - Part 2 of the series Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC"

Introduction:
This is the 2nd of the article of the series "Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC"
In this post we will do the installation of Oracle Database Gateways 11g for ODBC and will configure the listener so it could talk to ODBC, which we had configured in the previous article.

Also we have to patch this Oracle Database Gateways 11g 11.1.0.6.0 to 11.1.0.7. We need to download the patch 6890831.
Cookbook:
Installation of Oracle Database Gateways 11g (11.1.0.6):
Following are the images of install screen.

You need to select the option "Oralce Database Gateways for ODBC", I selected the option, but that is hidden in the following screen :)

After this installation, we need to apply the patch 6890831, and the version of Oracle Database Gateways 11g would be 11.1.0.7.0.

Configuration:

Now after the installtion and patching, we have to configure the Oracle Database Gateways 11g for ODBC, the metalink document "How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) [ID 561033.1]" can be followed to do this configuration.

Following is the configuration details.

1- Environment variables:

Following are the environment variables which was set with respect to Oracle Database Gateways 11g for ODBC.

ORACLE_HOME=/u04/oragateway/gateway

PATH=/usr/openwin/bin/xauth:/usr/bin:/usr/ucb:/etc:/usr/local/bin:/usr/X/bin:/u04/oragateway/gateway/bin:.

2- edit the file "initdg4odbc.ora" at the path $ORACLE_HOME/hs/admin, also after the change copy the file to $ORACLE_HOME

following is my initdg4odbc.ora

HS_FDS_CONNECT_INFO = Informix HS_FDS_TRACE_LEVEL = DEBUG HS_FDS_SHAREABLE_NAME = /export/home/odbc1/odbc/Connect64forODBC60/lib/libodbc.so
## ODBC specific environment variables#set ODBCINI= /export/home/odbc1/odbc/Connect64forODBC60/odbc.ini

3- configure the listener for Oracle Database Gateways 11g. Following is the configurtaion of the listener.ora at the path $ORACLE_HOME/network/admin

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = tabsmt04.mtcvb.com)(PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=/u04/oragateway/gateway)
(ENVS=LD_LIBRARY_PATH=/export/home/odbc1/odbc/Connect64forODBC60/lib:/u04/oragateway/gateway/hs/lib:/u04/oragateway/gateway/hs/lib32:/u04/oragateway/gateway/lib)
(PROGRAM=dg4odbc)
)
)

4- configure the tnsnames.ora file, following is the confiugration details.

DG4ODBC =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522))
(CONNECT_DATA=(SID=DG4ODBC))
(HS=OK)
)

5- verify the listener with the tnspin command.

bash-2.05$ tnsping dg4odbc

TNS Ping Utility for Solaris: Version 11.1.0.7.0 - Production on 25-NOV-2009 16:06:38

Copyright (c) 1997, 2008, Oracle. All rights reserved.

Used parameter files:
/u04/oragateway/gateway/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))
OK (10 msec)
Used parameter files:
/u04/oragateway/gateway/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=tabsmt04)(PORT=1522)) (CONNECT_DATA=(SID=DG4ODBC)) (HS=OK))

This shows the successfull connection, in the next article we will see to install and patch the Oracle 10g R2 database and will configure the Oracle 10g R2 database so we could fetch the data from Infomix database.

Informix to Oracle Connectivity by using the Oracle Gateway 11g for ODBC

Introduction:

In this series of articles we will see how to fetch data from Informix database into Oracle Database.

There can be 2 methods to do this, or might be more :)

The use of Oracle Gateway 11g for Informix or Oracle Gateway 11g for ODBC, the first option requires the license from Oracle Support, and the second has no license requirement. But in second option we need to have a ODBC driver for Informix which is a licensed from the vendor.

Cook Book:

We would test the fetching of data from Informix database into Oracle database by the use of the second option, i.e. we would need the Oracle Gateway 11g for ODBC and offcourse we need the ODBC driver for Informix database, which can be downloadable freely for a trial.

We need the following software for this testing.

1- ODBC driver for Informix database

2- Oracle 10g R2 (10.2.0.4) database (in which we will fetch the data from Informix database)

3- Oracle Gateway 11g for ODBC (11.0.7.0)

4- Informix database

In this article we will install the ODBC driver and connect this with the Informix database to check the connectivity with the Informix at ODBC level. This is most important step, as if it is successfull only then we can move forward to the next steps.

1- ODBC driver for Informix installation and configuration:

Installation:

The ODBC driver can be downloaded from the www.datadirect.com site for a trial free of cost. We would downlad the evsol64.tar.Z, this is for the Sun Sparc 64 bit.

We have the Sun Sparc machine for the testing, so we downloaded the ODBC driver for Informix for 64 bit Sun Sparc version.

For the ODBC installation, it is better to create a OS user "odbc" user, which will own the ODBC binaries. The installation is simple and straightforward. And it will take around 300MB - 350MB approx.

The environment variable ODBC_HOME can be set to the installation directory, and the PATH environment variable also can be set for the ODBC binaries / executables.

Configuration:

After the installation, we need to configure the ODBC driver for Informix so it could connect to the Informix and can fetch data at the ODBC level.

For the configuration we need to edit the "odbc.ini" file, this is the only file which needs to be configured for the ODBC connectivity, and if once it is configured successfully then the rest would be very easy. Always remember this is the main core file for the connectivity of Informix database to Oracle Database.

If the file is checked, we can see that there are lot of sections inside this "odbc.ini" file, and every section has its own purpose.

First section in the odbc.ini file is [ODBC data sources], and it contains all the information regarding all the databases which can be configured with this ODBC driver. e.g. Infromix, Sybase, DB2, mySQL, Teradata, PostgreSQL, SqlServer, Greenplum etc

The second section is [ODBC] contains the installation directory, trace level, and trace file directory.

The remaining sections will contain the required information to connect to respective databases, and for this article we are concerned with the section [Informix].

We need to change the parameters related to the Informix server. Following parameters needs to be modified.

1- Database - The name of Informix database to connect

2- HostName - The machine name which hosts the Informix database

3- LogonID - Informix database userid

4- Password - Password for the Informix database userid

5- PortNumber - Informix database portnumber

6- ServerName - This can be found out from ONFONGI file and there is parameter DBSERVERNAME

After setting these parameters in the odbc.ini file, we need to test the configuration, whether it connects to the Informix database and fetches the records at ODBC level.

Testing the configuration:

For a testing it is better to create a test user on the Informix database. Once user created on Informix database, we need to create a test table and insert some records into the test table. In the ODBC installation directory there is "demo" directory, there are sql scripts for each database, we can use the script "empinformix.sql" script to create a test table. We used Sql Razor tool to connect to the Informix database with the test user and created the table emp and isnerted some records into this table.

The utiltiy "demoodbc" can be used to test the ODBC connectivity with Informix database.

the syntax of "demoodbc" is

$ demoodbc -uid username -pwd password Informix Database Source name (from the file odbc.ini)

We created the user as "test" on Informix database and password is abc12345 and the data source name is "Informix" in the odbc.ini file, now we can test the ODBC connection as follows...

$ demodbc -uid test -pwd abc12345 Informix

for this execution we needs to be in the directory "demo" inside the ODBC installation directory.

on the execution of the above statement we got the following error messages....

==========================

bash-2.05$ demoodbc -uid test -pwd abc12345 Informix

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -329

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]Database not found or no system permission.

SQLConnect: Retrying Connect.

No connection could be established.

==========================

having seen the error message "carefully" we found that there is issue with the database name, when we checked the [Informix] section of the odbc.ini file, we found that the value for parameter "Database" is incorrect, on correction and re-execution of the "demoodbc" we got the following messages....

=====================

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

SQLConnect: Failed...

SQLSTATE = S1000

NATIVE ERROR = -761

MSG = [DataDirect][ODBC Informix Wire Protocol driver][Informix]INFORMIXSERVER does not match either DBSERVERNAME or DBSERVERALIASES.

SQLConnect: Retrying Connect.

No connection could be established.

============

having seen the error messages, we corrected the Servername parameter value in the odbc.ini file, we got the correct value from the ONFONFIG file from the Informix database machine. after correcting this value and again executing the "demoodbc" , it was successfull with the following messages :)

================

bash-2.05$ demoodbc -uid test -pwd abc12345 Informix

demoodbc DataDirect Technologies, Inc. ODBC Sample Application.

demoodbc: will connect to data source 'Informix' as user 'test/abcABC12!'.

First Name Last Name Hire Date Salary Dept

---------- --------- --------- ------ ----

Tyler Bennett 32000 D101

Tyler Bennett 32000 D101

John Rappl 47000 D050

George Woltman 53500 D101

Adam Smith 18000 D202

David McClellan 41500 D101

Rich Holcomb 49500 D202

Nathan Adams 21900 D050

Richard Potter 15900 D101

David Motsinger 19250 D202

Tim Sampair 27000 D101

Kim Arlich 57000 D190

Timothy Grove 29900 D190

Tyler Bennett 32000 D101

Tyler Bennett 32000 D101

John Rappl 47000 D050

George Woltman 53500 D101

SQLFetch returns: SQL_NO_DATA_FOUND

In the next article we will move forward with installation, configuration and testing of Oracle Database Gateways 11g 11.1.0.6 and patching to 11.1.0.7, and then installation of the Oracle 10g R2, patching to 10.2.0.4.