ORACLE数据库日常维护手册

ORACLE 数据库日常维护手册 1 ORACLE 数据库日常维护手册数据库日常维护手册 1前言前言.2 2ORACLE 数据库每天应检查的项目数据库每天应检查的项目.2 2.1数据库基本状况检查.2 2.1.1检查Oracle的进程.2 2.1.2检查Oracle监听进程.3 2.1.3检查Oracle实例状态.4 2.1.4检查归档情况.4 2.2数据库日志文件检查.4 2.2.1检查Oracle日志文件.4 2.2.2检查Oracle核心转储目录.5 2.3数据库对象状态检查.5 2.3.1检查Oracle控制文件状态.5 2.3.2检查Oracle在线日志状态.6 2.3.3检查Oracle表空间的状态.6 2.3.4检查Oracle所有数据文件状态.6 2.3.5检查Oracle所有表、索引、存储过程、触发器、包等对象的状态.7 2.4数据库资源使用情况检查.7 2.4.1检查Oracle初始化文件中相关的参数值.7 2.4.2检查Oracle各个表空间的增长情况.8 2.4.3检查一些扩展异常的对象.9 2.4.4检查system表空间内的内容.9 2.4.5检查对象的下一扩展与表空间的最大扩展值.9 2.4.6数据库表空间使用情况监控.10 2.5检查数据库定时作业的完成情况.11 2.6数据库坏块的处理.11 2.7数据库备份情况检查.12 2.7.1检查备份软件的日志.12 2.7.2检查数据库归档日志.12 2.7.3控制文件的备份.12 2.8数据库连接情况检查.12 2.8.1检查数据库连接.12 2.8.2检查数据库会话.12 2.9操作系统相关检查.13 2.9.1检查空间使用情况.13 2.9.2检查网络状况.13 2.9.3检查内存使用情况.13 2.9.4检查磁盘I/O瓶颈.14 3ORACLE 数据库每周应检查的项目数据库每周应检查的项目.14 3.1查看是否有危害到安全策略的问题.14 3.2查看的网络日志.14 ORACLE 数据库日常维护手册 2 3.3将所有的警告日志存档.14 3.4查看 ORACLE 相关的网站，获取最新信息.14 4ORACLE 数据库每月应检查的项目数据库每月应检查的项目.15 4.1查看对数据库会产生危害的增长速度.15 4.2查看数据库调整产生的影响.15 4.3查看 I/O 的屏颈问题.15 5附：附：.17 5.1启动数据库.17 5.2关闭数据库.17 5.3启动监听.18 5.4关闭监听.19 ORACLE 数据库日常维护手册 3 1 前言前言 数据库系统往往是企业或组织信息系统中最重要的基础架构，一旦数据库系统不能正常运 行，那基于数据库的整个信息架构都会随之瘫痪，因此我们必须保证数据库系统持续的健 康的运行。 因此数据库管理员必须每天对数据库的运行状态,日志文件,备份情况,数据库的空间使用情 况,系统资源的使用情况进行检查,发现并解决可能存在的问题，将隐患消除在萌芽中。 本文列举了 ORACLE 数据库管理员每天，每周和每月应该检查的一些东西，希望对 DBA 有些帮助。 注：所有示例可能与客户的实际系统不尽相同。 2 ORACLE 数据库每天应检查的项目数据库每天应检查的项目 2.1 数据库基本状况检查数据库基本状况检查 2.1.1 检查检查 Oracle 的进程的进程 $ps ef|grep “ora_”|grep v grep oracle 6586 1 0 May 15 ? 0:00 ora_pmon_ORCL oracle 6598 1 0 May 15 ? 0:00 ora_snp0_ORCL oracle 6600 1 0 May 15 ? 0:00 ora_snp1_ORCL oracle 6594 1 0 May 15 ? 0:06 ora_smon_ORCL oracle 6604 1 0 May 15 ? 0:00 ora_snp3_ORCL oracle 6588 1 0 May 15 ? 6:50 ora_dbw0_ORCL oracle 6590 1 0 May 15 ? 37:48 ora_lgwr_ORCL oracle 6602 1 0 May 15 ? 0:00 ora_snp2_ORCL oracle 6606 1 0 May 15 ? 5:19 ora_arc0_ORCL oracle 6592 1 0 May 15 ? 0:45 ora_ckpt_ORCL oracle 6596 1 0 May 15 ? 0:01 ora_reco_ORCL . 在检查 Oracle 的进程命令输出后，输出显示至少应包括以下一些进程： Oracle 写数据文件的进程，输出显示为：ora_dbw0_ORCL。 Oracle 写日志文件的进程，输出显示为：ora_lgwr_ORCL。 Oracle 监听实例状态的进程，输出显示为：ora_smon_ORCL。 Oracle 进行检查点的进程，输出显示为：ora_ckpt_ORCL。 注：进程的后缀 ORCL 是指数据库的实例名，以上仅是示例，与客户系统可能不尽相同。 ORACLE 数据库日常维护手册 4 2.1.2 检查检查 Oracle 监听进程监听进程 $lsnrctl status LSNRCTL for Solaris: Version 9.2.0.4.0 - Production on 23-MAY-2005 13:12:09 (c) Copyright 2002 Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ipasdb)(PORT=1521) STATUS of the LISTENER - Alias LISTENER Version TNSLSNR for Solaris: Version 9.2.0.4.0 - Production Start Date 15-MAY-2005 22:05:58 Uptime 7 days 15 hr. 6 min. 11 sec Trace Level off Security OFF SNMP OFF Listener Parameter File /opt/oracle/db01/app/oracle/product/920/network/ admin/listener.ora Listener Log File /opt/oracle/db01/app/oracle/product/920/network/ log/listener.log Services Summary. ORCL has 1 service handler(s) 注：以上仅是示例，与客户系统可能不尽相同。 2.1.3 检查检查 Oracle 实例状态实例状态 $sqlplus system/manager sqlselect instance_name,version,status,database_status from v$instance; INSTANCE_NAME VERSION STATUS DATABASE_STATUS - - - - ORCL 9.2.0.4.0 OPEN ACTIVE 其中 STATUS 表示 Oracle 当前的实例状态，必须为 OPEN；DATABASE_STATUS 表示 Oracle 当前数据库的状态，必须为 ACTIVE。 sqlselect name,log_mode,open_mode from v$database; NAME LOG_MODE OPEN_MODE - - - ORACLE 数据库日常维护手册 5 ORCLARCHIVELOGREAD WRITE 2.1.4 检查归档情况检查归档情况 SQLarchive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /opt/oracle/arch/ORCL Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 Automatic archivalEnabled 表示归档进行是否自动启动 其中 LOG_MODE 表示 Oracle 当前的归档方式。ARCHIVELOG 表示数据库运行在归 档模式下，NOARCHIVELOG 表示数据库运行在非归档模式下。 2.2 数据库日志文件检查数据库日志文件检查 2.2.1 检查检查 Oracle 日志文件日志文件 $cat $ORACLE_BASE/admin/ORCL/bdump/alert_ORCL.log|grep -i ora- $cat $ORACLE_BASE/admin/ORCL/bdump/alert_ORCL.log|grep -i err $cat $ORACLE_BASE/admin/ORCL/bdump/alert_ORCL.log|grep -i fail Oracle 在运行过程中，会在警告日志文件(alert_SID.log)中记录数据库的一些运行情况： 数据库的启动、关闭，启动时的非缺省参数； 数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操 作没有执行完成造成不能切换，会记录不能切换的原因； 对数据库进行的某些操作，如创建或删除表空间、增加数据文件； 数据库发生的错误，如表空间不够、出现坏块、数据库内部错误(ORA600) DBA 应该定期检查日志文件，根据日志中发现的问题及时进行处理 问题 处理： 启动参数不对 检查初始化参数文件 因为检查点操作或归档操作没有完成造成重做日志不能切换 如果经常发生这样的情况， 可以考虑增加重做日志文件组；想办法提高检查点或归档操作的效率； 有人未经授权删除了表空间 检查数据库的安全问题，是否密码太简单；如有必要，撤 消某些用户的系统权限 出现坏块 检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对 象出现了坏块，对这个对象进行重建 表空间不够 增加数据文件到相应的表空间 出现 ORA-600 根据日志文件的内容查看相应的 TRC 文件，如果是 Oracle 的 bug，要 及时打上相应的补丁 ORACLE 数据库日常维护手册 6 2.2.2 检查检查 Oracle 核心转储目录核心转储目录 $ls $ORACLE_BASE/admin/ORCL/cdump/*.trc|wc -l $ls $ORACLE_BASE/admin/ORCL/udump/*.trc|wc l 如果上面命令的结果每天都在增长，则说明 Oracle 进程经常发生核心转储。这说明某 些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特 别是数据库后台进程的核心转储会导致数据库异常终止。 2.3 数据库对象状态检查数据库对象状态检查 2.3.1 检查检查 Oracle 控制文件状态控制文件状态 sqlselect * from v$controlfile; STATUS NAME - /opt/oracle/db02/oradata/ORCL/control01.ctl /opt/oracle/db03/oradata/ORCL/control02.ctl /opt/oracle/db04/oradata/ORCL/control03.ctl 2.3.2 检查检查 Oracle 在线日志状态在线日志状态 sqlselect * from v$logfile; GROUP# STATUS MEMBER - - 1 ONLINE/opt/oracle/db02/oradata/ORCL/redo01.log 2 ONLINE/opt/oracle/db03/oradata/ORCL/redo02.log 3 ONLINE/opt/oracle/db04/oradata/ORCL/redo03.log 输出结果应该有 3 条以上（包含 3 条）记录，STATUS 应该为非 INVALID，非 DELETED。 注：STATUS 显示为空表示正常。 2.3.3 检查检查 Oracle 表空间的状态表空间的状态 sqlselect tablespace_name,status from dba_tablespaces; ORACLE 数据库日常维护手册 7 TABLESPACE_NAME STATUS - - SYSTEM ONLINE TOOLS ONLINE RBS ONLINE TEMP ONLINE USERS ONLINE INDX ONLINE DRSYS ONLINE 其中 STATUS 应该都为 ONLINE 2.3.4 检查检查 Oracle 所有数据文件状态所有数据文件状态 sqlselect name,status from v$datafile; NAME STATUS - /opt/oracle/db02/oradata/ORCL/system01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/tools01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/rbs01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/temp01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/users01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/indx01.dbf ONLINE /opt/oracle/db02/oradata/ORCL/drsys01.dbf ONLINE /opt/oracle/wacos/oradata/ORCL/wacos01.dbf ONLINE /opt/oracle/nms/oradata/ORCL/data01.dbf ONLINE 输出结果中 STATUS 应该都为 ONLINE。 2.3.5 检查检查 Oracle 所有表、索引、存储过程、触发器、包等所有表、索引、存储过程、触发器、包等 对象的状态对象的状态 sqlselect owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM; no rows selected 如果有记录返回，则说明存在无效的对象。如果应用相关的对象。请首先以 sys 用户登录 sqlplus，执行$ORACLE_HOME/rdbms/admin/utlrp.sql 来重新编译这些对象。编译后 再执行上述语句来检查，如果仍有输出，请寻求技术支持。 ORACLE 数据库日常维护手册 8 2.4 数据库资源使用情况检查数据库资源使用情况检查 2.4.1 检查检查 Oracle 初始化文件中相关的参数值初始化文件中相关的参数值 SQL select resource_name,max_utilization,initial_allocation,limit_value from v$resource_limit; RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE - - - - processes 11 150 150 sessions 11 170 170 enqueue_locks 14 2230 2230 enqueue_resources 10 968 UNLIMITED ges_procs 0 0 0 ges_ress 0 0 UNLIMITED ges_locks 0 0 UNLIMITED ges_cache_ress 0 0 UNLIMITED ges_reg_msgs 0 0 UNLIMITED ges_big_msgs 0 0 UNLIMITED ges_rsv_msgs 0 0 0 RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE - - - - gcs_resources 0 3300 3300 gcs_shadows 0 3300 3300 dml_locks 3 748 UNLIMITED temporary_table_locks 0 UNLIMITED UNLIMITED transactions 4 187 UNLIMITED branches 0 187 UNLIMITED cmtcallbk 0 187 UNLIMITED sort_segment_locks 1 UNLIMITED UNLIMITED max_rollback_segments 11 38 38 max_shared_servers 0 20 20 parallel_max_servers 0 6 6 若 LIMIT_VALU-MAX_UTILIZATIONselect 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; TABLESPACE_NAME USED_PERCENT - - DRSYS 30. FS .19375 INDX . Data01 .0375 SYSTEM 34. TEMP 2. TOOLS . USERS . 1、 如果使用率 USED_PERCENT 在 90%以上，则注意要增加数据文件来扩展表空 间而不要是用数据文件的自动扩展功能。 2、 请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据 文件大小为 2G，自动扩展的最大限制在 4G； 3、 增加数据文件后，请检查热备脚本，是否会包含新增的数据文件； 2.4.3 检查一些扩展异常的对象检查一些扩展异常的对象 sqlselect 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; no rows selected 如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些 对象要修改它的存储结构参数。 2.4.4 检查检查 system 表空间内的内容表空间内的内容 sqlselect distinct(owner) from dba_tables ORACLE 数据库日常维护手册 10 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; no rows selected 如果记录返回，则表明 system 表空间内存在一些非 system 和 sys 用户的对象。请把这些对 象移到非 System 表空间，同时应该检查这些对象属主的缺省表空间值。 2.4.5 检查对象的下一扩展与表空间的最大扩展值检查对象的下一扩展与表空间的最大扩展值 sqlselect 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; no rows selected 如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展 值，需调整相应表空间的存储参数 2.4.6 数据库表空间使用情况监控数据库表空间使用情况监控 数据库运行了一段时间后，由于不断的在表空间上创建和删除对象，会在表空间上产生大 量的碎片，DBA 应该及时了解表空间的碎片和可用空间情况，以决定是否要对碎片进行整 理或为表空间增加数据文件。 select tablespace_name, count(*) chunks , max(bytes/1024/1024) max_chunk ORACLE 数据库日常维护手册 11 from dba_free_space group by tablespace_name; 上面的 SQL 列出了数据库中每个表空间的空闲块情况,如下所示（以下只是示例，与客户 数据库可能有出入）： TABLESPACE_NAME CHUNKS MAX_CHUNK - - - INDX 1 57. RBS 3 490. RMAN_TS 1 16. SYSTEM 1 207. TEMP 20 70. TOOLS 1 11. USERS 67 71. 其中，CHUNKS 列表示表空间中有多少可用的空闲块(每个空闲块是由一些连续的 Oracle 数据块组成)，如果这样的空闲块过多，比如平均到每个数据文件上超过了 100 个，那么该 表空间的碎片状况就比较严重了，可以尝试用以下的 SQL 命令进行表空间相邻碎片的接合： alter tablespace 表空间名 coalesce; 然后再执行查看表空间碎片的 SQL 语句，看表空间的碎片有没有减少。如果没有效果，并 且表空间的碎片已经严重影响到了数据库的运行，则考虑对该表空间进行重建。 MAX_CHUNK 列的结果是表空间上最大的可用块大小，如果该表空间上的对象所需分配的 空间(NEXT 值)大于可用块的大小的话，就会提示 ORA-1652、ORA-1653、ORA-1654 的错 误信息，DBA 应该及时对表空间的空间进行扩充，以避免这些错误发生。 对表空间的扩充对表空间的数据文件大小进行扩展，或向表空间增加数据文件，具体操作 见“存储管理”部份。 2.5 检查数据库定时作业的完成情况检查数据库定时作业的完成情况 a 如果数据库使用了 Oracle 的 JOB 来完成一些定时作业，要对这些 JOB 的运行情况进行检 查：select job,log_user,last_date,failures from dba_jobs; 如果 FAILURES 列是一个大于 0 的数的话，说明 JOB 运行失败，要进一步的检查。 2.6 数据库坏块的处理数据库坏块的处理 当 Oracle 数据库出现坏块时，Oracle 会在警告日志文件（alert_SID.log）中记录坏块的信 息： ORA-01578: ORACLE data block corrupted (file # 7, block # ) ORA-01110: data file : /oracle1/oradata/V920/oradata/V816/users01.dbf 其中，AFN代表坏块所在数据文件的绝对文件号，代表坏块是数据文件上的 第几个数据块。 出现这种情况时，应该首先检查是否是硬件及操作系统上的故障导致 Oracle 数据库出现坏 块。在排除了数据库以外的原因后，再对发生坏块的数据库对象进行处理。 1 确定发生坏块的数据库对象 SELECT tablespace_name,segment_type,owner,segment_name ORACLE 数据库日常维护手册 12 FROM dba_extents WHERE file_id = AND between block_id AND block_id+blocks-1; 2 决定修复方法 如果发生坏块的对象是一个索引，那么可以直接把索引 DROP 掉后，再根据表里的记录进 行重建； 如果发生坏块的表的记录可以根据其它表的记录生成的话，那么可以直接把这个表 DROP 掉后重建； 如果有数据库的备份，则恢复数据库的方法来进行修复； 如果表里的记录没有其它办法恢复，那么坏块上的记录就丢失了，只能把表中其它数据块 上的记录取出来，然后对这个表进行重建。 3 用 Oracle 提供的 DBMS_REPAIR 包标记出坏块 exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(,); 4 使用 Create table as select 命令将表中其它块上的记录保存到另一张表上 create table corrupt_table_bak as select * from corrupt_table; 5 用 DROP TABLE 命令删除有坏块的表 drop table corrupt_table; 6 用 alter table rename 命令恢复原来的表 alter table corrupt_table_bak rename to corrupt_table; 7 如果表上存在索引，则要重建表上的索引 2.7 数据库备份情况检查数据库备份情况检查 2.7.1 检查备份软件的日志检查备份软件的日志 如果采用了第三方的备份软件，如：verytas，HP Ominback 等，备份过程中都会有日志，查 看备份软件的日志，若有错误报告，判断错误类型，然后分别检查备份软件与 ORACLE 数据库。 2.7.2 检查数据库归档日志检查数据库归档日志 所有的归档日志都应是当天产生的， 如果存在更早的归档日志， 则说明前次的备份有问 题。 如果归档目录所在卷已满会导致数据库挂起。 ORACLE 数据库日常维护手册 13 2.7.3 控制文件的备份控制文件的备份 在数据库结构发生变化时，如增加了表空间，增加了数据文件或重做日志文件这些操作， 都会造成 Oracle 数据库控制文件的变化，DBA 应及进行控制文件的备份，备份方法是： 执行 SQL 语句： alter database backup controlfile to /home/backup/control.bak; 或： alter database backup controlfile to trace; 这样会在 USER_DUMP_DEST(初始化参数文件中指定)目录下生成创建控制文件的脚本。 2.8 数据库连接情况检查数据库连接情况检查 2.8.1 检查数据库连接检查数据库连接 在客户端用字符串方式连接数据库，测试网络连接 SQLconnect username/passwordstring Connected 2.8.2 检查数据库会话检查数据库会话 DBA 要定时对数据库的连接情况进行检查，看与数据库建立的会话数目是不是正常，如果 建立了过多的连接，会消耗数据库的资源。同时，对一些“挂死”的连接，可能会需要 DBA 手工进行清理。 以下的 SQL 语句列出当前数据库建立的