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

　　

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

转载于:http://blog.itpub.net/22949165/viewspace-717625/