11i/R12 Scripts

OS Level Command

[root@sonu ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.4 (Tikanga)
 
[root@sonu ~]# cat /proc/version
Linux version 2.6.18-164.el5 (mockbuild@x86-002.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-46)) #1 SMP Tue Aug 18 15:51:54 EDT 2009
 
[root@sonu ~]# getconf LONG_BIT
32
[root@sonu ~]# uname -a
Linux sonu.oracle.com 2.6.18-164.el5 #1 SMP Tue Aug 18 15:51:54 EDT 2009 i686 i686 i386 GNU/Linux
 
sysdba SQL command:-(11g database)
 
[oracle@r0469 ~]$ cd /oracle/VIS/db/tech_st/11.1.0/
[oracle@r0469 11.1.0]$ . VIS_r0469.env
[oracle@r0469 11.1.0]$ sqlplus
 
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Sep 19 11:35:19 2012
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 
Enter user-name: sys as sysdba
Enter password: sys
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select * from v$version;
 
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
PL/SQL Release 11.1.0.7.0 – Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 – Production
NLSRTL Version 11.1.0.7.0 – Production
 
SQL> select
INST_ID,
INSTANCE_NUMBER ,
INSTANCE_NAME ,
HOST_NAME ,
VERSION ,
STATUS ,
PARALLEL ,
THREAD#
from gv$instance ; 
 
INST_ID INSTANCE_NUMBER INSTANCE_NAME  HOST_NAME     VERSION         STATUS         PAR    THREAD#
1                          1                                 VIS                     r0469.oracle.com           11.1.0.7.0         OPEN                 NO        1
 
 
SQL> select version from orasso.wwc_version$;
 
VERSION
——————————————————————————–
10.1.2.0.2
 
APPS User SQL COMMAND:-(R12.1.1)
 
[oracle@ebs602 scripts]$ sqlplus
 
SQL*Plus: Release 10.1.0.5.0 – Production on Sun Apr 19 11:27:04 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Enter user-name: apps
Enter password: apps
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select release_name from apps.fnd_product_groups;
 
RELEASE_NAME
————————————————–
12.1.1
 
PROMPT MAJOR TABLE LIST 

select owner, table_name, num_rows, LAST_ANALYZED 
From dba_tables 
where table_name in ( 
‘AP_INVOICES_ALL’, ‘AP_INVOICE_DISTRIBUTIONS_ALL’, ‘AR_PAYMENT_SCHEDULES_ALL’, 
‘RA_CUSTOMER_TRX_ALL’, ‘RA_CUSTOMER_TRX_LINES_ALL’ , 
‘HZ_PARTIES’, ‘HZ_CUST_ACCOUNTS’, 
‘AS_SALES_LEADS’, ‘AS_ACCESSES_ALL_ALL’, 
‘BOM_STRUCTURES_B’, ‘BOM_COMPONENTS_B’, 
‘CS_INCIDENTS_ALL_B’, 
‘FA_ADJUSTMENTS’, ‘FA_DEPRN_DETAIL’, ‘FA_DEPRN_SUMMARY’, 
‘FND_USERS’, 
‘GL_JE_HEADERS’, ‘GL_JE_LINES’, 
‘MRP_GROSS_REQUIREMENTS’, ‘MRP_RECOMMENDATIONS’, ‘MRP_FULL_PEGGING’, 
‘MRP_BOM_COMPONENTS’, ‘MTL_MATERIAL_TRANSACTIONS’, 
‘MTL_TRANSACTION_ACCOUNTS’, ‘MTL_SYSTEM_ITEMS_B’, 
‘HR_ORGANIZATION_INFORMATION’, ‘HR_OPERATING_UNITS’, 
‘MTL_PARAMETERS’, 
‘OE_ORDER_HEADERS_ALL’, ‘OE_ORDER_LINES_ALL’, 
‘PO_HEADERS_ALL’, ‘PO_LINES_ALL’, ‘PO_VENDORS’, 
‘WF_ITEM_ACTIVITY_STATUSES’, ‘WF_ITEM_ATRIBUTE_VALUES’, 
‘WF_NOTIFICATIONS’, ‘WF_NOTIFICATION_ATTRIBUTES’ , 
‘WSH_DELIVERY_DETAILS’ , ‘WSH_DELIVERY_ASSIGNMENTS’, 
‘WSH_NEW_DELIVERIES’, ‘WSH_DELIVERY_LEGS’, 
‘WSH_TRIP_STOPS’, ‘WSH_TRIPS’ ) 
order by table_name ;

 
PROMPT number of daily concurrent requests. 

SQL> SELECT trunc(REQUESTED_START_DATE), count(*)
FROM FND_CONCURRENT_REQUESTS
WHERE REQUESTED_START_DATE BETWEEN sysdate-30 AND sysdate
group by rollup(trunc(REQUESTED_START_DATE)) ;
 
PROMPT Applications versions and family pack versions

COL APPLICATION_NAME FORMAT A60 
COL SHT_NAME FORMAT A10 
col PATCH_LEVEL FORMAT A20 
SELECT A.APPLICATION_SHORT_NAME SHT_NAME,T.APPLICATION_NAME, I.STATUS, 
NVL(I.PATCH_LEVEL, ‘n/a’) PATCH_LEVEL, I.DB_STATUS 
FROM FND_PRODUCT_INSTALLATIONS I, 
FND_APPLICATION A, 
FND_APPLICATION_TL T 
WHERE A.APPLICATION_ID = I.APPLICATION_ID 
AND A.APPLICATION_ID = T.APPLICATION_ID 
AND T.LANGUAGE = USERENV(‘LANG’) 
ORDER BY 1 ;

 
PROMPT Multi-org being used.
SQL> select MULTI_ORG_FLAG org, MULTI_LINGUAL_FLAG lingual, MULTI_CURRENCY_FLAG currency
from FND_PRODUCT_GROUPS ;   2 
 O L C
- – -
Y   Y
 
PROMPT DB size with Tablespace
 
set head on 
set pagesize 30 
select NVL(tablespace_name,’** Total **’) “Tablespace Name”, 
sum(“allocated”) “Allocated(M)”, 
sum(“used”) “Used(M)”, 
sum(“free”) “Free(M)”, 
sum(df_cnt) “#(File)” 
from 

select a.tablespace_name, trunc(b.assigned/1048576) “allocated”, 
trunc((b.assigned-a.free)/1048576) “used”, 
trunc(a.free/1048576) “free”, 
df_cnt 
from 

select tablespace_name, sum(bytes) free 
from dba_free_space 
group by tablespace_name ) a, 

select tablespace_name, sum(bytes) assigned, count(*) df_cnt 
from dba_data_files 
group by tablespace_name ) b 
where a.tablespace_name = b.tablespace_name 
UNION ALL 
SELECT tablespace_name||’[TMP]‘, trunc(sum(bytes)/1048576), null, null, count(*) df_cnt 
from dba_temp_files 
group by tablespace_name 

group by rollup(tablespace_name) ;
 
Switch SQL promt to command promt:-
SQL> !
[oracle@ebs602 scripts]$
 
SQL> exit
[oracle@ebs602 scripts]$
 
Startup Applications (R12.1.1)
 
1. Start Database first:-
[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/
[oracle@r0469 VIS_r0469]$ ./addbctl.sh start
 
Connected to an idle instance.
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size                  1318172 bytes
Variable Size             427819748 bytes
Database Buffers          629145600 bytes
Redo Buffers               13049856 bytes
Database mounted.
Database opened.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 addbctl.sh: exiting with status 0
 
2. Start Listener:-
[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/
[oracle@r0469 VIS_r0469]$ ./addlnctl.sh start VIS
Starting listener process VIS …
 
 addlnctl.sh: exiting with status 0
 
3. Start Applications:-
[oracle@r0469 ~]$ cd /oracle/VIS/apps/apps_st/appl/
[oracle@r0469 appl]$ . APPSVIS_r0469.env
[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME
[oracle@r0469 scripts]$ ./adstrtal.sh apps/apps
 
All enabled services for this node are started.
adstrtal.sh: Exiting with status 0
adstrtal.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adstrtal.log for more information …
 
NOTE:- Open web page enter <IP Address:port pool>
URL :- 192.16.1.354:8000
open Application Login page…
 
Down Applications (R12.1.1)
 
1. Down Application Tier:-
[oracle@r0469 ~]$ cd /oracle/VIS/apps/apps_st/appl/
[oracle@r0469 appl]$ . APPSVIS_r0469.env
[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME
[oracle@r0469 scripts]$ ./adstpall.sh apps/apps
All enabled services on this node are stopped.
 adstpall.sh:Exiting with status 0
 
adstpall.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adstpall.log for more information … 
 
 2. Down Listener:-
[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/
[oracle@r0469 VIS_r0469]$ ./addlnctl.sh stop VIS
 
You are running addlnctl.sh version 120.1.12010000.2
 
Logfile: /oracle/VIS/db/tech_st/11.1.0/appsutil/log/VIS_r0469/addlnctl.txt
 
Shutting down listener process VIS …
addlnctl.sh: exiting with status 0
 
3. Down database Tier:-
 
[oracle@r0469 scripts]$ cd /oracle/VIS/db/tech_st/11.1.0/appsutil/scripts/VIS_r0469/
[oracle@r0469 VIS_r0469]$ ./addbctl.sh stop immediate
Connected.
Database closed.
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 addbctl.sh: exiting with status 0
 
 
       How to check Database & Application UP & Running:-
 
Check how many Database UP & Down:-
[oracle@r0469 ~]$ ps -ef | grep pmon
oracle   22862     1  0 12:19 ?        00:00:00 ora_pmon_VIS
oracle   24395 20422  0 12:27 pts/0    00:00:00 grep pmon
 
Hear only one database UP (VIS)
 
Check Listener UP  & Down:-
[oracle@r0469 ~]$ ps -ef | grep tns
oracle   22952     1  0 12:21 ?        00:00:00 /oracle/VIS/db/tech_st/11.1.0/bin/tnslsnr VIS -inherit
oracle   23123     1  0 12:22 ?        00:00:00 /oracle/VIS/apps/tech_st/10.1.2/bin/tnslsnr APPS_VIS -inherit
oracle   24399 20422  0 12:27 pts/0    00:00:00 grep tns
 Hear Listener Up & Running
 
Check Application UP & Down:-
[oracle@r0469 ~]$ ps -ef | grep FNDLIBR
oracle   23557 23524  0 12:23 pts/0    00:00:00 FNDLIBR                                                                                                                                     
oracle   23851 23730  0 12:24 ?        00:00:00 FNDLIBR                                                                                                                                                                                                                                       
oracle   23854 23730  0 12:24 ?        00:00:00 FNDLIBR                                                                                                                                                                                                                                       
oracle   23855 23730  0 12:24 ?        00:00:00 FNDLIBR                                                                                                                                                                                                                                      
oracle   23860 23730  0 12:24 ?        00:00:00 FNDLIBR                                                                                                                                                                                                                                        
oracle   24404 20422  0 12:28 pts/0    00:00:00 grep FNDLIBR
 
[oracle@r0469 ~]$ ps -ef | grep FNDLIBR |wc -l
6
 
Hear Application Up & Running….
 
 
count concurrent_users in Oracle apps:-
 
SQL> select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = ‘USER_NAME’ OR 1=1);
 
 COUNT(DISTINCTD.USER_NAME)
————————–
                         3
This will give the number of users on the system in the past 1 hour:-
SQL> select count(distinct user_id) “users” from icx_sessions where  last_connect > sysdate – 1/24 and user_id != ‘-1′;
  users
———-
         4
This will give the number of users on the system in the past 1 day:-
SQL> select count(distinct user_id) “users” from icx_sessions where  last_connect > sysdate – 1 and user_id != ‘-1′;
   users
———-
        12
This will show the activity in the last 15 minutes:-
SQL> select limit_time, limit_connects, to_char(last_connect, ‘DD-MON-RR HH:MI:SS’) “Last Connection time”, user_id, disabled_flag from icx_sessions where  last_connect > sysdate – 1/96;
 
 LIMIT_TIME LIMIT_CONNECTS Last Connection time           USER_ID D
———- ————– ————————— ———- -
       999           9000 19-SEP-12 02:25:37             1013630               N
       999           9000 19-SEP-12 02:12:50             1013691               Y
       999           9000 19-SEP-12 02:12:58                   6                     N
       999           9000 19-SEP-12 02:25:23             1013555               N
 
Total size of Database:-
SQL> SELECT ROUND(SUM(bytes/1024/1024/1024)) FROM dba_data_files;
 
 
ROUND(SUM(BYTES/1024/1024/1024))
——————————–
                             184
 
Free size of database:-
SQL> SELECT /*+ FIRST_ROWS*/ROUND(SUM(bytes/1024/1024/1024)) FROM dba_free_space;
 
 ROUND(SUM(BYTES/1024/1024/1024))
——————————–
                              38
 
All Invalid objects in database:-
SQL> SELECT COUNT(*) FROM all_objects WHERE Status = ‘INVALID';
 
 COUNT(*)
———-
       641
 
Total Blocking Session:-
SQL> SELECT count(*) FROM v$lock WHERE BLOCK=’1′;
 
 COUNT(*)
———-
         0
 
Total Locked objects:-
SQL> SELECT COUNT(*) FROM v$locked_object;
 
 COUNT(*)
———-
         1
Total no of session :-
 SQL> SELECT count(*) FROM v$session;
COUNT(*)
———-
       105
Total no of concurrent Process:-
SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_processes;
 COUNT(*)
———-
       672
 
Total no of concurrent request:-
SQL> SELECT COUNT(*) FROM apps.fnd_concurrent_requests;
COUNT(*)
———-
     21681
 
Running Concurrent Requests and scheduled Concurrent Requests:-
SQL> SELECT phase_code,COUNT(*) FROM apps.fnd_concurrent_requests WHERE phase_code IN(‘P’,’R’) GROUP BY phase_code ORDER BY 1 DESC;
P   COUNT(*)
- ———-
P          4
***To check files which are of huge size
du -a .| sort -n -r |head -n 10

** my session details **
    select sid,serial# from v$session where sid=(select sid from v$mystat where rownum=1);

**GRid agent bounce **
emctl status agent
emctl stop agent
emctl clearstate agent
emctl start agent
emctl status agent

******To check top 5 process consuming more cpu*****
    ps axo pcpu,comm,pid,ruser,time,etime,args  | sort -nr | head -n 5

**** To check top 5 process consuming more memory ****
ps auxf | sort -nr -k 4 | head -5
*********************************

To kill LIBR process
ps -ef | grep applmgr |awk ‘{print “kill -9 ” $2 }’

To kill LOCAL-NO process—
kill -9 `ps -ef  |grep LOCAL=NO | grep gcasq | awk ‘{print $2}’`

crs_stat -t
crsctl check crs
srvctl start nodeapps -n hostname
srvctl start instance -d utyeb05 -i utyeb051
   
SSO-Degistations issue:
[applmgr@hostname bin]$ ldapbind -h -p 389 -D “cn=orcladmin” -w
bind successful

ls -ltr | grep ‘Aug 28′ |grep -v ‘.Z’ |awk ‘{print “gzip ” $9 ” & ” }’ > Aug28.sh

select INSTANCE_NUMBER,SQL_ID,USER_ID,MODULE,ACTION from DBA_HIST_ACTIVE_SESS_HISTORY where MODULE=’&Module';

select CONCURRENT_QUEUE_NAME,NODE_NAME,TARGET_NODE,NODE_NAME2 from fnd_concurrent_queues;

select ‘drop database link ‘||DB_LINK||';’ from dba_db_links;

update fnd_concurrent_queues set NODE_NAME=’hostnameRS06′, NODE_NAME2=’hostnameRS06′,TARGET_NODE=’hostnameRS06′ where ENABLED_FLAG=’Y';

srvctl status database -d utyeb05

ALTER SESSION SET NLS_DATE_FORMAT =’DD-MON-YY HH24:MI:SS';
select sysdate from dual;

set lines 300
select INST_ID,HOST_NAME,INSTANCE_NAME,TO_CHAR(STARTUP_TIME, ‘DD-MON-RR HH24:MI:SS’) FROM gv$instance;

col “Conc_Node” for a20
col “Conc_Node” for a10
col “Form_Node” for a10
col “Web_Node” for a10
col Admin_Node for a10
col DB_Node for a10
select NODE_NAME,
decode(SUPPORT_CP,’Y’,’Yes’,’No’) “Conc_Node”,
decode(SUPPORT_FORMS,’Y’,’Yes’,’No’) “Form_Node”,
decode(SUPPORT_WEB,’Y’,’Yes’,’No’) “Web_Node”,
decode(SUPPORT_ADMIN,’Y’,’Yes’,’No’) “Admin_Node”,
decode(SUPPORT_DB,’Y’,’Yes’,’No’) “DB_Node”,
decode(STATUS,’Y’,’Enabled’,’Not Enabled’) “Status”
from apps.fnd_nodes order by 1;

************************************************************************************************************************

 ***    Users are not able to view responsiblity getting error like “There are no active responsibilities available for this user.” ***
CONCSUB APPS/appsdev SYSADMIN ‘System Administrator’ SYSADMIN WAIT=N CONCURRENT FND FNDFMRTC PROGRAM_NAME='”Synchronize WF LOCAL Tables”‘
bounce apache.

OR

Please run below con. and see its helpful
- “Sync responsibility role data into the WF table.”
- “Synchronize WF LOCAL tables”
- “Workflow Directory Services User/Role Validation”

************************************************************************************************************************
EXPLAIN PLAN for SQL_ID
select * from table (dbms_xplan.display_awr(‘c53c4b8d0yxhz’));

************************************************************************************************************************

to find the version of package from database;
select text from  dba_source where name like ‘ECS_AR_EDI810_OUTINT_PKG’ and line < 10

************************************************************************************************************************
IF opmn services is not coming up then do clean shutdown of all application services then remove following directories.

rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
        rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
        rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

then start opmn services and check services.

************************************************************************************************************************

SELECT distinct frt.RESPONSIBILITY_NAME, furg.end_date
FROM
         fnd_user_resp_groups furg,
         FND_RESPONSIBILITY fr,
         fnd_responsibility_tl frt,
         fnd_user fu
WHERE fu.user_name =’&user_name’
AND   fu.user_id = furg.user_id
AND   furg.responsibility_id = fr.RESPONSIBILITY_ID
AND   frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

************************************************************************************************************************

SIZE of the database

select a.data_size+b.temp_size+c.redo_size “total_size”
from ( select sum(bytes)/1024/1024/1024 data_size
         from dba_data_files ) a,
     ( select nvl(sum(bytes)/1024/1024/1024,0) temp_size
         from dba_temp_files ) b,
     ( select sum(bytes)/1024/1024/1024 redo_size
         from sys.v_$log ) c;

************************************************************************************************************************
 ***************** Track redo generation by day in GB *****************
 select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024/1024)) “REDO PER DAY (MB)”
from v$archived_log
group by trunc(completion_time)
order by 1;

************************************************************************************************************************
—– To Check java heap size per process ——
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = ‘FNDCPOPP’);

 ***************** Scheduled Tasks That Failed**************************
 COL log_id              FORMAT 9999   HEADING ‘Log#’
COL log_date            FORMAT A40    HEADING ‘Log Date’
COL owner               FORMAT A06    HEADING ‘Owner’
COL job_name            FORMAT A30    HEADING ‘Job’
COL status              FORMAT A10    HEADING ‘Status’
COL actual_start_date   FORMAT A50    HEADING ‘Actual|Start|Date’
COL error#              FORMAT 999999 HEADING ‘Error|Nbr’

TTITLE ‘Scheduled Tasks That Failed:’

select
   log_date,
   job_name,
   status,
   req_start_date,
   actual_start_date,
   run_duration
from
   dba_scheduler_job_run_details
where
–   job_name = ‘MYJOB’
   status <> ‘SUCCEEDED’
order by
   actual_start_date;

 ******************************************
select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj

Standby Database ************************
run {
allocate channel d1 device type disk format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak';
allocate channel d2 device type disk format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak';
backup as compressed backupset incremental level 0 format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbybak’ database include current controlfile for standby;
sql “alter system archive log current”;
backup archivelog all format ‘C:\oracle\product\10.2.0\oradata\standby\%U.standbyarch’ ;
}

run
{
duplicate target database for standby dorecover;
}

Put the standby database in managed recovery mode and open the primary database with the new protection mode enabled:
SQL> — [Connect to the standby database]
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The protection modes run in the order from highest (most data protection) to the lowest (least data protection):

Protection ,  Maximum Availablity  and Maximum performance.

alter system set log_archive_dest_1=’LOCATION=E:\rman';

*********************Block Corruption**********************

DECLARE num_corrupt INT;
CURSOR cur_table IS
SELECT table_name
FROM dba_tables d
WHERE d.owner=’EXPRESS';
BEGIN
FOR rec_table IN cur_Table
LOOP
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => ‘EXPRESS’,
OBJECT_NAME => rec_Table.table_name,
REPAIR_TABLE_NAME => ‘REPAIR_TABLE’,
CORRUPT_COUNT => num_corrupt);
IF num_corrupt > 0 THEN
DBMS_OUTPUT.PUT_LINE(‘Table Name : ‘||rec_table.table_name||’ number corrupt: ‘ || TO_CHAR (num_corrupt));
END IF;
END LOOP;

END;

