ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 日常点检

日常点检

原创 Oracle 作者：winnzheng 时间：2023-09-08 14:38:42 0 删除 编辑


-- JOB 检查

select t.owner,

       t.job_name,

       t.status,

       to_char(t.actual_start_date, 'yyyy-mm-dd hh24:mi:ss'),

       t.additional_info,

       t.error#

  from dba_scheduler_job_run_details t

 where t.status = 'FAILED'  --job名称

 order by log_date desc;

 --------------------------------------------------------------

 

 

select t.owner,

       t.job_name,

       t.status,

       count(t.job_name) errorsum,

       to_char(max(t.actual_start_date), 'yyyy-mm-dd hh24:mi:ss') actual_start_date,

       t.error#,

       t.additional_info

  from dba_scheduler_job_run_details t

 where t.status = 'FAILED'

 and t.actual_start_date > sysdate - 1

 group by t.owner,

   t.job_name,

       t.status,

       t.additional_info,

       t.error#

order by actual_start_date desc;

 --------------------------------------------------------------

 

 

 

 

-- ALERT 检查当天报错

WITH diag_alert_ext AS

 (SELECT /*+ materialize */

   originating_timestamp,

   message_text

    FROM v$diag_alert_ext

   WHERE originating_timestamp > systimestamp - INTERVAL '1' DAY)

SELECT originating_timestamp,

       message_text

  FROM diag_alert_ext

 WHERE message_text LIKE '%Error%'

    OR message_text LIKE '%Fail%'

    OR message_text LIKE '%WARNING%'

    OR message_text LIKE '%Invalid%'

    OR message_text LIKE '%ORA-%'

    OR message_text LIKE '%Global Enqueue Services%'

    OR message_text LIKE '%dead%'

 ORDER BY originating_timestamp DESC;


-- RMAN 检查

set line222

set pagesize1000

col status for a10

col input_type for a20

col INPUT_BYTES_DISPLAY for a10

col OUTPUT_BYTES_DISPLAY for a10 

col TIME_TAKEN_DISPLAY for a10

select input_type,

       status,

       to_char(start_time,

               'yyyy-mm-dd hh24:mi:ss'),

       to_char(end_time,

               'yyyy-mm-dd hh24:mi:ss'),

       input_bytes_display,

       output_bytes_display,

       time_taken_display,

       COMPRESSION_RATIO

  from v$rman_backup_job_details

 order by 3 desc;

 

-- 表空间检查

set line222 

set pagesize1000

SELECT tablespace_name,

       round(used_space * 8 / 1024,

             2) used_space,

       round(tablespace_size * 8 / 1024,

             2) tablespace_size,

       round(used_percent,

             2) || '%' used_percents

  FROM dba_tablespace_usage_metrics

 ORDER BY used_percent DESC;

 

 

-- ASM 检查

set line222

select name,

       state,

       offline_disks,

       type,

       total_mb,

       free_mb,

       case

         when round((1 - (free_mb / total_mb)) * 100,

                    2) > 90 then

          to_char(round((1 - (free_mb / total_mb)) * 100,

                        2),

                  999.99)

         else

          to_char(round((1 - (free_mb / total_mb)) * 100,

                        2),

                  999.99)

       end as "%FULL"

  from v$asm_diskgroup;


-- Archive log 检查  

SELECT

    SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22

  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23

  , COUNT(*)                                                                      TOTAL

FROM

  v$log_history  a where SYSDATE - first_time < 35

GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) order by 1;



-- 临时表空间检查

select  df.tablespace_name "Tablespace",

       df.totalspace "Total(MB)",

       nvl(FS.UsedSpace, 0)  "Used(MB)",

       (df.totalspace - nvl(FS.UsedSpace, 0)) "Free(MB)",

       round(100 * (1-( nvl(fs.UsedSpace, 0) / df.totalspace)), 2) "Pct. Free(%)"

