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 "racdb1.us.example.com" 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

------------------------------ ---------

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

SCOTTSPACE06 ONLINE

6 rows selected

输出结果中STATUS应该都为ONLINE。

4.4. 检查Oracle所有数据文件状态

SQL> select name,status from v$datafile;

NAME STATUS

---------------------------------------------------------------- -------

/u01/app/oracle/oradata/racdb1/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;

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

输出结果中“STATUS”应该都为“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 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 from dba_users where USERNAME='SCOTT';

4.9. 检查当前用户角色及权限

select * from dba_role_privs where GRANTEE='SCOTT';

select * from dba_sys_privs where GRANTEE='SCOTT';

select * from dba_tab_privs where GRANTEE='SCOTT';

4.10. 检查用户下的各个表的大小

Select Segment_Name,Sum(bytes)/1024/1024 MB From dba_Extents where OWNER='SCOTT' Group By Segment_Name order by sum(bytes)/1024/1024 desc;

4.11. 检查一个表的创建时间

select object_name,created from dba_objects where object_name=upper('&table_name');

4.12. 检查某个表的大小

select sum(bytes)/(1024*1024) as "size(M)" from dba_segments

where segment_name=upper('&table_name');

4.13. 检查每个表占用磁盘空间情况

select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes, (b.bytes-sum(nvl(a.bytes,0))) used, sum(nvl(a.bytes,0)) free, sum(nvl(a.bytes,0))/(b.bytes)*100 Percent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;

5. 检查Oracle相关资源的使用情况

在本节主要检查Oracle相关资源的使用情况，包含：检查Oracle初始化文件中相关的参数值，检查数据库连接情况，检查系统磁盘空间，检查Oracle各个表空间使用情况，检查一些扩展异常的对象，检查system表空间内的内容，检查对象的下一扩展与表空间的最大扩展值，总共七个部分。

5.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 63 150 150

sessions 72 247 247

enqueue_locks 34 3010 3010

enqueue_resources 38 1304 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

gcs_resources 0 0 0

gcs_shadows 0 0 0

dml_locks 0 1084 UNLIMITED

temporary_table_locks 0 UNLIMITED UNLIMITED

transactions 0 271 UNLIMITED

branches 0 271 UNLIMITED

cmtcallbk 1 271 UNLIMITED

max_rollback_segments 11 271 65535

sort_segment_locks 1 UNLIMITED UNLIMITED

RESOURCE_NAME MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE

------------------------------ --------------- -------------------- --------------------

k2q_locks 0 494 UNLIMITED

max_shared_servers 1 UNLIMITED UNLIMITED

parallel_max_servers 0 10 3600

23 rows selected

若LIMIT_VALU-MAX_UTILIZATION<=5，则表明与RESOURCE_NAME相关的Oracle初始化参数需要调整。可以通过修改Oracle初始化参数文件$ORACLE_BASE/admin/racdb1/pfile/initORCL.ora来修改。

5.2. 检查数据库连接情况

SQL> select count(*) from v$session;

select s.osuser os_user_name,

decode(sign(48 - command),

1,

to_char(command),

'Action Code #' || to_char(command) ) action,

p.program oracle_process,

status session_status,

s.terminal terminal,

s.program program,

s.username user_name,

s.fixed_table_sequence activity_meter,

'' query,

0 memory,

0 max_memory,

0 cpu_usage,

s.sid,

s.serial# serial_num

from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'

order by s.username, s.osuser;

SQL> select sid,serial#,username,program,machine,status from v$session;

其中：SID 会话(session)的ID号；

SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话；

USERNAME 建立该会话的用户名；

PROGRAM 这个会话是用什么工具连接到数据库的；

STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作；

如果建立了过多的连接，会消耗数据库的资源，同时，对一些“挂死”的连接可能需要手工进行清理。如果DBA要手工断开某个会话，则执行：（一般不建议使用这种方式去杀掉数据库的连接，这样有时候session不会断开。容易引起死连接。建议通过sid查到操作系统的spid,使用ps –ef|grep spidno的方式确认spid不是ORACLE的后台进程。使用操作系统的kill -9命令杀掉连接 ）

