linux中oracle的日常维护命令

1.  检查Oracle的进程 
$  ps -ef|grep "ora_"|grep -v grep 
  oracle  5998     1  0 11:15:59 ?        0:01 ora_j000_PPRD10 
  oracle  2968     1  0 21:16:57 ?        0:00 ora_q000_PPRD10 
  oracle  2927     1  0 21:16:33 ?        0:00 ora_pmon_PPRD10 
  oracle  2933     1  0 21:16:34 ?        0:07 ora_dbw0_PPRD10 
  oracle  2945     1  0 21:16:34 ?        0:02 ora_mmon_PPRD10 
  oracle  2931     1  0 21:16:33 ?        0:00 ora_mman_PPRD10 
  oracle  2949     1  0 21:16:34 ?        0:00 ora_d000_PPRD10 
  oracle  2970     1  0 21:16:57 ?        0:00 ora_q001_PPRD10 
  oracle  2935     1  0 21:16:34 ?        0:05 ora_lgwr_PPRD10 
  oracle  2951     1  0 21:16:34 ?        0:00 ora_s000_PPRD10 
  oracle  2939     1  0 21:16:34 ?        0:06 ora_smon_PPRD10 
  oracle  2957     1  0 21:16:47 ?        0:00 ora_qmnc_PPRD10 
  oracle  2943     1  0 21:16:34 ?        0:05 ora_cjq0_PPRD10 
  oracle  2947     1  0 21:16:34 ?        0:00 ora_mmnl_PPRD10 
  oracle  2937     1  0 21:16:34 ?        0:18 ora_ckpt_PPRD10 
  oracle  2941     1  0 21:16:34 ?        0:00 ora_reco_PPRD10 
oracle  2929     1  0 21:16:33 ?        0:00 ora_psp0_PPRD10 

在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程： 
? Oracle写数据文件的进程，输出显示为：“ora_dbw0_ORCL” 
? Oracle写日志文件的进程，输出显示为：“ora_lgwr_ORCL” 
? Oracle监听实例状态的进程，输出显示为：“ora_smon_ORCL” 
? Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_ORCL” 
? Oracle进行归档的进程，输出显示为：“ora_arc0_ORCL” 
? Oracle进行检查点的进程，输出显示为：“ora_ckpt_ORCL” 
? Oracle进行[url=javascript:;]恢复[/url]的进程，输出显示为：“ora_reco_ORCL” 


2 .  查看数据库的实例： 
SQL> select instance_name,status,version,database_status from v$instance; 

INSTANCE_NAME  STATUS    VERSION     DATABASE_STATUS 
----------------        ------------   -----------------    ----------------- 
PPRD10           OPEN     10.2.0.4.0       ACTIVE 

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


3 . 查看数据库的日志模式，打开模式 
SQL> select name,log_mode,open_mode from v$database; 

NAME      LOG_MODE       OPEN_MODE 
---------      ------------          ---------- 
PPRD10    NOARCHIVELOG  READ WRITE   

其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。 

4 . 查看数据库alert_SID.log 中的ora-的错误信息： 
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i ora- 

WARNING: inbound connection timed out (ORA-3136) 
WARNING: inbound connection timed out (ORA-3136) 
ORA-12012: error on auto execute of job 78677 
ORA-20820: ORA-20820: 
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 912 
ORA-06512: at "PS_SYSTEM.ROW_VALIDATOR_T", line 924 
ORA-06512: at "WORKORDER.INVENTORY_UTL", line 1260 
ORA-12012: error on auto execute of job 78677 


4.1 查alert_SID.log中的err的信息： 
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i err 

Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_2666.trc: 
ORA-12012: error on auto execute of job 78677 
: 6000101: Error occurred when rolling Inventory date 
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_11886.trc: 
ORA-12012: error on auto execute of job 78677 
: 6000101: Error occurred when rolling Inventory date 
Errors in file /u01/app/oracle/admin/pinnsoft/bdump/pinnsoft_j000_21375.trc: 
ORA-12012: error on auto execute of job 78677 
: 6000101: Error occurred when rolling Inventory date 


