• Oracle数据库日常巡检基本


    Oracle数据库日常巡检基本状况

    1.检查Oracle实例状态

    select instance_name,host_name,startup_time,status,database_status from v$instance;

    其中STATUS”表示Oracle当前的实例状态,必须为“OPEN”;“DATABASE_STATUS”表示Oracle当前数据库的状态,必须为“ACTIVE”。

    SQL> select instance_name,host_name,startup_time,status,database_status from v$instance; 

    INSTANCE_NAME

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

    HOST_NAME

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

    STARTUP_TIME STATUS                   DATABASE_STATUS

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

    orcl

    localhost.localdomain

    13-OCT-20    OPEN                     ACTIVE

    2.检查Oracle在线日志状态

    select group#,status,type,member from v$logfile; 

    输出结果应该有3条以上(包含3条)记录,“STATUS”应该为非“INVALID”,非“DELETED”。注:“STATUS”显示为空表示正常。

    SQL> select group#,status,type,member from v$logfile; 

        GROUP# STATUS         TYPE

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

    MEMBER

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

             3                ONLINE

    /data/oracle/oradata/orcl/redo03.log

             2                ONLINE

    /data/oracle/oradata/orcl/redo02.log 

             1                ONLINE

    /data/oracle/oradata/orcl/redo01.log

    3.v检查Oracle表空间的状态

    select tablespace_name,status from dba_tablespaces; 

    输出结果中STATUS应该都为ONLINE

    SQL> select tablespace_name,status from dba_tablespaces; 

    TABLESPACE_NAME                                              STATUS

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

    SYSTEM                                                       ONLINE

    SYSAUX                                                       ONLINE

    UNDOTBS1                                                     ONLINE

    TEMP                                                         ONLINE

    USERS                                                        ONLINE

    EXAMPLE                                                      ONLINE

    6 rows selected.

    4.检查Oracle所有数据文件状态

    select name,status from v$datafile;

    输出结果中STATUS”应该都为“ONLINE”。或者”SYSTEM”。

    select file_name,status from dba_data_files; 

    输出结果中STATUS”应该都为“AVAILABLE”。 

    SQL> select name,status from v$datafile; 

    NAME

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

    STATUS

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

    /data/oracle/oradata/orcl/system01.dbf

    SYSTEM

    /data/oracle/oradata/orcl/sysaux01.dbf

    ONLINE

    /data/oracle/oradata/orcl/undotbs01.dbf

    ONLINE

    NAME

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

    STATUS

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

    /data/oracle/oradata/orcl/users01.dbf

    ONLINE 

    /data/oracle/oradata/orcl/example01.dbf

    ONLINE

    5.检查表空间使用情况

    select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",

    round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"

    from

    (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,

    (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b

        where     a.tablespace_name=b.tablespace_name

          order by ((a.bytes-b.bytes)/a.bytes) desc;

    输出结果中tablespace_name=xxxx(表名)的记录”free MB”应该在1024以上

    SQL> select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",

      2  round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"

      3  from

      4  (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,

      5  (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b

      6      where     a.tablespace_name=b.tablespace_name

      7        order by ((a.bytes-b.bytes)/a.bytes) desc;

    TABLESPACE_NAME                                                  Sum MB

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

       used MB    free MB percent_used

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

    SYSTEM                                                              670

      667.5625     2.4375        99.64 

    SYSAUX                                                              500

      467.9375    32.0625        93.59

    UNDOTBS1                                                             60

        54.375      5.625        90.63

    TABLESPACE_NAME                                                  Sum MB

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

       used MB    free MB percent_used

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

    USERS                                                                 5

        4.0625      .9375        81.25

    EXAMPLE                                                             100

       78.4375    21.5625        78.44

    蓦然回首,那人却在,灯火阑珊处。
  • 相关阅读:
    VBA值列选取与复制,赋值
    Processing的条件式
    VBA之四给程序自动加行号
    自上而下的语法分析
    Processing绘制四边形
    Processing的代码编写流程
    Processing编程语言简介
    follow集的求解
    Processing函数与循环
    在UBUNTU下用ruby求得网卡地址IP地址和用户名
  • 原文地址:https://www.cnblogs.com/linux-186/p/13809822.html
Copyright © 2020-2023  润新知