OracleApps/DB/OS Administration Solutions: 2015

Tuesday 20 October 2015

EBS : To find version of JDBC thin driver in iAS

Solution:

Create a file with name JDBCVersion.java in your middle tier (Application Tier)
---------
import java.sql.*;
import oracle.jdbc.driver.*;
class JDBCVersion
{
public static void main (String args[])
throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Get a connection to a database
Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=YES)(FAILOVER=YES)
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST= <hostname>) (PORT=
<portno>)))(CONNECT_DATA=(SID=<yoursid>)))" ,"apps","<appspaswd>");
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
}
}
-----------

Replace following parameters
1. <hostname> with your database hostname or IP address
2. <portno> with your database port no.
3. <yoursid> with SID for your database
4. <appspaswd> with your apps password
After changing save it with name JDBCVersion.java in your middle tier & execute command
[Server@oracle]$ javac JDBCVersion.java
This will create class file in your workign directory. Include your current directory into your
classpath like
export CLASSPATH=/<location where JDBCVersion.class created by above:$CLASSPATH
program>
then execute
[Server@oracle]$ java JDBCVersion
you should see output like
JDBC driver version is 11.2.0.1.0
Which means you are using jdbc thin driver version 11.2.0.1.0

Monday 19 October 2015

Linux : Get the process id of deleted file but existing in the directory

Solution:

[root@testenv]$ lsof | grep deleted | grep maillog_1
chrome 3446 user 128u REG 253,2 16400 2364626 /var/tmp/maillog_1 (deleted)
[root@testenv]$ kill -9 3446

Tuesday 6 October 2015

EBS : Worflow related queries

1. To check workflow mailer service current status

sqlplus apps/<apps_pwd>
select running_processes from apps.fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';

Number of running processes should be greater than 0

2. Find current mailer status

sqlplus apps/<apps_pwd>
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');

Values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

3. Starting Workflow notification mailer

sqlplus apps/<apps_pwd>
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin -- To find mailer Id
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf); -- Starting the Workflow Mailer
commit;
end;
/

4. Stopping Workflow notification mailer

sqlplus apps/<apps_pwd>
declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin -- To find mailer Id
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf); -- Stopping the Workflow Mailer
commit;
end;
/

5. To find the open notifications count by message_type

select message_type, mail_status, count(*) from wf_notifications where status = 'OPEN' GROUP BY MESSAGE_TYPE, MAIL_STATUS

6. To find the alerts notifications we need to query WF_NOTIFICATION_OUT queue

select corr_id, retry_count, msg_state, count(*) from applsys.aq$wf_notification_out group by corr_id, msg_state, retry_count order by count(*) desc;

Sunday 30 August 2015

EBS : FRM-92100

Error:

While java is loading FRM-92100 error is coming in all Forms.

Solution:

1. Take the backup of $CONTEXT_FILE
2. Change the context file parameter "s_forms_jvm_start_options", and add parameter:
-Doracle.net.disableOob=true
FROM:
<forms_jvm_start_options oa_var="s_forms_jvm_start_options">-server -verbose:gc -Xmx256M -Xms64M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/d01/UAT/inst/apps/UAT_erpapp02/ora/10.1.3/j2ee/forms/config/jazn.xml</forms_jvm_start_options>
TO:
<forms_jvm_start_options oa_var="s_forms_jvm_start_options">-server -verbose:gc -Xmx256M -Xms64M -XX:MaxPermSize=128M -XX:NewRatio=2 -XX:+PrintGCTimeStamps -XX:+UseTLAB -XX:+UseParallelGC -XX:ParallelGCThreads=2 -Djava.security.policy=$ORACLE_HOME/j2ee/oacore/config/java2.policy -Djava.awt.headless=true -Dhttp.webdir.enable=false -Doracle.security.jazn.config=/d01/UAT/inst/apps/UAT_erpapp02/ora/10.1.3/j2ee/forms/config/jazn.xml -Doracle.net.disableOob=true</forms_jvm_start_options>
3. Run Autoconfig
4. Restart the application services

Reference : Intermittently Occurs FRM-92100 For All EBS Forms With "recv failed" Error Code (Doc ID 1908029.1)

Tuesday 25 August 2015