select owner, segment_name, segment_type from dba_extents where file_id = 60 and 25759 between block_id and block_id + blocks -1;
***********************************************************************************************

##Script to monitor>>>>>>>
while true
do

sqlplus -s “/ as sysdba” <set linesize 140
set pagesize 9999
col message for  a60

select sum(bytes)/1024/1024/1024 as “TOTAL UNDO SIZE” from dba_data_files where tablespace_name=’UNDOTBS1′;

#select tablespace_name,round(sum(bytes)/1024/1024)  as “SPACE IN MB” from dba_free_space where tablespace_name=’ARW_DATA’ group by tablespace_name order by 1;

col “Name” format a10
select a.tbl “Name”,a.tsz “Total Size”,b.fsz “Free Space”,
round((1-(b.fsz/a.tsz))*100) “Pct Used”,round((b.fsz/a.tsz)*100) “Pct Free” from
        (select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
        where tablespace_name = ‘DATA’ group by tablespace_name) a,
        (select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
        where tablespace_name = ‘DATA’ group by tablespace_name) b
Where a.tbl=b.tblsp;

col file_name for a60
select file_name,bytes/1024/1024/1024 from dba_data_files
where tablespace_name =’DATA';

#echo “Temp Space”
select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE”  from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP’ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;

SELECT SUM(BYTES_FREE)/1024/1024 as “SPACE IN MB TEMP” ,tablespace_name FROM V\$TEMP_SPACE_HEADER group by tablespace_name;

select sid,message,time_remaining/60 from v$session_longops where time_remaining>0;
!df -g /iu147/u05/oradata/ecsuat2/arch

Eof
sleep 15
clear
done

#!/bin/sh

for file in *.fmb
do
c
done

[applmgr@hostnamerh157 bin]$ cat a_patch.sh
export ORACLE_SID=`echo $TWO_TASK | tr [:lower:] [:upper:]`
export PATCH_NO=$1

adpatch logfile=${PATCH_NO}_${ORACLE_SID}.log patchtop=$PWD driver=u${PATCH_NO}.drv

**********Basic commands to check system statistics *************
Solaris:
=========

$ /usr/sbin/prtconf |grep -i “Memory size”
$ swap -s
$ df -k
$ /usr/local/bin/top
$ vmstat 5 100
$ sar -u 2 100
$ iostat -D 2 100
$ mpstat 5 100

Prtconf command will show how much Physical Memory

Swap command will provide us with the usage of swap space including the RAM.

Df command will indicate how much space is free in each mount point and also
provides information about swap space(s).

Top command wil provide the above information plus information about top CPU
consuming processes, CPU usage in the system, etc.

Vmstat will provide information about process run queue, memory usage,
paging and swapping and CPU % usage in the server.

Iostat provides IO usage by Disk, CPU % usage, etc. depending on the options
used.

Mpstat will provide CPU usage stats for a Solaris server with 1 or more CPUs.

So, the below OS commands also will give the same output related to this command outputs.

HP-Unix 11.0:
============

$ grep Physical /var/adm/syslog/syslog.log
$ df -k
$ sar -w 2 100
$ sar -u 2 100
$ /bin/top
$ vmstat -n 5 100
$ iostat 2 100
$ top

AIX:
=======

$ /usr/sbin/lsattr -E -l sys0 -a realmem
$ /usr/sbin/lsps -s
$ vmstat 5 100
$ iostat 2 100
$ /usr/local/bin/top # May not be installed by default in the server

Linux
======

$ dmesg | grep Memory
$ vmstat 5 100
$ /usr/bin/top

to set backspace
#############

stty erase {backspace}
this will set backspace.
###################

to change owner
###################
chown oracle:dba oracle
owner=oracle group=dba directory=oracle
##############################

to know CPU info
#################
psrinfo

uname -X
###################

to know Oracle Version(64 bit/32 bit):
#######################

go to $RACLE_HOME/bin..
then $file oracle or $file sqlplus–>
[gemsora13s:/gemsora13s/u01/app/oracle/product/9206/bin]
stgcpq:oracle>file sqlplus
sqlplus:        ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped

to create/modify a file’s date/timestamp:
##########################################

touch -m 01092200 dam

(create a file with timestamp jan 09 22:00 hrs

To copy a file with the same timestamp:
#######################################
cp -p dam dam1

To set LD_library_path:
#######################
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/gemsora15d/u01/app/oracle/product/10.1.0.4/dbee_1/lib:  or
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:.

To change the os users settings:
################################

conn as root,
DEFAULT:oracle>cat /etc/passwd|grep oracle
oracle:x:1001:200:Oracle Client Software Owner:/export/home/oracle:/bin/ksh
+@users_dba_oracle:x:::::

vi /etc/passwd

then the shell from sh to ksh,chanhe ora_home,etc..

oracle:x:1001:200:Oracle Client Software Owner:/export/home/oracle:/bin/ksh
osuser:encripted pwd:uid:gid:mail send id:home dir:default shell

To find the contents of the Binary File
########################################
strings -a exp_file.dmp|grep “CREATE USER”
strings -a exp_file.dmp|grep “TABLE”

To change the server settings(database level)
##############################################
In every Oracle Home ($ORACLE_HOME/sqlplus/admin) there is a glogin.sql which takes care of the sql session settings for all dataabses in that particlar home.

To set these settings at the server level for all homes
go to cd $SQLPATH
[gemsora6:/export/home/oracle/aps7/sql]
edev:oracle>ls -ltr login*
-rwxr-xr-x   1 oracle   dba         1016 Jan 17 04:06 login.sql_jan17
-rwxr-xr-x   1 oracle   dba         1017 Jan 17 04:09 login.sql

Here u have a login.sql file which takes care of these settings.
Make whatever changes u want in this file and run the .profile again.
If you login to the database now, you will have new session settings.

Grep usage
################################################
grep dbaodc `crontab -l|grep -v ^#|awk ‘{print $6}’`

grep -i “keyword” *
list all the lines in the files where keyword in present

grep -il “odcebiz” */*/*
liosts all the files which have odcebiz keyword in them

expr
##################################
export a=5

devec2:oracle>b=`expr $a – 1`

[oradb5d:/gemsora5/u01/app/oracle/admin/local/scripts]
devec2:oracle>echo $b
4

[oradb5d:/gemsora5/u01/app/oracle/admin/local/scripts]
devec2:oracle>tail -`expr $a – 1` res_con.sql
END;
/
exit
###############################################
prstat -a —will give the processes running.

ps -ef — processes

ipcs – report inter-process communication facilities status (semaphorees)

ipcs -b == the last col gives the no of semaphores.

ipcrm – remove a message queue,  semaphore  set,  or  shared memory ID

sysresv — The chances in this sort of case is sometimes that the semaphores are not cleared we can check that using “sysresv” to ensure whether semaphores are cleared after database is brought down.

to set line numbers in vi editor
###########################
:set nu
###########################

to see all child processes
##########################
ptree -a

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

To replace gzip by mv

:%s/gzip/mv/g                                                                                                                                                                                                              2,1           All

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

to remove spaces at the end of lines
#####################################

put the file into vi..

:%s/  //g

to append something at the begining
####################################
to add spce at the begining..

:%s/^/ /g

:%s/^/dam/g

to append something at the end
###############################

:%s/$/ /g

:%s/$/dam/g

to know JDK version
#####################
olt2:oracle>which jdk
no jdk in /olcdbd/u01/app/oracle/product/9.2.0/bin /opt/bin /bin /usr/bin /usr/sbin /usr/ccs/bin /usr/ucb /etc /usr/local/bin /usr/openwin/bin .

[olcdbd:/home/evuri]
olt2:oracle>java -version
java version “1.2.2”
Solaris VM (build Solaris_JDK_1.2.2_10, native threads, sunwjit)

FNDCPASS apps/p1lgr1m 0 Y system/milwaukee USER   SYSADMIN SYSADMIN

give the command in source server
cd /p04/oracle/product/
tar cf – * | remsh srvr13-other ‘(cd /p04/oracle/product/; tar xf – )’

————————————
ln -s $FND_TOP/bin/fndcpesr gems_swe_payment_concat

—————————————-
to know the version of patch
go to $APPL_TOP
find . -name “filename”
strings -a ./po/11.5.0/lib/rvsrq.o |grep ‘$Header’
—————————————-
product password like ar ,ap
custom passwords like Gems_
———————————

to know the sum of bytes
ls -ltr /preprod/u*/oradata/preprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
601.858
356
ls -ltr /glprod/u*/oradata/glprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
2000.87
1473
——————————————
dba file share location.
\\Uswaufs04medge\dba\GLPROD\GLPROD_DOCS
—————————————-
to remove some files from srvr date

find * -mtime +3 -exec \rm {} \;
————————————————-
cat nar|awk ‘{print “mv ” $2 ” /u92/oracle/itest2/bdump/.”}’>kum.sh
————————————————–
grep -i yes gltab
——————————————-
prodcopy—web server67
glprod—web servers
srvr21, srvr22, srvr37, srvr38, srvr39, srvr72, srvr73,
The web servers for PRODERP are SRVR65, SRVR63, SRVR64, SRVR75 and SRVR76
==================================================================
execute the below command
To list
/usr/openv/netbackup/bin/bplist -C srvr92.it.med.ge.com -B -b -k srvr92_manual_db -l -R -s 04/18/2004 ‘/lpadev9/u*/oradata/lpatest/*’
to restore
/usr/openv/netbackup/bin/bprestore -w -C srvr92.it.med.ge.com -B -K -s /04/18/2004 -c srvr92_manual_db -L /tmp/lpatest_restore.log ‘/lpadev9/u*/oradata/lpatest/*’ &

————————————————————
I need a relink of the following objects in $CZ_TOP/bin on srvr23 (devl3c1)

libczjni.so
libcz.so
libczlce.so

aderlink.sh force=y “cz file_name”
——————————————————————-
/mitshr/appsora/prod/scripts/scrape_database—-tablespace monitoring.
===================================================================
to know how many files copied from source to target.

 o=0
while test true
do
        n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
        s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
        d=`expr ${s} – ${o}`
        echo “$n—>${s}Mb—>${d}Mb”
        o=${s}
        sleep 60
done

while test true
 do
       n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
       s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
       d=`expr ${s} – ${o}`
       echo “$n—>${s}Mb—>${d}Mb”
       o=${s}
       sleep 60
 done
———————————————————————————
to remove files on given date
ls -lt |grep “Oct  8″|awk ‘{print “rm -rf “$9}’>t1.sh
chmod 777 t1.sh
df -k .
./t1.sh
df -k .
ls -ltr|more
———————————————————
CPU utilization for the database wise:

/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file

split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_
————————————————————-
to compile the forms

f60gen module=$AU_TOP/resource/CUSTOM.pll userid=apps/readmen0w output_file=$AU_TOP/resource/CUSTOM.plx module_type=LIBRARY compile_all=YES
———————————————————————–
Refer Metalink Note: 50538.1  for more details.
——————————————————–
to list the files from tape

/usr/openv/netbackup/bin/bplist -l -R -B -C srvr77.it.med.ge.com -k srvr77_manual_db -s 07/19/2004  -e 07/19/2004 -l ‘/cwtest/u*/oradata/cwdev/*’ |more
————————————————————————–
rsh srvr31c “ps -ef|grep reorg”;rsh srvr31b “ps -ef|grep reorg”;rsh srvr31a “ps -ef|grep reorg”
————————————————-
to know the duplicate files in same file.
cat n1|sort|uniq -c|grep -v ” 2″
——————————————————–
To see the cpu usage on servers.
while true
do
Servers=”srvr28a srvr28b srvr28c”
for node in `echo ${Servers}`
do
echo $node—Utilization
rsh ${node} sar 1 1
done
sleep 60
done
————————————————————————
/opt/openssh/bin/ssh srvr93 hostname

/opt/openssh/bin/ssh -l applmgr srvr13 hostname

/opt/openssh/bin/ssh -l root srvr107 hostname

/opt/openssh/bin/ssh -l oracle srvr93 hostname
————————————————
Enabling SSH/ssh
—————-
/opt/openssh/bin/ssh-keygen -t rsa
1. ssh into srvr81 as the user that needs to have access from srvr23 and srvr28 and run the following command:
/opt/openssh/bin/ssh-keygen -t rsa

Here is a sample ouput of the command. Make sure the key is written to $HOME/.ssh/id_rsa ( this is the default). For the passphrase just hit a return (twice) to create an empty passphrase.

——————————————-
To change the owner ship for the dir or files

chown root:other

chown -R applmgr:dba applmgr
————————————————————————–
To tar and untar the file or dir

remsh erpora1s ‘(cd /tools/merant/; tar cf – * )’| tar xf -

tar cf – 8.1.7.4 | remsh srvr26 ‘(cd /pjmtest/u010/app/oracle/product/; tar xf – )’

tar cf – 8.1.6 | remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar xf – )’ &
remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar cf – * )’| tar xf – &

to do tar

tar -cvf naresh.tar *

to do untar

tar -xvf /u92/applmgr/erpora1s/naresh.tar
—————————————————-
to create dir
df -k /mmdev2/u* |awk ‘{ print $6 }’|grep -v Mount|while read line
do
mkdir -p $line/oradata/mmdev2
chown -R oracle:dba $line/oradata
done
—————————————————————-

ln -s /orashr/oracle/sql/dblinks/cr_db_link_for_devl3.sql dblinks.sql

ln -s /orashr/oracle/sql/passwords/gl_devl3_passwords.sql passwords.sql
———————————————————

${APPATH}adcmctl.sh $CMD $DBNAME $app_pwd  >>$dblog/ora_app_$CMD_$DBNAME.log

$FND_TOP/bin/CONCSUB apps/adm1n SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
——————————————————————————————–
lines=`grep -n shutdown alert_prodpnfm.log|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_prodpnfm.log|head -3
done
———————————————————-
restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-
to know the ORA-600 entries

lines=`grep -n ORA-00600 alert_glprod3.log|grep 729|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_glprod3.log|head -3
done
—————————————–
to know the bcsync refresh

10/18/2009

To generate dbc file
jre oracle.apps.fnd.security.AdminAppServer apps/adm1n@gltest ADD FNDNAM=apps GWYUID=applsyspub/pub TWO_TASK=gltest1 SECURE_PATH=$FND_TOP/secure GUEST_USER_PWD=guest/guest APPS_JDBC_DRIVER_TYPE=THIN DB_HOST=srvr28a.med.ge.com DB_PORT=1521 FND_MAX_JDBC_CONNECTIONS=50 SERVER_ADDRESS=3.20.66.33 SERVER_DESCRIPTION=srvr28a.med.ge.com

To Remove the Files in Specific Date

ls -ltr |grep “May “|awk ‘{print “rm -rf “$9}’ > sha.sh;chmod 755 sha.sh;sh sha.sh &

================================================================================
to know the sum of bytes
ls -ltr /preprod/u*/oradata/preprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
167.517
ls -ltr /preprod/u*/oradata/preprod/*.dbf|wc -l
####### For GLPROD######
ls -ltr /glprod/u*/oradata/glprod/*.dbf|wc -l
ls -ltr /glprod/u*/oradata/glprod/*.dbf|awk ‘{sum += $5} END {print sum/1024/1024/1024}’
==================================================================================================
—————————————-
to remove some files from srvr date

find * -mtime +3 -exec \rm {} \;
————————————————-
cat nar|awk ‘{print “mv ” $2 ” /u92/oracle/itest2/bdump/.”}’kum.sh
————————————————–
grep -i yes gltab
——————————————-
prodcopy—web server67
glprod—web servers
srvr21, srvr22, srvr37, srvr38, srvr39, srvr72, srvr73,
The web servers for PRODERP are SRVR65, SRVR63, SRVR64, SRVR75 and SRVR76
========================================================================================

to know how many files copied from source to target.

 o=0
while test true
do
        n=`ls -lt /preprod/u*/oradata/preprod/*.*|wc -l`
        s=`ls -lt /preprod/u*/oradata/preprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
        d=`expr ${s} – ${o}`
        echo “$n—${s}Mb—${d}Mb”
        o=${s}
        sleep 60
done

while test true
do
n=`ls -lt /glprod/u*/oradata/glprod//*.*|wc -l`
s=`ls -lt /glprod//u*/oradata/glprod/*.*|awk ‘{s+=$5} END{print int(s/1024/1024)}’`
d=`expr ${s} – ${o}`
echo “$n—${s}Mb—${d}Mb”
o=${s}
sleep 60
done

bounce the listeners

cd $dbbin/listener_stop.sh -l listener_name

cd $dbbin

ls -lis*

listener_start/stop.sh -l listener_name

CPU utilization for the database wise:

/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file

split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_

restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-

to remove files on given date
ls -lt |grep “Apr 20″|awk ‘{print “rm -rf “$9}’t1.sh
chmod 777 t1.sh
df -k .
./t1.sh
df -k .
ls -ltr|more

find . -size 524288000c      
———————————————————
CPU utilization for the database wise:

/usr/ucb/ps -auxwww|grep prdscv | grep -i “local=no”|awk ‘{s += $3} END {print float(s)}’
—————————————————————————-
cat cr_database.sql|grep -v redo|grep -v tempx|grep “.dbf”|wc -l
—————————————————————–
to split the big file

split -b 1024m devl1c1_ora_4758.trc /u92/oracle/devl1c1/TRACE/devl1c1_ora_4758_
————————————————————-
to compile the forms

f60gen module=$AU_TOP/resource/CSCCCCRC.pll userid=apps/obel1x1 output_file=$AU_TOP/forms/US/CSCCCCRC.fmb module_type=LIBRARY compile_all=YES
———————————————————————–
Refer Metalink Note: 50538.1  for more details.
——————————————————– 
L ist the files from tape

/usr/openv/netbackup/bin/bplist -l -R -B -C srvr77.it.med.ge.com -k srvr77_manual_db -s 07/19/2004  -e 07/19/2004 -l ‘/cwtest/u*/oradata/cwdev/*’ |more
————————————————————————–
rsh srvr31c “ps -ef|grep reorg”;rsh srvr31b “ps -ef|grep reorg”;rsh srvr31a “ps -ef|grep reorg”
————————————————-
to know the duplicate files in same file.
cat n1|sort|uniq -c|grep -v ” 2″
——————————————————–
To see the cpu usage on servers.
while true
do
Servers=”srvr28a srvr28b srvr28c”1:05 PM
for node in `echo ${Servers}`
do
echo $node—Utilization
rsh ${node} sar 1 1
done
sleep 60
done
————————————————————————
/opt/openssh/bin/ssh srvr93 hostname

/opt/openssh/bin/ssh -l applmgr srvr13 hostname

/opt/openssh/bin/ssh -l root srvr107 hostname

/opt/openssh/bin/ssh -l oracle srvr93 hostname
————————————————
Enabling SSH/ssh
—————-
/opt/openssh/bin/ssh-keygen -t rsa
1. ssh into srvr81 as the user that needs to have access from srvr23 and srvr28 and run the following command:
/opt/openssh/bin/ssh-keygen -t rsa

Here is a sample ouput of the command. Make sure the key is written to $HOME/.ssh/id_rsa ( this is the default). For the passphrase just hit a return (twice) to create an empty passphrase.

——————————————-
To change the owner ship for the dir or files

chown root:other

chown -R applmgr:dba applmgr
————————————————————————–
To tar and untar the file or dir

remsh erpora1s ‘(cd /tools/merant/; tar cf – * )’| tar xf -

tar cf – 8.1.7.4 | remsh srvr26 ‘(cd /pjmtest/u010/app/oracle/product/; tar xf – )’

tar cf – 8.1.6 | remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar xf – )’ &
remsh erpora1s ‘(cd /u01/app/oracle/product/8.1.6; tar cf – * )’| tar xf – &

to do tar

tar -cvf naresh.tar *

to do untar

tar -xvf /u92/applmgr/erpora1s/naresh.tar
—————————————————-
to create dir
df -k /mmdev2/u* |awk ‘{ print $6 }’|grep -v Mount|while read line
do
mkdir -p $line/oradata/mmdev2
chown -R oracle:dba $line/oradata
done
—————————————————————-

ln -s /orashr/oracle/sql/dblinks/cr_db_link_for_devl3.sql dblinks.sql

ln -s /orashr/oracle/sql/passwords/gl_devl3_passwords.sql passwords.sql
———————————————————

${APPATH}adcmctl.sh $CMD $DBNAME $app_pwd  $dblog/ora_app_$CMD_$DBNAME.log

$FND_TOP/bin/CONCSUB apps/adm1n SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND ABORT
——————————————————————————————–
lines=`grep -n shutdown alert_prodpnfm.log|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_prodpnfm.log|head -3
done
———————————————————-
restore command
/usr/openv/netbackup/bin/bprestore -s 08/15/2004 -L /tmp/restore.log -f
/export/home/oracle/filelst1 -R /export/home/oracle/renamelst1
—————————————————————-
to know the ORA-600 entries

lines=`grep -n ORA-00600 alert_glprod3.log|grep 729|awk -F: ‘{print $1}’`
for l in $lines
do
lno=$(( $l-1 ))
echo $lno
tail +$lno alert_glprod3.log|head -3
done
—————————————–
to know the bcsync refresh

—————-
alter tablespace RINGX begin backup;

/usr/openv/netbackup/bin/bpbackup -w -c srvr108_manual_db -L /tmp/failed_tlsprd1.log -f /tmp/failed_tlsprd1

alter tablespace RINGX end backup;

****************************************************************************************

$AD_TOP/bin/adconfig.sh contextfile=/u02/app/applmgr/115/devl3c1/admin/devl3c1_srvr16.xml appspass=adm1n

select to_char(last_analyzed,’dd-mon-yy’),table_name,partition_name from
dba_tab_partitions where table_name=’GL_BALANCES’
group by to_char(last_analyzed,’dd-mon-yy’),partition_name,table_name ;

ln -s /u01/app/oracle/admin/mmdev3/pfile/initmmdev3.ora

select a.PROFILE_OPTION_NAME,a.SQL_VALIDATION,b.LEVEL_ID,b.LEVEL_VALUE,
b.LAST_UPDATE_LOGIN,b.PROFILE_OPTION_VALUE
from (select * from fnd_profile_options) a,
(select * from fnd_profile_option_values) b
where a.APPLICATION_ID=b.APPLICATION_ID
and a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME like ‘%FORMS%';

/orashr/oracle/scripts/shell/refresh_f5_setup.sh -d itestc1
/orashr/oracle/scripts/shell/update_wf.sh itestc1

SELECT * FROM SO_JOB_QUEUE
WHERE so_queue=’ORACLE’
AND so_status_name  < ‘HOLD’
ORDER BY so_start_date;

SELECT SUM(PINS) “EXECUTIONS”,
            SUM(RELOADS) “CACHE MISSES WHILE EXECUTING”
            FROM V$LIBRARYCACHE;
appsora_ctl.sh -d prodcopy -s start -a

block_id file_id

printf “%d\n” 0x2f400b8a

select dbms_utility.data_block_address_file(792726410),
dbms_utility.data_block_address_block(792726410)
from dual;

select segment_name from dba_extents where file_id=189 and block_id=2954;

*******************************************************************
sar command
rac_swait.sql
rac_locks.sql
rac_libcachelock.sql
————————-

select  ‘ ‘||username|| ‘ alter system kill session ”’||sid||’,’||serial#||”';’
from v$session
where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status=’ACTIVE';

————————————
—————————
v$nls_parameters
stats$snapshot

select (SNAP_ID) from stats$snapshot;

alter database backup controlfile to ‘/${ORACLE_SID}/u010/oradata/${ORACLE_SID}_ctl.ctl';

create table fnd_concurrent_queues_bkp as select *from fnd_concurrent_queues;
                                   

alter tablespace users default storage(pctincrease 0);

select next_extent,initial_extent,extents,max_extents,tablespace_name from dba_segments
where owner=’WSH’ and segment_name=’MIT_WSH_TRIP_STOPS’ and segment_type=’INDEX';

alter index WSH.MIT_WSH_TRIP_STOPS storage(maxextents 4000);

alter table PO.CHV_SCHEDULE_ITEMS storage(maxextents 1000);

alter index GL.GL_POSTING_INTERIM_N1 storage(maxextents 500);

alter system set undo_retention=10800 ;

SELECT ‘ALTER INDEX PWMARDEV.’||INDEX_NAME||’ REBUILD TABLESPACE INDX01;’FROM DBA_INDEXES
WHERE OWNER=’PWMARDEV’ AND TABLESPACE_NAME=’DATA01′;

col USERNAME for a12
col OSUSER for a12
select s.sid, s.serial#, s.username, s.status, p.spid , s.osuser, s.terminal
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = 938194;
====================================================================
document 215187.1
SQL TRACE

1) create the tablespace
create tablespace SQLT datafile ‘/gltest/u042/oradata/gltest/sqlt01.dbf’
size 100M
extent management local uniform size 1m;
2) go to the below dir
cd /u92/oracle/SQLT
# sqlplus apps/run the below sql
SQL START SQLTCREATE.SQL;
==============================================================================
select  APPLICATIONS_SYSTEM_NAME from applsys.FND_PRODUCT_GROUPS;

Update FND_PRODUCT_GROUPS
Set APPLICATIONS_SYSTEM_NAME=’DEV1ERPC';

———————————————————-
To know the patch applied or not in database
select bug_number from ad_bugs where creation_date to_date(’31-Jul-12′,’dd-mon-yy’);
————————————————
To compile invalid objects

select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile ;’
from dba_objects
where owner in (‘APPS’,’APPS_MRC’)
and status=’INVALID’ ;

  when locked objects occurred 
select
  lpad(ses.sid,4,’ ‘)||’ ‘||
   lpad(proc.spid,6,’ ‘)||’ ‘||
–  rpad(lck.OS_USER_NAME,8,’ ‘)||’ ‘||
–   decode(lck.LOCKED_MODE,3,’Share    ‘,5,’Exclusive’,’Unknown  ‘)||’ ‘||
      lpad(nvl(ses.client_info,0),3,’0′)||’ ‘||
        rpad(obj.object_name,30,’ ‘)||’ ‘||
–      rpad(ses.PROGRAM,30,’ ‘)||’ ‘||
–rpad(machine,8,’ ‘)||’ ‘||
  Substr(ses.status,1,1)||’ ‘||
  substr(wait.state,1,7)||’ ‘||
  decode(ses.module,NULL,ses.program,ses.action||’*’||ses.module)
 from   V$LOCKED_OBJECT lck,
        all_objects obj,
      v$session ses,
   v$session_wait wait,
 v$process Proc
 where  lck.object_id = obj.object_id
 and   ses.sid = lck.session_id
 and   ses.sid = wait.sid
 and  ses.paddr = proc.addr
and  obj.object_name = ‘FND_CONC_REQ_STAT’
 order  by obj.object_name,ses.client_info;

select sid,serial#,status,sql_hash_value from v$session where sid=221;

alter system kill session ‘221,  ‘;
——————————————————————————————-
we can know any body is accessing the table

select a.sid,a.serial#,c.object_name
from all_objects c , v$lock b , v$session a
where c.object_name in
(‘GEMS_FUT_TP_WA_TB’)
and c.object_type = ‘TABLE’
and c.object_id = b.id1
and b.sid = a.sid;

———————————————————————————————-
*********RBS*******************
col segment_name format a8
col tablespace_name format a13
col usn format 99s
col status format a8
col “Max” format 9999999999
col “Exts” format 99999
col xacts format 99999
col curext format 99999
col “Size” format 99999
col “Opt” format 99999
select a.segment_name,a.tablespace_name,b.usn,b.extents “Exts”,
a.max_extents “Max”,b.xacts,b.status,round(b.rssize/1024/1024) “Size” ,
b.curext,b.optsize/1024/1024 “Opt”
from dba_rollback_segs a, v$rollstat b
where a.segment_id=b.usn and a.tablespace_name like ‘RBS%’ order by b.curext;
———————————————————————————————-
alter rollback segment R05 shrink;
———————————————————————————————-
To see lob columns are there or not

select data_type from dba_tab_columns where data_type like ‘LO%’ and
table_name in
—————————————————————–
To create histograms
analyze table mrp.mrp_sr_assignments estimate statistics for columns ASSIGNMENT_TYPE size 10;
Analyze table ONT.oe_order_lines_all compute statistics for columns flow_status_code size 14;
=================================================================================
to know which session is using

col USERNAME for a12
col STATUS for a8
col OSUSER for a10
col TERMINAL for a10
col SPID for a10
select s.sid, s.serial#, s.username, s.status, p.spid , s.osuser, s.terminal,s. LOGON_TIME, s.module,s.hashvalue
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = 2547930;
25271;

/tmp/temp_check_gltest1.sql
===============================================================================

tempx usage

select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE”  from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP’ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;

select TABLESPACE_NAME,BYTES_USED/1024/1024,BLOCKS_FREE/1024/1024 from  v$temp_space_Header;

select TABLESPACE_NAME,file_name, sum(bytes)/1024/1024/1024 from dba_temp_files
group by tablespace_name,file_name;

select tablespace_name from dba_tablespaces
where tablespace_name like ‘TEM%';

*********Sort Space Usage by Session

SELECT   S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
——————————————————————–
to put the tablespaces in backup mode
select distinct ‘alter tablespace ‘||tablespace_name||’ begin backup ;’ from dba_data_files

select FILE#,STATUS from v$backup where status=’ACTIVE';

select distinct ‘alter tablespace ‘||tablespace_name||’ end backup ;’
from dba_data_files
where file_id in
(select FILE# from v$backup where status=’ACTIVE’);

alter system switch logfile;
————————————————————

select distinct status from v$backup;

select inst_id, machine, count(*)
from gv$session
where program like ‘f60runm%’
group by inst_id, machine;

alter system flush shared_pool;
—————————————-
break on sid
select a.sid,b.SQL_TEXT
from v$session a, v$sqltext b
where a.SQL_ADDRESS=b.address
and a.status=’ACTIVE’ and a.username is not null
order by sid,piece;
———————————————-
select num_rows from dba_tables
where table_name=’GEMS_TRAN_PRICE_ERR_TB';

select last_analyzed,owner from dba_tables
where table_name in
(‘GEMS_FUT_TP_WA_TB’,’GEMS_TRAN_PRICE_TB’,’GEMS_TRAN_PRICE_ERR_TB’);
————————————————————-
select  ‘ ‘||username|| ‘ alter system kill session ”’||sid||’,’||serial#||”';’
from v$session
where username not in (‘SYSTEM’,’SYS’,’APPS’,’OPS$ORACLE’,’APPLSYS’) and status=’ACTIVE';
—————————————————————–
To know spid of sessions marked as killed:———-

SELECT spid
FROM v$process
WHERE NOT EXISTS
( SELECT 1 FROM v$session  WHERE paddr = addr);

tempx usage.sql

set pagesize 1000
set linesize 120
c
col username format a8 truncated
col machine format a12 truncated
col program format a30 truncated
col since format a18
col status format a2 truncated
col sid format 9999
break on report
compute sum of mbytes on report

select s.username, s.sid, s.machine, s.program,
       to_char(s.logon_time,’DD-MON-RR HH24:MI:SS’) since,
       s.status, round((su.blocks*8192)/(1024*1024),1) Mbytes
from v$session s,v$sort_usage su
where s.saddr=su.session_addr
order by Mbytes asc
/      

SELECT spid
FROM v$process
WHERE NOT EXISTS
( SELECT 1 FROM v$session WHERE paddr = addr);

select table_name
from dict
where table_name like ‘%ROLL%';

rbs—–

SELECT   r.name “RB NAME “, p.pid “ORACLE PID”,
         p.spid “SYSTEM PID “, NVL (p.username, ‘NO TRANSACTION’), p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE    l.sid = p.pid(+)
AND      TRUNC (l.id1(+)/65536) = r.usn
AND      l.type(+) = ‘TX’
AND      l.lmode(+) = 6
ORDER BY r.name;

col name for a8
col sid for 99999
col serial# for 999999
col username for a32
col sql_text for a60
col addr for a18
set lines 180
ttitle ROLLBACK_SEGMENT_SQL
select a.name, b.xacts,c.sid,c.serial#,c.username,d.sql_text, e.addr
from v$rollname a, v$rollstat b, v$session c, v$sqlarea d, v$transaction e
where a.usn = b.usn and
b.usn=e.xidusn
and c.taddr=e.addr
and c.sql_address = d.address and
c.sql_hash_value = d.hash_value
order by a.name,c.sid
/

analyze table mrp.MRP_FORECAST_DATES validate structure cascade;
analyze table mrp.MRP_GROSS_REQUIREMENTS validate structure cascade;

If you see the ORA-600 (510) error again, please run the following query:
select count(*) number_of_waiters
from v$session_wait w, v$latch l
where w.wait_time = 0
and w.event = ‘latch free’
and w.p2 = l.latch#
and l.name like ‘library%';

column ssid format 9999 heading SID
column opname format a15 Heading Operation
column target format a14 Heading Target
column es format 999.9 Heading “Time|Ran”
column tr format 999.90 Heading “Time|Left”
column pct format 990 Heading “PCT”
column RATE Heading “I/O |Rate/m” just right
select
sid ssid,
substr(OPNAME,1,15) opname,
target,
trunc((sofar/totalwork)*100) pct,
to_char(60*sofar*8192/(24*60*(last_update_time – start_time))/1024/1024/60, ‘9999.0’)||’M’ Rate,
elapsed_seconds/60 es,
time_remaining/60 tr
from v$session_longops
where time_remaining  0 and sid=659
order by start_time
/

col “logon” for a16
col username for a12
col spid for 99999
col osuser for a10
col machine for a12
col sid for 999
col program for a30
select a.username,a.sid,a.process ,a.serial#,b.spid,to_char(a.logon_time,’dd:mm:yyyy hh24:mi’)
 “logon”, a.status,a.osuser,a.machine,a.program from Gv$session a, Gv$process b
where a.paddr=b.addr and a.username is not null and a.osuser like ‘%apwx%';
AND A.USERNAME NOT LIKE ‘%BPA%’ AND A.USERNAME NOT LIKE ‘GEMS’
AND A.USERNAME NOT LIKE ‘%MOM%';

break on sid
select a.sid,b.SQL_TEXT
from gv$session a, gv$sqltext b
where a.SQL_ADDRESS=b.address
and a.status=’ACTIVE’ and a.username is not null and a.sid=154
order by sid,piece;

9955

                                        FROM   apps.ra_customers cust ,
                                                ar.hz_party_sites s,
                                                ar.hz_cust_acct_sites_all cs,
                                                ar.hz_locations addr,
                                                ar.hz_cust_accounts ca ,
                                                ar.hz_cust_site_uses_all u
set echo off
set feed off
set head off
set feed off
col REFERENCED_NAME for a35
 SELECT REFERENCED_NAME,REFERENCED_TYPE FROM DBA_DEPENDENCIES
WHERE NAME=’GEMS_AR_GP_GNARMAST’ and referenced_type=’TABLE';

SELECT   r.name “RB NAME “, p.pid “ORACLE PID”,
         p.spid “SYSTEM PID “, NVL (p.username, ‘NO TRANSACTION’), p.terminal
FROM v$lock l, v$process p, v$rollname r
WHERE    l.sid = p.pid(+)
AND      TRUNC (l.id1(+)/65536) = r.usn
AND      l.type(+) = ‘TX’
AND      l.lmode(+) = 6
ORDER BY r.name;

—————————————-
TO KNOW THE VERSION OF A DB

select trunc( (&_O_RELEASE/100000000)) || ‘.’ ||
 trunc( mod((&_O_RELEASE/1000000),100)) || ‘.’ ||
 trunc( mod((&_O_RELEASE/10000),100)) || ‘.’ ||
 trunc( mod((&_O_RELEASE/100),100))
 from dual;

select ‘alter ‘||object_type||’ ‘||owner||’.’||object_name||’ compile ;’
from dba_objects
where owner in (‘APPS’,’APPS_MRC’)
and status=’INVALID’ ;

=========================================================================================

title ‘Rollback Segment Users’
set linesize 80
col usn                 format 99       heading ‘ID’
col name                format a15      heading ‘SEGMENT’
col schemaname          format a12      heading ‘USERNAME’
col pid                 format 999      heading ‘PID’
col sid                 format 999      heading ‘SID’
col osuser              format a10      heading ‘OSUSER’
col spid                format 99999    heading ‘OSPID’

select r.usn, r.name, s.schemaname, s.sid, p.pid, s.osuser, p.spid,USED_UBLK
from   v$transaction t, v$session s, v$process p, v$rollname r
where  t.xidusn = r.usn
and    t.addr   = s.taddr
and    s.paddr  = p.addr
and    s.sid=209
order by 1;
/
 ===========================================================================================
select s.sid, s.username,s.program, p.spid , s.osuser from v$session s, v$process p
where s.paddr = p.addr and s.program like ‘f%run%';

select SQL_STATEMENT_NAME,ACTIVE_FLAG from bom.Bom_Delete_Sql_Statements
 where SQL_STATEMENT_NAME in
(‘ITM_CON51′,’BIL_CON18′,’CMP_CON5′,’ITM_CON44′,’ITM_CON45′);

===========================================================================================

select FILE#,STATUS from v$backup where status=’ACTIVE';

select distinct ‘alter tablespace ‘||tablespace_name||’ end backup ;’
from dba_data_files
where file_id in
(select FILE# from v$backup where status=’ACTIVE’);

 INVALIDS COUNT 

select owner, count(*),OBJECT_NAME,OBJECT_TYPE  from dba_objects where status=’INVALID’
group by owner,OBJECT_NAME,OBJECT_TYPE ;

select owner, count(*)  from dba_objects where status=’INVALID’
and owner in (‘APPS’,’APPSVIEW’,’APPS_MRC’,’APPLSYS’,’MOMI41′,’BPA’)  group by owner

 TABLESPACE   

col file_name for a60
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name =’APPS_TS_TX_DATA';

alter tablespace GPPSD1 add datafile ‘/itestc1/u053/oradata/itestc1/statd09.dbf’
size 2048064k;

alter database datafile ‘/gltest/u053/oradata/gltest/gppsd103.dbf’ resize 2048064k;

ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE ‘/j2_gcasq/d001/a_txn_data07.dbf’ SIZE 6G;

 !ls -ltr /itestc1/u*/oradata/itestc1/statd09.dbf

!ls -ltr /gltest/u*/oradata/gltest/podm108.dbf
col file_name for a60
select file_name,bytes/1024/1024 from dba_temp_files
where tablespace_name=’TEMPX';

select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name=’RBS1′;

select * from dba_tablespaces
where tablespace_name=’EC';

set lines 200
col “Name” format a20
select a.tbl “Name”,a.tsz “Total Size”,b.fsz “Free Space”,
round((1-(b.fsz/a.tsz))*100) “Pct Used”,round((b.fsz/a.tsz)*100) “Pct Free” from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name = ‘APPS_TS_TX_IDX’ group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name = ‘APPS_TS_TX_IDX’ group by tablespace_name) b
Where a.tbl=b.tblsp;

col file_name for a60
select file_name,bytes/1024/1024/1024 from dba_data_files
where tablespace_name =’APPS_TS_TX_IDX';

select owner,tablespace_name,segment_name,sum(bytes/1024/1024/1024) from dba_segments where segment_name=’XXSLS_GBL_ORDER_HISTORY_MV’ GROUP BY owner,segment_name, tablespace_name;

**************List All the Tablespaces free space *******************
select  tsu.tablespace_name, ceil(tsu.used_mb) “size MB”
,       decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) “free MB”
,       decode(100 – ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 – ceil(tsf.free_mb/tsu.used_mb*100)) “% used”
from    (select tablespace_name, sum(bytes)/1024/1024 used_mb
        from    dba_data_files group by tablespace_name union all
        select  tablespace_name || ‘  **TEMP**’
        ,       sum(bytes)/1024/1024 used_mb
        from    dba_temp_files group by tablespace_name) tsu
,       (select tablespace_name, sum(bytes)/1024/1024 free_mb
        from    dba_free_space group by tablespace_name) tsf
where   tsu.tablespace_name = tsf.tablespace_name (+)
order   by 4
/

select a.tablespace_name “TBL”, a.”Total SIze”, b.”USED”,
round((b.”USED”/a.”Total SIze”)*100) “%USED”,
round((1-(b.”USED”/a.”Total SIze”))*100) “%FREE”  from
(select tablespace_name, sum(bytes)/1024/1024 “Total SIze” from dba_temp_files
where tablespace_name=’TEMP1′ group by tablespace_name) a,
(select tablespace_name, (total_blocks*(select value from v$parameter where name
=’db_block_size’))/1024/1024 “USED” from v$sort_segment) b
where a.tablespace_name=b.tablespace_name;

col file_name format a60;
col tablespace_name format a15;
select file_name,sum(bytes)/1024/1024 sum from
dba_data_files where tablespace_name=’PRECISE_I3_PWOR_IND’ group by file_name
order by file_name;

GATHER STATS 

execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’${tab_owner}’,tabname=’${tab_name}’,estimate_percent=${gat_per},cascade=true);

exec fnd_stats.gather_table_stats(‘MRP’,’MRP_RELIEF_INTERFACE’,20,1,”,’NOBACKUP’);

execute FND_STATS.GATHER_TABLE_STATS(ownname =’PO’,tabname = ‘PO_ACCRUAL_RECONCILE_TEMP_ALL’,percent =’20’,degree =’0′);

execute FND_STATS.GATHER_INDEX_STATS(ownname=’ENG’,indname=’ENG_REVISED_ITEMS_N3′,percent=20,BACKUP_FLAG=’NOBACKUP’);

execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’${tab_owner}’,tabname=’${tab_name}’,estimate_percent=${gat_per},cascade=true);

execute DBMS_STATS.GATHER_TABLE_STATS(ownname=’MM’,tabname=’GEMS_ITEM_TB’,partname=’CST_PMAX’,estimate_percent=20,cascade=true);

execute DBMS_STATS.GATHER_INDEX_STATS(ownname=’MM’,indname=’ITEM_PK’,degree=20);

EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

EXEC DBMS_STATS.gather_schema_stats(‘SCOTT’);
EXEC DBMS_STATS.gather_schema_stats(‘DP’, estimate_percent => 10);

EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’);
EXEC DBMS_STATS.gather_table_stats(‘SCOTT’, ‘EMPLOYEES’, estimate_percent => 15);

EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’);
EXEC DBMS_STATS.gather_index_stats(‘SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15);

