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

Popular posts from this blog

AWR Reports

AWR Reports Introduction Workload Repository Reports AWR Snapshots and Baselines Moving AWR Information Reading the AWR Report Useful Queries Remove and Disable AWR Links with AWR Analyzer Introduction AWR periodically gathers and stores system activity and workload data which is then analyzed by ADDM. Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found (by default up to 7 days). AWR runs by default and Oracle states that it does not add a noticeable level of overhead. A new background server process (MMON) takes snapshots of the in-memory database statistics (much like STATSPACK) and stores this information in the repository. MMON also provides Oracle10G with a server initiated alert feature, which notifies database admini
Read more

ORA-01565: error in identifying file '?/dbs/spfile@.ora'

QL> startup ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated SQL> create pfile from spfile; create pfile from spfile * ERROR at line 1: ORA-01565: error in identifying file '?/dbs/spfile@.ora' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3   SQL> shut abort SQL> create pfile='/home/oracle/initORA11G.ora' from spfile='+DATA/ORA11G/spfileORA11G.ora'; File created. oracle@rac1.localdomain:/home/oracle [ORA11G1] >dba SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 6 20:15:29 2016 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup pfile='/home/oracle/initORA11G.ora'; ORACLE instance started. Total System Global Area 1653518336 bytes Fixed Size                  2228904 bytes Variable Size             956304728 bytes Database Buf
Read more

My Fav Song in Telugu: Aa challani samudra garbham

Image
                                                                                                          ఆ చల్లని సముద్ర గర్భం దాచిన బడబానలమెంతో  ఆ నల్లని ఆకాశంలో కానరాని భాస్కరులెందరో  ||ఆ చల్లని|| భూగోళం పుట్టుక కోసం రాలిన సుర గోళాలెన్నో  ఈ మానవ రూపం కోసం జరిగిన పరిణామాలెన్నో ఒక రాజుని గెలిపించుటలో ఒరిగిన నర కంఠములెన్నో కుల మతాల సుడిగుండాలకు బలియైన పవిత్రులెందరో  ||ఆ చల్లని|| మానవ కళ్యాణం కోసం పణమెత్తిన రక్తము ఎంతో  రణరక్కసి కరాళ నృత్యం రాచిన పసి ప్రాణాలెన్నో కడుపు కోతతో అల్లాడిన కన్నులలో విషాదమెంతో  భూస్వాముల  దౌర్జన్యాలకు  ధనవంతుల దుర్మార్గాలకు  దగ్ధమైన బతుకులు ఎన్నో  ||ఆ చల్లని|| అన్నార్తులు అనాథలుండని ఆ నవయుగమదెంత దూరం  కరువంటూ కాటకమంటూ కనిపించని కాలాలెపుడో పసిపాపల నిదుర కనులలో ముసిరిన భవితవ్యం ఎంతో  గాయపడిన కవి గుండెలలో రాయబడని కావ్యాలెన్నో ||ఆ చల్లని || a challani samudra garbham daachina badabaanalamentho  Aa nallani aakasamlo kanarani bhaskarulendharoooo || Aa Challani ||  Bhoogolam puttuka kosam raalina sura golaalenno Eee manava rupam kosam jarigina parin
Read more