EBS : Login page alignment issue as Arabic login

Error:

When the login page is changed to arabic login, Some of the login areas are displayed nearly out of the browser window and USERNAME and PASSWORD fields are aligned on the left hand side vs centered.

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)

Wednesday 12 August 2015

EBS : For specific users after login Null pointer exceptions

Error:

## Detail 0 ##
java.lang.NullPointerException
at java.util.Hashtable.put(Unknown Source)
at oracle.apps.fnd.common.DBPreferenceStore.load(DBPreferenceStore.java:162)
at oracle.apps.fnd.wf.worklist.webui.NtfWorklistCO.processRequest(NtfWorklistCO.java:185)
at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:604)

Solution:

CREATE TABLE FND_USER_PREFERENCES_BKP AS SELECT * FROM FND_USER_PREFERENCES;
UPDATE FND_USER_PREFERENCES SET PREFERENCE_VALUE='NtfSubject,' WHERE USER_NAME='&User_name' AND PREFERENCE_NAME='ADVANCED_WL_SORT_PREF';
COMMIT;

Reference: You have encountered an unexpected Error for Some Users Login. Stacktrace shows java.lang.NullPointerException related to NtfWorklistCO.class (Doc ID 1684285.1)

Wednesday 24 June 2015

DB : Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed

Error:
oratest@myserver database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 61931 MB Passed
Checking swap space: must be greater than 150 MB. Actual 19999 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] n

User Selected: No

Exiting Oracle Universal Installer, log for this session can be found at /d01/oracle/app/oraInventory/logs/installActions2015-06-24_10-03-30AM.log
[oratest@myserver database]$

Solution:

As a root user in VNC type xhost + to continue the installation
[root@myserver TEST]# xhost +
access control disabled, clients can connect from any host

DB : Installing oracle database fails with inventory owner permission denied

When installing new database as "oratest" new user on the existing server with other databases running in it the installation failed with below error

Error:

[oratest@myserver database]$ ./runInstaller
You do not have sufficient permissions to access the inventory '/d01/oracle/app/oraInventory'. Installation cannot continue. It is required that the primary group of the install user is same as the inventory owner group. Make sure that the install user is part of the inventory owner group and restart the installer.: Permission denied
[oratest@myserver database]$

Solution:
Change the oratest user group to same as the group of oraInventory

As a root user change the "oratest" group to "dba" as below

useradd -G dba oratest

Monday 8 June 2015

EBS : dbTier autoconfig fails with afdbprf.sh adcrobj.sh

Error:

adcvmlog.xml renamed to /oradb/oracle/11.2.0.3/appsutil/log/TEST_drebsdb01/06080947/adcvmlog.xml.06080948

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

