HOWTO (ebiz) - Howto to restart OPP without having to restart full Concurrent Managers

This post describes the steps to restart OPP processes manually without having to take a full outage on concurrent managers.

Sometimes OPP does not re-start normally when DBA tries to re-start OPP from the Form "Concurrent Manager -> Administer" form.

Login to APPS and navigate to Concurrent Managers/Administer form

Query for %Output Post Processor% manager.

You will see Actual=4 and Target=4 processes

Click on Restart Button. Make sure Oputput Post Processor is selected.

Wait for at least 2 minutes.

You will see Target=0 and Actual=0

If Actual is still 4, then proceed with steps below to manual kill OPP processes.

Identify OPP process in the UNIX
Login to APPTIER server and identify the OPP processes.

[appl@papp1 log]$ ps -ef |grep FNDOPP | grep -v grep
appl  6947  7203  2 10:31 ?        00:01:21 /u01/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/bin/java -DCLIENT_PROCESSID=6947 -server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx1024m -Ddbcfile=/u01/oracle/PROD/inst/apps/PROD_papp1/appl/fnd/12.0.0/secure/PROD.dbc -Dcpid=486574 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/oracle/PROD/apps/logs/appl/conc/log/FNDOPP486574.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController
appl  6948  7203  3 10:31 ?        00:01:30 /u01/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/bin/java -DCLIENT_PROCESSID=6948 -server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx1024m -Ddbcfile=/u01/oracle/PROD/inst/apps/PROD_papp1/appl/fnd/12.0.0/secure/PROD.dbc -Dcpid=486575 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/oracle/PROD/apps/logs/appl/conc/log/FNDOPP486575.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController
appl  6949  7203  2 10:31 ?        00:01:06 /u01/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/bin/java -DCLIENT_PROCESSID=6949 -server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx1024m -Ddbcfile=/u01/oracle/PROD/inst/apps/PROD_papp1/appl/fnd/12.0.0/secure/PROD.dbc -Dcpid=486576 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/oracle/PROD/apps/logs/appl/conc/log/FNDOPP486576.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController
appl  6953  7203  0 10:31 ?        00:00:27 /u01/oracle/PROD/apps/tech_st/10.1.3/appsutil/jdk/bin/java -DCLIENT_PROCESSID=6953 -server -Xmx384m -XX:NewRatio=2 -XX:+UseSerialGC -Doracle.apps.fnd.common.Pool.leak.mode=stderr:off -verbose:gc -mx1024m -Ddbcfile=/u01/oracle/PROD/inst/apps/PROD_papp1/appl/fnd/12.0.0/secure/PROD.dbc -Dcpid=486577 -Dconc_queue_id=6269 -Dqueue_appl_id=0 -Dlogfile=/u01/oracle/PROD/apps/logs/appl/conc/log/FNDOPP486577.txt -DLONG_RUNNING_JVM=true -DOVERRIDE_DBC=true -DFND_JDBC_BUFFER_MIN=1 -DFND_JDBC_BUFFER_MAX=2 oracle.apps.fnd.cp.gsf.GSMServiceController

Kill OPP processes

By Killing the processes you are forcing OPP process to stop. ICM will detect all OPP processes were found dead and Manually DBA has to Activate OPP manager from the Administer (concurrent managers) form.

$ kill -9 6953  6949  6948  6947

Tail the Main concurrent Manager log and confirm OPP processes have been found dead

$ cd $APPLCSF/log/PROD_1017.mgr

NOTE 1017 is the month and date of the last time concurrent managers started. Output is is a follows. You need to confirm all four processes are found dead.

                   Process monitor session started : 10-NOV-2011 10:29:13
Found dead process: spid=(7307), cpid=(485567), Service Instance=(6269)
Found dead process: spid=(7295), cpid=(485566), Service Instance=(6269)
Found dead process: spid=(7278), cpid=(485565), Service Instance=(6269)
Found dead process: spid=(7268), cpid=(485564), Service Instance=(6269)

Confirm Actual and target processes count is 0

Navigate to Concurrent Managers/Administer form and query for %Output Post Processor% manager.

You will see Actual=0 and Target=0 processes

Click on Activate Button. Make sure Oputput Post Processor is selected.

Wait for at least 2 minutes.

You will see Target=4 and Actual=4

Tail the Main concurrent Manager log and confirm OPP processes have started

$ cd $APPLCSF/log/PROD_1017.mgr

NOTE 1017 is the month and date of the last time concurrent managers started. Output is a follows. You need to confirm all four processes have started.

                 Process monitor session started : 10-NOV-2011 10:31:50
Starting FNDCPOPP Concurrent Manager               : 10-NOV-2011 10:31:51
Starting FNDCPOPP Concurrent Manager               : 10-NOV-2011 10:31:51
Starting FNDCPOPP Concurrent Manager               : 10-NOV-2011 10:31:51
Starting FNDCPOPP Concurrent Manager               : 10-NOV-2011 10:31:52

