Thursday, January 29, 2015

FRM-40735: ON-INSERT Trigger Raised Unhandled Exception ORA-29273


Problem

Following Error Occur While Creating Location in R12.

FRM-40735: ON-INSERT Trigger Raised Unhandled Exception ORA-29273




Solution

Set Profile option 'IRC: Geocode Host' to null for site level and log out and log back



Tuesday, January 27, 2015

Release/Version Number for Oracle EBS Components and Linux OS


Database Version
Connect sqlplus as apps user and run below query
 SQL>select banner from v$version;

Application Release
Connect sqlplus as apps user and run below quer
 SQL>select release_name from apps.fnd_product_groups;

Start CD Version
 cd startCD/Disk1/rapidwiz
 ./RapidWizVersion.sh

Report Builder
Log in as Application user, set environment variable and run below command
$ORACLE_HOME/bin/rwrun | grep Release

Forms Compiler
Log in as Application user, set environment variable and run below command
$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Java
Log in as Application user, set environment variable and run below command
$ORACLE_HOME/jdk/bin/java -fullversion

Application Server or Webserver/Apache
Log in as Application user, set environment variable and run below command
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd –version

JRE
Log in as Application user, set environment variable and run below command
cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version

Oracle Jinitiator
Log in as Application user, set environment variable and run below command
$grep jinit_ver_comma $CONTEXT_FILE

File Version of File System
$strings -a <file_name> | grep '$Header'
OR
$cat <file_name> | grep Header

Workflow Version
Connect sqlplus as apps user and run below quer
 SQL>select text ,name from wf_resources where name like '%WF_VERSION%';

PL/SQL Version
Log in as Application user, set environment variable and run below command
$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

FMW Webtier
Log in as Application user, set environment variable and run below command
$export ORACLE_HOME=$IAS_ORACLE_HOME
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep WebTier

FMW Oracle Common
Log in as Application user, set environment variable and run below command
$export ORACLE_HOME=$FMW_HOME/oracle_common
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep Oracle |grep Common

Weblogic Server
Log in as Application user, set environment variable and run below command
$ cd $FMW_HOME/wlserver_10.3/server/lib
$ java -cp weblogic.jar weblogic.version

Linux
$ lsb_release -a 
OR
$ cat /etc/redhat-release

Monday, January 26, 2015

Cloning EBS Instance using Cold backup


1. Extract file system from backup
$ tar -xvf test.tar
2. Change user and Permission to binary owner
$ Chown -R oracle:dba TEST
$ chmod -R 775 TEST
3. Switch to binary owner
$ su - oracle
4. Run postclone for DB Tier
$ cd /u01/TEST/db/tech_st/11.1.0/appsutil/clone/bin/
$ perl adcfgclone.pl dbTier
Target System Hostname (virtual or normal) [test] :

 Target Instance is RAC (y/n) [n] :

 Target System Database SID : TEST

 Target System Base Directory : /u01/TEST/

 Target System utl_file_dir Directory List :  /u01/TEST/db/tech_st/11.1.0/appsutil                                              
 Number of DATA_TOP's on the Target System [1] :

 Target System DATA_TOP Directory 1 [/findata/TEST/db/apps_st/data] :  /u01/TEST/db/apps_st/data

 Target System RDBMS ORACLE_HOME Directory  [/u01/TEST/db/tech_st/11.1.0] :

 Do you want to preserve the Display [kiawiz:0.0] (y/n)  : n

 Target System Display [test:0.0] :

 Do you want the the target system to have the same port values as the  source system  (y/n) [y] ? : n

 Target System Port Pool [0-99] : 30
