Oracle Technology Stack: January 2015

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

$ORACLE_HOME/bin/rwrun | grep Release

Forms Compiler

$ORACLE_HOME/bin/frmcmp_batch|grep Forms| grep Version

Java

$ORACLE_HOME/jdk/bin/java -fullversion

Application Server or Webserver/Apache

$IAS_ORACLE_HOME/Apache/Apache/bin/httpd –version

JRE

cat $FORMS_WEB_CONFIG_FILE|grep sun_plugin_version

Oracle Jinitiator

$grep jinit_ver_comma $CONTEXT_FILE

File Version of File System

$strings -a <file_name> | grep '$Header'

$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

$ORACLE_HOME/bin/frmcmp_batch|grep PL/SQL|grep Version

FMW Webtier

$export ORACLE_HOME=$IAS_ORACLE_HOME
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep WebTier

FMW Oracle Common

$export ORACLE_HOME=$FMW_HOME/oracle_common
$ $ORACLE_HOME/OPatch/opatch lsinventory |grep Oracle |grep Common

Weblogic Server

$ cd $FMW_HOME/wlserver_10.3/server/lib
$ java -cp weblogic.jar weblogic.version

Linux

$ lsb_release -a

$ 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

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

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...

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