Check OPP log files are processing concurrent requests post actions correctly

[appl@papp1 log]$ $ cd $APPLCSF/log/FNDOPP*

check timestamp, normally last four processes

[appl@papp1 log]$ ls -lrt FNDOPP*
-rw-rw-r-- 1 appl dba 2836639 Nov  9 20:10 FNDOPP485566.txt
-rw-rw-r-- 1 appl dba 5704431 Nov  9 20:10 FNDOPP485565.txt
-rw-rw-r-- 1 appl dba 1316142 Nov 10 09:09 FNDOPP485567.txt
-rw-rw-r-- 1 appl dba 1016488 Nov 10 10:21 FNDOPP485564.txt
-rw-rw-r-- 1 appl dba   43403 Nov 10 11:59 FNDOPP486577.txt
-rw-rw-r-- 1 appl dba   47016 Nov 10 11:59 FNDOPP486576.txt
-rw-rw-r-- 1 appl dba   37590 Nov 10 11:59 FNDOPP486575.txt
-rw-rw-r-- 1 appl dba   42614 Nov 10 11:59 FNDOPP486574.txt

[appl@papp1 log]$ /sbin/fuser FNDOPP486574.txt FNDOPP486575.txt FNDOPP486576.txt FNDOPP486577.txt
FNDOPP486574.txt:     6947
FNDOPP486575.txt:     6948
FNDOPP486576.txt:     6949
FNDOPP486577.txt:     6953

Output file FNDOPP486574.txt

[11/10/11 10:34:43 AM] [486574:RT10109319] Executing post-processing actions for request 10109319.
[11/10/11 10:34:43 AM] [486574:RT10109319] Starting XML Publisher post-processing action.
[11/10/11 10:34:43 AM] [486574:RT10109319]
Template code: X142R
Template app:  XC
Language:      en
Territory:     null
Output type:   PDF
...
...
...
[11/10/11 10:34:44 AM] [486574:RT10109319] XML Publisher post-processing action complete.
[11/10/11 10:34:44 AM] [486574:RT10109319] Completed post-processing actions for request 10109319.

HOWTO (Oracle DB) - Monitor the progress and ETA of your RMAN Backup or Duplicate

Have you ever wondered how good or bad your RMAN backup or duplicate is performing?

This happened to me very often, backup and restore or duplicate operations are business critical activities. Normally, when a critical restore of your Production database is happening, everyone is looking over you shoulder asking "Is it done? What is the ETA?". These are questions that sometimes are hard to answer, specially if you have slow backup infrastructure or slow disk or Tapes.

The good thing is that Oracle provides all the information required to predict the ETA of an RMAN operation. All the information in there in the dictionary views. I have make my life easier by creating a shell script that gives you a full picture of your RMAN operation.

With this script you tell:

• When the operation will be finished.
• How much MBytes have been done.
• What is the current throughput/sec of the operation
• and mainly, give that information to your manager watching over your shoulder every second!!!

Output of the script:

$ . oraenv

$ bash rman_duplicate_monitor_eta.sh 

 
-------- RMAN: timing information - Tue May 15 22:05:47 EST 2012 ----------
 
OPNAME                              SOFAR  TOTALWORK   PCT_DONE    ELA_MIN    ETA_MIN START_TIME          FINISH_TIME
------------------------------ ---------- ---------- ---------- ---------- ---------- ------------------- -------------------
RMAN: incremental datafile bac        638        656      97.26          1          0 15-05-2012 22:02:30 15-05-2012 22:05:47
RMAN: aggregate input              737488     738214       99.9        5.2          0 15-05-2012 21:59:13 15-05-2012 22:05:47
RMAN: incremental datafile bac        640          0        100          1            15-05-2012 22:02:30
RMAN: aggregate output             519231          0        100        1.9            15-05-2012 22:02:30
 
'DUPLICATE/RESTORETHROUGHPUT MBYTES_SOFAR MBYTES_PER_SEC NAME
---------------------------- ------------ -------------- --------------------------
DUPLICATE/RESTORE THROUGHPUT         22.3            .07 physical write total bytes
 
'BACKUPTHROUGHPUT MBYTES_SOFAR MBYTES_PER_SEC NAME
----------------- ------------ -------------- --------------------------
BACKUP THROUGHPUT         5868          50.59 physical read total bytes

Shell Script code below:

#!/bin/bash
# $Id: ora_watch.sh 24 2010-03-16 00:18:42Z cricci $
#=========================================================================+
#  Copyright (c) 2009 Christian Daniel Ricci, Sydney, Australia           |
#                          All rights reserved.                           |
#=========================================================================+
# FILENAME
#       rman_duplicate_monitor_eta.sh.sh
#
# DESCRIPTION
#       Monitor RMAN operations
#
# HISTORY
#       CR - 16-Mar-2010 - Created
#
#=========================================================================*/

