How to move ASM and Voting Files to another location

Assume that  you have the following disks (here ASMLIB):

[root@xxxxx ~]# oracleasm listdisks
....
OCR_VOTED1
OCR_VOTED1B
OCR_VOTED2
OCR_VOTED2B
OCR_VOTED3
OCR_VOTED3B

where OCR_VOTED1-3 are the existing disks on the old SAN/location and OCR_VOTED1B - 3B are the new disks in the new SAN/location.

The current voting disks show as:

[oragrid@xxxxxxx]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   154c5cab6b2f4f5dbfd5dd9b1eab4bd7 (ORCL:OCR_VOTED1) [OCR]
 2. ONLINE   543487a699a44f50bfaa75fb4dd432a8 (ORCL:OCR_VOTED2) [OCR]
 3. ONLINE   5cf1b3ccf1a54f53bff8f0b2f6dc9930 (ORCL:OCR_VOTED3) [OCR]
Located 3 voting disk(s).

In sqlplus connected to the ASM instance check the current disk names for the OCR diskgroup:

sqlplus '/ as sysasm'
...
SQL> select GROUP_NUMBER,name from V$asm_diskgroup;

GROUP_NUMBER NAME
------------ ------------------------------
       1 DATA
       2 FRA
       3 OCR

SQL> select name from v$asm_disk where group_number = 3;

NAME
------------------------------
OCR_VOTED1
OCR_VOTED2
OCR_VOTED3

and then just issue the following command to add the new and drop  the old disks:

alter diskgroup OCR
add disk 
'ORCL:OCR_VOTED1B',
'ORCL:OCR_VOTED2B',
'ORCL:OCR_VOTED3B'
drop disk 
'OCR_VOTED1',
'OCR_VOTED2',
'OCR_VOTED3'
;

This can be done without any downtime, once the command completes the clusterware will show that the voting disks have changed:

[oragrid@xxxxxxx~]$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   7936809b67604f05bf39cb777384d4b5 (ORCL:OCR_VOTED1B) [OCR]
 2. ONLINE   27046d28c4e64f78bf34f8e89af212e4 (ORCL:OCR_VOTED2B) [OCR]
 3. ONLINE   419edf08dceb4f28bf14fa57a42666ad (ORCL:OCR_VOTED3B) [OCR]
Located 3 voting disk(s).

ORA-20005 WHEN GATHERING STATISTICS FOR EBS

Cause:
=====

This can happen with Advance Queue tables.

In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:

select owner, table_name, stattype_locked
from dba_tab_statistics
where stattype_locked is not null; 

Solution:
======

Unlock statistics gathering on those queues running the commands below.

To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner'); 

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('FND');

SQL> exec dbms_stats.unlock_table_stats('FND', 'APPLSYS.FND_CP_GSM_IPC_AQTBL'); 

Forms Launching directly on EBS login for specific user

Issue:
If this happens for a single user this may be the result of the fact the user defined this function as the Start Page in the Preferences link.

Solution:
Verify the profile option "Applications Start Page" being set at user level for the offending user. If a value is set a specific function has been set which is automatically called after the user logs
Remove the value for the profile at user level and retest the issue.

Or

If the above solution does not work, clear the cache as below.
Functional Administrator > Core Services > Caching Framework > Global Configuration > Clear all caches (button) to clear the Java Object Caches which cache Responsibility values.

See also Note:729375.1 About "Applications Start Page" profile option for more information on this profile

adcvm.sh[211]: AIX: unknown test operator in 11i

Hi,
This issue is caused in AIX 212 systems only

Below is the fix.

Comment out the below lines in $AD_TOP/bin/adcvm.sh file.

if ((test "$pltform" = "Solaris") ||(test "$pltform" = "Intel_Solaris")); then

echo "Passed platform test!"

if test "$tier" = "database"; then

echo " Passed tier test!"

if test "$dbversionnum" -ge "112" ; then

echo " Passed DB version test!"

ADJREOPTS="$ADJREOPTS -d64";

fi

fi

fi

Helpful Scripts for 11i and R12

To find details(including appln user name) of a lock on given object or all objects locked in database

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

To fine locks on table query
v$locked_object, some other tables dba_objecs, tab , dba-tables, v$session, v$process, v$loc, dba_locks ,dba-waiters
select OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_NAME='FA_MASS_ADDITIONS';
select * from v$locked_object where OBJECT_ID=79706; if this return any rows then lock is present on that object
rdbms/admin/utllockt.sql to get lock tree
Also check in v$access to see if any session is holding the opbject
select SESSION_ID,OBJECT_ID,LOCKED_MODE,PROCESS from v$locked_object where OBJECT_ID=79706;
select SESSION_ID,OBJECT_ID,LOCKED_MODE,PROCESS,ORACLE_USERNAME,OS_USER_NAME from v$locked_object where OBJECT_ID=79706;
select LOCKWAIT,STATUS,SCHEMANAME,OSUSER from v$session where SID=105;

