Below are the queries that are used very
frequently to monitor the database. Copy the below, it will create sql scripts.
echo "select name,open_mode,database_role
from v\$database
/
set line 200
col DEST_NAME for a50
col BINDING for a10
select
PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from
v\$managed_standby
/
archive log list
select count(*) from v\$archive_gap
/
select max(sequence#) from v\$log_history
/
col MESSAGE for a150
select * from(select message from
v\$dataguard_status where message like '%Media Recovery Log%' order by message
desc) where rownum<=5
/
" > sync_dr.sql
echo "
SET PAGESIZE 300
SET LINESIZE 150
col OWNER for a25
col OBJECT_NAME for a30
select OWNER,OBJECT_TYPE, STATUS,count(*)
from dba_objects where STATUS='INVALID' group by
OWNER,OBJECT_TYPE, STATUS order by owner
/
select count(*) from dba_objects where
STATUS='INVALID'
/
prompt Suggestion : @?/rdbms/admin/utlrp.sql"
> invalids.sql
echo "SELECT
a.tablespace_name,b.size_kb,a.free_kb,Trunc((a.free_kb/b.size_kb) * 100)
\"FREE_%\"FROM
(SELECT tablespace_name,Trunc(Sum(bytes)/1024)
FREE_KB FROM dba_free_space GROUP BY
tablespace_name) a,
(SELECT tablespace_name,Trunc(Sum(bytes)/1024)
SIZE_KB FROM dba_data_files GROUP BY
tablespace_name) b
WHERE
a.tablespace_name = b.tablespace_name AND Round((a.free_kb/b.size_kb) *
100,2) < 20
/
select unique status from v\$backup
/
col file_name for a60
set line 150
col \"Auto Extend\" for a10
SELECT tablespace_name,file_name,autoextensible
\"Auto Extend\",bytes/1024/1024 \"Used in
MB\",maxbytes/1024/1024 \"Max in MB\",increment_by
FROM dba_data_files where
tablespace_name='&T_NAME' order by file_name
/"> tsinfo.sql
echo "set pages 100
set line 200
col \"User Name\" for a10
col owner for a10
col Object_type for a10
col \"Lock Type\" for a15
select substr(lpad('
',decode(l.xidusn,0,3,0))||l.oracle_username,1,10) \"User
Name\",substr(l.session_id,1,5)Sid,substr(S.SERIAL#,1,5) serial#,
s.status,to_char(s.logon_time,'dd-mon-yy hh24:mi')
logon_time,substr(o.owner,1,10) Owner, o.object_name,substr(o.object_type,1,10)
Object_type,
substr(decode(l.locked_mode,2,'Row Share',3,'Row
exclusive',4,'Share mode',5,'Share row exclusive',6,'Exclusive mode'),1,15)
\"Lock Type\" from
gv\$locked_object l ,all_objects o,gv\$SESSION S
where l.object_id=o.object_id and s.sid=l.session_id order by logon_time
/" > locked.sql
echo "select
distinct to_name object_locked
from
v\$object_dependency
where
to_address in
(
select /*+ ordered */
w.kgllkhdl address
from
dba_kgllock w,
dba_kgllock h,
v\$session w1,
v\$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and
w.kgllktype =
h.kgllktype
and
w.kgllkhdl = h.kgllkhdl
and
w.kgllkuse = w1.saddr
and
h.kgllkuse = h1.saddr
)
/" > dml.sql
echo "select name,open_mode,database_role
from v\$database
/
set line 200
col DEST_NAME for a50
col BINDING for a10
select DEST_ID,DEST_NAME,STATUS,BINDING,ERROR from
v\$ARCHIVE_DEST where status<>'INACTIVE'
/
select
PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from
v\$managed_standby
/
col name for a30
col value for a40
select name,value from v\$parameter where value
like '%SERVICE%'
/
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 999999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED,
LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM(SELECT NAME
DB_NAME FROM v\$DATABASE),
(SELECT
UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1)))))
HOSTNAME FROM v\$INSTANCE),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM
v\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),(SELECT MAX(SEQUENCE#)
LOG_APPLIED FROM v\$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' ),(SELECT
TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM
v\$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES')
/
" > sync.sql
echo "SELECT
tablespace_name,SUM(bytes)/1024/1024 \"Free in MB\"
FROM dba_free_space where
tablespace_name='&T_NAME' GROUP BY tablespace_name
/"> chunk.sql
echo "column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 100
set pages 22
set head off
SELECT
'****************************************************************************************
Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace
Name:'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'Execution Method :'
||DECODE (execname.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
'N/A'
),'Executable Name :
'||execname.execution_file_name|| execname.subroutine_name,
'Phase : '||DECODE (req.phase_code,
'C', 'Completed',
'I', 'Inactive',
'P', 'Pending',
'R', 'Running',
'N/A'
)||' | Status : '||DECODE (req.status_code,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
'D', 'Cancelled',
'E', 'Errored',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', 'Normal',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'N/A'
),
'PID :'||proc.spid || ' | SID Serial:
'||ses.sid||','|| ses.serial#,
'Module : '||ses.module,
'Actual Start Date
:'||to_char(req.actual_start_date,'DD-MON-YYYY HH24:MI:SS')||' | Actual
Completion Date :'||to_char(req.actual_completion_date,'DD-MON-YYYY HH:MI:SS'),
'Duration Hours
:'||floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)||
' Min
:'||floor((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)||
' Sec
:'||round((((req.actual_completion_date-req.actual_start_date)*24*60*60) -
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600
-
(floor((((req.actual_completion_date-req.actual_start_date)*24*60*60)
-
floor(((req.actual_completion_date-req.actual_start_date)*24*60*60)/3600)*3600)/60)*60)
)),
'Log File Name : '||req.logfile_name,
'Out File Name : '||req.outfile_name,
'****************************************************************************************'
from apps.fnd_concurrent_requests req, v\$session
ses, v\$process proc,
v\$parameter dest, v\$parameter dbnm,
apps.fnd_concurrent_programs_vl prog,
apps.fnd_executables execname
where req.request_id = &Request_ID
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id =
prog.concurrent_program_id
and req.program_application_id =
prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id
/"> cr.sql
echo "select l1.sid,'is blocking',l2.sid from
v\$lock l1,v\$lock l2
where
l1.id1=l2.id1 and l1.id2=l2.id2 and l1.block=1 and l2.request>0 order
by l1.sid
/
set line 200
col program for a16
col MODULE for a16
col action for a35
col 'Time Hrs' for 999999.99
col user@machine for
a35
col (SID,SERIAL#) for
a13
set line 150
select sid||','||serial#
\"(SID,SERIAL#)\",status,last_call_et/3600 \"Time
Hrs\",username||'@'||machine as
\"user@machine\",program,module,action,type
from gv\$session where sid=&SID
/
select s.sql_text
FROM v\$process p,v\$session a,v\$sqltext
s,v\$sqlarea sa
where
a.sid =
&SID
and s.address = a.prev_sql_addr
AND p.addr = a.paddr
AND s.address=sa.address
AND a.username is not null
order by a.username, a.osuser, a.sid, s.piece
/"> locks.sql
echo "col USER_CONCURRENT_PROGRAM_NAME for
a50
col ARGUMENT_TEXT
for a50
set line 200
alter session set nls_date_format='dd-Mon-RR
hh24:mi:ss';
select
request_id,USER_CONCURRENT_PROGRAM_NAME,requested_by,ACTUAL_START_DATE,ARGUMENT_TEXT
from apps.fnd_concurrent_worker_requests where status_code='R'
/" > crs.sql
echo "set line 200
col program for a16
col MODULE for a16
col action for a30
col 'Time Hrs' for 999999.99
col user@machine for
a30
col (SID,SERIAL#) for
a13
set line 200
col SPID for a10
select b.spid,a.sid||','||a.serial#
\"(SID,SERIAL#)\",a.status,a.last_call_et/3600 \"Time
Hrs\",a.username||'@'||a.machine as
\"user@machine\",a.program,a.module,a.action,a.type
from gv\$session a,gv\$process b where
a.sid=&SID and a.paddr=b.addr
/" > sid.sql
echo "col user@machine for a30
col (SID,SERIAL#)
for a20
col s.program for a20
col MODULE for a20
col action for a20
col spid for a8
select p.spid,s.sid||','||s.serial#
\"(SID,SERIAL#)\",last_call_et/3600,status,s.username||'@'||s.machine
as \"user@machine\",s.program,module,action
from v\$session s,v\$process p
where p.spid='&PID'and p.addr=s.paddr
/" > pid.sql
echo "select s.sql_text
FROM v\$process p,v\$session a,v\$sqltext
s,v\$sqlarea sa
where
a.sid =
&SID
and s.address = a.prev_sql_addr
AND p.addr = a.paddr
AND s.address=sa.address
AND a.username is not null
order by a.username, a.osuser, a.sid, s.piece
/" > sql.sql
echo "set line 200
col program for a20
col MODULE for a16
col action for a30
col 'Time Hrs' for 999999.99
col user@machine for
a30
col (SID,SERIAL#) for
a13
set line 150
select sid||','||serial#
\"(SID,SERIAL#)\",status,last_call_et/3600 \"Time
Hrs\",username||'@'||machine as
\"user@machine\",program,module,action,type
from gv\$session
/" > sess.sql
echo "col db.name for a10
col HOST_NAME for a30
set line 200
select
inst.HOST_NAME,db.name,open_mode,instance_name,inst.status,database_role,to_char(startup_time,'dd-mon-yy
hh24:mi:ss') Startup,
sum(df.bytes / (1024*1024*1024)) \"DB Size in
GB\" from v\$database db,v\$instance inst,v\$datafile df group by
db.name,open_mode,
instance_name,inst.status,database_role,startup_time,inst.HOST_NAME
/" > db.sql
echo "select BYTES/1024/1024 from
dba_segments where segment_name='&table_size'
/">tab_size.sql
echo "select
lpad(' ',
2*level) || granted_role \"User, his roles and privileges\"
from
(
/* THE
USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* 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
/" > user_privs.sql
echo "
alter session set nls_date_format='dd-Mon-RR
hh24:mi:ss';
Prompt 'Entere Patch/Bug Number:'
Accept PATCHNUM
set line 200
col PATCH_NAME for a30
select PATCH_NAME,PATCH_TYPE,CREATION_DATE from
apps.ad_applied_patches where PATCH_NAME like '%&PATCHNUM%'
/
Prompt 'AD_BUG history:'
set line 200
col BUG_NUMBER for a20
col APPLICATION_SHORT_NAME for a20
select BUG_NUMBER,APPLICATION_SHORT_NAME,CREATION_DATE,GENERIC_PATCH
from apps.ad_bugs where BUG_NUMBER in('&PATCHNUM')
/"> patch.sql
echo "select release_name from
apps.fnd_product_groups
/">release.sql
echo "col APPLICATION_SHORT_NAME for a10
col APPLICATION_NAME for a60
set line 200
select fav.application_short_name
,fav.application_name
,fpi.patch_level
,decode(fpi.status,'N','None','I','Installed','S','Shared')
"Status"
from apps.fnd_product_installations fpi
,apps.fnd_application_vl fav
where fav.application_id=fpi.application_id
order by 1
/" > product_status.sql
echo "select PRIVILEGE from role_sys_privs
where ROLE='&role'
/">role_privs.sql
echo "set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading
\"ELAPSED|SECONDS\"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading
\"OUTPUT|MBYTES\"
col session_recid for 999999 heading
\"SESSION|RECID\"
col session_stamp for 99999999999 heading
\"SESSION|STAMP\"
col status for a10 trunc
col time_taken_display for a10 heading \"TIME|TAKEN\"
col output_instance for 9999 heading
\"OUT|INST\"
select
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3,
'Tuesday', 4, 'Wednesday',
5,
'Thursday', 6, 'Friday',
7,
'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf,
x.df, x.i0, x.i1, x.l,
ro.inst_id
output_instance
from v\$RMAN_BACKUP_JOB_DETAILS j
left outer
join (select
d.session_recid,
d.session_stamp,
sum(case when d.controlfile_included
= 'YES' then d.pieces else 0 end) CF,
sum(case when
d.controlfile_included = 'NO'
and
d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when
d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when
d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when
d.backup_type = 'L' then d.pieces else 0 end) L
from
v\$BACKUP_SET_DETAILS d
join v\$BACKUP_SET s on
s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid,
d.session_stamp) x
on
x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer
join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from Gv\$RMAN_OUTPUT o
group by o.session_recid,
o.session_stamp)
ro on
ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time >
trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time
/" > rman_status.sql
echo "set line 200
select
sid,
to_char(start_time,'dd-Mon-yy hh24:mi:ss') Start_Time,
to_char(sysdate,'dd-Mon-yy hh24:mi:ss')
Time_Now,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from
v\$session_longops
where
totalwork > sofar
AND
opname NOT LIKE '%aggregate%'
AND
opname like 'RMAN%'
/" > rman_sofar.sql
echo "col units for a10
set line 200
SELECT sid,
opname,
sofar,
totalwork,
units,
elapsed_seconds/60 \"Elasped Min.\",
time_remaining/60 \"Remaining Min.\"
FROM v\$session_longops
WHERE sofar != totalwork
/
prompt Note: There may be other sessions in
pending. This time is for current session" >rman_sofar1.sql
echo "col units for a10
set line 200
SELECT sid,
opname,
sofar,
totalwork,
units,
elapsed_seconds/60 \"Elasped Min.\",
time_remaining/60 \"Remaining Min.\"
FROM v\$session_longops
WHERE sofar != totalwork
/
prompt Note: There may be other sessions in
pending. This time is for current session" >longops.sql
echo "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')
/"
> cmstatus.sql
Usage of the above created queries:
sync_dr.sql – Will show the Sync status of Standby
database.
invalids.sql – will list the invalid objects.
tsinfo.sql –
- will list the tablespaces less than 20% free.
- Will display the hot backup status.
- Will prompt for tablespace name
- List the datafiles associated with the tablespace given in step 3.
No comments:
Post a Comment