Oracle DBA Daily Health Check Activity
Oracle DBA performs below activities as part of
DB Health Check.
1. Check
that all instances and listener are up if require check remote connectivity of
database.
cd /home/oracle
ls -ltr *.env
.
./.profile_easydb
or
$ ls -lrt *.env
-rw-r--r-- 1 oracle
oinstall 1823 Apr 18
2021 easydb.env
. easydb.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;
set line 150
col HOST_NAME for a15
col "UP TIME" for a25
col db_role for a20
col STATUS for a10
show user
select name,INSTANCE_NAME,OPEN_MODE,HOST_NAME,DATABASE_ROLE db_role, DATABASE_STATUS,STATUS, logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "UP TIME"from v$database,v$instance;
select instance_name,HOST_NAME,status from
gv$instance order by 1;
col "Datafiles Status" for a40
select distinct status "Datafiles
Status" from v$datafile;
set pagesize 999
break on inst_id
compute sum of cnt on inst_id
select inst_id,status,count(*)as cnt from
gv$session group by inst_id,status order by inst_id,status;
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;
select
sid,serial#,osuser,username,program,status,SQl_id from v$session where
sid=&sid;
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
SELECT
a.sql_text,b.sid,b.serial#,event,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 = '&sql_id';
SQL> select sid,serial#,program,sql_id,status
from v$session where sid=1359;
SID SERIAL# PROGRAM
SQL_ID
---------- ----------
------------------------------------------------ -------------
1359 44249 Toad.exe
SQL>
select sid,serial#,program,sql_id,event from v$session where sql_id='545wnzdh93xf5';
select select
sid,serial#,program,sql_id,status,machine,module,event from v$session where
sql_id='545wnzdh93xf5';
select sql_text from v$sqltext where sql_id='&sql_id';
select SQL_TEXT from v$sqlarea where SQL_ID='545wnzdh93xf5';
select
sql_id,rows_processed,upper(sql_fulltext) from v$sql where upper(sql_fulltext)
like '%UPDATE%AP_DW_GL_BALANCES%';
select
sql_id,rows_processed,upper(sql_fulltext) from v$sqlarea where
hash_value=&hash
select
sql_id,rows_processed,upper(sql_fulltext) from v$sqlarea where
sql_id='&sql_id';
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;
select
count(1) ,event from v$session group by event;
$ cat bskill.sql
select
'alter system disconnect session
'||chr(39)||sid||','||serial#||chr(39)||' immediate;' from v$session
where sid in (select blocking_session from
v$session where blocking_session_status='VALID' and state='WAITING' and
seconds_in_wait > 120)
and status='INACTIVE' and last_call_et > 300
order by last_call_et desc
/
$
$ cat
bskill2.sql
select
'alter system disconnect session '||chr(39)||sid||','||serial#||chr(39)||'
immediate;' from v$session
where sid in (select blocking_session from
v$session where blocking_session_status='VALID' and state='WAITING' and
seconds_in_wait > 120)
and status='INACTIVE' and last_call_et > 240
order by last_call_et desc
/
$
5. Check
is there any dbms jobs running & check the status of the same
set line 190 pages
1000
col JOB_ACTION for a50
col REPEAT_INTERVAL
for a30
col OWNER for a15
col JOB_NAME for a15
col JOB_ACTION for a20
col START_DATE for a20
col LAST_START_DATE
for a20
col LAST_RUN_DURATION
for a30
col NEXT_RUN_DATE for
a35
select
JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE
from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';
set linesize 200
column job_name format
a30
column log_date format
a40
column
actual_start_date format a40
column run_duration
format a60
SELECT job_name,
log_date,REQ_START_DATE, status, actual_start_date, run_duration FROM
dba_scheduler_job_run_details where JOB_NAME like '%STAT%' order by log_date;
Or
col JOB_ACTION for a20
col START_DATE for a20
col LAST_START_DATE for a20
col LAST_RUN_DURATION for a20
col NEXT_RUN_DATE for a30
select
JOB_NAME,JOB_ACTION,LAST_START_DATE,LAST_RUN_DURATION from dba_scheduler_jobs
where owner='SYS' and JOB_NAME like '%STAT%';
Or
col JOB_ACTION for a20
col START_DATE for a20
col LAST_START_DATE for a20
col LAST_RUN_DURATION for a20
col NEXT_RUN_DATE for a30
select JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE
from dba_scheduler_jobs where owner='SYS' and JOB_NAME like '%STAT%';
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 (log_date) =
(sysdate -1) ORDER BY LOG_DATE DESC;
SELECT job_name, log_date, status,
actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details;
To check currently running
SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs;
select sid,serial#,status,inst_id,sql_id from gv$session where sid in ( select sid from dba_jobs_running)
select job||' '||schema_user||'
'||Broken||' '||failures||' '||what||' '||last_date||' '||last_sec from
dba_jobs;
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
owner,job_name,logging_level,repeat_interval,enabled,state,run_count,last_start_date,next_run_date
from dba_scheduler_jobs order by owner,enabled
/
select job_name,owner,start_date,next_run_date,STATE,enabled
from dba_scheduler_jobs
or
select
job_name,owner,job_type,program_name,schedule_name,job_class,STATE from
dba_scheduler_jobs where job_name like
'%GATHER%';
/
select job_name, enabled from DBA_SCHEDULER_JOBS where job_name like '%WEEKEND1%';
select
job_name,owner, enabled from DBA_SCHEDULER_JOBS where JOB_NAME like
'%STATS_GATHER_WEEKEND%';
set line 190 pages 1000
col JOB_ACTION for a50
col REPEAT_INTERVAL for a30
col OWNER for a15
col JOB_NAME for a15
col JOB_ACTION for a20
col START_DATE for a20
col LAST_START_DATE for a20
col LAST_RUN_DURATION for a30
col NEXT_RUN_DATE for a35
select
JOB_NAME,JOB_ACTION,REPEAT_INTERVAL,LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE
from dba_scheduler_jobs
where owner='SYS' and JOB_NAME like '%WEEKEND1%';
JOB_NAME
JOB_ACTION
REPEAT_INTERVAL
LAST_START_DATE LAST_RUN_DURATION NEXT_RUN_DATE
--------------- --------------------
------------------------------ --------------------
------------------------------ -----------------------------------
STATS_GATHER_WE begin sys.proc_stats FREQ=DAILY;
BYDAY=FRI; BYHOUR= 22-JUL-22 06.15.00.1 +000000000 15:18:38.147326 29-JUL-22 06.15.00.200000 PM +04:00
EKEND1
_gather_weekend1; en 18 ;BYMINUTE=15 68978 PM +04:00
d;
SELECT job_name, session_id, running_instance,
elapsed_time, cpu_used FROM dba_scheduler_running_jobs;
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
'%WEEKEND1%' and (log_date) = (sysdate -1) ORDER BY LOG_DATE DESC;
SQL> select job_name from DBA_SCHEDULER_JOBS
where job_name like '%WEEKEND1%';
JOB_NAME
------------------------------
STATS_GATHER_WEEKEND1
SQL> execute dbms_scheduler.disable('SYS.STATS_GATHER_WEEKEND1');
PL/SQL procedure successfully completed.
SQL>
select job_name,owner, enabled from DBA_SCHEDULER_JOBS where JOB_NAME
like '%STATS_GATHER_WEEKEND%';
JOB_NAME OWNER ENABL
------------------------------
------------------------------ -----
STATS_GATHER_WEEKEND4 SYS FALSE
STATS_GATHER_WEEKEND3 SYS TRUE
STATS_GATHER_WEEKEND2 SYS FALSE
STATS_GATHER_WEEKEND_POSTEOD SYS FALSE
STATS_GATHER_WEEKEND1 SYS FALSE
SQL>
6. How much redo generated per day
set linesize
200 pagesize 1000
column day format a3
column total format 9999
column h00 format 999
column h01 format 999
column h02 format 999
column h03 format 999
column h04 format 999
column h04 format 999
column h05 format 999
column h06 format 999
column h07 format 999
column h08 format 999
column h09 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column h24 format 999
break on report
compute max of "total" on report
compute max of "h00" on report
compute max of "h01" on report
compute max of "h02" on report
compute max of "h03" on report
compute max of "h04" on report
compute max of "h05" on report
compute max of "h06" on report
compute max of "h07" on report
compute max of "h08" on report
compute max of "h09" on report
compute max of "h10" on report
compute max of "h11" on report
compute max of "h12" on report
compute max of "h13" on report
compute max of "h14" on report
compute max of "h15" on report
compute max of "h16" on report
compute max of "h17" on report
compute max of "h18" on report
compute max of "h19" on report
compute max of "h20" on report
compute max of "h21" on report
compute max of "h22" on report
compute max of "h23" on report
compute sum of NUM on report
compute sum of GB on report
compute sum of MB on report
compute sum of KB on report
REM
Script to Report the Redo Log Switch History
alter session set nls_date_format='DD MON
YYYY';
select thread#, trunc(completion_time) as
"date", to_char(completion_time,'Dy') as "Day", count(1) as
"total",
sum(decode(to_char(completion_time,'HH24'),'00',1,0))
as "h00",
sum(decode(to_char(completion_time,'HH24'),'01',1,0))
as "h01",
sum(decode(to_char(completion_time,'HH24'),'02',1,0))
as "h02",
sum(decode(to_char(completion_time,'HH24'),'03',1,0))
as "h03",
sum(decode(to_char(completion_time,'HH24'),'04',1,0))
as "h04",
sum(decode(to_char(completion_time,'HH24'),'05',1,0))
as "h05",
sum(decode(to_char(completion_time,'HH24'),'06',1,0))
as "h06",
sum(decode(to_char(completion_time,'HH24'),'07',1,0))
as "h07",
sum(decode(to_char(completion_time,'HH24'),'08',1,0))
as "h08",
sum(decode(to_char(completion_time,'HH24'),'09',1,0))
as "h09",
sum(decode(to_char(completion_time,'HH24'),'10',1,0))
as "h10",
sum(decode(to_char(completion_time,'HH24'),'11',1,0))
as "h11",
sum(decode(to_char(completion_time,'HH24'),'12',1,0))
as "h12",
sum(decode(to_char(completion_time,'HH24'),'13',1,0))
as "h13",
sum(decode(to_char(completion_time,'HH24'),'14',1,0))
as "h14",
sum(decode(to_char(completion_time,'HH24'),'15',1,0))
as "h15",
sum(decode(to_char(completion_time,'HH24'),'16',1,0))
as "h16",
sum(decode(to_char(completion_time,'HH24'),'17',1,0))
as "h17",
sum(decode(to_char(completion_time,'HH24'),'18',1,0))
as "h18",
sum(decode(to_char(completion_time,'HH24'),'19',1,0))
as "h19",
sum(decode(to_char(completion_time,'HH24'),'20',1,0))
as "h20",
sum(decode(to_char(completion_time,'HH24'),'21',1,0))
as "h21",
sum(decode(to_char(completion_time,'HH24'),'22',1,0))
as "h22",
sum(decode(to_char(completion_time,'HH24'),'23',1,0))
as "h23"
from
v$archived_log
where first_time > trunc(sysdate-10)
and dest_id = (select dest_id from
V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#, trunc(completion_time),
to_char(completion_time, 'Dy') order by 2,1;
REM Script to calculate the archive log size generated
per day for each Instances.
select THREAD#,
trunc(completion_time) as "DATE"
, count(1) num
, trunc(sum(blocks*block_size)/1024/1024/1024)
as GB
,
trunc(sum(blocks*block_size)/1024/1024) as MB
,
sum(blocks*block_size)/1024 as KB
from v$archived_log
where first_time >
trunc(sysdate-10)
and dest_id = (select
dest_id from V$ARCHIVE_DEST_STATUS where status='VALID' and type='LOCAL')
group by thread#,
trunc(completion_time)
order by 2,1
;
alter
session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select
sysdate from dual;
set long 99999999
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'));
per
day archive generation
SQL>
select to_char(first_time,'dd/mm/yyyy') "FIRST_DATE", count(*),
sum(round(blocks*block_size/1024/1024)) "SIZE IN MB"
from
v$archived_log WHERE TRUNC(first_time) > sysdate-30
group
by to_char(first_time,'dd/mm/yyyy')
order
by 1 asc;
2
3 4
FIRST_DATE COUNT(*) SIZE IN MB
----------
---------- ----------
01/08/2022 440
74832
02/08/2022 372
56872
03/08/2022 346
48242
SQL>
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 ;
column
member format a50 wrap heading 'Member'
column
group# format 99990 heading 'Group#'
column
status format a10 wrap heading 'Status'
SELECT a.group#, a.member, b.bytes/1024/1024 FROM
v$logfile a, v$log b WHERE a.group# = b.group# order by 1;
select group#,member from
v$logfile order by 1;
select
* from v$log;
select group#,members,status,bytes/1024/1024 as mb 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 ;
select SQL_TEXT from v$sqlarea where
SQL_ID='545wnzdh93xf5';
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,terminal,program,event,status,SQl_id from v$session where sid=&sid;
select
sid,serial#,osuser,username,program,terminal,event,status,SECONDS_IN_WAIT,ACTION,module,SQl_id
from v$session where sid=&sid;
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.sql_id = '&sql_id';
select sql_text from v$sqltext where
sql_id='&sql_id';
set lines 9999
set pages 9999
select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid order by piece;
COLUMN Sample_Time FOR A12
COLUMN username FOR A20
COLUMN sql_text FOR A40
COLUMN program FOR A40
COLUMN module FOR A40
SELECT
to_char(sample_time,'DD Mon HH24:MI') as Sample_Time,
u.username,h.program, h.module,s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time BETWEEN TO_DATE('2015-07-29 14:30',
'yyyy-mm-dd hh24:mi') AND TO_DATE('2015-07-29 15:00', 'yyyy-mm-dd hh24:mi')
AND h.user_id=u.user_id AND h.sql_id = s.sql_iD AND
session_id='2087'
ORDER BY 1
/
688 was blocking 2087
set linesize 300
column username format a20
column program format a20
column module format a30
column event format a30
select to_char(h.sample_time,
'dd-mon-YYYY:HH24:MI:SS') TIME,h.session_id,u.username,decode(h.session_state,
'WAITING' ,h.event, h.session_state) STATE,h.sql_id,h.blocking_session BLOCKER
from
DBA_HIST_ACTIVE_SESS_HISTORY h,
dba_users u where u.user_id = h.user_id and sample_time BETWEEN
TO_DATE('2015-07-29 14:30', 'yyyy-mm-dd hh24:mi') AND TO_DATE('2015-07-29
15:00', 'yyyy-mm-dd hh24:mi') and h.blocking_session=688
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 max
show parameter pga
show parameter sga
show parameter shared pool
show parameter db_cache_size
show parameter memory
SQL> show parameter use_large_pages
SQL> show parameter db_cache_advice;
SQL> show parameter pool;
/* 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;
SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC;
/* PGA */
select
sum(PGA_ALLOC_MEM)/1024/1024 "Total PGA Allocated (Mb)" from
v$process p, v$session s where p.addr = s.paddr
/
SQL> set lines 500
col c1 heading 'Program|Name' format a30
col c2 heading 'PGA|Used|Memory' format 999,999,999
col c3 heading 'PGA|Allocated|Memory' format 999,999,999
col c4 heading 'PGA|Maximum|Memory' format 999,999,999
SQL> select
program c1,pga_used_mem c2,
pga_alloc_mem c3,
pga_max_mem c4,pid,sosid
from
v$process
FETCH FIRST 15 ROWS ONLY;
order by c4 desc;
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 300
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
/
Or
SELECT 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
df -hP|grep <database_name> ---->Linux
bdf|grep <database_name> ---->HP-UX
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/60 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';
SELECT * FROM v$instance;
SELECT NAME FROM V$CONTROLFILE ;
SELECT * FROM v$recover_file;
SELECT * FROM v$recovery_log;
select distinct status from v$backup;
select distinct(status) from v$datafile;
select distinct (to_char(checkpoint_change#)) from
v$datafile;
select distinct (to_char(checkpoint_change#)) from
v$datafile_header;
11) Check DB link
col OWNER for a15
col USERNAME for a25
col DB_LINK for a25
col HOST for a20
set lines 300
select * from dba_db_links;
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;
CARDS_LARGE_INDX_NEW
==========
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;
!df -hk /edw_data5/edwdb01
alter tablespace CARDS_LARGE_INDX_NEW add datafile
'/edw_data5/edwdb01/cards_large_indx_new_24.dbf' size 30720m
alter tablespace CARDS_LARGE_INDX_NEW add datafile '/edw_data5/edwdb01/cards_large_indx_new_24.dbf' size 5000m;
alter database datafile '/edw_data5/edwdb01/cards_large_indx_new_24.dbf' resize 30720m;
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
df
-h | grep data
13.
Check all crons and Oracle Jobs are completed without
any error
crontab -l
14.
To check Database size
-------------------------------------To check Database
Growth Trend------------------
select to_char(CREATION_TIME,'RRRR') year,
to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB from
v$datafile group by
to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') order by 1
asc, 2 desc;
select to_char(creation_time, 'RRRR Month')
"Month",
sum(bytes)/1024/1024/1024 "Growth in gig"
from
sys.v_$datafile
where
creation_time > SYSDATE-370
group by
to_char(creation_time, 'RRRR Month');
-- get Oracle database size from dba_data_files:
select
"Reserved_Space(GB)",
"Reserved_Space(GB)" - "Free_Space(GB)"
"Used_Space(GB)","Free_Space(GB)"
from( select (select sum(bytes/(1024*1024*1024))
from dba_data_files) "Reserved_Space(GB)", (select
sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)" from dual);
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;
select name from v$datafile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile;
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
or
$
cat status.sql
SELECT
SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2)
"%_COMPLETE" FROM gV$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND
OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR != TOTALWORK order
by 6 desc
/
Temp usage / Rollback segment/PGA
usage:
We can get information of temporary tablespace
usage details with the help of below query:
Set lines 1000
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M'
"SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
select st.sid "SID", sn.name
"TYPE",
ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn
where st.statistic# = sn.statistic#
and sid in
(select sid from v$session where username like
UPPER('&user'))
and upper(sn.name) like '%PGA%'
order by st.sid, st.value desc;
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
18) To check Audit
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ -----------
------------------------------
audit_file_dest string /oracle/app/orasbm/product/11.
2.0/dbhome_1/rdbms/audit
audit_sys_operations boolean TRUE
audit_syslog_level string LOCAL1.INFO
audit_trail string OS
SQL>
There are three
levels that can be audited:
Statement level
Auditing will be done at statement level.
Statements that can be audited are found in
STMT_AUDIT_OPTION_MAP.
SQL> audit table by scott;
Audit records can be found in DBA_STMT_AUDIT_OPTS.
SQL> select * from DBA_STMT_AUDIT_OPTS;
select *
from dba_stmt_audit_opts where AUDIT_OPTION in ('CREATE SESSION', 'CREATE
USER', 'ALTER USER', 'DROP USER', 'CREATE PROFILE', 'ALTER PROFILE', 'DROP
PROFILE', 'ALTER SYSTEM', 'ALTER ANY TABLE', 'CREATE ANY TABLE', 'DROP ANY
TABLE', 'CREATE ANY PROCEDURE', 'DROP ANY PROCEDURE', 'ALTER ANY PROCEDURE',
'CREATE EXTERNAL JOB', 'CREATE ANY JOB', 'CREATE ANY LIBRARY', 'ALTER
DATABASE', 'GRANT ANY PRIVILEGE','GRANT ANY OBJECT PRIVILEGE','GRANT ANY ROLE')
order by AUDIT_OPTION ;
Object level
Auditing will be done at object level.
These objects can be audited: tables, views, sequences,
packages, stored procedures and stored functions.
SQL> audit insert, update, delete on scott.emp by hr;
Audit records can be found in DBA_OBJ_AUDIT_OPTS.
SQL> select * from DBA_OBJ_AUDIT_OPTS;
Privilege level
Auditing will be done at privilege level.
All system privileges that are found in
SYSTEM_PRIVILEGE_MAP can be audited.
SQL> audit create tablespace, alter tablespace by all;
Specify ALL PRIVILEGES to audit all system privileges.
Audit records can be found in DBA_PRIV_AUDIT_OPTS.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
1. NO AUDIT Enable with table name in ermdb01
database.
select * from
dba_obj_audit_opts where owner <>’SYS’;
select
username,account_status,profile from dba_username like ‘%BZ%’ and
account_status=’OPEN’;
select * from dba_sys_privs where PRIVILEGE='SELECT ANY TABLE'
--------Identify who is locking the user==============
SELECT
username,userhost,extended_timestamp,owner,obj_name,action_name FROM
dba_audit_trail where username like '%FLEXIBLELICENSE%';
and rownum <10 and returncode in (28000) and
trunc(TIMESTAMP) >= '14/JAN/2022'
order by timestamp desc;
select os_username, username, owner,
obj_name,action_name,to_char(timestamp,'DD-MON-YY HH24:MI:SS') time_stamp ,
decode(returncode,0,'Success', 'failed') from dba_audit_trail where action_name
= 'CCSOWNER';
select os_username, username, owner,
obj_name,action_name,
to_char(timestamp,'DD-MON-YY HH24:MI:SS')
time_stamp , decode(returncode,0,'Success', 'failed') from dba_audit_trail
where
action_name = 'CCSOWNER';
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
Memory Utilization
----------------------------
free: free is a standard command to check memory utilization in Linux.
awk: awk is a powerful command which is specialized for textual data manipulation.
FNR == 2: It gives the total number of records for each input file. Basically it’s used to select the given line (Here, it chooses the line number 2).
NR == 2: It gives the total number of records processed. Basically it’s used to filter the given line (Here, it chooses the line number 2)..
$3/$2*100: It divides column 2 with column 3 and it multiply the results with 100.
printf: It used to format and print data.
%.2f%: By default it prints floating point numbers with 6 decimal places. Use the following format to limit decimal places.
To check memory utilization in linux
free -t|awk 'FNR == 2 {print "Current Memory Utilization is: " $3/$2*100}'
free
free
| grep Mem | awk '{print $3/$2 * 100.0}'
free
| grep Mem | awk '{print $4/$2 * 100.0}'
CPU utilization
Top
select (sga+pga)/1024/1024 as "sga_pga"
from
(select sum(value) sga from v$sga),
(select sum(pga_used_mem) pga from v$process);
[orasbm@edwdb001 ~]$ cat rollback.sql
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM
v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr
= t.ses_addr
AND
t.xidusn = r.usn
AND
rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/
[orasbm@edwdb001 ~]$
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');
proctree 21496252 --aix
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;
selec
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' ;
Analyze a particular SQL ID and
see the trends for the past day
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;
Analyze a particular SQL ID and see the trends for
the past day
select
s.snap_id,
to_char(s.begin_interval_time,'HH24:MI') c1,
sql.executions_delta c2,
sql.buffer_gets_delta c3,
sql.disk_reads_delta c4,
sql.iowait_delta c5,
sql.cpu_time_delta c6,
sql.elapsed_time_delta c7
from
dba_hist_sqlstat sql,
dba_hist_snapshot s
where
s.snap_id =
sql.snap_id
and
s.begin_interval_time > sysdate -5
and
sql.sql_id='&sqlid'
order by c7
/
Do we have multiple plan hash
values for the same SQL ID – in that case may be changed plan is causing bad
performance
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where
SQL_ID in (
'&sqlid')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
Standby database Healhchek
1) To check standby database
select
name,CONTROLFILE_TYPE,NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,LOG_MODE
from v$database;
select
error from v$archive_dest_status where dest_id=2;
SELECT database_role
role, name, db_unique_name, platform_id, open_mode, log_mode, flashback_on,
protection_mode, protection_level FROM v$database;
SELECT
dbid, name, cdb 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%';
show parameter ARCHIVE_DEST
archive log list
select * from v$diag_info;
SELECT DEST_ID "ID",STATUS "DB_status",DESTINATION "Archive_dest",ERROR "Error"
FROM V$ARCHIVE_DEST;
SHOW PARAMETER JOB_QUEUE_PROCESSES;
Check the the standby logs are being used by running following
query :
|
set lines 155 pages 9999 col
thread# for 9999990 col
sequence# for 999999990 col
grp for 990 col
fnm for a50 head "File
Name" col
"Fisrt SCN Number" for 999999999999990 select a.thread# ,a.sequence#,a.group#
grp ,
a.bytes/1024/1024 Size_MB ,a.status,a.archived ,a.first_change#
"First SCN Number" ,to_char(FIRST_TIME,'DD-Mon-RR
HH24:MI:SS') "First SCN Time" ,to_char(LAST_TIME,'DD-Mon-RR
HH24:MI:SS') "Last SCN Time" from v$standby_log
a order by 1,2,3,4 / |
https://jhdba.wordpress.com/tag/vstandby_log/
https://docs.oracle.com/cd/B10500_01/server.920/a96653/troubleshooting.htm
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;
SELECT DESTINATION, STATUS, ERROR FROM
V$ARCHIVE_DEST WHERE DEST_ID=2;
SELECT DESTINATION, STATUS, ERROR FROM
V$ARCHIVE_DEST WHERE DEST_ID=3;
select message, timestamp from
v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
select group#,thread#,bytes/1024/1024 as
mbytes,used,archived,status
from v$standby_log
To check recovery Rate
set linesize 400
col Values for a65
col Recover_start for a21
select to_char(START_TIME,'dd.mm.yyyy
hh24:mi:ss') "Recover_start",to_char(item)||' = '||to_char(sofar)||'
'||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi')
"Values" from v$recovery_progress
where start_time=(select max(start_time) from
v$recovery_progress);
SQL> alter session set
nls_date_format='YYYY-MON-DD HH24:MI:SS';
SQL> select message, timestamp
from v$dataguard_status where severity
in ('Error','Fatal')
order by timestamp;
startup mount
alter database recover managed
standby database cancel;
alter database recover managed
standby database disconnect from session;
or
alter database recover managed
standby database parallel 8 disconnect ;
The managed recovery
process (MRP) applies archived redo log files to
the physical standby database, and automatically determines the
optimal number of parallel recovery processes at the time it starts.
The number of parallel recovery slaves spawned is based on the number
of CPUs available on the standby server."
alter database recover managed standby database parallel 2 using current logfile disconnect;
ALTER DATABASE RECOVER MANAGED STANDBY
DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION NODELAY PARALLEL 2;
In
SBM
To
start standby database
$
cat new_batchhub.env
export
ORACLE_SID=batchhubs
export
ORACLE_HOME=/oracle01/app/orasbm/product/11.2.0.4/dbhome_1
export
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export
PATH=$ORACLE_HOME/bin:$PATH
$
startup
mount
ALTER
DATABASE RECOVER managed standby
database disconnect from session
To stop database
alter database recover managed
standby database cancel
shutdown immediate;
3) To check applied log sync
detail
Run this command on primary and standby database
select
thread#,max(sequence#) from v$archived_log where applied='YES' group by
thread#;
SQL> select thread#,max(sequence#) from
v$archived_log where applied='YES' group by thread#;
THREAD# MAX(SEQUENCE#)
----------
--------------
1 173903
SQL> select
thread#,max(sequence#) from v$archived_log where applied='NO' group by thread#;
THREAD# MAX(SEQUENCE#)
----------
--------------
1
173938
SQL>
On production /primary
database ,
select thread#,sequence#
from v$archived_log where applied='NO' order by thread#;
SELECT
SEQUENCE#, FIRST_TIME,NEXT_TIME,APPLIED FROM V$ARCHIVED_log where applied='NO' ORDER BY SEQUENCE#;
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#
[orasbm@edwdb101
~]$ cat apply_rate.sql
select
to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss')
"Recover_start",to_char(item)||' = '||to_char(sofar)||'
'||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi')
"Values" from v$recovery_progress where start_time=(select
max(start_time) from v$recovery_progress)
/
Query what MRP process is waiting
select
a.event, a.wait_time, a.seconds_in_wait from gv$session_wait a, gv$session b
where a.sid=b.sid and b.sid=(select SID from v$session where PADDR=(select
PADDR from v$bgprocess where NAME='MRP0'
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;
select name from
v$recovery_file_dest;
show parameter db_recovery_file_dest_size
rman
connect target
DELETE ARCHIVELOG ALL BACKED UP 1 TIMES to
SBT_TAPE COMPLETED BEFORE 'sysdate-8/24';
4) Data Filesystem space/Disk
Group
set lines 200;
select GROUP_NUMBER, NAME,STATE,
TYPE,TOTAL_MB/1024 TOTAL_GB,round(FREE_MB/1024,0) FREE_GB from v$asm_diskgroup;
or
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)
No comments:
Post a Comment