alter system kill session 'SID,SERIAL#';

注意：上例中SID为1到10(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。

5.3. 检查系统磁盘空间

[oracle@racdb1 racdb1]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VgMasterRoot-LogVolRoot

9.5G 433M 8.6G 5% /

tmpfs 935M 315M 621M 34% /dev/shm

/dev/mapper/VgMasterU01-LogVolU01

20G 6.7G 12G 37% /u01

/dev/sda1 9.5G 91M 9.0G 1% /boot

/dev/mapper/VgMasterHome-LogVolHome

9.5G 72M 9.0G 1% /home

/dev/mapper/VgMasterTemp-LogVolTmp

3.7G 7.7M 3.5G 1% /tmp

/dev/mapper/VgMasterUsr-LogVolUsr

9.5G 1.3G 7.8G 15% /usr

/dev/mapper/VgMasterVar-LogVolVar

9.5G 118M 8.9G 2% /var

5.4. 检查表空间使用情况

SELECT df.file_id, df.file_name, df.size_mb, NVL (free.maxfree, 0) maxfree_mb,

ROUND (NVL (free.free_mb, 0), 2) free_mb, 100 - ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_used,

ROUND (100.0 * NVL (free.free_mb, 0) / df.size_mb, 2) pct_free

FROM (SELECT file_id, file_name, tablespace_name, BYTES / 1048576 size_mb FROM dba_data_files) df,

(SELECT file_id, SUM (BYTES) / 1048576 free_mb, TRUNC (MAX (BYTES / 1024 / 1024), 2) maxfree

FROM dba_free_space GROUP BY file_id) free WHERE df.file_id = free.file_id(+) ORDER BY 7;

TABLESPACE_NAME TOTAL FREE % Free

------------------------------ ---------- ---------- ----------

SYSTEM 680 6 1

SYSAUX 530 28 5

USERS 5 3 60

UNDOTBS1 90 70 78

SCOTTSPACE06 5 4 80

SQL> select 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

------------------------------ ------------

SYSAUX 95.259433962

UNDOTBS1 20.347222222

USERS 31.25

SYSTEM 99.172794117

SCOTTSPACE06 20

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",

ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",AUTOEXTENSIBLE

FROM (SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,

(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME,FILE_ID) E,

(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_DATA_FILES) F

WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)

UNION ALL --if have tempfile

SELECT D.TABLESPACE_NAME,FILE_NAME "FILE_NAME",SPACE "SUM_SPACE(M)", USED_SPACE "USED_SPACE(M)",

ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",AUTOEXTENSIBLE

FROM

(SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME,FILE_ID,FILE_NAME) D,

(SELECT FILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,

ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME,FILE_ID) E,

(SELECT FILE_ID,AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F

WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND D.FILE_ID = E.FILE_ID(+) AND D.FILE_ID = F.FILE_ID(+)

ORDER BY TABLESPACE_NAME,FILE_NAME;

如果空闲率%Free小于10%以上（包含10%），则注意要增加数据文件来扩展表空间而不要是用数据文件的自动扩展功能。请不要对表空间增加过多的数据文件，增加数据文件的原则是每个数据文件大小为2G或者4G，自动扩展的最大限制在8G。

5.5. 检查一些扩展异常的对象

select 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

如果有记录返回，则这些对象的扩展已经快达到它定义时的最大扩展值。对于这些对象要修改它的存储结构参数。

5.6. 检查表空间碎片情况

select tablespace_name, count(*) chunks,

max(bytes)/1024/1024 max_chunk,

sum(bytes)/1024/1024 total_space

from dba_free_space group by tablespace_name;

其中， CHUNKS 列表示表空间中有多少可用的空闲块(每个空闲块是由一些 连续的 Oracle 数据块组成) ，如果这样的空闲块过多， 比如平均到每个数据 文件上超过了 100 个， 那么该表空间的碎片状况就比较严重了， 可以尝试用以 下的 SQL 命令进行表空间相邻碎片的接合,如果最大可用块(max_chunk)与总大小(total_space)相比太小，要考虑接合表空间碎片或重建某些数据库对象。 碎片接合的方法: alter tablespace users coalesce;

5.7. 检查system表空间内的内容

select distinct(owner) from dba_tables

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';

如果记录返回，则表明system表空间内存在一些非system和sys用户的对象。应该进一步检查这些对象是否与我们应用相关。如果相关请把这些对象移到非System表空间，同时应该检查这些对象属主的缺省表空间值。

5.8. 检查对象的下一扩展与表空间的最大扩展值

select 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

如果有记录返回，则表明这些对象的下一个扩展大于该对象所属表空间的最大扩展值，需调整相应表空间的存储参数。

5.9. 检查flash recovery area空间

SELECT substr(name, 1, 30) name, space_limit AS quota,space_used AS used,

space_reclaimable AS reclaimable, number_of_files AS files

FROM v$recovery_file_dest ;

默认情况下开启归档后的文件存放路径，可以更改路径，监控该区域空间是否正常，防止数据库被挂起

6. 检查Oracle数据库性能

记录数据库的cpu使用、IO、内存等使用情况，使用vmstat,iostat,sar,top等命令进行信息收集并检查这些信息，判断资源使用情况。

6.1. 查询表空间读写情况

SQL> SELECT name,phyrds,phywrts

FROM V$datafile df,V$filestat fs

WHERE df.file# =fs.file#;

NAME PHYRDS PHYWRTS

-------------------------------------------------------------------------------- ---------- ----------

/u01/app/oracle/oradata/racdb1/system01.dbf 11356 563

/u01/app/oracle/oradata/racdb1/sysaux01.dbf 1869 3219

/u01/app/oracle/oradata/racdb1/undotbs01.dbf 32 1449

/u01/app/oracle/oradata/racdb1/users01.dbf 90 0

/u01/app/oracle/oradata/racdb1/SCOTTSPACE06.dbf 3 0

其中PHYRDS 代表已完成的物理读次数，PHYWRTS代表DBWR完成的物理写次数

数据库的IO负载系统的瓶颈在于磁盘IO，所以一般可以通过操作系统的一些命令来确认一个系统是否是存在IO负载问题，比如iostat，sar 等

6.2. 查询redo log buffer的繁忙程度

SQL> SELECT retries.value/entries.value "Redo Log Buffer Retry Ratio"

2 FROM V$sysstat retries, V$sysstat entries

3 Where retries.name = 'redo buffer allocation retries'

4 And entries.name = 'redo entries';

Redo Log Buffer Retry Ratio

---------------------------

8.96531320321675E-6

redo buffer allocation retries事件越少越好，该语句查询结果应该接近0或少于1%，否则要增加redo buffer

6.3. 判断undo表空间的使用情况

SQL> select sum(bytes) ,status from dba_undo_extents group by status;

SUM(BYTES) STATUS

---------- ---------

7667712 UNEXPIRED

10616832 EXPIRED

每个undo extent可以有三种状态:

active:有活动事务在此extent上

expired:已结束的事务，undo 信息超过undo_retention时间限制

unexpired:已经结束的事务，undo 信息未达到undo_retention时间限制

当一个事务开始它将会去寻找可用的undo block来存放undo信息，它将按照以下顺序请求undo space.

先去搜索拥有非active extent的undo segment，如果没有发现，那么会去创建新的undo segment,如果空间不够不能创建，将返回错误。

所以如果undo虽然表空间满，但是有很多非active的空间的话，应该不会出现事务失败的问题

6.4. 分析日志组切换频率

SELECT TO_CHAR (first_time, 'yyyy-mm-dd') DAY, COUNT (*) switch_times, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h00,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) h01, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) h02,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) h03, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) h04,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) h05, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) h06,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) h07, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) h08,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) h09, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) h10,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) h11, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) h12,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) h13, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) h14,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) h15, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) h16,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) h17, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) h18,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) h19, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) h20,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) h21, SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) h22,

SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) h23

FROM v$log_history WHERE first_time > TRUNC (SYSDATE - 30) GROUP BY ROLLUP (TO_CHAR (first_time, 'yyyy-mm-dd'));

分析最近时间段redo的切换频率，如果过于频繁或间隔太长增加日志组大小

6.5. 查看等待事件

Select

username,s.program,s.status,se.event,se.total_waits,se.total_timeouts,se.time_waited,se.average_wait

from v$session s,v$session_event se

Where s.sid=se.sid

And se.event not like 'SQl*Net%'

And s.status = 'ACTIVE'

And s.username is not null;

SQL> select * from

(select event "wait event",time_waited "time waited",

round(time_waited /(select sum(time_waited)

from v$system_event),4) "%time waited",

total_waits "waits",

round(total_waits /(select sum(total_waits)

from v$system_event),4) "%waited"

from v$system_event where wait_class!='Idle'

order by 2 desc)

where rownum <=30;

值%waited代表等待数据比，此值越小越好,此值越高代表等待事物数多，需要检查cp和io

6.6. 检查数据库cpu、I/O、内存性能

top - 10:29:35 up 73 days, 19:54, 1 user, load average: 0.37, 0.38, 0.29

Tasks: 353 total, 2 running, 351 sleeping, 0 stopped, 0 zombie

