Day To Day Responsibilities related oracle dba scripts
Day To Day Responsibiliteis related oracle dba scripts
These scripts are valid for Single & rac Database & exadata.
A => status to check Oracle instance is running or not.
[oracle@yesbolisetty01 ~]$ ps -ef | grep smon
or
[oracle@yesbolisetty01 ~]$ ps -ef | grep pmon
Check all Instance of any database as follows.
[oracle@yesbolisetty01 ~]$ srvctl status database -d <DB_NAME>
[oracle@yesbolisetty01 ~]$ ps -ef | grep smon
oracle 140550 1 0 2019 ? 00:01:49 asm_smon_+ASM1
root 147726 1 1 2019 ? 1-04:06:08 /u01/app/12.1.0.2/grid/bin/osysmond.bin
oracle 156342 1 0 2019 ? 00:13:57 ora_smon_testdb
oracle 248609 246524 0 12:02 pts/0 00:00:00 grep smon
[oracle@yesbolisetty01 ~]$
[oracle@yesbolisetty01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node yesbolisetty01
Instance testdb2 is running on node yesbolisetty02
[oracle@yesbolisetty01 ~]$
B => status to check Local /SCAN listeners are running or not
[oracle@yesbolisetty01 ~]$ lsnrctl status
[oracle@yesbolisetty01 ~]$ srvctl status scan_listener
C => Check the Server Storage or Disk of Oracle database.
[oracle@msddbadm01 ~]$ df -h
Unix
[oracle@msddbadm01 ~]$ df -g
[grid@msdidb01 ~]$ asmcmd lsdg
D => Check the Tablespaces for objects to extend if required.
set pagesize 1000 linesize 180
tti 'Tablespace Usage Status'
col "TOTAL(MB)" for 99,999,999.999
col "USAGE(MB)" for 99,999,999.999
col "FREE(MB)" for 99,999,999.999
col "EXTENSIBLE(MB)" for 99,999,999.999
col "FREE PCT %" for 999.99
col "USED PCT OF MAX %" for 999.99
col "NOTO" for 9999
col "OTO" for 999
select d.tablespace_name "NAME",
d.contents "TYPE",
nvl(a.bytes /1024/1024,0) "TOTAL(MB)",
nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024 "USAGE(MB)",
nvl(f.bytes,0)/1024/1024 "FREE(MB)",
nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %",
nvl(a.ARTACAK,0)/1024/1024 "EXTENSIBLE(MB)",
nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %",
a.NOTO, a.OTO
from sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes,
sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK,
count(decode(autoextensible,'NO',0)) NOTO,
count(decode(autoextensible,'YES',0)) OTO
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and NOT (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY')
UNION ALL
select d.tablespace_name "NAME",
d.contents "TYPE",
nvl(a.bytes /1024/1024,0) "TOTAL(MB)",
nvl(t.bytes,0)/1024/1024 "USAGE(MB)",
nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024 "FREE(MB)",
nvl(t.bytes/a.bytes * 100,0) "FREE PCT %",
nvl(a.ARTACAK,0)/1024/1024 "EXTENSIBLE(MB)",
nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %", a.NOTO, a.OTO
from sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes,
sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK,
count(decode(autoextensible,'NO',0)) NOTO,
count(decode(autoextensible,'YES',0)) OTO
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
and d.extent_management like 'LOCAL'
and d.contents like 'TEMPORARY%'
order by 3 desc;
exit;
E => HOW TO Check the Recovery Size Area
set pagesize 1000 line 200
col "db_recovery_file_dest" for a32;
col size_m for 999,999,999;
col used_m for 999,999,999;
col pct_used for 999;
select name "db_recovery_file_dest",ceil(space_limit/1024/1024) TOTAL_MB, ceil( space_used /1024/1024) USED_MB,
decode( nvl(space_used, 0),0,0,ceil(( space_used /space_limit) * 100)) PERCENTAGE(%)
from v$recovery_file_dest
order by 1;
exit;
F => How to Check the alert log if a vital error exists or not.
set linesize 150
set pagesize 150
SELECT SUBSTR (MESSAGE_TEXT, 1, 300) MESSAGE_TEXT, COUNT (*) cnt
FROM X$DBGALERTEXT
WHERE (MESSAGE_TEXT LIKE '%ORA-%' OR UPPER (MESSAGE_TEXT) LIKE '%ERROR%')
AND CAST (ORIGINATING_TIMESTAMP AS DATE) > SYSDATE - 1
GROUP BY SUBSTR (MESSAGE_TEXT, 1, 300);
exit;
G => Check the latest Archivelog and Full Backup are done or not
SELECT TO_CHAR (start_time, 'DD-MM-YYYY HH24:MI:SS') start_time, input_type, status, ROUND (elapsed_seconds / 3600, 1) time_hr,INPUT_BYTES/1024/1024/1024 IN_GB,OUTPUT_BYTES/1024/1024/1024 OUT_GB ,OUTPUT_DEVICE_TYPE FROM
v$rman_backup_job_details WHERE START_TIME > SYSDATE - 3 ORDER BY start_time DESC;
H => Check any session blocking the other session ( blocking session and Lock control.
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
I => Check the DBMS jobs running or not and check the status of the Jobs
-- Failed Scheduled Jobs
SELECT owner, job_name,status,LOG_DATE, ERROR#, ( EXTRACT (SECOND FROM run_duration) /60 + EXTRACT (MINUTE FROM run_duration) + EXTRACT (HOUR FROM run_duration) * 60 + EXTRACT (DAY FROM run_duration) * 60 * 24) MINUTES,ADDITIONAL_INFO
FROM dba_scheduler_job_run_details
WHERE LOG_DATE > SYSDATE - 1 AND status != 'SUCCEEDED' ORDER BY 1 ASC, 4 DESC;
-- Running and Succeeded Scheduled Jobs
SELECT OWNER, JOB_NAME, LAST_START_DATE, STATE
FROM DBA_SCHEDULER_JOBS
WHERE LAST_START_DATE > SYSDATE - 1 AND STATE <> 'SCHEDULED';
J => Check the Dataguard is synchronized or not.
select process, client_process,thread#,sequence#,status from v$managed_standby where process like '%MRP%';
select name,value from v$dataguard_stats;
K => Check the Performance Page of Enterprise Manager or Enterprise Manager Cloud Control
Open Performance Page of Enterprise manager Cloud Control as follows to check Performance.
L => Detect lock objects
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM gv$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID;
M => Check the SQL query consuming lot of resources ( CPU and Disk Resources )
elect * from (
select ss.sql_text,
a.SQL_ID,
sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s,v$sql ss
where
s.snap_id = a.snap_id and a.sql_id=ss.sql_id
and s.begin_interval_time > sysdate -1
group by
ss.sql_text,a.SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum<20;
N => Check the usage of physical RAM and SGA – Paging or Swapping exist or not.
free -m
SQL> set linesize 150
SQL> set pagesize 150
SQL> select * from v$sgainfo;
Select * from v$sgastat;
O => Check Log Switch and Archivelog generation frequency.
select to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
from v$log_history group by to_char(first_time,'YYYY-MON-DD');
Comments
Post a Comment