Oracle11G日常维护手册

1、Oracle Database 日常维护手册目录1.登陆到数据库31.1.服务器端配置Listener31.2.客户端tnsnames41.3.检查Oracle Listener51.4.登陆数据库的方式51.5.数据库的启动51.6.关闭数据库62.用户管理62.1.检察用户profile62.2.查看用户profile参数63.检查数据库基本状况73.1.检查数据库创建日期73.2.检查数据库版本信息73.3.检查实例状态73.4.查看前台进程73.5.查看数据库连接的session83.6.查看连接到数据库的模式83.7.查看并发连接数83.8.查看最大的连接processes83.9.

2、监控系统后台进程83.10.查看数据库初始化参数83.11.检查PGA使用情况83.12.检查SGA状态83.13.检查Oracle服务进程83.14.检查Oracle监听状态93.15.检查监听进程是否存在103.16.检查操作系统日志文件103.17.检查oracle日志文件103.18.检查Oracle核心转储目录103.19.检查Root用户和Oracle用户的email114.检查Oracle对象状态114.1.检查Oracle控制文件状态114.2.检查Oracle在线日志状态114.3.检查Oracle表空间的状态124.4.检查Oracle所有数据文件状态124.5.检查无效对

3、象124.6.检查所有回滚段状态134.7.检查用户下的表134.8.检查用户默认表空间134.9.检查当前用户角色及权限134.10.检查用户下的各个表的大小134.11.检查一个表的创建时间134.12.检查某个表的大小134.13.检查每个表占用磁盘空间情况145.检查Oracle相关资源的使用情况145.1.检查Oracle初始化文件中相关参数值145.2.检查数据库连接情况155.3.检查系统磁盘空间165.4.检查表空间使用情况165.5.检查一些扩展异常的对象185.6.检查表空间碎片情况185.7.检查system表空间内的内容185.8.检查对象的下一扩展与表空间的最大扩展值

4、195.9.检查flash recovery area空间196.检查Oracle数据库性能196.1.查询表空间读写情况196.2.查询redo log buffer的繁忙程度206.3.判断undo表空间的使用情况206.4.分析日志组切换频率216.5.查看等待事件216.6.检查数据库cpu、I/O、内存性能226.7.内存使用情况226.8.系统I/O情况226.9.系统负载情况236.10.查看是否有僵死进程236.11.检查缓冲区命中率236.12.检查共享池命中率246.13.检查排序区246.14.检查日志缓冲区246.15.检查失效的索引246.16.检查不起作用的约束24

5、6.17.检查无效的trigger256.18.检查尚未建立索引的表256.19.检查运行时间长的SQL256.20.检查性能差的前10条SQL256.21.查看占 io 较大的正在运行的 session256.22.检查消耗CPU最高的PID对应的SQL256.23.检查占用CPU多的session266.24.检查表空间的IO266.25.检查临时表空间IO266.26.检查锁和等待267.检查数据库安全性277.1.检查系统安全日志信息277.2.检查登录失败的日志：277.3.检查用户修改密码278.数据表空间日常维护278.1.查看表空间的一些信息278.2.创建表空间288.3.表

6、空间扩容288.4.创建大数据文件298.5.数据表空间文件迁移298.6.不停机移动表空间文件309.存储过程管理309.1.找出特定用户的存储过程309.2.通过表名找出存储过程309.3.查看存储过程内容3010.触发器管理3010.1.找出数据库中所有触发器3010.2.找出特定用户的触发器3110.3.找出当前用户定义的触发器3110.4.查看某个用户自定义的触发器内容3110.5.查看某个表关联的触发器3110.6.查看当前用户所有触发器及存储过程3111.Redo Log 管理3111.1.离线迁移日志文件3111.2.在线更改Redo Log文件容量321. 登陆到数据库1.1

7、. 服务器端配置ListenerLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY =racdb1) (ADDRESS = (PROTOCOL = TCP)(HOST = 0)(PORT = 1521) ) )ADR_BASE_LISTENER = /u01/app/oracleSID_LIST_LISTENER= (SID_LIST= (SID_DESC=#BEQUEATH CONFIG (GLOBAL_DBNAME=racdb1) (SID_NAME=racdb

8、1) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1) #PRESPAWN CONFIG (PRESPAWN_MAX=20) (PRESPAWN_LIST= (PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1) ) ) )GLOBAL_DBNAME=racdb1Service 名称，在客户端一定配置和他相同SID_NAME=racdb1实例名称，这个要和SID相同GLOBAL_DBNAME可以不等于SID_NAME客户端根据tnsname.ora中的SERVICE_NAME和地址(A