4.2 查alert_SID.log的fail的信息： 
$ more /u01/app/oracle/admin/pinnsoft/bdump/alert_pinnsoft.log | grep -i fail 

PMON failed to acquire latch, see PMON dump 
PMON failed to acquire latch, see PMON dump 
PMON failed to acquire latch, see PMON dump 


5. 检查控制文件状态
SQL> select status,name from v$controlfile; 
STATUS  NAME 
-------    ------------------------------------------------------------------------ 
        /data/app/oracle/oradata/PPRD10/control01.ctl 
        /data/app/oracle/oradata/PPRD10/control02.ctl 
        /data/app/oracle/oradata/PPRD10/control03.ctl 

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

6.  查询日志状态 
SQL> select group#,status,member from v$logfile; 

GROUP#  STATUS  MEMBER 
----------    -------    ------------------------------------------------------------- 
           1         /data/app/oracle/oradata/PPRD10/redo01.log 
           2         /data/app/oracle/oradata/PPRD10/redo02.log 
           3         /data/app/oracle/oradata/PPRD10/redo03.log 

  输出结果应该有3条以上(包含 3 条)记录,”STATUS”应该为非”INVALID”，非“DELETED”。 

7 .  查询数据状态： 
SQL> select file#,status,name from v$datafile; 

FILE#  STATUS   NAME 
----------  -------    -------------------------------------------------- 
         1  SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf 
         2  ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf 
         3  ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf 
         4  ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf 
         5  ONLINE  /u01/app/oracle/oradata/orcl/example01.dbf 
         6  ONLINE  /u01/app/oracle/oradata/orcl/perfstat.dbf 
         7  ONLINE  /u01/app/oracle/oradata/orcl/risenet.dbf 

  “ONLINE"表示正常在线状态 

8 . 查询表空间状态 
SQL> select tablespace_name,status from dba_tablespaces; 

TABLESPACE_NAME           STATUS 
------------------------------     --------- 
SYSTEM                           ONLINE 
UNDOTBS1                       ONLINE 
SYSAUX                           ONLINE 
TEMP                              ONLINE 
USERS                            ONLINE 
EXAMPLE                         ONLINE 
PERFSTAT                        ONLINE 
RISENET                          ONLINE 

“ONLINE”表示正常在线状态 

9  . 检查Oracle所有回滚段的状态 

SQL> select segment_name,status from dba_rollback_segs; 

SEGMENT_NAME              STATUS 
------------------------------    ---------------- 
SYSTEM                           ONLINE 
_SYSSMU10$                    ONLINE 
_SYSSMU9$                      ONLINE 
_SYSSMU8$                      ONLINE 
_SYSSMU7$                      ONLINE 
_SYSSMU6$                      ONLINE 
_SYSSMU5$                      ONLINE 
_SYSSMU4$                      ONLINE 
_SYSSMU3$                      ONLINE 
_SYSSMU2$                      ONLINE 
_SYSSMU1$                      ONLINE 

11 rows selected. 

10 .检查Oracle初始化文件中相关的参数值 

SQL>select resource_name,current_utilization,max_utilization,initial_allocation  ,limit_value from v$resource_limit; 

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI 
------------------------------ ------------------- --------------- ---------- -- 
processes                                       26              31        150 
sessions                                        30              37        170 
enqueue_locks                                   23              31       2300 
enqueue_resources                               23              49        968  U 
ges_procs                                        0               0          0 
ges_ress                                         0               0          0  U 
ges_locks                                        0               0          0  U 
ges_cache_ress                                   0               0          0  U 
ges_reg_msgs                                     0               0          0  U 
ges_big_msgs                                     0               0          0  U 
ges_rsv_msgs                                     0               0          0 

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LI 
------------------------------ ------------------- --------------- ---------- -- 
gcs_resources                                    0               0          0 
gcs_shadows                                      0               0          0 
dml_locks                                        0              68        748  U 
temporary_table_locks                            0               3  UNLIMITED  U 
transactions                                     2              11        187  U 
branches                                         0               0        187  U 
cmtcallbk                                        0               2        187  U 
sort_segment_locks                               0               3  UNLIMITED  U 
max_rollback_segments                           11              11        187 
max_shared_servers                               1               1  UNLIMITED  U 
parallel_max_servers                             0               0         40 