Cpu(s): 1.2% us, 0.1% sy, 0.0% ni, 98.8% id, 0.0% wa, 0.0% hi, 0.0% si

Mem: 16404472k total, 12887428k used, 3517044k free, 60796k buffers

Swap: 8385920k total, 665576k used, 7720344k free, 10358384k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

30495 oracle 15 0 8329m 866m 861m R 10 5.4 7:53.90 oracle

32501 oracle 15 0 8328m 1.7g 1.7g S 2 10.6 1:58.38 oracle

32503 oracle 15 0 8329m 1.6g 1.6g S 2 10.2 2:06.62 oracle

注意上面的蓝色字体部分，此部分内容表示系统剩余的cpu，当其平均值下降至10%以下的时视为CPU使用率异常，需记录下该数值，并将状态记为异常。

6.7. 内存使用情况

[oracle@racdb1 racdb1]$ free -m

total used free shared buffers cached

Mem: 2026 1958 67 0 76 1556

-/+ buffers/cache: 326 1700

Swap: 5992 92 5900

如上所示，蓝色部分表示系统总内存，红色部分表示系统使用的内存，黄色部分表示系统剩余内存，当剩余内存低于总内存的10%时视为异常。

6.8. 系统I/O情况

[oracle@racdb1 racdb1]$ iostat -k 1 3

Linux 2.6.32-504.1.3.el6.x86_64 (racdb1.us.example.com) 01/21/15 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle

0.27 0.00 0.51 1.69 0.00 97.53

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn

sda 5.07 14.59 46.92 458025 1472924

dm-0 0.10 1.02 0.09 32077 2800

dm-1 0.01 0.04 0.00 1408 0

dm-2 0.01 0.02 0.01 713 192

dm-3 11.97 11.39 46.15 357501 1448664

dm-4 0.18 0.21 0.60 6637 18788

dm-5 0.18 1.39 0.05 43749 1576

dm-6 0.02 0.04 0.03 1113 872

如上所示，蓝色字体部分表示磁盘读写情况，红色字体部分为cpu IO等待情况。

重点关注的几个指标就是idle值和iowait，但是idle值非常小(小于20%)或者iowait值非常大 (大于70%)时，说明IO出现问题。

由于IO问题涉及操作系统，存储系统，cp负荷及应用系统等一些因素，故当发现问题请及时联系硬件厂家进行分析处理

6.9. 系统负载情况

[oracle@racdb1 racdb1]$ uptime

21:31:08 up 8:56, 2 users, load average: 0.00, 0.00, 0.00

如上所示，蓝体字部分表示系统负载，后面的3个数值如果有高于2.5的时候就表明系统在超负荷运转了，并将此值记录到巡检表，视为异常

6.10. 查看是否有僵死进程

SQL> select spid from v$process where addr not in (select paddr from v$session);

SPID

------------------------

4491

4493

有些僵尸进程有阻塞其他业务的正常运行，定期杀掉僵尸进程

6.11. 检查缓冲区命中率

SQL> SELECT a.VALUE + b.VALUE logical_reads,

c.VALUE phys_reads,

round(100*(1-c.value/(a.value+b.value)),4) hit_ratio

FROM v$sysstat a,

v$sysstat b,