select ‘exec dbms_stats.unlock_table_stats (‘ ||”’SYSTEM”’||’,”’||table_name||”” || ‘);’ from dba_tables where owner=’SYSTEM’

select ‘exec dbms_stats.delete_table_stats(OWNNAME=>’ ||”’SYSTEM”’||’,TABNAME=>”’||table_name||”” || ‘);’ from dba_tables where owner=’SYSTEM';

select ‘exec dbms_stats.gather_table_stats(OWNNAME=>’ ||”’SYSTEM”’||’,TABNAME=>”’||table_name||”” ||’,’||’ESTIMATE_PERCENT=>100,DEGREE=>4,CASCADE=>TRUE ‘||’);’ from dba_tables where owner=’SYSTEM';

 ROLLBACK SEGMENTS 

col segment_name format a8
col tablespace_name format a13
col usn format 99
col status format a8
col “Max” format 9999999999
col “Exts” format 99999
col xacts format 99999
col curext format 99999
col “Size” format 99999
col “Opt” format 99999
select a.segment_name,a.tablespace_name,b.usn,b.extents “Exts”,
a.max_extents “Max”,b.xacts,b.status,round(b.rssize/1024/1024) “Size” ,
b.curext,b.optsize/1024/1024 “Opt”
from dba_rollback_segs a, v$rollstat b
where a.segment_id=b.usn and a.tablespace_name=’RBS1′ order by b.curext;

alter rollback segment R03 shrink

Database link 

col db_link for a26
col owner for a10
col host for a20
col created for a10
col username for a10
select * from dba_db_links;

drop public database link GL_APPS.MED.GE.COM;

create public database link GL_PREPRO.MED.GE.COM
connect to PREPRO identified by aur0ra
using ‘itest.med.ge.com';

select count(*) from tab@GL_PREPRO.MED.GE.COM;

select name from v$database@GL_APPS.MED.GE.COM;

  MISSING FILES 
select * from v$datafile where name = ‘%MISS%';

SELECT count(file#) FROM v$datafile_header WHERE recover =’YES’ OR error is not null;

set pagesize 20000
set linesize 180
set pause off
set serveroutput on
set feedback on
set echo on
set numformat 999999999999999
Spool recovery_info.txt
select substr(name, 1, 50), status from v$datafile;
select substr(name,1,40), recover, fuzzy, checkpoint_change# from v$datafile_header;
select GROUP#,substr(member,1,60) from v$logfile;
select * from v$recover_file;
select distinct status from v$backup;
select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;
select distinct (fuzzy) from v$datafile_header;
spool off
exit

 INDEX REBUILD 

alter index owner.index_name rebuild;

/u92/oracle/outage/prod/itestc1/reorgs1/gl_bal_global_ind.sql

Database invalid sessions 
set pagesize 100
col sid for a30
col machine for a25
col osuser for a10
col program for a20
select sid, machine,osuser,program, to_char(logon_time,’ddth hh24 mi’ ) “login_time” from v$session
where username =’APPS’
and status = ‘f’
order by to_char(logon_time,’ddth hh24 mi’);

 patch application particulars 

col BUG_ID for a10                    
col APPLICATION_SHORT_NAME for a10  
col BUG_NUMBER  for a10                    
col CREATION_DATE for a10                  
col ARU_RELEASE_NAME for a10              
col SUCCESS_FLAG for a10                        
col CREATED_BY for a10                    
col LAST_UPDATE_DATE for a10              
col LAST_UPDATED_BY for a10
col BUG_STATUS for a10  

select * from ad_bugs where to_char(CREATION_DATE,’mm_dd_yy’)=’05_10_12′ ;

 select BUG_ID||’ ‘||APPLICATION_SHORT_NAME||’ ‘||BUG_NUMBER||’ ‘||to_char(CREATION_DATE,’mm-dd-yy:hh24:mi:ss’)||’ ‘||BUG_STATUS||’ ‘||SUCCESS_FLAG from ad_bugs where to_char(CREATION_DATE,’mm_dd_yy’)=’07_16_05′ ;

Admin mount point filled 

In future if you get any alert reg admin mount point on any apps related server , pls do following steps to

1) Find the trace files which are having more than 300m

Go to admin mount point and execute following command

 find . -size +300000000c –

2) if you get any output(files) from step one check the trace files whether ther are generating from front end or from backend – to chk the same follow as below