query to find a lock
select sid,username,serial#,status from v$session where sid in
(select session_id from dba_locks where blocking_others='Blocking');


To find locks on a module
SELECT a.object_id, a.session_id, SUBSTR(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.owner IN ('&M')
ORDER BY a.session_id;

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40),owner,status
FROM v$locked_object a, dba_objects b WHERE a.object_id = b.object_id AND b.owner in ('&module');


To find and terminate all the sessions held by an object
SQL> select sid, serial#, status, module from v$session where sid in (select distinct sid from v$access where object = 'XXART_RCV_TRANSACTION_PK');

SID SERIAL# STATUS MODULE
---------- ---------- -------- ------------------------------------------------
3434 53145 ACTIVE XXPOURCVW
3854 9742 ACTIVE XXPOURCVW
3949 6531 INACTIVE XXPOURCVW
3955 14566 INACTIVE XXPOURCVW
3991 50125 INACTIVE XXPOURCVW
4143 58949 ACTIVE XXPOURCVW
4294 39052 ACTIVE XXPOURCVW
4347 19960 INACTIVE XXPOURCVW
4770 65507 INACTIVE XXPOURCVW
4841 59140 INACTIVE XXPOURCVW
4895 3632 INACTIVE XXPOURCVW"


Some times JVM processes keeps hogging on the db server, to check who all the users connected from that jvm prcoess we can do the below to track those users along with the action they are doing and report to client.

set linesize 1000
set pagesize 1000
column module format a40
column program format a20
column address format 9
column appsuser format a8
column username format a4
column SID format 99999
column sql_text format a80
column user_name format a15
col action for a10
SELECT s.sid,s.serial#,p.pid,p.spid,s.status,s.action,s.last_call_et/60 wait_mins,f.user_name,s.module
FROM gv$process p, gv$session s,fnd_signon_audit_view f
WHERE p.inst_id = s.inst_id 
and p.addr = s.paddr 
and f.pid=p.pid 
and s.process = '12649'
-- and s.sid = 9606;
/



To find forms session details which are existing from several hours"select b.sid ""sid"", b.serial# ""ser#"", c.spid ""OS Prc."", b.status, b.username, b.module, b.action, to_char(b.logon_time, 'DD-MON-YY HH:MI AM') ""Logon Time""
from v$session b, v$process c
where b.paddr = c.addr
and b.action like 'FRM%'
and b.logon_time < sysdate - (1/2) --logged in over 16 hours ago
order by logon_time asc
/"

select b.sid "sid", b.serial# "ser#", c.spid "OS Prc.", a.event, b.status, b.username, b.module, b.action,
to_char(b.logon_time, 'DD-MON-YY HH:MI AM') "Logon Time"
from v$session_wait a, v$session b, v$process c
where a.sid = b.sid
and b.paddr = c.addr
and b.action like 'FRM%'
and b.logon_time < (sysdate - (1/4)) -- logged in over 6 hours ago
order by logon_time asc

To find number of sessions held by application users
select count(*), fu.user_name
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name IN ('VLEE', 'TNGUYEN', 'DBREIG' ) group by fu.user_name 
/"

To Kill all sessions of an user ( eg: DBREIG) 

select 'alter system kill session ' || '''' ||sid || ',' || s.serial# || '''' || ';'
from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu
where p.addr = s.paddr
and f.spid (+) = s.process
and fu.user_id (+) = f.user_id
and f.end_time(+) is null
and fu.user_name='DBREIG'
order by f.start_time
/

TO get session informations of an application user

set linesize 1000 pagesize 1000 feedback off

column module format a15

column program format a20

column address format 9

column appsuser format a8

column username format a4

column SID format a10

column sql_text format a80

column user_name format a30

column "Time (m)" for a9

SELECT s.sid||','||s.serial# sid,p.pid,p.spid,s.status,s.action,f.time "Time (m)",u.email_address user_name

FROM v$process p, v$session s,fnd_signon_audit_view f, fnd_user u

WHERE p.addr = s.paddr

and f.pid=p.pid 

and f.user_id = u.user_id

and u.user_name like '&Application_User%';

To find sql test from sid
"select sql_text from v$sqlarea where address in (select sql_address from v$session where sid =928); 

SQL_TEXT 
---------------------------------------------------------------------------------------------------------------------------------- 
begin DP.DATA_LOAD.EP_LOAD_SALES; end; 

SQL> select SID,EVENT,p1,p2 from v$session_wait where sid=928; 

SID EVENT P1 P2 
---------- ---------------------------------------------------------------- ---------- ---------- 
928 db file sequential read 53 223359 


