OracleG日常维护基础手册.docx

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- 14:51:49
Copyright (c) 1997, , 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- 14:51:55
Copyright (c) 1997, , 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
----------- ------------
/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 -08-17 16:34:32 NOARCHIVELOG -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- 17:00:42
Copyright (c) 1991, , 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- 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" 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文件，假如是Oraclebug，要立即打上对应补丁

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
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP