Oracle DBA Daily Health Check Activity
Oracle DBA performs below activities as part of
DB Health Check.
Oracle Database Performance Monitor And Health Check Tricks and Tips -RAC Database and Dataguard step by step
1. Check
that all instances and listener are up if require check remote connectivity of
database.
To set profile or Enviroment
Cd /home/oracle
ls -ltr .prof*
. ./.profile_<database name>
or
$
ls -lrt *.env
-rw-r--r-- 1 oracle
oinstall 1823 Apr 18
2021 TESTDB.env
. TESTDB.env
ps –ef|grep pmon
ps –ef|grep tns
pqlplus a/a@<database name>
select name,open_mode
from gv$database;
select
count(1),inst_id ,status from gv$session group by inst_id ,status;
select instance_name,status,to_char(STARTUP_TIME,'dd-mon-yyyy
hh24:mi:ss') from v$instance;
cd $ORACLE_HOME/OPatch
opatch lsinventory
./opatch lspatches
2. Monitor
alert log for error (using tail -f)
Check
alert log location
Select * from v$diag_info
select value from v$diag_info where name ='Diag Trace';
Tail -200f <alert log name>
3. Check
any session blocking the other session
SELECT
DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2,
lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type)
IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
4.
Check module wise usages and problematic event on database
Select count(1),status
from v$session group by status
select
count(1),status,module from v$session group by status,module order by module
desc
select
count(1),status,module,sql_id from v$session group by status,module,sql_id
order by module desc
select count(1),event
from v$session_wait group by event;
5. Check
is there any dbms jobs running & check the status of the same
To check
job history
ALTER SESSION SET NLS_DATE_FORMAT
='DD-MM-YYYY HH24:MI:SS';
column job_name format a30
column log_date format a40
column actual_start_date format
a40
column run_duration format a60
column cpu_used format a50
SELECT job_name, log_date,
status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details
where job_name like '%STA%' and trunc(log_date) = trunc(sysdate -1) ORDER BY
LOG_DATE DESC;
To check currently running
SELECT job_name, session_id,
running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;
set linesize 120
column WINDOW_NAME format a20
column ENABLED format a7
column REPEAT_INTERVAL format a60
column DURATION format a15
select
job_name,job_type,program_name,schedule_name,job_class,STATE from
dba_scheduler_jobs where job_name = 'GATHER_STATS_JOB'
/
6. How
much redo generated per day
alter
session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select
sysdate from dual;
set long 9999999
set pagesize 0
set linesize 120
select
max(bytes_redo) as max_redo_hour_bytes
from
(select to_char(first_time,'YYYYMMDD HH24') as day_hour,sum(blocks*block_size)
as bytes_redo
from
v$archived_log
group
by to_char(first_time,'YYYYMMDD HH24'));
col
day for a8;
select
to_char(first_time,'YY-MM-DD')
day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999')
"00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999')
"01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999')
"02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999')
"03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999')
"04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999')
"05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999')
"06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999')
"07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999')
"08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999')
"09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999')
"10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999')
"11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999')
"12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999')
"13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999')
"14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999')
"15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999')
"16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999')
"17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999')
"18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999')
"19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999')
"20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999')
"21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999')
"22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999')
"23",
COUNT(*)
from
v$log_history
group
by to_char(first_time,'YY-MM-DD')
order
by day ;
select
* from v$log;
7.
To detect object level
locking
select a.sid,
a.serial#,OBJECT_NAME
from v$session a,
v$locked_object b, dba_objects c =
where b.object_id =
c.object_id
and a.sid = b.session_id;
8.
To check session,SQL
detail and kill a session
SELECT s.sid, s.serial#, s.username,
s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS')
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid in('10429','10393','10393','10392');
Or
SELECT p.program,
p.spid, s.saddr, s.sid, s.serial#, s.username, s.osuser, s.machine, s.program,
s.logon_time, s.status,sql_id FROM
v$session s, v$process p WHERE s.paddr = p.addr AND s.sid = XXX ;
We can determine
the specific SQL statement for each ID:
select sql_text from
dba_hist_sqltext where sql_id='0r5xv5d42p3p6';
SELECT
SQL_ID,PLAN_HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_ID=’&SQL_ID’
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT a.sql_text,b.sid,b.serial#,username,b.status,b.sql_id,to_char(b.logon_time,'dd-mon:hh24:mi'),b.machine,b.module
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sid = &1
alter system kill session
'sid,serial#' immediate;
eg
alter system kill session '41,5'
immediate;
ps
-ef|grep java
Kill
-9 <OSPID>
Query the database to make sure the above killed
database session has been killed
select sid,serial#,osuser,username,program,status,SQl_id
from v$session where sid=&sid;
SELECT
USERNAME,SID,SERIAL#,PROGRAM,STATUS,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY
HH24:MI:SS') FROM V$SESSION where sid=XX
and serial=xx ;
9. Check
the usage of SGA
set linesize 300
select * from v$resource_limit where
resource_name in ('processes','sessions')
show parameter MEMORY%TARGET
show parameter SGA%TARGET
show parameter PGA%TARGET
show parameter pga
show parameter sga
show parameter shared pool
show parameter db_cache_size
show parameter memory
/* Current MEMORY settings */
select component,
current_size/(1024*1024) from V$MEMORY_DYNAMIC_COMPONENTS;
/* SGA */
select
sum(value)/(1024*1024)"Total SGA (Fixed+Variable)" from v$sga
/
select * from v$sga_target_advice
order by sga_size;
/* PGA */
select
sum(PGA_ALLOC_MEM)/1024/1024 "Total PGA Allocated (Mb)" from
v$process p, v$session s where p.addr = s.paddr
/
select
PGA_TARGET_FOR_ESTIMATE/1024/1024,BYTES_PROCESSED/1024/1024,ESTD_EXTRA_BYTES_RW/1024/1024
from V$PGA_TARGET_ADVICE;
SELECT
round(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE
cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice
/
set linesize 90
set pagesize 60
column component format a25
column Final format
99,999,999,999
column Started format A25
SELECT instance_name,COMPONENT
,OPER_TYPE,FINAL_SIZE/1024/1024 Final,to_char(start_time,'dd-mon hh24:mi:ss')
Started, to_char(end_time,'dd-mon hh24:mi:ss') FROM V$SGA_RESIZE_OPS
/
10. Check the free space at O/s Level
df -g|grep <database_name>
df -k|grep <database_name> ---->solaris
df -h|grep <database_name> ---->solaris
11. Check
all last night backups were successful.
set
lines 200 pages 200
col
STATUS format a25
col hrs
format 999.99
col
START_TIME format a20
col
END_TIME format a25
select
INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi')
start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from
V$RMAN_BACKUP_JOB_DETAILS where START_TIME > (sysdate-2)
order
by SESSION_KEY
/
RMAN> LIST BACKUP SUMMARY;
RMAN>
list backup;
RMAN>
LIST BACKUP BY DATAFILE;
RMAN>
LIST BACKUP OF DATABASE;
RMAN>
LIST BACKUP OF ARCHIVELOG ALL;
RMAN>
LIST BACKUP OF CONTROLFILE;
RMAN>
list backup of database completed between '08-Mar-2022' and '10-aug-2022';
12.
Check tablespaces should not be used more that 95%.
col
"TABLESPACE" for a50
col
"Totalspace(MB)" for
9999999
col
"Used Space(MB)"
for 9999999
set linesize 200
select t.tablespace,
t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2)
as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as
"%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as
totalspace,
d.tablespace_name tablespace
from
dba_data_files d group by d.tablespace_name) t,
(select
round(sum(f.bytes)/(1024*1024)) as freespace,
f.tablespace_name tablespace
from dba_free_space f group by
f.tablespace_name) fs
where t.tablespace=fs.tablespace (+) and fs.tablespace='&TSPACE'
order by t.tablespace;
==========
col
"TABLESPACE" for a50
col
"Totalspace(MB)" for
9999999
col
"Used Space(MB)"
for 9999999
set linesize 200
select
t.tablespace,
t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used
Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as
"%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select
round(sum(d.bytes)/(1024*1024)) as totalspace,
d.tablespace_name tablespace
from
dba_data_files d group by d.tablespace_name) t,
(select
round(sum(f.bytes)/(1024*1024)) as freespace,
f.tablespace_name tablespace
from
dba_free_space f group by f.tablespace_name) fs
where
t.tablespace=fs.tablespace (+)
order by
t.tablespace;
To
check Temp Ta bleSpace
select sysdate dtstamp,
s.tablespace_name,
d.tbspc_mb,
s.total_blocks*8192/1024/1024 temp_tot_mb,
s.used_blocks*8192/1024/1024 temp_used_mb,
s.free_blocks*8192/1024/1024 temp_free_mb
from v$sort_segment s,
(select tablespace_name,sum(bytes/1024/1024)
tbspc_mb
from dba_data_files
group by tablespace_name
union
select tablespace_name,sum(bytes/1024/1024)
tbspc_mb
from dba_temp_files
group by tablespace_name) d
where s.tablespace_name=d.tablespace_name;
column file_name format a50
select file_name,TABLESPACE_NAME,bytes/1024/1024
from dba_temp_files where TABLESPACE_NAME='TEMP' order by 1 ;
alter database tempfile
'/ora/TESTDB/temp/oradata/TESTDB/tivoli_temp_01.dbf' resize 4000m
!ls -ltr
/ora/TESTDB/temp/oradata/TESTDB/tivoli_temp_01.dbf' resize 4000m
column file_name format a42
select file_name,TABLESPACE_NAME,bytes/1024/1024
from dba_temp_files order by 1 ;
SQL> alter database tempfile
'/ora/ebsprd/temp/oradata/ebsprd/temp_002.dbf' resize 900m
SELECT file_name,bytes/1024/1024,AUTOEXTENSIBLE
from dba_temp_files;
ALTER TABLESPACE temp ADD TEMPFILE
'/oradata/temp03.dbf' SIZE 100M;
ALTER TABLESPACE temp ADD TEMPFILE
'/ora/mim11prd/temp/oradata/mim11prd/temp_10.dbf' size 10240m;
set
num 5
COL
file_name FORMAT A60
select file_name,bytes/1024/1024
mb,AUTOEXTENSIBLE from dba_data_files
where AUTOEXTENSIBLE='YES';
set
num 5
COL
file_name FORMAT A60
select file_name,bytes/1024/1024 mb ,AUTOEXTENSIBLE from dba_data_files where
tablespace_name='&TSPAC' ;
set
num 5
COL
file_name FORMAT A60
select file_name,bytes/1024/1024 mb ,AUTOEXTENSIBLE from dba_data_files where
tablespace_name='UNDO' ;
alter tablespace undo add datafile
'/ora/daaprd/008/oradata/daaprd/undo_19.dbf' size 6001m;
2.DATAFILE LEVEL SPACE:
set linesize 500
col file_name for a100
select file_name ,(bytes)/1024/1024 from
dba_data_files where TABLESPACE_NAME='<ablespace_name>' order by file_name asc;
1) Archivelog filesystem
& Trace mount (<70%)
df -g |grep
/ora/database_name/arch
df -g |grep /ora/database_name/trace
Data
Filesystem space (<85%) And also check the oracle home size
df -g |grep
<database_name>
df -g /oracle
13. Check all crons and Oracle Jobs are completed without
any error
crontab -l
14. To check Database size
SQL> select
sum(bytes)/1024/1024/1024 "Total DB size in GB" from dba_data_files;
SQL> select
sum(bytes/1024/1024/1024) tbspc_gb from dba_temp_files;
SQL> select count(1) from
dba_data_files;
SQL> select sum(bytes)/1024/1024/1024
"Total DB size in GB" from dba_segments;
15. Verify resources for acceptable performance
Basic
Performance Check
select count(1),event from v$session group by
event;
select count(1),status,username from v$session
group by status,username;
select count(1),status,module
from v$session group by status,module order by module desc
select
count(1),status,module,sql_id from v$session group by status,module,sql_id
order by module desc
select sid, serial#, context, sofar,
totalwork,round(sofar/totalwork*100,2) "%_complete" from
v$session_longops where sofar <> totalwork;
select inst_id,sql_hash_value,sql_id,
sid,serial#,to_char(start_time,'dd-mon:hh24:mi') start_time,
opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,username,
time_remaining tre
from
gv$session_longops
where
totalwork <> SOFAR
order
by 7,tre, start_time,sid
1.
Generate AWR,ASH and ADDM Report for further analysis
@
?/rdbms/admin/awrrpt.sql
@
?/rdbms/admin/awrrpti.sql
@
?/rdbms/admin/ashrpt.sql
@
?/rdbms/admin/addmrpt.sql
@?/rdbms/admin/awrddrpt.sql--compare
how the database performed during two different periods.
2.
Job failure
find the job details and logs
cd /prod
ps -ef|grep -i <job_name>
find . -name URLW0131* 2>/dev/null
/home/oracle $proctree 44630986
Database performance issue check
1) To Check server load
topas - To know top consuming
process and load on server
nmon and press m to know
memory and paging utilization
vmstat 5 5
lsps -s To know paging usages
2) Map OS
processes with database process as below
select s.sql_id,s.sql_hash_value,s.prev_hash_value,s.sql_id,s.sid,s.serial#,s.username,s.event,to_char(s.logon_time,'dd-mon:hh24:mi') from v$session s, v$process p
where p.addr=s.paddr and
p.spid in(1105,4452)
3)
Take explain plan and check statistics of tables and indexes
select * from
table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'))
select a.owner,
a.table_name, a.num_rows, b.bytes/1048576 MB, a.last_analyzed from dba_tables
a, dba_segments b
where a.owner=b.owner and a.table_name=b.segment_name
and a.table_name='&table_name' order by b.bytes desc;
select count(1),event
from v$session group by event;
4)
Check any long running SQL on database
select sid, serial#,
context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where sofar <> totalwork;
5)
Check session level and object level lock on database
Object level locking
------------------------
select a.sid,
a.serial#,OBJECT_NAME
from v$session a,
v$locked_object b, dba_objects c =
where b.object_id =
c.object_id
and a.sid =
b.session_id;
or
Select * from (
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
p.spid,s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),machine,module
from v$session s, v$process p
where p.addr=s.paddr and
s.sid =369
kill -9 5747
ALTER SYSTEM KILL
SESSION '369,12849' immediate;
SELECT s.sid, s.serial#,
s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY
HH24:MI:SS')
FROM v$session s,
v$process p WHERE s.paddr = p.addr AND p.spid in('10429','10393','10393','10392');
To kill background process in Linux
ps -ef |grep nohup
$ pstree -ap 1336769 ---
konsole,1336769
└─bash,1336776
└─sleep,1492051 999
[user@testserver]$ nohup sh STAT_WEEKEND2.sh &
[1] 29189
[user@testserver]$ nohup: ignoring input and appending output to `nohup.out'
[user@testserver]$ jobs -l
[1]+ 29189 Running nohup sh STAT_WEEKEND2.sh &
[user@testserver]$ date
Sun Nov 6 12:00:18 +04 2022
[user@testserver]$ ps -ef |grep nohup
user 2367 12244 0 12:04 pts/3 00:00:00 grep nohup
[user@testserver]$ pstree -ap 29189
sh,29189 STAT_WEEKEND2.sh
└─sqlplus,29192 -s \040\040\040\040\040\040
└─oracle,29193 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[user@testserver]$ ps -ef|grep -i STAT_WEEKEND2.sh
user 12577 12244 0 12:06 pts/3 00:00:00 grep -i STAT_WEEKEND2.sh
user 29189 12244 0 11:58 pts/3 00:00:00 sh STAT_WEEKEND2.sh
[user@testserver]$
kill -9 29189
kill -9 29193
to kill a job in AIX enviroment
---------------
$proctree <pid> ---->it shows threads under that process
kill the last one thread
ps -ef|grep -i WEEKEND
proctree 44630986
server:TESTDB:/home/oracle $proctree 44630986
17367190 /opt/agent/ctm/exe/p_ctmag
44630986 /bin/ksh -x /prod/jobs_exec/umld6671.sh
25756450 /bin/ksh /prod/scripts/runsql.sh ml7001u
24904314 sqlplus @/prod/sql/ml7001u.sql
21693544 oracleTESTDB (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
server:TESTDB:/home/oracle $
kill -9 <pid> kill -9 21693544
Saving the PID While Starting the Process
When we started the process using nohup above, the output of the command execution was [1] 20634. The number within the brackets, 1, is the job ID. The number after the job ID, 20634, is the PID of the process spawned by executing the sleep infinity command.
We can use this PID later to check or control the process. For example, we can check whether the process is still running:
$ ps -ef -o pid | grep 20634
20634
Using the ps Command
If we forget to note the PID of the process while starting it with nohup, we can get it later using ps. Let’s search the command sleep infinity in the existing processes:
$ ps -ef | grep "sleep infinity"
alice 21443 1 0 00:27 pts/0 00:00:00 sleep infinity
alice 21673 4672 0 00:27 pts/0 00:00:00 grep sleep infinity
Copy
Here, we filtered the output of ps using grep. We didn’t use the term “nohup” in the search as it doesn’t appear in the output of the ps command.
Since the grep “sleep infinity” command within the pipe is also listed in the output, we can filter it further using grep:
$ ps -ef | grep "sleep infinity" | grep -v grep
alice 21443 1 0 00:27 pts/0 00:00:00 sleep infinity
Copy
Finally, we can print only the PID of the process using the awk command:
$ ps -ef | grep "sleep infinity" | grep -v grep | awk '{print $2}'
21443
The Below SQL is used to identify the snapshots when a particular SQL was running
SELECT dhs.sql_id,
dsn.snap_id-1 begin_snap,
dsn.snap_id end_snap,
TO_CHAR(dsn.begin_interval_time,'DD-MON-YYYY HH24:MI:SS') begin_time,
TO_CHAR(dsn.end_interval_time,'DD-MON-YYYY HH24:MI:SS') end_time,
ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs
FROM dba_hist_sqlstat dhs,
v$database d,
v$instance i,
dba_hist_snapshot dsn
WHERE dhs.dbid = d.dbid
AND dsn.snap_id = dhs.snap_id
AND dsn.dbid = dhs.dbid
AND dsn.instance_number = dhs.instance_number
AND dhs.instance_number = i.instance_number
AND dhs.sql_id = '&sql_id'
GROUP BY dhs.sql_id, dsn.snap_id-1, dsn.snap_id, dsn.begin_interval_time, dsn.end_interval_time
ORDER BY dsn.snap_id;
Top users,
program,modules,machine,sql_id
SELECT * FROM (
SELECT count(*) AS count,USERNAME
program, module, machine, sql_id
FROM
SYS.DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS
WHERE
DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID
AND session_type='FOREGROUND' and
snap_id between 96067 and 96073
GROUP BY USERNAME, program, module, machine,
sql_id
ORDER BY count(*) DESC
)
WHERE rownum <= 20
or
SELECT * FROM (
SELECT count(*) AS count,USERNAME
program, module, machine, sql_id
FROM
SYS.DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS
WHERE
DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID
AND session_type='FOREGROUND'
AND
sample_time > sysdate-60/1440
AND sample_time < sysdate
--AND event = 'library cache: mutex
X'
GROUP BY USERNAME, program, module,
machine, sql_id
ORDER BY count(*) DESC
)
WHERE rownum <= 20
-- Top temp segments given than a threshold
SELECT * FROM (
SELECT count(*) AS count,username,
program, module, machine, sql_id,sum(temp_space_allocated)
FROM
SYS.DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS
WHERE
DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID
AND sample_time > sysdate-60/1440
AND sample_time < sysdate
AND temp_space_allocated >
100*1024*1024
GROUP BY USERNAME, program, module,
machine, sql_id
ORDER BY count(*) DESC
)
WHERE rownum <= 20
/
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT
b.username,b.sid,b.serial#,username,b.status,b.sql_id,to_char(b.logon_time,'dd-mon:hh24:mi'),b.machine,b.module
FROM v$sqltext a,
v$session b
WHERE a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.sql_id = 'dt7cud8uwzstp';
select sid,serial#,username,program,machine,module
from v$session where sql_id='dt7cud8uwzstp';
select count(1),event
from v$session group by event;
select
s.sql_id,s.sid,s.serial#,s.username,s.event,to_char(s.logon_time,'dd-mon:hh24:mi') from v$session s, v$process p
where p.addr=s.paddr and
p.spid
in(4260298,41418786,5570940,3146020,1507632,4587808,4915642,3080460,2425340);
SELECT s.sid, s.serial#,
s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY
HH24:MI:SS')
FROM v$session s, v$process
p WHERE s.paddr = p.addr AND p.spid in('33423802', '34799798','23789680');
SELECT p.program,s.module, p.spid, s.saddr,
s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.logon_time,
s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY HH24:MI:SS') FROM v$session s, v$process p WHERE s.paddr
= p.addr AND p.spid in('18705', '18704','18699','18681');
SELECT s.sid, s.serial#,
s.username, s.osuser, s.status,LAST_CALL_ET,TO_CHAR(LOGON_TIME,'DD-MON-YY
HH24:MI:SS')
FROM v$session s,
v$process p WHERE s.paddr = p.addr AND p.spid in('18705',
'18704','18699','18681');
select * from
table(dbms_xplan.display_awr('d7dpgqhquy6c4',NULL,NULL,'ALL'));
select * from
table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));
select * from table(dbms_xplan.display_cursor('d7dpgqhquy6c4',null,'AdVanced
ALLSTATS LAST'));
select * from
table(dbms_xplan.display_cursor('&sql_id',null,'AdVanced ALLSTATS LAST'));
select * from table(dbms_xplan.display_awr('&sql_id',null,null,'advanced'));
select * from
table(dbms_xplan.display_awr('4jgxkz2msmvgn',null,null,'advanced'));
select * from table(dbms_xplan.display_awr('&SQL_ID',NULL,NULL,'ALL'));
select
substr(value_string,1,40) from v$sql_bind_capture where sql_id='dup0ph9mdm3fc';
select
substr(value_string,1,40) from DBA_HIST_SQLBIND
where sql_id='dup0ph9mdm3fc';
select substr(value_string,1,40)
from DBA_HIST_SQLBIND where
sql_id='dup0ph9mdm3fc';
select
sn.END_INTERVAL_TIME,sb.NAME,sb.VALUE_STRING from DBA_HIST_SQLBIND
sb,DBA_HIST_SNAPSHOT sn where sb.sql_id='dup0ph9mdm3fc' and
sb.WAS_CAPTURED='YES' and sn.snap_id=sb.snap_id order by sb.snap_id,sb.NAME;
SELECT
a.sql_text,b.name,b.position,b.datatype_string,b.value_string FROM v$sql_bind_capture b,v$sqlarea a WHERE b.sql_id = 'dup0ph9mdm3fc' AND
b.sql_id = a.sql_id;
SELECT a.sql_text FROM v$sqlarea a WHERE a.sql_id = 'dup0ph9mdm3fc' ;
select
sql_text,a.sql_id,,sid,serial#,username from v$sqlarea a, v$session v where sid
in ( ) and a.sql_id=v.sql_id
select sid,status from
v$session
select
s.sid,s.serial#,s.username,to_char(s.logon_time,'dd-mon:hh24:mi'),s.sql_hash_value,s.prev_hash_value from v$session s, v$process p
where p.addr=s.paddr and
p.spid in (17629,23767,27834);
select
sql_text,address,hash_value,sid,serial#,username from v$sqlarea a, vv$session b
where hash_value='555131536' and a.address=b.sql_address;
Standby database Healhchek
1) To check standby database
select
name,CONTROLFILE_TYPE,NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,LOG_MODE
from v$database;
SQL> select
process,status,sequence# from v$managed_standby;
ps
-ef |grep mrp
select
inst_id,process,thread#,sequence#,blocks,status from gv$managed_standby where
process like %MRP%;
2) To start recover process
startup
alter database recover managed
standby database disconnect using current logfile;
or
alter
database recover managed standby database disconnect from session;
or
select
name,CONTROLFILE_TYPE,NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,LOG_MODE
from v$database;
startup mount
alter database recover managed
standby database cancel;
alter database recover managed
standby database disconnect from session;
3)
To stop database
alter database recover managed
standby database cancel
shutdown immediate;
4) To check applied log sync
detail
select
thread#,max(sequence#) from v$archived_log where applied='YES' group by
thread#;
Select
THREAD#,
LOW_SEQUENCE#,
HIGH_SEQUENCE# From
V$ARCHIVE_GAP;
Or
SELECT ARCH.THREAD#
"Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# -
APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD#
,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE#
FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT
THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# =
APPL.THREAD#
Health Check For RAC DATABASE
1) Check CRS status
crsctl
config has
crsctl
status res –t
crsctl stat res -t –init
srvctl config asm
srvctl config
database -d <database name>
oifcfg iflist
-p –n
ifconfig –a
cat /etc/hosts
olsnodes -t -s
srvctl
config scan
ps
-ef|grep d.bin
GRID_HOME/bin/oifcfg
getif
crsctl
check crs
crsctl
check has
crsctl config has
crsctl query has
releaseversion
crsctl query has
softwareversion
2)
Database Status (Green/Red)
ps -ef|grep pmon
select name,open_mode from gv$database;
select
count(1),inst_id ,status from gv$session group by inst_id ,status;
3)Listener
Status (Green/Red)
ps -ef|grep tns
4)Archivelog
filesystem
column USED_GB format
999,990.999
column USED% format 990.99
column RECLAIM_GB format
999,990.999
column RECLAIMABLE%
format 990.99
column LIMIT_GB format
999,990.999
select frau.file_type as
type,
frau.percent_space_used/100
* rfd.space_limit /1024/1024 "USED_MB",
frau.percent_space_used
"USED%",
frau.percent_space_reclaimable
"RECLAIMABLE%",
frau.percent_space_reclaimable/100
* rfd.space_limit /1024/1024 "RECLAIM_MB",
frau.number_of_files
"FILES#"
from
v$flash_recovery_area_usage frau,v$recovery_file_dest rfd order by file_type
5)
Data Filesystem space/Disk Group
set lines 200 pages 200
SELECT g.group_number
"Group"
, g.name "Group
Name"
, g.state
"State"
, g.type
"Type"
, g.total_mb/1024
"Total GB"
, g.free_mb/1024
"Free GB"
,
100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb)
"Imbalance"
,
100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"
,
100*(min(d.free_mb/d.total_mb)) "MinFree"
,
100*(max(d.free_mb/d.total_mb)) "MaxFree"
, count(*)
"DiskCnt"
FROM v$asm_disk d,
v$asm_diskgroup g
WHERE d.group_number =
g.group_number and
d.group_number <>
0 and
d.state = 'NORMAL' and
d.mount_status =
'CACHED'
GROUP BY g.group_number,
g.name, g.state, g.type, g.total_mb, g.free_mb
ORDER BY 1;
6)
Check the disk in ASM Diskgroup
set lines 999;
col diskgroup for a15
col diskname for a15
col path for a35
select a.name
DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB,
b.free_mb,b.path,b.header_status
from v$asm_disk b,
v$asm_diskgroup a
where a.group_number (+)
=b.group_number
order by
b.group_number,b.name;
select substr(name,1,10)
name,substr(path,1,20) path, REDUNDANCY, TOTAL_MB, os_mb, free_mb from
V$ASM_DISK;
7)
Clusterware log location
clusterware alert log (<GRID_HOME>/log/<hostname>/alert<hostname>.log)
crsd log
(<GRID_HOME>/log/<hostname>/crsd/crsd.log)
cssd log ( $GI_HOME/log/nodename/cssd
location)