while [ 1 -eq 1 ]; do

ps -ef | grep rman | grep -v grep > /dev/null

[ $? -eq 1 ] && exit 0

echo "-------- RMAN: timing information - `date` ----------"

sqlplus -s '/as sysdba' <<EOF

col opname for a30

col message for a30 wra on

col units for a15

set lin 300 pages 1000 feed off echo off

alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

select OPNAME,

--message,

SOFAR,TOTALWORK,round((SOFAR*100/decode(TOTALWORK,0,SOFAR,TOTALWORK)),2) PCT_DONE,

--UNITS,

round(elapsed_seconds/60,1) ela_min,round(time_remaining/60,1) eta_min,

START_TIME,round(time_remaining/60,1)/60/24+sysdate finish_time

 from v\$session_longops

where

OPNAME like 'RMAN%'

and SOFAR != TOTALWORK

order by 8;

SELECT

  'DUPLICATE/RESTORE THROUGHPUT',

  round(SUM(v.value/1024/1024),1) mbytes_sofar,

  round(SUM(v.value     /1024/1024)/nvl((SELECT MIN(elapsed_seconds)

  FROM v\$session_longops

  WHERE OPNAME LIKE 'RMAN: aggregate input'

  AND SOFAR            != TOTALWORK

  AND elapsed_seconds IS NOT NULL

  ),SUM(v.value     /1024/1024)),2) mbytes_per_sec,

  n.name

FROM gv\$sesstat v,

  v\$statname n,

  gv\$session s

WHERE v.statistic#=n.statistic#

AND n.name = 'physical write total bytes'

AND v.sid = s.sid

AND v.inst_id=s.inst_id

AND s.program like 'rman@%'

GROUP BY 'DUPLICATE/RESTORE THROUGHPUT',n.name;

SELECT

  'BACKUP THROUGHPUT',

  round(SUM(v.value/1024/1024),1) mbytes_sofar,

  round(SUM(v.value     /1024/1024)/nvl((SELECT MIN(elapsed_seconds)

  FROM v\$session_longops

  WHERE OPNAME LIKE 'RMAN: aggregate output'

  AND SOFAR            != TOTALWORK

  AND elapsed_seconds IS NOT NULL

  ),SUM(v.value     /1024/1024)),2) mbytes_per_sec,

  n.name

FROM gv\$sesstat v,

  v\$statname n,

  gv\$session s

WHERE v.statistic#=n.statistic#

AND n.name = 'physical read total bytes'

AND v.sid = s.sid

AND v.inst_id=s.inst_id

AND s.program like 'rman@%'

GROUP BY 'BACKUP THROUGHPUT',n.name;

exit

EOF

sleep 60

done

  

NON-TECHIE (Your health) - Sweet Poison

NON-TECHIE (Your health) - Sweet Poison

This is an email it was sent to me from one of my friends, the subject worries me, since my friend works in the health are.

This is what email says... in quotes

In October of 2001, my sister started getting very sick.
She had stomach spasms and she was having a hard time getting around.
Walking was a major chore.
It took everything she had just to get out of bed; she was in so much pain.


By March 2002, she had undergone several tissue and muscle biopsies
and was on 24 various prescription medications.
The doctors could not determine what was wrong with her.
She was in so much pain, and so sick she just knew she was dying.


She put her house, bank accounts, life insurance, etc., in her oldest
daughter's name, and made sure that her younger children were to be
taken care of.


She also wanted her last hooray, so she planned a trip to Florida
(basically in a wheelchair) for March 22nd.


On March 19 I called her to ask how her most recent tests went, and
she said they didn't find anything on the test, but they believe she
had MS.
I recalled an article a friend of mine e-mailed to me and I asked my
sister if she drank diet soda?
She told me that she did.
As a matter of fact, she was getting ready to crack one open that moment.


I told her not to open it, and to stop drinking the diet soda!
I e-mailed her article my friend, a lawyer, had sent.
My sister called me within 32 hours after our phone conversation and
told me she had stopped drinking the diet soda AND she could walk!
The muscle spasms went away. She said she didn't feel 100% but, she
sure felt a lot better.


She told me she was going to her doctor with this article and would
call me when she got home.


Well, she called me, and said her doctor was amazed!
He is going to call all of his MS patients to find out if they
consumed artificial sweeteners of any kind.
In a nutshell, she was being poisoned by the Aspartame in the diet
soda...and literally dying a slow and miserable death.


When she got to Florida March 22, all she had to take was one pill,
and that was a pill for the Aspartame poisoning!
She is well on her way to a complete recovery.
And she is walking!
No wheelchair!


