linux中oracle的日常維護命令

1、.：.；1 檢查Oracle的進程oraclemzl $ ps -ef|grep ora_|grep -v greporacle 2791 1 0 17:22 ? 00:00:00 ora_pmon_ORCLoracle 2793 1 0 17:22 ? 00:00:00 ora_psp0_ORCLoracle 2795 1 0 17:22 ? 00:00:00 ora_mman_ORCLoracle 2797 1 0 17:22 ? 00:00:00 ora_dbw0_ORCL.在檢查Oracle的進程命令輸出后，輸出顯示至少應包括以下一些進程： Oracle寫數(shù)據(jù)文件的進程，輸出顯示為：“

2、ora_dbw0_ORCL Oracle寫日志文件的進程，輸出顯示為：“ora_lgwr_ORCL Oracle監(jiān)聽實例形狀的進程，輸出顯示為：“ora_smon_ORCL Oracle監(jiān)聽客戶端銜接進程形狀的進程，輸出顯示為：“ora_pmon_ORCL Oracle進展歸檔的進程，輸出顯示為：“ora_arc0_ORCL Oracle進展檢查點的進程，輸出顯示為：“ora_ckpt_ORCL Oracle進展恢復的進程，輸出顯示為：“ora_reco_ORCL2 查看數(shù)據(jù)庫的實例：SQL select instance_name,status,version,database_status

3、 from v$instance;INSTANCE_NAME STATUS VERSION DATABASE_STATUS- - - -ORCL OPEN 10.2.0.1.0 ACTIVE 其中“STATUS表示Oracle當前的實例形狀，必需為“OPEN；“DATABASE_STATUS表示Oracle當前數(shù)據(jù)庫的形狀，必需為“ACTIVE。3 查看數(shù)據(jù)庫的日志方式，翻開方式SQL select name,log_mode,open_mode from v$database;NAME LOG_MODE OPEN_MODE- - -ORCL ARCHIVELOG READ WRITE其中“L

4、OG_MODE表示Oracle當前的歸檔方式。“ARCHIVELOG表示數(shù)據(jù)庫運轉在歸檔方式下，“NOARCHIVELOG表示數(shù)據(jù)庫運轉在非歸檔方式下。在我們的系統(tǒng)中數(shù)據(jù)庫必需運行在歸檔方式下。4 查看數(shù)據(jù)庫alert_SID.log 中的ora-的錯誤信息：oraclemzl $ cat /u01/app/oracle/admin/orcl/bdump/alert_ORCL.log|grep -i ora-ORA-1126 signalled during: alter database archivelog .ORA-1126 signalled during: alter databas

5、e archivelog.ORA-01157: cannot identify/lock data file 4 - see DBWR trace fileORA-01110: data file 4: /u01/app/oracle/oradata/orcl/users01.dbf查alert_SID.log中的err的信息：oraclemzl $ cat $ORACLE_BASE/admin/orcl/bdump/alert_ORCL.log|grep -i errMem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.logMem# 0 er

6、rs 0: /u01/app/oracle/oradata/orcl/redo02.log查alert_SID.log的fail的信息：oraclemzl $ cat $ORACLE_BASE/admin/orcl/bdump/alert_ORCL.log|grep -i failPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dumpPMON failed to acquire latch, see PMON dump5檢查控制文件形狀：SQL select status,na

7、me from v$controlfile;STATUS NAME- - /u01/app/oracle/oradata/orcl/control01.ctl /u01/app/oracle/oradata/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control03.ctl輸出結果應該有3條以上包含3條的記錄，“STATUS應該為空。形狀為空表示控制文件形狀正常。6 查詢日志形狀SQL select group#,status,member from v$logfile; GROUP# STATUS MEMBER- - - 2 /u01/

