Thursday, November 3, 2016
[oracle@ora12c ~]$ more incrconvert.sql
DECLARE
d varchar2(512);
h varchar2(512) ;
t varchar2(30) ;
b1 boolean ;
b2 boolean ;
outhandle varchar2(512) ;
outtag varchar2(30) ;
done boolean ;
failover boolean ;
devtype VARCHAR2(512);
BEGIN
d := sys.dbms_backup_restore.deviceAllocate;
sys.dbms_backup_restore.applysetdatafile(check_logical=>false, cleanup=>false);
sys.dbms_backup_restore.applyDatafileTo(
dfnumber=>6,
toname =>'+DATA/ORA12C/DATAFILE/mybigts.280.926495409',
fuzziness_hint=>0,
max_corrupt =>0,
islevel0=>0,
recid=>0,
stamp=>0);
sys.dbms_backup_restore.restoreSetPiece(
handle=>'/tmp/11g_xtts/incr/mybigts_incr_final_0drjjc4m_1_1',
tag=>null,
fromdisk=>true,
recid=>0,
stamp=>0);
sys.dbms_backup_restore.restoreBackupPiece(
done=>done,
params=>null,
outhandle=>outhandle,
outtag=>outtag,
failover=>failover);
sys.dbms_backup_restore.restoreCancel(TRUE);
sys.dbms_backup_restore.deviceDeallocate;
END;
/
Wednesday, November 2, 2016
TEMP Tablespace administration
SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
Free space in a temporary tablespace.
select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);
find out which SQL statement is using up space in a sort segment.
select s.sid || ',' || s.serial# sid_serial, s.username,
o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,
o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by s.sid;
select file_name,sum(bytes)/1024/1024 from dba_temp_files where tablespace_name='TEMP' group by file_name;
select file_name, sum(bytes/1024/1024/1024),tablespace_name from dba_temp_files where tablespace_name='TEMP'group by file_name, tablespace_name ;
UNDO tablespace
set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
unxpstealcnt,
expstealcnt,
activeblks,
unexpiredblks,
expiredblks,
tuned_undoretention
from v$undostat
order by end_time;
--------------------------------------------
set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
undoblks,
expblkreucnt,
ssolderrcnt,
nospaceerrcnt,
activeblks,
unexpiredblks,
expiredblks,
tuned_undoretention
from v$undostat
order by end_time;
Show all connected users
set lines 100 pages 999
col ID format a15
select username , sid , serial# , status, last_call_et "Last Activity" from v$session
where username is not null
order by status desc, last_call_et desc
2. Time since last user activity
set lines 100 pages 999
select username, floor(last_call_et / 60) "Minutes", status from v$session where username is not null order by last_call_et
3. Sessions sorted by logon time
set lines 100 pages 999
set linesize 200
col ID format a15col osuser format a15
col login_time format a14
select username, osuser, sid , serial# , status, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time, last_call_et from v$session where username is not null order by login_time
4. Show user info including os pid
col "SID/SERIAL" format a10
col username format a15col
osuser format a15col
program format a40
select s.sid , s.serial# , s.username, s.osuser, p.spid "OS PID", s.program from v$session s, v$process p Where s.paddr = p.addr order by to_number(p.spid)
5. Show a users current sql
Select sql_text from v$sqlarea where (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username')
6. Session status associated with the specified os process id
select s.username, s.sid, s.serial#, p.spid, last_call_et, status from V$SESSION s, V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid'
8. Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops
where time_remaining = 0 order by time_remaining desc
9. List open cursors per user
set pages 999
select sess.username, sess.sid, sess.serial#, stat.value cursors from v$sesstat stat, v$statname sn, v$session sess where sess.username is not null
and sess.sid = stat.sid and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current' order by value
Number of connection for the particular user from host
column username format a20
column machine format a30
column session_count format 9,999
set lines 100
set pages 100
select username, machine, count(*) session_count
from v$session
where username = 'username'
and upper(machine) like 'hostname%'
group by username, machine
order by username, machine;
sort operation based on snap id
set lines 200
col MODULE for a28
col SQL_TEXT for a80 wrap
select snap_id,DBA_HIST_SQLTEXT.SQL_ID,MODULE,SORTS_TOTAL, SORTS_DELTA,SQL_TEXT from DBA_HIST_SQLSTAT,DBA_HIST_SQLTEXT where snap_id between 27381 and 27382 and DBA_HIST_SQLSTAT.SQL_ID=DBA_HIST_SQLTEXT.SQL_ID order by 4;
Temp Segment:
Track Temp Segment Free space:
SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE
FROM V$temp_space_header
GROUP BY tablespace_name;
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A, (SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
Track Who is Currently using the Temp:
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;
Undo & Rollback Segment:
Monitor UNDO information:
select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss'), maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks;
Track Active Rollback Segment:
SELECT r.NAME, l.sid, p.spid, NVL (p.username, 'no transaction') "Transaction",
p.terminal "Terminal" FROM v$lock l, v$process p, v$rollname r
WHERE l.sid = p.pid(+) AND TRUNC (l.id1(+) / 65536) = r.usn AND l.TYPE(+) = 'TX' AND l.lmode(+) = 6 ORDER BY R.NAME;
Track Currently Who is using UNDO and TEMP:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser, s.program, r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x
WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size';
ref:
https://paulstuartoracle.wordpress.com/2014/07/20/analysing-temp-space-usage-with-ash-data/
column sum_max_mb format 999,999,999;
column temporary_tablespace format A20
WITH
pivot1 AS
(
SELECT
trunc(ash.sample_time,'MI') sample_time,
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace,
max(temp_space_allocated)/(1024*1024) max_temp_mb
FROM GV$ACTIVE_SESSION_HISTORY ash, dba_users U
WHERE
ash.user_id = U.user_id
and ash.session_type = 'FOREGROUND'
and ash.temp_space_allocated > 0
GROUP BY
trunc(ash.sample_time,'MI'),
ash.SESSION_ID,
ash.SESSION_SERIAL#,
ash.SQL_ID,
ash.sql_exec_id,
U.temporary_tablespace
)
SELECT temporary_tablespace, sample_time, sum(max_temp_mb) sum_max_mb
from pivot1
GROUP BY sample_time, temporary_tablespace
ORDER BY temporary_tablespace, sample_time;
set lines 500
set pages 500
column ts format a8
column tablespace_name format a30
column allocated format 999,999,999
column unallocated format 999,999,999
column used format 999,999,999
column allocated_free format 999,999,999
column total_free format 999,999,999
column allocated_used_pct format 990.00
column total_used_pct format 990.00
select 'TS' as TS,
ts.tablespace_name,
allocs.allocated allocated,
allocs.unallocated unallocated,
trunc((allocs.allocated - trunc(free.MB))/(allocated+unallocated)*100,2) total_used_pct
from dba_tablespaces ts
inner join
(select tablespace_name, sum(allocated) allocated, sum(unallocated) unallocated
from
(select tablespace_name, sum(bytes)/1024/1024 allocated, sum(maxbytes-bytes)/1024/1024 unallocated
from dba_data_files
where autoextensible = 'YES'
group by tablespace_name
union
select tablespace_name, sum(bytes)/1024/1024 allocated, 0
from dba_data_files
where autoextensible = 'NO'
group by tablespace_name) inner_allocs
group by tablespace_name) allocs
on ts.tablespace_name = allocs.tablespace_name
left join (select tablespace_name, sum(bytes)/1024/1024 MB
from dba_free_space
group by tablespace_name) free
on ts.tablespace_name = free.tablespace_name
where ts.contents = 'PERMANENT'
order by 5 desc;
set lines 150
column file_name format a80
column MB format 999,999,999
column MAXMB format 999,999,999
select file_name, bytes/1024/1024 MB, autoextensible, maxbytes/1024/1024 MAXMB
from dba_data_files
where tablespace_name = upper('&ts_name');
col tablespace format A16
SELECT /* + RULE */ df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free", Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
Order by 4;
set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
hello YES
SQL> select name,time,guarantee_flashback_database from v$restore_point;
-- List Flashback Log Details.
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN "Log NO" FOR 9,999
COLUMN "Thread No" FOR 99
COLUMN "Seq No" FOR 99
COLUMN name FOR A50
COLUMN "Size(GB)" FOR 999,999
COLUMN "First Chg No" FOR 999,999,999,999,999,999
ALTER SESSION
SET nls_date_format='DD MON YYYY hh24:mi:ss'
/
SELECT
log# as "Log No",
thread# as "Thread No",
sequence# as "Seq No",
name,
bytes/1024/1024/1024 as "Size(GB)",
first_change# as "First Chg No",
first_time
FROM
v$flashback_database_logfile
/
FLASHBACK DATABASE to a Restore Point
Ref:
http://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta023.htm
SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;
The bulk update fails, leaving the database with extensive corrupted data. You start an RMAN session, connect to the target database and recovery catalog, and list the guaranteed restore points:
RMAN> LIST RESTORE POINT ALL;
SCN RSP Time Type Time Name
---------------- --------- ---------- --------- ----
412742 GUARANTEED 15-FEB-07 BEFORE_UPDATE
You mount the database, flash back the database to the restore point (sample output included), and then open the database with the RESETLOGS option:
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';
Starting flashback at 15-FEB-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=104 device type=DISK
starting media recovery
archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 15-FEB-07
RMAN> ALTER DATABASE OPEN RESETLOGS;
Note.305648.1 What is a Flash Recovery Area and how to configure it ?
Note.369759.1 FLASH RECOVERY AREA and FLASHBACK database
Note.833663.1 Flash Recovery Area - FAQ
Note.560133.1 Benefits Of Using Flash Recovery Area
Note.315098.1 How is the space pressure managed in the Flash Recovery Area - An Example.
Note.305812.1 Flash Recovery area - Space management Warning & Alerts
Note.829755.1 Space issue in Flash Recovery Area( FRA )
Note.305810.1 Configuring file creation in Flash recovery area and order of Precedence
Note.305651.1 How to change Flash Recovery Area to a new location ?
Note.762934.1 Flash Recovery Area Sizing
The articles listed below help you in understanding the concepts,configuration,usage and known issues of the flashback database feature:
Note.249319.1 Configure flashback database
Note.565535.1 Flashback Database Best Practices & Performance
Note.834824.1 Recovering Dropped User using Flashback Database
Note.330535.1 Restore Points in Oracle10g Release2
Note.369759.1 FLASH RECOVERY AREA and FLASHBACK database
Note.761126.1 CALCULATE THE VOLUME OF FLASHBACK LOG GENERATION
Note.369755.1 Flashback Logs-Space management
Note.427222.1 FLASHBACK LOGS NOT PURGED WHEN ARCHIVER DOESN'T HAVE DISK SPACE
Note.454768.1 Can you use flashback database if a period of noarchivelog exists
Note.566647.1 Guaranteed Restore Point with Flashback Database disabled generates too many flashback logs
Note.833584.1 How To Disable Flashback Database Without A Bounce
Note.828410.1 High "Flashback buf free by RVWR" waits
Note.386262.1 Cloning database using EM Fails If flashback is ON
Note.342764.1 Flashback 'ON' fails with ORA-38706 and ORA-38714
Note.829254.1 Database Crashed With ORA-19815 ORA-19809 ORA-16038
Note.847090.1 Flashback Database Failed With ORA-19554 and ORA-27001
Note.554445.1 ORA-26040 FLASHBACK DATABASE WITH NOLOGGING OBJECTS/ACTIVITIES RESULTS IN CORRUPTION
Note.444389.1 Querying V$Flashback_Database_Log Or Flashing Back Database to Before Resetlogs Triggers ORA-07445 [krfbDoConvertPlugin]
Note.982104.1 Workaround for Flashback Database fails with ORA-38753 ORA-01110
Dataguard Administration
Find archive generation over last one month
SELECT TRUNC(FIRST_TIME)
, COUNT(*)
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
AND DEST_ID = 1
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1
To check the gap history (last 24 hours) in DR run the below query:
select thread#, sequence#, to_char(next_time,'DD-HH24:MI'), to_char(completion_time,'DD-HH24:MI'), round((completion_time-next_time)*24*60) as delta_minutes from v$archived_log where completion_time>SYSDATE-1 and (completion_time-next_time)*24*60>30;
Run the primary and see the any Gap in the sequence
select thread#, applied, max(sequence#) from gv$archived_log group by thread#, applied order by thread#, applied desc;
Last applied and Last received status
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES') union select 'Last received :
' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time from v$archived_log where sequence# = (select max(sequence#) from v$archived_log);
- Verify that the last sequence# received and the last sequence# applied to standby
-- database.
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
The following query
can be used on the primary database to identify peak archive times for a specified day:
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME)
Archive log gaps can be monitored by examining the low and high sequence numbers in the
V$ARCHIVE_GAP view, as shown here:
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;
When you detect a redo transport delay, execute the following query on the primary database
to identify archive logs that have not made it to the standby destination:
SELECT L.THREAD#, L.SEQUENCE#
FROM
(SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1) L
WHERE L.SEQUENCE# NOT IN
(SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=2
AND THREAD# = L.THREAD#);
Archive log genarating per hour
select day, MAX(DECODE(thread#, 1, switches_per_thread, NULL)) thread_1, MAX(DECODE(thread#, 2, switches_per_thread, NULL)) thread_2,MAX(DECODE(thread#, 3, switches_per_thread, NULL)) thread_3 from (select trunc(first_time) day, thread#, sum(1) switches_per_thread from v$log_history group by trunc(first_time), thread#) group by day order by 1
how to calculate the amount of redo per day and thread. To find out, use this query:
SELECT TRUNC(first_time), thread#, ROUND(SUM(blocks * block_size) / 1024/ 1024,2) m FROM v$archived_log WHERE dest_id = 1 GROUP BY TRUNC(first_time), thread #ORDER BY 1
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
break on thread
# skip 1
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
/
column name format a45 trunc
set lines 300
set pages 999
set trimspool on
column dest_id format 9999
column thread# format 9999
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
select sequence#, name, dest_id, thread#, first_time,
round(sum((blocks*block_size/1024/1024))) mbytes
from v$archived_log
where first_time >=trunc(sysdate-3)
group by sequence#, name, dest_id, thread#, first_time
order by sequence# desc, dest_id asc;
PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
select 'Last applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
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#
ORDER BY 1;
#########
Datagurd standby troubleshooting
Ref:
http://www.oracle-ckpt.com/dataguard_troubleshoot_snapper/
http://shivanandarao-oracle.com/2012/03/26/roll-forward-physical-standby-database-using-rman-incremental-backup/
Run Below scripts from SYS user from Both Primary & Standby databases.
Primary Script:-
spool dg_Primary_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
column name for a30
column display_value for a30
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
column ID format 99
column "SRLs" format 99
column active format 99
col type format a4
col PROTECTION_MODE for a20
col RECOVERY_MODE for a20
col db_mode for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE,switchover_status from v$database;
select thread#,max(sequence#) from v$archived_log group by thread#;
col severity for a15
col message for a70
col timestamp for a20
select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=2;
select ds.dest_id id
, ad.status
, ds.database_mode db_mode
, ad.archiver type
, ds.recovery_mode
, ds.protection_mode
, ds.standby_logfile_count "SRLs"
, ds.standby_logfile_active active
, ds.archived_seq#
from v$archive_dest_status ds
, v$archive_dest ad
where ds.dest_id = ad.dest_id
and ad.status != 'INACTIVE'
order by
ds.dest_id;
column FILE_TYPE format a20
col name format a60
select name
, floor(space_limit / 1024 / 1024) "Size MB"
, ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name;
spool off
This script will generate dg_Primary_output.log file where user is connected to SQLPlus from shell.
Standby Script:-
spool dg_standby_output.log
set feedback off
set trimspool on
set line 500
set pagesize 50
set linesize 200
column name for a30
column display_value for a30
col value for a10
col PROTECTION_MODE for a15
col DATABASE_Role for a15
SELECT name, display_value FROM v$parameter WHERE name IN ('db_name','db_unique_name','log_archive_config','log_archive_dest_2','log_archive_dest_state_2','fal_client','fal_server','standby_file_management','standby_archive_dest','db_file_name_convert','log_file_name_convert','remote_login_passwordfile','local_listener','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','log_archive_max_processes') order by name;
col name for a10
col DATABASE_ROLE for a10
SELECT name,db_unique_name,protection_mode,DATABASE_ROLE,OPEN_MODE from v$database;
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
select process, status,thread#,sequence# from v$managed_standby;
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# ORDER BY 1;
col name for a30
select * from v$dataguard_stats;
select * from v$archive_gap;
col name format a60
select name,floor(space_limit / 1024 / 1024) "Size MB" ,ceil(space_used / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name;
spool off
column applied_time for a30
set linesize 140
select to_char(sysdate,'mm-dd-yyyy hh24:mi:ss') "Current Time" from dual;
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP ,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 5))
then 'Error! Log Gap is '
else 'OK!'
end) Status
FROM
(
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 1
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=1
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=1
)
UNION
SELECT DB_NAME, APPLIED_TIME, LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
(case when ((APPLIED_TIME is not null and (LOG_ARCHIVED-LOG_APPLIED) is null) or
(APPLIED_TIME is null and (LOG_ARCHIVED-LOG_APPLIED) is not null) or
((LOG_ARCHIVED-LOG_APPLIED) > 5))
then 'Error! Log Gap is '
else 'OK!'
end) Status
from (
SELECT INSTANCE_NAME DB_NAME
FROM GV$INSTANCE
where INST_ID = 2
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' and THREAD#=2
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' and THREAD#=2
)
/
check
Performing Database health checks
Performing Database health checks, when there is an issue reported by Application users.
1. Check the Database details
2. Monitor the consumption of resources
3. Check the Alert Log
4. Check Listener log
5. Check Filesystem space Usage
6. Generate AWR Report
7. Generate ADDM Report
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database
9. Check for alerts in OEM
1. Check the Database details :-
=============================
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
For RAC:
-------
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
2. Monitor the consumption of resources :-
=======================================
select * from v$resource_limit where resource_name in ('processes','sessions');
The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.
3. Check the Alert Log :-
======================
$locate alert_
--- OR ---
UNIX/Linux command to locate the alert log file
-----------------------------------------------
$ find / -name 'alert_*.log' 2> /dev/null
vi
shift+g
?ORA- ---> press enter key
press 'n' to check backwards/up side and 'N' for forward/down side search.
:q! --and press enter, for exiting vi editor
--- OR ---
11G
===
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = 'Diag Trace';
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory
Before 11G
==========
$ sqlplus "/as sysdba"
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;
On a server with multiple instances, each instance will have it's own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory
4. Check Listener log :-
=====================
$locate listener.log
--- OR ---
UNIX/Linux command to locate the listener log file
--------------------------------------------------
$ find / -name 'listener.log' 2> /dev/null
vi
shift+g
?TNS- ---> press enter key
press 'n' to check backwords and 'N' for forword search.
AND
shift+g
?error ---> press enter key
press 'n' to check backwords and 'N' for forword search.
:q! --and press enter, for exiting vi editor
--- OR ---
$lsnrctl status
from the output you can get the listener log location (see the value for "Listener Log File" in the output).
5. Check Filesystem space Usage :-
===============================
df -h (Linux / UNIX)
df -g (AIX)
6. Generate AWR Report :-
======================
Generate AWR report for current and before to compare
SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql - for each instance)
If Required,
SQL> @?/rdbms/admin/awrddrpt.sql ----> Produces Workload Repository Compare Periods Report
7. Generate ADDM Report :-
=======================
Generate ADDM report for current and before to compare.
ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql - for each instance)
8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-
========================================================================
Select * from v$lock;
Select * from gv_$lock; (For RAC)
A fast way to check blocking/waiting situations
-----------------------------------------------
SELECT * FROM v$lock WHERE block > 0 OR request > 0;
set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12
SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, '(oracle)') AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, 'None',1, 'Null (NULL)',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share (S)',5, 'S/Row-X (SSX)',6, 'Exclusive (X)',
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;
Blocker Session and Waiting sessions
====================================
column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;
To find waiters:
---------------
set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10
select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- --------- --------- -------------- -------- --------
Blocking details:
----------------
set pages 50000 lines 32767
select distinct s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( INST=' || s1.inst_id || ' 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 and l1.inst_id = s1.inst_id;
set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select (select username from v$session where sid = a.sid ) blocker,a.sid, 'is blocking ',(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
------- ---------- ---------- ------- --------
set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;
9. Check for alerts in OEM :-
============================
Login to Oracle Enterprise Manager with valid username and password
click on "Alerts" tab
then select the below tabs one by one to see the alerts generated
Targets Down/Critical/Warning/Errors/
check
# ##############################################################################################
# DATABASE DAILY HEALTH CHECK MONITORING SCRIPT
#
# ===============================================================================
# CAUTION:
# THIS SCRIPT MAY CAUSE A SLIGHT PERFORMANCE IMPACT WHEN IT RUN,
# I RECOMMEND TO NOT RUN THIS SCRIPT SO FREQUENT, I USUALLY RUN IT ONCE A DAY.
# E.G. YOU MAY CONSIDER TO SCHEDULE IT TO RUN ONE TIME BETWEEN 12:00AM to 5:00AM.
# ===============================================================================
#
# FEATURES:
# CHECKING ALL DATABASES ALERTLOGS FOR ERRORS.
# CHECKING ALL LISTENERS ALERTLOGS FOR ERRORS.
# CHECKING CPU UTILIZATION.
# CHECKING FILESYSTEM UTILIZATION.
# CHECKING TABLESPACES UTILIZATION.
# CHECKING FLASH RECOVERY AREA UTILIZATION.
# CHECKING ASM DISKGROUPS UTILIZATION.
# CHECKING BLOCKING SESSIONS ON THE DATABASE.
# CHECKING UNUSABLE INDEXES ON THE DATABASE.
# CHECKING INVALID OBJECTS ON THE DATABASE.
# CHECKING FAILED LOGIN ATTEMPTS ON THE DATABASE.
# CHEKCING AUDIT RECORDS ON THE DATABASE.
# CHECKING CORRUPTED BLOCKS ON THE DATABASE.
# CHECKING FAILED JOBS IN THE DATABASE.
# CHECKING ACTIVE INCIDENTS.
# CHECKING OUTSTANDING ALERTS.
# CHECKING DATABASE SIZE GROWTH.
# CHECKING OS / HARDWARE STATISTICS.
# CHECKING RESOURCE LIMITS.
# CHECKING RECYCLEBIN.
# CHECKING CURRENT RESTORE POINTS.
# CHECKING HEALTH MONITOR CHECKS RECOMMENDATIONS THAT RUN BY DBMS_HM PACKAGE.
# CHEKCING MONITORED INDEXES.
# CHECKING REDOLOG SWITCHES.
# CHECKING MODIFIED INTIALIZATION PARAMETERS SINCE THE LAST DB STARTUP.
# CHECKING ADVISORS RECOMMENDATIONS:
# - SQL TUNING ADVISOR
# - SGA ADVISOR
# - PGA ADVISOR
# - BUFFER CACHE ADVISOR
# - SHARED POOL ADVISOR
# - SEGMENT ADVISOR
#
# # # #
# Author: Mahmmoud ADEL # # # # ###
# # # # # #
#
# Created: 22-12-13 Based on dbalarm.sh script.
# Modifications:18-05-14 Added Filsystem monitoring.
# 19-05-14 Added CPU monitoring.
# 09-12-14 Added Tablespaces monitoring
# Added BLOCKING SESSIONS monitoring
# Added UNUSABLE INDEXES monitoring
# Added INVALID OBJECTS monitoring
# Added FAILED LOGINS monitoring
# Added AUDIT RECORDS monitoring
# Added CORRUPTED BLOCKS monitoring
# [It will NOT run a SCAN. It will look at V$DATABASE_BLOCK_CORRUPTION]
# Added FAILED JOBS monitoring.
# 06-10-15 Replaced mpstat with iostat for CPU Utilization Check
# 02-11-15 Enhanced "FAILED JOBS monitoring" part.
# 13-12-15 Added Advisors Recommendations to the report
# 04-04-16 dba_tablespace_usage_metrics view will be used for 11g onwards versions
# for checking tablespaces size, advised by: Satyajit Mohapatra
# 10-04-16 Add Flash Recovery Area monitoring
# 10-04-16 Add ASM Disk Groups monitoring
# 15-07-16 Add ACTIVE INCIDENTS, RESOURCE LIMITS, RECYCLEBIN, RESTORE POINTS,
# MONITORED INDEXES, REDOLOG SWITCHES, MODIFIED SPFILE PARAMETERS checks.
#
#
# ##############################################################################################
SCRIPT_NAME="dbdailychk.sh"
SRV_NAME=`uname -n`
MAIL_LIST="youremail@yourcompany.com"
case ${MAIL_LIST} in "youremail@yourcompany.com")
echo
echo "###################################################################################################################"
echo "You Missed Something :-)"
echo "Please ADD your E-mail at line# 75 by replacing this template [youremail@yourcompany.com] with YOUR E-mail address."
echo "###################################################################################################################"
echo
echo "Script Terminated !"
echo
exit;;
esac
# #########################
# THRESHOLDS:
# #########################
# Send an E-mail for each THRESHOLD if been reached:
# ADJUST the following THRESHOLD VALUES as per your requirements:
FSTHRESHOLD=95 # THRESHOLD FOR FILESYSTEM %USED [OS]
CPUTHRESHOLD=95 # THRESHOLD FOR CPU %UTILIZATION [OS]
TBSTHRESHOLD=95 # THRESHOLD FOR TABLESPACE %USED [DB]
FRATHRESHOLD=95 # THRESHOLD FOR FLASH RECOVERY AREA %USED [DB]
ASMTHRESHOLD=95 # THRESHOLD FOR ASM DISK GROUPS [DB]
UNUSEINDXTHRESHOLD=1 # THRESHOLD FOR NUMBER OF UNUSABLE INDEXES [DB]
INVOBJECTTHRESHOLD=1 # THRESHOLD FOR NUMBER OF INVALID OBJECTS [DB]
FAILLOGINTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED LOGINS [DB]
AUDITRECOTHRESHOLD=1 # THRESHOLD FOR NUMBER OF AUDIT RECORDS [DB]
CORUPTBLKTHRESHOLD=1 # THRESHOLD FOR NUMBER OF CORRUPTED BLOCKS [DB]
FAILDJOBSTHRESHOLD=1 # THRESHOLD FOR NUMBER OF FAILED JOBS [DB]
# #########################
# Checking The FILESYSTEM:
# #########################
# Report Partitions that reach the threshold of Used Space:
FSLOG=/tmp/filesystem_DBA_BUNDLE.log
echo "Reported By Script: ${SCRIPT_NAME}" > ${FSLOG}
echo "" >> ${FSLOG}
df -h >> ${FSLOG}
df -h | grep -v "^Filesystem" |awk '{print substr($0, index($0, $2))}'| grep -v "/dev/mapper/"| grep -v "/dev/asm/"|awk '{print $(NF-1)" "$NF}'| while read OUTPUT
do
PRCUSED=`echo ${OUTPUT}|awk '{print $1}'|cut -d'%' -f1`
FILESYS=`echo ${OUTPUT}|awk '{print $2}'`
if [ ${PRCUSED} -ge ${FSTHRESHOLD} ]
then
mail -s "ALARM: Filesystem [${FILESYS}] on Server [${SRV_NAME}] has reached ${PRCUSED}% of USED space" $MAIL_LIST < ${FSLOG}
fi
done
rm -f ${FSLOG}
# #############################
# Checking The CPU Utilization:
# #############################
# Report CPU Utilization if reach >= 95%:
OS_TYPE=`uname -s`
CPUUTLLOG=/tmp/CPULOG_DBA_BUNDLE.log
# Getting CPU utilization in last 5 seconds:
case `uname` in
Linux ) CPU_REPORT_SECTIONS=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1 | grep ';' -o | wc -l`
CPU_COUNT=`cat /proc/cpuinfo|grep processor|wc -l`
if [ ${CPU_REPORT_SECTIONS} -ge 6 ]; then
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 7`
else
CPU_IDLE=`iostat -c 1 5 | sed -e 's/,/./g' | tr -s ' ' ';' | sed '/^$/d' | tail -1| cut -d ";" -f 6`
fi
;;
AIX ) CPU_IDLE=`iostat -t $INTERVAL_SEC $NUM_REPORT | sed -e 's/,/./g'|tr -s ' ' ';' | tail -1 | cut -d ";" -f 6`
CPU_COUNT=`lsdev -C|grep Process|wc -l`
;;
SunOS ) CPU_IDLE=`iostat -c $INTERVAL_SEC $NUM_REPORT | tail -1 | awk '{ print $4 }'`
CPU_COUNT=`psrinfo -v|grep "Status of processor"|wc -l`
;;
HP-UX) SAR="/usr/bin/sar"
CPU_COUNT=`lsdev -C|grep Process|wc -l`
if [ ! -x $SAR ]; then
echo "sar command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
else
CPU_IDLE=`/usr/bin/sar 1 5 | grep Average | awk '{ print $5 }'`
fi
;;
*) echo "uname command is not supported on your environment | CPU Check ignored"; CPU_IDLE=99
;;
esac
# Getting Utilized CPU (100-%IDLE):
CPU_UTL_FLOAT=`echo "scale=2; 100-($CPU_IDLE)"|bc`
# Convert the average from float number to integer:
CPU_UTL=${CPU_UTL_FLOAT%.*}
if [ -z ${CPU_UTL} ]
then
CPU_UTL=1
fi
if [ ${CPU_UTL} -ge ${CPUTHRESHOLD} ]
then
echo "CPU STATS:" > /tmp/top_processes_DBA_BUNDLE.log
echo "=========" >> /tmp/top_processes_DBA_BUNDLE.log
mpstat 1 5 >> /tmp/top_processes_DBA_BUNDLE.log
echo "" >> /tmp/top_processes_DBA_BUNDLE.log
echo "VMSTAT Output:" >> /tmp/top_processes_DBA_BUNDLE.log
echo "=============" >> /tmp/top_processes_DBA_BUNDLE.log
echo "[If the runqueue number in the (r) column exceeds the number of CPUs [${CPU_COUNT}] this indicates a CPU bottleneck on the system]." >> /tmp/top_processes_DBA_BUNDLE.log
echo "" >> /tmp/top_processes_DBA_BUNDLE.log
vmstat 2 5 >> /tmp/top_processes_DBA_BUNDLE.log
echo "" >> /tmp/top_processes_DBA_BUNDLE.log
echo "Top 10 Processes:" >> /tmp/top_processes_DBA_BUNDLE.log
echo "================" >> /tmp/top_processes_DBA_BUNDLE.log
echo "" >> /tmp/top_processes_DBA_BUNDLE.log
top -c -b -n 1|head -17 >> /tmp/top_processes_DBA_BUNDLE.log
#ps -eo pcpu,pid,user,args | sort -k 1 -r | head -11 >> /tmp/top_processes_DBA_BUNDLE.log
mail -s "ALERT: CPU Utilization on Server [${SRV_NAME}] has reached [${CPU_UTL}%]" $MAIL_LIST < /tmp/top_processes_DBA_BUNDLE.log
fi
rm -f ${CPUUTLLOG}
rm -f /tmp/top_processes_DBA_BUNDLE.log
# #########################
# Getting ORACLE_SID:
# #########################
# Exit with sending Alert mail if No DBs are running:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|grep -v ASM|wc -l )
if [ $INS_COUNT -eq 0 ]
then
echo "Reported By Script: ${SCRIPT_NAME}:" > /tmp/oracle_processes_DBA_BUNDLE.log
echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
echo "The following are the processes running by oracle user on server ${SRV_NAME}:" >> /tmp/oracle_processes_DBA_BUNDLE.log
echo " " >> /tmp/oracle_processes_DBA_BUNDLE.log
ps -ef|grep ora >> /tmp/oracle_processes_DBA_BUNDLE.log
mail -s "ALARM: No Databases Are Running on Server: $SRV_NAME !!!" $MAIL_LIST < /tmp/oracle_processes_DBA_BUNDLE.log
rm -f /tmp/oracle_processes_DBA_BUNDLE.log
exit
fi
# #########################
# Setting ORACLE_SID:
# #########################
for ORACLE_SID in $( ps -ef|grep pmon|grep -v grep|grep -v ASM|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
do
export ORACLE_SID
# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|grep -v ASM|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' $ORATAB | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
## If oratab is not exist, or ORACLE_SID not added to oratab, find ORACLE_HOME in user's profile:
if [ -z "${ORACLE_HOME}" ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
fi
# #########################
# Variables:
# #########################
export PATH=$PATH:${ORACLE_HOME}/bin
export LOG_DIR=${USR_ORA_HOME}/BUNDLE_Logs
mkdir -p ${LOG_DIR}
chown -R ${ORA_USER} ${LOG_DIR}
chmod -R go-rwx ${LOG_DIR}
if [ ! -d ${LOG_DIR} ]
then
mkdir -p /tmp/BUNDLE_Logs
export LOG_DIR=/tmp/BUNDLE_Logs
chown -R ${ORA_USER} ${LOG_DIR}
chmod -R go-rwx ${LOG_DIR}
fi
# ########################
# Getting ORACLE_BASE:
# ########################
# Get ORACLE_BASE from user's profile if it EMPTY:
if [ -z "${ORACLE_BASE}" ]
then
ORACLE_BASE=`grep -h 'ORACLE_BASE=\/' $USR_ORA_HOME/.bash* $USR_ORA_HOME/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
fi
# #########################
# Getting DB_NAME:
# #########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <= the defined threshold then send an email alert:
if [ ${FRAPRCUSED} -ge ${FRATHRESHOLD} ]
then
FRA_RPT=${LOG_DIR}/FRA_REPORT.log
FRACHK2=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 199
col name for a100
col TOTAL_MB for 99999999999999999
col FREE_MB for 99999999999999999
SPOOL ${FRA_RPT}
PROMPT
PROMPT FLASH RECOVER AREA Utilization:
PROMPT -----------------------------------------------
SELECT NAME,SPACE_LIMIT/1024/1024 TOTAL_MB,(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE)/1024/1024 AS FREE_MB,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "%FULL"
FROM V\$RECOVERY_FILE_DEST;
PROMPT
PROMPT FRA COMPONENTS:
PROMPT ------------------------------
select * from v\$flash_recovery_area_usage;
spool off
exit;
EOF
)
mail -s "ALERT: FRA has reached ${FRAPRCUSED}% on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${FRA_RPT}
fi
rm -f ${FRAFULL}
rm -f ${FRA_RPT}
fi
# ################################
# Check ASM Diskgroup Utilization:
# ################################
VAL314=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" < ${ASMFULL}
echo " " >> ${ASMFULL}
echo "ASM_DISK_GROUP %USED" >> ${ASMFULL}
echo "---------------------- --------------" >> ${ASMFULL}
echo "${ASMDGNAME} ${ASMPRCUSED}%" >> ${ASMFULL}
# Convert ASMPRCUSED from float number to integer:
ASMPRCUSED=${ASMPRCUSED%.*}
if [ -z ${ASMPRCUSED} ]
then
ASMPRCUSED=1
fi
# If ASM %USED >= the defined threshold send an email for each DISKGROUP:
if [ ${ASMPRCUSED} -ge ${ASMTHRESHOLD} ]
then
ASM_RPT=${LOG_DIR}/ASM_REPORT.log
ASMCHK2=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 100
set linesize 199
col name for a35
SPOOL ${ASM_RPT}
prompt
prompt ASM DISK GROUPS:
PROMPT ------------------
select name,total_mb,free_mb,ROUND((1-(free_mb / total_mb))*100, 2) "%FULL" from v\$asm_diskgroup;
spool off
exit;
EOF
)
mail -s "ALERT: ASM DISK GROUP [${ASMDGNAME}] has reached ${ASMPRCUSED}% on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${ASM_RPT}
fi
done
rm -f ${ASMFULL}
rm -f ${ASM_RPT}
fi
# #########################
# Tablespaces Size Check:
# #########################
if [ ${DB_VER} -gt 10 ] && [ ${DB_ROLE_ID} -eq 0 ]
then
# If The Database Version is 11g Onwards:
TBSCHK=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 termout off echo off feedback off
col tablespace_name for A25
col y for 999999999 heading 'Total_MB'
col z for 999999999 heading 'Used_MB'
col bused for 999.99 heading '%Used'
spool ${LOG_DIR}/tablespaces_DBA_BUNDLE.log
select tablespace_name,
(used_space*$blksize)/(1024*1024) Used_MB,
(tablespace_size*$blksize)/(1024*1024) Total_MB,
used_percent "%Used"
from dba_tablespace_usage_metrics;
spool off
exit;
EOF
)
else
# If The Database Version is 10g Backwards:
# Check if AUTOEXTEND OFF (MAXSIZE=0) is set for any of the datafiles divide by ALLOCATED size else divide by MAXSIZE:
VAL33=$(${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_DATA_FILES WHERE MAXBYTES=0;
exit;
EOF
)
VAL44=`echo $VAL33| awk '{print $NF}'`
case ${VAL44} in
"0") CALCPERCENTAGE1="((sbytes - fbytes)*100 / MAXSIZE) bused " ;;
*) CALCPERCENTAGE1="round(((sbytes - fbytes) / sbytes) * 100,2) bused " ;;
esac
VAL55=$(${ORACLE_HOME}/bin/sqlplus -S '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_TEMP_FILES WHERE MAXBYTES=0;
exit;
EOF
)
VAL66=`echo $VAL55| awk '{print $NF}'`
case ${VAL66} in
"0") CALCPERCENTAGE2="((sbytes - fbytes)*100 / MAXSIZE) bused " ;;
*) CALCPERCENTAGE2="round(((sbytes - fbytes) / sbytes) * 100,2) bused " ;;
esac
TBSCHK=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 termout off echo off feedback off
col tablespace for A25
col "MAXSIZE MB" format 9999999999
col x for 999999999 heading 'Allocated MB'
col y for 999999999 heading 'Free MB'
col z for 999999999 heading 'Used MB'
col bused for 999.99 heading '%Used'
--bre on report
spool ${LOG_DIR}/tablespaces_DBA_BUNDLE.log
select a.tablespace_name tablespace,bb.MAXSIZE/1024/1024 "MAXSIZE MB",sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes - fbytes)/1024/1024 z,
$CALCPERCENTAGE1
--round(((sbytes - fbytes) / sbytes) * 100,2) bused
--((sbytes - fbytes)*100 / MAXSIZE) bused
from (select tablespace_name,sum(bytes) sbytes from dba_data_files group by tablespace_name ) a,
(select tablespace_name,sum(bytes) fbytes,count(*) ext from dba_free_space group by tablespace_name) b,
(select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_data_files group by tablespace_name) bb
--where a.tablespace_name in (select tablespace_name from dba_tablespaces)
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = bb.tablespace_name
and round(((sbytes - fbytes) / sbytes) * 100,2) > 0
UNION ALL
select c.tablespace_name tablespace,dd.MAXSIZE/1024/1024 MAXSIZE_GB,sbytes/1024/1024 x,fbytes/1024/1024 y,
(sbytes - fbytes)/1024/1024 obytes,
$CALCPERCENTAGE2
from (select tablespace_name,sum(bytes) sbytes
from dba_temp_files group by tablespace_name having tablespace_name in (select tablespace_name from dba_tablespaces)) c,
(select tablespace_name,sum(bytes_free) fbytes,count(*) ext from v\$temp_space_header group by tablespace_name) d,
(select tablespace_name,sum(MAXBYTES) MAXSIZE from dba_temp_files group by tablespace_name) dd
--where c.tablespace_name in (select tablespace_name from dba_tablespaces)
where c.tablespace_name = d.tablespace_name (+)
and c.tablespace_name = dd.tablespace_name
order by tablespace;
select tablespace_name,null,null,null,null,null||'100.00' from dba_data_files minus select tablespace_name,null,null,null,null,null||'100.00' from dba_free_space;
spool off
exit;
EOF
)
fi
TBSLOG=${LOG_DIR}/tablespaces_DBA_BUNDLE.log
TBSFULL=${LOG_DIR}/full_tbs.log
cat ${TBSLOG}|awk '{ print $1" "$NF }'| while read OUTPUT2
do
PRCUSED=`echo ${OUTPUT2}|awk '{print $NF}'`
TBSNAME=`echo ${OUTPUT2}|awk '{print $1}'`
echo "Reported By Script: ${SCRIPT_NAME}:" > ${TBSFULL}
echo " " >> ${TBSFULL}
echo "Tablespace_name %USED" >> ${TBSFULL}
echo "---------------------- -------------" >> ${TBSFULL}
# echo ${OUTPUT2}|awk '{print $1" "$NF}' >> ${TBSFULL}
echo "${TBSNAME} ${PRCUSED}%" >> ${TBSFULL}
# Convert PRCUSED from float number to integer:
PRCUSED=${PRCUSED%.*}
if [ -z ${PRCUSED} ]
then
PRCUSED=1
fi
# If the tablespace %USED >= the defined threshold send an email for each tablespace:
if [ ${PRCUSED} -ge ${TBSTHRESHOLD} ]
then
mail -s "ALERT: TABLESPACE [${TBSNAME}] reached ${PRCUSED}% on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${TBSFULL}
fi
done
rm -f ${LOG_DIR}/tablespaces_DBA_BUNDLE.log
rm -f ${LOG_DIR}/full_tbs.log
# ############################################
# Checking BLOCKING SESSIONS ON THE DATABASE:
# ############################################
VAL77=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
select count(*) 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;
exit;
EOF
)
VAL88=`echo $VAL77| awk '{print $NF}'`
case ${VAL88} in
"0") ;;
*)
VAL99=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 0 echo off feedback off
col BLOCKING_STATUS for a90
spool ${LOG_DIR}/blocking_sessions.log
select 'User: '||s1.username || '@' || s1.machine || '(SID=' || s1.sid ||' ) running SQL_ID:'||s1.sql_id||' is blocking
User: '|| s2.username || '@' || s2.machine || '(SID=' || s2.sid || ') running SQL_ID:'||s2.sql_id||' For '||s2.SECONDS_IN_WAIT||' sec
------------------------------------------------------------------------------
Warn user '||s1.username||' Or use the following statement to kill his session:
------------------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '''||s1.sid||','||s1.serial#||''' immediate;' 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
order by s2.SECONDS_IN_WAIT desc;
spool off
exit;
EOF
)
mail -s "ALERT: BLOCKING SESSIONS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/blocking_sessions.log
rm -f ${LOG_DIR}/blocking_sessions.log
;;
esac
# ############################################
# Checking UNUSABLE INDEXES ON THE DATABASE:
# ############################################
VAL111=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from DBA_INDEXES where status='UNUSABLE';
exit;
EOF
)
VAL222=`echo $VAL111 | awk '{print $NF}'`
if [ ${VAL222} -ge ${UNUSEINDXTHRESHOLD} ]
then
VAL333=$(${ORACLE_HOME}/bin/sqlplus -s "/ as sysdba" << EOF
set linesize 160 pages 0 echo off feedback off
spool ${LOG_DIR}/unusable_indexes.log
PROMPT FIX UN-USABLE INDEXES USING THE FOLLOWING STATEMENTS:
PROMPT ------------------------------------------------------------------------------
PROMPT
select 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE;' from dba_indexes where status='UNUSABLE';
spool off
exit;
EOF
)
mail -s "INFO: UNUSABLE INDEXES detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/unusable_indexes.log
rm -f ${LOG_DIR}/unusable_indexes.log
fi
# ############################################
# Checking INVALID OBJECTS ON THE DATABASE:
# ############################################
VAL444=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from dba_objects where status <> 'VALID';
exit;
EOF
)
VAL555=`echo $VAL444 | awk '{print $NF}'`
if [ ${VAL555} -ge ${INVOBJECTTHRESHOLD} ]
then
VAL666=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/invalid_objects.log
col SUBOBJECT_NAME for a30
col status for a15
col "OWNER.OBJECT_NAME" for a65
select OWNER||'.'||OBJECT_NAME "OWNER.OBJECT_NAME",SUBOBJECT_NAME,OBJECT_TYPE,status,to_char(LAST_DDL_TIME,'DD-MON-YY HH24:mi:ss') LAST_DDL_TIME from DBA_INVALID_OBJECTS;
set pages 0 echo off feedback off
PROMPT
PROMPT ----------------------------------------------------------------------------------------------------
PROMPT YOU CAN FIX THOSE INVALID OBJECTS USING THE FOLLOWING STATEMENTS:
PROMPT ----------------------------------------------------------------------------------------------------
PROMPT
select 'alter package '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type like '%PACKAGE%' union
select 'alter type '||owner||'.'||object_name||' compile specification;' from dba_objects where status <> 'VALID' and object_type like '%TYPE%'union
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type not in ('PACKAGE','PACKAGE BODY','SYNONYM','TYPE','TYPE BODY') union
select 'alter public synonym '||object_name||' compile;' from dba_objects where status <> 'VALID' and object_type ='SYNONYM';
spool off
exit;
EOF
)
mail -s "WARNING: ${VAL555} INVALID OBJECTS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/invalid_objects.log
rm -f ${LOG_DIR}/invalid_objects.log
fi
# ###############################################
# Checking FAILED LOGIN ATTEMPTS ON THE DATABASE:
# ###############################################
VAL777=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select /*+ parallel 2 */ COUNT(*) from DBA_AUDIT_SESSION where returncode = 1017 and timestamp > (sysdate-1);
exit;
EOF
)
VAL888=`echo $VAL777 | awk '{print $NF}'`
if [ ${VAL888} -ge ${FAILLOGINTHRESHOLD} ]
then
VAL999=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/failed_logins.log
PROMPT FAILED LOGIN ATTEMPT [SESSION DETAILS]:
PROMPT --------------------------------------------------------------------
PROMPT
col OS_USERNAME for a20
col USERNAME for a25
col TERMINAL for a30
col ACTION_NAME for a20
col TIMESTAMP for a21
col USERHOST for a40
select /*+ parallel 2 */ to_char (EXTENDED_TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') TIMESTAMP,OS_USERNAME,USERNAME,TERMINAL,USERHOST,ACTION_NAME
from DBA_AUDIT_SESSION
where returncode = 1017
and timestamp > (sysdate -1)
order by 1;
spool off
exit;
EOF
)
mail -s "INFO: FAILED LOGIN ATTEMPT detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/failed_logins.log
rm -f ${LOG_DIR}/failed_logins.log
fi
# ###############################################
# Checking AUDIT RECORDS ON THE DATABASE:
# ###############################################
VAL70=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT (SELECT COUNT(*) FROM dba_audit_trail
where ACTION_NAME not like 'LOGO%' and ACTION_NAME not in ('SELECT','SET ROLE') and timestamp > SYSDATE-1)
+
(SELECT COUNT(*) FROM dba_fga_audit_trail WHERE timestamp > SYSDATE-1) AUD_REC_COUNT FROM dual;
exit;
EOF
)
VAL80=`echo $VAL70 | awk '{print $NF}'`
if [ ${VAL80} -ge ${AUDITRECOTHRESHOLD} ]
then
VAL90=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/audit_records.log
col EXTENDED_TIMESTAMP for a36
col OWNER for a25
col OBJ_NAME for a25
col OS_USERNAME for a20
col USERNAME for a25
col USERHOST for a21
col ACTION_NAME for a25
col ACTION_OWNER_OBJECT for a55
prompt
prompt
prompt ----------------------------------------------------------
prompt Audit records in the last 24Hours AUD$...
prompt ----------------------------------------------------------
prompt
select extended_timestamp,OS_USERNAME,USERNAME,USERHOST,ACTION_NAME||' '||OWNER||' . '||OBJ_NAME ACTION_OWNER_OBJECT
from dba_audit_trail
where
ACTION_NAME not like 'LOGO%'
and ACTION_NAME not in ('SELECT','SET ROLE')
-- and USERNAME not in ('CRS_ADMIN','DBSNMP')
-- and OS_USERNAME not in ('workflow')
-- and OBJ_NAME not like '%TMP_%'
-- and OBJ_NAME not like 'WRKDETA%'
-- and OBJ_NAME not in ('PBCATTBL','SETUP','WRKIB','REMWORK')
and timestamp > SYSDATE-1 order by EXTENDED_TIMESTAMP;
prompt
prompt ----------------------------------------------------------
prompt Fine Grained Auditing Data ...
prompt ----------------------------------------------------------
prompt
col sql_text for a70
col time for a36
col USERHOST for a21
col db_user for a15
select to_char(timestamp,'DD-MM-YYYY HH24:MI:SS') as time,db_user,userhost,sql_text,SQL_BIND
from dba_fga_audit_trail
where
timestamp > SYSDATE-1
-- and policy_name='PAYROLL_TABLE'
order by EXTENDED_TIMESTAMP;
spool off
exit;
EOF
)
mail -s "INFO: AUDIT RECORDS on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/audit_records.log
rm -f ${LOG_DIR}/audit_records.log
fi
# ############################################
# Checking CORRUPTED BLOCKS ON THE DATABASE:
# ############################################
# It won't validate the datafiles nor scan for corrupted blocks, it will just check V$DATABASE_BLOCK_CORRUPTION view if populated.
VAL10=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
select count(*) from V\$DATABASE_BLOCK_CORRUPTION;
exit;
EOF
)
VAL20=`echo $VAL10 | awk '{print $NF}'`
if [ ${VAL20} -ge ${CORUPTBLKTHRESHOLD} ]
then
VAL30=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/corrupted_blocks.log
PROMPT CORRUPTED BLOCKS DETAILS:
PROMPT --------------------------------------
PROMPT
select * from V\$DATABASE_BLOCK_CORRUPTION;
spool off
exit;
EOF
)
mail -s "ALARM: CORRUPTED BLOCKS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/corrupted_blocks.log
rm -f ${LOG_DIR}/corrupted_blocks.log
fi
# ############################################
# Checking FAILED JOBS ON THE DATABASE:
# ############################################
VAL40=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
--SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM dba_scheduler_jobs where FAILURE_COUNT <> '0') FAIL_COUNT FROM dual;
SELECT (SELECT COUNT(*) FROM dba_jobs where failures <> '0') + (SELECT COUNT(*) FROM DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED') FAIL_COUNT FROM dual;
exit;
EOF
)
VAL50=`echo $VAL40 | awk '{print $NF}'`
if [ ${VAL50} -ge ${FAILDJOBSTHRESHOLD} ]
then
VAL60=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/failed_jobs.log
PROMPT DBMS_JOBS:
PROMPT -----------
col LAST_RUN for a25
col NEXT_RUN for a25
set long 9999999
--select dbms_xmlgen.getxml('select job,schema_user,failures,LAST_DATE LAST_RUN,NEXT_DATE NEXT_RUN from dba_jobs where failures <> 0') xml from dual;
select job,schema_user,failures,to_char(LAST_DATE,'DD-Mon-YYYY hh24:mi:ss')LAST_RUN,to_char(NEXT_DATE,'DD-Mon-YYYY hh24:mi:ss')NEXT_RUN from dba_jobs where failures <> '0';
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT ----------------
col OWNER for a25
col JOB_NAME for a40
col STATE for a11
col STATUS for a11
col FAILURE_COUNT for 999 heading 'Fail'
col RUNTIME_IN_LAST24H for a25
col RUN_DURATION for a14
--HTML format Outputs:
--Set Markup Html On Entmap On Spool On Preformat Off
-- Get the whole failed runs in the last 24 hours:
select to_char(LOG_DATE,'DD-Mon-YYYY hh24:mi:ss')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>'SUCCEEDED';
--XML Output
--select dbms_xmlgen.getxml('select to_char(LOG_DATE,''DD-Mon-YYYY hh24:mi:ss'')RUNTIME_IN_LAST24H,OWNER,JOB_NAME,STATUS,ERROR#,RUN_DURATION from DBA_SCHEDULER_JOB_RUN_DETAILS where LOG_DATE > sysdate-1 and STATUS<>''SUCCEEDED''') xml from dual;
spool off
exit;
EOF
)
mail -s "WARNING: FAILED JOBS detected on database [${DB_NAME_UPPER}] on Server [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/failed_jobs.log
rm -f ${LOG_DIR}/failed_jobs.log
fi
# ############################################
# Checking Advisors:
# ############################################
# If the database version is 10g onward collect the advisors recommendations:
if [ ${DB_VER} -gt 9 ]
then
VAL611=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set linesize 190 pages 100
spool ${LOG_DIR}/advisors.log
PROMPT REPORTED BY: dbdailychk.sh
PROMPT -----------------------------------------
PROMPT
PROMPT -------------------------
PROMPT Tablespaces Size:
PROMPT -------------------------
PROMPT
PROMPT Based on Datafile MAXSIZE:
PROMPT ..........................................
set pages 1000 linesize 1000 tab off
col tablespace_name for A25
col Total_MB for 999999999999
col Used_MB for 999999999999
col '%Used' for 999.99
comp sum of Total_MB on report
comp sum of Used_MB on report
bre on report
select tablespace_name,
(tablespace_size*$blksize)/(1024*1024) Total_MB,
(used_space*$blksize)/(1024*1024) Used_MB,
used_percent "%Used"
from dba_tablespace_usage_metrics;
PROMPT ASM STATISTICS:
PROMPT ----------------------------------
select name,state,OFFLINE_DISKS,total_mb,free_mb,ROUND((1-(free_mb / total_mb))*100, 2) "%FULL" from v\$asm_diskgroup;
PROMPT FRA STATISTICS:
PROMPT ------------------------
PROMPT
PROMPT FRA_SIZE:
PROMPT ------------------------
col name for a25
SELECT NAME,NUMBER_OF_FILES,SPACE_LIMIT/1024/1024/1024 AS TOTAL_SIZE_GB,SPACE_USED/1024/1024/1024 SPACE_USED_GB,
SPACE_RECLAIMABLE/1024/1024/1024 SPACE_RECLAIMABLE_GB,ROUND((SPACE_USED-SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS "%FULL_AFTER_CLAIM",
ROUND((SPACE_USED)/SPACE_LIMIT * 100, 1) AS "%FULL_NOW" FROM V\$RECOVERY_FILE_DEST;
PROMPT FRA_COMPONENTS:
PROMPT -------------------------------
select * from v\$flash_recovery_area_usage;
PROMPT ------------------------------
PROMPT DATABASE GROWTH: [In the Last ~8 days]
PROMPT ------------------------------
set serveroutput on
Declare
v_BaselineSize number(20);
v_CurrentSize number(20);
v_TotalGrowth number(20);
v_Space number(20);
cursor usageHist is
select a.snap_id,
SNAP_TIME,
sum(TOTAL_SPACE_ALLOCATED_DELTA) over ( order by a.SNAP_ID) ProgSum
from
(select SNAP_ID,
sum(SPACE_ALLOCATED_DELTA) TOTAL_SPACE_ALLOCATED_DELTA
from DBA_HIST_SEG_STAT
group by SNAP_ID
having sum(SPACE_ALLOCATED_TOTAL) <> 0
order by 1 ) a,
(select distinct SNAP_ID,
to_char(END_INTERVAL_TIME,'DD-Mon-YYYY HH24:Mi') SNAP_TIME
from DBA_HIST_SNAPSHOT) b
where a.snap_id=b.snap_id;
Begin
select sum(SPACE_ALLOCATED_DELTA) into v_TotalGrowth from DBA_HIST_SEG_STAT;
select sum(bytes) into v_CurrentSize from dba_segments;
v_BaselineSize := (v_CurrentSize - v_TotalGrowth) ;
dbms_output.put_line('SNAP_TIME Database Size(GB)');
for row in usageHist loop
v_Space := (v_BaselineSize + row.ProgSum)/(1024*1024*1024);
dbms_output.put_line(row.SNAP_TIME || ' ' || to_char(v_Space) );
end loop;
end;
/
PROMPT
PROMPT ----------------------
PROMPT Active Incidents:
PROMPT ----------------------
set linesize 170
col RECENT_PROBLEMS_1_WEEK_BACK for a45
select PROBLEM_KEY RECENT_PROBLEMS_1_WEEK_BACK,to_char(FIRSTINC_TIME,'DD-MON-YY HH24:mi:ss') FIRST_OCCURENCE,to_char(LASTINC_TIME,'DD-MON-YY HH24:mi:ss')
LAST_OCCURENCE FROM V\$DIAG_PROBLEM WHERE LASTINC_TIME > SYSDATE -10;
PROMPT
PROMPT OUTSTANDING ALERTS:
PROMPT ----------------------
select * from DBA_OUTSTANDING_ALERTS;
PROMPT
PROMPT ------------------------------------
PROMPT SCHEDULED JOBS STATUS:
PROMPT ------------------------------------
PROMPT
PROMPT DBMS_JOBS:
PROMPT ----------------------
set linesize 200
col LAST_RUN for a25
col NEXT_RUN for a25
select job,schema_user,failures,to_char(LAST_DATE,'DD-Mon-YYYY hh24:mi:ss')LAST_RUN,to_char(NEXT_DATE,'DD-Mon-YYYY hh24:mi:ss')NEXT_RUN from dba_jobs;
PROMPT
PROMPT DBMS_SCHEDULER:
PROMPT --------------------------------
col OWNER for a10
col STATE for a15
col FAILURE_COUNT for 9999 heading 'Fail'
col "DURATION(d:hh:mm:ss)" for a22
col REPEAT_INTERVAL for a70
col "LAST_RUN || REPEAT_INTERVAL" for a65
col "DURATION(d:hh:mm:ss)" for a12
--col LAST_START_DATE for a40
select JOB_NAME,OWNER,ENABLED,STATE,FAILURE_COUNT,to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')||' || '||REPEAT_INTERVAL "LAST_RUN || REPEAT_INTERVAL",
extract(day from last_run_duration) ||':'||
lpad(extract(hour from last_run_duration),2,'0')||':'||
lpad(extract(minute from last_run_duration),2,'0')||':'||
lpad(round(extract(second from last_run_duration)),2,'0') "DURATION(d:hh:mm:ss)"
from dba_scheduler_jobs order by ENABLED,STATE;
PROMPT
PROMPT AUTOTASK INTERNAL MAINTENANCE WINDOWS:
PROMPT --------------------------------------------------------------------------
col WINDOW_NAME for a17
col NEXT_RUN for a20
col ACTIVE for a6
col OPTIMIZER_STATS for a15
col SEGMENT_ADVISOR for a15
col SQL_TUNE_ADVISOR for a16
col HEALTH_MONITOR for a15
SELECT WINDOW_NAME,TO_CHAR(WINDOW_NEXT_TIME,'DD-MM-YYYY HH24:MI:SS') NEXT_RUN,AUTOTASK_STATUS STATUS,WINDOW_ACTIVE ACTIVE,OPTIMIZER_STATS,SEGMENT_ADVISOR,SQL_TUNE_ADVISOR,HEALTH_MONITOR FROM DBA_AUTOTASK_WINDOW_CLIENTS;
PROMPT ------------------------------
PROMPT ADVISORS STATUS:
PROMPT ------------------------------
col CLIENT_NAME for a60
col window_group for a60
col STATUS for a15
SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name;
PROMPT
PROMPT ------------------------------
PROMPT SQL TUNING ADVISOR:
PROMPT ------------------------------
PROMPT Last Execution of SQL TUNING ADVISOR:
PROMPT ---------------------------------------------------------
col TASK_NAME for a60
set long 2000000000
SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI') Last_Execution FROM dba_advisor_executions where TASK_NAME='SYS_AUTO_SQL_TUNING_TASK' and execution_end>sysdate-1;
variable Findings_Report CLOB;
BEGIN
:Findings_Report :=DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK(
begin_exec => NULL,
end_exec => NULL,
type => 'TEXT',
level => 'TYPICAL',
section => 'ALL',
object_id => NULL,
result_limit => NULL);
END;
/
print :Findings_Report
PROMPT
PROMPT
PROMPT ------------------------------
PROMPT MEMORY ADVISORS:
PROMPT ------------------------------
PROMPT
PROMPT SGA ADVISOR:
PROMPT ----------------------
col ESTD_DB_TIME for 99999999999999999
col ESTD_DB_TIME_FACTOR for 9999999999999999999999999999
select * from V\$SGA_TARGET_ADVICE where SGA_SIZE_FACTOR > .6 and SGA_SIZE_FACTOR < 1.6;
PROMPT
PROMPT Buffer Cache ADVISOR:
PROMPT --------------------------------------
col ESTD_SIZE_MB for 9999999999999
col ESTD_PHYSICAL_READS for 99999999999999999999
col ESTD_PHYSICAL_READ_TIME for 99999999999999999999
select SIZE_FACTOR "%SIZE",SIZE_FOR_ESTIMATE ESTD_SIZE_MB,ESTD_PHYSICAL_READS,ESTD_PHYSICAL_READ_TIME,ESTD_PCT_OF_DB_TIME_FOR_READS
from V\$DB_CACHE_ADVICE where SIZE_FACTOR >.8 and SIZE_FACTOR<1.3;
PROMPT
PROMPT Shared Pool ADVISOR:
PROMPT --------------------------------------
col SIZE_MB for 99999999999
col SIZE_FACTOR for 99999999
col ESTD_SIZE_MB for 99999999999999999999
col LIB_CACHE_SAVED_TIME for 99999999999999999999999999
select SHARED_POOL_SIZE_FOR_ESTIMATE SIZE_MB,SHARED_POOL_SIZE_FACTOR "%SIZE",SHARED_POOL_SIZE_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,ESTD_LC_TIME_SAVED LIB_CACHE_SAVED_TIME,
ESTD_LC_LOAD_TIME PARSING_TIME from V\$SHARED_POOL_ADVICE
where SHARED_POOL_SIZE_FACTOR > .9 and SHARED_POOL_SIZE_FACTOR < 1.6;
PROMPT
PROMPT PGA ADVISOR:
PROMPT ----------------------
col SIZE_FACTOR for 999999999
col ESTD_SIZE_MB for 99999999999999999999
col MB_PROCESSED for 99999999999999999999
col ESTD_TIME for 99999999999999999999
select PGA_TARGET_FACTOR "%SIZE",PGA_TARGET_FOR_ESTIMATE/1024/1024 ESTD_SIZE_MB,BYTES_PROCESSED/1024/1024 MB_PROCESSED,
ESTD_TIME,ESTD_PGA_CACHE_HIT_PERCENTAGE PGA_HIT,ESTD_OVERALLOC_COUNT PGA_SHORTAGE
from V\$PGA_TARGET_ADVICE where PGA_TARGET_FACTOR > .7 and PGA_TARGET_FACTOR < 1.6;
PROMPT ------------------------------
PROMPT SEGMENT ADVISOR:
PROMPT ------------------------------
select'Task Name : ' || f.task_name || chr(10) ||
'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||
'Segment Name : ' || o.attr2 || chr(10) ||
'Segment Type : ' || o.type || chr(10) ||
'Partition Name : ' || o.attr3 || chr(10) ||
'Message : ' || f.message || chr(10) ||
'More Info : ' || f.more_info || chr(10) ||
'------------------------------------------------------' Advice
FROM dba_advisor_findings f
,dba_advisor_objects o
,dba_advisor_executions e
WHERE o.task_id = f.task_id
AND o.object_id = f.object_id
AND f.task_id = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name = 'Segment Advisor'
ORDER BY f.task_name;
PROMPT --------------------------------------------------------
PROMPT CURRENT OS / HARDWARE STATISTICS:
PROMPT --------------------------------------------------------
select stat_name,value from v\$osstat;
PROMPT
PROMPT --------------------------------
PROMPT RESOURCE LIMIT:
PROMPT --------------------------------
col INITIAL_ALLOCATION for a20
col LIMIT_VALUE for a20
select * from gv\$resource_limit order by RESOURCE_NAME;
PROMPT
PROMPT --------------------------------
PROMPT RECYCLEBIN OBJECTS#:
PROMPT --------------------------------
set feedback off
select count(*) "RECYCLED_OBJECTS#",sum(space)*$blksize/1024/1024 "TOTAL_SIZE_MB" from dba_recyclebin group by 1;
set feedback on
PROMPT
PROMPT [Note: Consider Purging DBA_RECYCLEBIN for better performance]
PROMPT
PROMPT ------------------------------------------
PROMPT FLASHBACK RESTORE POINTS:
PROMPT ------------------------------------------
select * from V\$RESTORE_POINT;
PROMPT
PROMPT ----------------------------------
PROMPT HEALTH MONITOR:
PROMPT ----------------------------------
select name,type,status,description,repair_script from V\$HM_RECOMMENDATION where time_detected > sysdate -1;
PROMPT ----------------------------------
PROMPT Monitored INDEXES:
PROMPT ----------------------------------
set linesize 180
col Index_NAME for a40
col TABLE_NAME for a40
select io.name Index_NAME, t.name TABLE_NAME,decode(bitand(i.flags, 65536),0,'NO','YES') Monitoring,
decode(bitand(ou.flags, 1),0,'NO','YES') USED,ou.start_monitoring,ou.end_monitoring
from sys.obj$ io,sys.obj$ t,sys.ind$ i,sys.object_usage ou where i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#;
PROMPT ----------------------------------
PROMPT REDO LOG SWITCHES:
PROMPT ----------------------------------
set linesize 199
col day for a11
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 where first_time > sysdate-1
GROUP by to_char(first_time,'YYYY-MON-DD') order by 1 asc;
PROMPT
PROMPT ------------------------------------------------------------------
PROMPT Modified Parameters Since Instance Startup:
PROMPT ------------------------------------------------------------------
col name for a45
col VALUE for a100
col DEPRECATED for a10
select NAME,VALUE,ISDEFAULT "DEFAULT",ISDEPRECATED "DEPRECATED" from v\$parameter where ISMODIFIED = 'SYSTEM_MOD' order by 1;
PROMPT
PROMPT ------------------------------------------------------------------
PROMPT Cred Backup:
PROMPT ------------------------------------------------------------------
col name for a35
col "CREATE_DATE||PASS_LAST_CHANGE" for a60
select name,PASSWORD HASH,CTIME ||' || '||PTIME "CREATE_DATE||PASS_LAST_CHANGE" from user\$ where PASSWORD is not null order by 1;
spool off
exit;
EOF
)
mail -s "ADVISORS: For Database [${DB_NAME_UPPER}] on Server: [${SRV_NAME}]" $MAIL_LIST < ${LOG_DIR}/advisors.log
fi
# #########################
# Getting ALERTLOG path:
# #########################
VAL2=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" </dev/null`
fi
# Rename the old log generated by the script (if exists):
if [ -f ${LOG_DIR}/alert_${ORACLE_SID}_new.log ]
then
mv ${LOG_DIR}/alert_${ORACLE_SID}_new.log ${LOG_DIR}/alert_${ORACLE_SID}_old.log
# Create new log:
tail -1000 ${ALERTLOG} > ${LOG_DIR}/alert_${ORACLE_SID}_new.log
# Extract new entries by comparing old & new logs:
echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/diff_${ORACLE_SID}.log
echo " " >> ${LOG_DIR}/diff_${ORACLE_SID}.log
diff ${LOG_DIR}/alert_${ORACLE_SID}_old.log ${LOG_DIR}/alert_${ORACLE_SID}_new.log |grep ">" | cut -f2 -d'>' >> ${LOG_DIR}/diff_${ORACLE_SID}.log
# Search for errors:
ERRORS=`cat ${LOG_DIR}/diff_${ORACLE_SID}.log | grep 'ORA-\|TNS-' | grep -v 'ORA-2396' | tail -1`
FILE_ATTACH=${LOG_DIR}/diff_${ORACLE_SID}.log
else
# Create new log:
echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/alert_${ORACLE_SID}_new.log
echo " " >> ${LOG_DIR}/alert_${ORACLE_SID}_new.log
tail -1000 ${ALERTLOG} >> ${LOG_DIR}/alert_${ORACLE_SID}_new.log
# Search for errors:
ERRORS=`cat ${LOG_DIR}/alert_${ORACLE_SID}_new.log | grep 'ORA-\|TNS-' | grep -v "ORA-2396" | tail -1`
FILE_ATTACH=${LOG_DIR}/alert_${ORACLE_SID}_new.log
fi
# Send mail in case error exist:
case "$ERRORS" in
*ORA-*|*TNS-*)
mail -s "ALERT: Instance [${ORACLE_SID}] on Server [${SRV_NAME}] reporting errors: ${ERRORS}" ${MAIL_LIST} < ${FILE_ATTACH}
esac
# #####################
# Reporting Offline DBs:
# #####################
# Populate ${LOG_DIR}/alldb_DBA_BUNDLE.log from ORATAB:
grep -v '^\#' $ORATAB | grep -v "ASM" |grep -v "${DB_NAME_LOWER}:"| grep -v "${DB_NAME_UPPER}:"| grep -v '^$' | grep "^" | cut -f1 -d':' > ${LOG_DIR}/alldb_DBA_BUNDLE.log
# Populate ${LOG_DIR}/updb_DBA_BUNDLE.log:
echo $ORACLE_SID >> ${LOG_DIR}/updb_DBA_BUNDLE.log
echo $DB_NAME >> ${LOG_DIR}/updb_DBA_BUNDLE.log
# End looping for databases:
done
# Continue Reporting Offline DBs...
# Sort the lines alphabetically with removing duplicates:
sort ${LOG_DIR}/updb_DBA_BUNDLE.log | uniq -d > ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
sort ${LOG_DIR}/alldb_DBA_BUNDLE.log > ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
diff ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort ${LOG_DIR}/updb_DBA_BUNDLE.log.sort > ${LOG_DIR}/diff_DBA_BUNDLE.sort
echo "The Following Instances are POSSIBLY Down on $SRV_NAME :" > ${LOG_DIR}/offdb_DBA_BUNDLE.log
grep "^< " ${LOG_DIR}/diff_DBA_BUNDLE.sort | cut -f2 -d'<' >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo " " >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo "If those instances are permanently offline, please hash their entries in $ORATAB to let the script ignore them in the next run." >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
OFFLINE_DBS_NUM=`cat ${LOG_DIR}/offdb_DBA_BUNDLE.log| wc -l`
# If OFFLINE_DBS is not null:
if [ ${OFFLINE_DBS_NUM} -gt 3 ]
then
echo "" >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo "Current Running Instances:" >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo "************************" >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
ps -ef|grep pmon|grep -v grep >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
echo "" >> ${LOG_DIR}/offdb_DBA_BUNDLE.log
VALX1=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <> ${LOG_DIR}/offdb_DBA_BUNDLE.log
mail -s "ALARM: Database Inaccessible on Server: [$SRV_NAME]" $MAIL_LIST < ${LOG_DIR}/offdb_DBA_BUNDLE.log
fi
# Wiping Logs:
#cat /dev/null > ${LOG_DIR}/updb_DBA_BUNDLE.log
#cat /dev/null > ${LOG_DIR}/alldb_DBA_BUNDLE.log
#cat /dev/null > ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
#cat /dev/null > ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
#cat /dev/null > ${LOG_DIR}/diff_DBA_BUNDLE.sort
rm -f ${LOG_DIR}/updb_DBA_BUNDLE.log
rm -f ${LOG_DIR}/alldb_DBA_BUNDLE.log
rm -f ${LOG_DIR}/updb_DBA_BUNDLE.log.sort
rm -f ${LOG_DIR}/alldb_DBA_BUNDLE.log.sort
rm -f ${LOG_DIR}/diff_DBA_BUNDLE.sort
# ###########################
# Checking Listeners log:
# ###########################
# In case there is NO Listeners are running send an (Alarm):
LSN_COUNT=$( ps -ef|grep -v grep|grep tnslsnr|wc -l )
if [ $LSN_COUNT -eq 0 ]
then
echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/listener_processes.log
echo " " >> ${LOG_DIR}/listener_processes.log
echo "The following are the processes running by user ${ORA_USER} on server ${SRV_NAME}:" >> ${LOG_DIR}/listener_processes.log
echo " " >> ${LOG_DIR}/listener_processes.log
ps -ef|grep -v grep|grep oracle >> ${LOG_DIR}/listener_processes.log
mail -s "ALARM: No Listeners Are Running on Server: $SRV_NAME !!!" $MAIL_LIST < ${LOG_DIR}/listener_processes.log
# In case there is a listener running analyze it's log:
else
for LISTENER_NAME in $( ps -ef|grep -v grep|grep tnslsnr|awk '{print $(NF-1)}' )
do
LISTENER_HOME=`ps -ef|grep -v grep|grep tnslsnr|grep "${LISTENER_NAME} "|awk '{print $(NF-2)}' |sed -e 's/\/bin\/tnslsnr//g'|grep -v sed|grep -v "s///g"`
TNS_ADMIN=${LISTENER_HOME}/network/admin; export TNS_ADMIN
LISTENER_LOGDIR=`${LISTENER_HOME}/bin/lsnrctl status ${LISTENER_NAME} |grep "Listener Log File"| awk '{print $NF}'| sed -e 's/\/alert\/log.xml//g'`
LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log
# Determine if the listener name is in Upper/Lower case:
if [ -f ${LISTENER_LOG} ]
then
# Listner_name is Uppercase:
LISTENER_NAME=$( echo ${LISTENER_NAME} | perl -lpe'$_ = reverse' |perl -lpe'$_ = reverse' )
LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log
else
# Listener_name is Lowercase:
LISTENER_NAME=$( echo "${LISTENER_NAME}" | tr -s '[:upper:]' '[:lower:]' )
LISTENER_LOG=${LISTENER_LOGDIR}/trace/${LISTENER_NAME}.log
fi
# Rename the old log (If exists):
if [ -f ${LOG_DIR}/alert_${LISTENER_NAME}_new.log ]
then
mv ${LOG_DIR}/alert_${LISTENER_NAME}_new.log ${LOG_DIR}/alert_${LISTENER_NAME}_old.log
# Create a new log:
tail -1000 ${LISTENER_LOG} > ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
# Get the new entries:
echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/diff_${LISTENER_NAME}.log
echo " " >> ${LOG_DIR}/diff_${LISTENER_NAME}.log
diff ${LOG_DIR}/alert_${LISTENER_NAME}_old.log ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep ">" | cut -f2 -d'>' >> ${LOG_DIR}/diff_${LISTENER_NAME}.log
# Search for errors:
ERRORS=`cat ${LOG_DIR}/diff_${LISTENER_NAME}.log| grep "TNS-" |tail -1`
SRVC_REG=`cat ${LOG_DIR}/diff_${LISTENER_NAME}.log| grep "service_register" `
FILE_ATTACH=${LOG_DIR}/diff_${LISTENER_NAME}.log
# If no old logs exist:
else
# Just create a new log without doing any comparison:
echo "Reported By Script: ${SCRIPT_NAME}" > ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
echo " " >> ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
tail -1000 ${LISTENER_LOG} >> ${LOG_DIR}/alert_${LISTENER_NAME}_new.log
# Search for errors:
ERRORS=`cat ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep "TNS-" | tail -1`
SRVC_REG=`cat ${LOG_DIR}/alert_${LISTENER_NAME}_new.log | grep "service_register" `
FILE_ATTACH=${LOG_DIR}/alert_${LISTENER_NAME}_new.log
fi
# Report TNS Errors (Alert)
case "$ERRORS" in
*TNS-*)
mail -s "ALERT: Listener [${LISTENER_NAME}] on Server [${SRV_NAME}] reporting errors: ${ERRORS}" $MAIL_LIST < ${FILE_ATTACH}
esac
# Report Registered Services to the listener (Info)
case "$SRVC_REG" in
*service_register*)
mail -s "INFO: Service Registered on Listener [${LISTENER_NAME}] on Server [${SRV_NAME}] | TNS poisoning posibility" $MAIL_LIST < ${FILE_ATTACH}
esac
done
fi
# #############
# END OF SCRIPT
# #############
# REPORT BUGS to: mahmmoudadel@hotmail.com
# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM:
# http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
Subscribe to:
Posts (Atom)