Thứ Năm, 9 tháng 1, 2020

Hé lộ bí mật giám sát cơ sở dữ liệu chủ động

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.
Kết quả hình ảnh cho oracle tips

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

*****
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.

*****
@ 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

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master