Oracle11G日常维护手册.doc

(word完整版)Oracle11G日常维护手册

Oracle Database 日常维护手册
目录
1. 登陆到数据库 3
1.1。 服务器端配置Listener 3
1.2。 客户端tnsnames 4
1.3. 检查Oracle Listener 5
1。4。 登陆数据库的方式 5
1。5. 数据库的启动 5
1.6. 关闭数据库 6
2。 用户管理 6
2.1。 检察用户profile 6
2。2. 查看用户profile参数 6
3。 检查数据库基本状况 7
3。1. 检查数据库创建日期 7
3.2. 检查数据库版本信息 7
3.3。 检查实例状态 7
3.4。 查看前台进程 7
3。5。 查看数据库连接的session 8
3.6. 查看连接到数据库的模式 8
3。7。 查看并发连接数 8
3。8. 查看最大的连接'processes' 8
3。9. 监控系统后台进程 8
3。10。 查看数据库初始化参数 8
3。11。 检查PGA使用情况 8
3。12。 检查SGA状态 8
3。13。 检查Oracle服务进程 8
3.14. 检查Oracle监听状态 9
3。15。 检查监听进程是否存在 10
3。16。 检查操作系统日志文件 10
3。17。 检查oracle日志文件 10
3。18。 检查Oracle核心转储目录 10
3。19。 检查Root用户和Oracle用户的email 11
4. 检查Oracle对象状态 11
4.1. 检查Oracle控制文件状态 11
4。2。 检查Oracle在线日志状态 11
4。3. 检查Oracle表空间的状态 12
4。4。 检查Oracle所有数据文件状态 12
4.5. 检查无效对象 12
4.6。 检查所有回滚段状态 13
4.7. 检查用户下的表 13
4。8。 检查用户默认表空间 13
4.9。 检查当前用户角色及权限 13
4。10。 检查用户下的各个表的大小 13
4.11. 检查一个表的创建时间 13
4.12. 检查某个表的大小 13
4。13. 检查每个表占用磁盘空间情况 14
5. 检查Oracle相关资源的使用情况 14
5.1. 检查Oracle初始化文件中相关参数值 14
5。2. 检查数据库连接情况 15
5。3. 检查系统磁盘空间 16
5.4。 检查表空间使用情况 16
5。5. 检查一些扩展异常的对象 18
5。6。 检查表空间碎片情况 18
5。7. 检查system表空间内的内容 18
5。8. 检查对象的下一扩展与表空间的最大扩展值 19
5。9。 检查flash recovery area空间 19
6。 检查Oracle数据库性能 19
6。1。 查询表空间读写情况 19
6。2. 查询redo log buffer的繁忙程度 20
6。3. 判断undo表空间的使用情况 20
6。4. 分析日志组切换频率 21
6。5. 查看等待事件 21
6。6。 检查数据库cpu、I/O、内存性能 22
6。7。 内存使用情况 22
6.8. 系统I/O情况 22
6。9。 系统负载情况 23
6。10. 查看是否有僵死进程 23
6。11. 检查缓冲区命中率 23
6.12。 检查共享池命中率 24
6.13. 检查排序区 24
6.14。 检查日志缓冲区 24
6.15。 检查失效的索引 24
6.16。 检查不起作用的约束 24
6.17. 检查无效的trigger 25
6。18。 检查尚未建立索引的表 25
6.19. 检查运行时间长的SQL 25
6。20。 检查性能差的前10条SQL 25
6.21。 查看占 io 较大的正在运行的 session 25
6。22. 检查消耗CPU最高的PID对应的SQL 25
6.23. 检查占用CPU多的session 26
6.24. 检查表空间的IO 26
6.25. 检查临时表空间IO 26
6。26。 检查锁和等待 26
7。 检查数据库安全性 27
7.1。 检查系统安全日志信息 27
7。2. 检查登录失败的日志: 27
7。3. 检查用户修改密码 27
8. 数据表空间日常维护 27
8。1。 查看表空间的一些信息 27
8。2. 创建表空间 28
8.3. 表空间扩容 28
8。4. 创建大数据文件 29
8.5. 数据表空间文件迁移 29
8.6. 不停机移动表空间文件 30
9. 存储过程管理 30
9.1。 找出特定用户的存储过程 30
9。2. 通过表名找出存储过程 30
9。3。 查看存储过程内容 30
10。 触发器管理 30
10.1. 找出数据库中所有触发器 30
10。2。 找出特定用户的触发器 31
10.3。 找出当前用户定义的触发器 31
10.4. 查看某个用户自定义的触发器内容 31
10.5. 查看某个表关联的触发器 31
10。6。 查看当前用户所有触发器及存储过程 31
11。 Redo Log 管理 31
11。1. 离线迁移日志文件 31
11。2. 在线更改Redo Log文件容量 32