8、app/oracle/oradata/orcl/redo02.log 1 STALE /u01/app/oracle/oradata/orcl/redo01.log 3 STALE /u01/app/oracle/oradata/orcl/redo03.log 輸 出結 果應該 有 3 條以上 包 含 3 條記 錄， “ STATUS 應該為 非 “ INVALID ， 非“DELETED。7 查詢數(shù)據(jù)形狀：SQL select file#,status,name from v$datafile; FILE# STATUS NAME- - - 1 SYSTEM /u01/app/oracle/

9、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/

10、oracle/oradata/orcl/risenet.dbf7 rows selected.“ONLINE表示正常在線形狀8 查詢表空間形狀：SQL select tablespace_name,status from dba_tablespaces;TABLESPACE_NAME STATUS- -SYSTEM ONLINEUNDOTBS1 ONLINESYSAUX ONLINETEMP ONLINEUSERS ONLINEEXAMPLE ONLINEPERFSTAT ONLINERISENET ONLINE8 rows selected.“ONLINE表示正常在線形狀9 檢查Oracle

11、一切回滾段的形狀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$ ONLINE11 rows selected.10 檢查Oracle初始化文件中相關的參數(shù)值SQ

12、L l1* select resource_name,current_utilization,max_utilization,initial_allocation,limit_value from v$resource_limitSQL /RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU- - - - -processes 20 30 150 150sessions 25 33 170 170enqueue_locks 14 22 2300 2300enqueue_resourc 14 37 968

13、UNLIMITEDesges_procs 0 0 0 0ges_ress 0 0 0 UNLIMITEDges_locks 0 0 0 UNLIMITEDges_cache_ress 0 0 0 UNLIMITEDges_reg_msgs 0 0 0 UNLIMITEDRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU- - - - -ges_big_msgs 0 0 0 UNLIMITEDges_rsv_msgs 0 0 0 0gcs_resources 0 0 0 0gcs_shadows 0 0

14、0 0dml_locks 0 44 748 UNLIMITEDtemporary_table 0 0 UNLIMITED UNLIMITED_lockstransactions 0 10 187 UNLIMITEDbranches 0 0 187 UNLIMITEDcmtcallbk 0 1 187 UNLIMITEDRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU- - - - -sort_segment_lo 0 1 UNLIMITED UNLIMITEDcksmax_rollback_se 11

15、 11 187 65535gmentsmax_shared_serv 1 1 UNLIMITED UNLIMITEDersparallel_max_se 0 2 40 3600rversRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU- - - - -22 rows selected.11 檢查Oracle各個表空間的增長情況SQL l1 select2 A.tablespace_name,(1-(A.total)/B.total)*100 used_percent3 from4 (select ta

16、blespace_name,sum(bytes) total5 from dba_free_space group by tablespace_name) A,6 (select tablespace_name,sum(bytes) total7 from dba_data_files group by tablespace_name) B8* where A.tablespace_name=B.tablespace_nameSQL /TABLESPACE_NAME USED_PERCENT- -UNDOTBS1 33.25SYSAUX 99.0364583RISENET .0125USERS

17、 62.5SYSTEM 98.6067708EXAMPLE 68.25PERFSTAT 28.76257 rows selected.12 檢查一些擴展異常的對象SQL select segment_name,segment_type,tablespace_name,2 (extents/max_extents)*100 Percent3 from sys.DBA_segments4 where max_extents!=0 and (extents/max_extents)*100=955 order by percent;no rows selected假設有記錄前往，那么這些對象的擴展曾

18、經快到達它定義時的最大擴展值。對于這些對象要修正它的存儲構造參數(shù).13 檢查system表空間內的內容SQL select distinct(owner) from dba_tables2 where tablespace_name=SYSTEM and3 owner!=SYS and owner!=SYSTEM4 union5 select distinct(owner) from dba_indexes6 where tablespace_name=SYSTEM and7 owner!=SYS and owner!=SYSTEM;OWNER-MDSYSOLAPSYSOUTLN假設有記錄前往，那么闡明system表空間內存在一些非system和sys用戶的對象。應該進一步檢查這些對象能否與我們運用相關。假設相關請把這些對象移到非System表空間，同時應該檢查