DB : OPatch failed with make ioracle

Error:

[Dec 30, 2014 6:05:42 PM]    OUI-67200:Make failed to invoke "/usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/d01/UAT/db/tech_st/11.2.0.3"....'/usr/libexec/gcc/x86_64-redhat-linux/4.4.7/cc1: error while loading shared libraries: libmpfr.so.1: cannot open shared object file: No such file or directory
                             /usr/bin/ar: /d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o: No such file or directory
                             make: *** [/d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o] Error 1
                             '
[Dec 30, 2014 6:05:42 PM]    Re-link fails on target "ioracle".
[Dec 30, 2014 6:05:42 PM]    --------------------------------------------------------------------------------
                             Failed to run make commands. They are stored in file '/d01/UAT/db/tech_st/11.2.0.3/.patch_storage/12834800_Jan_27_2012_21_14_30/make.txt'
                             Invoke these commands manually to restore the binaries in the Oracle Home.
[Dec 30, 2014 6:05:42 PM]    OUI-67115:OPatch failed to restore OH '/d01/UAT/db/tech_st/11.2.0.3'. Consult OPatch document to restore the home manually before proceeding.
[Dec 30, 2014 6:05:42 PM]    --------------------------------------------------------------------------------
[Dec 30, 2014 6:05:42 PM]    The following warnings have occurred during OPatch execution:
[Dec 30, 2014 6:05:42 PM]    1) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/d01/UAT/db/tech_st/11.2.0.3"....'/usr/libexec/gcc/x86_64-redhat-linux/4.4.7/cc1: error while loading shared libraries: libmpfr.so.1: cannot open shared object file: No such file or directory
                             /usr/bin/ar: /d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o: No such file or directory
                             make: *** [/d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o] Error 1
                             '
[Dec 30, 2014 6:05:42 PM]    2) OUI-67124:Re-link fails on target "ioracle".
[Dec 30, 2014 6:05:42 PM]    3) OUI-67200:Make failed to invoke "/usr/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/d01/UAT/db/tech_st/11.2.0.3"....'/usr/libexec/gcc/x86_64-redhat-linux/4.4.7/cc1: error while loading shared libraries: libmpfr.so.1: cannot open shared object file: No such file or directory
                             /usr/bin/ar: /d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o: No such file or directory
                             make: *** [/d01/UAT/db/tech_st/11.2.0.3/rdbms/lib/config.o] Error 1
                             '

Solution:

11.2.0.3 Installation failed on RHEL 6 with the error libraries: libmpfr.so.1: cannot open shared object file: No such file or directory (Doc ID 1543787.1)

1. Check if library file exist

$cd /usr/lib64

$ls -la libmpfr*

Expected output:

lrwxrwxrwx 1 root root     16 Jul  6  2012 libmpfr.so.1 -> libmpfr.so.1.2.0*

-rwxr-xr-x 1 root root 321064 Jul 24  2010 libmpfr.so.1.2.0*

2. Check for missing mpfr package by below

$rpm -q --whatprovides /usr/lib64/libmpfr.so.1

or

$rpm -q mpfr-2.4.1-6.el6.x86_64

Expected output is as below

$ rpm -q --whatprovides /usr/lib64/libmpfr.so.1

mpfr-2.4.1-6.el6.x86_64

or

$ rpm -q mpfr-2.4.1-6.el6.x86_64

mpfr-2.4.1-6.el6.x86_64

3. If the output is not as expected then Install package mpfr by below and retry the installation.

$yum install mpfr-2.4.1-6.el6.x86_64

DB : oracle installation failed

Error:

INFO: rm -f ntcontab.*

INFO: (if [ "compile" = "compile" ] ; then \
          /PS/app/oracle/product/11.2.0/db_1/bin/gennttab > ntcontab.c ;\
          gcc -m64  -c ntcontab.c ;\
          rm -f /PS/app/oracle/product/11.2.0/db_1/lib/ntcontab.o ;\
          mv ntcontab.o /PS/app/oracle/product/11.2.0/db_1/lib/ ;\
          /usr/bin/ar rv /PS/app/oracle/product/11.2.0/db_1/lib/libn11.a /PS/app/oracle/product/11.2.0/db_1/lib/ntcontab.o ; fi)