[PROFILE PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oradb/oracle/11.2.0.3/appsutil/install/TEST_drebsdb01
afdbprf.sh INSTE8_PRF 1

[APPLY PHASE]
AutoConfig could not successfully execute the following scripts:
Directory: /oradb/oracle/11.2.0.3/appsutil/install/TEST_drebsdb01
adcrobj.sh INSTE8_APPLY 1

AutoConfig is exiting with status 2

AutoConfig execution completed on Mon Jun 8 09:48:27 2015

Time taken for AutoConfig execution to complete : 0 mins 49 secs

Solution:

Enter the correct apps password while running autoconfig and check whether APPS account is locked by connecting as SYSDBA
SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME='APPS';

USERNAME ACCOUNT_STATUS LOCK_DATE
------------------------------ -------------------------------- ---------------
APPS LOCKED 08-JUN-15

SQL> alter user apps account unlock;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS,LOCK_DATE from dba_users where USERNAME='APPS';

USERNAME ACCOUNT_STATUS LOCK_DATE
------------------------------ -------------------------------- ---------------
APPS OPEN

now re-run the autoconfig in dbTier

Sunday 31 May 2015

EBS : Login to application gets "unable to authenticate session" or "ORA-01403 error"

When we login to the application we get unable to authenticate session or ORA-01403 error

ERROR:

<PRE>Oracle error 1403: java.sql.SQLException: ORA-01403: no data found ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION. Your session is no longer valid. </PRE>

Servlet error: An exception occurred. The current application deployment descriptors do

Solution 1

1. Execute the following scripts from $FND_TOP/patch/115/sql as apps user:
SQL> @AFICXSMS.pls %
SQL> @AFICXSMB.pls %
2. Bounce the server.
3. Retest the issue.

Reference :Login Page Throws Error 1403: Java.Sql.Sqlexception: Ora-01403: No Data Found Ora-06512: At Line 1 H (Doc ID 1336030.1)

Solution 2:

"Unable to authenticate session"

1. Open a new explorer window using its own session cookie by selecting File -> New Session from the Menu Bar in an existing browser window. (IE9 Users may have to enable the Menu Bar first by right clicking the browser header frame and selecting Menu Bar.)
2. Run from the command line adding the -noframemerging parameter
e.g. Start -> Run -> iexplore -noframemerging
3. Create a new IE icon adding the -noframemerging parameter
e.g. Start -> Program Files
4. Right click on the Internet Explorer icon and select Properties

5. Add -noframemerging to the end of the link in the Target field
e.g."C:\Program Files\Internet Explorer\iexplore.exe" -noframemerging
6. Click the OK button to close the window

Reference : R12: Using IE8 to access two EBS Instances runs in error "Unable To Authenticate Session" (Doc ID 1098563.1)

Solution 3:

1. Go to Control Panel
2. Select Java Control Panel from the list.
3. From the popup window (Java Control Panel), uncheck "Enable the next-generation Java Plug-in" option.
4. Click "Apply" and click "OK"
5. Clear browsing history (Internet Explorer Menu Bar: Tools --> Delete Browsing History) and close all the browser sessions.
6. Open IE browser and try to access Oracle Applications, if the issue still persists reboot the system.

Solution 4:

Disable Internet Explorer add-ons:

Click "Tools" and select "Internet Options".
Click "Advanced" tab on the right.
Under "Browsing", remove/uncheck the "Enable third-party browser extensions" check box.
Click "OK" and close all opened Internet Explorer.
Open New Internet Explorer

Wednesday 13 May 2015

DB startup time sql query

SELECT TO_CHAR (startup_time, 'dd-mon-yyyy hh24:mi:ss') start_time from V$instance;

Wednesday 29 April 2015

ORA-20002: 3825: Error '-24033 - ORA-24033: no recipients for message' encountered during execution of Rule function 'WF_RULE.ERROR_RULE' for event 'oracle.apps.fnd.cp.gsc.SvcComponent.stop' with key 'SVC:29-APR-2015'. FUNCTION_NAME=WF_EVENT_OJMSTEXT_QH.enqueue()

Error:
When we try to start/stop the Worflow mailer it shows this error,

ORA-20002: 3825: Error '-24033 - ORA-24033: no recipients for message' encountered during execution of Rule function 'WF_RULE.ERROR_RULE' for event 'oracle.apps.fnd.cp.gsc.SvcComponent.stop' with key 'SVC:29-APR-2015'. FUNCTION_NAME=WF_EVENT_OJMSTEXT_QH.enqueue()

Solution:

To resolve the issue try restarting the components

1. Workflow Mailer Service
2. Workflow Document Web Services
3. Workflow Agent Listener Service

Navigation to restart the components

Login as SYSADMIN --> System Administrator --> Oracle Application Manager --> Workflow
Click on "Notification Mailer" --> Click "Workflow Mailer Services" Under Container Column --> Select "Workflow Mailer Services" --> Select "Start" --> "Go" button --> Ok
Similary do the same for "Workflow Document Web Services" and "Workflow Agent Listener Service"

Now the workflow mailer will be Started/Stopped

Sunday 19 April 2015

EBS : Transparent Data Encryption (TDE) implementation

1. Add the below entry in the sqlnet.ora

ENCRYPTION_WALLET_LOCATION=
(SOURCE= (METHOD=FILE)(METHOD_DATA=
(DIRECTORY=/oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/)
))

2. Create the directory to contain the encrypted key:

mkdir -p /oradb/oracle/11.2.0.3/admin/<CONTEXT_NAME>/encryption_wallet/

3. Creating the wallet with password "g00g1e": login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY <password>;

Eg:
ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "g00g1e";

4. Encrypting the columns of the table: login as sysdba

Syntax:
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT NO SALT);
ALTER TABLE owner.table_name MODIFY(column_name ENCRYPT);

