linux中oracle的日常维护命令
- 格式：docx
- 大小：18.51 KB
- 文档页数：7
1 检查Oracle的进程[oracle@mzl ~]$ 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写数据文件的进程，输出显示为：“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查看数据库的实例：SQL> select instance_name,status,version,database_status from v$instance;INSTANCE_NAME STATUS VERSION DATABASE_STATUS---------------- ------------ ----------------- -----------------ORCL OPEN 10.2.0.1.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--------- ------------ ----------ORCL ARCHIVELOG READ WRITE其中“LOG_MODE”表示Oracle当前的归档方式。
“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。
在我们的系统中数据库必须运行在归档方式下。
4 查看数据库alert_SID.log 中的ora-的错误信息：[oracle@mzl ~]$ 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 database 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的信息：[oracle@mzl ~]$ cat $ORACLE_BASE/admin/orcl/bdump/alert_ORCL.log|grep -i errMem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo01.logMem# 0 errs 0: /u01/app/oracle/oradata/orcl/redo02.log查alert_SID.log的fail的信息：[oracle@mzl ~]$ 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,name 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/app/oracle/oradata/orcl/redo02.log1 STALE /u01/app/oracle/oradata/orcl/redo01.log3 STALE /u01/app/oracle/oradata/orcl/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.dbf2 ONLINE /u01/app/oracle/oradata/orcl/undotbs01.dbf3 ONLINE /u01/app/oracle/oradata/orcl/sysaux01.dbf4 ONLINE /u01/app/oracle/oradata/orcl/users01.dbf5 ONLINE /u01/app/oracle/oradata/orcl/example01.dbf6 ONLINE /u01/app/oracle/oradata/orcl/perfstat.dbf7 ONLINE /u01/app/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所有回滚段的状态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初始化文件中相关的参数值SQL> 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 150 sessions 25 33 170 170 enqueue_locks 14 22 2300 2300 enqueue_resourc 14 37 968 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 0 0dml_locks 0 44 748 UNLIMITED temporary_table 0 0 UNLIMITED UNLIMITED_lockstransactions 0 10 187 UNLIMITED branches 0 0 187 UNLIMITED cmtcallbk 0 1 187 UNLIMITEDRESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU--------------- ------------------- --------------- ---------- ----------sort_segment_lo 0 1 UNLIMITED UNLIMITED cksmax_rollback_se 11 11 187 65535 gmentsmax_shared_serv 1 1 UNLIMITED UNLIMITED ersparallel_max_se 0 2 40 3600 rversRESOURCE_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 tablespace_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 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如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。