INFO: gcc: error trying to exec 'cc1': execvp:
INFO: Permission denied

INFO: mv: cannot stat `ntcontab.o': No such file or directory

INFO: /usr/bin/ar: /PS/app/oracle/product/11.2.0/db_1/lib/ntcontab.o: No such file or directory

INFO: make: *** [ntcontab.o] Error 1

INFO: End output from spawned process.
INFO: ----------------------------------
INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'mkldflags ntcontab.o nnfgt.o' of makefile '/PS/app/oracle/product/11.2.0/db_1/network/lib/ins_net_client.mk'. See '/PS/app/oraInventory/logs/installActions2014-12-30_02-01-46PM.log' for details.
Exception Severity: 1

Solution:

check the pre-reqs rpms are installed refer the following note id

Master Note of Linux OS Requirements for Database Server (Doc ID 851598.1)

DB : Oracle runInstaller failed with no protocol specified

Error:

[oracle@localhost database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 80 MB. Actual 7440 MB Passed
Checking swap space: must be greater than 150 MB. Actual 2527 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<

Some requirement checks failed. You must fulfill these requirements before

continuing with the installation,

Continue? (y/n) [n] y

Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-05-12_01-26-43PM. Please wait ...[oracle@localhost database]$ No protocol specified
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:164)
at java.awt.Toolkit$2.run(Toolkit.java:821)
at java.security.AccessController.doPrivileged(Native Method)
at java.awt.Toolkit.getDefaultToolkit(Toolkit.java:804)
at com.jgoodies.looks.LookUtils.isLowResolution(Unknown Source)
at com.jgoodies.looks.LookUtils.<clinit>(Unknown Source)
at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:122)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:242)
at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1783)
at javax.swing.UIManager.setLookAndFeel(UIManager.java:480)
at oracle.install.commons.util.Application.startup(Application.java:758)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:164)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:265)
at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:114)
at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:132)

Solution:
Check the DISPLAY variable is set
Give xhost + in the VNC session as root user and retry the installation

DB : Oracle database installation failed Error in invoking target 'links proc gen_pcscfg' of makefile

ERROR:

INFO: /bin/chmod: cannot access `/PS/app/oracle/product/11.2.0/db_1/product/11.2.0/db_1/precomp/lib/proc'
INFO: : No such file or directory

INFO: make[1]: *** [/PS/app/oracle/product/11.2.0/db_1/product/11.2.0/db_1/precomp/lib/proc] Error 1