Eg:
ALTER TABLE HR.PER_ALL_PEOPLE_F MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE HR.PQP_EXT_CROSS_PERSON_RECORDS MODIFY(NATIONAL_IDENTIFIER encrypt no salt);
ALTER TABLE BEN.BEN_PL_F MODIFY(NAME encrypt);

5. Once you added the columns to be encrypted close the wallet using below command: login as sysdba

Syntax:
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY <password>;

Eg:
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "g00g1e";

6. Opening the wallet: login as sysdba
Syntax:
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY <password>;

Eg:
ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "g00g1e";

7. How to check whether the wallet is open / closed : login as sysdba

SQL> COL WRL_PARAMETER FOR A65
SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE WRL_PARAMETER STATUS
-------------------- ----------------------------------------------------------------- ------------------
file /oradb/oracle/11.2.0.3/admin/TEST_drebsdb01/encryption_wallet/ OPEN

8. SQL to check what are the table colums that are encrypted : login as sysdba

SQL> COL OWNER FOR A5
SQL> COL COLUMN_NAME FOR A20
SQL> COL ENCRYPTION_ALG FOR A16
SQL> SELECT * FROM DBA_ENCRYPTED_COLUMNS;

OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
----- ------------------------------ -------------------- ---------------- ---
HR PER_ALL_PEOPLE_F NATIONAL_IDENTIFIER AES 192 bits key NO
HR PQP_EXT_CROSS_PERSON_RECORDS NATIONAL_IDENTIFIER AES 192 bits key NO
BEN BEN_PL_F NAME AES 192 bits key NO
BEN BEN_OPT_F NAME AES 192 bits key NO
BEN BEN_REPORTING NATIONAL_IDENTIFIER AES 192 bits key YES
HR GHR_MASS_ACTIONS_PREVIEW NATIONAL_IDENTIFIER AES 192 bits key YES
HR GHR_RIF_REGISTERS NATIONAL_IDENTIFIER AES 192 bits key YES
HRI EDW_HR_PERM_ASSIGN_LSTG NATIONAL_IDENTIFIER AES 192 bits key YES
HRI EDW_HR_PERM_ASSIGN_LTC NATIONAL_IDENTIFIER AES 192 bits key YES
HR HR_H2PI_EMPLOYEES NATIONAL_IDENTIFIER AES 192 bits key YES
BEN BEN_PRTT_ENRT_RSLT_F BNFT_AMT AES 192 bits key YES

9. When the Wallet is close we cannot access the data from backend:
SQL> SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL;
SELECT NATIONAL_IDENTIFIER FROM HR.PER_ALL_PEOPLE_F WHERE NATIONAL_IDENTIFIER IS NOT NULL
*
ERROR at line 1:
ORA-28365: wallet is not open

Note: We need to open the wallet in order to view the values of the columns table that are encrypted.

Saturday 18 April 2015

DB : pdksh-5.2.14 missing while installing 11g in RHEL-6.5 (64-bit)

Error:

pdksh-5.2.14 missing

Solution:

1. Change directory to <path>/database/stage/cvu/cv/admin
2. Backup cvu_config
$ cp cvu_config backup_cvu_config
3. Edit cvu_config and change the following line:
from:
CV_ASSUME_DISTID=OEL4
to:
CV_ASSUME_DISTID=OEL6
4. Save the updated cvu_config file
5. Install the 11.2.0.3 or 11.2.0.4 software using <path>/database/runInstaller
$ cd <path>/database
$ ./runInstaller
OUI should now perform the OEL6 prerequisite checks (which are identical to the RHEL6 prerequisite checks) and no longer report that packages "elfutils-libelf-devel-0.97" and "pdksh-5.2.14" are missing.

Reference : Installing 11.2.0.3 Or 11.2.0.4 (32-bit (x86) or 64-bit (x86-64) ) On RHEL6 Reports That Packages "elfutils-libelf-devel-0.97" And "pdksh-5.2.14" Are Missing (PRVF-7532) (Doc ID 1454982.1)

