Search This Blog

Saturday, 2 January 2010

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.

Wednesday, 30 December 2009

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.

Wednesday, 25 November 2009

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.

Wednesday, 28 October 2009

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.

Sunday, 27 September 2009

Passed Oracle 11i APPS DBA OCP Exam

I wanted to share a good news with you all, that I passed the Oracle 11i APPS DBA OCP Exam.

The exam was easy, but it took lot of time to prepare the exam, as there were 30 chapters in the course contents.

I have preapred some tips for myself, I will post them soon.

have a nice day.

Monday, 7 September 2009

kcrrwkx: nothing to do (end)

Introduciton:
We got the following error message in one of the trace file in our background dump dest of Oracle 10g R2 (10.2.0.1) database.

kcrrwkx: nothing to do (end)

Troubleshooting and Resolution:
On searching of different forums and metalink, we found the note number: 372364.1, and according to the note, the message is safely ignored, and the only impact would be the size of the trace file, which needs to be purged manually or with cron job. The fix of this bug is in Oracle 11g or Oralce R2 10.2.0.3.

Thursday, 27 August 2009

WARNING! Recovering data file 1 from a fuzzy file. If not the current file

Introduction:

We use EMC BCV feature to clone our production database, and it was all working fine, but suddenly we started getting the errors , on recovery process. Following were the errors..

"WARNING! Recovering data file 1 from a fuzzy file. If not the current fileit might be an online backup taken without entering the begin backup command."

Troubleshooting and Resolution:

It was very interesting situation, as the same process which we were following was not working suddenly, our team tried the same procedure twice and every time they got the errors on recovery process. And we were following the below BCV cloning procedure.

Steps for cloning by use of BCV:

1- put the production database in backup mode.

2- sync the BCV volumes.

3- split the BCV volumes.

4- take the production database out of backup mode.

5- start the recovery on the cloned database.

6- open the database after successfull recovery.


We tried twice with the same proceudre but we were getting following errors..
---------------------------------------------
ALTER DATABASE RECOVER database using backup controlfile until cancel
Wed Aug 6 13:03:00 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
---------------------------------------------

and we opend the cases with Oracle and EMC, but the error was showing some thing not correct with some of the procedure, either the production database didnt go properly in the backup mode, but when we checked the alert log, the database went to backup mode correctly, and it came out of the backup mode properly. Having seen the alert log, we suspected that something went wrong with BCV etc. But we were not sure, as we didnt get any error messages during the BCV sync and split.

Then we decided to give a one more try with the same procedure, and when we checked the team, they were not following the listed procedure, as they were doing split of the BCV after taking the database out of backup mode, whereas they should have done the split before the database taken out of backup mode. When we followed the same listed procedure, every thing was ok, and we were able to do successfull recovery of the database and able to open the database. :)








manage long running sessions with "screen" on linux OS - handy utility for Oracle APPS DBA for cloning, patching etc

  n Oracle E-Business Suite (EBS), there is no standard   -S   flag for the primary cloning commands ( adpreclone.pl   or   adcfgclone.pl )....