To get Top 5 time-consuming requets
*****************************

select f1.conc_prog,f1.req_id,f1.oracle_id from
(select f.concurrent_program conc_prog,f.request_id req_id,f.oracle_process_id oracle_id from
(select t.user_concurrent_program_name concurrent_program,
r.request_id request_id,r.oracle_process_id oracle_process_id
from fnd_concurrent_requests r,
fnd_concurrent_processes p,
fnd_concurrent_programs_tl t
where r.controlling_manager = p.concurrent_process_id
and r.program_application_id = t.application_id
and r.concurrent_program_id = t.concurrent_program_id
and to_char(sysdate-r.actual_start_date)*1440 > 60
and r.Phase_Code = 'R' and r.Status_Code = 'R'
order by to_char(sysdate-r.actual_start_date)*1440 ) f
where rownum < 6) f1

To get sqls of Top 5 time-consuming concurrent requests
***********************************************

set pages 5000
set lines 132
break on cy
spoo /tmp/cy
select f1.conc_prog||' '||f1.req_id||' '||f1.oracle_id cy,st.sql_text
FROM
(select f.concurrent_program conc_prog,f.request_id req_id,f.oracle_process_id oracle_id from
(select t.user_concurrent_program_name concurrent_program,
r.request_id request_id,r.oracle_process_id oracle_process_id
from fnd_concurrent_requests r,
fnd_concurrent_processes p,
fnd_concurrent_programs_tl t
where r.controlling_manager = p.concurrent_process_id
and r.program_application_id = t.application_id
and r.concurrent_program_id = t.concurrent_program_id
and to_char(sysdate-r.actual_start_date)*1440 > 60
and r.Phase_Code = 'R' and r.Status_Code = 'R'
order by to_char(sysdate-r.actual_start_date)*1440 ) f where rownum < 6) f1,
v$session s,
v$process p,
v$sqltext st
WHERE
p.spid = f1.oracle_id
and p.addr = s.paddr
and s.sql_address = st.address
and s.sql_hash_value = st.hash_value
order by cy,st.piece
/

Monitors for excessive JDBC connections and alerts if the connection count breaches the defined threshold.

Use the following query:
select count(*) from v$session where program like 'JDBC Thin%'

How to check JDBC Connection ?
connect using apps, sys or system & issue
select count(*), module from v$session where program like '%JDBC%' group by module;

Command to kill a session I.e also to release a lock heldby a table
alter system kill session 'sid,serial#';

REQUEST ID TAKING LONG TIME
SQL> select REQUEST_ID,ORACLE_ID,ORACLE_PROCESS_ID,ORACLE_SESSION_ID,OS_PROCESS_ID from FND_CONCURRENT_REQUESTS where REQUEST_ID=10779300;

REQUEST_ID ORACLE_ID ORACLE_PROCESS_ID ORACLE_SESSION_ID
---------- ---------- ------------------------------ -----------------
OS_PROCESS_ID
--------------------------------------------------------------------------------
10779300 900 15875 32829458
29985

SQL> select addr from v$process where spid=15875;

ADDR
----------------
C0000000513FEAF0



SQL> select sid,status,serial# from v$session where paddr='C0000000513FEAF0';

SID STATUS SERIAL#
---------- -------- ----------
178 ACTIVE 48878

SQL> select SOFAR,TOTALWORK from v$session_longops where sid=178;

SOFAR TOTALWORK
---------- ----------
16576 16576
1116 12668

SQL> /

SOFAR TOTALWORK
---------- ----------
16576 16576
1131 12668

SQL> select sql_hash_value from v$session_longops where sid=178;

SQL_HASH_VALUE
--------------
2468084395
2468084395


SQL> select sql_text from v$sql where hash_value='2468084395';

SQL_TEXT
--------------------------------------------------------------------------------
SELECT CRRID.* FROM CNC_RS_RES_INT_DELTA CRRID WHERE CRRID.PROCESS_FLAG IN ('PRO
CESSED','UNPROCESSED','ERRORED') AND (CRRID.PROCESS_TYPE = 'DELETE' OR ( CRRID.P
ROCESS_TYPE = 'NEW' AND CRRID.END_DATE_ACTIVE IS NOT NULL) ) AND NOT EXISTS ( SE
LECT 1 FROM CNC_RS_RG_INT_DELTA CRRID1 WHERE CRRID1.SOURCE_ID = CRRID.SOURCE_ID
AND CRRID1.PROCESS_FLAG IN ('G','ERRORED','UNPROCESSED') ) ORDER BY CRRID.START_
DATE_ACTIVE


SQL> select last_analyzed from dba_tables where table_name='CNC_RS_RES_INT_DELTA';

LAST_ANAL
---------
22-Dec-05


This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.
The column named ""module"" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';