- 监听程序状态检查
使用命令:lsnrctl status
命令输出示例:
[oracle@bys1 ~]$ ps -ef|grep tns
root 15 2 0 15:27 ? 00:00:00 [netns]
oracle 1887 1 0 15:30 ? 00:00:00 /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 2639 2031 0 17:06 pts/1 00:00:00 grep tns
[oracle@ray1 ~]$ lsnrctl status —默认的lsnrctl status命令会列出监听名为LISTENER 的监听程序状态。如果使用了个性的监听程序名,如LIST1,则使用的查看监听程序状态命令为:lsnrctl status LIST1;
具体的监听程序名可以从上一步的ps -ef|grep tns 标红处找出。
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-JUL-2016 17:06:40
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-JUL-2016 15:30:15
Uptime 0 days 1 hr. 36 min. 25 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /u01/app/oracle/diag/tnslsnr/bys1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bys1.bys.com)(PORT=1521)))
Services Summary...
Service "bys1" has 1 instance(s).
Instance "bys1", status READY, has 1 handler(s) for this service...
Service "bys1XDB" has 1 instance(s).
Instance "bys1", status READY, has 1 handler(s) for this service...
The command completed successfully
- 监听日志大小检查
监听日志过大除了会占用空间,在一些32位系统上的老版本ORACLE数据库中监听日志大于2G会遇到一些报错或BUG;因此建议监听日志大小在2G左右时即进行备份、清理。如果对历史监听日志没有查询需求,也可以直接删除。
10G版本数据库:监听状态可以看到监听日志的位置:
Listener Log File /u01/app/oracle/diag/tnslsnr/bys1/listener/log/listener.log
LINUX使用du –sh /u01/app/oracle/diag/tnslsnr/bys1/listener/log/listener.log 命令查看日志大小。
AIX操作系统使用du – sk /u01/app/log/listener.log 命令查看日志大小。
11G版本数据库:监听状态可以看到XML格式的日志位置,如下:
Listener Log File /u01/app/oracle/diag/tnslsnr/bys1/listener/alert/log.xml
XML格式监听日志大小达到10M后会自动分割.
传统文本格式日志位置在/u01/app/oracle/diag/tnslsnr/bys1/listener/trace/listener.log
删除监听日志方法:
方法1:简单粗暴的;
cp listener.log listener.log20131111 —如果对历史监听日志没有查询需求,此步骤可不做。
echo >listener.log
如备份,在cp和echo命令中间可能会有部分log丢失.
如果确认不需要从监听日志中分析或审计之前的连接信息,直接echo >listener.log清理即可。
以下来自MOS–1319797.1
方法2:
- Stop the listener process using the command line or Control Panel Service.
- Delete the log file(s) that are at or approaching the 4G size limit at this location:
$ORACLE_BASE\diag\tnslsnr<hostname>\listener\trace<listener_name>.log - Issue any lsnrctl command and you will see a new listener.log in its place under:
$ORACLE_BASE\diag\tnslsnr<hostname>\listener\trace\
- 数据库远程连接数查看
使用命令:ps –ef|grep LOCAL=NO|wc -l
命令输出示例:
[oracle@ray1 trace]$ ps -ef|grep LOCAL=NO|wc -l
3
[oracle@bys1 trace]$ ps -ef|grep LOCAL=NO
oracle 1897 1 0 15:32 ? 00:00:01 oraclebys1 (LOCAL=NO)
oracle 1910 1 0 15:35 ? 00:00:00 oraclebys1 (LOCAL=NO)
oracle 2693 2031 0 17:23 pts/1 00:00:00 grep LOCAL=NO
[oracle@ray1 trace]$ ps -ef|grep LOCAL=NO|grep -v grep|wc –l --排除查询进程自身
2
[oracle@ray1 trace]$ ps -ef|grep LOCAL=NO
oracle 1897 1 0 15:32 ? 00:00:01 oraclebys1 (LOCAL=NO)
oracle 1910 1 0 15:35 ? 00:00:00 oraclebys1 (LOCAL=NO)
oracle 2743 2031 0 17:31 pts/1 00:00:00 grep LOCAL=NO
- 数据库实例状态
使用命令:ps –ef|grep smon sqlplus / as sysdba
命令输出示例:
[oracle@ray1 trace]$ ps -ef|grep smon
oracle 1811 1 0 15:28 ? 00:00:00 ora_smon_ray1
oracle 2747 2031 0 17:32 pts/1 00:00:00 grep smon
[oracle@ray1 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 20 17:32:26 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select status,instance_name,to_char(startup_time,'yyyy/mm/dd hh24:mi:ss') from gv$instance;
STATUS INSTANCE_NAME TO_CHAR(STARTUP_TIM
------------ ---------------- -------------------
OPEN ray1 2016/07/20 15:28:55
SQL> select open_mode,name from v$database;
OPEN_MODE NAME
-------------------- ---------
READ WRITE RAY
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2016/07/20 17:32:38
- 检查表空间使用情况
命令示例:
col file_id for a5
col file_name for a50
col tablespace_name for a20
set linesize 200
set pagesize 200
select a.tablespace_name tablespace_name,a.total AS "Total(M)",
nvl(ceil((1 - b.free / a.total) * 100),100) "usage_of_tablespace%",
nvl(b.free,0) "left_space(M)",
c.EXTENT_MANAGEMENT "Extent_management"
from (select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 total
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(nvl(bytes, 0)) / 1024 / 1024 free
from dba_free_space
group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name = c.tablespace_name
and c.tablespace_name = b.tablespace_name(+)
order by (1 - b.free / a.total) * 100 desc;
TABLESPACE_NAME Total(M) usage_of_tablespace% left_space(M) Extent_man
-------------------- ---------- -------------------- ------------- ----------
SYSTEM 740 100 2 LOCAL
SYSAUX 510 96 24.375 LOCAL
UNDOTBS1 75 28 54.25 LOCAL
USERS 12.5 12 11.0625 LOCAL
TEST1 10 11 8.9375 LOCAL
TEST3 10 10 9 LOCAL
TEST2 10 10 9 LOCAL
7 rows selected.
如果发现某个表空间使用率较高,则需要查看此表空间的数据文件是否运行自动扩展,在自动扩展时还要检查数据文件是否达到自动扩展的上限(8K数据块时是32G);如上面查询中的SYSTEM表空间使用率达到100%,对SYSTEM表空间数据文件状态进行查询,示例如下:
col file_id for a5
col file_name for a45
col FILE_STATUS for a15
col TABLESPACE_STATUS for a10
col tablespace_name for a20
set linesize 200
set pagesize 200
select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE,maxbytes/1024/1024 from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME MB AUT MAXBYTES/1024/1024
--------------------------------------------- ---------- --- ------------------
/u01/app/oradata/ray1/system01.dbf 740 YES 32767.9844
可以查到数据文件当前大小是740M,最大允许大小是32G,支持自动扩展AUTOEXTENSIBLE=YES。
- 数据库ALERT日志
ALERT日志位置查询:
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/diag/rdbms/ray
1/ray1/trace
core_dump_dest string /u01/app/oracle/diag/rdbms/ray
1/ray1/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/diag/rdbms/ray
1/ray1/trace
ORACLE 10G中,ALERT日志位置在background_core_dump对应的目录中。
ORACLE 11G中,ALERT日志位置在user_dump_dest对应的目录中。
ALERT日志名字为alert<ORACLESID>.log ,
[oracle@ray1 trace]$ cd /u01/app/oracle/diag/rdbms/ray1/ray1/trace
[oracle@ray1 trace]$ ls ale*
alert_ray1.log
[oracle@ray1 trace]$ env |grep ORACLE_SID
ORACLE_SID=ray1
查看监听日志内容可以通过vi或者tail等方式。
- REDO日志切换频率
ORACLE官方建议的REDO日志切换频率应保持在20分钟左右或以上一次;过多的日志切换会对系统性能 造成监听,因此需要对此进行检查。
查询REDO LOG相关信息,如日志大小、组数等:
set linesize 160
col member for a50
select a.status,a.group#,b.member,a.BYTES/1024/1024 mb,a.SEQUENCE#,a.thread# from v$log a,v$logfile b where a.group#=b.group#;
STATUS GROUP# MEMBER MB SEQUENCE# THREAD#
---------------- ---------- -------------------------------------------------- ---------- ---------- ----------
INACTIVE 3 /u01/app/oradata/ray1/redo03.log 50 42 1
CURRENT 2 /u01/app/oradataray1/redo02.log 50 44 1
INACTIVE 1 /u01/app/oradata/ray1/redo01.log 50 43 1
查询REDO日志切换频率:
按小时汇总,查询最近7天日志切换情况:
set linesize 300
set pagesize 500
col THREAD for a10
col date for a20
select to_char(first_time,'yyyy/mm/dd:hh24') "Date",to_char(thread#) thread,count(1) "Archives_in_last_7_days/hour"
from v$log_history
where trunc(first_time)
in (trunc(sysdate),trunc(sysdate-1),trunc(sysdate-2),trunc(sysdate-3),trunc(sysdate-4),trunc(sysdate-5),trunc(sysdate-6),trunc(sysdate-7))
group by to_char(first_time,'yyyy/mm/dd:hh24'),thread# order by 1,thread#,1;
Date THREAD Archives_in_last_7_days/hour
-------------------- ---------- ----------------------------
2016/07/13:00 1 8
2016/07/13:01 1 15
2016/07/13:02 1 1
2016/07/13:03 1 1
2016/07/13:04 1 7
2016/07/13:05 1 9
2016/07/20:15 1 2
7 rows selected.
- 归档日志检查
首先需要确认是否开启归档,如未开启,可以跳过此步。
如果开启归档,应检查归档日志是否有删除策略,并且归档日志被正常定时删除;检查归档日志目录使用情况。
示例如下:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled ----从这里可以发现归档日志模式是开启的。
Archive destination /u01/app/archlog/
Oldest online log sequence 42
Next log sequence to archive 44
Current log sequence 44
[oracle@ray1 archlog]$ pwd
/u01/app/archlog
[oracle@ray1 archlog]$ ls
1_15_917051177.dbf 1_22_917051177.dbf 1_29_917051177.dbf 1_36_917051177.dbf 1_43_917051177.dbf
1_16_917051177.dbf 1_23_917051177.dbf 1_30_917051177.dbf 1_37_917051177.dbf 1_4_917051177.dbf
[oracle@ray1 archlog]$ du -sh
125M .
[oracle@ray1 archlog]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 47G 12G 34G 26% /
tmpfs 1002M 7.1M 995M 1% /dev/shm
/dev/sda1 190M 54M 126M 31% /boot