v$sysstat c

WHERE a.NAME='db block gets'

AND b.NAME ='consistent gets'

AND c.NAME ='physical reads' ;

LOGICAL_READS PHYS_READS HIT_RATIO

------------- ---------- ----------

657342 13067 98.0121

如果命中率低于90% 则需加大数据库参数db_cache_size。

6.12. 检查共享池命中率

SQL> select sum(pinhits)/sum(pins)*100 from v$librarycache;

SUM(PINHITS)/SUM(PINS)*100

--------------------------

95.4880803

如低于95%，则需要调整应用程序使用绑定变量，或者调整数据库参数shared pool的大小。

6.13. 检查排序区

SQL> select name,value from v$sysstat where name like '%sort%';

NAME VALUE

---------------------------------------------------------------- ----------

sorts (memory) 66230

sorts (disk) 0

sorts (rows) 408773

如果disk/(memoty+row)的比例过高，则需要调整sort_area_size(workarea_size_policy=false)或pga_aggregate_target(workarea_size_policy=true)。

6.14. 检查日志缓冲区

SQL> select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');

NAME VALUE

---------------------------------------------------------------- ----------

redo entries 45014

redo buffer allocation retries 1

如果redo buffer allocation retries/redo entries 超过1% ，则需要增大log_buffer。

6.15. 检查失效的索引

Sql>select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';

注：分区表上的索引status为N/A是正常的，如有失效索引则对该索引做rebuild，如：

Sql>alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME;

6.16. 检查不起作用的约束

SELECT owner, constraint_name, table_name, constraint_type, status

FROM dba_constraints

WHERE status ='DISABLE' and constraint_type='P';

如有失效约束则启用，如：

Sql>alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;

6.17. 检查无效的trigger

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

如有失效触发器则启用，如：

Sql>alter Trigger TRIGGER_NAME Enable;

6.18. 检查尚未建立索引的表

SELECT /*+ rule */ owner, segment_name, segment_type, tablespace_name, TRUNC (BYTES / 1024 / 1024, 1) size_mb

FROM dba_segments t

WHERE NOT EXISTS ( SELECT 'x' FROM dba_indexes i

WHERE t.owner = i.table_owner

AND t.segment_name = i.table_name)

AND t.segment_type IN ('TABLE', 'TABLE PARTITION')

AND t.owner NOT IN ('SYS', 'SYSTEM')

ORDER BY 5 DESC;

不应该含有比较大而且又不含索引的业务中正式使用的表

6.19. 检查运行时间长的SQL

SELECT sql_text "SQL", executions "运行次数", buffer_gets / decode(executions, 0, 1, executions) / 4000 "响应时间"

FROM v$sql WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10 AND executions > 0;

此项目查询结果可以提交开发人员，建议对这些SQL进行调整，或者结合等待事件top5，综合判断效率低下的原因

6.20. 检查性能差的前10条SQL

SELECT * FROM (SELECT PARSING_USER_ID

EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,

SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC)

WHERE ROWNUM<10 ;

6.21. 查看占 io 较大的正在运行的 session

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program, se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

6.22. 检查消耗CPU最高的PID对应的SQL

SELECT P.pid pid,S.sid sid,P.spid spid,S.username username,S.osuser osname,P.serial# S_#,P.terminal,P.program program,P.background,S.status,RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND P.spid LIKE '%&1%';

SELECT '++'||S.username username,RTRIM(REPLACE(a.sql_text,chr(10),''))||';' FROM v$process P, v$session S,v$sqlarea A WHERE P.addr = s.paddr AND S.sql_address = a.address (+) AND P.spid LIKE '%&&1%';

select * from v$sql where sql_id in ( select sql_id from gv$session where paddr in ( select addr from gv$process where program = 'ORACLE.EXE (SHAD)' and spid = 188 ));

Enter value for 1: PID（这里输入占用 CPU 最高的进程对应的 PID）

6.23. 检查占用CPU多的session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,client_info,osuser,value/60/100 value

from v$session a,v$process b,v$sesstat c

where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

6.24. 检查表空间的IO