EBS : Change the FORMS color from backend

How to change the java forms color from backuend?

Solution:

Connect to apps schema
sqlplus apps/apps

set serveroutput on
REM Change Java Scheme Color - Site Level
REM update fnd_profile_option_values set PROFILE_OPTION_VALUE='TEAL' 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;
/

instead of PRURPLE provide any of the below value to change the color

BLUE
KHAKI
OLIVE
PURPLE
RED
SWAN
TEAL
TITANIUM

Monday 6 April 2015

EBS : Arabic characters are appearing as "?" Question mark in the standard output of CCR

Solution:

Check ALBAN*.ttf files exists in the directory $OA_JRE_TOP/lib/fonts/
ls -l $OA_JRE_TOP/lib/fonts/ALBAN*.ttf

1. If it doesnt exists

1.1 Copy it from the $FND_TOP/resource/ directory
cp $FND_TOP/resource/ALBAN*.ttf $OA_JRE_TOP/lib/fonts/

1.2 Restart the apache and the oc4j
cd $ADMIN_SCRIPTS_HOME
adapcctl.sh stop
adoacorectl.sh stop
adoacorectl.sh start
adapcctl.sh start

1.3 Retest the issue.

Reference : PDF Reports Show Arabic Characters As Question Marks (Doc ID 1199013.1)

2. If exists

2.1 Login as SYSADMIN
2.2 System Adminstrator --> Profile --> System
2.3 Check the profile option "FND: NATIVE CLIENT ENCODING" is set to the database character set
Check the database character set using the below query
SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

Thursday 2 April 2015

EBS : List of EBS users with their responsibilities

select fu.user_name,frv.responsibility_name,papf.full_name,papf.previous_last_name,
(select name from hr_organization_units where organization_id = paaf.ORGANIZATION_ID) department_name
from FND_USER_RESP_GROUPS_DIRECT furgd,fnd_user fu,
fnd_responsibility_vl frv,per_all_assignments_f paaf,per_all_people_f papf
where furgd.user_id = fu.user_id
and furgd.responsibility_id = frv.responsibility_id
--and fu.user_name like 'H%'
and paaf.person_id = fu.employee_id
and paaf.person_id = papf.person_id
order by 5,1

EBS : List of users who logged in to EBS application in last 14 days

SELECT DISTINCT user_name,
TO_CHAR(last_logon_date,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN_DATE,
papf.full_name,
(select name from hr_organization_units where organization_id = paaf.ORGANIZATION_ID) department_name
FROM fnd_user fu, per_all_people_f papf,per_all_assignments_f paaf
WHERE last_logon_date >=sysdate-14
--and fu.user_name like 'H%'
and paaf.person_id = fu.employee_id
and paaf.person_id = papf.person_id
order by 4,2,1 desc

Monday 30 March 2015

EBS : Font And Links Have Changed After Patching in OAF pages

Error:
Arabic heading fonts in the OAF pages are bigger, in english it is working fine

Solution:
1. Shutdown all services

2. Take a backup of the image and styles cache
cp -pr $OA_HTML/cabo/images/cache $OA_HTML/cabo/images/cache_date
cp -pr $OA_HTML/cabo/styles/cache $OA_HTML/cabo/styles/cache_date

3. Remove the files under the styles/cache
cd $OA_HTML/cabo/styles/cache
rm -rf *

4. Remove the files under the images/cache
cd $OA_HTML/cabo/images/cache
rm -rf *

5. Compile the jsp files
cd $FND_TOP/patch/115/bin
perl ojspCompile.pl --compile --flush -p 2

6. Start the application services

Reference : R12: Font And Links Have Changed After Patching (Doc ID 1348791.1)

Sunday 29 March 2015

EBS : All OAF pages turns blue after bouncing

Error:

Getting Dark Blue color on OA Framework pages after stopping the instance for Backup

Solution:

Clear the Cabo Cache

1. Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache

2. Restart the apache and adoacore

Refernce: How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite? (Doc ID 742107.1)

Wednesday 18 March 2015

EBS : Checking the INVALID indexes for a particular tablespace and rebuilding it

Here we check for APPS_TS_QUEUES tablespace and rebuild the indexes

select index_name,status from dba_indexes where tablespace_name='APPS_TS_QUEUES' and STATUS != 'VALID';

Above query gives the indexes which are INIVALID in APPS_TS_QUEUES tablespace

select 'alter index 'owner'.'index_name' rebuild;' from dba_indexes where tablespace_name='APPS_TS_QUEUES' and STATUS != 'VALID';

Get the output from the above query and run to rebuild it

Tuesday 17 March 2015

EBS : HR-PAY-ROLL run performance related Note-Ids and URL-links

How Do I Improve Payroll Performance? (Doc ID 219306.1)

TIPS FOR IMPROVING PAYROLL PERFORMANCE (Doc ID 1079475.6)

Oracle Human Resources (HRMS) Payroll PAY_ACTION_PARAMETERS Comprehensive Overview (Doc ID 549367.1)

How to Determine the Best Setting for the THREADS Parameter in the Pay_Action_Parameters Table (Doc ID 359354.1)

https://blogs.oracle.com/mandalika/entry/e_business_suite_role_of

https://community.oracle.com/thread/2612218

Extremely Slow Performance On Payroll Calculation Process (Doc ID 1944756.1)

Wednesday 11 March 2015

Useful metalink Ids

How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent (Doc ID 372933.1)

Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc 1411723.1- Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1499538.1)

Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.9 and R12.TXK.C.Delta.9 (Doc ID 2233485.1)

Cloning Oracle E-Business Suite Release 12.2 (AD-TXK Delta 6 or Lower Codelevel) with Rapid Clone (Doc ID 2047809.1)

How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace (Doc ID 287679.1)

Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1)

Concurrent Processing - CP Analyzer Sample SQL Statements Including Date Range Parameters from Doc 1411723.1- Concurrent Processing - CP Analyzer for E-Business Suite (Doc ID 1499538.1)

Cloning Oracle Applications Release 12 with Rapid Clone (Doc ID 406982.1)

Database Preparation Guidelines for an E-Business Suite Release 12.1 Upgrade (Doc ID 761570.1)

Oracle E-Business Suite Release 12.1.3 Readme (Doc ID 1080973.1)

R11i / R12 : Requesting Translation Synchronization Patches (Doc ID 252422.1)

How to Disable the FYI Workflow Notification Auto close "AutoClose FYI" So That The Users Can Close Them Manually (Doc ID 1634776.1)

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

What Are Root.sh And OrainstRoot.sh Scripts In A Standalone RDBMS Installation? (Doc ID 1493121.1)

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

Automatic Tuning of Undo_retention Causes Space Problems (Doc ID 420525.1)

Compatibility Matrix for Export And Import Between Different Oracle Versions [Video] (Doc ID 132904.1)

Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

Concurrent Processing - Troubleshooting Concurrent Request ORA-20100 errors in the request logs (Doc ID 261693.1)

Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) for Linux x86-64 (Doc ID 1330701.1)

Configuring and Managing Oracle E-Business Suite Release 12.1.x Application Tiers for Oracle RAC (Doc ID 1311528.1)

Master Note for Real Application Clusters (RAC) Oracle Clusterware and Oracle Grid Infrastructure (Doc ID 2108593.2)

Starting With Oracle JDBC Drivers - Installation, Certification, and More! (Doc ID 401934.1)

Sunday 8 March 2015

EBS : Patchset level query

select fpi.patch_level,
fa.application_short_name,
fa.application_id
from fnd_product_installations fpi,
fnd_application fa
where fa.application_short_name = '&PATCHSET_SHORT_NAME'
and fa.application_id = fpi.application_id;

Note: Provide the patchset_short_name as input

EBS : Gather Schema Statistics failed with ORA-20001: invalid column name or duplicate

Error:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
stats on table FND_CP_GSM_IPC_AQTBL is locked
stats on table FND_SOA_JMS_IN is locked
stats on table FND_SOA_JMS_OUT is locked
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:
Step 1: Identify duplicate rows

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

SQL> conn apps/apps
Connected.
SQL> select table_name, column_name, count(*) from FND_HISTOGRAM_COLS group by table_name, column_name having count(*) > 1;

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

8 rows selected.

Step 2: Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS where table_name = '&TABLE_NAME' and column_name = '&COLUMN_NAME'and rownum=1;
commit;

