Archive for August, 2008

SQL Query to view sql for a OS Pid / process

Posted on August 29, 2008. Filed under: DBA/Admin Tips, Scripts | Tags: , , , , |

Select a.sid,a.serial#,b.pid,c.sql_text,a.module,
to_number(b.spid) PROCESSID,
substr(a.username, 1, 12) username
From v$session a,
v$process b,v$sql c
Where a.paddr = b.addr
and a.sql_address = c.address
and b.spid = ‘&spid’
/

Advertisements
Read Full Post | Make a Comment ( 1 so far )

SQL Query to view sql of a session

Posted on August 25, 2008. Filed under: DBA/Admin Tips, Diagnostics, R12, RAC, Scripts, TroubleShooting | Tags: , , , , |

SELECT
T.sql_text
FROM
v$session S,
v$sqltext_with_newlines T
WHERE
S.sid = &sid
AND S.serial# = &serial
AND S.sql_address = T.address
AND S.sql_hash_value = T.hash_value
ORDER BY T.piece
/

Replace v$session with gv$session if the database is running on RAC configuration.

cheers,
Ram.

Read Full Post | Make a Comment ( 1 so far )

SQL Query to list running concurrent requests

Posted on August 25, 2008. Filed under: DBA/Admin Tips, R12, RAC, Scripts, TroubleShooting | Tags: , , , |

col user_name format a20 word_wrapped
col ProgName format a25 word_wrapped
col requestId format 99999999
col StartDate format a20 word_Wrapped
col OS_PROCESS_ID format a6
col ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
set lines 200

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,’DD-MON-YYYY HH24:MI:SS’) StartDate,
request_id RequestId,
(sysdate – actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = ‘US’
and fcr.phase_code = ‘R’
and fcr.status_code = ‘R’
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC
/

Replace v$session with gv$session if the database is RAC.

Click here to extract the sql being executed by a concurrent program

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 2 so far )

Cloning or Refresh – What did I do?

Posted on August 25, 2008. Filed under: Clone, DBA/Admin Tips | Tags: , , |

Most of us use these terms, interchangeably. Is there a difference in the actual meaning of these terms, Yes, it does.

Clone – Describes when you create a new environment exactly as that of an existing environment. Eg. RAC to RAC, Multi Node Appl Top to Multi Node Appl Top

Refresh – Describes wherein you already have an environment (same replica or different) and you just sync the data between the instances. (eg. RAC to Non-Rac, Multi Node Appl Top to Single Node Appl Top)

So what did you do over the weekend, cloning or refresh 

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

Post Clone – Forms Error – oracle.forms.engine.RunformException: Forms session failed during startup: no response from runtime process

Posted on August 12, 2008. Filed under: Clone, Diagnostics, R12, TroubleShooting | Tags: , , , , , , |

We recently did a clone and everything went well – autoconfig ran properly – services started sucessfully – but when we selected a responsibility which will launch a form it errored out with below message :

formsweb: Forms session exception stack trace:
oracle.forms.engine.RunformException: Forms session failed during startup: no response from runtime process
at oracle.forms.servlet.RunformProcess.connect(Unknown Source)
at oracle.forms.servlet.RunformProcess.dataToRunform(Unknown Source)
at oracle.forms.servlet.RunformSession.dataToRunform(Unknown Source)
at oracle.forms.servlet.ListenerServlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)

Checking the log file revealed more information

formsweb: Forms session aborted: runtime process failed during startup with errors /usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnftboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnfoboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnfoboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnfhboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnfhboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nnflboot’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘nttini’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘ntusini’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘ntpini’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.
/usr/lib/hpux32/dld.so: Unsatisfied code symbol ‘ntzini’ in load module ‘/opt/var/applbin/apps/tech_st/10.1.2/lib32/libclntsh.so.10.1’.

As a normal standard practice we normally recreate all the symbolic links under APPL_TOP, but still we got this error. Now heres the

Solution :

Stop the forms services (if possible run adopmnctl.sh stopall)
cd $ORACLE_HOME/forms/lib32
make -f ins_forms.mk install
restart the services.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 3 so far )

Missing Libraries during Post Clone Stage (adcfgclone.pl)

Posted on August 12, 2008. Filed under: Clone, Configuration, R12, Uncategorized | Tags: , , |

We recently faced this interesting issue during the post clone stage. All the .a and .o library files were removed during execution of adcfgclone.pl which ultimately results in relinking errors of forms and reports … strange isnt it …

Both Source and Target boxes were of the same OS configuration : HP UX 11.23 Itanium 64 Bit

For some reason adcfgclone.pl was doing a platform migration instead of normal cloning.

