linux中oracle的日常维护命令

1检查Oracle的进程[oracle@mzl~]$ps-ef|grepora_|grep-vgreporacle27911017:22?00:00:00ora_pmon_ORCLoracle27931017:22?00:00:00ora_psp0_ORCLoracle27951017:22?00:00:00ora_mman_ORCLoracle27971017:22?00:00:00ora_dbw0_ORCL......在检查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进行恢复的进程，输出显示为：“ora_reco_ORCL”2查看数据库的实例：SQLselectinstance_name,status,version,database_statusfromv$instance;INSTANCE_NAMESTATUSVERSIONDATABASE_STATUS--------------------------------------------------------------ORCLOPEN10.2.0.1.0ACTIVE其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS”表示Oracle当前数据库的状态，必须为“ACTIVE”。3查看数据库的日志模式，打开模式SQLselectname,log_mode,open_modefromv$database;NAMELOG_MODEOPEN_MODE-------------------------------ORCLARCHIVELOGREADWRITE其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。4查看数据库alert_SID.log中的ora-的错误信息：[oracle@mzl~]$cat/u01/app/oracle/admin/orcl/bdump/alert_ORCL.log|grep-iora-ORA-1126signalledduring:alterdatabasearchivelog...ORA-1126signalledduring:alterdatabasearchivelog...ORA-01157:cannotidentify/lockdatafile4-seeDBWRtracefileORA-01110:datafile4:'/u01/app/oracle/oradata/orcl/users01.dbf'查alert_SID.log中的err的信息：[oracle@mzl~]$cat$ORACLE_BASE/admin/orcl/bdump/alert_ORCL.log|grep-ierrMem#0errs0:/u01/app/oracle/oradata/orcl/redo01.logMem#0errs0:/u01/app/oracle/oradata/orcl/redo02.log查alert_SID.log的fail的信息：[oracle@mzl~]$cat$ORACLE_BASE/admin/orcl/bdump/alert_ORCL.log|grep-ifailPMONfailedtoacquirelatch,seePMONdumpPMONfailedtoacquirelatch,seePMONdumpPMONfailedtoacquirelatch,seePMONdump5检查控制文件状态：SQLselectstatus,namefromv$controlfile;STATUSNAME---------------------------------------------------------/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查询日志状态SQLselectgroup#,status,memberfromv$logfile;GROUP#STATUSMEMBER-------------------------------------------------------------------2/u01/app/oracle/oradata/orcl/redo02.log1STALE/u01/app/oracle/oradata/orcl/redo01.log3STALE/u01/app/oracle/oradata/orcl/redo03.log输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。7查询数据状态：SQLselectfile#,status,namefromv$datafile;FILE#STATUSNAME-------------------------------------------------------------------1SYSTEM/u01/app/oracle/oradata/orcl/system01.dbf2ONLINE/u01/app/oracle/oradata/orcl/undotbs01.dbf3ONLINE/u01/app/oracle/oradata/orcl/sysaux01.dbf4ONLINE/u01/app/oracle/oradata/orcl/users01.dbf5ONLINE/u01/app/oracle/oradata/orcl/example01.dbf6ONLINE/u01/app/oracle/oradata/orcl/perfstat.dbf7ONLINE/u01/app/oracle/oradata/orcl/risenet.dbf7rowsselected.“ONLINE表示正常在线状态8查询表空间状态：SQLselecttablespace_name,statusfromdba_tablespaces;TABLESPACE_NAMESTATUS---------------------------------------SYSTEMONLINEUNDOTBS1ONLINESYSAUXONLINETEMPONLINEUSERSONLINEEXAMPLEONLINEPERFSTATONLINERISENETONLINE8rowsselected.“ONLINE”表示正常在线状态9检查Oracle所有回滚段的状态SQLselectsegment_name,statusfromdba_rollback_segs;SEGMENT_NAMESTATUS----------------------------------------------SYSTEMONLINE_SYSSMU10$ONLINE_SYSSMU9$ONLINE_SYSSMU8$ONLINE_SYSSMU7$ONLINE_SYSSMU6$ONLINE_SYSSMU5$ONLINE_SYSSMU4$ONLINE_SYSSMU3$ONLINE_SYSSMU2$ONLINE_SYSSMU1$ONLINE11rowsselected.10检查Oracle初始化文件中相关的参数值SQLl1*selectresource_name,current_utilization,max_utilization,initial_allocation,limit_valuefromv$resource_limitSQL/RESOURCE_NAMECURRENT_UTILIZATIONMAX_UTILIZATIONINITIAL_ALLIMIT_VALU---------------------------------------------------------------------processes2030150150sessions2533170170enqueue_locks142223002300enqueue_resourc1437968UNLIMITEDesges_procs0000ges_ress000UNLIMITEDges_locks000UNLIMITEDges_cache_ress000UNLIMITEDges_reg_msgs000UNLIMITEDRESOURCE_NAMECURRENT_UTILIZATIONMAX_UTILIZATIONINITIAL_ALLIMIT_VALU---------------------------------------------------------------------ges_big_msgs000UNLIMITEDges_rsv_msgs0000gcs_resources0000gcs_shadows0000dml_locks044748UNLIMITEDtemporary_table00UNLIMITEDUNLIMITED_lockstransactions010187UNLIMITEDbranches00187UNLIMITEDcmtcallbk01187UNLIMITEDRESOURCE_NAMECURRENT_UTILIZATIONMAX_UTILIZATIONINITIAL_ALLIMIT_VALU---------------------------------------------------------------------sort_segment_lo01UNLIMITEDUNLIMITEDcksmax_rollback_se111118765535gmentsmax_shared_serv11UNLIMITEDUNLIMITEDersparallel_max_se02403600rversRESOURCE_NAMECURRENT_UTILIZATIONMAX_UTILIZATIONINITIAL_ALLIMIT_VALU---------------------------------------------------------------------22rowsselected.11检查Oracle各个表空间的增长情况SQLl1select2A.tablespace_name,(1-(A.total)/B.total)*100used_percent3from4(selecttablespace_name,sum(bytes)total5fromdba_free_spacegroupbytablespace_name)A,6(selecttablespace_name,sum(bytes)total7fromdba_data_filesgroupbytablespace_name)B8*whereA.tablespace_name=B.tablespace_nameSQL/TABLESPACE_NAMEUSED_PE