22 rows selected. 


11. 检查Oracle各个表空间的增长情况 

Select  A.tablespace_name,(1-(A.total)/B.total)*100 used_percent 
from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B 
where A.tablespace_name=B.tablespace_name; 

SQL> Select  A.tablespace_name,(1-(A.total)/B.total)*100 used_percent 
  2  from (select tablespace_name,sum(bytes) total from dba_free_space group by tablespace_name) A, (select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) B 
where A.tablespace_name=B.tablespace_name; 
3 

TABLESPACE_NAME                USED_PERCENT 
------------------------------         ------------ 
UNDOTBS1                              33.25 
SYSAUX                               99.0364583 
RISENET                                .0125 
USERS                                   62.5 
SYSTEM                               98.6067708 
EXAMPLE                               68.25 
PERFSTAT                             28.7625 

7 rows selected. 

12 . 检查一些扩展异常的对象 
select segment_name,segment_type,tablespace_name, 
(extents/max_extents)*100 Percent from sys.DBA_segments 
where max_extents!=0 and (extents/max_extents)*100>=95 
order by percent; 

SQL> select segment_name,segment_type,tablespace_name, 
  2      (extents/max_extents)*100 Percent 
  3      from sys.DBA_segments 
  4      where max_extents!=0 and (extents/max_extents)*100>=95 
  5  order by percent; 

no rows selected 

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数. 

13  . 检查system表空间内的内容 
select distinct(owner) from dba_tables 
where tablespace_name='SYSTEM' and owner!='SYS' 
and owner!='SYSTEM' 
union 
select distinct(owner) from dba_indexes 
where tablespace_name='SYSTEM' 
and owner!='SYS' and owner!='SYSTEM'; 
SQL> select distinct(owner) from dba_tables 
  2  where tablespace_name='SYSTEM' and 
  3  owner!='SYS' and owner!='SYSTEM' 
  4  union 
  5  select distinct(owner) from dba_indexes 
  6  where tablespace_name='SYSTEM' and 
  7  owner!='SYS' and owner!='SYSTEM'; 

OWNER 
------------------------------ 
MDSYS 
OLAPSYS 
OUTLN 

  如果有记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值， 

14. 检查对象的下一扩展与表空间的最大扩展值 
select a.table_name,a.next_extent,a.tablespace_name 
from all_tables a, 
(select tablespace_name,max(bytes) as big_chunk 
from dba_free_space group by tablespace_name) f 
where f.tablespace_name=a.tablespace_name 
and a.next_extent>f.big_chunk 
union 
select a.index_name,a.next_extent,a.tablespace_name 
from all_indexes a, 
(select tablespace_name,max(bytes) as big_chunk 
from dba_free_space 
group by tablespace_name) f 
where f.tablespace_name=a.tablespace_name 
and a.next_extent>f.big_chunk; 

SQL> select a.table_name,a.next_extent,a.tablespace_name 
  2      from all_tables a, 
  3      (select tablespace_name,max(bytes) as big_chunk 
  4      from dba_free_space group by tablespace_name) f 
  5      where f.tablespace_name=a.tablespace_name 
  6      and a.next_extent>f.big_chunk 
  7      union 
  8      select a.index_name,a.next_extent,a.tablespace_name 
  9      from all_indexes a, 
10      (select tablespace_name,max(bytes) as big_chunk 
11      from dba_free_space 
12      group by tablespace_name) f 
13      where f.tablespace_name=a.tablespace_name 
14      and a.next_extent>f.big_chunk; 

no rows selected 

  如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数