select df.tablespace_name name,df.file_name "file",f.phyrds "物理读次数", f.phyblkrd "物理读BLOCKS",f.phywrts "物理写次数", f.phyblkwrt "物理写BLOCKS"

from v$filestat f, dba_data_files df

where f.file# = df.file_id

order by df.tablespace_name;

定期检查，发现并报告物理读、物理写特别大的表空间

6.25. 检查临时表空间IO

SELECT se.username 用户名, se.sid, se.serial#, se.sql_address, se.machine, se.program, su.tablespace,

su.blocks*8192/1024/1024 "Used Space(M)", su.segtype, su.contents

FROM v$session se, v$sort_usage su

WHERE se.saddr=su.session_addr;

多次检查，发现并报告临时表空间使用过高的会话session的程序

6.26. 检查锁和等待

SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name, o.owner,o.object_name,o.object_type,s.sid,s.serial#

FROM v$locked_object l,dba_objects o,v$session s

WHERE l.object_id=o.object_id AND l.session_id=s.sid

ORDER BY o.object_id,xidusn DESC;

alter system kill session '&sid,&serial#';

多次检查，及早发现并报告锁与等待的对象,如果可能可以结束该进程。

7. 检查数据库安全性

在本节主要检查Oracle数据库的安全性，包含：检查系统安全信息，定期修改密码，总共两个部分。

7.1. 检查系统安全日志信息

[root@racdb1 ~]# grep -i accepted /var/log/secure

Jan 21 14:36:00 racdb1 sshd[3455]: Accepted password for oracle from 192.168.137.1 port 56246 ssh2

系统安全日志文件的目录在/var/log 下，主要检查登录成功或失败的用户日志信息。

检查登录成功的日志：

7.2. 检查登录失败的日志：

[root@racdb1 ~]# grep -i inval /var/log/secure &&grep -i failed /var/log/secure

在出现的日志信息中没有错误(Invalid、refused)提示，如果没有(Invalid、refused)视为系统正常，出现错误提示，应作出系统告警通知。

8. 数据表空间日常维护8.1. 查看数据库表空间是大表空间还是小表空间

select property_name,property_value from database_properties where property_name like '%TBS%';

8.2. 查看表空间的一些信息1. 查看数据文件是否为自动增长

select file_name,tablespace_name,bytes,autoextensible,maxbytes from dba_data_files order by tablespace_name;

2. 察看表空间名称与文件名称对应关系

select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.TS#=t2.TS#;

select tablespace_name,file_name,bytes from dba_data_files order by tablespace_name;

3. 查看表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

　　D.TOT_GROOTTE_MB "表空间大小(M)",

　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

　 TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",

　　F.TOTAL_BYTES "空闲空间(M)",

　　F.MAX_BYTES "最大块(M)"

　　FROM (SELECT TABLESPACE_NAME,

　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

　　FROM SYS.DBA_FREE_SPACE

　　GROUP BY TABLESPACE_NAME) F,

　　(SELECT DD.TABLESPACE_NAME,

　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

　　FROM SYS.DBA_DATA_FILES DD

　　GROUP BY DD.TABLESPACE_NAME) D

　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

　　ORDER BY 1;

4. 知道表的名称察看表空间

select tablespace_name,table_name from user_tables where table_name='EMP';

5. 察看一个表空间有多少表

select * from all_tables where tablespace_name='USERS';

8.3. 创建表空间

CREATE TABLESPACE "TEST" DATAFILE '/u01/app/oracle/oradata/racdb1/TEST01.dbf' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 3000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SQL> show parameter db_block_size

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_size integer 8192

增加一个数据文件，文件路径和大小需要自己修改成适合的位置和大小。空间数据文件容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为 4K,8K、16K、32K、64K等几种大小，ORACLE的物理文件最大只允许4194304个数据块（由操作系统决定），表空间单个数据文件的最大值为 4194304×DB_BLOCK_SIZE/1024M。

4k最大表空间为：16384M

8K最大表空间为：32768M

16k最大表空间为：65536M

32K最大表空间为：131072M

64k最大表空间为：262144M

8.4. 表空间扩容

alter tablespace TEST add datafile '/u01/app/oracle/oradata/racdb1/TEST02.dbf' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 300M ;