This article saved her life.
If it says 'SUGAR FREE' on the label;
DO NOT EVEN THINK ABOUT IT!
I have spent several days lecturing at the WORLD ENVIRONMENTAL
CONFERENCE on 'ASPARTAME,' marketed as 'Nutra Sweet,'
'Equal,' and
'Spoonful.'


In the keynote address by the EPA, it was announced that in the United
States in 2001 there is an epidemic of multiple sclerosis and systemic
lupus.
It was difficult to determine exactly what toxin was causing this to
be rampant.
I stood up and said that I was there to lecture on exactly that subject.
I will explain why Aspartame is so dangerous:
When the temperature of this sweetener exceeds 86 degrees F, the wood
alcohol in ASPARTAME converts to formaldehyde and then to formic acid,
which in turn causes metabolic acidosis.. Formic acid is the poison
found in the sting of fire ants.
The methanol toxicity mimics, among other conditions, multiple
sclerosis and systemic lupus.


Many people were being diagnosed in error.
Although multiple sclerosis is not a death sentence, Methanol toxicity is!


Systemic lupus has become almost as rampant as multiple sclerosis,
especially with Diet Coke and Diet Pepsi drinkers.


The victim usually does not know that the Aspartame is the culprit.
He or she continues its use; irritating the lupus to such a degree
that it may become a life-threatening condition.
We have seen patients with systemic lupus become asymptotic, once
taken off diet sodas.


In cases of those diagnosed with Multiple Sclerosis, most of the
symptoms disappear. We've seen many cases where vision loss returned
and hearing loss improved markedly.


This also applies to cases of tinnitus and fibromyalgia.
During a lecture, I said,
'If you are using ASPARTAME (Nutra Sweet, Equal, Spoonful, etc) and
you suffer from fibromyalgia symptoms, spasms, shooting, pains,
numbness in your legs,
Cramps,
Vertigo,
Dizziness,
Headaches,
Tinnitus,
Joint pain,
Unexplainable depression, anxiety attacks, slurred speech, blurred
vision, or memory loss you probably have ASPARTAME poisoning!'
People were jumping up during the lecture saying,
'I have some of these symptoms..
Is it reversible?'


Yes!
Yes!
Yes!
STOP drinking diet sodas and be alert for Aspartame on food labels!
Many products are fortified with it!
This is a serious problem.
Dr. Espart (one of my speakers) remarked that so many people seem to
be symptomatic for MS and during his recent visit to a hospice; a
nurse stated that six of her friends, who were heavy Diet Coke
addicts, had all been diagnosed with MS. This is beyond coincidence!


Diet soda is NOT a diet product! It is a chemically altered, multiple
SODIUM (salt) and ASPARTAME containing product that actually makes you
crave carbohydrates.


It is far more likely to make you GAIN weight!


These products also contain formaldehyde, which stores in the fat
cells, particularly in the hips and thighs.
Formaldehyde is an absolute toxin and is used primarily to preserve
'tissue specimens.'


Many products we use every day contain this chemical but we SHOULD NOT
store it IN our body!


Dr. H. J. Roberts stated in his lectures that once free of the 'diet
products' and with no significant increase in exercise; his patients
lost an average of 19 pounds over a trial period.


Aspartame is especially dangerous for diabetics.
We found that some physicians, who believed that they had a patient
with retinopathy, in fact, had symptoms caused by Aspartame.
The Aspartame drives the blood sugar out of control.
Thus diabetics may suffer acute memory loss due to the fact that
aspartic acid and phenylalanine are NEUROTOXIC when taken without the
other amino acids necessary for a good balance.


Treating diabetes is all about BALANCE.
Especially with diabetics, the Aspartame passes the blood/brain
barrier and it then deteriorates the neurons of the brain; causing
various levels of brain damage,
Seizures,
Depression,
Manic depression,
Panic attacks,
Uncontrollable anger and rage.


Consumption of Aspartame causes these same symptoms in non-diabetics as well.
Documentation and observation also reveal that thousands of children
diagnosed with ADD and ADHD have had complete turnarounds in their
behavior when these chemicals have been removed from their diet.


So called 'behavior modification prescription drugs' (Ritalin and
others) are no longer needed.
Truth be told, they were never NEEDED in the first place!


Most of these children were being 'poisoned' on a daily basis with the
very foods that were 'better for them than sugar.'


It is also suspected that the Aspartame in thousands of pallets ofdiet
Coke and diet Pepsiconsumed by men and women fighting in the Gulf War,
may be partially to blame for the well-known Gulf War Syndrome.


Dr. Roberts warns that it can cause birth defects, i.e. mental
retardation, if taken at the time of conception and during early
pregnancy.
Children are especially at risk for neurological disorders and should
NEVER be given artificial sweeteners.


There are many different case histories to relate of children
suffering grand mal seizures and other neurological disturbances
talking about a plague of neurological diseases directly caused by the
use of this deadly poison.'


