Ngày hôm nay tôi sẽ chia sẻ với các bạn 1 báo cáo rất KINH ĐIỂN mà nhìn vào đó bạn có thể biết ngay được hiện trạng của cơ sở dữ liệu của mình, có thể bạn chưa HIỂU được script này và có thể bạn cần nhiều mục khác nữa, bạn bổ sung thêm vào nhé. Hãy copy về và nghiền ngẫm, sử dụng trước khi nó KHÔNG còn được PUBLIC rộng rãi.
1. Tạo thủ tục
create or replace procedure hc_all
is
tAll varchar2(32700):='';
tAll_New varchar2(10000):='';
tDBName varchar2(20):='';
v_err varchar2(1000):='';
--1.Tai DB
nActiveSession number;
nTotalActiveSession number; -- Ca background
nTotalInactiveSession number;
nTotalSession number;
nLock number;
nLockDBLink number;
CURSOR cTotalUserSession
IS
select /* count , status*/ username,status, count(*) num from gv$session group by username,status
having (count(*)>200 and status='INACTIVE') or (count(*)>15 and status='ACTIVE')
order by status,count(*) desc,username;
tTotalUserSession varchar2(30000):='';
cursor c_cpu_ram
is
select event_date, duration, server_name, cpu_idle, round(ram_free_k/1024/1024,0) "GB"
from BINHTV.dbamf_ct_vmstat
where event_date > sysdate-1/24
and (cpu_idle <= 20 or round(ram_free_k/1024/1024,0) <=70)
order by event_date desc;
v_cpu_ram varchar2(30000):='';
--2.Backup
CURSOR cBackup
IS
select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,0) RATIO ,
input_bytes_per_sec_display, output_bytes_per_sec_display
from v$rman_backup_job_details
where trunc(end_time)>=trunc(sysdate-3)
order by end_time desc;
tBackup varchar2(30000):='';
--3.Storage
cursor cASM is
select name, state, type, round(total_mb/1024) total_gb, round(usable_file_mb/1024) usable_file_gb from v$asm_diskgroup;
tASM varchar2(30000) := '';
cursor cTBS is
SELECT a.tablespace_name,100 - ROUND
( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) pct_usage, ROUND(a.bytes_alloc / 1024 / 1024/1024) size_gb, ROUND
(NVL (b.bytes_free, 0) / 1024 / 1024/1024) free_gb,
(ROUND (a.bytes_alloc / 1024 / 1024/1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024/1024)) used_gb, ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) pct_free, ROUND (maxbytes / 1048576/1024) max_gb,
ROUND (ROUND ( (a.bytes_alloc - NVL
(b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) as pct_used_max
FROM ( SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
--and
a.tablespace_name in ('DATA','INDX')
and ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) > 85
order by pct_used_max desc;
tTBS varchar2(30000) := '';
cursor c_user_usage is
select owner,round(sum(bytes)/1024/1024/1024,0) "GB" from dba_segments
group by owner
having round(sum(bytes)/1024/1024/1024,0) > 50
order by "GB" desc;
v_user_usage varchar2(30000) := '';
cursor c_object_usage is
select owner,segment_name,round(sum(bytes)/1024/1024/1024,0) "GB" from dba_segments
group by owner, segment_name
having round(sum(bytes)/1024/1024/1024,0) > 100
order by "GB" desc;
v_object_usage varchar2(3000) := '';
-- Cac
tablespace Read Only:Chay lau
/* cursor cTBS_RO is
select tablespace_name,
round(sum(bytes)/1024/1024/1024,0) "GB"
from dba_segments
where tablespace_name in
(select name from v$tablespace where ts# in (select ts# from v$datafile where
enabled='READ ONLY'))
group by tablespace_name
order by tablespace_name ;
tStorage varchar2(4000):='';*/
cursor c_du
is
select * from binhtv.dbamf_ct_disk_usage
where event_date>sysdate-1
and nvl(substr(disk_percen,-3,2),0)>70
order by disk_percen desc;
v_du varchar2(30000):='';
--4.Object
invalid
nInvalidObject number;
--5.Index
Non-partition, parttion Unuable
nUnuableIndNonPar number;
nUnuableIndPar number;
--6.Archive log
-- Theo doi
archived log sinh ra
cursor cTotalArchivedLog is
select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,0) "Archived Log GB" from V$ARCHIVED_LOG
where trunc(completion_time) >= trunc(sysdate-1)
--and
trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
and dest_id=1
group by trunc(completion_time)
order by trunc(completion_time) desc;
-- Archived log
sinh ra theo gio
cursor cHourArchivedLog is
select to_char(next_time,'YYYY-MM-DD hh24') Hour, round(sum(size_in_byte)/1024/1024,0) as size_in_mb, count(*) log_switch from (
select thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE as size_in_byte, next_time
from v$archived_log where name is not null and
completion_time>sysdate-1 group by thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE, next_time)
group by to_char(next_time,'YYYY-MM-DD hh24') order by 1 desc;
tArchivedLog varchar2(30000):='';
--7.Gather
tGather varchar2(4000):='';
tGatherTabPar varchar2(100) :='';
tGatherIndPar varchar2(100) :='';
tGatherTabNonPar varchar2(100) :='';
tGatherIndNonPar varchar2(100) :='';
BEGIN
insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)
values(binhtv.dbamf_log_jobs_seq.nextval,'Starting sys.dba_rp.hc_all',1,sysdate,'sys.dba_rp.hc_all');
commit;
--dbms_output.put_line('Before
if');
--if
(to_char(sysdate,'hh24') in ('00','08','13')) then
--dbms_output.put_line('After
if');
select name into tDBName from v$database;
tAll := tAll ||'<h2>BAO
CAO CSDL ' || tDBName ||' NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') ||'</h2>';
--1.TAI DB
select count(*) into nActiveSession from gv$session where
username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE') and status='ACTIVE';
select count(*) into
nTotalActiveSession from gv$session where status='ACTIVE';
select count(*) into
nTotalInactiveSession from gv$session where status='INACTIVE';
select count(*) into nTotalSession from gv$session;
SELECT COUNT(*) INTO nLock FROM
gv$session WHERE blocking_session IS NOT NULL;
sELECT COUNT ( * ) INTO nLockDBLink FROM dba_2pc_pending
where (retry_time-fail_time)*24*60>2;
tAll := tAll|| '<br><b>SUMMARY SESSION:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1"><tr
bgcolor="#FFF1D9" style="font-weight:bold"><td>nActiveSession</td><td>nTotalActiveSession</td><td>nTotalInactiveSession</td><td>nTotalSession</td><td>nLock</td><td>nLockDBLink</td></tr>';
if nActiveSession > 200 then
tAll := tAll||'<tr>
<td><font color="red"><b>'||nActiveSession||'</b></font></td>
<td>'||nTotalActiveSession||'</td>
<td>'||nTotalInactiveSession||'</td>
<td>'||nTotalSession||'</td>
<td>'||nLock||'</td>
<td>'||nLockDBLink||'</td>
</tr>'||'</table>';
else
tAll := tAll||'<tr>
<td>'||nActiveSession||'</td>
<td>'||nTotalActiveSession||'</td>
<td>'||nTotalInactiveSession||'</td>
<td>'||nTotalSession||'</td>
<td>'||nLock||'</td>
<td>'||nLockDBLink||'</td>
</tr>'||'</table>';
end if;
--1.3.Total
session theo tung user
tTotalUserSession :=tTotalUserSession||'
<br><b>USER SESSIONS DETAIL</b>:
<table width="600"
border ="1" cellspacing="1"
cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td>UserName</td>
<td>Status</td>
<td>Total
Sesion</td>
</tr>';
FOR r1 IN
cTotalUserSession
LOOP
tTotalUserSession:=tTotalUserSession||'<tr>
<td>'||r1.username||'</td>
<td>'||r1.status||' </td>
<td>'||r1.num||'</td>
</tr>';
END LOOP;
tTotalUserSession:=tTotalUserSession||'</table>';
tAll :=tAll ||
tTotalUserSession;
--cdr_monitor.send_email_html_m('binhtv10@gmail.com;tranbinh48ca@gmail.com','DBA_BILL_DBA
MONITOR_' || sysdate, tAll);
dbms_output.put_line('1');
--CPU,RAM
v_cpu_ram:=v_cpu_ram||'<br><b>CPU, RAM USAGE:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr bgcolor="#FFF1D9"
style="font-weight:bold">
<td> event_date</td>
<td> duration</td>
<td> server_name</td>
<td> cpu_idle</td>
<td> GB</td>
</tr>';
for r11 in c_cpu_ram loop
v_cpu_ram:=v_cpu_ram||'<tr>
<td>'||to_char(r11.event_date,'dd/mm/yyyy hh24:mm:ss')||'</td>
<td>'||r11.duration||'</td>
<td>'||r11.server_name||'</td>
<td>'||r11.cpu_idle||'</td>
<td>'||r11.GB||'</td>
</tr>' ;
end loop;
v_cpu_ram := v_cpu_ram || '</table>';
tAll := tAll || v_cpu_ram;
--dbms_output.put_line('2');
--2.BACKUP: Lay
1 tuan
tBackup:=tBackup||'<br><b>SUMMARY BACKUP:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr bgcolor="#FFF1D9"
style="font-weight:bold">
<td>command_id</td>
<td>start_time</td>
<td>end_time</td>
<td>status</td>
<td>INPUT_TYPE</td>
<td>input_bytes_display</td>
<td>output_bytes_display</td>
<td>time_taken_display</td>
<td>RATIO</td>
<td>input_bytes_per_sec_display</td>
<td>output_bytes_per_sec_display</td>
</tr>';
for r1 in cBackup loop
tBackup:=tBackup||'<tr>
<td>'||r1.command_id||'</td>
<td>'||to_char(r1.start_time,'dd/mm/yyyy
hh24:mm:ss')||'</td>
<td>'||to_char(r1.end_time,'dd/mm/yyyy hh24:mm:ss')||' </td>
<td>'||r1.status||' </td>
<td>'||r1.INPUT_TYPE||'</td>
<td>'||r1.input_bytes_display||'</td>
<td>'||r1.output_bytes_display||'</td>
<td>'||r1.time_taken_display||'</td>
<td>'||r1.RATIO||'</td>
<td>'||r1.input_bytes_per_sec_display||'</td>
<td>'||r1.output_bytes_per_sec_display||'</td>
</tr>' ;
end loop;
tBackup := tBackup || '</table>';
tAll := tAll || tBackup;
--dbms_output.put_line('2');
--3.STORAGE
tASM :='<br><b>ASM DISKGROUP:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td>name</td>
<td>state</td>
<td>type</td>
<td>total_gb</td>
<td>usable_file_gb</td>
</tr>';
for r2 in cASM loop
tASM:=tASM||'<tr>
<td>'||r2.name||'</td>
<td>'||r2.state||'</td>
<td>'||r2.type||'</td>
<td>'||r2.total_gb||'</td>
<td>'||r2.usable_file_gb||'</td>
</tr>' ;
end loop;
tASM := tASM || '</table><br>';
tAll := tAll || tASM;
--dbms_output.put_line('3.tASM');
tTBS :='<br><b>DUNG LUONG TABLESPACE:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td> Tablespace Name
</td>
<td> %Usage
</td>
<td> Size GB </td>
<td> Free GB
</td>
<td> Used GB
</td>
<td> %Free
</td>
<td> Max GB
</td>
<td> %Used Max
</td>
</tr>';
--dbms_output.put_line('0');
for r3 in cTBS loop
if r3.pct_used_max
> 90 then
tTBS:=tTBS||'<tr>
<td><font color="red"><b>'||r3.tablespace_name||'</b></font></td>
<td>'||r3.pct_usage||'</td>
<td>'||r3.size_gb||'</td>
<td>'||r3.free_gb||'</td>
<td>'||r3.used_gb||'</td>
<td>'||r3.pct_free||'</td>
<td>'||r3.max_gb||'</td>
<td><font color="red"><b>'||r3.pct_used_max||'</b></font></td>
</tr>' ;
else
tTBS:=tTBS||'<tr>
<td>'||r3.tablespace_name||'</td>
<td>'||r3.pct_usage||'</td>
<td>'||r3.size_gb||'</td>
<td>'||r3.free_gb||'</td>
<td>'||r3.used_gb||'</td>
<td>'||r3.pct_free||'</td>
<td>'||r3.max_gb||'</td>
<td>'||r3.pct_used_max||'</td>
</tr>' ;
end if;
end loop;
--dbms_output.put_line('1:'
|| length(tTBS));
tTBS := tTBS || '</table><br>';
tAll := tAll || tTBS;
--dbms_output.put_line('3.TBS');
v_user_usage :='<br><b>DUNG LUONG THEO
USER:</b><table width="600"
border ="1" cellspacing="1"
cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td> User </td>
<td> GB </td>
</tr>';
--dbms_output.put_line('for
r31 in c_user_usage loop');
for r31 in c_user_usage loop
if r31."GB" > 100 then
v_user_usage:=v_user_usage||'<tr>
<td><font color="red"><b>'||r31.owner||'</b></font></td>
<td><font color="red"><b>'||r31."GB"||'</b></font></td>
</tr>' ;
else
v_user_usage:=v_user_usage||'<tr>
<td>'||r31.owner||'</td>
<td>'||r31."GB"||'</td>
</tr>' ;
end if;
end loop;
--dbms_output.put_line('end
loop;');
v_user_usage := v_user_usage || '</table>';
tAll := tAll || v_user_usage;
--dbms_output.put_line('3.DUNG
LUONG THEO USER');
v_object_usage :='<br><b>DUNG LUONG THEO
OBJECT:</b><table width="600" border ="1"
cellspacing="1" cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td> User</td>
<td> Segment_name/td>
<td> GB </td>
</tr>';
for r32 in c_object_usage loop
if r32."GB"
> 1000 then
v_object_usage:=v_object_usage||'<tr>
<td><font color="red"><b>'||r32.owner||'</b></font></td>
<td><font color="red"><b>'||r32.segment_name||'</b></font></td>
<td><font color="red"><b>'||r32."GB"||'</b></font></td>
</tr>' ;
else
v_object_usage:=v_object_usage||'<tr>
<td>'||r32.owner||'</td>
<td>'||r32.segment_name||'</td>
<td>'||r32."GB"||'</td>
</tr>';
end if;
end loop;
v_object_usage := v_object_usage || '</table>';
tAll := tAll || v_object_usage;
dbms_output.put_line('3.DUNG LUONG THEO OBJECT');
--dbms_output.put_line('3');
v_du :='<br><b>DUNG LUONG CAC PHAN VUNG OS:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td>
Host_Name</td>
<td> Name</td>
<td> Size </td>
<td> Free </td>
<td> Percent
</td>
<td> Mount Point
</td>
<td> Event_Date
</td>
<td> IP </td>
</tr>';
--dbms_output.put_line('v_object_usage');
for r33 in c_du loop
if(substr(r33.Disk_percen,-3,0) > 80) then
v_du:=v_du||'<tr>
<td><font color="red"><b>'||r33.host_name||'</b></font></td>
<td><font
color="red"><b>'||r33.disk_name||'</b></font></td>
<td><font color="red"><b>'||r33.Disk_size||'</b></font></td>
<td><font color="red"><b>'||r33.Disk_free||'</b></font></td>
<td><font color="red"><b>'||r33.Disk_percen||'</b></font></td>
<td><font color="red"><b>'||r33.Disk_mount||'</b></font></td>
<td><font color="red"><b>'||to_char(r33.event_date,'dd/mm/yyyy hh24:mm:ss')||'</b></font></td>
<td><font color="red"><b>'||r33.db_ip||'</b></font></td>
</tr>' ;
else
v_du:=v_du||'<tr>
<td>'||r33.host_name||'</td>
<td>'||r33.disk_name||'</td>
<td>'||r33.Disk_size||'</td>
<td>'||r33.Disk_free||'</td>
<td>'||r33.Disk_percen||'</td>
<td>'||r33.Disk_mount||'</td>
<td>'||to_char(r33.event_date,'dd/mm/yyyy hh24:mm:ss')||'</td>
<td>'||r33.db_ip||'</td>
</tr>' ;
end if;
end loop;
v_du := v_du || '</table>';
tAll := tAll || v_du;
--dbms_output.put_line('3.DUNG
LUONG THEO OS');
--dbms_output.put_line('3.Completed');
--4.Object
invalid
select count(*) into nInvalidObject from (select 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects
where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE') and status like 'INVALID'and OWNER in ('CDR_OWNER','CUS_OWNER','MC_OWNER')
UNION ALL
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' from dba_objects
where object_type in ('PACKAGE BODY') and status like 'INVALID' and OWNER like 'CDR_OWNER');
tAll := tAll||'<br><b>OBJECT
INVALID:</b><table width="600" border ="1"
cellspacing="1" cellpadding="1">
<tr
bgcolor="#FFF1D9" style="font-weight:bold">
<td> So luong Object
Invalid </td>
<td> So luong Index
Partition UNUSABLE </td>
<td> So luong Index
Non-Partition UNUSABLE </td>
</tr>';
--dbms_output.put_line('tAll:
' || length(tAll));
tAll := tAll||'<tr>
<td>'||nInvalidObject||'</td>' ;
--dbms_output.put_line('tAll:
' || length(tAll));
--Index invalid:
Partition, non-partition
select count(*) into
nUnuableIndNonPar from dba_indexes where status='UNUSABLE';
select count(*) into
nUnuableIndPar from dba_ind_partitions where status='UNUSABLE';
tAll := tAll||'
<td>'||nUnuableIndPar||'</td>
<td>'||nUnuableIndNonPar||'</td>
</tr>' ;
tAll := tAll || '</table>';
--dbms_output.put_line('4');
--5.Archive log
trong 1 ngay hien tai
tAll := tAll||'<br><b>ARCHIVE
LOG TRONG 7 NGAY:</b><table width="600" border ="1"
cellspacing="1" cellpadding="1">
<tr bgcolor="#FFF1D9"
style="font-weight:bold">
<td> Completion_time
</td>
<td> Archived_Log_GB
</td>
</tr>';
for r1 in (select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,0) as archived_log_gb
from V$ARCHIVED_LOG
where trunc(completion_time) >= trunc(sysdate-7)
--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')
and dest_id=1
group by trunc(completion_time)
order by trunc(completion_time) desc) loop
if r1.archived_log_gb > 1000 then
tAll:=tAll||'<tr>
<td><font
color="red"><b>'||r1.completion_time||'</b></font></td>
<td><font
color="red"><b>'||r1.archived_log_gb||'</b></font></td>
</tr>' ;
else
tAll:=tAll||'<tr>
<td>'||r1.completion_time||'</td>
<td>'||r1.archived_log_gb||'</td>
</tr>' ;
end if;
end loop;
tAll:=tAll||'</table>';
--dbms_output.put_line('5');
--6.Gather
tAll := tAll||'<br><b>GATHER:</b><table
width="600" border
="1" cellspacing="1" cellpadding="1">
<tr bgcolor="#FFF1D9"
style="font-weight:bold">
<td> tGatherTabPar </td>
<td> tGatherIndPar
</td>
<td> tGatherTabNonPar
</td>
<td> tGatherIndNonPar
</td>
</tr>';
select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherTabPar from dba_tab_partitions
where table_owner in ('USER_OWNER') and
last_analyzed<sysdate and last_analyzed>sysdate-7;
select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherIndPar from dba_ind_partitions
where index_owner in ('USER_OWNER') and last_analyzed<sysdate and last_analyzed>sysdate-7;
-- Non-partition
select to_date(max(last_analyzed),'dd/mm/yyyy') into
tGatherTabNonPar from dba_tables
where owner in ('USER_OWNER') and
last_analyzed<sysdate and last_analyzed>sysdate-7
;
select to_date(max(last_analyzed),'dd/mm/yyyy') into
tGatherIndNonPar from dba_indexes
where owner in ('USER_OWNER') and
last_analyzed<sysdate and last_analyzed>sysdate-7;
tAll:=tAll||'<tr>
<td>'||tGatherTabPar||'</td>
<td>'||tGatherIndPar||'</td>
<td>'||tGatherTabNonPar||'</td>
<td>'||tGatherIndNonPar||'</td>
</tr>' ;
tAll := tAll || '</table>';
--dbms_output.put_line('6');
--dbms_output.put_line('tAll:
'||length(tAll));
--execute
immediate 'truncate table tc_monior_lob';
--insert into
tc_monior_lob(text) values(tAll);
--commit;
--select text into
tAll_New from tc_monior_lob;
IF nActiveSession >= 250 then
binhtv.send_email ('binhtv@gmail.com,'HIGH - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);
ELSIF nActiveSession > 200 and nActiveSession < 250 then
binhtv.send_email ('binhtv@gmail.com,'MID - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);
ELSE
binhtv.send_email ('binhtv@gmail.com,'LOW - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);
END IF;
--send_sms_binhtv('sys.dba_rp.hc');
--end if;
insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)
values(binhtv.dbamf_log_jobs_seq.nextval,'Completed sys.dba_rp.hc_all',1,sysdate,'sys.dba_rp.hc_all');
commit;
EXCEPTION
WHEN others THEN
send_sms ('Error sys.dba_rp.hc: ' || SQLERRM);
v_err := SUBSTR(SQLERRM, 1, 200);
insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)
values(binhtv.dbamf_log_jobs_seq.nextval,'Error sys.dba_rp.hc_all',-1,sysdate,'Error sys.dba_rp.hc_all, '||v_err);
commit;
END;
2. Chạy và có thể đặt vào job
Chạy định kỳ vào 8h sáng, 10h sáng, 14h chiều, 16h chiều nhé (có thể đặt lịch dày hơn hay giãn hơn tùy bạn)
begin
hc_all;
end;
3. Kết quả
- Nhìn vào tiêu đề (HIGH, LOW, MID) có thể biết ngay hiện trạng CSDL
- Đọc chi tiết về tải Active Session Session, Total Session, session active theo user, backup, ASM Disk, tablespace có dung lượng cao cần chú ý, dung lượng theo từng user, phân vùng OS, archive sinh ra, thông tin Gather:


Phục lục: Danh sách các bảng cần tạo:
DROP TABLE BINHTV.DBAMF_LOG_JOBS CASCADE CONSTRAINTS;
CREATE TABLE BINHTV.DBAMF_LOG_JOBS
(
ID NUMBER,
NAME VARCHAR2(4000 BYTE),
STATUS VARCHAR2(50 BYTE),
EVENT_DATE DATE DEFAULT sysdate,
NOTE VARCHAR2(2000 BYTE)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
MONITORING;
-- Start of DDL Script for Table BINHTV.DBAMF_CT_TBS_SIZE
-- Generated 5-Nov-2020 16:12:10 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_TBS_SIZE
DROP TABLE binhtv.dbamf_ct_tbs_size
/
CREATE TABLE binhtv.dbamf_ct_tbs_size
(id NUMBER,
tbs_name VARCHAR2(100 BYTE),
size_gb NUMBER,
event_date DATE DEFAULT sysdate,
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
DROP SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ;
CREATE SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ
START WITH 2156723
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
-- End of DDL Script for Table BINHTV.DBAMF_CT_TBS_SIZE
-- Start of DDL Script for Table BINHTV.DBAMF_CT_USER_SIZE
-- Generated 5-Nov-2020 16:12:14 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_USER_SIZE
DROP TABLE binhtv.dbamf_ct_user_size
/
CREATE TABLE binhtv.dbamf_ct_user_size
(id NUMBER,
user_name VARCHAR2(100 BYTE),
size_mb NUMBER,
event_date DATE DEFAULT sysdate,
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_USER_SIZE
-- Start of DDL Script for Table BINHTV.DBAMF_CT_VMSTAT
-- Generated 5-Nov-2020 16:12:18 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_VMSTAT
DROP TABLE binhtv.dbamf_ct_vmstat
/
CREATE TABLE binhtv.dbamf_ct_vmstat
(event_date DATE,
duration NUMBER,
server_name VARCHAR2(30 BYTE),
runque_waits NUMBER,
swap_in NUMBER,
swap_out NUMBER,
cpu_user NUMBER,
cpu_sys NUMBER,
cpu_idle NUMBER,
cpu_wait NUMBER,
swapd NUMBER,
ram_free_k NUMBER)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- Grants for Table
GRANT INSERT ON binhtv.dbamf_ct_vmstat TO monitor
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_VMSTAT
-- Start of DDL Script for Table BINHTV.DBAMF_DISK_USAGE
-- Generated 5-Nov-2020 16:12:22 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_DISK_USAGE
DROP TABLE binhtv.dbamf_disk_usage
/
CREATE TABLE binhtv.dbamf_disk_usage
(host_name VARCHAR2(40 BYTE),
disk_name VARCHAR2(50 BYTE),
disk_size VARCHAR2(20 BYTE),
disk_free VARCHAR2(20 BYTE),
disk_percen VARCHAR2(10 BYTE),
disk_mount VARCHAR2(50 BYTE),
db_ip VARCHAR2(20 BYTE),
event_date DATE DEFAULT sysdate)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_DISK_USAGE
-- Start of DDL Script for Table BINHTV.DBAMF_LOG_GG
-- Generated 5-Nov-2020 16:12:26 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_LOG_GG
DROP TABLE binhtv.dbamf_log_gg
/
CREATE TABLE binhtv.dbamf_log_gg
(id NUMBER,
program VARCHAR2(20 BYTE),
status VARCHAR2(50 BYTE),
gg_group VARCHAR2(100 BYTE),
lag VARCHAR2(50 BYTE),
time_since_chkpt VARCHAR2(50 BYTE),
event_date DATE DEFAULT sysdate)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_LOG_GG
-- Start of DDL Script for Table BINHTV.DBAMF_LOG_JOBS
-- Generated 5-Nov-2020 16:12:31 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_LOG_JOBS
DROP TABLE binhtv.dbamf_log_jobs
/
CREATE TABLE binhtv.dbamf_log_jobs
(id NUMBER,
name VARCHAR2(4000 BYTE),
status VARCHAR2(50 BYTE),
event_date DATE DEFAULT sysdate,
note VARCHAR2(2000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_LOG_JOBS
-- Start of DDL Script for Table BINHTV.DBAMF_LOG_OP
-- Generated 5-Nov-2020 16:12:34 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_LOG_OP
DROP TABLE binhtv.dbamf_log_op
/
CREATE TABLE binhtv.dbamf_log_op
(id NUMBER,
msg VARCHAR2(1000 BYTE),
event_datetime TIMESTAMP (6) DEFAULT sysdate,
type VARCHAR2(1000 BYTE),
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_LOG_OP
-- Start of DDL Script for Table BINHTV.DBAMF_LOG_WN
-- Generated 5-Nov-2020 16:12:37 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_LOG_WN
DROP TABLE binhtv.dbamf_log_wn
/
CREATE TABLE binhtv.dbamf_log_wn
(id NUMBER,
msg VARCHAR2(1000 BYTE),
event_date TIMESTAMP (6) DEFAULT sysdate,
type VARCHAR2(1000 BYTE),
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_LOG_WN
-- Start of DDL Script for Table BINHTV.DBAMF_TAB_CYCLE
-- Generated 5-Nov-2020 16:12:42 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_TAB_CYCLE
DROP TABLE binhtv.dbamf_tab_cycle
/
CREATE TABLE binhtv.dbamf_tab_cycle
(id NUMBER,
owner VARCHAR2(50 BYTE),
table_name VARCHAR2(200 BYTE),
purpose VARCHAR2(1000 BYTE),
cycle_days NUMBER,
rw_days NUMBER,
readonly_days NUMBER,
size_mb NUMBER,
num_rows NUMBER,
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_TAB_CYCLE
-- Start of DDL Script for Table BINHTV.DBAMF_CT_SQL
-- Generated 5-Nov-2020 16:12:06 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_SQL
DROP TABLE binhtv.dbamf_ct_sql
/
CREATE TABLE binhtv.dbamf_ct_sql
(inst_id NUMBER,
username VARCHAR2(30 BYTE),
sid NUMBER,
osuser VARCHAR2(30 BYTE),
machine VARCHAR2(64 BYTE),
event VARCHAR2(64 BYTE),
sql_id VARCHAR2(13 BYTE),
sql_text VARCHAR2(3000 BYTE),
logon_time DATE,
event_date DATE DEFAULT sysdate)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_SQL
-- Start of DDL Script for Table BINHTV.DBAMF_CT_SESSION
-- Generated 5-Nov-2020 16:12:01 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_SESSION
DROP TABLE binhtv.dbamf_ct_session
/
CREATE TABLE binhtv.dbamf_ct_session
(username VARCHAR2(50 BYTE),
status VARCHAR2(10 BYTE),
count_low VARCHAR2(5 BYTE),
count_avg VARCHAR2(5 BYTE),
count_high VARCHAR2(5 BYTE),
count_critical VARCHAR2(5 BYTE),
notes VARCHAR2(1000 BYTE),
event_date DATE DEFAULT sysdate,
count NUMBER)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_SESSION
-- Start of DDL Script for Table BINHTV.DBAMF_CT_OBJ_SIZE
-- Generated 5-Nov-2020 16:11:56 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_OBJ_SIZE
DROP TABLE binhtv.dbamf_ct_obj_size
/
CREATE TABLE binhtv.dbamf_ct_obj_size
(id NUMBER,
object_owner VARCHAR2(50 BYTE),
object_name VARCHAR2(200 BYTE),
size_gb NUMBER,
event_date DATE DEFAULT sysdate,
note VARCHAR2(1000 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_OBJ_SIZE
-- Start of DDL Script for Table BINHTV.DBAMF_CT_DISK_USAGE
-- Generated 5-Nov-2020 16:11:52 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CT_DISK_USAGE
DROP TABLE binhtv.dbamf_ct_disk_usage
/
CREATE TABLE binhtv.dbamf_ct_disk_usage
(host_name VARCHAR2(40 BYTE),
disk_name VARCHAR2(50 BYTE),
disk_size VARCHAR2(20 BYTE),
disk_free VARCHAR2(20 BYTE),
disk_percen VARCHAR2(10 BYTE),
disk_mount VARCHAR2(50 BYTE),
db_ip VARCHAR2(20 BYTE),
event_date DATE DEFAULT sysdate)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CT_DISK_USAGE
-- Start of DDL Script for Table BINHTV.DBAMF_CPU_MEM_USAGE
-- Generated 5-Nov-2020 16:11:45 from BINHTV@DBAVIET
-- Drop the old instance of DBAMF_CPU_MEM_USAGE
DROP TABLE binhtv.dbamf_cpu_mem_usage
/
CREATE TABLE binhtv.dbamf_cpu_mem_usage
(id NUMBER,
pct_usage NUMBER,
type VARCHAR2(50 BYTE),
event_date DATE DEFAULT sysdate,
note VARCHAR2(100 BYTE))
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
LOGGING
/
-- End of DDL Script for Table BINHTV.DBAMF_CPU_MEM_USAGE
*****
@ Trần Văn Bình - Founder of Oracle DBA AZ
#BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration
Hãy nhanh tay đăng ký khóa học "Quản trị cơ sở dữ liệu Oracle 12c cơ bản" trên Unica giành cho những bạn mới học cơ sở dữ liệu Oracle hoặc cần nâng cao kiến thức, kinh nghiệm thực tiễn trên Unica (giá gốc 900K, giá khi đăng ký HÔM NAY là 299K):
Bước 1: Truy cập Unica.vn, bấm ĐĂNG KÝ nếu chưa có tài khoản
Bước 2: Đăng nhập tài khoản học viên trên Unica.vn
Bước 4: Bấm KÍCH HOẠT NGAYđể thanh toán và bắt đầu vào học.
*****