To select the invalid objects
Select object_name, object_type,status from all_objects
where status ='INVALID' and object_name like 'JA%';
(or)
SELECT OWNER,object_name,object_type,status FROM DBA_OBJECTS WHERE STATUS = 'INVALID'
order by owner,object_type
You can use adadmin utility to compile or you can use utlrp.sql script shipped with Oracle Database to compile Invalid Database Objects
You can use ojspCompile.pl perl script shipped with Oracle apps to compile JSP files. This script is under $JTF_TOP/admin/scripts.
Sample compilation method is
perl ojspCompile.pl --compile --quiet
To create user oracle and apps
groupadd dba
useradd -d /ora/oracle/9.2.3 -s /bin/sh -c "Oracle home" -g dba cmwora
useradd -d /ora/apps/prodcomn -s /bin/sh -c "Oracle Application" -g dba cmwapps
passwd cmwora
enter passwd
TO start and stop
/etc/init.d/volmgt start/stop
eject or eject cdrom
TO be added in profile
PS1='ORACLE>/$ '
PATH="/usr/bin/zip-23:$PATH"
export PATH
. /oradb/oracle/9.2.1/DAT_projdevel.env
TO ENTER IN TO SQL Prompt (env)
ORACLE_SID=TESTORA
export TESTORA
ORACLE_HOME=/oracle/9.2.1
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:/usr/bin
export PATH
To create a NFS mount point
Go to source node ( Ex .where the space is available (Projdevel)
# Cd /etc/dfs
# more fstypes
#more dfstab (to view all the sharable mountpoint)
create a backup directory where the space is available
example
root@projdevel # cd ..
root@projdevel # ls -ls
total 226
6 drwxr-xr-x 77 oracle dba 2560 Feb 15 11:42 9.2.3
2 drwxr-xr-x 5 oratrg dba 512 Nov 20 13:53 admin
2 drwxr-xr-x 5 appltest dba 512 Nov 23 00:35 apps
178 drwxr-xr-x 2 oratrg dba 90624 Feb 15 14:42 archive
2 drwxrwxrwx 2 oracle dba 1024 Feb 17 07:50 backup
2 drwxr-xr-x 2 root other 512 Nov 21 15:42 rcp_scripts
16 drwxr-xr-x 2 oracle dba 7680 Jan 29 21:36 testdata
root@projdevel # cd backup
root@projdevel # pwd
/oracle/backup
make the entry in dfstab
share –F nfs –o rw /oracle/backup
example
root@projdevel # more dfstab
# Place share(1M) commands here for automatic execution
# on entering init state 3.
#
# Issue the command '/etc/init.d/nfs.server start' to run the NFS
# daemon processes and the share commands, after adding the very
# first entry to this file.
#
# share [-F fstype] [ -o options] [-d "
# .e.g,
# share -F nfs -o rw=engineering -d "home dirs" /export/home2
share -F nfs -o ro /ravi
share -F nfs -o rw /oracle/backup
# /etc/init.d/nfs.server start
#share (to view the sharable paths)
create a mount point (dir) in root ( ex rmanback)
# mount –F nfs 172.16.1.203:/oracle/backup /rmanback
#df –h (to verify the mount point)
GOTO THE TARGET SERVER ( Ex Production)
# mount –F nfs 172.16.1.203:/oracle/backup /rmanback
make the necessary changes in RMAN configuration
To create a new datafile in production (create first in SUN)
Step1)
vxassist -g racdg -U gen make
(or)
vxassist -g racdg -U gen make
Step 2)
vxedit -g racdg set user=oracle group=dba
Step 3)
To verify the file name status
vxprint –Aht| grep
(reference
vxdisk list
vxprint –Aht|more )
========================================================
to be included in system file ( /etc/system)
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=500
set shmsys:shminfo_shmseg=50
set semsys:seminfo_semmsl=1024
set semsys:seminfo_semmns=1400
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
To selecte all invalid database objects in a schema
Note : SGA size can be increaced upto (4294967295 ie 4 GB). If more space is needed for SGA increase the size in the above parameter
set shmsys:shminfo_shmmax=4294967295
To select the invalid objects
select 'alter ' || decode(object_type,'PACKAGE
BODY','PACKAGE',object_type)
|| ' ' || object_name || ' compile ' ||
decode(object_type,'PACKAGE BODY',' body;',';') from user_objects
where object_type in ('FUNCTION','PACKAGE','PACKAGE
BODY','PROCEDURE','TRIGGER','VIEW')
and status = 'INVALID'
order by object_type , object_name;
set heading off;
set pagesize 500;
spool c:\dba\compile.sql;
select 'alter ' || object_type ||' '|| OBJECT_NAME || ' compile ' || ';'
from dba_objects where object_type in ('FUNCTION','PACKAGE','PROCEDURE','TRIGGER','PACKAGE BODY','VIEW')
AND STATUS ='INVALID';
spool off;
for running the script
@c:\dba\compile.sql
TO KILL THE INACTIVE FORMS AND SESSIONS
SELECT p.spid,s.process,s.status,s.machine,
to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
s.last_call_et/3600 Last_Call_ET,
s.action,s.module,s.sid,s.serial#
FROM
V$SESSION s, V$PROCESS p
WHERE
s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND s.osuser = 'applprod'
AND s.last_call_et/3600 > 1
and s.action like 'FRM%'
and s.status='INACTIVE' order by logon_time;
database
Select
'alter system kill session '''||s.sid||','||s.serial#||''';',s.action
FROM
V$SESSION s, V$PROCESS p
WHERE
s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND s.osuser = 'applprod'
AND s.last_call_et/3600 > 1
and s.action like 'FRM%'
and s.status='INACTIVE';
Forms
SELECT
' '||s.machine||' kill -9 '||s.process, s.action
FROM
V$SESSION s, V$PROCESS p
WHERE
s.paddr = p.addr
AND s.username IS NOT NULL
AND s.username = 'APPS'
AND s.osuser = 'applprod'
AND s.last_call_et/3600 > 1
and s.action like 'FRM%'
and s.status='INACTIVE';
To Compile all invalid database objects in a schema
EXEC DBMS_UTILITY.COMPILE_SCHEMA( 'schema-name' );
To Compile all invalid database objects
declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;
begin
dbms_output.put_line(chr(0));
dbms_output.put_line('Re-compilation of Invalid Objects');
dbms_output.put_line('---------------------------------');
dbms_output.put_line(chr(0));
for invalid in (select object_type, owner, object_name
from sys.dba_objects o,
sys.order_object_by_dependency d
where o.object_id = d.object_id(+)
and o.status = 'INVALID'
and o.object_type in ('PACKAGE', 'PACKAGE BODY',
'FUNCTION',
'PROCEDURE', 'TRIGGER',
'VIEW')
order by d.dlevel desc, o.object_type) loop
if invalid.object_type = 'PACKAGE BODY' then
sql_statement := 'alter package '||invalid.owner||'.'||invalid.object_name||
' compile body';
else
sql_statement := 'alter '||invalid.object_type||' '||invalid.owner||'.'||
invalid.object_name||' compile';
end if;
/* now parse and execute the alter table statement */
cursor_id := dbms_sql.open_cursor;
dbms_sql.parse(cursor_id, sql_statement, dbms_sql.native);
ret_val := dbms_sql.execute(cursor_id);
dbms_sql.close_cursor(cursor_id);
dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||
invalid.object_name, 32)||' : compiled');
end loop;
end;
User's Status in the ICX_SESSIONS Table
select
disabled_flag,
to_char(first_connect,'MM/DD/YYYY HH:MI:SS') Start_Time,
to_char(sysdate,'HH:MI:SS') Current_Time,
USER_NAME,
session_id,
(SYSDATE-last_connect)*24*60 Mins_Idle,
fnd_profile.value_specific
('ICX_SESSION_TIMEOUT',
a.user_id,
a.responsibility_id,
a.responsibility_application_id,
a.org_id,
NULL
) TimeOut
from
ICX_SESSIONS a, fnd_User b
where
a.user_id=b.user_id
and last_connect > sysdate-1/24;
To select the username and the process status
select a.requested_start_date,a.last_update_date,a.status_code,b.user_name
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224
select a.requested_start_date,a.last_update_date,a.status_code,b.user_name ,a.argument_text
from fnd_concurrent_requests a,fnd_user b where a.requested_by = b.user_id and a.request_id = 677224
Steps to release the stuck for PO entry
Su – applprod
Cd $FND_TOP
Cd sql
Sqlplus apps/apps
SQL>
Select org_id,release_num,wf_item_type,wf_item_key
From po_releases_all
Where po_header_id
In(select po_header_id from po_headers_all where segment1=’&PO_NUMBER’);
SQL>
select po_header_id,wf_item_type,wf_item_key
from po_headers_all
where segment1='168/2005'
SQL>@wfstatus.sql
Enter value for 1: POAPPRV
Enter value for 2: 3319-4115
SQL>@wfretry.sql
Enter value for 1: POAPPRV
Enter value for 2: 3319-4115
Lable : POAPPRV_TOP
Command :RETRY
Result :NULL
INVENTORY (STORES) COSTED ERROR
Stop the COST manager or CONCURRENT manager
1)select * from mtl_material_transactions where costed_flag='E'
Confirm the error and find the organization_id and transaction_id
2)SELECT organization_id,
default_cost_group_id
FROM mtl_parameters
WHERE organization_id = '155'
(place organization id from the step 1)
2) UPDATE mtl_material_transactions
SET transfer_cost_group_id = &dcgi
WHERE tranasction_id = &txn_id;
(&dcgi = default_cost_group_id and &txn_id = from step1)
3) UPDATE mtl_material_transactions
SET costed_flag = 'N',
transaction_group_id = null,
error_code = null,
error_explanation = null
WHERE transaction_id = &txn_id;
(&txn_id= from step1)
update mtl_material_transactions
set request_id = null,
costed_flag = 'N',
transaction_group_id = null,
transaction_set_id = null,
cost_group_id = transfer_cost_group_id
where costed_flag = 'E'
and transaction_id = '3392889'
Start the COST manager or CONCURRENT manager
To select the username,process,status,Terminal name using SID
select a.status,p.spid, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c,v$process p
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and p.addr = a.paddr
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
To see the currently updated archive log files
SQL> select name from v$archived_log
where trunc(completion_time) >= trunc(sysdate)-5;
To find the BDUMP,UDUMP directory
select value from v$parameter where name = 'background_dump_dest'
select value from v$parameter where name = 'user_dump_dest'
select value from v$parameter where name in ('background_dump_dest','user_dump_dest', 'log_archive_dest')
To enable archive log
log_archive_start = true
log_archive_format = arch_%s_%t.arc
log_archive_dest = '/oracle/archive'
Tracing an Oracle session by SID
This code accepts an Oracle session ID [SID] as a parameter and will show you what SQL statement is running in that session and what event the session is waiting for. You simply create a SQL file of the code and run it from the SQL prompt.
prompt Showing running sql statements ...........................
select a.sid Current_SID, a.last_call_et ,b.sql_text
from v$session a
,v$sqltext b
where a.sid = 14
and a.username is not null
and a.status = 'ACTIVE'
and a.sql_address = b.address
order by a.last_call_et,a.sid,b.piece;
prompt Showing what sql statement is doing.....................
select a.sid, a.value session_cpu, c.physical_reads,
c.consistent_gets,d.event,
d.seconds_in_wait
from v$sesstat a,v$statname b, v$sess_io c, v$session_wait d
where a.sid= 14
and b.name = 'CPU used by this session'
and a.statistic# = b.statistic#
and a.sid=c.sid
and a.sid=d.sid;
Check all active processes, the latest SQL, and the SQL hit ratio
select a.status, a.sid, a.serial#, a.username, a.terminal,
a.osuser, c.Consistent_Gets, c.Block_Gets, c.Physical_Reads,
(100*(c.Consistent_Gets+c.Block_Gets-c.Physical_Reads)/
(c.Consistent_Gets+c.Block_Gets)) HitRatio, c.Physical_Reads, b.sql_text
from v$session a, v$sqlarea b, V$SESS_IO c
where a.sql_hash_value = b.hash_value
and a.SID = c.SID
and (c.Consistent_Gets+c.Block_Gets)>0
and a.Username is not null
and a.status = 'ACTIVE'
Order By a.status asc, c.Consistent_Gets desc , c.Physical_Reads desc;
3278532
Monitoring Oracle processes
select p.spid "Thread ID", b.name "Background Process", s.username
"User Name",
s.osuser "OS User", s.status "STATUS", s.sid "Session ID",
s.serial# "Serial No.",
s.program "OS Program"
from v$process p, v$bgprocess b, v$session s
where s.paddr = p.addr and b.paddr(+) = p.addr
order by s.status,1;
TO FIND OUT USER NAME AND PROCESS STATUS
SELECT REQUEST_ID,
TO_CHAR(a.ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(a.ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((a.ACTUAL_COMPLETION_DATE - a.ACTUAL_START_DATE)*(60*24),2) rtime,
b.user_name,a.phase_code,a.status_code,
a.printer,a.print_style,a.description,
SUBSTR(a.completion_text,1,20) compl_txt
FROM fnd_concurrent_requests a,fnd_user b
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-MON-RRRR') and a.requested_by = b.user_id
And a.phase_code = ‘R’
ORDER BY 1 desc,2
TO FIND OUT UGA and PGA STATUS FOR ALL SID
SELECT V.sid,
P.SPID "OS_PID",
P.USERNAME "OS_USERNAME",
U.USERNAME "USERNAME",
P.PROGRAM "PROGRAM",
B.NAME,
TO_CHAR(V.VALUE,'999,999,999.99')
FROM V$SESSTAT V, V$STATNAME B, V$SESSION U, V$PROCESS P
WHERE V.STATISTIC# = B.STATISTIC# AND
U.SID = V.SID AND
(B.NAME LIKE '%pga%' OR B.NAME LIKE '%uga%') AND
U.USERNAME <> ' ' AND
U.PADDR = P.ADDR
ORDER BY V.SID,B.NAME;
Displays concurrent requests that have run times longer than one hour (3600 seconds)
SELECT REQUEST_ID,
TO_CHAR(ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') starttime,
TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
ROUND((ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE)*(60*24),2) rtime,
OUTCOME_CODE,phase_code,status_code,
printer,print_style,description,
SUBSTR(completion_text,1,20) compl_txt
FROM fnd_concurrent_requests
WHERE to_date(ACTUAL_START_DATE,'DD-MON-RRRR') = to_date(sysdate,'DD-
MON-RRRR')
ORDER BY 2 desc
This script will map concurrent manager process information about current concurrent managers.
SELECT proc.concurrent_process_id concproc,
SUBSTR(proc.os_process_id,1,6) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(vproc.spid,1,10) svrproc,
DECODE(proc.process_status_code,'A','Active',
proc.process_status_code) cstat,
SUBSTR(concq.concurrent_queue_name,1,30) qnam,
-- SUBSTR(proc.logfile_name,1,20) lnam,
SUBSTR(proc.node_name,1,10) nnam,
SUBSTR(proc.db_name,1,8) dbnam,
SUBSTR(proc.db_instance,1,8) dbinst,
SUBSTR(vsess.username,1,10) dbuser
FROM fnd_concurrent_processes proc,
fnd_concurrent_queues concq,
v$process vproc,
v$session vsess
WHERE proc.process_status_code = 'A'
AND proc.queue_application_id = concq.application_id
AND proc.concurrent_queue_id = concq.concurrent_queue_id
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+)
ORDER BY proc.queue_application_id,
proc.concurrent_queue_id
Show currently running concurrent requests
SELECT SUBSTR(LTRIM(req.request_id),1,15) concreq,
SUBSTR(proc.os_process_id,1,15) clproc,
SUBSTR(LTRIM(proc.oracle_process_id),1,15) opid,
SUBSTR(look.meaning,1,10) reqph,
SUBSTR(look1.meaning,1,10) reqst,
SUBSTR(vsess.username,1,10) dbuser,
SUBSTR(vproc.spid,1,10) svrproc,
vsess.sid sid,
vsess.serial# serial#
FROM fnd_concurrent_requests req,
fnd_concurrent_processes proc,
fnd_lookups look,
fnd_lookups look1,
v$process vproc,
v$session vsess
WHERE req.controlling_manager = proc.concurrent_process_id(+)
AND req.status_code = look.lookup_code
AND look.lookup_type = 'CP_STATUS_CODE'
AND req.phase_code = look1.lookup_code
AND look1.lookup_type = 'CP_PHASE_CODE'
AND look1.meaning = 'Running'
AND proc.oracle_process_id = vproc.pid(+)
AND vproc.addr = vsess.paddr(+);
----------------------------------------------------------------------------------