Herein lies the problem:
There were Congressional Hearings when Aspartame was included in 100
different products and strong objection was made concerning its use.
Since this initial hearing, there have been two subsequent hearings,
and still nothing has been done.
The drug and chemical lobbies have very deep pockets.


Sadly, MONSANTO'S patent on Aspartame has EXPIRED!
There are now over 5,000 products on the market that contain this
deadly chemical and there will be thousands more introduced.
Everybody wants a 'piece of the Aspartame pie.'


I assure you that MONSANTO, the creator of Aspartame, knows how deadly it is.


And isn't it ironic that MONSANTO funds, among others, the American
Diabetes   Association, the American Dietetic Association and the
Conference of the American College of Physicians?


This has been recently exposed in the New York Times.
These [organizations] cannot criticize any additives or convey their
link to MONSANTO because they take money from the food industry and
are required to endorse their products.


Senator Howard Metzenbaum wrote and presented a bill that would
require label warnings on products containing Aspartame, especially
regarding pregnant women, children and infants.


The bill would also institute independent studies on the known dangers
and the problems existing in the general population regarding
seizures, changes in brain chemistry, neurological changes and
behavioral symptoms.


The bill was killed.


It is known that the powerful drug and chemical lobbies are
responsible for this, letting loose the hounds of disease and death on
an unsuspecting and uninformed public. Well, you're informed now!


YOU HAVE A RIGHT TO KNOW!

HOWTO (Oracle DB) - Troubleshooting Mutex Concurrency problems in the Library Cache

Several times we've seen concurrency event "Library cache: mutex X" on database sessions across the cluster database.

A session waiting on Library cache Mutex causes

• High CPU usage on the instance node
• Can potentially evict of a RAC node in case loadavg is very high
• Large number of locked sessions waiting on "Library Cache: mutex X" event

Definition of Library cache MUTEX
The library cache mutex is acquired for similar purposes that the library cache latches were acquired in prior versions of Oracle. In 10g, mutexes were introduced for certain operations in the library cache. Starting with 11g, the library cache latches were replaced by mutexes, hence this new wait event.

Mutexes are a lighter-weight and more granular concurrency mechanism than latches. Mutexes take advantage of CPU architectures that offer the compare and swap instructions (or similar). The reason for obtaining a mutex in the first place, is to ensure that certain operations are properly managed for concurrency. E.g., if one session is changing a data structure in memory, then another session must wait to acquire the mutex before it can make a similar change - this prevents unintended changes that would lead to corruptions or crashes if not serialised.

