EasyReliableDBA: Oracle Database Performance Monitor and Health Check commands step by step

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>

$ 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');

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;

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;

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

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 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;

alter database recover managed standby database disconnect from session;

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;

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)

EasyReliableDBA

Wednesday, 4 May 2022

Oracle Database Performance Monitor and Health Check commands step by step

No comments:

Post a Comment

Search This Blog