1. 登陆到数据库
1.1. 服务器端配置Listener
LISTENER =
（DESCRIPTION_LIST =
（DESCRIPTION =
（ADDRESS = (PROTOCOL = IPC)（KEY =racdb1)）
(ADDRESS = (PROTOCOL = TCP)(HOST = 192。168。137.10）(PORT = 1521）)
）
)

ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER=
（SID_LIST=
(SID_DESC=
＃BEQUEATH CONFIG
（GLOBAL_DBNAME=racdb1）
（SID_NAME=racdb1)
（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=racdb1
Service 名称,在客户端一定配置和他相同
SID_NAME=racdb1
实例名称，这个要和SID相同
GLOBAL_DBNAME可以不等于SID_NAME
客户端根据tnsname.ora中的SERVICE_NAME和地址（ADDRESS = (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 = 192。168.137。10）（port = 1521))
)
（connect_data =
（service_name =racdb1）（ur=a）
）
)

这里的service_name =racdb1 就是在服务器端的GLOBAL_DBNAME=racdb1
[oracle@racdb1 ~］＄ tnsping racdb1
TNS Ping Utility for Linux： Version 11。2.0.1.0 — Production on 21-JAN—2015 14：51：49
Copyright （c) 1997, 2009, Oracle. All rights reserved.
TNS-03502： Insufficient arguments. Usage： tnsping 〈address〉 [<count〉]
[oracle@racdb1 ~]$ tnsping racdb1
TNS Ping Utility for Linux: Version 11.2。0。1。0 — Production on 21—JAN—2015 14:51：55
Copyright （c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact （description = (address_list = (address = (protocol = tcp）(host = racdb1)(port = 1521)）) (connect_data = （service_name = racdb1）(ur=a)）)
OK (0 msec）
1.3. 检查Oracle Listener
lsnrctl stop
lsnrctl start
lsnrctl status
lsnrctl service
1.4. 登陆数据库的方式

修改登陆oracle 认证模式
默认情况下我们oracle 安装好后是使用操作系统用户的验证，所以这里如果我们使用sys用户不用密码就可以登录,如果我们想使用oracle 密码文件验证的话我们就要进入下列文件夹
cd /u01/app/oracle/product/11。2。0/dbhome_1/network/admin修改sqlnet.ora
增加下列命令
SQLNET.AUTHENTICATION_SERVICES = NONE

配置了tnsnames登录数据库方式
[oracle@racdb1 ～]$ sqlplus scott/111111＠racdb1
sqlplus /nolog
使用scott登陆到指定数据库racdb1
conn sys/111111@racdb1 AS SYSDBA;
察看登陆到了哪个数据库实例
select instance_name from v$instance
使用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
启动过程中限制访问数据库
startup force
强制数据库启动
startup pfile=/oracle/app/oracle/product/10g/dbs/initminos。ora
使用非缺省参数文件启动数据库，以特定文件中指定参数启动数据库,本例为”/oracle/app/oracle/product/11g/dbs/initminos。ora
startup
启动实例、装载数据库、打开数据库
startup open racdb1
startup nomount
启动数据库实例， 该步骤只是启动了一个数据库实例.
在此状态下我们可以访问下列结构文件
Select * from v$instance；
Select ＊ 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 restricted session
grant restrict session to scott
上述命令是只有用户拥有restrict 权限才可以连接
启动数据库后起的进程
select name, DESCRIPTION from v$bgprocess where paddr<〉’00';
1.6. 关闭数据库
shutdown normal 
等待用户完成工作然后关闭
Shutdown transactional
等待用户完成工作但是强制关闭连接
shutdown immediate  
立即关闭 ,当前所有做工作回滚到一致状态,断开其连接
shutdown abort
强制关闭，非干净关闭,下次重起后要回滚日志
2. 用户管理
2.1. 检察用户profile
select 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 NOARCHIVELOG
3.2. 检查数据库版本信息
Select version from Product_component_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, 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"表示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｜egrep —v ’total｜buffers’｜awk ’{ print ＄1，$3 ｝’

Oracle 的前台进程是操作系统进程,它和oracle session 一一对应，官方建议，oracle sesson
=1.5*process +20
3.5. 查看数据库连接的session
SQL> 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 where d.PADDR=c.DISPATCHER and s.PADDR=c.SERVER

（S012)以s001等就是共享模式连接
3.7. 查看并发连接数
select count(*） from v＄session where status=’ACTIVE’；
3.8. 查看最大的连接'processes'
SQL〉 select value from v＄parameter where name = ’processes'
3.9. 监控系统后台进程
SQL〉 Select name，Description From V$BGPROCESS Where Paddr<>’00'；
3.10. 查看数据库初始化参数
select 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_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服务进程
[oracle@racdb1 ～]＄ ps —ef｜grep ora_｜grep —v grep＆＆ps -ef|grep ora_｜grep —v grep｜wc ?l
oracle 4459 1 0 15：53 ？ 00:00:00 ora_pmon_racdb1
oracle 4461 1 0 15：53 ? 00:00：17 ora_vktm_racdb1
oracle 4465 1 0 15：53 ? 00:00：00 ora_gen0_racdb1
oracle 4467 1 0 15:53 ? 00:00:00 ora_diag_racdb1
oracle 4469 1 0 15:53 ？ 00：00:00 ora_dbrm_racdb1
oracle 4471 1 0 15：53 ？ 00:00:00 ora_psp0_racdb1
oracle 4473 1 0 15：53 ? 00:00：02 ora_dia0_racdb1
oracle 4475 1 0 15:53 ? 00:00:00 ora_mman_racdb1
oracle 4477 1 0 15：53 ? 00:00：00 ora_dbw0_racdb1
oracle 4479 1 0 15：53 ? 00:00：01 ora_lgwr_racdb1
oracle 4481 1 0 15:53 ? 00:00：05 ora_ckpt_racdb1
oracle 4483 1 0 15：53 ？ 00:00：00 ora_smon_racdb1
oracle 4527 1 0 15:53 ？ 00：00：00 ora_arc0_racdb1
oracle 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进行归档的进程，输出显示为：“ora_arc0_racdb1”
。 Oracle进行检查点的进程，输出显示为：“ora_ckpt_racdb1”
。 Oracle进行恢复的进程，输出显示为:“ora_reco_racdb1”
3.14. 检查Oracle监听状态
[oracle@racdb1 ~］＄ lsnrctl status
LSNRCTL for Linux: Version 11.2.0。1.0 — Production on 21—JAN-2015 17:00：42
Copyright （c） 1991, 2009, Oracle. All rights reserved。
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC）（KEY=racdb1）)）
STATUS of the LISTENER
—--———----—---———-————-—
Alias LISTENER
Version TNSLSNR for Linux： Version 11.2。0。1。0 — Production
Start Date 21—JAN-2015 12：37：48
Uptime 0 days 4 hr。 22 min. 53 sec
Trace Level off
Security ON： Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11。2。0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/racdb1/listener/alert/log.xml
Listening Endpoints Summary.。.
(DESCRIPTION=(ADDRESS=（PROTOCOL=ipc）（KEY=racdb1）））
（DESCRIPTION=(ADDRESS=（PROTOCOL=tcp）(HOST=192.168.137.10）(PORT=1521)））
Services Summary。。。
Service ”racdb1" 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.us。example。com” has 1 instance(s).
Instance "racdb1", status READY, has 1 handler(s） for this service。..
The command completed successfully

“Services Summary”项表示Oracle的监听进程正在监听哪些数据库实例，输出显示中至少应该有“racdb1XDB"这一项。
3.15. 检查监听进程是否存在
［oracle＠racdb1 ～］$ ps -ef｜grep lsn|grep -v grep
oracle 2140 1 0 12：37 ? 00:00：01 /u01/app/oracle/product/11。2。0/dbhome_1/bin/tnslsnr LISTENER -inherit
3.16. 检查操作系统日志文件
[root@racdb1 ～]# cat /var/log/messages |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/trace

cat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log ｜ grep ora-
cat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log | grep err
[oracle@racdb1 trace］$ cat /u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1。log | grep fail

Oracle在运行过程中，会在警告日志文件(alert_SID。log）中记录数据库的一些运行情况：数据库的启动、关闭,启动时的非缺省参数；数据库的重做日志切换情况，记录每次切换的时间，及如果因为检查点(checkpoint)操作没有执行完成造成不能切换,会记录不能切换的原因;对数据库进行的某些操作，如创建或删除表空间、增加数据文件；数据库发生的错误，如表空间不够、出现坏块、数据库内部错误（ORA－600)等。定期检查日志文件，根据日志中发现的问题及时进行处理：
问题
处理
启动参数不对
检查初始化参数文件
因为检查点操作或归档操作没有完成造重做日志不能切换
如果经常发生这样的情况,可以考虑增加重做日日志文件组；想办法提高检查点或归档操作的效率；
有人未经授权删除了表空间
检查数据库的安全问题，是否密码太简单；如有必要，撤消某些用户的系统权限
出现坏块
检查是否是硬件问题（如磁盘本生有坏块)，如果不是，检查是那个数据库对象出现了坏块，对这个对象进行重建
表空间不够
增加数据文件到相应的表空间
出现ORA—600
根据日志文件的内容查看相应的TRC文件,如果是Oracle的bug,要及时打上相应的补丁

3.18. 检查Oracle核心转储目录
SQL> select value from v$parameter where name='core_dump_dest'；
VALUE
—--———-———----—--—-————--—---—--—--———-——--——--——---———-———----———————--———--——-
/u01/app/oracle/diag/rdbms/racdb1/racdb1/cdump
[oracle@racdb1 cdump]$ ls /u01/app/oracle/diag/rdbms/racdb1/racdb1/cdump/＊.trc|wc —l

如果上面命令的结果每天都在增长，则说明Oracle进程经常发生核心转储。这说明某些用户进程或者数据库后台进程由于无法处理的原因而异常退出。频繁的核心转储特别是数据库后台进程的核心转储会导致数据库异常终止。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_syslog_level’；
3.19. 检查Root用户和Oracle用户的email
[oracle@racdb1 racdb1］$ tail —n 200 /var/mail/oracle
［oracle＠racdb1 racdb1］＄ tail -n 200 /var/mail/root

查看有无与Oracle用户相关的出错信息。
4. 检查Oracle对象状态
在本节主要检查相关Oracle对象的状态，包含：检查Oracle控制文件状态，检查Oracle在线日志状态，检查Oracle表空间的状态，检查Oracle所有数据文件状态，检查Oracle所有表、索引、存储过程、触发器、包等对象的状态，检查Oracle所有回滚段的状态，总共六个部分。
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 from 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> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
--------—-—-—---——---————-—--- —-—-—-—--
S