SOA

Nov 23, 2011

Converting the wallet into java key store in Oracle B2B 11g

Certificate management in 11g B2B follows a standard mechanism using Java Key store(JKS), which enables to use a common key store across the enterprise to be shared with other java based application.

Below are the steps to convert wallet into keystore.

1. Login to SOAB2B 10g server

2. Export the ewallet.p12 file in /app/oracle/product/APP/10.1.2/Apache/Apache/conf/ssl.wlt/default/ewallet.p12 into SOAB2B 11g server

3. Login to SOAB2B 11g server

4. Set JAVA_HOME if it’s not set already (export JAVA_HOME=

5. Go to the path /oracle_common/bin/orapki

6. Run the command ./orapki wallet pkcs12_to_jks -wallet /tmp/ewallet.p12 -pwd -jksKeyStoreLoc /tmp/test.jks -jksKeyStorepwd welcome1

7. Now the wallet file from B2B 10g is converted into JKS in B2B 11g and stored in /tmp location. Passowrd of your JKS is welcome1

8. Now we need to provide this file path in the Host trading partner in the B2B console

Provide Key Store Information for the Host Trading Partner

You can choose any Key Store for Oracle B2B. If you are using SSL, using the same Key Store for both B2B and Oracle WebLogic Server SSL configuration is recommended to avoid SSL-related problems when exchanging messages with trading partners.

9. Click the Partners link.

10. Click the Profile tab.

11. Select the host trading partner
12. In the Key Store section, provide a password and location, as shown below

13. Click Save.

14. This will enable the different certificate options in the trading partner delivery channel which will be disabled by default

Feb 1, 2011

Connecting to Oracle 8i database from ODI 11g

Note: Below steps are tried and configured in a Linux 64 bit environment

It’s been while I was trying to setup connecting to Oracle 8i database from ODI 11g. One of our target applications uses 8i databases even though it’s a decade old upgradeJ. I have found out that Oracle will provide support to only 2 versions backwards of its current version. So the maximum version the ODI 11g supports is Oracle 9i database. Even if we use the JDBC drivers we cannot connect it.

Below is the database compatibility matrix the Oracle currently supports.

Server Version

Client Version

11.2.0

11.1.0

10.2.0

10.1.0

9.2.0

9.0.1

8.1.7

8.1.6

8.1.5

8.0.6

8.0.5

7.3.4

11.2.0

Yes

ES #7

ES #5

No #3

11.1.0

Yes

ES #7

ES #6

ES #5

No #3

10.2.0

ES #7

ES #5

Was

No #3

10.1.0(#4)

ES #6

Was

Was #2

No #3

9.2.0

ES #5

Was

No #1

9.0.1

Was

8.1.7

Was

8.1.6

Was

8.1.5

Was

8.0.6

Was

8.0.5

Was

7.3.4

Was

Key:

Yes	Supported
ES	Supported but fixes only possible for customers with Extended Support .
Was	Was a supported combination but one of the releases is no longer covered by any of Premier Support , Primary Error Correct support , Extended Support nor Extended Maintenance Support so fixes are no longer possible.
No	Has never been Supported

Also below is the list of JDK drivers that we have tried to test the connectivity between Oracle 8i and 11g. We have placed below drivers in $ORACLE_HOME/agent/drivers path and added into additional_path.txt file before testing the connectivity. We found that it’s mandatory to have Ojdbc6.jar in the drivers’ directory of the agent for the agent to start.

JDK Version	OJDBC Driver Version	Issue
JDK 1.6	Ojdbc6.jar Ojdbc.14.jar Ojdbc5.jar Classes12.zip Classes12.jar	Invocation exception 7. ODI Agent failing to start due to older version.
JRocket 1.6	Ojdbc6.jar	Invocation exception 7.
JDK 1.4	Any version	Unable to install the agent.

Since none of the JDBC driver is compatible we planned to go with ODBC drivers.

Follow below steps to configure ODBC in Linux Environment

· Install Oracle 10.2.0.4 client – Administrator

· After installing the client copy the file $ORACLE_HOME/odbc/lib/libsqora.so.10.1 to
$ORACLE_HOME/lib

· Download ODBC Driver from below path

http://www.unixodbc.org/

· gunzip unixODBC*.tar.gz and tar xvf unixODBC*.tar

· Install the UNIXODBC driver version 2.2.12 (certified version) as follows
./configure --prefix=/u02/unixODBC-2.2.12 --exec-prefix=/u02/unixODBC-2.2.12 --enable-gui=no
CFLAGS="-DBUILD_REAL_64_BIT_MODE -DSIZEOF_LONG=8 -fshort-wchar"
Where the /u02/unixODBC-2.2.12 is the location of the unixodbc driver.
Please note that we are building the unixodbc driver using the "BUILD_REAL_64_BIT_MODE" flag which is recommended on the 64-bit platforms and used to build the ODBC 10.2.0.4 on x64bit platform.

· Set the testing environment
       export DM_HOME=/u02/unixODBC-2.2.12
       export PATH=$DM_HOME/bin:$PATH
       export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$DM_HOME/lib:$LD_LIBRARY_PATH
       export TNS_ADMIN=/usupport/etc

· Make the following changes in SQLConnect.c
You need to do some manual modification to the SQLConnect.c file to avoid errors due to setting the "BUILD_REAL_64_BIT_MODE" flag. Those changes will be sent to UNIXODBC.org to incorpeorate them in the new version of the driver.

v Go to DriverManager source i.e where you have untared the Driver Manager source
i.e $cd unixODBC-2.2.12/DriverManager
In SQLConnect.c: __connect_part_two(), initialise the local variable 'supported' to SQL_TRUE.
Here is the difference of the source.
bash-2.03$ diff SQLConnect_orig.c SQLConnect.c
1814c1814
< SQLUSMALLINT supported;
---
> SQLUSMALLINT supported = SQL_TRUE;
1842c1842
< SQLUSMALLINT supported;
---
> SQLUSMALLINT supported = SQL_TRUE;
v Rebuild the Driver Manager
$ make all
--> This command compiles the SQLConnect.c file and rebuilds the libodbc.so.1.0 in .libs
directory.
v Replace the Driver Manager shared libraries libodbc.so.1.0.0/libodbc.so with the newly built binaries
$cp .libs/libodbc.so* $DM_HOME/lib/
Where DM_HOME is the location where we have installed the Driver Manager.

· The following files are set.
odbcinst.ini
------------
[ODBC]
Trace = No
TraceFile = /tmp/sql.log
ForceTrace = No
Pooling = No
[ODBC10204]
Description = Oracle 10g R2 ODBC driver.
Driver = /u02/app/oracle/product/10.2.0.4/lib/libsqora.so.10.1
Setup =
FileUsage =
CPTimeout =
CPReuse =
odbc.ini
--------
[ODBC10204] (This is your DSN which you will put in your ODI topology)
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = T
DisableDPM = F
DisableMTS = T
Driver = ODBC10204
DSN = V1021U

EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = V1021U (This name is the service name of the database you want to connect. This has to be present in the tnsnames.ora file)
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = scott
Password = tiger
.odbc.ini
---------
[ODBC10204] -- (This is your DSN which you will put in your ODI topology)
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
CloseCursor = T
DisableDPM = F
DisableMTS = T
Driver = ODBC10204
DSN = V1021U

EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = V1021U
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
UserID = scott
Password = tiger
isql -v ODBC10204 scott tiger

· Add below lines in profile file

export DM_HOME=/u02/unixODBC-2.2.12
export PATH=$DM_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$DM_HOME/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/usupport/etcBottom of Form

Below is the ODI Topology Screenshot which is configured for this DSN

Dec 28, 2010

Installing Oracle Fusion Middleware 11g–PART IV–Creating Repositories

Configuring schema

Now, create the database schema for the BAM and SOA servers.

1. To create the new schema, open a command window and enter the following:

cd c:\stageSOA\rcuHome\bin rcu.bat

2. The bat command returns to the prompt immediately and, after a few seconds, the Repository Creation Utility opens (if you just ran the utility to drop the schema, it opens the second time much more quickly).

3. On the Welcome screen, click on Next.

4. Select Create and click on Next.

5. Enter the database information

6. Click on Next.

7. The pre-requisites are reviewed. When complete, click on OK. The utility moves to the next page—with a slight delay, just wait for it.

8. On the Select Components screen, enter DEV in the field for creating a new prefix.

9. Select the component SOA Infrastructure or whichever component you want to install. Dependent schemas are selected automatically.

10. Click on Next.

11. The pre-requisites for this step are checked. When completed, click on OK.

12. Select the radio button to Use the same password for all schemas. Enter a schema password. The password welcome1 is assumed in this document but you should choose your own secure password or a different one for each schema and be sure to record your passwords as you will need them later.

13. Click on Next.

14. Review the tablespaces and schema owners for the components.

15. Accepting the defaults, click on Next, and then click on OK to create the tablespaces.

16. When the pre-requisites for this step are completed, click on OK.

17. Click on Create to create the tablespaces. This takes about two minutes.

18. When completed, click on Close.

Installing Oracle Fusion Middleware 11g–PART III–Installing WebLogic Server

1. To install WebLogic Server, open a command window and enter:

cd c:\your_softwares_directory wls1033_oepe111150_win32.exe

2. When the Welcome screen for the install wizard comes up, click on Next.

3. Select Create a new Middleware Home and enter: C:\Oracle\Middleware\home_11gR1(This is your Oracle Middleware home which has lot of space, so select carefully)

4. If you wish to receive any updates enter your email and other details. Or else just ignore and press next

5. Choose "Typical" Install Type

6. Don't change anything and press next

7. Press Next. If you want don't want to create shortcuts for all users select second option

8. Press Next and all the components will be installed. Installation takes about three minutes.

9. When the installation is complete, deselect the Run Quickstart checkbox and click on Done.

Installing Oracle Fusion Middleware 11g–PART II–Installing the Database

Steps to do before Installing the Database:

Installing on DHCP Computers

Check if you are installing on DHCP Computers simply means you are installing on a external internal provider who provides dynamic IP address. Normally it will be dynamic address unless and otherwise you buy static IP address.

If it's a DHCP computer then Install Loopback Adapter before moving forward

Adding Domain Name along with Computer name

a. Open System in the Control Panel, and select the Computer Name tab.(or Right click on "My Computer" and go to properties. In Full computer name, make sure you see the host name and the

domain name, for example, sales.us.example.com.

b. Click Change. In Computer name, you should see the host name, and in Full

computer name, you should see the host name and domain name.

c. Click More. In Primary DNS suffix of this computer, you should see the

domain name, for example, us.example.com or else enter domain name. You have to restart the machine to make it effective.

Steps to Install Loopback Adapter on Windows XP

1. Open the Windows Control Panel.

2. Double-click Add Hardware to start the Add Hardware wizard.

3. In the Welcome window, click Next.

4. In the Is the hardware connected? window, select Yes, I have already connected

the hardware, and click Next.

5. In the The following hardware is already installed on your computer window, in

the list of installed hardware, select Add a new hardware device, and click Next.

6. In the The wizard can help you install other hardware window, select Install the

hardware that I manually select from a list, and click Next.

7. From the list of common hardware types, select Network adapters, and click Next.

8. In the Select Network Adapter window, make the following selections:

Manufacturer: Select Microsoft.

Network Adapter: Select Microsoft Loopback Adapter.

9. Click Next.

10. In the The wizard is ready to install your hardware window, click Next.

11. In the Completing the Add Hardware Wizard window, click Finish.

12. Right-click My Network Places on the desktop and choose Properties.

13. Right-click the connection that was just created. This is usually named "Local Area

Connection 2". Choose Properties.

14. On the General tab, select Internet Protocol (TCP/IP), and click Properties.

15. In the Properties dialog box, click Use the following IP address and do the

following:

a. IP Address: Enter a non-routable IP for the loopback adapter. Oracle recommends the following non-routable addresses:

– 192.168.x.x (x is any value between 0 and 255)

– 10.10.10.10

b. Subnet mask: Enter 255.255.255.0.

c. Record the values you entered, which you need later in this procedure.

d. Leave all other fields empty.

e. Click OK.

16. Click Close.

17. Close Network Connections.

18. Restart the computer.

19. Add a line to the DRIVE_LETTER:\WINDOWS\system32\drivers\etc\hosts file with the following format, afterthe localhost line:

IP_address hostname.domainname hostname

where:IP_address is the non-routable IP address you entered in step 15.

hostname is the name of the computer.

domainname is the name of the domain.

For example:

10.10.10.10 mycomputer.mydomain.com mycomputer

Installing the Database

1. If you want to receive security updates then enter your details or else ignore the step by clicking next

2. Choose create and configure database and press Next

3. Choose Desktop class and press next

4. Choose oracle base directory where you want to Install the Oracle database. You can allocate one separate drive for all the OFM components and just give the drive letter at the beginning of the location. For Example. just change "D" to whatever you want in the below location and don't alter other paths. Leave other values as is and enter administrative passwords. There is some restrictions for the passwords. But you can ignore that.

5. Press Next and database will be installed and configured.

Post Installation Tasks

Validating Invalid PL/SQL Modules

Oracle recommends running the utlrp.sql script after creating or upgrading a database. This script recompiles all PL/SQL modules that may be in an INVALID state, including packages, procedures, types, and so on. This step is optional, but recommended so that the performance cost of recompilation is incurred during the installation rather than in the future.

1. Start SQL*Plus:

DRIVE_LETTER:\> sqlplus /nolog

2. Connect to the database with the SYS account:

SQL> CONNECT SYS AS SYSDBA

Enter password: SYS_password

3. Start the database (if necessary):

SQL> STARTUP

4. Run the utlrp.sql script, which by default is located in ORACLE_HOME\rdbms\admin\utlrp.sql. For example:

SQL> @?\rdbms\admin\utlrp.sql

Dec 27, 2010

Installing Oracle Fusion Middleware 11g–PART I-Preparing the Installation

I have done the Installation for Windows 7 64 bit Operating System. However below steps can be used to Install any windows Operating System and I will mention if any specific steps needed.

1. Checking the Certification Matrix
Please refer the certification matrix provided by Oracle before starting with the Installation. I had to re install everything just because I didn't go through it properly and I missed some of the notes like Creating Virtual Machine in case of Windows 7 64 Bit OS.
You can find the CertMatrix in the link below
http://download.oracle.com/docs/html/E18558_01/fusion_requirements.htm
1.1 Important Points from the CertMatrix Excel:
Ø For Windows 7 64 Bit OS users one sad news. You cannot install the OFM 11g directly here. As per the certification matrix -- > This certification is with Windows 7 in XP mode only. So download VMWare and Install 32 bit XP in that, allocate around 30 GB of disk space to that and that's it. You can happily install the software's within that.
Ø Check for JDK Versions too. For Windows 7 64 bit OS users, since you are installing within XP 32 bit OS download 32 bit version of JDK
Ø Most of the Windows Versions supports below database versions. If you are planning to install on non-Oracle DB, then refer "Non Oracle DBs Certification" section in the excel

Oracle 10.2.0.4+

Oracle 11.1.0.7+
Oracle 11.2.0.1+

Ø Supported Browsers -- >

Internet Explorer 7.x

Internet Explorer 8.x

Firefox 3.x

Safari 4.x

That's it from the CertMatrix part. We will move ahead with Downloading the softwares

2. Preparing for the Installation
2.1 Memory and Disk Space requirements
This installation requires 3 GB or more available memory. The installation process requires about 12 GB of disk space. After installation, you can delete the files used by installation to save about 4 GB.
Note: Installation size of 12 GB is only for basic softwares like WebLogic Server, Database and JDeveloper. If you are planning for additional development softwares like IDM,SOA,ODI then you may plan for additional space.
2.2 Downloading the Softwares
Well there are couple of basic software's we need to download to configure OFM 11g.
All the softwares can be downloaded from the link below. You need to have/create Oracle SSO ID to download the softwares
http://www.oracle.com/technetwork/middleware/fusion-middleware/downloads/index.html

Weblogic(Required) -- This is need to configure weblogic server which can be used to run any development softwares like SOA,ODI,IDM,OSB etc.. Below are the different versions of the softwares you can think of downloading

Oracle WebLogic Server 10.3.3(preferred) -- This can be used only with 32 bit OS

Oracle WebLogic Server 10.3.2 -- This can be used only with 32 bit OS

wls1033_generic.zip -- This can be used with any OS

Note: For Windows 7 64 bit users you can install Oracle WebLogic Server 10.3.3 as you are installing within 32 bit Windows XP
RCU(Repository Creation Utility)(Required) -- This is need to create schemas before installing any development softwares like SOA,ODI,IDM etc. For example if you want to Install ODI(Oracle Data Integrator) then this RCU will create master and work repositories for you. You can download below versions of RCU

ofm_rcu_win32_11.1.1.2

ofm_rcu_win32_11.1.1.3.3

Note: There is no 64 bit versions or generic versions of RCU. You have to install one of the above mentioned versions
Database(Required) -- Download and Install 11g database(Preferred) or upgrade your existing 10g database to the version required by OFM 11g installation(10.2.0.4+) or install other vendor databases
After these above mentioned downloads you can download your development softwares as required by you. I am using SOA suite and Oracle Data Integrator. So mentioning those here.
SOA Suite(Optional) -- ofm_soa_generic_11.1.1.2.0_disk1_1of1
JDeveloper Studio(Required) – This tool will be used to develop most of the OFM components like SCA,Mediator Services etc. You can download jdevstudio11113install.exe from below link
Oracle Data Integrator 11g(Optional)

Very Important: Just before Downloading please take care of below note

Please download softwares as below mentioned set. Don't mix up the versions as it will create lot of problems and you might have to re install everything from scratch.
Set A:
WebLogic -- Oracle WebLogic Server 10.3.3
RCU -- ofm_rcu_win32_11.1.1.3.3
SOA Suite -- ofm_soa_generic_11.1.1.2.0_disk1_1of1
SOA Suite -- ofm_soa_generic_11.1.1.3.0_disk1_1of1(patch set, you cannot directly install this version, 11.1.1.2.0 is pre requisite for this installation)
Set B:
WebLogic -- Oracle WebLogic Server 10.3.2
RCU -- ofm_rcu_win32_11.1.1.2
SOA Suite -- ofm_soa_generic_11.1.1.2.0_disk1_1of1
For Windows 7 64 Bit I have used Set A.

Jul 14, 2010

ODI: To Check if the source file is Empty and make the Scenario run as successful

Consider the scenario in which you are creating an interface to transfer the data from file to a table. In this case the interface will fail if the file content is empty. We can skip the interface run if the file is empty and make the scenario run successful using the below steps.

Step 1: Create a variable named “CHECK_EMPTYFILE”. Go to Refresh tab and enter the below code.Be sure to select the correct Logical schema where the file exists.

“select C1
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=ONE_COLUMSNP$CRLOAD_FILE=<%=odiRef.getSchemaName("D")%>/yourfilename.txtSNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=0D0ASNP$CRFILE_SEP_LINE=3BSNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=C1SNP$CRTYPE_NAME=STRINGSNP$CRLINE_OFFSET=1SNP$CRLENGTH=5000SNP$CRPRECISION=5000SNP$CRACTION_ON_ERROR=0SNP$CR$$SNPS_END_KEY*/”

Step 2: Use this variable in your package diagram. And on failure just end the scenario session using “ODIWait” command with “0” seconds wait. In this case your Scenario will be success even if the file content is empty in the source