oracle数据库日常维护（3）

3、检查Oracle对象状态

在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。

1.1. 检查Oracle控制文件状态

SQL> select status,name from v$controlfile;

 

STATUS  NAME

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

        /data/oradata/CKDB/control01.ctl

        /data/oradata/CKDB/control02.ctl

        /data/oradata/CKDB/control03.ctl

输出结果应该有3条以上（包含3条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常。

1.2. 检查Oracle在线日志状态

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

 

    GROUP# STATUS  TYPE    MEMBER

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

         3         ONLINE  /data/oradata/CKDB/redo03.log

         2         ONLINE  /data/oradata/CKDB/redo02.log

         1         ONLINE  /data/oradata/CKDB/redo01.log

         4         ONLINE  /data/oradata/CKDB/redo04.log

         5         ONLINE  /data/oradata/CKDB/redo05.log

         6         ONLINE  /data/oradata/CKDB/redo06.log

 

6 rows selected

输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 注：“STATUS”显示为空表示正常。

1.3. 检查Oracle表空间的状态

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

SYSAUX                         ONLINE

TEMP                           ONLINE

USERS                          ONLINE

SJ1                            ONLINE

ADM_INDEX                      ONLINE

HOME_DATA                      ONLINE

HOME_INDEX                     ONLINE

PHOTO_DATA                     ONLINE

PHOTO_INDEX                    ONLINE

。。。。。。。

输出结果中STATUS应该都为ONLINE。

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

SQL> select name,status from v$datafile;

 

NAME                                               STATUS

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

/data/oradata/CKDB/system01.dbf                    SYSTEM

/data/oradata/CKDB/undotbs01.dbf                   ONLINE

/data/oradata/CKDB/sysaux01.dbf                    ONLINE

/data/oradata/CKDB/users01.dbf                     ONLINE

/data/oradata/CKDB/sj.dbf                          ONLINE

/data/oradata/CKDB/HOME_DATA1.dbf                  ONLINE

/data/oradata/CKDB/HOME_INDEX1.dbf                 ONLINE

/data/oradata/CKDB/PHOTO_DATA1.dbf                 ONLINE

/data/oradata/CKDB/PHOTO_INDEX1.dbf                ONLINE

/data/oradata/CKDB/BLOG_DATA1.dbf                  ONLINE

/data/oradata/CKDB/BLOG_INDEX1.dbf                 ONLINE

/data/oradata/CKDB/AUDIO_DATA1.dbf                 ONLINE

/data/oradata/CKDB/AUDIO_INDEX1.dbf                ONLINE

/data/oradata/CKDB/VIDEO_DATA1.dbf                 ONLINE

/data/oradata/CKDB/VIDEO_INDEX1.dbf                ONLINE

/data/oradata/CKDB/SYS_DATA1.dbf                   ONLINE

/data/oradata/CKDB/SYS_INDEX1.dbf                  ONLINE

/data/oradata/CKDB/ADM_DATA1.dbf                   ONLINE

/data/oradata/CKDB/ADM_INDEX1.dbf                  ONLINE

/data/oradata/CKDB/perfstat.dbf                    ONLINE

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

SQL> select file_name,status from dba_data_files;

 

FILE_NAME                                     STATUS

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

/data/oradata/CKDB/users01.dbf                AVAILABLE

/data/oradata/CKDB/sysaux01.dbf               AVAILABLE

/data/oradata/CKDB/undotbs01.dbf              AVAILABLE

/data/oradata/CKDB/system01.dbf               AVAILABLE

/data/oradata/CKDB/sj.dbf                     AVAILABLE

/data/oradata/CKDB/perfstat.dbf               AVAILABLE

/data/oradata/CKDB/HOME_DATA1.dbf             AVAILABLE

/data/oradata/CKDB/HOME_INDEX1.dbf            AVAILABLE

/data/oradata/CKDB/PHOTO_DATA1.dbf            AVAILABLE

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

1.5. 检查无效对象

sql>select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';

 

no rows selected

如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者：

SELECT owner, object_name, object_type FROM dba_objects WHERE status= 'INVALID';

1.6. 检查所有回滚段状态

SQL> select segment_name,status from dba_rollback_segs;

 

SEGMENT_NAME                   STATUS

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

SYSTEM                         ONLINE

_SYSSMU1$                      ONLINE

_SYSSMU2$                      ONLINE

_SYSSMU3$                      ONLINE

_SYSSMU4$                      ONLINE

_SYSSMU5$                      ONLINE

_SYSSMU6$                      ONLINE

_SYSSMU7$                      ONLINE

_SYSSMU8$                      ONLINE

_SYSSMU9$                      ONLINE

_SYSSMU10$                     ONLINE

 

11 rows selected

输出结果中所有回滚段的“STATUS”应该为“ONLINE”。