5. After Successful of DB Clone then Run postclone for Application Tier
$ cd /u01/TEST/apps/apps_st/comn/clone/bin/
$ perl adcfgclone.pl appsTier
Target System Hostname (virtual or normal) [test] :

 Target System Database SID : TEST

 Target System Database Server Node [test] :

 Target System Database Domain Name [kia.lk] :

 Target System Base Directory : /u01/TEST/

 Target System Tools ORACLE_HOME Directory  [/u01/TEST/apps/tech_st/10.1.2] :

 Target System Web ORACLE_HOME Directory  [/u01/TEST/apps/tech_st/10.1.3] :

 Target System APPL_TOP Directory [/u01/TEST/apps/apps_st/appl] :

 Target System COMMON_TOP Directory [/u01/TEST/apps/apps_st/comn] :

 Target System Instance Home Directory [/u01/TEST/inst] :

 Target System Root Service [enabled] :

 Target System Web Entry Point Services [enabled] :

 Target System Web Application Services [enabled] :

 Target System Batch Processing Services [enabled] :

 Target System Other Services [disabled] :

 Do you want to preserve the Display [kiawiz:0.0] (y/n)  : n

 Target System Display [test:0.0] :

 Do you want the the target system to have the same port values as the  source system  (y/n) [y] ? : n

 Target System Port Pool [0-99] : 30

 Checking the port pool 30
 done: Port Pool 30 is free
 Report file located at /u01/TEST/inst/apps/TEST_test/temp/portpool.lst
 Complete port information available at  /u01/TEST/inst/apps/TEST_test/temp/portpool.lst

 UTL_FILE_DIR on database tier consists of the following directories.

1. /usr/tmp
2. /u01/TEST/db/tech_st/11.1.0/appsutil
3. /u01/TEST/db/tech_st/11.1.0/appsutil/outbound/TEST_test
4. /usr/tmp
Choose a value which will be set as APPLPTMP value on the target node[1]
After successful of post clone, Can access to the system.

Wednesday, January 21, 2015

Installation and Configuration of 12c Database


Container and Pluggable Databases are the New future of 12c Databases.





We can give the name of pluggable while installing 12c