alter database datafile '/u01/app/oracle/oradata/racdb1/TEST01.dbf' resize 1G;

重新设置一个数据文件的大小，只能扩容，不能减少容量。

8.5. 创建大数据文件

CREATE BIGFILE TABLESPACE "BIGFILE03" DATAFILE '/u01/app/oracle/oradata/racdb1/bigdata01.dbf' SIZE 100M AUTOEXTEND ON NEXT 128M MAXSIZE 40G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

SELECT tablespace_name, bigfile FROM dba_tablespaces

bigfile tablespace是Oracle10g的新特性。Bigfile tablespaces包含1个最大4G个blocks 的datafile。因此，使用8KBdata blocks 的bigfile tablespace 最大可以达到32TB。因为Bigfile的Tablespace不能有多个文件，必须保证在同一个磁盘空间有足够的容量. BFT(bigfile tablespace) 还受到操作系统的文件系统的限制。理论上我们可以创建最大 32T (4G*8K) 的表空间。经过测试linux 系统不建议超过2T, 大文件表空间（bigfile tablespace）应该和自动存储管理（Automatic Storage Management）或其他逻辑卷管理工具（logical volume manager）配合使用，这些工具应该能够支持动态扩展逻辑卷，也能支持striping（数据跨磁盘分布）或RAID。

文件系统(块) 单个文件大小限制 文件系统大小限制

ext2/3 (4K) 2T 16T

ext4 (4K) 16TB 1EB

[root@racdb1 ~]# tune2fs -l /dev/sda1

Block size: 4096

8.6. 数据表空间文件迁移

SQL>select file_name,tablespace_name,bytes,autoextensible,maxbytes from dba_data_files order by tablespace_name;

SQL> shutdown immediate;

[oracle@racdb1 ~]$ mv /u01/app/oracle/oradata/data02.dbf /u01/app/oracle/oradata/racdb1/data02.dbf

[oracle@racdb1 ~]$ mv /u01/app/oracle/oradata/data01.dbf /u01/app/oracle/oradata/racdb1/data01.dbf

[oracle@racdb1 ~]$ mv /u01/app/oracle/oradata/TEST02.dbf /u01/app/oracle/oradata/racdb1/TEST02.dbf

SQL> startup mount;

SQL> alter database rename file '/u01/app/oracle/oradata/data02.dbf' to '/u01/app/oracle/oradata/racdb1/data02.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/data01.dbf' to '/u01/app/oracle/oradata/racdb1/data01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/TEST02.dbf' to '/u01/app/oracle/oradata/racdb1/TEST02.dbf';

SQL> alter database open;

select file_name,tablespace_name,bytes,autoextensible,maxbytes from dba_data_files order by tablespace_name;

1. 查询当前数据表空间文件位置并关闭数据库实例

2. 关闭数据库

3. 移动数据库表空间文件的物理位置

4. 启动数据库到mount状态

5. 修改数据表空间文件在数据库中路径

6. 启动数据库

7. 查看修改过的表空间位置

8.7. 不停机移动表空间文件

alter tablespace DATASPACE offline;

mv /u01/app/oracle/oradata/racdb1/data01.dbf /u01/app/oracle/oradata/data01.dbf

SQL> alter tablespace DATASPACE rename datafile '/u01/app/oracle/oradata/racdb1/data01.dbf' to '/u01/app/oracle/oradata/data01.dbf';

alter tablespace DATASPACE online;

1. 将表空间离线

2. 移动数据文件到新位置

3. 修改数据字典新数据文件位置

4. 将表空间online

8.8. 检查用户修改密码

alter user USER_NAME identified by PASSWORD;

在数据库系统上往往存在很多的用户，如：第三方数据库监控系统，初始安装数据库时的演示用户，管理员用户等等，这些用户的密码往往是写定的，被很多人知道，会被别有用心的人利用来攻击系统甚至进行修改数据。需要修改密码的用户包括：

数据库管理员用户SYS，SYSTEM；其他用户。

登陆系统后，提示符下输入cat /etc/passwd，在列出来的用户中查看是否存在已经不再使用的或是陌生的帐号。若存在，则记录为异常

9. 存储过程管理9.1. 找出特定用户的存储过程

