Technology Stack: May 2013

Tuesday, May 28, 2013

Find consuming sql from process id

Get cpu consuming PID from top command and then execute below script to find relevant SQL statements along with SQL ID & Username

prompt "Please Enter The UNIX Process ID"

set lines 200

set long 20000

set pages 0

select

s.username su, sa.SQL_ID, sa.sql_fulltext

from v$process p,

v$session s,

v$sqlarea sa

where p.addr=s.paddr

and s.username is not null

and s.sql_address=sa.address(+)

and s.sql_hash_value=sa.hash_value(+)

and spid=&SPID;

for eg,

[oracle@igrexandc003d02 ~]$ top

top - 09:14:40 up 86 days, 10 min, 3 users, load average: 13.56, 13.70, 14.04

Tasks: 818 total, 14 running, 804 sleeping, 0 stopped, 0 zombie

Cpu(s): 56.4%us, 0.9%sy, 0.0%ni, 42.5%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st

Mem: 148704036k total, 113554696k used, 35149340k free, 1573068k buffers

Swap: 25165816k total, 3712k used, 25162104k free, 53273236k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

21899 oracle 20 0 32.4g 64m 32m R 99.7 0.0 1873:07 oracle

21903 oracle 20 0 32.4g 63m 32m R 99.7 0.0 1826:24 oracle

21905 oracle 20 0 32.3g 64m 32m R 99.7 0.0 1654:55 oracle

21915 oracle 20 0 32.3g 63m 31m R 99.7 0.0 1826:48 oracle