INFO: make[1]: Leaving directory `/PS/app/oracle/product/11.2.0/db_1/product/11.2.0/db_1/precomp/lib'

INFO: make: *** [proc] Error 2

INFO: End output from spawned process.
INFO: ----------------------------------
INFO: Exception thrown from action: make
Exception Name: MakefileException
Exception String: Error in invoking target 'links proc gen_pcscfg' of makefile '/PS/app/oracle/product/11.2.0/db_1/product/11.2.0/db_1/precomp/lib/ins_precomp.mk'. See '/PS/app/oracle/product/11.2.0/oraInventory/logs/installActions2014-12-29_04-15-27PM.log' for details.
Exception Severity: 1

Solution:

Make sure you are installing 64 bit oracle on your 64 bit linux; 32 bit oracle software on your 32 bit linux software. If you don't do "Error in invoking target 'client_sharedlib' of makefile" will likely be the problem. So before proceed ensure your hardware platform, linux platform and oracle software. Identified these things are discussed detail in 

EBS : View the concurrent output files *.pdf *.excel in the clone instance

The solution to view the output files is to update the tables fnd_conc_req_outputs, fnd_concurrent_requests.

1.        Create the backup of both the tables fnd_conc_req_outputs, fnd_concurrent_requests

      create table fnd_conc_req_outputs_bkp as select * from fnd_conc_req_outputs;

      create table fnd_concurrent_requests_bkp as select * from fnd_concurrent_requests;

2.       Update the columns in both the tables listed below accordingly.

fnd_concurrent_requests
Column Name	Value
file_name	OS file path for output logfile
file_node_name	node name
fnd_conc_req_outputs
Column Name	Value
logfile_name	OS file path for request logfile
logfile_node_name	node name
outfile_node_name	node name
outfile_name	OS file path for output logfile

3.       Update statements:

update fnd_conc_req_outputs set file_name=replace(file_name,'/oraapps/prod/prodcomn','/oraapps/dev/devcomn'), file_node_name='DEV';

commit;

update fnd_concurrent_requests set

logfile_name=replace(logfile_name,'/oraapps/prod/prodcomn','/oraapps/dev/devcomn'),

logfile_node_name='DEV', outfile_node_name='DEV',

outfile_name=replace(outfile_name,'/oraapps/prod/prodcomn','/oraapps/dev/devcomn') where logfile_name like '%prod%';

commit;

EBS : Database Tier (perl adcfgclone.pl dbTier/dbTechStack) fails with RC-00110

Error:

APPS Password : Log file located at /d01/PREPROD/oradb/11.2.0.3/appsutil/log/PREPROD_rgovis/ApplyDBTechStack_12201105.log
  \      0% completed       RC-00110: Fatal: Error occurred while relinking of ApplyDBTechStack

ERROR while running Apply...
Sat Dec 20 11:06:07 2014

 ERROR: Failed to execute /d01/PREPROD/oradb/11.2.0.3/appsutil/clone/bin/adclone.pl

 Please check logfile.


 Detail log : /d01/PREPROD/oradb/11.2.0.3/appsutil/log/PREPROD_rgovis/ApplyDBTechStack_12201105.log
[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [APPLY PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /d01/PREPROD/oradb/11.2.0.3/perl/bin/perl -I /d01/PREPROD/oradb/11.2.0.3/perl/lib/5.8.3 -I /d01/PREPROD/oradb/11.2.0.3/perl/lib/site_perl/5.8.3 -I /d01/PREPROD/oradb/11.2.0.3/appsutil/perl /d01/PREPROD/oradb/11.2.0.3/appsutil/clone
      ouicli.pl               INSTE8_APPLY       255


AutoConfig is exiting with status 1

RC-50013: Fatal: Instantiate driver did not complete successfully.
/d01/PREPROD/oradb/11.2.0.3/appsutil/driver/regclone.drv

Solution:

Set the perl to point to the <ORACLE_HOME>/perl/bin
Eg:
export PATH=<ORACLE_HOME>/perl/bin:$PATH

Linux : /var/lib/mlocate/mlocate.db': Permission denied

Solution:

Run the below command as root user

chmod 711 /usr/bin/locate
chmod g+s /usr/bin/locate

Then run locate command as other user

EBS : oracle apps r12 username,password field doesn't appear in arabic interface

Solution:

1) Log into the application as the "System administrator" responsibility
2) Choose Profile > System
3) Click in the USER field and add the username GUEST
4) Set the following profile options at user level

Personalize Self-Service Defn = No

5) Save the changes
6) Bounce the E-Business suite web tier services
7) Re-test the login issue

Reference

Main Login Page Layout Issue In BIDI Languages such as Arabic and Hebrew (Doc ID 453279.1)

EBS : ERROR: OUI files not found at their expected location.

ERROR:

[oraprod@drebsdb01 TEST_drebsdb01]$ perl adpreclone.pl appsTier

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

                        Oracle Applications Rapid Clone

                                 Version 12.0.0

                      adpreclone Version 120.20.12010000.5

Enter the APPS User Password:
Running:
perl /oradb/oracle/11.2.0.3/appsutil/bin/adclone.pl java=/oradb/oracle/11.2.0.3/jdk/jre mode=stage stage=/oradb/oracle/11.2.0.3/appsutil/clone component=appsTier method=CUSTOM dbctx=/oradb/oracle/11.2.0.3/appsutil/TEST_drebsdb01.xml showProgress
APPS Password :
You must define an applications context to use
        appctx=<applications context file>


ERROR: OUI files not found at their expected location. Please make sure that the pre-requisite OUI patch was applied correctly: patch should be unzipped directly at the ORACLE_HOME root level so that OraInstaller.jar exists in either
/oui/jlib/
or
/oui/lib/
Restart adclone.pl after performing required corrections.

ERROR while running perl /oradb/oracle/11.2.0.3/appsutil/bin/adclone.pl java=/oradb/oracle/11.2.0.3/jdk/jre mode=stage stage=/oradb/oracle/11.2.0.3/appsutil/clone component=appsTier method=CUSTOM dbctx=/oradb/oracle/11.2.0.3/appsutil/TEST_drebsdb01.xml showProgress ...
Mon Dec  1 09:28:49 2014

Soultion:

Check in which tier you are running the perclone and pass the parameter correctly as dbTier for DatabaseTier and appsTier for ApplicationTier

DB : OPatch cannot find a valid oraInst.loc file to locate Central Inventory OPatch failed with error code 104

Error:

[oradev@tsebsdb01 OPatch]$ opatch lsinventory /
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /d01/oracle/DEV01/db/tech_st/11.2.0.3
Central Inventory : n/a
   from           :
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : n/a

OPatch cannot find a valid oraInst.loc file to locate Central Inventory.

OPatch failed with error code 104
[oradev@tsebsdb01 OPatch]$

Solution:

Use -invPtrLoc with the oraInst.loc full path as highlighted below in green

[oradev@tsebsdb01 OPatch]$ opatch lsinventory -invPtrLoc /d01/oracle/DEV01/db/tech_st/11.2.0.3/oraInst.loc
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /d01/oracle/DEV01/db/tech_st/11.2.0.3
Central Inventory : /d01/oracle/DEV01/db/tech_st/11.2.0.3/admin/oui/DEV_tsebsdb01/oraInventory
   from           : /d01/oracle/DEV01/db/tech_st/11.2.0.3/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /d01/oracle/DEV01/db/tech_st/11.2.0.3/cfgtoollogs/opatch/opatch2014-11-30_10-31-30AM.log

Lsinventory Output file location : /d01/oracle/DEV01/db/tech_st/11.2.0.3/cfgtoollogs/opatch/lsinv/lsinventory2014-11-30_10-31-30AM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.3.0
There are 1 products installed in this Oracle Home.


Interim patches (10) :

Patch  13366268     : applied on Thu Feb 20 15:58:22 GST 2014
Unique Patch ID:  14394336
   Created on 21 Dec 2011, 08:14:06 hrs PST8PDT
   Bugs fixed:
     13366268

EBS DB : SIMPLE RMAN BACKUP BASED DUPLICATION (SAME & DIFFERENT DIRECTORY STRUCTURE)

PROD --> Source
TEST --> Target

SAME DIRECTORY STRUCTURE:
1. Copy the ORACLE_HOME and RMAN backup to the Target server

2. Configure EBS Oracle Home using perl adcfgclone.pl dbTechStack
3. Start the database in nomount state
    sqlplus / as sysdba
    startup nomount

4. Connect to RMAN in the target instance as auxiliary
    rman auxiliary /

5. Run the below RMAN command
    duplicate database to "<target_Sid>" BACKUP LOCATION '<rman_bkP_copied_path>' nofilenamecheck;
Example:
    duplicate database to "TEST" BACKUP LOCATION '/backup/RMANBKP' nofilenamecheck;


DIFFERENT DIRECTORY STRUCTURE:
1. Copy the ORACLE_HOME and RMAN backup to the Target server

2. Configure EBS Oracle Home using perl adcfgclone.pl dbTechStack
3. Start the database in nomount state
    sqlplus / as sysdba
    startup nomount

4. Connect to RMAN in the target instance as auxiliary
    rman auxiliary /

5. Run the below RMAN command, only change between the same and different Directory structure is to add the following lines in the RMAN command

run
{
SET DB_FILE_NAME_CONVERT=’/d01/oradata/proddata’,’/d01/oradata/clonedata′
SET LOG_FILE_NAME_CONVERT=’/d01/oradata/proddata’,’/d01/oradata/clonedata′
duplicate database to "TEST" BACKUP LOCATION '/backup/RMANBKP' nofilenamecheck;
}

EBS : Check which files will be affected when you Run autoconfig in appsTier

Solution:

Run $AD_TOP/bin/adchkcfg.sh by giving full $CONTEXT_FILE path and APPS pwd, it will generate a HTML file with which file will be changed

Example:

[applprod@driebsapp01 ~]$ sh $AD_TOP/bin/adchkcfg.sh
Enter the full path to the Applications Context file:
/appl/inst/apps/TEST_driebsapp01/appl/admin/TEST_driebsapp01.xml
Enter the APPS password:

The log file for this session is located at: /appl/inst/apps/TEST_driebsapp01/admin/log/01221047/adconfig.log

AutoConfig is running in test mode and building diffs...

AutoConfig will consider the custom templates if present.
        Using CONFIG_HOME location     : /appl/inst/apps/TEST_driebsapp01
        Classpath                   : /appl/apps/apps_st/comn/java/lib/appsborg2.zip:/appl/apps/apps_st/comn/java/classes

        Using Context file          : /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/TEST_driebsapp01.xml

Context Value Management will now update the test Context file

        Updating test Context file...COMPLETED

        [ Test mode ]
        No uploading of Context File and its templates to database.

Testing templates from all of the product tops...
        Testing AD_TOP........COMPLETED
        Testing FND_TOP.......COMPLETED
        Testing ICX_TOP.......COMPLETED
        Testing MSC_TOP.......COMPLETED
        Testing IEO_TOP.......COMPLETED
        Testing BIS_TOP.......COMPLETED
        Testing AMS_TOP.......COMPLETED
        Testing CCT_TOP.......COMPLETED
        Testing WSH_TOP.......COMPLETED
        Testing CLN_TOP.......COMPLETED
        Testing OKE_TOP.......COMPLETED
        Testing OKL_TOP.......COMPLETED
        Testing OKS_TOP.......COMPLETED
        Testing CSF_TOP.......COMPLETED
        Testing IGS_TOP.......COMPLETED
        Testing IBY_TOP.......COMPLETED
        Testing JTF_TOP.......COMPLETED
        Testing MWA_TOP.......COMPLETED
        Testing CN_TOP........COMPLETED
        Testing CSI_TOP.......COMPLETED
        Testing WIP_TOP.......COMPLETED
        Testing CSE_TOP.......COMPLETED
        Testing EAM_TOP.......COMPLETED
        Testing FTE_TOP.......COMPLETED
        Testing ONT_TOP.......COMPLETED
        Testing AR_TOP........COMPLETED
        Testing AHL_TOP.......COMPLETED
        Testing OZF_TOP.......COMPLETED
        Testing IES_TOP.......COMPLETED
        Testing CSD_TOP.......COMPLETED
        Testing IGC_TOP.......COMPLETED

Differences text report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/cfgcheck.txt

        Generating Profile Option differences report...COMPLETED
Differences text report for the Database is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/ProfileReport.txt
        Generating File System differences report......COMPLETED
Differences html report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/cfgcheck.html

Differences Zip report is located at: /appl/inst/apps/TEST_driebsapp01/admin/out/01221047/ADXcfgcheck.zip

AutoConfig completed successfully.

EBS : RMAN backup fails with "ORA-19566: exceeded limit of 0 corrupt blocks for file"

Error:

Starting backup at 29-OCT-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00352 name=/d01/UAT/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/29/2014 12:25:04
ORA-19566: exceeded limit of 0 corrupt blocks for file /d01/UAT/db/apps_st/data/system09.dbf

Solution:

1. Check the free space and used space of datafile system09.dbf
 
   SET PAGESIZE 60
   SET LINESIZE 300
   COLUMN "Tablespace Name" FORMAT A22
   COLUMN "File Name" FORMAT A82
   SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
           Substr(df.file_name,1,80) "File Name",
           Round(df.bytes/1024/1024,0) "Size (M)",
           decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used Space(M)",
           decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free Space(M)",
           decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
   FROM    DBA_DATA_FILES DF,
          (SELECT file_id,
                  sum(bytes) used_bytes
           FROM dba_extents
           GROUP by file_id) E,
          (SELECT Max(bytes) free_bytes,
                  file_id
           FROM dba_free_space
           GROUP BY file_id) f
   WHERE    e.file_id (+) = df.file_id
   AND      df.file_id  = f.file_id (+)
   AND      df.file_name like '%system09.dbf'
   /

   If the space used is less than 1000 MB,

2. Resize the datafile system09.dbf
     alter database datafile '/d01/UAT/db/apps_st/data/system09.dbf' resixe 1000M;
     alter database datafile '/d01/UAT/db/apps_st/data/system09.dbf' resixe 1500M;

3. Now re-run the RMAN backup.

EBS : Restrict Specific users to access E-Business Suit R12

Solution:

1. Edit file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/custom.conf and add a list of ip addresses for the users that you want to allow access to the system. When autoconfig is run the values in the custom.conf is preserved.

Example:
<Location ~ "/OA_HTML">
Order deny,allow
Deny from all
Allow from test0.local.com
Allow from test1.local.com
Allow from test2.local.com
Allow from test3.local.com
Allow from localhost
Allow from <appsTier Full hostname>
Allow from <appTier host>
</Location>

Include localhost and your apps tier server name. Its better to use the PC name instead of IP addresses as the IP address may change

3. Restart Apache server

4.Users whose IP addresses are assigned will have access. All other users will get a forbidden error message as below when they attempt to login.

ERROR Message:

Forbidden

You don't have permission to access /OA_HTML/RF.jsp on this server.

EBS : The Workflow notification mailer is stuck and emails are not going out

Solution:

1. Log into applications as the SYSADMIN user.

2. In the upper right hand corner of the screen, click on Preferences.

3. Within the Notifications section for Email Style, select a valid option other than: Do not send me email or Disabled.

4. Now run the Synchronize WF LOCAL tables Concurrent request.

5. Retest the issue after the Synchronize WF LOCAL tables request is complete.

6. Migrate the solution as appropriate to other environments.

Reference:

Workflow Notifications are Stuck (Doc ID 577234.1)

LINUX : Sync Time between Two servers

Solution:
date --set="$(ssh user@server date)"

Eg:

[root@driebsapp01 ~]# date
Mon Feb 23 12:08:22 GST 2015
[root@driebsapp01 ~]# date --set="$(ssh root@10.101.40.10 date)"
The authenticity of host '10.101.40.10 (10.101.40.10)' can't be established.
RSA key fingerprint is 29:94:78:7d:13:92:8b:b2:4c:02:e2:76:68:56:f4:8d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.101.40.10' (RSA) to the list of known hosts.
root@10.101.40.10's password:
Mon Feb 23 11:10:30 GST 2015
[root@driebsapp01 ~]#

EBS : Gather Schema Statistics Error - ORA-20001: invalid column name or duplicate columns/column

Error:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #2: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***
Error #3: ERROR: While GATHER_TABLE_STATS:
object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Solution: Refrence note ID 781813.1

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LINE_TYPE_MAP'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
SOURCE                                254
SOURCE                                254

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_LOGS'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
DECLARATION_TYPE_CODE                 254
DECLARATION_TYPE_CODE                 254

SQL> select column_name, nvl(hsize,254) hsize
from FND_HISTOGRAM_COLS
where table_name = 'JE_BE_VAT_REP_RULES'
order by column_name;  2    3    4

COLUMN_NAME                         HSIZE
------------------------------ ----------
LINE_TYPE                             254
LINE_TYPE                             254
SOURCE                                254
SOURCE                                254
VAT_REPORT_BOX                        254
VAT_REPORT_BOX                        254

6 rows selected.

SQL> select table_name, column_name, count(*)
from FND_HISTOGRAM_COLS
 group by table_name, column_name
having count(*) > 1;  2    3    4

TABLE_NAME                     COLUMN_NAME                      COUNT(*)
------------------------------ ------------------------------ ----------
JE_FR_DAS_010                  TYPE_ENREG                              2
JE_FR_DAS_010_NEW              TYPE_ENREG                              2
JE_BE_LINE_TYPE_MAP            SOURCE                                  2
JE_BE_LOGS                     DECLARATION_TYPE_CODE                   2
JG_ZZ_SYS_FORMATS_ALL_B        JGZZ_EFT_TYPE                           2
JE_BE_VAT_REP_RULES            LINE_TYPE                               2
JE_BE_VAT_REP_RULES            SOURCE                                  2
JE_BE_VAT_REP_RULES            VAT_REPORT_BOX                          2

Run the following as APPS user queries and run the delete quries, commit  and re-run the Gather Schema Statistics Concurrent program:

select 'delete from FND_HISTOGRAM_COLS where table_name = '''||TABLE_NAME||''' and  column_name = '''||COLUMN_NAME||''' and rownum=1;' from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

select 'delete from FND_HISTOGRAM_COLS where (table_name, column_name) in (select hc.table_name, hc.column_name from FND_HISTOGRAM_COLS hc , dba_tab_columns tc where hc.table_name  ='''||TABLE_NAME||''' and hc.table_name= tc.table_name (+) and hc.column_name = tc.column_name (+) and tc.column_name is null);' from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

commit;

DB : Tablespace Free Size + Total Size + Datafiles in TBS + add datafile

Solution:

------ * FREE SIZE *---------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_free_space where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * TOTAL SIZE *--------
select tablespace_name,round(sum(bytes/1024/1024)) from dba_data_files where tablespace_name in('APPS_TS_SEED')group by tablespace_name order by tablespace_name;

------ * DATAFILES *--------
column name format a40

SELECT name, bytes/1024/1024 FROM v$datafile WHERE ts# = (SELECT ts# FROM v$tablespace WHERE name = 'APPS_TS_SEED');

------ * Resize DATAFILE *--------

alter database datafile '/files/VIS/db/apps_st/data/reference1.dbf' resize 1024M;

------ * ADD DATAFILE *--------

alter tablespace SYSAUX add datafile '/files/VIS/db/apps_st/data/sysaux01.dbf' size 2000M;

------ * Tablespace free + used + total Size *--------
set lines 200
set pages 100
column "Tablespace" format a20
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999

select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
((df.totalspace - fs.freespace)*100/ df.totalspace)"Used Percentage",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name
order by 6;


------ * Database Used Space + DB Size + Free Size *--------

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select     bytes
     from     v$datafile
     union     all
     select     bytes
     from      v$tempfile
     union      all
     select      bytes
     from      v$log) used
,     (select sum(bytes) as p
     from dba_free_space) free
group by free.p
/

EBS : Configuring Printer

Soultion:

1. Printer must be registered in OS level by server admin
2. Printer name and IP address need to be added in hosts file
3. System Administrator -> Install -> Printer -> Register
                 Register the printer with the Printer name on the hosts file and choose the type of driver and save it
4. Bounce the concurrent manager
5. Now test the printer by giving print from any concurrent request.

DB : Getting the database environment variable in SQL propmt

Solution:

var OHM varchar2(1000);
EXEC dbms_system.get_env('ORACLE_HOME', :OHM);
print OHM

Windows : How to Check the number of Processor and cores

Solution:

Open Command prompt  and type the following commands:

To get the number of processors:

echo %NUMBER_OF_PROCESSORS%

To get the full details about CPU:

WMIC CPU Get /Format:List

EBS : SQL query to List the users on the system in the past N day

Solution:

select user_name,LAST_LOGON_DATE from FND_USER where USER_ID in ( select distinct user_id from icx_sessions where  last_connect > sysdate - N and user_id != '-N') order by 2;

Note: Substitute Value for N for the required number of days

EBS : Profile options to disable the personalization links in JSP pages EBS

Solution:

FND: Personalization Region Link Enabled - No
Personalize Self-Service Defn - No
Disable Self-Service Personal - No

EBS : Oracle EBS application page alignment issue

ISSUE:

Alignment of Buttons headers of the JSP pages are not aligned properly in IE but works fine in Chrome.

Solution:

1. Shutdown ALL services.

2. BACKUP the files in the following directories and then remove them :
Note: Once you complete these steps, all the above directories would still be present, but will be empty.
$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache

3.Restart ALL services

4. Clear ALL browser cache and close ALL browser windows.

EBS : adrelink fails in RHEL 6+ while executing patches

Error:

Relink of module “MSONEW” failed.
…….
Relink of module “FEMCCE” failed.
…….
Relink of module “MSRNEW” failed.
…….
Relink of module “MSCCPP” failed.
…….
Relink of module “MSCMON” failed.
…….
Relink of module “MSCNEW” failed.
…….
Relink of module “MSCNSP” failed.
………
Relink of module “MSCNSPNM” failed.
………
Relink of module “MSCPCL” failed.
………
Relink of module “MSCPDW” failed.
………
Relink of module “MSCPRG” failed.
………
Relink of module “MSCSDW” failed.
………
Relink of module “MSCSLD” failed.
………


It asks us whether we want to continue patch session as Normal or else Stop

NO-Will stop the patch session and YES will allows us continue patching as if normal.

So to exit patch session type NO and once patch session ends. we have to perform below work around.

Solution:

To fix this problem, we are required to replace the following line under the Linux section of file ” $AD_TOP/bin/adrelinknew.sh”:

From:

CPP='g++'
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh'

TO:

CPP='g++'
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec'

DB : Space Used Free %used for Each Datafiles

SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A22
COLUMN "File Name" FORMAT A82

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used Space(M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free Space(M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT Max(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name
/

LINUX : Split and join the files

split -b 3072m "MySegment" "MYSegment"

cat MySegmentab >> MySegmentaa
cat MySegmentac >> MySegmentaa
cat MySegmentad >> MySegmentaa

EBS : Workflow mailer status

1. Check workflow mailer service current status

select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';


2. Find current mailer status

SELECT component_name as Component, component_status as Status FROM fnd_svc_components where component_name = 'Workflow Notification Mailer';

select component_status from apps.fnd_svc_components where component_id = (select component_id from apps.fnd_svc_components where component_name = 'Workflow Notification Mailer');

  Possible values:
  ****************
  RUNNING
  STARTING
  STOPPED_ERROR
  DEACTIVATED_USER
  DEACTIVATED_SYSTEM

DB : Who is using the temp tablespace

SELECT b.tablespace,
          ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
          a.sid||','||a.serial# SID_SERIAL,
          a.username,
          a.program
     FROM sys.v_$session a,
          sys.v_$sort_usage b,
          sys.v_$parameter p
    WHERE p.name  = 'db_block_size'
      AND a.saddr = b.session_addr
   ORDER BY b.tablespace, b.blocks; 

DB : Database start time

select TO_CHAR(startup_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as "START_TIME" FROM sys.v_$instance;

EBS : Adding SYSADMIN responsibility at backend

BEGIN
FND_USER_PKG.AddResp('<username>', 'SYSADMIN', 'SYSTEM_ADMINISTRATOR','STANDARD', 'Auto Assignment by Daily Refresh Script', sysdate, SYSDATE + 100);
commit;
END;
/

EBS : Change the Oracle Forms color at back end

REM Change Java Scheme Color - Site Level
REM update fnd_profile_option_values set PROFILE_OPTION_VALUE='PURPLE' where PROFILE_OPTION_ID = 1002097 and level_id = 10001;
DECLARE
        stat boolean;
BEGIN
        dbms_output.disable;
        dbms_output.enable(100000);
        stat := FND_PROFILE.SAVE('FND_COLOR_SCHEME', 'PURPLE', 'SITE');
        IF stat THEN
                dbms_output.put_line( 'Stat = TRUE - profile updated' );
        ELSE
                dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
        END IF;
        commit;
END;
/

DB : Archive log generation query

SQL to Generate the DAILY Archive Log Generation:

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;


SQL to Generate the HOURLY Archive Log Generation:

set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;


SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/

EBS : ORA-04031: unable to allocate 3896 bytes of shared memory

Error:

04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
    *Cause:  More shared memory is needed than was allocated in the shared
         pool.
    *Action: If the shared pool is out of memory, either use the
         DBMS_SHARED_POOL package to pin large packages,
         reduce your use of shared memory, or increase the amount of
         available shared memory by increasing the value of the
         initialization parameters SHARED_POOL_RESERVED_SIZE and
         SHARED_POOL_SIZE.
         If the large pool is out of memory, increase the initialization
         parameter LARGE_POOL_SIZE.

Solution:

1 . Stop the application services
     adstpal.sh apps/apps
     CCM will not stop kill all the application Tier services

2. Bounce the database
    alter system switch logfile;
    alter system switch logfile;
    alter system switch logfile;
    shut immediate [if shut immediate does not bring down the database use shut abort]

3. Increase the shared_pool_size value in init.ora file

4. Start the database
     Startup

5. Start the application services
    adstrtal.sh apps/apps

EBS : ICX Authentication cookie

Steps:

1. Open IE
2. Provide the link to access EBS R12.
3. Logged to EBS
4. In same Tab & in the address bar paste : javascript:document.writeln(document.cookie);

EBS : Workflow mailer Startup problem (System Deactivated):

Error:

at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:300)
 at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:522)
 at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1238)
 at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)
 at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)
 at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2212)
 at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)
 at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)

Solution:

Check for the inbound and outbound server name in the WF_mailer configuration both the name must be same.

Start the workflow mailer