Enterprise Manager DB Console set up for Oracle APPS R12 database 11g

Introduction:
In this article we will configure the Enterprise Manager DB console for Oracle 11g database for Oracle APPS R12.

CookBook:
Following points needs to be done.
1- Check for the "sysman" schema in 11g database, if it exist then it means there should be DB Console setup, to check this we can run the following statement,
$ emctl start dbconsole
if this successfull start the DB console then it means the EM DB console has already been configured, and if this statement returns any error, then better to resolve the issues or we can drop and recreate the DB Console. But before dropping the DB Console better to take the backup for the configuration files and database repository.

2- To drop the existing DB Console setup, we can use the following command.

/bin/emca -deconfig dbcontrol db -repos drop

This command will drop both the configuration files and database repository, and if this command is not working for some issues, we can go with manually deletion of the configuration files and database repository. Following commands can be used for manual deletion.

a- To drop the database repository
Step 1: Drop AQ related objects in the SYSMAN schema
Logon SQLPLUS as user SYSMAN
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'MGMT_NOTIFY_QTABLE',force =>TRUE);

Step 2: Drop the DB Control Repository Objects
Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP RESTRICT;
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
SQL> EXEC sysman.setEMUserContext('',5);
SQL> REVOKE dba FROM sysman;
SQL> DECLARE
CURSOR c1 IS
SELECT owner, synonym_name name
FROM dba_synonyms
WHERE table_owner = 'SYSMAN';
BEGIN
FOR r1 IN c1 LOOP
IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM 'r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM 'r1.owner'.'r1.name;
END IF;
END LOOP;
END;
/
SQL> DROP USER mgmt_view CASCADE;
SQL> DROP ROLE mgmt_user;
SQL> DROP USER sysman CASCADE;
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

b- To drop the configuraiton files

/
/oc4j/j2ee/OC4J_DBConsole__

3- now we can use the following command to create the database repository and configuration files for DB Console.


-bash-3.00$ emca -config dbcontrol db -repos recreate
^C-bash-3.00$
-bash-3.00$
-bash-3.00$
-bash-3.00$ emca -config dbcontrol db -repos create
STARTED EMCA at May 20, 2010 9:32:40 AM
EM Configuration Assistant, Version 11.1.0.7.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: ORA
Listener port number: 1581
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /oracle/oradb/tech_st/11.1.0
Local hostname ................ APPSDBServer.test.com
Listener port number ................ 1581
Database SID ................ ORA
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
May 20, 2010 9:33:13 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /oracle/oradb/tech_st/11.1.0/cfgtoollogs/emca/ORA/emca_2010_05_20_09_32_40.log.
May 20, 2010 9:33:16 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 20, 2010 10:16:06 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 20, 2010 10:16:31 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
May 20, 2010 10:23:33 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
May 20, 2010 10:23:41 AM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
May 20, 2010 10:23:41 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
May 20, 2010 10:24:27 AM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
May 20, 2010 10:24:27 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
May 20, 2010 10:25:12 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 20, 2010 10:27:21 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://APPSDBServer.test.com:1158/em <<<<<<<<<<<
May 20, 2010 10:27:37 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************ WARNING ************************
Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /oracle/oradb/tech_st/11.1.0/APPSDBServer.test.com_ORA/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.
***********************************************************
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 20, 2010 10:27:37 AM

4- Now the EM DB Console can be accessed by the following link.

https://APPSDBServer.test.com:1158/em

Access Forms directly in Oracle Applications R12

Introduction:
In this article we would look how we can access the forms directly in Oracle Applications R12.
Cook Book:
Following steps needs to be done to access the forms directly.
1- Take the backup of $CONTEXT_FILE in $INST_TOP/appl/admin
2- change the value "SECURE" to "OFF" for parameter s_appserverid_authentication, in the above file.
3- shutdown all the services.
4- run the autoconfig for all the application tiers even if the APPL_TOP is shared.
5-start all the services.
6- access the direct forms link from the following url.
http://appsserver.test.com:8000/forms/frmservlet

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.