Monday, November 14, 2016

ORA-01940: cannot drop a user that is currently connected

SQL>drop user username cascade;
drop user username cascade

*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = 'ISL_DF' and p.addr (+) = s.paddr;

SID           SERIAL# STATUS    SPID

----------   ----------    --------       ------------
159               29          INACTIVE 12349

SQL>alter system kill session '159,29';

System altered.
SQL>!kill -9 12349            - (kill the process in OS level)
SQL>drop user username cascade;
User dropped.

Remove the RPM in linux if dependencies exit

Solution ::

yum remove $(rpm -qa | grep PACKAGENAME)

* Change PACKAGENAME with your Package name

* For disabling plugins just add --disableplugin=PLUGIN-NAME

* If you can't access the Internet, just add this options to the line above --disablerepo=*

How to find the Dependent objects of particular object in oracle

select t.name,t.type,s.status from dba_dependencies t ,dba_objects s where t.referenced_name='<object name >' and t.name=s.object_name;

NAME TYPE STATUS

------------------------------ ------------------ -------

JJ VIEW VALID

DD VIEW INVALID

Setting System Profile Options

Oracle Applications establishes a value for user profile options when a user signs on or changes responsibility. Use this procedure to view or set a profile option value.

Steps

In the Navigator window, on the Functions tab, choose Profile > System.

The Find System Profile Values window appears. The Site and Profile with No Values boxes are selected by default.

Select the level at which you want to view or set the profile option value. You have the following options:

Level Description

Site :: View or set profile option values for all users.

Application :: View or set profile option values for users working under responsibilities owned by a specific application. Select an application from the list.

Responsibility :: View or set profile option values for users working under a specific responsibility. Select a responsibility from the list.

User :: View or set profile option values for a specific user. Select the username from the list.

If you want to display profile options both with and without values, select the Profiles with No Values box.

If you want to display profile options that include a specific character string, enter the string in the Profile field.

You may search for profile options using character strings and the wildcard symbol (%).For example, to find profile options prefixed by "IEU," the product code for Oracle Universal Work Queue, enter IEU%.

Click Find.

The System Profile Values window appears.

Set the values for the profile options at one or more levels.

When a profile option may be set at more than one level, the value entered at the Site level has the lowest priority.The value entered at the Site level is superseded by any value entered at the Application level value and the value entered at the Application level is superseded by any value entered at the Responsibility level.The value entered at the User level has the highest priority and overrides values entered at any other level.

Your changes take effect as soon as users sign on or change responsibility.

Checking the profile option name and profile id from the backend for particular profile name

select profile_option_id,profile_option_name,user_profile_option_name from fnd_profile_options_vl where user_profile_option_name like '%OIC%%SQL%Loader%Control%File%Directory%';

To check the number of values set for for profile option

select count(*) from fnd_profile_option_values where PROFILE_OPTION_ID= 5614;

So here only one profile option is set so the count is only 1.

To check the value assigned to the profile option use below command

Select profile_option_value from fnd_profile_option_values where PROFILE_OPTION_ID=5614;

To update the profile option at site,user,organization,resposibilty,server,application level user below

Site =10001

Application=10002

Resposibilty=10003

User =10004

SQL>update fnd_profile_option_values set PROFILE_OPTION_VALUE='xyz'

where PROFILE_OPTION_ID=5614 and LEVEL_ID=10002;

Find out the Applied AD patches and Current Patch level in R12

To Find the Applied AD Patches

From Application

1. Login to Sysadmin

2. Navigate to the System Administrator->Oracle Applications Manager ->Patching and Utilities

3. Then Search the patch with relevant parameter and click 'Go' Button

From SQL

1)
SELECT DISTINCT
e.patch_name Patch_NO,
d.patch_abstract Patch,
e.CREATION_DATE Applied_Date
FROM
ad_patch_drivers d ,
ad_applied_patches e
WHERE
d.applied_patch_id = e.applied_patch_id ORDER BY e.CREATION_DATE desc;

2)
SELECT DISTINCT a.bug_number,
e.patch_name,
d.patch_abstract,
e.CREATION_DATE,
c.end_date,
b.applied_flag
FROM
ad_bugs a,
ad_patch_run_bugs b,
ad_patch_runs c,
ad_patch_drivers d ,
ad_applied_patches e
WHERE
a.bug_id = b.bug_id AND
b.patch_run_id = c.patch_run_id AND
c.patch_driver_id = d.patch_driver_id AND
d.applied_patch_id = e.applied_patch_id AND
b.applied_flag='Y'
order by e.CREATION_DATE desc