If the file naming convention is like  devl3c1_ora_4273_501022571.trc ( here u will see an extra field with usre login name(pink-bold one) then this is generating from frond end.

NOTE: if its not from front end then the trace file format is devl3c1_ora_9081.trc.

Using above file u can contact anyone from apps admin team and chk with them whether we can remove these trace files or not or aks them to do the needfull then they will take care of that.

Greping to a file 

ls -ltr|grep “Apr 19″|awk ‘{print “rm -rf “$9}’RM1.SH

 finding process which are running

For knowing the sessions presently running

select SID,SERIAL#,PADDR,USERNAME,STATUS,SCHEMANAME,OSUSER,PROGRAM,TYPE,SQL_HASH_VALUE,MODULE,ACTION,LOGON_TIME from v$session where SCHEMANAME=’APPS';

To know what sql statement it is runnig

select b.SQL_TEXT from v$sqlarea b, v$session a
where b.HASH_VALUE=a.SQL_HASH_VALUE
and a.sid=’138′;

To know whether it is running or struck

select * from v$sess_io where sid=’138′;

select trunc(last_analyzed),owner, count(*) from dba_tables
where owner=’PERFSTAT’
group by trunc(last_analyzed),owner;

select trunc(last_analyzed),count(*) from dba_tables where trunc(last_analyzed) in(’12-JUN-05′,’13-JUN-05′,’14-JUN-05′) group by trunc(last_analyzed);

select trunc(last_analyzed),count(*) from dba_tables group by trunc(last_analyzed);

select table_name,trunc(last_analyzed) from dba_tables where trunc(last_analyzed) in(’21-JUl-05′);

select index_name,trunc(last_analyzed) from dba_indexes where trunc(last_analyzed) in(’21-JUl-05′);

select patition_name,table_name,trunc(last_analyzed) from dba_tab_partitions where trunc(last_analyzed) in(’21-JUl-05′);

ROLE CREATION 

select * from ROLE_SYS_PRIVS where role in (‘GE_PST’,’GE_DEV’,’GE_VIEW_ONLY’);

create role GE_VIEW_ONLY

grant CREATE SESSION,SELECT ANY TABLE to GE_VIEW_ONLY;

ls -lrt | sed -n ‘1,100p’|awk ‘{print “rm -rf ” $9 }’  — first 20

ls -lrt|egrep “Nov 18″| awk ‘{print “rm -rf ” $9 }’ >  oct.sh;chmod 755 oct.sh; sh oct.sh &
ls -lrt|egrep “Nov 18″| awk ‘{print “gzip ” $9 ” & “}’
ps -ef|grep oracle$ORACLE_SID|grep LOCAL=NO|awk ‘{print “kill -9 ” $2 }’ > kill.sh;chmod 755 kill.sh;sh kill.sh &

find . -mtime +3 -type f -exec rm {} \;
   
ls -lrt |grep “Jun 30″ |awk ‘{print “gzip ” $9 ” & ” }’ > Jun30.sh;chmod 755 Jun30.sh;sh Jun30.sh

FNDCPASS apps/appsdev 0 Y system/sygc321 USER SYSADMIN sygc321

grep password $IAS_ORACLE_HOME/Apache/modplsql/cfg/*app|head -1|awk ‘{print $3}’

grep password $IAS_CONFIG_HOME/Apache/modplsql/cfg/*app|head -1|awk ‘{print $3}’

select node_name,SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN from fnd_nodes;

select text from dba_source where name=’XXSCP_GBL_SO_OPTIMISE_PKG';  – Package code

sqlplus -s apps/appsdev @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
sqlplus -s apps/appsdev @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

admrgpch -s /rh157/u04/Unity_Patches/cmppat/sour -d /rh157/u04/Unity_Patches/cmppat/desti -merge_name CMPPAT

select fnd_profile.value(‘APPS_MAINTENANCE_MODE’) from dual;

adpatch options=hotpatch,nocompiledb,nocompilejsp

adpatch options=nocompiledb,nocompilejsp,nomaintainmrc,noautoconfig defaultsfile=$APPL_TOP/admin/$TWO_TASK/adalldefaults.txt patchtop=$APPL_TOP/patches/7184125 driver=u7184125.drv logfile=adpatch_7184125.log workers=32

select bug_number from ad_bugs where bug_number in (‘6328412′, ‘7340487’, ‘7347275’);
select bug_number from ad_bugs where bug_number = &patchno;

How to know that if the patch is applied successfully
select D.PATCH_NAME, B.APPLICATIONS_SYSTEM_NAME, B.NAME, C.DRIVER_FILE_NAME, A.PATCH_DRIVER_ID, A.PATCH_RUN_ID,
A.SESSION_ID, A.PATCH_TOP, A.START_DATE, A.END_DATE, A.SUCCESS_FLAG, A.FAILURE_COMMENTS
from AD_PATCH_RUNS A, AD_APPL_TOPS B, AD_PATCH_DRIVERS C, AD_APPLIED_PATCHES D
where A.APPL_TOP_ID = B.APPL_TOP_ID AND A.PATCH_DRIVER_ID = C.PATCH_DRIVER_ID
and C.APPLIED_PATCH_ID = D.APPLIED_PATCH_ID
and A.PATCH_DRIVER_ID
in (select PATCH_DRIVER_ID from AD_PATCH_DRIVERS where APPLIED_PATCH_ID
in (select APPLIED_PATCH_ID from AD_APPLIED_PATCHES where PATCH_NAME = ‘10059638’)) ORDER BY 3;

PATCH_NAME   APPLICATIONS_SYSTEM_NAME  NAME       DRIVER_FILE_NAME   PATCH_DRIVER_ID PATCH_RUN_ID SESSION_ID PATCH_TOP                            START_DATE    END_DATE   S FAILURE_COMMENTS
———— ————————- ———- —————— ————— ———— ———- ———————————————————————————
8265428      utyeb03                   hostnamerh212  u8265428.drv                100500       107491     164030 /dbautyshare/Unity_Patches/8265428   18-JUN-12     18-JUN-12  Y
Merged patches are applied for which language
select a.PATCH_DRIVER_ID,DRIVER_FILE_NAME,c.bug_id,d.language
from ad_patch_drivers a,AD_COMPRISING_PATCHES b, ad_bugs
c,AD_PATCH_DRIVER_LANGS d
where c.bug_number = ‘&no’
and c.bug_id = b.bug_id
and a.PATCH_DRIVER_ID = b.patch_driver_id
and a.patch_driver_id = d.patch_driver_id;

 pathces applies in last no of days 
 select substr(bug_number,1,7) patch,
B.LAST_UPDATE_DATE,
substr(aru_release_name,0,10)
from ad_bugs B,
ad_applied_patches AP
where substr(AP.PATCH_NAME,1,7) = substr(B.bug_number,1,7)
and B.creation_date > sysdate-&days
order by bug_number
/

select  ‘alter system kill session ”’||sid||’,’||serial#||”';’ from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status!=’ACTIVE';

admrgpch -s /home/applmgr/patch10g/source -d /home/applmgr/patch10g/dest -merge_name ramesh
 
 Oracle Application Tier consists of following services, which you can run on one or more machines
- Root Service (OPMN)
- WebEntry Point Services (OHS)
- Web Application Services(OC4J- oacore, oafm, forms)
- Batch Processing Services(CM, apps listener, fullfillment)
- Other Services (MWA, Metric client/server)

Jserver(jre)
Forms Server(f60srv)
Metric Server(d2ls)
Metric Client(d2lc)
Report Server(rwm60)
Concurrent Server(FNDLIBR)

The Oracle AWR report is the evolution of the STATSPACK report in Oracle 10g, and is invoked as follows:

$ORACLE_HOME/rdbms/admin/awrrpt.sql
$ORACLE_HOME/rdbms/admin/awrrpti.sql
$ORACLE_HOME/rdbms/admin/awrinput.sql

Workflow Notification 
update wf_resources set text=’*’ where name=’WF_ADMIN_ROLE';

SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

select B.status, B.RULE_FUNCTION
from wf_events A, wf_event_subscriptions B
Where A.GUID=B.EVENT_FILTER_GUID and
A.name=’&event’ and
B.RULE_FUNCTION = ‘&subscription';

SET VERIFY OFF;
SET SERVEROUTPUT ON SIZE 1000000;
declare
  l_readyc          NUMBER;
  l_waitc           NUMBER;
  l_processedc      NUMBER;
  l_expiredc        NUMBER;
  l_undeliverablec  NUMBER;
  l_errorc          NUMBER;
begin
    WF_QUEUE.getCntMsgSt
        (p_agent        => ‘WF_DEFERRED’,
         p_ready        => l_readyc,
         p_wait         => l_waitc,
         p_processed    => l_processedc,
         p_expired      => l_expiredc,
         p_undeliverable => l_undeliverablec,
         p_error        => l_errorc);
    dbms_output.put_line(‘WF_DEFERRED messages ready:’ || l_readyc || ‘, waiting:’ || l_waitc || ‘, expired:’|| l_expiredc || ‘, undeliverable:’ || l_undeliverablec || ‘, processed:’ || l_processedc);
end;
/
set verify on
Invalids 

select OWNER,OBJECT_NAME,OBJECT_TYPE,LAST_DDL_TIME,STATUS  from all_objects where  object_name like ‘OE_VERIFY_PAYMENT_PUB%’  order by LAST_DDL_TIME asc;

 select count(*) from dba_objects where status=’INVALID';

exec sys.utl_recomp.recomp_parallel(8);
set lines 200
set pages 400
col OBJECT_NAME for a34
select owner,object_name,object_type,status from dba_objects where status=’INVALID';
select owner,object_name,object_type,status from dba_objects where object_name=’XXCRM_GBL_PCN_ARC_UTL_PKG';
select object_name, object_type from all_objects where object_name = ‘DBMS_SUPPORT’ and object_type like ‘PACKAGE%’ ;
*********************************************************

col “DB User” format a10
col “OS User” format a14
col “Logon time” format a25
set verify off pagesize 100
select
a.username “DB User”,
a.osuser   “OS User”,
a.sid      “SID”,a.serial#,
to_char(a.LOGON_TIME,’DD-MON-YYYY HH24:MI:SS’)  “Logon time”,
a.status   “Status”
–a.sid,
–a.serial#
from v$session a,
v$process b
where a.paddr = b.addr
and a.username is not null
–and round(abs(((sysdate)-(logon_time))*24))  50
and status=’ACTIVE’
order by 4 desc;

z03845
47arvada

 select value from v$parameter where NAME=’utl_file_dir';
exec FND_FILE.PUT_LINE(FND_FILE.LOG, ‘THIS IS A TEST’);

To change Apps Password using FNDCPASS

FNDCPASS apps/
– adding TEMP FILE

alter tablespace TEMP add tempfile ‘/ofp/oratemp/ebis/temp07.dbf’ size 1800m;

 patchset level

SELECT B.APPLICATION_NAME, A.APPLICATION_ID,
PATCH_LEVEL FROM FND_PRODUCT_INSTALLATIONS A, FND_APPLICATION_TL B
WHERE A.APPLICATION_ID= B.APPLICATION_ID;

– firing “awk” command

ls -l | grep ‘Apr’ | awk ‘{print $9}’ | xargs -i rm {} &

ls -l | grep ‘Dec 17′ | awk ‘{print $9}’ | xargs -i rm {}

ls -l |awk ‘{print “ln -sf “$11″ ” $9}’

For log files newer than 5 days

find /opt/app/logs/ -name *.log -mtime -5 -exec ls -tl {} \;

find /opt/app/logs/ -name *.log -mtime -5 -exec rm -f {} \;

– to generate forms (run this from “applmgr”)

f60gen module=/oraapps/testappl/au/11.5.0/forms/US/RCVTXERT.fmb userid=APPS/
– patch log file

$APPL_TOP/admin/
– listing directory

ls -F | grep /

ls -l | sort +0

tkprof tracefile outputfile explain=system/passwd sys=no

Saeed : tkprof  Saeed : if you just type tkprof it will show you the usage page.
Saeed : [oraprd@IRODBP01] $ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
              [print= ] [insert= ] [sys= ] [sort= ]
  table=schema.tablename   Use ‘schema.tablename’ with ‘explain=’ option.
  explain=user/password    Connect to ORACLE and issue EXPLAIN PLAIN.
  print=integer    List only the first ‘integer’ SQL statements.
  aggregate=yes|no
  insert=filename  List SQL statements and data inside INSERT statements.
  sys=no           TKPROF does not list SQL statements run as user SYS.
  record=filename  Record non-recursive statements found in the trace file.
  sort=option      Set of zero or more of the following sort options:
    prscnt  number of times parse was called
    prscpu  cpu time parsing
    prsela  elapsed time parsing
    prsdsk  number of d

     f60gen…
  strings -a binary

it will show u the SQL it is running

To Check how the roll back segment is being utilized

select b.sid,a.message,opname,start_time pct_complete,
to_char(start_time+(sysdate-start_time) /(sofar/totalwork),’dd-mon-yy:hh:mi:ss’) Est_completion_time
from v$session_longops a, v$session b where a.serial#=b.serial#
and (sofar/totalwork)*100 < 100

Generating jar files

adjava -mx512m -nojit oracle.apps.ad.jri.adjmx @/applprod/prodappl/admin/DEV/out/genjars.cmd

– To bring the tape offline

mt -f /dev/rmt/0 offline

lock table

Performance Scripts

    col PROGRAM for a20
    col EVENT for a20
    col USERNAME for  a8
    col WAIT_CLASS for a10          
    set lines 200
    select inst_id, SID, serial#, BLOCKING_SESSION,rpad(WAIT_CLASS,8)
    WAIT_CLASS,SECONDS_IN_WAIT,PROGRAM, MODULE , EVENT ,username
    from gv$session where BLOCKING_SESSION is not null order by BLOCKING_SESSION,PROGRAM;

select sid,blocking_session,username,sql_hash_value,logon_time from v$session where blocking_session is not null and sid=&sid;
select sid,serial#,sql_hash_value,logon_time from v$session where sid in (select SESSION_ID from v$LOCKED_OBJECT);

select ‘alter system kill session ”’||sid||’,’||serial#||”';’ from v$session where sid in (select SESSION_ID from v$LOCKED_OBJECT);

 alter system kill session ‘1156,8202’;

 select LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE from dba_2pc_pending;
 COMMIT FORCE ‘ASCEB05.rolta.COM.32e48fdf.40.35.306125′;

select sid,blocking_session,event,status,machine,username,sql_hash_value from v$session where sql_hash_value!=0 and status=’ACTIVE';

 select sql_hash_value from v$session where sid=4301;

select SQL_TEXT from v$sqlarea where HASH_VALUE=380700310 order by piece;

select used_urec from v$transaction where addr = (select taddr from v$session where sid = 4077);

select SECONDS_IN_WAIT from v$session where sql_hash_value=380700310;

**Objects Locked**
select s.osuser “O/S-User”, s.username “Ora-User”, s.sid “Session-ID”, s.serial# “Serial”, s.process “Process-ID”, s.status
“Status”,l.name “Obj Locked”, l.mode_held “Lock Mode” from gv$session s,dba_dml_locks l,gv$process p where l.session_id = s.sid and p.addr = s.paddr;

select object_id from dba_objects where object_name=’&tablename';

select * from v$locked_object where object_id=&id number ;
col OBJECT_NAME for a45
select owner,object_name,object_type ,status from dba_objects where object_name=
select sid from v$access where owner=’&table_owner’ and object=’&table_name';

select owner,object_name,object_type,status from dba_objects where object_name=’&object_name';
select sid, serial#, command, taddr,status from v$session where sid=&session_id;
module,terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’)
select ‘kill -9’||p.spid from v$session s ,v$process p where s.PADDR=p.addr and s.sid in (select sid from v$session)

alter system kill session ”;
 Concurrent Managers

 CONCSUB apps/apps SYSADMIN ‘System Administrator’ SYSADMIN CONCURRENT FND DEACTIVATE or ABORT

select v.request_id,s.sid, s.serial#, s.event, p.spid   from apps.fnd_conc_requests_form_v v,
v$process p,   v$session s   where v.request_id = ‘896967’ and v.oracle_process_id = p.spid(+)  and p.addr = s.paddr(+)

select oracle_process_id from apps.fnd_concurrent_Requests where request_id in (‘3112884′,’3111837′,’3110635′,’3111142′);

select REQUEST_ID,OS_PROCESS_ID from fnd_concurrent_requests where OS_PROCESS_ID=3087;

 select sql_hash_value from gv$session where sid=4181
 select sql_fulltext,rows_processed from gv$sql where sql_id=’3hbxr52kk5u42′;

 select SQL_TEXT from v$sqlarea where HASH_VALUE=3295793396;

 select used_urec from v$transaction where addr = (select taddr from v$session where sid = 3899);

 select SECONDS_IN_WAIT from v$session where sql_hash_value=2405550420

 select last_analyzed from dba_tables where table_name=’MSC_ST_SALES_ORDERS’
If the Concurrent Request is there at the front end, but there is no process running at the O/S level, then run the following query

select a.CONCURRENT_REQUEST_ID ,b.NODE_NAME,b.LOGFILE_NAME from fnd_conc_pp_actions a,fnd_concurrent_processes b
where a.PROCESSOR_ID=b.CONCURRENT_PROCESS_ID and a.ACTION_TYPE=6 and CONCURRENT_REQUEST_ID=3922386;

select r.request_id, s.sid, s.serial#, g.concurrent_program_name
from applsys.fnd_concurrent_requests r,
applsys.fnd_concurrent_queues_tl qt,
applsys.fnd_concurrent_queues q,
applsys.fnd_concurrent_processes p,
applsys.fnd_concurrent_programs g,
gv$session s
where r.controlling_manager=p.concurrent_process_id
and q.application_id=p.queue_application_id
and q.concurrent_queue_id=p.concurrent_queue_id
and qt.application_id=q.application_id
and qt.concurrent_queue_id=q.concurrent_queue_id
and r.phase_code=’R’
and qt.language in (‘US’)
and p.session_id=s.audsid
and g.concurrent_program_id=r.concurrent_program_id
and r.request_id in(6114233);

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request ;

select p.spid, s.inst_id,s.sql_hash_value from gv$session s,gv$process p where s.paddr=p.addr and s.sid=3435;

select used_urec from gv$transaction where addr = (select taddr from gv$session where sid = 3435);

select p.spid, s.inst_id from gv$session s,gv$process p where s.paddr=p.addr and s.sid=4065;
select inst_id,sql_id from gv$session where sid=&sid;
Tunning
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
select * from table(dbms_xplan.display_awr(‘&sqlid’));

ALTER SESSION SET sql_trace = TRUE;
EXECUTE SYS.dbms_system.set_sql_trace_in_session (1694, 47200, TRUE);

update fnd_concurrent_requests
set status_code = ‘X’, phase_code=’C’
where request_id =5655240;

select USER_CONCURRENT_PROGRAM_NAME,c.request_id,s.sid,p.spid,s.process,s.osuser,s.username,s.program,
s.status,logon_time,last_call_et from v$session s, v$process p, apps.fnd_concurrent_requests c,apps.fnd_concurrent_programs_tl ct where
oracle_process_id=p.spid and s.paddr=p.addr and ct.concurrent_program_id=c.concurrent_program_id and request_id=&creq_id
/

OPP log/out files

SELECT fcpp.concurrent_request_id req_id, fcp.node_name, fcp.logfile_name
  FROM fnd_conc_pp_actions fcpp, fnd_concurrent_processes fcp
WHERE fcpp.processor_id = fcp.concurrent_process_id
   AND fcpp.action_type = 6
   AND fcpp.concurrent_request_id = &request_id;

—- give this query…
—- it will ask u the req id…
—- give the req id…
—- it will give the details about what it is doing ….
—- take the sid from that output…..
—- and run the following query

select sql_text from v$sqlarea where hash_value = (select sql_hash_value from v$session where sid =&sid)
and provide the sid….
Index rebuild 
alter session set sort_area_size=100000000;
alter session set db_file_multiblock_read_count=128;
alter session set sort_multiblock_read_count=128;

select distinct status from dba_indexes;

select distinct status from dba_ind_partitions;

select distinct status from dba_ind_subpartitions;

select owner,TABLE_NAME,INDEX_NAME,status from dba_indexes where owner=’MSC’ and status=’UNUSABLE';

select ‘alter index ‘||owner||’.’||index_name||’ rebuild;’ from dba_indexes where blevel>2;

select ‘alter index ‘ || owner ||’.’ ||index_name || ‘ rebuild online ;’ from dba_indexes where table_name=’MDP_MATRIX’ and owner=’DP’ and  BLEVEL>1;

select TABLE_OWNER,PARTITION_NAME,COUNT(*) from dba_tab_partitions where partition_name IN(‘P_2008_05′,’P_2008_06′) GROUP BY TABLE_OWNER,PARTITION_NAME ORDER BY TABLE_OWNER;

select TABLE_OWNER,PARTITION_NAME,COUNT(*) from dba_tab_partitions where partition_name IN(‘P_2009_07′,’P_2009_08′) GROUP BY TABLE_OWNER,PARTITION_NAME ORDER BY TABLE_OWNER;

select ‘ALTER INDEX ‘||INDEX_OWNER||’.’||INDEX_NAME||’ REBUILD PARTITION ‘||PARTITION_NAME ||';’ from dba_ind_partitions where INDEX_NAME in (‘XIE1MAILING_STATUS_TBL’,’XIE2MAILING_STATUS_TBL’,’XIE3MAILING_STATUS_TBL’,’XIE4MAILING_STATUS_TBL’,’IDX_TM_MAILING_STATUS_TBL’) and PARTITION_NAME=’

select ‘ALTER INDEX ‘||INDEX_OWNER||’.’||INDEX_NAME||’ REBUILD PARTITION ‘||PARTITION_NAME ||';’ from dba_ind_partitions where INDEX_NAME in (‘XIE1SEND_STATUS_TBL’,’XIE2SEND_STATUS_TBL’,’XIE3SEND_STATUS_TBL’) and PARTITION_NAME in (‘P_2008_04′,’P_2008_01′,
  2  ‘P_2008_03′);

SPID_SID_SERIAL
SELECT sess.sid,  sess.serial#,  pro.spid,  sess.program,  sess.username,sess.event
  sess.module,  sess.terminal, to_char(sess.logon_time,’Mon-ddth  HH24:MI:SS ‘)LogOn_Time,
  osuser,  sess.status, sess.process FROM   v$process pro,  v$session sess WHERE
  pro.spid in (‘&spid’) and   pro.addr = sess.paddr
/

select spid from gv$process where addr in (select paddr from gv$session where sid=&sid);
select s.INST_ID,spid,s.status,s.module,s.terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’) from gv$session s,gv$process p where paddr=addr and sid=3962;
whoami
select s.sid,s.status,s.module,s.terminal,TO_CHAR(s.logon_time, ‘DD-MON-RR HH24:MI:SS’) from v$process p, v$session s, v$sess_io i  where i.sid = s.sid  and s.paddr = p.addr  and s.audsid = userenv(‘sessionid’);

select nvl(ss.USERNAME,’ORACLE PROC’) username, se.SID,VALUE cpu_usage,SS.SQL_HASH_VALUE,SS.LOGON_TIME
from v$session ss, v$sesstat se, v$statname sn where se.STATISTIC# = sn.STATISTIC#
and   NAME like ‘%CPU used by this session%’ and se.SID = ss.SID
and ss.username=’APPS’ AND STATUS=’INACTIVE’ order by VALUE,SS.LOGON_TIME desc;

 SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID FROM apps.fnd_concurrent_requests a,
 apps.fnd_concurrent_processes b,v$process c,v$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id
 AND b.session_id=d.audsid AND a.request_id = 1971141 — AND a.phase_code = ‘C';

select  ‘alter system kill session ”’||sid||’,’||serial#||”';’ from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and to_char(logon_time,’hh’)>’01’ and status!=’ACTIVE';
select sid,serial#,logon_time,sql_hash_value from v$session where username not in (‘SYSTEM’,’SYS’,’OPS$ORACLE’,’APPLSYS’) and status!=’ACTIVE';
select owner,table_name,last_analyzed from dba_tables where table_name in (‘WF_ITEM_ACTIVITY_STATUSES’,’WF_NOTIFICATIONS’);

set pages 300
set lines 300
select sid,serial#,username,status,to_char(logon_time,’hh:mi:ss’),module,sql_hash_value from v$session where to_char(logon_time,’hh’)>’01’ and status=’INACTIVE';

select  ‘alter system kill session ”’||sid||’,’||serial#||”';’ from v$session where to_char(logon_time,’hh’)>’02’ and status=’INACTIVE';

select SQL_TEXT from v$sqlarea where HASH_VALUE=3272871235 order by piece;

 select distinct o.object_name, sh.username||'(‘||sh.sid||’)’ “Holder”, sw.username||'(‘||sw.sid||’)’ “Waiter”,
        decode(lh.lmode, 1, ‘null’, 2,
              ‘row share’, 3, ‘row exclusive’, 4,  ‘share’,
              5, ‘share row exclusive’ , 6, ‘exclusive’)  “Lock Type”
  from all_objects o, gv$session sw, gv$lock lw, gv$session sh, gv$lock lh
where lh.id1  = o.object_id
  and  lh.id1  = lw.id1
  and  sh.sid  = lh.sid
  and  sw.sid  = lw.sid
  and  sh.lockwait is null
  and  sw.lockwait is not null
  and  lh.type = ‘TM’
  and  lw.type = ‘TM';

wait events for a session
library cache lock
================
SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event like ‘library cache%’
AND state = ‘WAITING';

select
 distinct
   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,
   ob.kglnaown obj_owner, ob.kglnaobj obj_name
   ,pn.kglpncnt pin_cnt, pn.kglpnmod pin_mode, pn.kglpnreq pin_req
   , w.state, w.event, w.wait_Time, w.seconds_in_Wait
   — lk.kglnaobj, lk.user_name, lk.kgllksnm,
   –,lk.kgllkhdl,lk.kglhdpar
   –,trim(lk.kgllkcnt) lock_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req,
   –,lk.kgllkpns, lk.kgllkpnc,pn.kglpnhdl
 from
  x$kglpn pn,  x$kglob ob,x$ksuse ses
   , v$session_wait w
where pn.kglpnhdl in
(select kglpnhdl from x$kglpn where kglpnreq >0 )
and ob.kglhdadr = pn.kglpnhdl
and pn.kglpnuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc
/

select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser,
b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 1511
order by time_waited;

Shared Pool

SELECT SUM(A.BYTES)/(1024*1024)SHARED_POOL_USED,
MAX(B.VALUE)/(1024*1024) SHARED_POOL_SIZE,
(MAX(B.VALUE)/(1024*1024)) – (SUM(A.BYTES)/(1024*1024)) SHARED_POOL_AVAIL,
((SUM(A.BYTES)/(1024*1024))/(MAX(B.VALUE)/(1024*1024)))*100 PCT_SHARED_POOL_AVL
FROM V$SGASTAT A, V$PARAMETER B
WHERE A.POOL=’shared pool’
AND A.NAME NOT IN (‘free memory’)
AND B.NAME=’shared_pool_size';

High Disk Reads
  select sql_text, executions, disk_reads, buffer_gets
from v$sqlarea
where decode(executions,0,disk_reads,disk_reads/executions) >
(select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3
order by disk_reads desc;

Details regarding sessions which are causing high waits and cpu usage.

SELECT NVL(s.username, ‘(oracle)’) AS username,
s.sid, s.serial#, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM   v$session_wait sw,
v$session s WHERE  s.sid = sw.sid
and    sw.event not in (‘SQL*Net message from client’, ‘SQL*Net message to client’)
and    sw.event not like ‘Streams AQ%’
ORDER BY sw.seconds_in_wait DESC;

select  nvl(ss.USERNAME,’ORACLE PROC’) username,
ss.SID, ss.serial#,ss.sql_hash_value,sa.executions,VALUE cpu_usage from    v$session ss,
v$sesstat se, v$sqlarea sa, v$statname sn where   se.STATISTIC# = sn.STATISTIC#
and     NAME like ‘%CPU used by this session%’
and     se.SID = ss.SID
and     ss.sql_hash_value = sa.hash_value
and     value > 0
order   by VALUE desc;

******************************************************

col ssid form A6
col lmode form 999 heading “loc”
col request form 999 heading “req”
col name form A30
break on id1 on sid

select a.sid, a.type, a.id1, a.id2,
       decode(a.lmode, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
              3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
              6, ‘EXCLUSIVE’, ‘?’),
       decode(a.request, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
              3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
              6, ‘EXCLUSIVE’, ‘?’),
       a.block
 from v$lock a
where a.id1 in ( select id1 from v$lock where request < 0 )
order by a.id1, a.request, a.sid
/
****************************************************************************
Finding Locks:

rem ********************************************************************
rem * Filename          : locks.sql – Version 1.0
rem * Description       : Information about sessions waiting for locks
rem * Usage             : start locks.sql
rem ********************************************************************

SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM gV$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request ;

select p.spid, s.inst_id from gv$session s,gv$process p where s.paddr=p.addr and s.sid=3769;
2/11/2010
col ssid form A6
col lmode form 999 heading “loc”
col request form 999 heading “req”
col name form A30
break on id1 on sid

select a.sid, a.type, a.id1, a.id2,
       decode(a.lmode, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
              3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
              6, ‘EXCLUSIVE’, ‘?’),
       decode(a.request, 0, ‘NONE’, 1, ‘NULL’, 2, ‘ROW SHARE’,
              3, ‘ROW EXCLUSIVE’, 4, ‘SHARE’, 5, ‘SHARE ROW EXCLUSIVE’,
              6, ‘EXCLUSIVE’, ‘?’),
       a.block
 from gv$lock a
where a.id1 in ( select id1 from v$lock where request < 0 )
order by a.id1, a.request, a.sid
/

find /rh175/u03/app/applmgr/cmpcrpdv/inst/apps/cmpcrpdv_hostnamerh175/logs/appl/conc/log -name “*.req” -mtime +30 -exec rm {} \;

DBlinks 
SELECT
‘create ‘||DECODE(U.NAME,’PUBLIC’,’public ‘)||’database link ‘||CHR(10)
||DECODE(U.NAME,’PUBLIC’,Null, U.NAME||’.’)|| L.NAME||chr(10)
||’connect to ‘ || L.USERID || ‘ identified by ”appsdev’
||L.PASSWORD||”’ using ”’ || L.host || ””
||chr(10)||';’ TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;

REM START ESBLOG XXGFN_GBL_APPS_TO_SOA.rolta.COM
  CREATE DATABASE LINK “XXGFN_GBL_ APPS_TO_SOA.rolta.COM”
  CONNECT TO “ESBLOG” IDENTIFIED BY VALUES ’05C50A870C878511BC07617BC776B3C3DB’
  henSING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu145.rolta.com) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ecstesti) ) )';
REM END ESBLOG XXGFN_GBL_ APPS_TO_SOA.rolta.COM

XXGFN_GBL_SOA_TO_APPS.rolta.COM
  CREATE DATABASE LINK “XXGFN_GBL_SOA_TO_APPS.rolta.COM”
   CONNECT TO “APPS” IDENTIFIED BY VALUES ‘0509DD0DC7FDD8F7FAE2BFE85A3988F65FF571A2EF34C71579′
   USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu93.rolta.com) (PORT = 1522) ) ) (CONNECT_DATA = (SERVICE_NAME = AIBMUT1D) ) ) ‘;
REM END ESBLOG XXGFN_GBL_SOA_TO_APPS.rolta.COM

CREATE DATABASE LINK “APS_TO_EBS.rolta.COM”
   CONNECT TO “XXITF_USER” IDENTIFIED BY XXITF_USER
   USING ‘hostnameiu202.rolta.com:1621/gccap';

Oracle: using recycling bin
Information about the objects in recycle bin:

set lines 400
select object_name, original_name, type, can_undrop , can_purge , droptime from recyclebin

or

show recyclebin

Restore

flashback table mytable to before drop;

Clear recycle bin

purge recyclebin;

To save the flashback information for the last 30 minutes:
ALTER SYSTEM SET UNDO_RETENTION = 1800;


SELECT * FROM some_table AS OF TIMESTAMP (SYSDATE – INTERVAL ‘5‘ MINUTE);

SELECT * FROM some_table AS OF TIMESTAMP (‘2008-01-18 06:31:58′, ‘YYYY-MM-DD HH24:MI:SSS’);

SELECT * FROM some_table AS OF SCN 364583948;

– dbms_flashback could be also used
EXECUTE dbms_flashback.enable_at_time (‘18-JAN-08 11:00:00‘);
Source text of the package

select text
from dba_source
where upper(name) like upper(‘&which_object’)
order by line ;

Use $ORACLE_HOME/bin/wrap utility to encrypt the package (there is no unwrap)
(Well, actually, there is unwrap – just look in the search machine for the words unwrap10 or rewrap…)

Source text of the views

set long 5000
col text for a80

select text from dba_views where view_name = upper(‘&which_view’);
select query from DBA_mviews where mview_name = upper(‘&which_view’);
SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’&which_view';

Source text of the synonym

select TABLE_OWNER || ‘.’ || TABLE_NAME || decode ( db_link , null , ” , ‘@’ || db_link ) SYNONYM_OBJECT
from dba_synonyms
where SYNONYM_NAME = upper(‘&which_synonym’);

Source text of the trigger

select
‘create or replace trigger “‘ || trigger_name || ‘”‘
|| chr(10)|| decode( substr( trigger_type, 1, 1 ), ‘A’, ‘AFTER’, ‘B’, ‘BEFORE’, ‘I’, ‘INSTEAD OF’ ) || chr(10) ||
triggering_event || chr(10) || ‘ON “‘ || table_owner || ‘”.”‘ || table_name
|| ‘”‘ || chr(10) || decode( instr( trigger_type, ‘EACH ROW’ ), 0, null, ‘FOR EACH ROW’ ) || chr(10) ,
trigger_body
from dba_triggers
where trigger_name = upper(‘&which_trigger’) and owner=upper(‘&trigger_owner’);

Export to the compressed file

s/etc/mknod my_own_pipe p
gzip -c -9 < my_own_pipe > mydump.gz &
exp username/password …… file=my_own_pipe
rm -f my_own_pipe

Import from a compressed file:

/etc/mknod my_own_pipe p
uncompress < mydump.Z > my_own_pipe &
imp username/password ……. file=my_own_pipe
rm -f my_own_pipe

set lines 400
col DIRECTORY_PATH for a65
select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;
create directory EXPORT_DP as ‘/j2_utyas03/d001/backup';

expdp SCHEMAS=XXITF_USER directory=DUMP_DIR dumpfile=expdpXXITF_USER.dmp logfile=expdpXXITF_USER.log PARALLEL=2

expdp scott/tiger@db10g tables=EMP,DEPT directory=DUMP_DIR dumpfile=expdpXXITF_OWNER.dmp logfile=expdpXXITF_OWNER.log

impdp SCHEMAS=XXITF_USER directory=DUMP_DIR dumpfile=expdpXXITF_USER.dmp logfile=impdpXXITF_USER.log REMAP_SCHEMA=XXITF_USER:XXITF_USER PARALLEL=2

select * from dba_datapump_jobs;
NOT working …expdp system/sygc321 SCHEMAS=DP DUMPFILE=DATAPUMP:DP_user%U.dmp,DATAPUMP:DP_user2%U.dmp,DATAPUMP:DP_user3%U.dmp,DATAPUMP:DP_user4%U.dmp LOGFILE=DATAPUMP:DP_user.log FILESIZE=12g PARALLEL=8 JOB_NAME=DP_user1

http://coskan.wordpress.com/2007/03/21/datapump-restart-capability-for-ora-39095/
Export> status

************************************************

set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘TABLESPACE’,’TEMP’,user) from dual;

set pagesize 0
set linesize 980
set long 90000
SELECT DBMS_METADATA.GET_DDL(‘MATERIALIZED_VIEW’,’XXITF_CARRIER_MV’,’XXITF_OWNER’) FROM dual;

set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘USER’, ‘XXGFN_USER’) || ‘/’ usercreate
from dba_users;

select ‘exec dbms_mview.refresh(‘||””||'”‘||owner||'”‘||’.’||'”‘||MVIEW_NAME||'”‘||””||’,’||””||’F’||””||’);’ from  dba_mviews WHERE MVIEW_NAME IN (select name from dba_refresh_children where job=365);

CREATE USER A00424
  IDENTIFIED BY welcome1
  PASSWORD EXPIRE
  DEFAULT TABLESPACE USER_DATA
  TEMPORARY TABLESPACE TEMP2
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

  — 1 Role for Z01734
  GRANT CONNECT TO A00424;
  GRANT ARW_APPS_READ TO A00424;

  ALTER USER A00424 DEFAULT ROLE ALL;

ls -lrt //nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15|awk ‘{print “cp /nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15/” $9 “/*.lst /nfs01/u01/app/oraecsqual/ecsqualappl/Migrations/Jan15/logs/”}’>copy_to_logs.ksh
ksh copy_to_logs.ksh

                                              RMAN
nohup rman target rman_dba/backup rcvcat rman/rman@rmant cmdfile=utyca02_archive_bkup.sh msglog=utyca02_archive_bkup.log &

rman rman_unity/rman_unity@rmanp auxiliary / target rman_dba/backup@gcebp — Production..

 catalog backuppiece ‘C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TEST\BACKUPSET\RMAN_TEST\ORA_TEST_699129410_S53_S1′;

RUN {
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK FORMAT;
ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE';
ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE';
SEND ‘NB_ORA_POLICY=UNITY_CRPDEV_CMPCRPDV_RMAN_HOT_DB';
#SEND ‘NB_ORA_POLICY=UNITY_CMPGOLD_RMAN_HOT_DB_IU03_VTL';
#SEND ‘NB_ORA_SCHED=Default-Application-Backup';
#SEND ‘NB_ORA_SCHED=Hot_full';
CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE';
crosscheck archivelog all;
backup archivelog all delete input ;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
}
alter system set log_archive_dest_1=’location=/iu166/u02/oradata/arch/cmpcrpdv/db/apps_st/data';
list backup of archivelog from sequence 3025 until sequence 246;

run{
backup archivelog from sequence 138 until sequence 142 delete input;
}
restore archivelog from sequence 212 until sequence 213;

set lines 200
col OPNAME for a45
SELECT     sid,opname,context,sofar,totalwork,time_remaining/60,round(sofar/totalwork*100,1) “%DONE”
FROM  v$session_longops
WHERE opname LIKE ‘gthr%’
AND totalwork <> 0
AND sofar <> totalwork
/

set lines 300
col filename for a55
select FILENAME,STATUS,ELAPSED_TIME,OPEN_TIME,CLOSE_TIME,BYTES/1024/1024,EFFECTIVE_BYTES_PER_SECOND/1024/1024 from V$BACKUP_SYNC_IO
where sid in (select sid from v$session where  PROGRAM like ‘%rman%’);

col opname for a40
col units for a10
set lines 600
SELECT sid ,opname,sofar,totalwork,units,elapsed_seconds,time_remaining/60
FROM v$session_longops
WHERE sofar != totalwork;

rman rcvcat rman/rman@rmant auxiliary / target rman_dba/backup@utyeb05  cmdfile rman_duplicate_utyeb06.rman   msglog rman_duplicate_utyeb06.log
oracle utyeb06 ->cat rman_duplicate_utyeb06.rman
resync catalog;
run {
set until time “to_date(’26-05-2009 22:22:00′,’dd-mm-yyyy hh24:mi:ss’)”;
#set until scn 61513786203 ;
ALLOCATE auxiliary CHANNEL ch00 TYPE ‘SBT_TAPE';
ALLOCATE auxiliary CHANNEL ch01 TYPE ‘SBT_TAPE';
SEND ‘NB_ORA_SERV=hostnamesu64, NB_ORA_CLIENT=hostnameiu161′;
DUPLICATE TARGET DATABASE TO utyeb06;
}

list backup summary;
crosscheck backup;
delete expired backup;

******************************************************************************************************************************

fetchcp.bat 1234 >c:\mks_only\extract_1234.txt 2>&1

lsattr -El aio0 12/23/2008

run{
allocate channel 1 disk type disk fomat;
allocate channel 2 disk type disk fomat;
backup archivelog all delete input
release channel 1;
release channel 2;
}

ls -lrt | sed -n ‘1,20p’|awk ‘{print “rm -rf ” $9 }’

ls -l | grep ‘Jan 19 ‘ | awk ‘{print $9}’ | xargs -i rm -rf {}

ls -lrt|egrep ‘Jan|Feb’
rm `ls -lrt|egrep ‘Jan|Feb’`

REM START ESBLOG XXGFN_GBL_APPS_TO_SOA.rolta.COM
  CREATE DATABASE LINK “XXGFN_GBL_ APPS_TO_SOA.rolta.COM”
  CONNECT TO “ESBLOG” IDENTIFIED BY VALUES ’05C50A870C878511BC07617BC776B3C3DB’
  USING ‘(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostnameiu145.rolta.com) (PORT = 1521) ) ) (CONNECT_DATA = (SERVICE_NAME = ecstesti) ) )';
REM END ESBLOG XXGFN_GBL_ APPS_TO_SOA.rolta.COM

ps -ef|grep oracle$ORACLE_SID|grep LOCAL=NO|awk ‘{print “kill -9 ” $2 }’ > kill.sh;chmod 755 kill.sh;sh kill.sh &

opmnctl start
opmnctl startproc process-type=HTTP_Server
opmnctl startproc process-type=oc4j_soa

FNDLOAD apps/ppassprod213 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct user.ldt FND_USER USER_NAME=’A39499′
FNDLOAD apps/apgc321 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct user.ldt

set head off
set pages 0
set long 9999999
select dbms_metadata.get_ddl(‘USER’, username) || ‘/’ usercreate
from dba_users where username=’XXCNV';

Metalink Notes…..

2. Run the command:
cd $FND_TOP/patch/115/bin

3. Run the command:
perl ojspCompile.pl –compile –flush -p 10
perl ojspCompile.pl –compile –fast -quiet -p 20      *** for faster compilation..

We saw a recurrence of the OACORE process taking CPU  on hostnamers10 (SIT1 middle tier).  Please do the following if you see the issue  again.  $ps -ef |grep  
$ kill -3  
$ adopmnctl.sh debug > ~/knj/.log  
$ cp  /rs10/u02/applmgr/utyeb05/inst/apps/utyeb05_hostnamers10/logs/ora/10.1.3/opmn/default_group~oacore~default_group~1.log  ~/knj/oacore_.log

- Stop all Oracle applications services

- Delete the following:

$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

- Start up the application services

If you still get the same error, then please run AutoConfig. Let us see whether it completes successfully or not.

Note: 387859.1 – Using AutoConfig to Manage System Configurations in Oracle Applications Release 12
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=387859.1

If the above does not help, then I believe we need to enable debug to get more details about the error.

Note: 422419.1 – R12 – How To Enable and Collect Debug for HTTP, OC4J and OPMN
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=422419.1

745711.1 – Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications R12

$FND_TOP/patch/115/bin/ojspCompile.pl ojspCompile.conf –compile –flush -p 20
OracleMetaLink Note 215268.1 contains complete syntax and definition

frmcmp_batch ARIINR.fmb userid=apps/av0tar321 module_type=form compile_all=Yes

if `ps -ef|grep 17440|grep -v grep|wc -l` = 2
then
echo “not completed”
else
echo “Completed”
if

while true
do
ps -ef|grep 17440|grep -v grep
sleep 15
clear
done

 scp filename login@hostname:filename
if [ $? -ne 0 ]
then
        echo “Error Occured While SCP”
    else
        echo “———————————————————”
        echo “successful transmission “
        echo “———————————————————”
fi
pspendurkar (2:58:33 PM): a39057
pspendurkar (2:58:36 PM): password is India123

# —————————————————-
# ENV Setup
# —————————————————-
set -o vi
export PATH=.:$PATH:/rh157/u04/Unity_SW/datAvail/bin
export ORACLE_PATH=.:$ORACLE_PATH:/rh157/u04/Unity_SW/datAvail/dba_sql
export scripts=’cd $ADMIN_SCRIPTS_HOME’

MATERIALIZED VIEW
select mview_name,owner from dba_mviews;
SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’TEMP’) from dual;
DROP MATERIALIZED VIEW emp_data;

frmcmp module=ODPN.pll userid=apps/apps@ module_type=library compile_all=yes

TKPROF returns “could not open trace file”
  Doc ID:  300439.1

gunzip linux_grid_control_agentdwn_10_1_0_2.cpio.gz
cpio -idmv < linux_grid_control_agentdwn_10_1_0_2.cpio
unzip p3731593_10103_LINUX.zip

**************OPatch ********************
export PATH=$PATH:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin
opatch apply -local
opatch apply -invPtrLoc /rs10/u03/utyeb05/apps/tech_st/10.1.2/oraInst.loc
$ORACLE_HOME/OPatch/opatch lsinventory | grep -i 5998987

OPatch analysis for patch 7196863
opatch query -all

./runInstaller -silent -attachHome -noClusterEnabled -invPtrLoc ./etc/oraInst.loc ORACLE_HOME=”/u01/oracle/
product/10.2.0/prodracdb” ORACLE_HOME_NAME=”OraDb10g_home1″

 RAC

$ORA_CRS_HOME/bin/crsctl stop crs
————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
————————————————————————–

crs_start and crs_stop
New feature introduced in 10g R2
The crs_start and crs_stop command can be used to start and stop CRS managed resources
$ crs_start resource-name -all (all resource)
$ crs_stop resource-name -all (all resource)

crs_relocate
change where resource is active$ORA_CRS_HOME/bin/crsctl stop crs

crs_stat -t
crs_start ora.hostnameiu60.ons -f

export CRS_HOME=/racp01/u02/oracle/crs/10.2.0
export PATH=$CRS_HOME/bin:$PATH:

————————————————————————–
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
********ASM***
resize–qiomkfile -r 8199M dat384cl1.dbf
 qiomkfile -r 2051M idx842bfip1.dbf

qiomkfile -r 4102M dlscl1_cl2_tsc08_03.dbf ==> 4097M

qiomkfile -r 8199M dat384cl1.dbf  ==> 8194M
 add==>   qiomkfile -h -s 8199M idx110dbshare3.dbf

 qiomkfile -h -s 5005m dat310citi.dbf
 4. Add datafile into database, the size is 5000m, (not 2001m)

********SWB******
opmnctl restartproc process-type=OC4J_swb
opmnctl restartproc process-type=OC4J_swb_ui
opmnctl @cluster status -l
opmnctl @cluster status -app |grep esb
opmnctl @cluster status -app |grep asc
opmnctl @cluster status -app |grep orabpel
opmnctl @cluster status -app |grep default

Cleaning temporary tablespace
Run ICXDLTMP.sql
Location in Release12 $FND_TOP/patch/115/sql/ICXDLTMP.sql
Running ICXDLTMP.sql is equivalent to running “Purge data from temporary table” concurrent request.

–specify a file with extension htm or html
set markup html on spool on

spool c:\ex1.html

–your query
select * from dept;

–end the html file
spool off

–set back to normal output
set markup html off spool off
******************************Grid Agent*******http://download.oracle.com/docs/html/B12013_03/emctl.htm
emctl status agent
emctl stop agent
emctl clearstate agent
emctl start agent
Listing the Targets on a Managed Host
./emctl config agent listtargets

Stop an immediate blackout emctl stop blackout
Set an immediate blackout for all targets on a host emctl start blackout [-nodeLevel] [-d ]

The -nodeLevel option is used to specify a blackout for all the targets on the host; in other words, all the targets that the Management Agent is monitoring, including the Management Agent host itself. The -nodeLevel option must follow the blackout name. If you specify any targets after the -nodeLevel option, the list is ignored.
Check the status of a blackout emctl status blackout [[:]]….

emctl stop blackout utyeb02

#To start an immediate blackout called “bk3″ for database “db1″ for 30 minutes:
 emctl start blackout bk3 db1 -d 30
#To start an immediate blackout called “bk3″ for database “db2″ for five hours:
 emctl start blackout bk db2 -d 5:00


Grid server 

Start All
/u01/app/oracle/product/10.2.0/db10g/bin/dbstart
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl startall
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl start agent
Stop All
/u01/app/oracle/product/10.2.0/agent10g/bin/emctl stop agent
/u01/app/oracle/product/10.2.0/oms10g/opmn/bin/opmnctl stopall
/u01/app/oracle/product/10.2.0/db10g/bin/dbshut

List all indexed columns for a given table                   

break on index_name skip 1
col index_name  format a30
col uniuenes    format a12
col column_name format a30

prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from   user_indexes i, user_ind_columns c
where  i.index_name = c.index_name
  and  i.table_name = upper(‘&1′)
order  by c.index_name, c.column_position
/

Enable trace for a user session:

SQLPLUS> exec sys.dbms_system.set_sql_trace_in_session(SID, SERIAL, true);

Use tkprof to analyze the trace file:

tkprof [TRACE FILE] [OUTPUT FILE] explain=/ sort=prsela,exeela,fchela
table=ops\$oracle.plan_table

select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser,
b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 283
order by time_waited;

ls -lrt |egrep ‘Nov|Oct’

 Debug or Trace Option Enabled in Profiles

 SELECT A.CONCURRENT_PROGRAM_NAME “Program Name”,
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) “User Program Name”,
SUBSTR(B.USER_NAME,1,15) “Last Updated By”,
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE=’Y’
AND A.LAST_UPDATED_BY=B.USER_ID;

Run this script to see if any Debug or Trace profile options have been set to Y, meaning that they are enabled. Some profile options may be required to be set to Y, but others should be N.

select  distinct
        a.application_short_name app_short,
        user_profile_option_name optname,
        decode(level_id,
        10001,’SITE’,
        10002,’APP : ‘||a2.application_short_name,
        10003,’RESP: ‘||r.responsibility_key,
        10004,’USER: ‘||u.user_name,
        ‘Unknown’) d_level,
        profile_option_value optval,
        v.last_update_date updated
from fnd_profile_options_vl o,
        fnd_profile_option_values v,
        fnd_application a,
        fnd_application a2,
        fnd_responsibility r,
        fnd_user u
where (
        o.user_profile_option_name like ‘%Debug%’ or
        o.user_profile_option_name like ‘%DEBUG%’ or
        o.user_profile_option_name like ‘%Trace%’ or
        o.user_profile_option_name like ‘%TRACE%’
        )
and a.application_id = v.application_id
and o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
– Find the associate level for profile
and r.application_id (+) = v.level_value_application_id
and r.responsibility_id (+) = v.level_value
and a2.application_id (+) = v.level_value
and u.user_id (+) = v.level_value
and profile_option_value = ‘Y’
order by 2,1,3,4;

Last day total no. of concurrent requests:-
SQL> SELECT sysdate -1 , count(*) FROM apps.fnd_concurrent_requests WHERE to_char(actual_completion_date,’YYYYMMDD’) =(SELECT to_char(sysdate -1,’YYYYMMDD’) FROM dual);
 
SYSDATE-1            COUNT(*)
—————— ———-
18-SEP-12                 218
 
Database Size:-
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name order by 1;
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
APPS_CALCLIP                                   4770
APPS_OMO                                       1150
APPS_TS_ARCHIVE                                1101
APPS_TS_DISCO                                   856
APPS_TS_DISCO_OLAP                             1350
APPS_TS_INTERFACE                              2404
APPS_TS_MEDIA                               5211.25
APPS_TS_NOLOGGING                               575
APPS_TS_QUEUES                                 4299
APPS_TS_SEED                                   3803
APPS_TS_SUMMARY                               22369
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
APPS_TS_TX_DATA                            62692.25
APPS_TS_TX_IDX                                32471
B2B_DT                                           68
B2B_IDX                                          16
B2B_LOB                                          12
B2B_RT                                           42
BAM                                               8
BIA_RTL                                        1000
CTXSYS                                           79
CUST                                             20
CWMLITE                                          23
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
DATA                                           7200
DCM                                             198
DEMANTRA                                       5400
DISCO_PTM5_CACHE                                108
DISCO_PTM5_META                                   3
DSGATEWAY_TAB                                     7
DW_AGGREGATE_IDX                                100
DW_AGGREGATE_TBS                                100
DW_BASE_IDX                                     100
DW_BASE_TBS                                     100
DW_DERIVED_IDX                                  100
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
DW_DERIVED_TBS                                  100
DW_DM_TBS                                       100
DW_LOOKUP_TBS                                   100
DW_MVLOG_TBS                                    100
DW_REFERENCE_IDX                                100
DW_REFERENCE_TBS                           133.9375
GEOR_TBS                                       2136
HTMLDB                                          287
IAS_META                                        221
MTR                                               4
OCATS                                             3
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
ODM_DATA                                         40
OLAP_BAAD                                      1500
OLTS_ATTRSTORE                                   15
OLTS_BATTRSTORE                                  15
OLTS_CT_STORE                                     8
OLTS_DEFAULT                                      3
OLTS_SVRMGSTORE                                   3
OPMOR                                             6
ORABPEL                                          12
OWAPUB                                           10
PERFSTAT_TS                                     500
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
RBS_MIG                                          43
RE                                                8
SYNCSERVER                                       54
SYSAUX                                         1327
SYSTEM                                        19297
TS_DP                                            50
TS_SALES_DATA                                    50
TS_SALES_DATA_ENGINE                             50
TS_SALES_DATA_ENGINE_X                           50
TS_SALES_DATA_X                                  50
TS_SIM                                           50
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
TS_SIM_X                                         50
UDDISYS_TS                                       21
UNDO_TBS                                 3686.40625
USERS                                            10
WCRSYS_TS                                         3
XDB                                              61
 
 
72 rows selected.
 
 Database free Size:-
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name order by 1;
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
APPS_CALCLIP                                1543.75
APPS_OMO                                    511.875
APPS_TS_ARCHIVE                             197.375
APPS_TS_DISCO                                86.125
APPS_TS_DISCO_OLAP                         1333.875
APPS_TS_INTERFACE                           220.125
APPS_TS_MEDIA                                495.75
APPS_TS_NOLOGGING                            285.25
APPS_TS_QUEUES                                  610
APPS_TS_SEED                                 420.75
APPS_TS_SUMMARY                             2208.75
 
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
APPS_TS_TX_DATA                                6836
APPS_TS_TX_IDX                             3728.875
B2B_DT                                        7.125
B2B_IDX                                      1.6875
B2B_LOB                                      1.3125
B2B_RT                                            4
BAM                                          1.6875
BIA_RTL                                    999.9375
CTXSYS                                         29.5
CUST                                             19
CWMLITE                                        1.75
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
DATA                                      7199.6875
DCM                                           19.75
DEMANTRA                                    5399.75
DISCO_PTM5_CACHE                               10.5
DISCO_PTM5_META                              1.0625
DSGATEWAY_TAB                                   1.5
DW_AGGREGATE_IDX                            99.9375
DW_AGGREGATE_TBS                            99.9375
DW_BASE_IDX                                 99.9375
DW_BASE_TBS                                 99.9375
DW_DERIVED_IDX                              99.9375
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
DW_DERIVED_TBS                              99.9375
DW_DM_TBS                                   99.9375
DW_LOOKUP_TBS                               99.9375
DW_MVLOG_TBS                                99.9375
DW_REFERENCE_IDX                            99.9375
DW_REFERENCE_TBS                            133.875
GEOR_TBS                                     214.25
HTMLDB                                       28.625
IAS_META                                     21.875
MTR                                          2.3125
OCATS                                          1.25
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
ODM_DATA                                     9.5625
OLAP_BAAD                                 1499.8125
OLTS_ATTRSTORE                              14.6875
OLTS_BATTRSTORE                             14.6875
OLTS_CT_STORE                                 .8125
OLTS_DEFAULT                                 1.4375
OLTS_SVRMGSTORE                               .8125
OPMOR                                         1.375
ORABPEL                                          .5
OWAPUB                                       9.9375
PERFSTAT_TS                                  347.25
 
TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
RBS_MIG                                      3.9375
RE                                           1.4375
SYNCSERVER                                5.2265625
SYSAUX                                        80.25
SYSTEM                                   1862.41406
TS_DP                                       49.9375
TS_SALES_DATA                               49.9375
TS_SALES_DATA_ENGINE                        49.9375
TS_SALES_DATA_ENGINE_X                      49.9375
TS_SALES_DATA_X                             49.9375
TS_SIM                                      49.9375
 
 TABLESPACE_NAME                SUM(BYTES/1024/1024)
—————————— ——————–
TS_SIM_X                                    49.9375
UDDISYS_TS                                    1.875
UNDO_TBS                                    1176.25
USERS                                        1.9375
WCRSYS_TS                                    1.3125
XDB                                          6.1875
 
72 rows selected.
 

                          O/S level observation

 
1.Average Cpu Utilization (Percentage):-
 
[oracle@r0469 ~]$ top
top – 14:54:01 up 19 days,  2:40,  2 users,  load average: 0.13, 0.09, 0.09
Tasks: 277 total,   1 running, 275 sleeping,   1 stopped,   0 zombie
Cpu(s):  9.6%us,  1.5%sy,  0.0%ni, 88.2%id,  0.5%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   4081424k total,  3907568k used,   173856k free,    39372k buffers
Swap:  8193140k total,    33848k used,  8159292k free,  2351040k cached
 
 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                        
24483 oracle    15   0  111m  66m  16m S 15.3  1.7   2:03.81 frmweb                                        
23176 oracle    16   0 45524 7720 4920 S  3.3  0.2   0:30.07 httpd                                         
24487 oracle    15   0 1194m 488m 476m S  1.7 12.3   1:18.22 oracle                                        
26519 oracle    15   0  2360 1164  800 R  0.7  0.0   0:00.27 top                                           
23850 oracle    17   0 1159m  90m  86m S  0.3  2.3   0:02.34 oracle                                        
24080 oracle    17   0 1158m  54m  51m S  0.3  1.4   0:01.02 oracle   
 
2. No of apps form process on os level:-
[oracle@r0469 ~]$ ps -ef | grep f60webmx
oracle   26535 26486  0 14:54 pts/0    00:00:00 grep f60webmx
 
[oracle@r0469 ~]$ ps -ef | grep frmweb
oracle   24483 23292  1 12:30 ?        00:02:05 /oracle/VIS/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,172.16.3.4
oracle   26371 23292  0 14:43 ?        00:00:04 /oracle/VIS/apps/tech_st/10.1.2/bin/frmweb server webfile=HTTP-0,0,1,default,172.16.3.19
oracle   26537 26486  0 14:54 pts/0    00:00:00 grep frmweb
 
3. Size of Archive-logs (in GB):-
 du -sh /apps/archive
 
 4. Alert log Checking:-
 vi alert log file and check last one hour error.
 
5. Total size and free size on apps mount point (All Servers):-
 [oracle@r0469 ~]$ df -h
Filesystem            Size      Used     Avail   Use%     Mounted on
/dev/sda5             434G     358G     54G       87%        /
/dev/sda2             9.5G     151M     8.9G       2%          /tmp
/dev/sda1             965M   23M       893M     3%         /boot
tmpfs                   2.0G     3.0M      2.0G       1%         /dev/shm
How to bounce Apache, clear HTML cache and have updated Java code class active in R12:-
To clear HTML cache and bounce Apache at R12 level.
First, go to the scripts directory of R12, as shown below:
 
[oracle@r0469 ~]$ cd /oracle/VIS/apps/apps_st/appl/
[oracle@r0469 appl]$ . APPSVIS_r0469.env 
[oracle@r0469 appl]$ cd $ADMIN_SCRIPTS_HOME 
[oracle@r0469 scripts]$ pwd
/oracle/VIS/inst/apps/VIS_r0469/admin/scripts
 
To clear HTML cache:
 
The pages directory under $COMMON_TOP should not be cleared for E-Biz R12,
which we used to do in 11i applications.
For clearing middle tier cache in R12, Navigate to
Login as sysadmin/sysadmin
Functional Administrator responsibility > Core Services > Caching Framework > Global Configuration > Clear cache
Bouncing apache or Application….
 
Instead of clearing the _pages, in R12 creates blank login page issue, as in R12 the jsp files does not get compiled automatically.
[oracle@r0469 ~]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts

[oracle@r0469 scripts]$ ./adapcctl.sh stop

You are running adapcctl.sh version 120.7.12010000.2

Stopping OPMN managed Oracle HTTP Server (OHS) instance …

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adapcctl.txt for more information … 

[oracle@r0469 scripts]$ ./adapcctl.sh start

You are running adapcctl.sh version 120.7.12010000.2

Starting OPMN managed Oracle HTTP Server (OHS) instance …

adapcctl.sh: exiting with status 0

adapcctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adapcctl.txt for more information …  

At R12 level bouncing apache command is different from the one to bounce oacore, oc4j engine and others.
In 11i the apache start and stop command, also bounce the JVM running on JSERV and clear caches by doings as below :
 
To clear the jsp & modplsql caches run this command:-
[oracle@r0469 ~]$ rm -rf $OA_HTML/_page/*
[oracle@r0469 ~]$ rm -rf $COMMON_TOP/_page/*
[oracle@r0469 ~]$ rm -rf $IAS_ORACLE_HOME/apache/modplsql/_page/*

But in R12 the command to stop and start apache only takes action on Apache web server and not on the OC4J container that executes the JVM.

 
To bounce the OC4J container there is a specific script:
$INST_TOP/admin/scripts/adoacorectl.sh
Therefore when making modification to JAVA class in R12 for the web application, in order to have that change recognized, it is needed to bounce OC4J using adoacorectl.sh script.
 SCRIPT TO KNOW “ACTIVE USERS” FOR OACOREGROUP
 REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
select ‘Number of user sessions : ‘ || count( distinct session_id) How_many_user_sessions from icx_sessions icx where disabled_flag != ‘Y’ and PSEUDO_FLAG = ‘N’ and (last_connect + decode(FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’), NULL,limit_time, 0,limit_time,FND_PROFILE.VALUE(‘ICX_SESSION_TIMEOUT’)/60)/24) > sysdate  and counter < limit_connects;
REM
REM END OF SQL
REM
HOW TO DETERMINE “ACTIVE FORMS USERS” FOR FORMSGROUP
Check the number of f60webmx processes on the Middle Tier server.
For example:
ps -ef | grep f60webx | wc -l

Oracle Tuning And Diagnostics Script — Active Session Info
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece

Trace SQL Query Average Execution Time Using SQL ID
SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
FROM v$sql s
WHERE s.sql_id=’4n01r8z5hgfru’

 Get The Detail Explain Plan Using SQL ID
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(‘dtdqt19kfv6yx’))

 Session Elapsed Processing Time
SELECT s.sid, s.username, s.module,
round(t.VALUE/1000000,2) “Elapsed Processing Time (Sec)”
FROM v$sess_time_model t, v$session s
WHERE t.sid = s.sid
AND t.stat_name = ‘DB time’
AND s.username IS NOT NULL
AND t.VALUE/1000000 >= ’1′ –running more than 1 second
ORDER BY round(t.VALUE/1000000,2) DESC

 Session Elapsed Processing Time Statistic By SID
SELECT a.sid, b.username, a.stat_name, ROUND((a.VALUE/1000000),2) “Time (Sec)”
FROM v$sess_time_model a, v$session b
WHERE a.sid = b.sid
AND b.sid = ’194′
ORDER BY ROUND((a.VALUE/1000000),2) DESC

 Use Longops To Check The Estimation Query Runtime
SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time,
last_update_time, time_remaining “REMAIN SEC”, round(time_remaining/60,2) “REMAIN MINS”,
elapsed_seconds “ELAPSED SEC”, round(elapsed_seconds/60,2) “ELAPSED MINS”,
round((time_remaining+elapsed_seconds)/60,2)”TOTAL MINS”, message TIME
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> ’0′

 Detect Blocking Session
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’

 Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece

 RAC Active Table Locking
SELECT b.sid, b.serial#, a.spi0A 
SELECT sid, serial#, username, STATUS, state, event,
blocking_session, seconds_in_wait, wait_time, action, logon_time
FROM gv$session
WHERE state IN (‘WAITING’)
AND wait_class != ‘Idle’
AND event LIKE ‘%enq%’
AND TYPE=’USER’

 Active Table Locking
SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
c.sql_text,b.logon_time, e.owner, e.object_name “Table Lock”
FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
WHERE b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY b.sid, c.piece

 RAC Active Table Locking
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text,
b.state, c.sql_text,b.logon_time,
b.STATUS, e.owner, e.object_name “Table Lock”
FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
WHERE a.addr=b.paddr
AND b.sql_address = c.address
AND b.sid = d.session_id
AND d.object_id = e.object_id
AND b.STATUS = ‘ACTIVE’
ORDER BY a.spid, c.piece

 Monitor Highest SQL Wait Time Using Active Session History (ASH)
SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.sql_id = SQL.sql_id
AND h.user_id = u.user_id
AND h.event# = e.event#
GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state,
h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
ORDER BY SUM(h.wait_time + h.time_waited) DESC

Monitor Highest Object Wait Time Using Active Session History (ASH)
SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, dba_objects o, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.current_obj# = o.object_id
AND e.event_id = h.event_id
GROUP BY o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
h.sql_id, h.module
ORDER BY SUM(h.wait_time + h.time_waited) DESC

 Monitor Highest Event Wait Time Using Active Session History (ASH)
SELECT h.event “Wait Event”, SUM(h.wait_time + h.time_waited) “Total Wait Time (ms)”
FROM v$active_session_history h, v$event_name e
WHERE h.sample_time BETWEEN sysdate – 1/24 AND sysdate –event in the last hour
AND h.event_id = e.event_id
AND e.wait_class <> ‘Idle’
GROUP BY h.event
ORDER BY SUM(h.wait_time + h.time_waited) DESC

 Database Time Model Statistic
SELECT wait_class, NAME, ROUND (time_secs, 2) “Time (Sec)”,
ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
FROM
(SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
FROM v$system_event e, v$event_name n
WHERE n.NAME = e.event
AND n.wait_class <> ‘Idle’
AND time_waited > 0
UNION
SELECT
‘CPU’,
‘Server CPU’,
SUM (VALUE / 1000000) time_secs
FROM v$sys_time_model
WHERE stat_name IN (‘background cpu time’, ‘DB CPU’))
ORDER BY time_secs DESC;

 Monitor I/O On Data Files
SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim
FROM v$filestat vfs, dba_data_files dbf
WHERE vfs.file# = dbf.file_id

 I/O Stats For Data Files & Temp Files
SELECT file_no,
filetype_name,
small_sync_read_reqs “Synch Single Block Read Reqs”,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
round(small_sync_read_latency/1000,2) “Single Block Read Latency (s)”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
async_io “Asynch I/O Availability”
FROM v$iostat_file
WHERE filetype_id IN (2,6) –data file and temp file

 I/O Stats By Functionality
SELECT function_name,
small_read_reqs “Single Block Read Requests”,
small_write_reqs “Single Block Write Requests”,
large_read_reqs “Multiblock Read Requests”,
large_write_reqs “Multiblock Write Requests”,
number_of_wait “I/O Waits”,
round(wait_time/1000,2) “Total Wait Time (ms)”
FROM v$iostat_function
ORDER BY function_name
  
Temporary Tablespace Usage By SID
SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype,
tu.extents, tu.blocks, SQL.sql_text
FROM v$tempseg_usage tu, v$session s, v$sql SQL
WHERE tu.session_addr = s.addr
AND tu.session_num = s.serial#
AND s.sql_id = SQL.sql_id
AND s.sql_address = SQL.address

 Monitor Overall Oracle Tablespace
SELECT d.STATUS “Status”,
d.tablespace_name “Name”,
d.contents “Type”,
d.extent_management “Extent Management”,
d.initial_extent “Initial Extent”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024,’99,999,999.999′) “Used (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ’990.00′) “Used %”,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),’99,999,990.900′) “MaxSize (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.maxbytes * 100, 0), ’990.00′) “Used % of Max”
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,’NO’,bytes,’YES’,maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;

 Cache Hit Ratio
SELECT ROUND(((1-(SUM(DECODE(name,
‘physical reads’, VALUE,0))/
(SUM(DECODE(name, ‘db block gets’, VALUE,0))+
(SUM(DECODE(name, ‘consistent gets’, VALUE, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
FROM v$sysstat –Use gv$sysstat if running on RAC environment

 Library Cache Hit Ratio
SELECT SUM(pins) “Total Pins”, SUM(reloads) “Total Reloads”,
SUM(reloads)/SUM(pins) *100 libcache
FROM v$librarycache –Use v$librarycache if running on RAC environment

 DB Session Memory Usage
SELECT se.sid,n.name, MAX(se.VALUE) maxmem
FROM v$sesstat se, v$statname n
WHERE n.statistic# = se.statistic#
AND n.name IN (‘session pga memory’,’session pga memory max’,
‘session uga memory’,’session uga memory max’)
GROUP BY n.name, se.sid
ORDER BY MAX(se.VALUE)

You can query this for how much free, used, total, and percentage filled space is available for each tablespace
select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused
from (select a.tablespace_name, a.bytes_free,b.bytes_total,
ROUND((100-(100*a.bytes_free/b.bytes_total)),2)  perused from
(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,
(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b
where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;
select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space 
group by tablespace_name;
How to check apps HTTP_Server,OC4J status/Start/Stop :-
[oracle@r0469 scripts]$ ./adopmnctl.sh status
[oracle@r0469 scripts]$ ./adopmnctl.sh start
[oracle@r0469 scripts]$ ./adopmnctl.sh stop 
 You are running adopmnctl.sh version 120.6
 Checking status of OPMN managed processes…
 Processes in Instance: VIS_r0469.r0469.oracle.com
———————————+——————–+———+———
ias-component                    | process-type       |     pid | status 
———————————+——————–+———+———
OC4JGroup:default_group          | OC4J:oafm          |   12248 | Alive  
OC4JGroup:default_group          | OC4J:forms         |   12182 | Alive  
OC4JGroup:default_group          | OC4J:oacore        |   12100 | Alive  
HTTP_Server                      | HTTP_Server        |   12048 | Alive  
 
 adopmnctl.sh: exiting with status 0
 adopmnctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adopmnctl.txt for more information … 
 
How to check apps Concurrent Manager status/Start/Stop :-
[oracle@r0469 scripts]$ ./adcmctl.sh start
[oracle@r0469 scripts]$ ./adcmctl.sh stop
[oracle@r0469 scripts]$ ./adcmctl.sh status
You are running adcmctl.sh version 120.17.12010000.3
 Enter the APPS username : apps
 Enter the APPS password :apps
Internal Concurrent Manager is Active.
 adcmctl.sh: exiting with status 0
 adcmctl.sh: check the logfile /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adcmctl.txt for more information … 
 
 R12 Apps Scripts Locations:-
 
 [oracle@r0469 ]$ cd /oracle/VIS/inst/apps/VIS_r0469/admin/scripts
[oracle@r0469 scripts]$ ll
total 176
-rwx—— 1 oracle dba  7301 Aug 31 12:33 adalnctl.sh
-rwx—— 1 oracle dba  8243 Aug 31 12:34 adapcctl.sh
-rwx—— 1 oracle dba  1540 Aug 31 12:33 adautocfg.sh
-rwx—— 1 oracle dba 16497 Aug 31 12:33 adcmctl.sh
-rwx—— 1 oracle dba  7215 Aug 31 12:33 adexecsql.pl
-rwx—— 1 oracle dba  8728 Aug 31 12:34 adformsctl.sh
-rwx—— 1 oracle dba 10208 Aug 31 12:34 adformsrvctl.sh
-rwx—— 1 oracle dba  7996 Aug 31 12:34 adoacorectl.sh
-rwx—— 1 oracle dba  8004 Aug 31 12:34 adoafmctl.sh
-rwx—— 1 oracle dba  7282 Aug 31 12:34 adopmnctl.sh
-rwx—— 1 oracle dba 15298 Aug 31 12:33 adpreclone.pl
-rwx—— 1 oracle dba  5801 Aug 31 12:33 adstpall.sh
-rwx—— 1 oracle dba  5945 Aug 31 12:33 adstrtal.sh
-rwx—— 1 oracle dba  2240 Aug 31 12:34 gsmstart.sh
drwxrwxrwx 2 oracle dba  4096 May  5 14:53 ieo
-rwx—— 1 oracle dba  2277 Aug 31 12:34 java.sh
-rwx—— 1 oracle dba  6388 Aug 31 12:36 jtffmctl.sh
drwxrwxrwx 2 oracle dba  4096 May  5 14:53 msc
-rwxrwxr– 1 oracle dba  6632 Aug 31 12:36 mwactl.sh
-rwxrwxr– 1 oracle dba  6645 Aug 31 12:36 mwactlwrpr.sh
-rwxrwxrwx 1 oracle dba  1438 May 16 11:56 sqlnet.log
 
How to check apps Listener Status/Start/Stop:-
 
[oracle@r0469 scripts]$ ./adalnctl.sh status
[oracle@r0469 scripts]$ ./adalnctl.sh start
[oracle@r0469 scripts]$ ./adalnctl.sh stop
adalnctl.sh version 120.3
 
Checking status for listener process APPS_VIS.
 
LSNRCTL for Linux: Version 10.1.0.5.0 – Production on 20-SEP-2012 12:52:08
 
Copyright (c) 1991, 2004, Oracle.  All rights reserved.
 
Connecting to (ADDRESS=(PROTOCOL=TCP)(Host=r0469)(Port=1626))
STATUS of the LISTENER
————————
Alias                     APPS_VIS
Version                   TNSLSNR for Linux: Version 10.1.0.5.0 – Production
Start Date                20-SEP-2012 09:47:09
Uptime                    0 days 3 hr. 4 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/VIS/inst/apps/VIS_r0469/ora/10.1.2/network/admin/listener.ora
Listener Log File         /oracle/VIS/inst/apps/VIS_r0469/logs/ora/10.1.2/network/apps_vis.log
Listening Endpoints Summary…
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=r0469.oracle.com)(PORT=1626)))
Services Summary…
Service “FNDFS” has 1 instance(s).
  Instance “FNDFS”, status UNKNOWN, has 1 handler(s) for this service…
Service “FNDSM” has 1 instance(s).
  Instance “FNDSM”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
 
adalnctl.sh: exiting with status 0
 
adalnctl.sh: check the logfile
 
 /oracle/VIS/inst/apps/VIS_r0469/logs/appl/admin/log/adalnctl.txt for more information … 
 
Database and Application Information
 
Below are some of the queries that can be used to get the database and Application information.
 
1) Get Product Version
 
SQL> SELECT product
     , VERSION
     , status
FROM   product_component_version;
 
 2) Get Applications Version and Patch Information
 
SQL> SELECT   SUBSTR (a.application_name, 1, 60) Application_Name
       , SUBSTR (i.product_version, 1, 4) Version
       , i.patch_level
       , i.application_id
       , i.last_update_date
FROM     apps.fnd_product_installations i
       , apps.fnd_application_all_view a
WHERE    i.application_id = a.application_id
ORDER BY a.application_name;
 
 
3) Patch Information AD_APPLIED_PATCHES table stores information about all the patches installed in the system.
 
SQL> SELECT applied_patch_id
     , patch_name
     , patch_type
     , source_code
     , creation_date
     , last_update_date
FROM   ad_applied_patches;
 
 
4) Check if the application is setup for Multi-Org
 
SQL> SELECT multi_org_flag
FROM   fnd_product_groups;
 
M
-
Y
 
Add Responsibility using Sqlplus:
 
BEGIN
FND_USER_RESP_GROUPS_API.LOAD_ROW
(X_USER_NAME => ‘ANATESH’,
X_RESP_KEY => ‘APPLICATION_DEVELOPER’,
X_APP_SHORT_NAME => ‘FND’,
X_SECURITY_GROUP => ‘STANDARD’,
X_OWNER => ‘SYSADMIN’,
X_START_DATE => Trunc(sysdate),
X_END_DATE => NULL,
X_DESCRIPTION => NULL,
X_LAST_UPDATE_DATE => Trunc(sysdate));
COMMIT;
END
 

 Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for. 

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE 
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv(‘LANG’);

 Oracle Database Links (DB Link)

A database link is a path through which a remote user in another database can connect to any other database. Once created the database link exists as an object in the user schema. 

Type of DB Links
There are 3 types of DB links. They are as follows: 

1. PRIVATE: When the DB links is created, it is created under Private mode as default. The Private DBLINK is only available to the user who has created it. It is not possible for a user to grant access on a private DBLINK to other users. 

2. PUBLIC: The Public DBLINK is available to all the users and all users can have the access without any restrictions. 

3. SHARED: Shared database link uses share the server connection to support database link connection. If there are multiple concurrent database link access into a remote database, shared database link can be used to reduce the number of server connections required. Without the shared clause each database link connection requires a separate connection to the remote database. 

Types of Logins: 
In dblink we can use 2 types of login. They are as follows: 

1. DEFAULT LOGIN: The User name and Password is same in both the databases. 
Syntax 
====== 
CREATE [PUBLIC] DATABASE LINK CONNECT TO CURRENT_USER USING 
Code: (Text) 
Create public database link daslink connect to current_user using ‘ORCL’ 

2. EXPLICIT LOGIN: The User Name and Password is different in both the databases. 
Syntax 
====== 
CREATE [PUBLIC|SHARED] DATABASE LINK CONNECT TOIDENTIFIED BY USING 
Code: (text) 
CREATE PUBLIC DATABASE LINK DDLNK CONNECT TO SCOTT IDENTIFIED BY TIGER USING ‘ORCL’ 

Note: To create the public DBLINK the user must have create public database link system privileges.

path:D:\app\prasanthb\product\11.1.0\db_1\NETWORK\ADMIN

oracle2test =  

  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.7.107)(PORT = 1526))
      )
    (CONNECT_DATA =
      (SERVICE_NAME =VIS)
      (SRVR = DEDICATED)
    )
  )
Script to delete the concurrent program from Oracle

Begin 

fnd_program.delete_program(‘program short name’,’schema’);
fnd_program.delete_executable(‘program short name’,’schema’);
commit;
End; 
Find out the URL of the Application in the database


If you want to find out the URL of the Application in the database in which table you can check ?

Option 1:
SQL> select * from icx_parameters;

Option 2:
SELECT PROFILE_OPTION_VALUE
  FROM FND_PROFILE_OPTION_VALUES
WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
                                                             FROM FND_PROFILE_OPTIONS
                                                           WHERE PROFILE_OPTION_NAME =’APPS_FRAMEWORK_AGENT’)
                                                                AND LEVEL_VALUE=0;

 

 Script to display status of all the Concurrent Managers

SELECT DISTINCT concurrent_process_id “Concurrent Process ID”,

       pid “System Process ID”, os_process_id “Oracle Process ID”,
       q.concurrent_queue_name “Concurrent Manager Name”,
       p.process_status_code “Status of Concurrent Manager”,
       TO_CHAR(p.process_start_date,’MM-DD-YYYY HH:MI:SSAM’) “Concurrent Manager Started at”
  FROM fnd_concurrent_processes p,
       fnd_concurrent_queues q,
       fnd_v$process
 WHERE q.application_id = queue_application_id
   AND q.concurrent_queue_id = p.concurrent_queue_id
   AND spid = os_process_id
   AND process_status_code NOT IN (‘K’, ‘S’)
ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name;
 
Query to find who and when update an Oracle Application user’s profile


SELECT t.user_profile_option_name, profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date v.
last_update_date “Change Date”,
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”,
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
Query to fetch Responsibilities attached to a user:
The below query will fetch the responsibilities assigned to a particular user.

 

SELECT 

    fu.user_id,
    fu.user_name,
    fr.responsibility_name,
    fr.description,
    fa.application_name
FROM fnd_user fu,
     fnd_user_resp_groups g,
     fnd_application_tl fa,
     fnd_responsibility_tl fr
WHERE
     g.user_id(+) = fu.user_id
     AND g.responsibility_application_id = fa.application_id
     AND fa.application_id = fr.application_id
     AND g.responsibility_id = fr.responsibility_id
     AND fu.user_name  =UPPER(‘User_Name’);
 How to get Login Passwords for application & Data Base instances
Most often, oracle apps developers have to work in different DB instances for development as well as for testing purposes. In such situations we need to get access to different oracle application instances and data base (dev/test/crp etc.,) instances. So we have to request & follow a lengthy approval process to get those login details, some times you may end up in loosing your delivery schedules.
 
There is a simple way by which you can get the logins/passwords of your DB & Application with out chasing the DBAs.
Oracle follows an encryption algorithm to encrypt user passwords. Most references to the encryption algorithm point to either the PL/SQL package APPS.FND_WEB_SEC or the Java class “oracle.apps.fnd.security.WebSessionManagerProc”.
For decryption and encryption, the following calls are made:
APPS.FND_WEB_SEC >
oracle.apps.fnd.security.WebSessionManagerProc>
oracle.apps.fnd.security.AolSecurity>
oracle.apps.fnd.security.AolSecurityPrivate
 
The actual encryption and decryption routines are in the “oracle.apps.fnd.security.AolSecurityPrivate” Java class. This Java class is stored both in the database as a Java Stored Procedure and in the operating system directory $COMMON_TOP/java.
Create a package specification: get_pwd
– Package Specification
CREATE OR REPLACE PAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2,VALUE IN VARCHAR2)RETURN VARCHAR2;
END get_pwd;
 
 
Create the package body: get_pwd
– Package Body 
CREATE OR REPLACE PAGE 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;
 
Call the package function as shown below:
/** Run this on toad, Get the DB apps password */
SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE (‘GUEST_USER_PWD’))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ((SELECT SUBSTR (fnd_profile.VALUE (‘GUEST_USER_PWD’)
,1
, INSTR (fnd_profile.VALUE (‘GUEST_USER_PWD’), ‘/’)
- 1
)
FROM DUAL))
 
Call the package function as shown below:
/** Run this on toad, Get the application usernames and passwords */
SELECT usertable.user_name
, (SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE (‘GUEST_USER_PWD’))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE (‘GUEST_USER_PWD’)
,1
, INSTR (fnd_profile.VALUE (‘GUEST_USER_PWD’), ‘/’)
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL) AS encrypted_user_password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER (‘username’) — Here username is application login such as ‘OPERATIONS’
 

Concurrent Program Monitoring Scripts

 
Most frequently in support projects we need to monitor and track the concurrent programs. Here are some the important queries to track the status of concurrent program. These are also very useful for the oracle apps system administrator
 
——— Terminating requests———-
 
SELECT Request_Id Reqst,
 user_concurrent_program_name concurrent_program,
       User_Name Requestor,
 Oracle_Username Orcl,
       Fcr.Logfile_Name LN,
       Concurrent_Queue_Name Manager,
       TO_CHAR(Actual_Start_Date, ‘Mm-Dd-Yy Hh24:Mi’) Started,
       Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,   Fnd_Concurrent_Programs_tl Fcp,
     Fnd_Oracle_Userid O,
     Fnd_Concurrent_Processes P,
     Fnd_Concurrent_Queues Q,
     Fnd_User
WHERE
     Controlling_Manager = Concurrent_Process_ID
  AND ( P.Concurrent_Queue_ID  = Q.Concurrent_Queue_ID
  AND P.Queue_Application_ID = Q.Application_ID )
  AND O.Oracle_Id = Fcr.Oracle_Id
  AND ( Fcr.Program_Application_Id = Fcp.Application_Id
  AND Fcr.Concurrent_Program_Id  = Fcp.Concurrent_Program_Id )
  AND Requested_By = User_Id
  AND Phase_Code = ‘R’ AND Status_Code = ‘T’
ORDER BY  Actual_Start_Date, Request_Id
 
 
—— Currently running requests ————
SELECT Request_Id Reqst,
User_Name Requestor,
Oracle_Username Orcl,
      Concurrent_Queue_Name Manager,
Concurrent_Program_Name Program, 
user_concurrent_program_name conc_program,
      TO_CHAR(Actual_Start_Date, ‘Mm-Dd-Yy Hh24:Mi’) Started,
Fcr.Logfile_Name LN,
      Run_Alone_Flag, Single_Thread_Flag
FROM Fnd_Concurrent_Requests Fcr,
      fnd_concurrent_programs_tl fcpt,
      Fnd_Concurrent_Programs Fcp,
      Fnd_Oracle_Userid O,
Fnd_Concurrent_Processes P,
      Fnd_Concurrent_Queues Q, Fnd_User
WHERE
      Controlling_Manager = Concurrent_Process_ID
  AND ( P.Concurrent_Queue_ID  = Q.Concurrent_Queue_ID
  AND P.Queue_Application_ID = Q.Application_ID )
  AND O.Oracle_Id = Fcr.Oracle_Id
  AND ( Fcr.Program_Application_Id = Fcp.Application_Id
  AND Fcr.Concurrent_Program_Id  = Fcp.Concurrent_Program_Id )
  AND (Fcpt.concurrent_program_id = Fcp.Concurrent_program_id)
  AND Requested_By = User_Id
  AND Phase_Code = ‘R’ AND Status_Code   = ‘R’
ORDER BY  Actual_Start_Date, Request_Id
 
 
— Summary of concurrent request execution since Date —
SELECT L1.Meaning Request_Type,
       L2.Meaning Status,
       COUNT(Status_Code) NR
FROM   Fnd_Concurrent_Requests R,
       Fnd_Concurrent_Programs P,
       Fnd_Lookups L1,
       Fnd_Lookups L2
WHERE
       L1.Lookup_Code = P.Execution_Method_Code
  AND  L1.Lookup_Type = ‘CP_EXECUTION_METHOD_CODE’
  AND (R.Program_Application_ID = P.Application_ID 
  AND R.Concurrent_Program_ID  = P.Concurrent_Program_ID )
  AND (R.Status_Code  = L2.Lookup_Code
  AND L2.Lookup_Type = ‘CP_STATUS_CODE’ )
  AND R.Phase_Code = ‘C’
  AND R.Actual_Completion_Date > SYSDATE – 5
GROUP BY
  L1.Meaning, L2.Meaning
 
 
Errored programs on a day
SELECT fcp.user_concurrent_program_name
FROM fnd_concurrent_requests fcr,
     fnd_concurrent_programs_tl fcp
WHERE status_code = ‘E’
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND TRUNC(requested_start_date) = TRUNC(SYSDATE)
 
STATUS_CODE
A Waiting
B Resuming
C
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I
M No Manager
Q Standby
R
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting
 
Managers that are currently running a request
SELECT Concurrent_Queue_Name Manager,
       Request_Id Request,
 User_name,
       Concurrent_Program_Name Program,
 Run_Alone_Flag,
       TO_CHAR(Actual_Start_Date, ‘DD-MON-YY HH24:MI’) Started
  FROM Fnd_Concurrent_Queues Fcq,
 Fnd_Concurrent_Requests Fcr,
       Fnd_Concurrent_Programs Fcp,
 Fnd_User Fu,
 Fnd_Concurrent_Processes Fpro
 WHERE
       Phase_Code = ‘R’ AND
       Fcr.Controlling_Manager = Concurrent_Process_Id       AND               (Fcq.Concurrent_Queue_Id = Fpro.Concurrent_Queue_Id    AND 
Fcq.Application_Id      = Fpro.Queue_Application_Id ) AND (Fcr.Concurrent_Program_Id = Fcp.Concurrent_Program_Id AND Fcr.Program_Application_Id = Fcp.Application_Id )     AND 
Fcr.Requested_By = User_Id;
 
 Oracle Performance Tuning related Queries
 

SQL to identify the query which takes long time

Step1 :  Run the first query ,  this will list all the programs that currently running in Application. Take the SID and use it in the second query.
 
SELECT 
      f.user_name
      ,a.request_id “Req Id” 
      ,a.concurrent_program_id “Prg Id”
      ,a.RESPONSIBILITY_ID Responsibility
      ,a.phase_code,a.status_code
      ,b.os_process_id “OS”
      ,vs.sid
      ,vs.serial# “Serial#”
      ,vp.spid
      ,TO_CHAR(request_date,’DD-MON-YY hh24:mi:ss’) request_date
      ,(NVL(a.actual_completion_date,SYSDATE)-a.actual_start_date)*1440 “Time”
      ,c.concurrent_program_name||’ – ‘||c2.user_concurrent_program_name “Program”
FROM ASYS.fnd_Concurrent_requests a
    ,APPLSYS.fnd_concurrent_processes b
    ,applsys.fnd_concurrent_queues q
    ,APPLSYS.fnd_concurrent_programs_tl c2
    ,APPLSYS.fnd_concurrent_programs c
    ,APPLSYS.fnd_user f
    ,v$session vs
    ,v$process vp
WHERE 
      a.controlling_manager = b.concurrent_process_id
  AND a.concurrent_program_id = c.concurrent_program_id
  AND a.program_application_id = c.application_id
  AND c2.concurrent_program_id = c.concurrent_program_id
  AND c2.application_id = c.application_id
  AND a.phase_code IN (‘I’,’P’,’R’,’T’)
  AND a.requested_by = f.user_id
  AND b.queue_application_id = q.application_id
  AND b.concurrent_queue_id = q.concurrent_queue_id
  AND c2.LANGUAGE = ‘US’ 
  AND a.oracle_process_id = vp.spid
  AND vs.paddr = vp.addr 
ORDER BY 9
 
Step 2 : Get Sid from step1 and Keep on executing this query in SQL. This query will show the currently running SQL in the DB, as your concurrent is submitted and running. You can now find out the exact query  ( select / insert / update ) which is actually taking time in your concurrent program.
 
SELECT sql_text FROM v$sqltext t,v$session s
WHERE t.ADDRESS = s.SQL_ADDRESS
AND t.HASH_VALUE = s.SQL_HASH_VALUE
AND s.sid = 100 – Get this value from step1
ORDER BY PIECE  
 
Currently running sessions in database
Often it requires keeping track of the currently running sessions and the Sql executed in the sessions. So here is the query by which you can find out.
 
 
Get all the active sessions in the database
select username, sid, serial#, process, status, module, action, osuser
from v$session
where username is not null
and status = ‘ACTIVE’
– Get the sqls executed by the active sessions
declare
    x number;
begin
    for x in
    ( select username||'(‘||sid||’,’||serial#||
                ‘) ospid = ‘ ||  process ||
                ‘ program = ‘ || program username,
             to_char(LOGON_TIME,’ Day HH24:MI’) logon_time,
             to_char(sysdate,’ Day HH24:MI’) current_time,
             sql_address,
             LAST_CALL_ET,
             osuser,
           machine
        from v$session
       where status = ‘ACTIVE’
         rawtohex(sql_address) <> ’00’
         and username is not null order by last_call_et )
    loop
        for y in ( select max(decode(piece,0,sql_text,null)) ||
                          max(decode(piece,1,sql_text,null)) ||
                          max(decode(piece,2,sql_text,null)) ||
                          max(decode(piece,3,sql_text,null))
                          sql_text
                     from v$sqltext_with_newlines
                    where address = x.sql_address
                      and piece < 4)
        loop
            if ( y.sql_text not like ‘%listener.get_cmd%’ and
                 y.sql_text not like ‘%RAWTOHEX(SQL_ADDRESS)%’)
            then
                dbms_output.put_line( ‘——————–‘ );
                dbms_output.put_line( x.username );
                dbms_output.put_line( x.logon_time || ‘ ‘ ||
                                      x.current_time||
                                      ‘ last et = ‘ ||
                                      x.LAST_CALL_ET);
                dbms_output.put_line(x.osuser||':’||x.machine);
                dbms_output.put_line(substr( y.sql_text, 1, 250 ) );
            end if;
        end loop;
    end loop;
end;
 
Get all the views that refer a particular table:
SELECT o.object_name, o.status, o.last_ddl_time
  FROM SYS.dba_objects o
 WHERE o.object_type = ‘VIEW’
   AND EXISTS (
              SELECT ‘x’
                   FROM SYS.dba_dependencies d
           WHERE d.referenced_type = ‘TABLE’
                 AND d.TYPE = ‘VIEW’
                    AND d.owner = ‘APPS’
             AND d.NAME = o.object_name
                 AND d.referenced_name LIKE ‘<TABLE_NAME>’);

Get all the tables with the column name:
SELECT
  TABLE_NAME
FROM
  all_tab_columns
WHERE
  column_name = ‘<COLUMN_NAME>’
 
Get SQL help from the Data base itself:
If you need any syntax of a sql command or its details, you don’t need to search for it in google. what you just need to do is use the below sql command from the data base
 
In iSQL*Plus, click the Help button to display iSQL*Plus help. Or Use the below command line help system. In some DB system schema may not be available, then use sys.help instead.
select *
from   system.help
where  upper(topic)=upper(‘string’)
 
Find from which responsibility a concurrent program can be run:
SELECT distinct
  a.user_concurrent_program_name,
  a.description,
  request_group_name,
  e.responsibility_name
FROM
  fnd_concurrent_programs_tl a,
  fnd_request_groups b,
  fnd_request_group_units c,
  fnd_responsibility d,
  fnd_responsibility_tl e
WHERE
  a.concurrent_program_id = c.request_unit_id     
and b.request_group_id = c.request_group_id
and b.request_group_id = d.request_group_id
and d.responsibility_id = e.responsibility_id
and a.application_id = b.application_id
and b.application_id = c.application_id
and d.application_id = e.application_id
and a.concurrent_program_id = :p_conc_program_id
 
 
Find all running concurrent requests with their run times:
The below query is useful for analyzing concurrent program run times and identifying their performance
 
select request_id,
         fcpt.user_concurrent_program_name,
         completion_text,
         actual_start_date,
         actual_completion_date,
         to_date((actual_completion_date – actual_start_date), ‘HH:MM:SS’) duration
 from fnd_concurrent_requests fcr,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
    and fcp.concurrent_program_id = fcpt.concurrent_program_id
    and fcr.actual_start_date > sysdate – 1
    order by actual_completion_date – actual_start_date desc
 
Search all packages for a line of code:
SELECT  *
FROM    ALL_SOURCE
WHERE   TYPE IN (‘PACKAGE’, ‘PAGE BODY’)
AND     TEXT LIKE ‘%XXXX%’
 
The below table shows all the data base object information:
select * from dba_objects where object_name like ‘%HEADER%’ and object_type = ‘TABLE’
 
Sequal to get the concurrent program file name (procedure/pkg name) based on the concurrent program name
select fct.user_concurrent_program_name, 
          fcp.concurrent_program_name,
          fe.execution_file_name,
          fl.meaning execution_method
from fnd_concurrent_programs_tl fct,
        fnd_concurrent_programs fcp,
        fnd_executables fe,
        fnd_lookups fl
where
     upper(fct.user_concurrent_program_name) = upper(‘concurrent program’)
and     fct.concurrent_program_id = fcp.concurrent_program_id
and     fe.executable_id = fcp.executable_id
and     fl.lookup_code = fe.execution_method_code
and     fl.lookup_type = ‘CP_EXECUTION_OD_CODE’
 
List of all active workflow users and their roles
select  wu.name user_name,
        wr.name role_name
from    wf_users wu,
        wf_user_roles wur,
        wf_roles wr
where   wu.name = wur.user_name
and     wur.role_name = wr.name
and     wu.status = ‘ACTIVE’
and     wr.status = ‘ACTIVE’
and     wr.orig_system = ‘WF_LOCAL_ROLES’
order by wu.name,
        wr.name
 
Concurrent requests raised in the last day
select request_id,
       decode(parent_request_id, -1, ‘None’) as parent_request_id,
       fcpt.user_concurrent_program_name,
       decode(  fcr.status_code,
                ‘A’, ‘Waiting’,
                ‘B’, ‘Resuming’,
                ‘C’, ”,
                ‘D’, ‘Cancelled’,
                ‘E’, ‘Error’,
                ‘F’, ‘Scheduled’,
                ‘G’, ‘Warning’,
                ‘H’, ‘On Hold’,
                ‘I’, ”,
                ‘M’, ‘No Manager’,
                ‘Q’, ‘Standby’,
                ‘R’, ”,
                ‘S’, ‘Suspended’,
                ‘T’, ‘Terminating’,
                ‘U’, ‘Disabled’,
                ‘W’, ‘Paused’,
                ‘X’, ‘Terminated’,
                ‘Z’, ‘Waiting’) as status,       
       decode(  fcr.phase_code,
                ‘C’, ‘Completed’,
                ‘I’, ‘Inactive’,
                ‘P’, ‘Pending’,
                ‘R’, ‘Running’) as phase,
       fu.user_name,
       fr.responsibility_name,              
       completion_text,
       argument_text,
       request_date,
       requested_start_date,
       actual_start_date,
       actual_completion_date,
       fcp.concurrent_program_name,
       fe.executable_name,
       fe.execution_file_name
from   fnd_concurrent_requests fcr,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_executables fe,
       fnd_responsibility_vl fr,
       fnd_user fu
where  fcr.concurrent_program_id = fcp.concurrent_program_id
and    fcp.concurrent_program_id = fcpt.concurrent_program_id
and    fcp.executable_id = fe.executable_id
and    fcr.responsibility_id = fr.responsibility_id
and    fcr.requested_by = fu.user_id
and    fcr.request_date > sysdate – 1
–and    fcr.concurrent_program_id = XXXX
order by request_id desc
 
How to find the Trace file generated for a concurrent program
 

 

You can use the following query to locate the trace file, it takes as input the ‘ Concurrent Request id’
SELECT
‘Request id: ‘||request_id , 
‘Trace id: ‘||oracle_Process_id,
‘Trace Flag: ‘||req.enable_trace,
‘Trace Name: ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status : ‘||decode(phase_code,’R’,’Running’) ||’-‘||decode(status_code,’R’,’Normal’),
‘SID Serial: ‘||ses.sid||’,’|| ses.serial#, ‘Module : ‘||ses.module
from
fnd_concurrent_requests req,
v$session ses,
v$process proc,
v$parameter dest,
v$parameter dbnm,
fnd_concurrent_programs_vl prog,
fnd_executables execname where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’
and dbnm.name=’db_name’
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
 
The output would be ‘directory’/’filename.trc’
eg: ‘directory’/ora_3465_act.trc.

In the above directory, do ‘ls -l’ for ‘*3465*trc’ and any latest file that you see with 3465 as a part of the file name would be the trace file.

 
Removing Table Lock
Login to database as SYS or SYSTEM user. Find out the SESSION_ID by running the following SELECT statement
SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name;
 
Use this session id to find SERIAL# by using following SELECT statment
SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = Table Name)
 
Use ALTER SYSTEM command to KILL SESSION and this will release the lock.
ALTER SYSTEM KILL SESSION ‘SID,SERIALl#';
 
This script will report the SQL text of some of the locks currently being held in the database
select s.username username, 
       a.sid sid, 
       a.owner||’.’||a.object object, 
       s.lockwait, 
       t.sql_text SQL
from   v$sqltext t, 
       v$session s, 
       v$access a
where  t.address=s.sql_address 
and    t.hash_value=s.sql_hash_value 
and    s.sid = a.sid 
and    a.owner != ‘SYS’
and    upper(substr(a.object,1,2)) != ‘V$’
 
Script to check Responsibilities assigned to a particular user or users assigned for particular responsibility or all users and their responsibilities:
 SELECT fu.user_id,
fu.user_name,
fur.responsibility_id,
          fr.responsibility_name
FROM fnd_user fu,
fnd_user_resp_groups fur,
fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date – 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date – 1), SYSDATE))
AND user_name like ‘OPERATIONS’ — for all user or for particular user
AND fur.responsibility_application_id = 155 –- Check particular resp.
order by user_name
 
Script to find Oracle API’s for any module:
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name 
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name
 
Query used for audit point of view i.e. when a profile is changed and by which user 
SELECT t.user_profile_option_name,
profile_option_value,
v.creation_date,
v.last_update_date,
v.creation_date – v.last_update_date “Change Date”,
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) “Created By”,
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) “Last Update By”
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id =  :p_value
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;
 
:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)
 
Check Current Applied Patch
SELECT patch_name, patch_type,
maint_pack_level,
creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date
 
Query used to view the patch level status of all modules
SELECT a.application_name,
DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,
patch_level
FROM apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
 
This query will shows concurrent program processing time
SELECT f.request_id ,
pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ‘ HOURS ‘ ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ‘ MINUTES ‘ ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ‘ SECS ‘ time_difference,
DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||’['||f.descriptio
n||']‘,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
 
View all types of request Application wise
SELECT fa.application_short_name,
fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
‘B’, ‘Request Set Stage Function’,
‘Q’, ‘SQL*Plus’,
‘H’, ‘Host’,
‘L’, ‘SQL*Loader’,
‘A’, ‘Spawned’,
‘I’, ‘PL/SQL Stored Procedure’,
‘P’, ‘Oracle Reports’,
‘S’, ‘Immediate’,
fcpv.execution_method_code
) exe_method,
output_file_type,
program_type,
printer_name,
minimum_width,
minimum_length,
concurrent_program_name,
concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,
            Os_Process_ID Osid,
            Q.Concurrent_Queue_Name Manager,
            P.process_status_code Status,
            To_Char(P.Process_Start_Date, ‘MM-DD-YYYY HH:MI:SSAM’) Started_At
from   Fnd_Concurrent_Processes P,
            Fnd_Concurrent_Queues Q,
            FND_V$Process
where  Q.Application_Id = Queue_Application_ID
  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
  and  Spid = Os_Process_ID
  and  Process_Status_Code not in (‘K’,’S’)
order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name
 
Print Oracle Apps versions
SELECT substr(a.application_short_name, 1, 5) code,
       substr(t.application_name, 1, 50) application_name,
       p.product_version version
FROM   fnd_application a,
       fnd_application_tl t,
       fnd_product_installations p
WHERE  a.application_id = p.application_id
AND    a.application_id = t.application_id
AND    t.language = USERENV(‘LANG’);
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s