FROM  (SELECT tablespace_name, round(SUM(bytes) / 1048576) TotalSpace

        FROM   dba_TEMP_files

        GROUP  BY tablespace_name) df,

       (SELECT tablespace_name, ROUND(SUM(bytes_used) / 1024 / 1024)  UsedSpace

        FROM   gV$temp_extent_pool

        GROUP  BY tablespace_name) fs  WHERE  df.tablespace_name = fs.tablespace_name(+);


-- 检查主机磁盘空间

!df -Th



-- 分区表检查

SELECT t2.table_owner,

       t1.table_name,

       t1.max_partition_name,

       t2.high_value

  FROM (SELECT table_name,

               MAX(partition_name) AS max_partition_name

          FROM dba_tab_partitions

         GROUP BY table_name) t1,

       (SELECT table_owner,

               table_name,

               partition_name,

               high_value

          FROM dba_tab_partitions

         WHERE table_owner NOT IN ('SYS',

                                   'SYSAUX',

                                   'SYSTEM',

                                   'AUDSYS')) t2

 WHERE t1.table_name = t2.table_name

   AND t1.max_partition_name = t2.partition_name

 ORDER BY 1,

          2;




-- 控制文件检查

col name format a40

select name,status from v$controlfile;


-- TOP 10 物理读 SQL

col sql_text format a30

SELECT *

  FROM (SELECT disk_reads,

               username,

               parsing_user_id,

               sql_id,

   executions,

               elapsed_time / 1000000,

               sql_text

          FROM v$sql,

               dba_users

         WHERE user_id = parsing_user_id

         ORDER BY disk_reads DESC)

 WHERE rownum <= 10;

 

 

-- TOP 10 逻辑读 SQL

SELECT *

  FROM (SELECT buffer_gets,

               username,

               parsing_user_id,

               sql_id,

   executions,

               elapsed_time / 1000000,

               sql_text

          FROM v$sql,

               dba_users

         WHERE user_id = parsing_user_id

         ORDER BY buffer_gets DESC)

 WHERE rownum <= 10;


-- TOP 10 执行时间 SQL

SELECT *

  FROM (SELECT buffer_gets,

               username,

               parsing_user_id,

               sql_id,

   executions,

               elapsed_time / 1000000,

               sql_text

          FROM v$sql,

               dba_users

         WHERE user_id = parsing_user_id

         ORDER BY elapsed_time DESC)

 WHERE rownum <= 10;



-- 无效对象检查

set line222

set pagesize1000

COLUMN owner           FORMAT a85

COLUMN object_name     FORMAT a30

COLUMN object_type     FORMAT a20

COLUMN status          FORMAT a75

SELECT

    owner

  , object_name

  , object_type

  , DECODE(   status

            , 'VALID'

            , status

            , status) status

FROM dba_objects

WHERE status <> 'VALID'

ORDER BY

    owner

  , object_name;




-- 检查并行度

col table_name format a35

col index_name format a35

Rem Tables that have Indexes with not the same DOP

Rem !!!!! This command can take some time to execute !!!

Rem ---------------------------------------------------;

set lines 150

select substr(t.owner,1,15) Owner ,

t.table_name ,

substr(trim(t.degree),1,7) Degree ,

substr(trim(t.instances),1,9) Instances,

i.index_name ,

substr(trim(i.degree),1,7) Degree ,

substr(trim(i.instances),1,9) Instances

from all_indexes i,

all_tables t

where ( trim(i.degree) != trim(t.degree) or

trim(i.instances) != trim(t.instances) ) and

i.owner = t.owner and

i.table_name = t.table_name;





DG 检查


主库：

sql>archive log list




备库：

select process,status,thread#,sequence# from v$managed_standby;






来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70031606/viewspace-2981892/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 其他监控项及注意事项
下一篇： sqlplus 设置输出文件为不同的文件名称
请登录后发表评论 登录
全部评论
winnzheng
Happy --开心 Appreciation --心怀感恩 Passion --激情四溢 Performance --出类拔萃 Young --充满活力

注册时间：2023-09-04

  • 博文量
    30
  • 访问量
    24392

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593