[root@logisticdev ~]# su - oracle
[oracle@logisticdev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 13:40:33 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select banner from v$version;

BANNER
----------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

On Container Database

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------
CDB$ROOT
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ WRITE
SQL> select name from v$tablespace;

NAME
------------------------------
SYSAUX
SYSTEM
UNDOTBS1
USERS
TEMP
SYSTEM
SYSAUX
TEMP
SYSTEM
SYSAUX
TEMP

NAME
------------------------------
USERS
EXAMPLE

13 rows selected.

On Pluggable Database

SQL> alter session set container =PD1;

Session altered.
SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
----------------------------------------------------------
PD1
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PD1                            READ WRITE
SQL> select name from v$tablespace;

NAME
------------------------------
UNDOTBS1
SYSTEM
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64 bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions

Cloning Pluggable Database

[oracle@logisticdev ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 21 13:48:29 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions
SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY
SQL> alter pluggable database pd1 close;

Pluggable database altered.
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            MOUNTED
SQL> alter pluggable database pd1 open read only;
alter pluggable database pd1
Pluggable database altered.
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY
SQL> create pluggable database PD2 from PD1 file_name_convert=('/ora12c/oracle/a pp/oracle/oradata/TEST/pd1','/ora12c/oracle/app/oracle/oradata/TEST/pd2');

Pluggable database created.
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ ONLY
         4 PD2                            MOUNTED
SQL> alter pluggable database PD1 close;

Pluggable database altered.
SQL> alter pluggable database PD1 open;

Pluggable database altered.
SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PD1                            READ WRITE
         4 PD2                            MOUNTED

Uninstall 12c Database

$ cd <ORACLE_HOME>/deinstall
$ ./deinstall

Saturday, January 17, 2015

R12 Apache Issue : error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory


Problem

Web server were not up while start the Application server


Installation Log File
You are running adapcctl.sh version 120.7.12010000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance ...
opmnctl: opmn is already running.
opmnctl: starting opmn managed processes...
================================================================================
opmn id=ebs.calspence.lk:6250
    0 of 1 processes started.

ias-instance id=CRP3_ebs.ebs.calspence.lk
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
    HTTP_Server/HTTP_Server/HTTP_Server/

Error
--> Process (index=1,uid=1355424968,pid=10518)
    failed to start a managed process after the maximum retry limit
    Log:
    /EBS/CRP3/inst/apps/CRP3_ebs/logs/ora/10.1.3/opmn/HTTP_Server~1.lo

Apache Log File
15/01/16 14:24:32 Start process
--------
/EBS/CRP3/inst/apps/CRP3_ebs/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
/EBS/CRP3/apps/tech_st/10.1.3/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory

Status of the Services
[oracle@ebs scripts]$ ./adopmnctl.sh status apps/apps                           
You are running adopmnctl.sh version 120.6.12010000.5

Checking status of OPMN managed processes...

Processes in Instance: CRP3_ebs.ebs.calspence.lk
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:forms-c4ws    |    9886 | Alive
OC4JGroup:default_group          | OC4J:oafm          |    9747 | Alive
OC4JGroup:default_group          | OC4J:forms         |    9679 | Alive
OC4JGroup:default_group          | OC4J:oacore        |    9533 | Alive
HTTP_Server                      | HTTP_Server        |     N/A | Down

Cause

http web server unable to start due to missing library.


Solution

1. Shutdown all application services.
2. create link for library file as below
$ ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
OR

OS Library Patch for Oracle HTTP Server (on Oracle Linux 5 and RHEL 5 only)
Download and apply the patch 6078836 from My Oracle Support to fix an issue with the Oracle HTTP Server (missing libdb.so.2) bundled with the E-Business Suite technology stack.

Link to Berkeley DB library for HTTP server (on Oracle Linux 7 and RHEL 7 only)
$ cd /usr/lib
$ ln -s libdb-4.7.so libdb-4.3.so

Reference : Attempting to Start Apache (adapcctl.sh) Throws an 'Error While Loading Shared Libraries: libdb.so.2' on Linux (Doc ID 879522.1)

Wednesday, January 14, 2015

EBS Cloning Error : Perl lib version does not match with executable version


Problem

Following Error occur while running DB postclone
RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack
Perl lib version (5.10.0) doesn't match executable version (v5.8.8) at /EBS/CRP3/db/tech_st/11.2.0/perl/lib/5.10.0/x86_64-linux-thread-multi/Config.pm line 46.
Compilation failed in require at adcfgclone.pl line 28.
BEGIN failed--compilation aborted at adcfgclone.pl line 28.

Solution

First check current perl version using below command
perl -version
Then Setting the PERL5LIB environment variable according to the current perl version on the Database tier

By default, the PERL5LIB environment variable is set to the following on the Database tier:
<ORACLE_HOME>/perl/lib/5.8.3:<ORACLE_HOME>/perl/site_perl/5.8.3:<ORACLE_HOME>/appsutil/perl
But if the perl version is different, then need to set the PERL5LIB parameter with current perl vesion.
export PERL5LIB=<ORACLE_HOME>/perl/lib/<perl_version>:<ORACLE_HOME>/perl/lib/site_perl/<perl_version>:<ORACLE_HOME>/appsutil/perl

EBS Cloning Error : Unable to find PD KSH version


Problem

[oracle@test bin]$ perl adcfgclone.pl dbTier

                     Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adcfgclone Version 120.31.12010000.8

Enter the APPS password :

Checking for make...    found - /usr/bin/make
 Checking for ld...     found - /usr/bin/ld
 Checking for gcc...    found - /usr/bin/gcc
 Checking for g++...    found - /usr/bin/g++
 Checking for ar...     found - /usr/bin/ar
 Checking for ksh...
 Unable to find PD KSH version.
 Unable to locate all utilities with system path.
 PATH = /EBS/CRP3/db/tech_st/11.2.0/appsutil/clone/bin/../jre/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/java/latest/bin

WARNING: Could not find all the required OS utilities in the $PATH. Please review the checks above

Solution

Before running the perl utility on the adcfgclone.pl script set the KSH_VERSION. The following is applicable to the Linux Red Hat 5 platform 

1. Check pdksh rpm is applied to the system
$ [root@test ~]# rpm -qa pdksh*
pdksh-5.2.14-36.el5
2. And run below export command to set the version
$ export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'
Repeat the 'perl adcfgclone.pl dbTier' and it should now run successfully.

Permanent fix

Apply Patch 5972212 "ADCHKUTL.SH FAILS IN SLES 10 DUE TO PDKSH REQUIREMENT" for 11i or R12 which will bring the correct version of adchkutl.sh 115.6 which no longer checks for pdksh