SELECT DISTINCT NAME FROM dba_source WHERE TYPE = 'PROCEDURE' and OWNER='SCOTT';

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='PROCEDURE' and OWNER='SCOTT';

9.2. 通过表名找出存储过程

SELECT DISTINCT NAME FROM dba_source WHERE TYPE = 'PROCEDURE' AND upper(text) LIKE '%USERS%';

9.3. 查看存储过程内容

SELECT * FROM ALL_SOURCE where TYPE='PROCEDURE' AND NAME ='BOB_PRO9';

select text from dba_source where owner= 'SCOTT' and name='BOB_PRO9' order by line;

10. 触发器管理10.1. 找出数据库中所有触发器

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER'

10.2. 找出特定用户的触发器

SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='TRIGGER' AND OWNER='SCOTT';

10.3. 找出当前用户定义的触发器

SELECT * FROM USER_SOURCE WHERE TYPE='TRIGGER';

10.4. 查看某个用户自定义的触发器内容

select * from dba_triggers where OWNER='SCOTT';

select text from dba_source where owner= 'SCOTT' and name='ON_LOGON_TRIGGER' order by line;

10.5. 查看某个表关联的触发器

select * from all_triggers where table_name='EMP';

select * from dba_triggers where table_name='EMP';

10.6. 查看当前用户所有触发器及存储过程

select * from user_source

11. Redo Log 管理11.1. 离线迁移日志文件

SQL> select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#;

SQL> alter system switch logfile;

SQL> shutdown immediate;

mv /u01/app/oracle/oradata/racdb1/redo01.log /u01/app/oracle/oradata/racdb1/RedoLog/redo01.log

mv /u01/app/oracle/oradata/racdb1/redo02.log /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log

mv /u01/app/oracle/oradata/racdb1/redo03.log /u01/app/oracle/oradata/racdb1/RedoLog/redo03.log

SQL> startup mount;

alter database rename file '/u01/app/oracle/oradata/racdb1/redo01.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo01.log';

alter database rename file '/u01/app/oracle/oradata/racdb1/redo02.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo02.log';

alter database rename file '/u01/app/oracle/oradata/racdb1/redo03.log' to '/u01/app/oracle/oradata/racdb1/RedoLog/redo03.log';

SQL> alter database open;

SQL> alter system switch logfile;

SQL> select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#;

1. 查看日志文件信息

2. 关闭数据库

3. 移动Redo Log 数据文件到新位置

4. 启动数据库到mount状态

5. 更新控制文件

6. 打开数据库并查看改变结果

11.2. 在线更改Redo Log文件容量

SQL> select * from v$logfile;

SQL> select * from v$log;

SQL>select g.member, v.status from v$log v, v$logfile g where v.GROUP#=g.GROUP#;

SQL>alter database add logfile group 4 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo401.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo402.log ') size 100M;

SQL>alter database add logfile group 5 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo501.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo502.log ') size 100M;

SQL>alter database add logfile group 6 ('/u01/app/oracle/oradata/racdb1/RedoLog/redo601.log','/u01/app/oracle/oradata/racdb1/RedoLog/redo602.log ') size 100M;

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

SQL> select * from v$log;

SQL> alter database drop logfile group 1;

SQL> alter database drop logfile group 2;

SQL> alter database drop logfile group 3;

[root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo01.log

[root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log

[root@racdb1 ~]# rm –rf /u01/app/oracle/oradata/racdb1/RedoLog/redo02.log

SQL> select * from v$logfile;

1. 查看日志文件，组，当前日志组信息

2. 新增日志组

3. 旧日志组切换到新日志组

使用日志查询命令查出的结果中，必定有一个日志组为CURRENT状态

重复执行日志切换命令，可以使新增加的日志组状态由unused变为active

如果删除日志时报错,报错的日志组会转入active状态，该状态不能删除，至少等待5分钟，当该日志组自动从active状态转为inactive后，该日志文件才能被删除。

本步骤的删除、切换、查看日志命令可以反复执行，直到将group1,gourp2,gourp3日志都删除后，方可进入下一步。

4. 删除旧日志组

5. 手动删除日志文件

6. 检查切换结果