delete from FND_HISTOGRAM_COLS where table_name = 'JE_FR_DAS_010' and column_name = 'TYPE_ENREG' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_FR_DAS_010_NEW' and column_name = 'TYPE_ENREG' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_BE_LINE_TYPE_MAP' and column_name = 'SOURCE' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_BE_LOGS' and column_name = 'DECLARATION_TYPE_CODE' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JG_ZZ_SYS_FORMATS_ALL_B' and column_name = 'JGZZ_EFT_TYPE' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_BE_VAT_REP_RULES' and column_name = 'LINE_TYPE' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_BE_VAT_REP_RULES' and column_name = 'SOURCE' and rownum=1;
delete from FND_HISTOGRAM_COLS where table_name = 'JE_BE_VAT_REP_RULES' and column_name = 'VAT_REPORT_BOX' and rownum=1;

Step 3 : Submit the Gather schema statistics again

Reference (Doc ID 781813.1)

Thursday 5 March 2015

EBS : ORA-2001 unabled to call fnd_ldap_wrapper.create_user due

Error:

Unable to call fnd_ldap_wrapper.create_user due to the following reason
ORA-2001 unabled to call fnd_ldap_wrapper.create_user due to the following reason
An unexpected error occured. please concact your system Administrator

While creating new users in non SSO environment

Solution:

Set the Profile Option "Applications SSO Type" to "SSWA"
Save your settings
Bounce Apache Services for changes to take place
Retest the issue

Reference : ( Doc ID 863410.1)

Thursday 26 February 2015

EBS : Status Diagram not opening

The issue can be reproduced at will with the following steps:
1. Log in to Sysadmin
2. Select Workflow Adminstrator Responsbility
3. Select status monitor
4. Provide the details of the item type
5. Click on status diagram. Now it shows error page.

Solution
1. Set the profile option Server Timezone at Site level (should be set to the value of database timezone)

select dbtimezone from dual;

2. Bounce the Apache Server

Thursday 19 February 2015

EBS : Create Accounting - Cost Management Errs with Out of Memory Exception retry with scalable option or modify the Data template

Concurrent log error:

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***

Solution:
1. SYSADMIN --> Concurrent --> Program --> Define

2. Query for concurrent program with the short name = CSTCRACC

In the 'Options' field change the value to -Xmx1024M. (or higher). You can change the value to -Xmx2048M, and possibly even -Xmx4096M. Ultimately, you may exceed the heap size.

3. Restart the OPP concurrent manager so that changes take effect

Note: Enabling the scalability feature and changing the heap size to -Xmx1024M' is not recommended to be on the global level, only on the concurrent program level.

Reference : (Doc ID 873774.1)

Wednesday 18 February 2015

Linux : context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023

Error:

[oracle@oracledb ~]$ id
uid=501(oracle) gid=500(dba) groups=500(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@oracledb ~]$ cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=enforcing
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted

Solution:

[root@oracledb ~]$ vi /etc/selinux/config

disable selinux and restart the server

[root@oracledb ~]$ cat /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@oracledb ~]$

[oracle@oracledb ~]$ id
uid=501(oracle) gid=500(dba) groups=500(dba)

DB : installation failed with oracle.install.commons.util.exception.DefaultErrorAdvisor

Error:

SEVERE: [FATAL] oracledb.local.com: oracledb.local.com.
Refer associated stacktrace #oracle.install.commons.util.exception.DefaultErrorAdvisor:37
INFO: Advice is ABORT
SEVERE: Unconditional Exit
INFO: Adding ExitStatus FAILURE to the exit status set

Solution:

Check for output the values of the below

hostname
uname -a
/etc/sysconfig/network

it should contain same values of hostname in all the above three

[oracle@oracledb ~]$ hostname
oracledb.local.com
[oracle@oracledb ~]$ uname -a
Linux oracledb.local.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracledb ~]$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=oracledb.local.com
[oracle@oracledb ~]$

EBS : Unable To View Status Diagram in Status monitor workflow

Error:

oracle.apps.fnd.framework.OAException: java.lang.NullPointerException

at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)

at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processRequest(OAWebBeanHelper.java:616)