This wait event is present whenever a library cache mutex is held in exclusive mode by a session and other sessions need to wait for it to be released. There are many different operations in the library cache that will require a mutex, so its important to recognise which "location" (in Oracle's code) is involved in the wait. "Location" is useful to Oracle Support engineers for diagnosing the cause for this wait event.

This query will show sessions waiting on Concurrency Mutex event

select

s1.sid||','||s1.serial#||',@'||s1.inst_id kill,

machine,p1,p1text,p2,p2text,p3,p3text,

(select p.spid from gv$process p

where p.addr=s1.paddr and p.inst_id=s1.inst_id) spid,s1.process,

s1.username,substr(s1.program,1,20) program,substr(s1.module,1,20) module,

substr(s1.action,1,20) action,

substr(s1.event,1,25)event,s1.wait_class,s1.sql_id, last_call_et,seq#

from gv$session s1

where

event like '%mutex%'

order by last_call_et desc;

Identify the Blocking session holding the MUTEX in the library cache

select *

from gv$mutex_sleep_history

where

mutex_identifier=121516;

Note: Use P1 value from the query above, IDN - Mutex identifier. Session sid=7250 on instance=1 is holding a MUTEX identifier=121516 

Check Blocking session from the GV$SESSION table

select

s1.sid||','||s1.serial#||',@'||s1.inst_id kill,machine,

(select p.spid from gv$process p where p.addr=s1.paddr and p.inst_id=s1.inst_id) spid,s1.process,

s1.username,substr(s1.program,1,20) program,substr(s1.module,1,20) module,substr(s1.action,1,20) action,substr(s1.event,1,25) event,s1.wait_class,s1.sql_id,

last_call_et,seq#

from gv$session s1

where

sid = 7250 and

inst_id = 2

order by last_call_et desc;

Note: the blocking session is waiting on the Network event. This could indicate the client is still consuming data. Killing the blocking session will realease the locks. 

Get the SQL statement being run by the blocking session

select *

from gv$sqltext

where

sql_id = 'ajx51mvr52a60' and

inst_id=2; 

Get the SQL statement to kill all sessions that belong to a particular module

select 'echo '||inst_id||';kill -9 '||spid from gv$process where (addr,inst_id) in (select paddr,inst_id from gv$session where module like '%Fogli%');

'ECHO'||INST_ID||';KILL-9'||SPID
--------------------------------
echo 1;kill -9 5367
echo 1;kill -9 9151
echo 1;kill -9 19199
echo 1;kill -9 9981
echo 1;kill -9 8478
echo 1;kill -9 8486

Reference: WAITEVENT "library cache: mutex X" (Doc ID 727400.1)

HOWTO (eBiz) - Tracking file changes after applying patches

We normally use adident utility to find what is the current version of a particular file in EBS, however in case you want to see home many patches changed the same file there is no tools that provides this information.

The query below will help you to identify not only the current file version, but the history of changes on the file after being modified by a particular patch.

/* 

-- Filename: check_file_version_with_patch_number.sql

-- Description: file change history tracking 

-- Author: Christian Ricci

-- Created on: 09/Mar/2011

-- Modified on: 20110309 - CR - Initial version

*/
col APP_SHORT_NAME for a8
col filename for a25
col subdir for a30
col version for a40
set lin 300 pages 1000 trim on trimspool on verify off
set colsep '|'
--
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select instance_name from v$instance;
--
SELECT
  ab.bug_number         ,
  f.app_short_name      ,
  f.subdir              ,
  f.filename            ,
  v.file_id             ,
  v.creation_date       ,
  v.last_update_date    ,
  v.version
FROM applsys.AD_FILE_VERSIONS v,
  applsys.ad_files f,
  applsys.AD_PATCH_RUN_BUGS pr,
  applsys.AD_PATCH_RUN_BUG_ACTIONS pra,
  applsys.ad_bugs ab
WHERE
    v.file_id=f.file_id
and pra.file_id=v.file_id
and pra.patch_file_version_id=v.file_version_id
and pr.patch_run_bug_id=pra.patch_run_bug_id
and ab.bug_id=pr.bug_id
and f.filename='&1'
ORDER BY
  v.last_update_date DESC,
  f.filename             ,
  v.creation_date;

This below query will show the files modified by a particular patch.

/* 

-- Filename: check_file_on_patch_number.sql

-- Description: Files changes by a particular patch

-- Author: Christian Ricci

-- Created on: 09/Mar/2011

-- Modified on: 20110309 - CR - Initial version

*/col APP_SHORT_NAME for a8
col filename for a25
col subdir for a30
col version for a40
set lin 300 pages 1000 trim on trimspool on verify off
set colsep '|'
--
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select instance_name from v$instance;
--
SELECT
  ab.bug_number         ,
  f.app_short_name      ,
  f.subdir              ,
  f.filename            ,
  v.file_id             ,
  v.creation_date       ,
  v.last_update_date    ,
  v.version
FROM applsys.AD_FILE_VERSIONS v,
  applsys.ad_files f,
  applsys.AD_PATCH_RUN_BUGS pr,
  applsys.AD_PATCH_RUN_BUG_ACTIONS pra,
  applsys.ad_bugs ab
WHERE
    v.file_id=f.file_id
and pra.file_id=v.file_id
and pra.patch_file_version_id=v.file_version_id
and pr.patch_run_bug_id=pra.patch_run_bug_id
and ab.bug_id=pr.bug_id
and ab.bug_number='&1'
ORDER BY
  v.last_update_date DESC,
  f.filename             ,
  v.creation_date;
 

HOWTO (eBiz) - E-Business Suite R12 Apps DBA full implementation project

During 2007/2008 I had the chance to work on a full implementation of eBiz version R12. I was the main DBA in site and my scope was the delivery of the Production eBiz cluster and rest of support environments. I was involved in the architecture design, installation of the OS via PXE boot (process that I built), installation of R12 in cluster both on the database tier and application tier, initial configuration of eBiz, HA test and documentations, and the cloning process on a RAC and MULTINODE configuration.
The interesting setup of this eBiz is the utilisation of the Shared APPL_TOP on a READ-ONLY file system. This is possible thanks to the new R12 features of the INST_TOP which is the separation of the config and log files outside the APPL_TOP.

Another interesting setup was the way I configured Concurrent Managers in HA mode or Parallel Concurrent Processes, ICM and FNDSM are the key components of this setup, I had lots of problems with TNS session timeout during fail-over tests which I managed to fix by with the help a lot of older metalink notes. I used OCFS2 to shared key directory path locations needed by the MULTINODE setup. Among these locations were APPLCSF, APPLPTMP, APPLTMP, FNDREVIVER, BNE and the list keep growing, this is not very well documented by Oracle and it was difficult to troubleshoot.

These days OCFS2 has been certified to be used on shared APPL_TOP configuration and there is not need to use READ-ONLY file system implementation anymore, however this configuration works perfectly. Other solutions are to use an external NAS device over NFS to mount APPL_TOP (please do not use local NFS server in apps eBiz nodes since this will introduce a single point of failure, some people don't think about HA all the time, so please speak to your System Architect before taking any architectural decision).

Cloning 8 nodes eBiz was really a challenge, specially when you use RAC and MULTINODE setup on a Shared APPL_TOP. The process is not very well documented, and ADCLONE tool are not fully prepared to manage a non-standard setup. I decided to use a manual clone process, where I had to run FND_SETUP.CONC_CLEAN and adfcfclone dbTechStack and adcfgclone appsTechStack. When you use MULTINODE and RAC there are other things to consider like running ADCONFIG which very tricky. 
 
If you have 4 database node, you need to run adconfig in the follwing order node1 , node2, node3 and node4, at this stage people normally think adconfig was run in all the nodes and that's it, but that is not really true... If you have a look at the tnsname.ora for example on the node1, you will see there is no reference of the rest of the nodes in the “ADDRESS LIST”, Why? This is because of a limitation of ADCONFIG which grabs the information of the nodes from the FND tables which are populated as ADCONFIG runs. 
 
To workaround this problem, your need to re-run ADCONFIG twice. For example: run ADCONFIG in node1, node2, node3 and node4, at this stage node4 know about (node 1,2,3,4), node3 know about (1,2,3 but not 4), node2 knows about (1,2 but not 3,4) and node1 knows about (1 but not 2,3,4). So you need to re-run ADCONFIG in this order node3, node2 and node1 to fix the problem. After running ADCONFIG in the remaining nodes TWICE check tnsnames.ora and you will see “ADDRESS LIST” contain reference to all the cluster nodes. This workaround applies both for APP TIER and DB TIER. Hopefully Oracle will fix their documentation soon and properly document this process.

Hardware/Software Specs:

HP class 7000 blade chassis. Composed of 16 blades.
Citrix Netscaler Load Balancers

Redhat 4 EL x86_64
Oracle CRS/ASM/RDBMS V10.2.0.4 and EBS R12.0.4

Contributions:
Thanks to Neil and Dipak for reviewing this documentation and helping all the way through the project.

Full documentation:

This post has references to step-by-step build documentation for the E-Business Suite OS build, Database RAC and Shared APPL_TOP read-only implementation, Multinode setup using Parallel concurrent processing. These documents are very long, see the document index below to have an idea of the content.

Please post a comment including your email address and I will send you the password.

ebsr12_os_build.pdf

ebsr12_oracle_rac_build.pdf

ebsr12_oracle_multinode_build.pdf

ebsr12_clone.pdf

I am asking for a minimum donation via PayPal logo located on the right hand side of my blog (this can come as part of your department's training budget). Alternatively, you can engage my services directly, please contact me on my email.

Click link to download full documentation ebsr12_multinode_rac_full_cluster.tgz.gpg (password will be emailed to you when you Donate).

Powered by

http://www.gnupg.org/download

Hardware Setup, Linux Red Hat EL 4 Build using PXE Boot, Backup Configuration, Maintenance tasks and Procedures

1 Document Control 4

1.1 Signoff 4

1.2 Document Owner 4

1.3 Distribution 4

1.4 Change History 4

2 Purpose 5

2.1 Scope 5

3 Hardware Setup 6

3.1 Overview of HP Blade System c7000 Enclosure 6

3.2 HP Blade System c7000 Enclosure Configuration 8

3.3 ProLiant c-Class Server Blade architecture 11

3.4 Network/SAN Interconnect Bays 13

3.5 Power and Thermal Management 14

4 Linux Red Hat OS Build using PXE Boot process 16

4.1 PXE boot server configuration using Linux 17

4.2 PXE boot server configuration using Microsoft SMS 19

4.3 Configuring the NFS server to store Red Hat Source packages 21

4.4 Start the OS build process 23

5 Configure Kernel Huge Pages for Oracle SGA 26

5.1 HugePages Features 26

5.2 HugePages setup 26

6 Configure CUPS unix printing 29

6.1 Configure cups web access 29

6.2 Add a printer to unix 30

7 Account set up and password policies 31

7.1 Define UNIX password policies 31

7.2 Set up sudo access 32

7.3 Set ACLs on JAVA_TOP and DEE_TOP 32

8 Install and configure CommVault UNIX File System agent 34

8.1 Install the file system agent 34

9 Install and configure CommVault Oracle agent 36

9.1 Install the Oracle agent 36

9.2 Configure the Oracle dataset backup on tape 37

9.3 Perform a disaster recovery of the database from a tape backup 42

10 Setting up backup storage policy and schedules in CommVault 54

10.1 CommVault Backup schedules 54

10.2 CommVault Storage Policies 55

10.3 Current backup policy 56

11 Appendix 57

11.1 FILE: ks-linux-oracle.cfg 57

11.2 FILE: fix_ip.sh 63

11.3 FILE: /etc/sudoers 66

11.4 FILE: create_accounts.sh 68

11.5 FILE: pfile.uat 69

Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12, Configuring Application Tier using READ-ONLY file system

1 Document Control 4

1.1 Signoff 4

1.2 Document Owner 4

1.3 Distribution 4

1.4 Change History 4

2 Purpose 5

2.1 Scope 5

3 Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12 6

3.1 Network Configuration 6

3.2 Disk configuration 7

3.3 EBS-R12 installation (non-cluster) 8

3.4 Install Oracle Clusterware 10.2.0.1 30

3.5 Install Oracle Clusterware patchset 10.2.0.4 54

3.6 Configure ONS to support multiple accounts configuration 60

3.7 Install Oracle ASM software 10.2.0.1 62

3.8 Install Oracle ASM patchset 10.2.0.4 69

3.9 Clone Oracle Database home from existing DB Tech Stack home 74

3.10 Create ASM Instances and Disk Groups 78

3.11 Convert 10gR2 Database to Oracle RAC using rconfig 99

3.12 Enable AutoConfig on Database Tier 104

3.12.1 Enable AutoConfig in First DB TIER node 104

3.12.2 Enable AutoConfig on all other DB TIER nodes 111

3.12.3 AutoConfig Post actions on all DB TIER nodes 115

3.13 Establish Applications Environment for Oracle RAC 118

4 Configuring Application Tier cluster using Read-Only file system implementation 120

4.1 Prepare existing node 120

4.2 Make the Applications files accessible 120

4.3 Configure the node to be added 120

4.4 Finishing Tasks 122

4.4.1 DNS Load Balancing with Single Web Entry Point 122

4.4.2 Implement Parallel Concurrent Processing 122

5 APPENDIX 125

5.1 FILE: rac-prereqs.sh 125

5.2 FILE: asm_build.sh 129

5.3 FILE: UAT_ConvertToRAC.xml 131

5.4 FILE: tnsnames.ora (DB TIER Node1) 133

5.5 FILE: tnsnames.ora (DB TIER Node2) 136

5.6 FILE: listener.ora (DB TIER node1) 139

5.7 FILE: listener.ora (DB TIER node2) 141

5.8 FILE: rac-enabled-pfile.ora 143

5.9 FILE: spfile (for the ASM) 161

5.10 FILE: ons_config_db.tmp 162

5.11 FILE: ons_config – CRS_HOME 163

E-Business Suite R12 Multi-Node configuration sharing the Application Tier File System as Read-Only, Network Load Balancing configuration and Parallel Concurrent Processing

1 Document Control 3

1.1 Signoff 3

1.2 Document Owner 3

1.3 Distribution 3

1.4 Change History 3

2 Purpose 4

2.1 Scope 4

3 Multi-Node and shared application tier file system concepts 5

4 Network Load balancing concepts 6

5 Parallel Concurrent Processing Concepts 8

6 Maintaining a Shared Application Tier File System 9

7 Add a node to a Shared Application Tier file system (Read-Only Implementation) 10

7.1 Prepare the Source System 11

7.2 Make the application files accesible to the Secondary node 11

7.3 Configure the node to be added 12

8 Network Load balancing for the Web Entry Point services 17

8.1 Configure Load Balancers with Single Web Entry Point 19

8.2 Enable OC4J clustering on the application tier nodes 22

9 Configuring Parallel Concurrent Processing 24

9.1 Configure a shared file system for PCP 25

9.2 Enabling Concurrent Processing service on App Tier nodes 29

10 APPENDIX 37

10.1 FILE: igccmprf.sh 37

10.2 FILE: mod_oc4j_conf_1013.tmp 40

10.3 FILE: build_ocfs2.sh 41

E-Business Suite R12 environment cloning procedures

1 Document Control 3

1.1 Signoff 3

1.2 Document Owner 3

1.3 Distribution 3

1.4 Change History 3

2 Purpose 4

2.1 Scope 4

3 Cloning process in summary 5

4 Clone DBTIER from source system RAC to target system RAC 6

4.1 Prerequisites 7

4.2 Prepare the Source System 8

4.3 Perform clone steps in DB TIER Target System 9

4.3.1 Enable AutoConfig on Applications Database Tier 26

4.3.2 Enable AutoConfig on all other DB TIER nodes 31

4.3.3 AutoConfig Post actions on all DB TIER nodes 35

4.4 Perform clone steps in APP TIER Target System 38

4.5 Perform post task clone actions (all app tiers) 42

5 APPENDIX 43

5.1 FILE: rac-enabled-pfile.ora 43

5.2 FILE: tnsnames.ora (node2) 59

5.3 FILE: listener.ora (node2) 62

5.4 FILE: listener.ora (node1) 64

5.5 FILE: tnsnames.ora (node1) 66

5.6 FILE: ons_config_db.tmp 69

5.7 FILE: adlsnr10RAC.ora 70

5.8 FILE: /tmp/clone.patch 71

5.9 FILE: /staging/oracle/cmclean.sql 74

5.10 FILE: /staging/oracle/coins-ebs.sql 79