To Find The Current Patch Level

From Application

1. Login to Sysadmin

2. Navigate to the System Administrator->Oracle Applications Manager and open one of Java Page

3. Click Support Cart –> Applications Signature –> Collect->Check “Product Information” box and Click on “View” (eyeglasses) to run the patch set level report

From SQL

SELECT
a.application_name, a.APPLICATION_SHORT_NAME,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM
apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;

Monday, November 7, 2016

1)Currently we have issue with Patch 21841299:R12.AD.C.(DELTA 8).

2)In worker log below is the error message, As the issue in worker log adop session still remains hangs. And in adctrl we can see that worker status running but its not performing any action
for many hours, So we forcefully existed the session.

ms/java:/u01/apps/
fs1/EBSapps/10.1.2/forms/java/frmall.jar:/u01/apps/fs1/EBSapps/10.1.2/jlib/ewt3.jar:/u01/apps/fs1/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar"

Calling /u01/apps/fs1/EBSapps/comn/util/jdk32/jre/bin/java ...
Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 114
ORA-06512: at "APPS.AD_ZD_CTX", line 13
ORA-06512: at line 1

3)Next we removed the current application and database and restored from the previous backup(Before the Delta 8 patch).

4) Analysis for Delta 8 patch

As per error message in worker log worker is looking for object "APPS.AD_ZD_CTX" package, but the object doesnot exists in database, the object is created by "ADZDCTXS.pls,ADZDCTXS.pls". As this object creation is the
part of application(21841299 delta 8 patch). Also we observed that only "ADZDCTXS.pls,ADZDCTXS.pls" are not copying to $AD_TOP/patch/115/sql while copy portion activity of driver u21841299.drv.

5) Before applying the 21841299, we manually created the AD_ZD_CTX package using the "ADZDCTXS.pls,ADZDCTXS.pls" from patch top (21841299).

6)Next we applied the patch as specifed in doc. Which went sucessful without any issues.

$ adop phase=apply patches=21841299 hotpatch=yes