[oracle@igrexandc003d02 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 28 09:14:43 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> prompt "Please Enter The UNIX Process ID"

"Please Enter The UNIX Process ID"

SQL> set lines 200

set long 20000

SQL> SQL> set pages 0

SQL> select

2 s.username su, sa.SQL_ID, sa.sql_fulltext

3 from v$process p,

4 v$session s,

5 v$sqlarea sa

6 where p.addr=s.paddr

and s.username is not null

7 8 and s.sql_address=sa.address(+)

9 and s.sql_hash_value=sa.hash_value(+)

10 and spid=&SPID;

Enter value for spid: 21899

old 10: and spid=&SPID

new 10: and spid=21899

OBI_RO byk85pccx0ydb

select T454950.MASTER_VALUE_CHAR as c1,

count(distinct T436164.CHANGE_ISSUE_NUM) as c2

from

WC_PRODUCTIVITY_PROJ_D T436164 /* Dim_WC_PRODUCTIVITY_PROJ_D */ ,

WC_PRODUCTIVITY_PROJ_SNAP_D T454994 /* Dim_WC_PRODUCTIVITY_PROJ_SNAP_D */ ,

WC_PRODUCTIVITY_PROJ_CODE_D T455950 /* Dim_WC_PRODUCTIVITY_PROJ_CODE_D_Fina

ncial_Unit */ ,

WC_PRODUCTIVITY_PROJ_NOTE_F T454975 /* Fact_WC_PRODUCTIVITY_PROJ_NOTE_F */

WC_PRODUCTIVITY_PROJ_CODE_D T454950 /* Dim_WC_PRODUCTIVITY_PROJ_CODE_D_Note

*/ ,

W_PROD_CAT_DH T441548 /* Dim_W_PROD_CAT_DH_Prod_Proj */

where ( T436164.CHANGE_ISSUE_NUM = T454975.CHANGE_ISSUE_NUM and T436164.PROD_CA

T_WID = T441548.ROW_WID and T436164.SNAPSHOT_DT_WID = T454994.SNAPSHOT_DT_WID an

d T436164.FINANCIAL_UNIT_WID = T455950.ROW_WID and T436164.SAVINGS_TYPE = 'Hard

Savings P&L' and T436164.VARIANCE_TYPE = 'Productivity' and T441548.L6ANC_PRODCA

T_NAME = 'Valves & Tube Fabrication' and T441548.L7ANC_PRODCAT_NAME = 'Valves &

Tube Fabrication' and T441548.HIERARCHY_CODE = 'AUTO_CUSTOM_CATEGORY1' and T4549

50.ROW_WID = T454975.NOTE_TYPE_WID and T454994.SNAPSHOT_NAME = '2013-05-27 (Dail

y Refresh)' and T455950.GROUP_NAME = 'Asia-Pac' and T455950.MASTER_CODE <> 'ZZ -

Hussmann' and (T436164.PROJECT_TYPE in ('Material Other / In bound Logistics',

'Material Price', 'Material VAVE - external (Supplier driven)', 'Material VAVE -

internal (IR driven)')) and T454975.CREATED_ON_DT >= TO_DATE('2012-05-07 00:00:

00' , 'YYYY-MM-DD HH24:MI:SS') )

group by T454950.MASTER_VALUE_CHAR

order by c1

SQL>

Tuesday, May 21, 2013

block_detail.sql

To find out blocker & waiter in RAC Instance (specially useful in EBS)

set lines 200
col WMOD for a20
col WCID for a20
col BCID for a20
col BMOD for a20
col BLOCKER for a12
col WAITER for a10
select vsb.sid bsid, vsb.inst_id binst, vsb.username blocker, vsb.client_identifier bcid, vsb.module bmod,
vsw.sid wsid, vsw.inst_id winst, vsw.username waiter, vsw.client_identifier wcid, vsw.module wmod,
trunc(vlw.ctime/60) minutes
from (select inst_id,sid, id1, id2, ctime from gv$lock where request > 0) vlw,
(select inst_id,sid, id1, id2, ctime from gv$lock where block > 0) vlb,
gv$session vsb,
gv$session vsw
where vsw.sid=vlw.sid
and vsb.sid=vlb.sid
and vsb.inst_id=vlb.inst_id
and vsw.inst_id=vlw.inst_id
and vlb.id1=vlw.id1
and vlb.id2=vlw.id2
order by blocker, minutes desc
/

BSID BINST BLOCKER BCID BMOD WSID WINST WAITER WCID WMOD MINUTES
---------- ---------- ------------ -------------------- -------------------- ---------- ---------- ---------- -------------------- -------------------- ----------
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 13861 1 APPS MMRNKA e:INV:frm:WSHFRREL 23
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 5696 1 APPS M-JTKADLEC e:INV:frm:INVTOTRX 23
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 6908 1 APPS MMRNKA e:INV:frm:WSHFRREL 20
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 8866 1 APPS MMRNKA e:INV:frm:WSHFRREL 16
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 11372 1 APPS SMPATEKA e:WIP:frm:WIPTXMAT 16
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 3773 1 APPS AJPATEL e:INV:frm:WSHFRREL 16
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 39 1 APPS CHAUHANK e:INV:frm:WSHFRREL 14
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 77 1 APPS NSHARMA e:INV:frm:WSHFRREL 8
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 9423 1 APPS IRBJEV e:INV:frm:INVTOTRX 6
3204 2 APPS JSTANIK e:INV:frm:WIPTXCFM 1917 2 APPS PVOJACEK e:INV:bes:xxont.orac 1
le.apps.ont.OrderLin

Sunday, May 5, 2013

-manifest option in admrgpch

While applying more than 100 patches, you cannot unzip all the patches and put it in source directory instead you can do use of -manifest option of admrgpch command to merge those patches with downloaded zip files from metalink.

The sequence of these parameters are important !!!

[ebspat@igrexanwh002cn04 nonad]$ admrgpch -s src -manifest /u08/patches/r12/oks_csp_modules/nonad/src/patchlist.txt -d dest -merge_name oks_csp_merge

Put all zipped patches in src directory and keep list of those patches in patchlist.txt

unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p10097694_R12.OKS.B_R12_GENERIC.zip..
unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p11806604_R12.OKS.B_R12_cs.zip..
unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p11806604_R12.OKS.B_R12_d.zip..
unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p11806604_R12.OKS.B_R12_esa.zip..
unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p11806604_R12.OKS.B_R12_e.zip..
unzipping /u08/patches/r12/oks_csp_modules/nonad/src/p11806604_R12.OKS.B_R12_f.zip..
...
...
...
...
...
...

-- Processing patch: src/12411685_ZHS
-- Processing file: src/12411685_ZHS/u12411685.drv
-- Done processing file: src/12411685_ZHS/u12411685.drv
-- Done processing patch: src/12411685_ZHS

Copying files...

5% complete. Copied 161 files of 3202...
10% complete. Copied 321 files of 3202...
15% complete. Copied 481 files of 3202...
20% complete. Copied 641 files of 3202...
25% complete. Copied 801 files of 3202...
30% complete. Copied 961 files of 3202...
35% complete. Copied 1121 files of 3202...
40% complete. Copied 1281 files of 3202...
45% complete. Copied 1441 files of 3202...
50% complete. Copied 1601 files of 3202...
55% complete. Copied 1762 files of 3202...
60% complete. Copied 1922 files of 3202...
65% complete. Copied 2082 files of 3202...
70% complete. Copied 2242 files of 3202...
75% complete. Copied 2402 files of 3202...
80% complete. Copied 2562 files of 3202...
85% complete. Copied 2722 files of 3202...
90% complete. Copied 2882 files of 3202...
95% complete. Copied 3042 files of 3202...
100% complete. Copied 3202 files of 3202...

Character-set converting files...

200 unified drivers merged.

Patch merge completed successfully

Please check the log file at ./admrgpch.log.
[ebspat@igrexanwh002cn04 nonad]$

Output Post Processor Log File Contains java.io.FileNotFoundException (No such file or directory)

[ebsi1a@igrexanwh002cn05 log]$ ls -rtl *FNDOPP*
-rw-r--r-- 1 ebsi1a dba 1186 Apr 17 09:30 FNDOPP44572.txt
-rw-r--r-- 1 ebsi1a dba 1186 Apr 17 09:30 FNDOPP44571.txt
-rw-r--r-- 1 ebsi1a dba 1186 Apr 17 09:30 FNDOPP44570.txt
-rw-r--r-- 1 ebsi1a dba 1186 Apr 17 09:32 FNDOPP44573.txt
-rw-r--r-- 1 ebsi1a dba 5581 Apr 18 11:09 FNDOPP44730.txt
-rw-r--r-- 1 ebsi1a dba 13341 Apr 18 11:29 FNDOPP44731.txt
-rw-r--r-- 1 ebsi1a dba 3998 Apr 18 11:29 FNDOPP44732.txt
-rw-r--r-- 1 ebsi1a dba 15165 Apr 18 11:32 FNDOPP44729.txt
[ebsi1a@igrexanwh002cn05 log]$ vi FNDOPP44731.txt

[041813_094235103][][EXCEPTION] [DEBUG] [sun.cpu.isalist]:[]
[4/18/13 9:42:38 AM] [44731:RT2214394] Output file was found but is zero sized - Deleted
[4/18/13 9:42:38 AM] [UNEXPECTED] [44731:RT2214394] java.io.FileNotFoundException: /ebsi1a/app/applmgr/IREBSI1A/comn/temp/xdoHXsE2Rzqiu041813_0942381350.fo (No such file or directory)

Issue was with XML Publisher temporary directory path.

Solution: Output Post Processor Log File Contains java.io.FileNotFoundException (No such file or directory) Error [ID 463388.1]

Rsync command usage for Application files while Cloning

Run rsync commands from target application node

/usr/bin/rsync --delete -av --exclude-from=/asccnv/rsync_appl_top_exclude -e ssh ascuat@igrexandc002cn05:/ascuat/app/applmgr/ascuat/appl/ /asccnv/app/applmgr/IRasccnv/appl/ --rsync-path=/usr/bin/rsync

/usr/bin/rsync --delete -av --exclude-from=/asccnv/rsync_comn_top_exclude -e ssh ascuat@igrexandc002cn05:/ascuat/app/applmgr/ascuat/comn/ /asccnv/app/applmgr/IRasccnv/comn/ --rsync-path=/usr/bin/rsync

/usr/bin/rsync --delete -av --exclude-from=/asccnv/rsync_techst_exclude -e ssh ascuat@igrexandc002cn05:/ascuat/app/applmgr/ascuat/techst/ /asccnv/app/applmgr/IRasccnv/techst/ --rsync-path=/usr/bin/rsync

[asccnv@igrexanwh002cn06 ~]$ cat /asccnv/rsync_appl_top_exclude
- admin/log/**
- admin/out/**
- *.log
- *.out
- *.req
- *.tmp
- *.rti
- *.PDF
- *.tar.gz
- *.EXCEL

[asccnv@igrexanwh002cn06 ~]$ cat /asccnv/rsync_comn_top_exclude
- admin/log/**
- admin/out/**
- temp/**
- ptemp/**
- _pages/**
- *.log
- *.out
- *.req
- *.mgr
- *.tmp
- FNDCP*.txt
- WFM*.txt
- *.rti
- *.PDF
- *.tar.gz
- *.EXCEL

[asccnv@igrexanwh002cn06 ~]$ cat /asccnv/rsync_techst_exclude
- *.log
- *.out
- *.tmp
- *.tar.gz

Unable to authenticate session

we are using the Microsoft Internet Explorer 8 (IE 8) to access your Release 12.1.x E-Business-Suite Instances, for example PROD, TEST and DEV.

When we logout from DEV Instance and want to login to the TEST- or PROD-Instance it happens, that we see following error :

...
Unable to authenticate session
...

we need to start the Internet Explorer with the option '-noframemerging', as described in Note 389422.1 - 'Recommended Browsers for Oracle E-Business Suite Release 12' under the Known issues.

Multiple Sessions

Previous releases of Internet Explorer have allowed users to logon to the same Oracle E-Business Suite environment concurrently from the same desktop by launching each application session through a new browser session. By default the ‘Session Merging’ feature within IE8 and IE9 prevents this functionality from working by default. This is because the same session id is used when accessing the same Oracle E-Business Suite environment despite opening a new browser window to launch the second session. This can result in unusual behavior with invalid sessions causing errors such as “Your login session has become invalid" and failures to launch forms causing errors such as “FRM-92050: Failed to connect to the server: /forms/formservlet: -1"

To retain the use of multiple sessions to the same Oracle E-Business Suite environment with IE8 and higher please use one of the workarounds below to ensure each browser session remains independent;

Run from the command line adding the -noframemerging parameter e.g. Start -> Run -> iexplore -noframemerging
Create a new IE8 icon adding the -noframemerging parameter e.g.
Start -> Program Files
Right click on the Internet Explorer icon and select Properties
Add -noframemerging to the end of the link in the Target field e.g."C:\Program Files\Internet Explorer\iexplore.exe" -noframemerging
Click the OK button to close the window
This icon may then be copied to the desktop if desired

PRINTER ISSUE: APP-FND-00314: INVALID PRINTER (NOPRINT) AND PRINT STYLE (LANDWIDE240) COMBINATION

Issue: APP-FND-00314: Invalid printer(noprint_ and print style (LANDWIDE240) combination

Accrual Reconciliation Report Errors With APP-FND-00314 Invalid Printer And Print Style Combination [ID 564009.1] (was for 11.5.10.2)
========================================================================

Cause
-------
The printer is defined with a certain type but that type does not have the different combinations of styles and drivers that the concurrent request uses.

Solution
--------
To implement the solution, please execute the following steps:
1. System Administrator responsibility
Install -> Printer -> Register
2. Query for 'noprint' Note the value in the Type column (For example: HPLJ4SI )
3. Click Printer Types button
4. Query for value returned from Register screen
5. Add a new row with Style = 'LANDWIDE240' , Driver = 'PASTA_LANDWIDE_240'
6. Save

Print device 'noprint' will now have the default Print Style used by the concurrent request. Retest the report.
References

NOTE:334156.1 - Printing Problems Involving Accrual Reconciliation and Accrual Write Off Reports

How to take trace for Concurrent Program ?

1. Enable Trace at the Report Definition.
Go to System Administrator -> Concurrent Programs -> Define , Query the report and check the 'Enable trace' check box

( Navigate to:
Profile->system
Query on the Profile Option: "Concurrent: Allow Debugging"

This should be set to 'yes' at 'Site' level.

If it isn't set, then set it, then logout and bounce the APPS services.

The 'Debug Options' button on the concurrent program will now be enabled. )

2. Run the report for Step 1. above

3. Get the request id from below query. i.e. Request id = 555555

select fcr.request_id "Request ID"
--, fcr.oracle_process_id "Trace ID"
, p1.value||'/'||lower(p2.value)||'_ora_'||fcr.oracle_process_id||'.trc' "Trace File"
, to_char(fcr.actual_completion_date, 'dd-mon-yyyy hh24:mi:ss') "Completed"
, fcp.user_concurrent_program_name "Program"
, fe.execution_file_name|| fe.subroutine_name "Program File"
, decode(fcr.phase_code,'R','Running')||'-'||decode(fcr.status_code,'R','Normal') "Status"
, fcr.enable_trace "Trace Flag"
from fnd_concurrent_requests fcr
, v$parameter p1
, v$parameter p2
, fnd_concurrent_programs_vl fcp
, fnd_executables fe
where p1.name='user_dump_dest'
and p2.name='db_name'
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fe.application_id
and fcp.executable_id=fe.executable_id
and ((fcr.request_id = &request_id
or fcr.actual_completion_date > trunc(sysdate)))
order by decode(fcr.request_id, &request_id, 1, 2), fcr.actual_completion_date desc;

--you will be prompted to enter the request_id;

4. In SQL: select value from v$parameter where name = 'user_dump_dest'

5. Go to directory in Step 4. above.

6. grep ‘555555’ *.trc

7. Find the file name from step 6. above

8. Upload the raw trace file from step 4. above , and the tkprofed trace as well.

$ tkprof <RAW TRACE> <output> explain=apps_uname/apps_pwd sys=no sort=prsela,exeela,fchela

Purge Inactive Sessions - Apps R12

This article illustrates how to run "Purge Inactive Sessions" concurrent program in Oracle E-Business suite R12.1 This program will purge all session which are not active.

"Purge Inactive Sessions" concurrent program is not included in request group of system administrator responsibility. So first we need to add this request to system administrator responsibility request group.

Navigation:

login as sysadmin >

System Administrator > Security > Responsibility > Request

- Here search for Group "System Administrator"

- Add concurrent program "Purge Inactive Sessions"

- save

- Submit new request "Purge Inactive Sessions""

- After successful completion of this request then check no. of records in table icx_sessions.

Hope this helps :)

Database Initialization Parameters for R12

Database Initialization Parameter Sizing

This provides sizing recommendations based on the active Oracle E-Business Suite user counts. The following table should be used to size the relevant parameters:

Parameter Name	Development or Test Instance	11-100 Users	101-500 Users	501-1000 Users	1001-2000 Users
processes	200	200	800	1200	2500
sessions	400	400	1600	2400	5000
sga_target ^{Footnote 1}	1G	1G	2G	3G	14G
shared_pool_size (csp)	N/A	N/A	N/A	1800M	3000M
shared_pool_reserved_size (csp)	N/A	N/A	N/A	180M	300M
shared_pool_size (no csp)	400M	600M	800M	1000M	2000M
shared_pool_reserved_size (no csp)	40M	60M	80M	100M	100M
pga_aggregate_target	1G	2G	4G	10G	20G
Total Memory Required ^{Footnote 2}	~ 2 GB	~ 3 GB	~ 6 GB	~ 13 GB	~ 34 GB

Reference: Database Initialization Parameters for Oracle E-Business Suite Release 12 [ID 396009.1]

Script to get Application user passwords !!! don't misuse it :)

Connect to APPS user and create following package to get Application user password.

Package Specification:
------------------------------------------------------------------------------------------------------------

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

Package Body:
------------------------------------------------------------------------------------------------------------

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;
/

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';

###############################################################################

How to get APPS Password, that's very interesting...

Step #1 Connect as system or sys User in the database.

[oracle@~~inblrdrdbadm01~~ ~]$ sqlplus system

SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 25 21:24:58 2013

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Step#2: Create Function to decrypt the encrypted password

SQL> set linesize 200 long 300
SQL> create 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';
2 /

Function created.

Step#3: Query for password

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

ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------------------------------------------
ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45

Step#4: Get the Apps password using encrypted guest password

SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG040B6D2CDF90B3493544F3BC2EFD960DD2F5C5D230E6F2C14D59E59C9F258726756BD7B9227552996F63F8795EAFE62F45')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EBSDEVCLONE

Step#5: Test apps password

SQL> conn apps/ebsdevclone
Connected.
SQL> show user
USER is "APPS"
SQL>

I hope, this could be important to get APPS / Any Application User password in-case of loss.

Cheeeers !!!