9、DDRESS = (PROTOCOL = TCP)(HOST =racdb1)(PORT = 1521)，到这个地址去访问监听器。然后监听器根据文件lisnter.ora文件中的GLOBAL_NAME来判断是否有一个 GLOBAL_DBNAME 和 SERVICE_NAME 相等。如果相等，则建立客户端到SID标识的服务端实例的连接,在客户端上我们可以使用tnsping 命令来测试1.2. 客户端tnsnames racdb1 = (description = (address_list = (address = (protocol = tcp)(host = 0)(

10、port = 1521) ) (connect_data = (service_name =racdb1)(ur=a) ) )这里的service_name =racdb1 就是在服务器端的GLOBAL_DBNAME=racdb1oracleracdb1 $ tnsping racdb1TNS Ping Utility for Linux: Version .0 - Production on 21-JAN-2015 14:51:49Copyright (c) 1997, 2009, Oracle. All rights reserved.TNS-03502: Insuffic

11、ient arguments. Usage: tnsping oracleracdb1 $ tnsping racdb1TNS Ping Utility for Linux: Version .0 - Production on 21-JAN-2015 14:51:55Copyright (c) 1997, 2009, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (description = (ad

12、dress_list = (address = (protocol = tcp)(host = racdb1)(port = 1521) (connect_data = (service_name = racdb1)(ur=a)OK (0 msec)1.3. 检查Oracle Listenerlsnrctl stoplsnrctl startlsnrctl statuslsnrctl service1.4. 登陆数据库的方式修改登陆oracle 认证模式默认情况下我们oracle 安装好后是使用操作系统用户的验证,所以这里如果我们使用sys用户不用密码就可以登录,如果我们想使用oracle 密

13、码文件验证的话我们就要进入下列文件夹cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin修改sqlnet.ora增加下列命令SQLNET.AUTHENTICATION_SERVICES = NONE配置了tnsnames登录数据库方式oracleracdb1 $ sqlplus scott/111111racdb1sqlplus /nolog 使用scott登陆到指定数据库racdb1conn sys/111111racdb1 AS SYSDBA; 察看登陆到了哪个数据库实例select instance_name from v$in

14、stance使用sys用户登陆conn sys/change_on_installer as sysdba 用sysdba 登陆conn /as sysdba 使用sys用户登录conn sys/change_on_install as sysdba; conn / as sysdba 连接数据库conn scott/111111 使用scott进行连接1.5. 数据库的启动数据库启动方式方式含义startup启动实例、装载数据库、打开数据库startup nomount启动实例，不加载数据库startup mount启动实例，加载数据库但不打开数据库startup restrict启动过程中

15、限制访问数据库startup force强制数据库启动startup pfile=/oracle/app/oracle/product/10g/dbs/initminos.ora使用非缺省参数文件启动数据库，以特定文件中指定参数启动数据库,本例为”/oracle/app/oracle/product/11g/dbs/initminos.orastartup 启动实例、装载数据库、打开数据库startup open racdb1startup nomount启动数据库实例, 该步骤只是启动了一个数据库实例.在此状态下我们可以访问下列结构文件Select * from v$instance;Sel

16、ect * from v$bgprocess;Select * from v$sga;利用以前读取的参数文件查找控制文件,这些控制文件包含数据文件名和重做日志名,然后将数据库装载.alter database mount一旦这一步完成我们就可以看到下列状态select * from v$database;select * from v$tablespace;select * from v$log;实例验证数据文件及日志文件并启动数据库alter database open;打开数据库startup restrict启动过程中限制访问数据库alter system enable restrict

17、ed sessiongrant restrict session to scott上述命令是只有用户拥有restrict 权限才可以连接启动数据库后起的进程select name, DESCRIPTION from v$bgprocess where paddr00;1.6. 关闭数据库shutdown normal 等待用户完成工作然后关闭Shutdown transactional 等待用户完成工作但是强制关闭连接shutdown immediate 立即关闭 ,当前所有做工作回滚到一致状态,断开其连接shutdown abort 强制关闭,非干净关闭,下次重起后要回滚日志2. 用户管理2

18、.1. 检察用户profileselect username,profile from dba_users where username=SCOTT;2.2. 查看用户profile参数select * from dba_profiles where profile=DEFAULT;3. 检查数据库基本状况3.1. 检查数据库创建日期Select Created, Log_Mode From V$Database;CREATED LOG_MODE- -2014/8/17 1 NOARCHIVELOG3.2. 检查数据库版本信息Select version from Product_compon

19、ent_version where SUBSTR(PRODUCT,1,6)=Oracle;3.3. 检查实例状态 SQL SELECT inst_id, instance_name, host_name, VERSION, TO_CHAR (startup_time, yyyy-mm-dd hh24:mi:ss) startup_time, status, archiver, database_status FROM gv$instance;SELECT inst_id, dbid, NAME, TO_CHAR (created, yyyy-mm-dd hh24:mi:ss) created,

20、 log_mode, TO_CHAR (version_time, yyyy-mm-dd hh24:mi:ss) version_time,open_mode FROM gv$database;INST_ID DBID NAME CREATED LOG_MODE VERSION_TIME OPEN_MODE- - - - - - -1 94264408 RACDB1 2014-08-17 16:34:32 NOARCHIVELOG 2014-08-17 16:34:32 READ WRITE其中“STATUS”表示Oracle当前的实例状态，必须为“OPEN”；“DATABASE_STATUS

21、”表示Oracle当前数据库的状态，必须为“ACTIVE”。SQL select name,log_mode,open_mode from v$database;NAME LOG_MODE OPEN_MODE- - -RACDB1 ARCHIVELOG READ WRITE其中“LOG_MODE”表示Oracle当前的归档方式。“ARCHIVELOG”表示数据库运行在归档模式下，“NOARCHIVELOG”表示数据库运行在非归档模式下。在我们的系统中数据库必须运行在归档方式下。3.4. 查看前台进程ps -C oracle -o rsz,sid,cmd|grep LO free -m|egre

22、p -v total|buffers|awk print $1,$3 Oracle 的前台进程是操作系统进程，它和oracle session 一一对应，官方建议，oracle sesson=1.5*process +203.5. 查看数据库连接的sessionSQL select count(*) from v$session;3.6. 查看连接到数据库的模式select program from v$process order by program;select d.NAME,s.NAME from v$dispatcher d,v$shared_server s,v$circuit c

23、where d.PADDR=c.DISPATCHER and s.PADDR=c.SERVER(S012)以s001等就是共享模式连接3.7. 查看并发连接数select count(*) from v$session where status=ACTIVE;3.8. 查看最大的连接processesSQL select value from v$parameter where name = processes3.9. 监控系统后台进程SQL Select name,Description From V$BGPROCESS Where Paddr00;3.10. 查看数据库初始化参数selec

24、t name,value from v$parameter where isbasic=TRUE order by name;这些参数是当前数据库已经应用的参数3.11. 检查PGA使用情况select name,value from v$pgastat where name in (maximum PGA allocated,total PGA allocated);3.12. 检查SGA状态SELECT request_misses, request_failures FROM v$shared_pool_reserved;Select component,current_size,min

25、_size,max_size from v$sga_dynamic_components;REQUEST_MISSES REQUEST_FAILURES- - 0 0期望结果：request_misses和request_failures应该接近于0。巡检说明：request_misses是保留列表没有满足请求的可用内存片从而开始利用LRU列表刷新对象的次数；request_failures是未找到满足请求的内存次数。3.13. 检查Oracle服务进程oracleracdb1 $ ps -ef|grep ora_|grep -v grep&ps -ef|grep ora_|grep -v g

26、rep|wc ?loracle 4459 1 0 15:53 ? 00:00:00 ora_pmon_racdb1oracle 4461 1 0 15:53 ? 00:00:17 ora_vktm_racdb1oracle 4465 1 0 15:53 ? 00:00:00 ora_gen0_racdb1oracle 4467 1 0 15:53 ? 00:00:00 ora_diag_racdb1oracle 4469 1 0 15:53 ? 00:00:00 ora_dbrm_racdb1oracle 4471 1 0 15:53 ? 00:00:00 ora_psp0_racdb1ora

27、cle 4473 1 0 15:53 ? 00:00:02 ora_dia0_racdb1oracle 4475 1 0 15:53 ? 00:00:00 ora_mman_racdb1oracle 4477 1 0 15:53 ? 00:00:00 ora_dbw0_racdb1oracle 4479 1 0 15:53 ? 00:00:01 ora_lgwr_racdb1oracle 4481 1 0 15:53 ? 00:00:05 ora_ckpt_racdb1oracle 4483 1 0 15:53 ? 00:00:00 ora_smon_racdb1oracle 4527 1 0

28、 15:53 ? 00:00:00 ora_arc0_racdb1oracle 4485 1 0 15:53 ? 00:00:00 ora_reco_racdb1在检查Oracle的进程命令输出后，输出显示至少应包括以下一些进程： . Oracle写数据文件的进程，输出显示为：“ora_dbw0_racdb1” . Oracle写日志文件的进程，输出显示为：“ora_lgwr_racdb1” . Oracle监听实例状态的进程，输出显示为：“ora_smon_racdb1” . Oracle监听客户端连接进程状态的进程，输出显示为：“ora_pmon_racdb1” . Oracle进行归档的

29、进程，输出显示为：“ora_arc0_racdb1” . Oracle进行检查点的进程，输出显示为：“ora_ckpt_racdb1”. Oracle进行恢复的进程，输出显示为：“ora_reco_racdb1”3.14. 检查Oracle监听状态oracleracdb1 $ lsnrctl statusLSNRCTL for Linux: Version .0 - Production on 21-JAN-2015 17:00:42Copyright (c) 1991, 2009, Oracle. All rights reserved.Connecting to (DESC

30、RIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=racdb1)STATUS of the LISTENER-Alias LISTENERVersion TNSLSNR for Linux: Version .0 - ProductionStart Date 21-JAN-2015 12:37:48Uptime 0 days 4 hr. 22 min. 53 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracl

31、e/product/11.2.0/dbhome_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/racdb1/listener/alert/log.xmlListening Endpoints Summary. (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=racdb1) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0)(PORT=1521)Services Summary.Service r

32、acdb1 has 1 instance(s). Instance racdb1, status UNKNOWN, has 3 handler(s) for this service.Service has 1 instance(s). Instance racdb1, status READY, has 1 handler(s) for this service.Service racdb1XDB has 1 instance(s). Instance racdb1, status READY, has 1 handler(s) for this service.The command co

33、mpleted successfully“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“racdb1XDB”这一项。3.15. 检查监听进程是否存在oracleracdb1 $ ps -ef|grep lsn|grep -v greporacle 2140 1 0 12:37 ? 00:00:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit3.16. 检查操作系统日志文件rootracdb1 # cat /var/log/messages

34、 |grep failed查看是否有与Oracle用户相关的出错信息。3.17. 检查oracle日志文件SQL select value from v$diag_info where name=Diag Trace; VALUE-/u01/app/oracle/diag/rdbms/racdb1/racdb1/tracecat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log | grep ora-cat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_ra

35、cdb1.log | grep erroracleracdb1 trace$ cat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log | grep failOracle在运行过程中，会在警告日志文件(alert_SID.log)中记录数据库的一些运行情况：数据库的启动、关闭，启动时的非缺省参数；数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换，会记录不能切换的原因；对数据库进行的某些操作，如创建或删除表空间、增加数据文件；数据库发生的错误，如表空间不

36、够、出现坏块、数据库内部错误(ORA600)等。定期检查日志文件，根据日志中发现的问题及时进行处理：问题处理启动参数不对检查初始化参数文件因为检查点操作或归档操作没有完成造重做日志不能切换如果经常发生这样的情况，可以考虑增加重做日日志文件组；想办法提高检查点或归档操作的效率；有人未经授权删除了表空间检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限出现坏块检查是否是硬件问题(如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建表空间不够增加数据文件到相应的表空间出现ORA-600根据日志文件的内容查看相应的TRC文件，如果是Oracle的bug，

37、要及时打上相应的补丁3.18. 检查Oracle核心转储目录SQL select value from v$parameter where name=core_dump_dest;VALUE-/u01/app/oracle/diag/rdbms/racdb1/racdb1/cdumporacleracdb1 cdump$ ls /u01/app/oracle/diag/rdbms/racdb1/racdb1/cdump/*.trc|wc -l如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特

38、别是数据库后台进程的核心转储会导致数据库异常终止。Oracle 常用日志文件位置查询select * from v$parameter where name=background_dump_dest;select * from v$parameter where name=user_dump_dest;select * from v$parameter where name=core_dump_dest;select * from v$parameter where name=audit_file_dest;select * from v$parameter where name=audit_

39、syslog_level;3.19. 检查Root用户和Oracle用户的emailoracleracdb1 racdb1$ tail -n 200 /var/mail/oracle oracleracdb1 racdb1$ tail -n 200 /var/mail/root查看有无与Oracle用户相关的出错信息。4. 检查Oracle对象状态在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracl

40、e所有回滚段的状态，总共六个部分。4.1. 检查Oracle控制文件状态SQL select status,name from v$controlfile;STATUS NAME- - /u01/app/oracle/oradata/racdb1/control01.ctl /u01/app/oracle/flash_recovery_area/racdb1/control02.ctl输出结果应该有2条以上（包含2条）的记录，“STATUS”应该为空。状态为空表示控制文件状态正常4.2. 检查Oracle在线日志状态SQL select group#,status,type,member fr

41、om v$logfile; GROUP# STATUS TYPE MEMBER- - - - 3 ONLINE /u01/app/oracle/oradata/racdb1/redo03.log 2 ONLINE /u01/app/oracle/oradata/racdb1/redo02.log 1 ONLINE /u01/app/oracle/oradata/racdb1/redo01.log 输出结果应该有3条以上（包含3条）记录，“STATUS”应该为非“INVALID”，非“DELETED”。 注：“STATUS”显示为空表示正常。4.3. 检查Oracle表空间的状态SQL sele

42、ct tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS- -SYSTEM ONLINESYSAUX ONLINEUNDOTBS1 ONLINETEMP ONLINEUSERS ONLINESCOTTSPACE06 ONLINE 6 rows selected输出结果中STATUS应该都为ONLINE。4.4. 检查Oracle所有数据文件状态SQL select name,status from v$datafile; NAME STATUS- -/u01/app/oracle/oradata/racdb1/

43、system01.dbf SYSTEM/u01/app/oracle/oradata/racdb1/sysaux01.dbf ONLINE/u01/app/oracle/oradata/racdb1/undotbs01.dbf ONLINE/u01/app/oracle/oradata/racdb1/users01.dbf ONLINE/u01/app/oracle/oradata/racdb1/SCOTTSPACE06.dbf ONLINE输出结果中“STATUS”应该都为“ONLINE”。或者：SQL select file_name,status from dba_data_files;

44、 FILE_NAME STATUS- -/u01/app/oracle/oradata/racdb1/users01.dbf AVAILABLE/u01/app/oracle/oradata/racdb1/undotbs01.dbf AVAILABLE/u01/app/oracle/oradata/racdb1/sysaux01.dbf AVAILABLE/u01/app/oracle/oradata/racdb1/system01.dbf AVAILABLE/u01/app/oracle/oradata/racdb1/SCOTTSPACE06.dbf AVAILABLE输出结果中“STATU

45、S”应该都为“AVAILABLE”。4.5. 检查无效对象select owner,object_name,object_type from dba_objects where status!=VALID and owner!=SYS and owner!=SYSTEM; no rows selecte如果有记录返回，则说明存在无效对象。若这些对象与应用相关，那么需要重新编译生成这个对象，或者SELECT owner, object_name, object_type FROM dba_objects WHERE status= INVALID;4.6. 检查所有回滚段状态SQL select

46、 segment_name,status from dba_rollback_segs; SEGMENT_NAME STATUS- -SYSTEM ONLINE_SYSSMU10_3550978943$ ONLINE_SYSSMU9_1424341975$ ONLINE_SYSSMU8_2012382730$ ONLINE_SYSSMU7_3286610060$ ONLINE_SYSSMU6_2443381498$ ONLINE_SYSSMU5_1527469038$ ONLINE_SYSSMU4_1152005954$ ONLINE_SYSSMU3_2097677531$ ONLINE_SYSSMU2_2232571081$ ONLINE_SYSSMU1_3780397527$ ONLINE 11 rows selected输出结果中所有回滚段的“STATUS”应该为“ONLINE”。4.7. 检查用户下的表select * from user_tables;4.8. 检查用户默认表空间 select username,default_tablespace, temporary_tablespace