7)Also we analyzied the Patch 20745242 (R12.AD.C.Delta.7

AD_ZD_CTX object is created as part of patch 20745242, so the users didnt got any issue.

Conclusion :: While applying patch 21841299, patch itself is not creating the package AD_ZD_CTX. Due to this issue occured.

Thursday, November 3, 2016

Script to Remove SSL Entries from Contextfile after the EBS clone

The easiest method to disable SSL in Clone, After finishing the clone of Apps Tier Bringdown the Apps Services and run following command

txkrun.pl -script=SetAdvCfg \-appsuser=apps -appspass=<Apps Password> -disable=SSL \-s_webport=<Web port>(Replace your Web Port Number)

The above script will disable all SSL enable configuration in Apps Autoconfig XML file, then run autoconfig on Apps Tier after above command, now the URL will point http.

txkrun.pl -script=SetAdvCfg \-appsuser=apps -appspass=appps -disable=SSL \-s_webport=8001

APP-PO-14142: get_po_parameters-10: ORA-01403: no data found

having these SQL errors while trying to create a Purchase Order when selecting the Operating Unit and any other field.

Please refer metalink note 878682.1

ORA-07445: Exception Encountered: Core Dump [Nstimexp()+28] [Sigsegv]

Problem Description
In the Oracle database alert log following error is encountered.

ORA-07445: exception encountered: core dump [nstimexp()+28] [SIGSEGV] [ADDR:0x58] [PC:0x107D6071C] [Address not mapped to object] []
In the trace file generated for this error inside the 'stack trace' portion the following function codes are listed.

nstimexp ltmdvp sslsstehdlr sighndlr call_user_handler longjmp dbgrip_start_iterat dbgrip_relation_iterator dbgruprac_read_adrc dbgrsc_set_createid

Cause of the Problem
The problem raised due to Oracle bug 6918493.

In 11g database, this is a reintroduction of Bug 2752985 which affected earlier versions of Oracle.

The silly thing is Bug 3934729 has been introduced on Oracle database 11.0.7 to manage this issue.

Solution of the Problem
To implement the solution, execute the following steps:

1. Set sqlnet.expire_time=0 in $ORACLE_HOME/network/admin/sqlnet.ora file on the server.

2. Restart the listener to ensure that these changes take effect.
$ lsnrctl stop
$ lsnrctl start

3. You can also check on Metalink to see if patch 6918493 is available yet.

4. For 11g, apply patch 3934729 if it is available for your OS.

R12 Clone Error - Unable to find PD KSH version

ERROR :: 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 = /u01/*******/db/tech_st/11.2.3/appsutil/clone/bin/../jre/bin:/usr/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin

Solution :: export KSH_VERSION='@(#)PD KSH v5.2.14 99/07/13.2'

How To Compile Oracle Fast Formulas

we can observe the below invalid objects(around 200 +) in database. It never be validated by regular compilation

OWNER OBJECT_TYPE OBJECT_NAME

APPS PACKAGE BODY FFP61926_01010001

APPS PACKAGE BODY FFP61912_01010001

APPS PACKAGE BODY FFP61897_01010001

APPS PACKAGE BODY FFP54823_01012000

APPS PACKAGE BODY FFP58861_01042004

APPS PACKAGE BODY FFP51841_01011990

APPS PACKAGE BODY FFP50581_01011900

APPS PACKAGE BODY FFP55306_01011995

APPS PACKAGE BODY FFP50525_01011900

solution ::::

How To Compile Oracle Fast Formulas [ID 155737.1]

or

$FF_TOP/bin/FFXBCP apps/apps 0 Y %% %%

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file

sqlplus / as sysdba

-bash: sqlplus: command not

Solution:::: switch to database OS user and perform bellow

export ORACLE_HOME=/u05/oracle/prod/db/tech_st/11.1.0/

$ export PATH=$ORACLE_HOME/bin:$PATH

$ export ORACLE_SID=PROD

sqlplus / as sysdba

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Solution:::: export LD_LIBRARY_PATH=$ORACLE_HOME/lib

sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue May 27 06:28:05 2014

FRM-92095: Oracle JInitiator version too low. Please install version 1.1.8.2 or higher

After logging to oracle application you may see above error in forms.

solution :: go to control panel ---> search for java control panel -->click on view --> add below parameter in runtime parameter textbox and check enabled.

-Djava.vendor="Sun Microsystems Inc."

clean your browser cache and restest the issue. :)

Find the apps or sysadmin password in oracle EBS 12 if you lost or forgotten

To find the apps password ::

Step #1 :: sqlplus / as sysdba

Step #2 :: Create Function to decrypt the encrypted password

create or replace FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';

Step#3: Query for Apps password

select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD

--------------------------------------------------------------------------------

ZG3038D54765ACE934DC0A38BEB448D156FC8E6861B0C26794FFA4E9EFBA84297CA7E41E58E78FA7A951ED6670EF445D2131

SELECTapps.decrypt_pin_func('GUEST/ORACLE','ZG3038D54765AC0A38BEB448D156FC8E6861B0C26794FFA4E9EFBA84297CA7E41E58E78FA7A951ED6670EF445D2131') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG3038D54765ACE934DC0A38BEB448D156FC8E6861

--------------------------------------------------------------------------------

GOODBYE

Step #4 : Test apps password

sql>conn apps/goodbye;

Connected.

To find SYSADMIN Password or any Front end user in ORACLE EBS

Step 1 # :: SQL> conn apps/goodbye;

Connected.

Step 2 # :: create the decrypt package

CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2; END get_pwd;

Step# 3:

CREATE OR REPLACE PACKAGE BODY get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;

Step #4: To get user password use below query

SELECT usr.user_name,get_pwd.decrypt((SELECT (SELECT get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd, usertable.encrypted_foundation_password) FROM DUAL) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name =

(SELECT SUBSTR(fnd_web_sec.get_guest_username_pwd,1, INSTR

(fnd_web_sec.get_guest_username_pwd,'/' ) - 1)

FROM DUAL)),

usr.encrypted_user_password) PASSWORD FROM fnd_user usr

WHERE usr.user_name = '&USERNAME';

USER_NAME PASSWORD

--------------------------------------------------------------------------------

SYSADMIN Admin@123