The culprit was this file – SystemCheck.java ($AD_TOP/java/oracle/apps/ad/util) version 120.11.12000000.5, which gets copied to $COMMON_TOP/clone/jlib/java/oracle/apps/ad/util during adpreclone.pl on the Source Instance, which is later used to verify the target server OS Platform during execution of adcfgclone.pl on the target instance.

Solution

Apply Patch p6679053_R12.TXK.A_R12_GENERIC.zip

This patch brings in a new version of SystemCheck.class file (SystemCheck.java 120.11.12000000.7) which identifies and stores the host platform information as HPUX_IA64 against the previous version of the same file (SystemCheck.java 120.11.12000000.5) which stores as HPUX_IA_64, which results in removing of .a & .o files (Platform migration).

cheers,
OraclePitStop

Read Full Post | Make a Comment ( None so far )

ASO_ORDER_FEEDBACK_T is huge !!!

Posted on August 12, 2008. Filed under: DBA/Admin Tips |

Friends,
As part of regular house-keeping activities, we were checking for objects with huge sizes in our 11i10 apps instance and came across this object : ASO_ORDER_FEEDBACK_T

This is basically an “Order Capture” queue table that is maintained by Order Management. It works in a broadcast-subscribe concept. Order Management broadcasts evets (messages) via this queue and ANY (built-in or custom) application can subscribe to this queue to receive those messages.

In our environment, the table had millions of rows and was quite huge (12Gb). This was taking bulk of the space in the APPS_TS_QUEUES tablespace. When i was checking metalink to find ways to purge this queue, i came across this informative metalink note : 181410.1

To check, which modules have subscribed to this queue, run the SQL

SQL > select consumer_name, msg_state, count(*) from aso.aq$aso_order_feedback_t group by consumer_name, msg_state ;

In our case , we had  OZF || READY || 4555658 which means only OZF module has subscribed to this queue and has NOT processed around 4.5 million records. We realized we maynot be using this and checked with the Functional group. Since they said NO, we thought of purging it.

How to purge?

Need to do 2 things.

1) Unsubscribe OZF module subscription. To do this, login to apps as “Quoting Sales Manager” resp and navigate as Setup -> Quick Codes -> Query for “ASO_ORDER_FEEDBACK_CRM_APPS”. You will see OZF’s subscription (result of above SQL query). Just disable / delete the record there.

2) Follow step 4 of the above metalink document to drop and recreate the queue.

As always, do this on a test environment , get a good functional (Order Mgmt,Shipping etc) flow testing done before thinking to do this in PROD.

We saved close to 12G. Perhaps, you could save more instead of adding another datafile to APPS_TS_QUEUES tablespace !!

Hope this tip would help.

Cheers,

OPS Team

Read Full Post | Make a Comment ( 4 so far )

User ‘SYSADMIN’ does not have access to notification. ORA-06512: at APPS.WF_ADVANCED_WORKLIST

Posted on August 7, 2008. Filed under: Configuration, DBA/Admin Tips, R12, Workflow Mailer | Tags: , , , |

Users reported that they got below error message when they were trying to view the notifications while logged in as SYSADMIN user on a development instance.

ORA-20002: 3207: User ‘SYSADMIN’ does not have access to notification .
ORA-06512: at “APPS.WF_ADVANCED_WORKLIST”, line 82

A quick check on the Workflow System Administrator Value (How to check this value) revealed that the value is not set to SYSADMIN and its set to “Workflow Administrator Web (New)”.

Is this the cause of the problem, NO.

The problem was SYSADMIN user didnt have this responsibility (Workflow Administrator Web (New)) assigned to him. To resolve the issue, Assign the responsibility using System Administrator -> Security : User -> Define -> SYSADMIN user.

cheers,
OraclePitStop

Read Full Post | Make a Comment ( 1 so far )

How to enable users to view other users workflow notifications

Posted on August 7, 2008. Filed under: Configuration, DBA/Admin Tips, R12, Workflow Mailer | Tags: , , |

By default and design, only SYSADMIN user can view other users notifications. What if a requirement comes to enable everyone/certain group of people to view everyones notifications … this might sound crazy for a production environment but its a valid request on a Development environment.

Ok coming to the technicalities,

Step 1: Login as SYSADMIN User

Step 2: Select System Administrator or Workflow Administrator for Web Applications ->
-> Workflow : Administrator Workflow
-> Administration
-> In the admin page you will see this message “Set * to grant admin privilege to all users”
-> Put * in the text box next to “Workflow System Administrator” and click Apply.

Thats it, now anyone can see anybodys notifications.

Personally im not for setting it as ” * ” , i would rather set it to a responsibility viz., “Workflow Administrator Web (New)” and assign the responsibility to the respective group of development group to view the notifications.

cheers,
OraclePitStop.

Read Full Post | Make a Comment ( 1 so far )

Liked it here?
Why not try sites on the blogroll...