Skip to content

Oracle - 40. page

浅谈如何计算oracle文件的偏移量

##############################
www.ludatou.com 大头
转载请指明处，否则追究法律责任
##############################

了解oracle的偏移量可以帮助在一些特殊恢复场景时候急救需求用到。
关于oracle 偏移量在10g之前一直是DBA需要惦记的事情，曾经有一个在AIX平台存在的经典bug2620053就是因为关于在裸设备上偏移量的问题，在那个时代DBA在aix系统普遍使用裸设备，每次加数据文件总是要考虑到offset的问题。

在众多平台中，目前只有在AIX平台上在为Oracle使用裸设备时候，会预留出4k的空间保留裸设备元数据，俗称磁盘头（lv header）。而在以前HP TRU64也存在一样的情况，只是目前TRU 64已经不受Oracle支持了。
在每一个oracle文件都存在一个系统文件头（block 0），它们由oracle维护，数据文件上的BLOCK 0的大小等于该数据文件的block_size，而重做日志文件上的BLOCK 0除了hp平台为1M外其他都为512bytes（具体可以通过dump logfile，通过tracefile的block_size查出）。BLOCK 0之后的BLOCK 1就是Oracle header block。这里的BLOCK 1的offset就等于v$datafile中的block1_offset。

在正常情况下，不算上AIX,HP tru64的平台中使用裸设备的话，实际的oracle数据的byte offset通用计算方法如下：

byte offset=oracle_block_number * oracle_block_size （对应文件上的block number）
另外一种算法是
byte offset=block1_offset + 1 + ((oracle_block_number - 1) * oracle_block_size)
因为严格意义上讲，block1_offset并不是oracle真实数据的起始块，这两种结果都一样，只是从意义上区分开来了。

在AIX，HP tru64系统上使用裸设备的情况下，byte offset计算方法如下：

offset = raw_device_block0_offset + 1 + (oracle_block_number * oracle_block_size)

其实到这里就可以看出来了，在AIX平台上，当使用裸设备时候（这里的裸设备不是使用-T -O的big vg或者scan vg划分出来的lv），脱离了文件系统管理，file_syatem_block0_offset为0。只有在使用文件系统存放oracle文件的时才存在file_syatem_block0_offset为对应的blocksize或者redoblocksize。

AIX平台裸设备offset：

raw_device_block0_offset = 4096
file_system_block0_offset = 0

HP tru64平台裸设备offset：

raw_device_block0_offset = 65536
file_system_block0_offset = 0

从oracle 9203开始，Oracle就建议使用rlv这种不带raw_device_block0_offset的类型裸设备（lv），具体信息参考
《关于aix上的逻辑卷偏移量》

相比较而言，

Oracle Oracle恢复 数据技术 浅谈oracle技术系列 | Guang Cai Li | |   file offset

如何检测oracle所在介质上的偏移量

##########################
www.ludatou.com 大头
转载请指明处，否则追责法律责任
##########################

该文主要是把当前我所知道的检测Oracle数据文件中在系统上真实数据的起始偏移量的方式，该文档只作为介绍检测偏移量的方式，关于偏移量的相关信息参考《如何计算oracle文件的偏移量》

1.v$datafile视图中的BLOCK1_OFFSET字段可以找出偏移量

默认为8192，在一个块为8k的数据库中每个数据文件在创建时候都会在文件头创建一个OS Header Block，该块大小与数据库块大小一致，所以默认的偏移量都是8192。但是由于现在raw用的少了因为raw中也会为os file system预留空间，在aix为4k，在AIX平台中使用裸设备的文件实际偏移量应该是8192+4096.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio
NLSRTL Version 10.2.0.5.0 - Production

SQL> set linesize 200
SQL> col name for a50

SQL> select name,block1_offset from v$datafile

NAME BLOCK1_OFFSET
-------------------------------------------------- -------------
/data/ludatou/system01.dbf 8192
/data/ludatou/undotbs01.dbf 8192
/data/ludatou/sysaux01.dbf 8192
/data/ludatou/users01.dbf 8192

2.Oracle自带的dbfsize工具

无偏移量情况：

$ dbfsize /dev/rlvsystem01

Database file: /dev/rlvsystem01
Database file type: raw device without 4K starting offset
Database file size: 40960 8192 byte blocks

有偏移量情况：

MDDB$dbfsize /dev/rloradb1

Database file: /dev/rloradb1
Database file type: raw device
Database file size: 262016 8192 byte blocks

3.AIX系统下使用lslv的命令确认是否存在

#lslv datalv
LOGICAL VOLUME: DATALV VOLUME GROUP: datavg
LV IDENTIFIER: 00c3dff400004c00000001217a9d839e.84 PERMISSION: read/write
VG STATE: active/complete LV STATE: closed/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 1024 PP SIZE: 32 megabyte(s)
COPIES: 1 SCHED POLICY: parallel
LPs: 64 PPs: 64
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: maximum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 1024
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?: NO
DEVICESUBTYPE : DS_LVZ

当存在DEVICESUBTYPE : DS_LVZ时候证明存在偏移量。

4.在AIX上使用checkoffset脚本检测

使用方法：checkoffset uraw_name
该脚本如下：

#!/bin/ksh

# input:
# string: raw character device to check

# output:
# numeric: offset in raw device where oracle data begins

# error exit codes:
# 255 - usage
# 254 - block device (instead of raw device)
# 253 - not a raw device (regular file, directory, etc.)
# 252 - LV not recognized by 'lslv' command

# notes:
# This script checks the output of the AIX 'lslv' command for a device subtype of DS_LVZ.
# Note that most implementations of 'lslv' will only output the DEVICESUBTYPE field if
# the value is something other than DS_LV.  If the DEVICESUBTYPE field is not displayed,
# or displays a value other than DS_LVZ, then offset is output as 4096, otherwise 0.  A
# device subtype of DS_LVZ indicates that the normal AIX Logical Volume Control Block
# (lvcb) does not occupy the first 512 bytes of the raw device file, and Oracle data
# begins at offset 0.  A device subtype of DS_LV (default) indicates the first 512 bytes
# of the raw device are occupied by the AIX lvcb, and Oracle data begins at offset 4096.

# check that one, and only one, parameter was passed
if [ $# -lt 1 -o $# -gt 1 ]; then
   /usr/bin/echo "Usage: checkoffset raw_device"
   /usr/bin/echo "Example: checkoffset /dev/rASMRAW1"
   exit 255
fi

# resolve relative path to full path
dirn=`/usr/bin/dirname $1`
if [ $dirn = "." ]; then
   dirn=`/usr/bin/pwd`
fi
if [ $dirn = "/" ]; then
   dirn=""
fi
basn=`/usr/bin/basename $1`
rdev="$dirn/$basn"

# check whether the parameter passed is a block device
if [ -b $1 ];  then
   /usr/bin/echo "Error: $rdev is a block device"
   exit 254
fi

# check whether the parameter passed is a raw character device
if [ ! -c $1 ];  then
   /usr/bin/echo "Error: $rdev is not a raw character device"
   exit 253
fi

# strip the 'r' from the device name to get the LV name
lvname=`/usr/bin/echo $basn | /usr/bin/cut -c 2-`

# check whether lslv accepts the LV name as valid
lvchk=`/usr/sbin/lslv $lvname > /dev/null 2>&1 ; print $?`
if [ $lvchk != "0" ]; then
   /usr/bin/echo "Error: Command failed: /usr/sbin/lslv $lvname"
   /usr/sbin/lslv $lvname
   exit 252
fi

# check lslv output for DEVICESUBTYPE of DS_LVZ and set offset
offset=`/usr/sbin/lslv $lvname | /usr/bin/grep DEVICESUBTYPE`
offset=`echo $offset | /usr/bin/grep DS_LVZ ; print $?`
if [ $offset = "0" ]; then
   offset="0"
else
   offset="4096"
fi

# display offset and exit cleanly
echo $offset
exit 0
Oracle 数据技术 | Guang Cai Li |

遭遇ORA-3927错误的不规范处理操作

客户早上碰到问题，我正好检查下系统，在告警日志中碰到3927错误，大致如下：

Thu Jul 17 09:53:13 2014
ORA-1654: unable to extend index DYMPOPSA.IDX_P17_TB_SP_LOG by 8192 in tablespace               DYMPOPS
Thu Jul 17 09:53:13 2014
ORA-1654: unable to extend index DYMPOPSA.IDX_P17_TB_SP_LOG by 8192 in tablespace               DYMPOPS
Thu Jul 17 09:53:18 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA2.dbf'
RESIZE 500M
Thu Jul 17 09:53:18 2014
ORA-3297 signalled during: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA2.dbf'
RESIZE 500M...
Thu Jul 17 09:53:28 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 500M
Thu Jul 17 09:53:28 2014
ORA-3297 signalled during: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 500M...
Thu Jul 17 09:53:45 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 2000M
Thu Jul 17 09:53:45 2014
Completed: ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 2000M
Thu Jul 17 09:54:12 2014
ALTER DATABASE
DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf'
RESIZE 3000M

发现在17号表空间DYMPOPS满了，有人尝试通过resize的方式去扩大datafile，从500M开始一直尝试到3000m,终于成功了。其中在小于3000M时候，系统报错如下：

ORA-3297 signalled during: ALTER DATABASE DATAFILE '/u02/oradata/DYMPOPS/DYMPOPSA5.dbf' RESIZE *M...

通过以上可以看出几个问题：

1.首先ORA-3297的错误是告诉你file contains used data beyond requested RESIZE value，意思是实际数据文件使用的数据量要大于你所resize的指定值，所以最好在做这个操作的时候，最好先查一下dba_free_space，所要更改的这个数据文件的一些信息，比如实际使用数据量是多少等；

2.其次日志中显示是在早上9点多时候执行操作，这个点对这个系统来讲是高峰期，在这个时候需要考虑整体的资源使用情况，一般不建议在高峰时间resize datafile，建议通过增加datafile的方式来处理ORA-1654表空间空间不足的情况.

如果要问第二个问题为什么？请考虑下resize datafile的原理以及系统处理高负荷状况下resize datafile可能造成的对系统的影响.

Database Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li | |   ora 1654 ora 3297

提供一个数据库检测的脚本。适用新数据库环境检测

该脚本适用于新环境数据库的初步检测。

数据库检查：check_db_stats.sql

Rem
Rem check_db_stats_v1.2.sql
Rem modify by ludatou
Rem
Rem only for single instance database
Rem

Rem
Rem This script do health check .
Rem Must run under system or user has dba privilege.
Rem

set pages 0
set lines 1000
set trimspool on
set head off
set feedback off
set echo off
set verify off

Rem get report name based on database name and report date
Rem
col logname noprint new_value log_name
select lower(name)||to_char(sysdate,'yyyymmddhh24mi')||'.txt' logname
from v$database;
spool &log_name


Rem
Rem report header
Rem
prompt
select 'Report produced at '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')  from dual;

prompt
prompt Basic information:
prompt --------------------------------------------------------------------------------

Rem
Rem Check database information
Rem
set head on
set pages 45
col dbid heading "Database|ID" format a11
col name heading "Database|Name"
col open_mode heading  "Open |Mode"
col force_logging heading "Force|Logging" format a7
select
	to_char(dbid,9999999999) dbid
	, name
	, open_mode
	, force_logging
from 	v$database
;

Rem
Rem Check instance information
Rem
col instance_name heading "Instance|Name" format a10
col host_name heading "Host|Name" format a10
col status heading "Instance|Status" format a8
col archiver heading "Archiver|Status" format a8
col up_time heading "Running Time" format a30
select
	host_name
	, instance_name
	, status
	, archiver
	, trunc(sysdate - startup_time) || ' Days '
		|| trunc(mod(sysdate-startup_time, 1) *24) || ' Hours '
		|| trunc(mod((sysdate-startup_time)*24,1)*60) || ' Minutes '
		up_time
from 	v$instance
;


prompt
prompt
prompt Check hit ratio
prompt       These value expected higher than 90%
prompt --------------------------------------------------------------------------------

Rem
Rem Check buffer cache hit ratio
Rem
col pr heading "Physical|Reads" format 999,999,999
col prd heading "Phy_Reads|Direct" format 999,999,999
col prl heading "Phy_Reads|Direct_LOB" format 999,999,999
col bg heading "Block|Gets" format 999,999,999,999
col cg heading "Consistent|Gets" format 999,999,999,999
col ht heading "Buffer|Hit Ratio"
select
	to_char((1-((pr - prd - prl) / (bg + cg - prd - prl))) * 100, '999.9')||'%' ht
	, bg, cg, pr, prd, prl
from
	(select value pr from v$sysstat where name = 'physical reads') pr
	, (select value prd from v$sysstat where name = 'physical reads direct') prd
	, (select value prl from v$sysstat where name = 'physical reads direct (lob)') prl
	, (select value bg from v$sysstat where name = 'db block gets') bg
	, (select value cg from v$sysstat where name = 'consistent gets') cg
;

Rem
Rem check library hit ratio
Rem
col ht heading "Libray|Hit Ratio" format a10
select
	to_char(sum(pinhits) / sum(pins) * 100, 999.9)||'%' ht
from 	v$librarycache
;


prompt
prompt Check session informations
prompt --------------------------------------------------------------------------------

Rem
Rem Check session high water mark
Rem
col sessions_current heading "Sessions|Current" format 999,999,999
col sessions_highwater heading "Sessions|High Water" format 999,999,999
select
	sessions_current
	, sessions_highwater
from 	v$license
;

Rem
Rem Check session wait events
Rem
col username heading "User Name" format a15
col program heading "Program" format a35
col event heading "Wait Event" format a25
prompt
prompt Session wait events, excluding waiting for user's message
prompt _________________

col event format a32
col wait_class format a18
col segment_name format a30
select event, count(*)
  from v$session_wait
 where event not in (select name from v$event_name where wait_class = 'Idle')
  group by event order by 2 desc;

Rem
Rem Check session status
Rem
col status heading "Session|Status"
col nu heading "Number|of Sessions"
compute sum of nu on report
break on report
prompt
prompt Session status
prompt -----------------

select
	status, count(*) nu
from 	v$session
where username is not null
group by status
;
clear break;

Rem
Rem detail information for sessions which idle for more than 4 hours
Rem
col lc heading "Idle Time|(Hours)" format a8
col username format a10 heading "Database|Username"
col machine format a17 heading "Machine"
col osuser format a10 heading "OS|Username"
col prg format a35 heading "Program"
prompt
prompt Idle sessions
prompt -----------------

select
	username, machine, osuser, program prg
	, to_char(trunc(last_call_et/3600, 1), 99999.9) lc
from 	v$session
where last_call_et > 14400
	and username is not null
order by last_call_et desc
;


prompt
prompt Redo log files information
prompt --------------------------------------------------------------------------------

Rem
Rem Online redo logfile information
Rem
col grp format 99 heading "Log Group|Number"
col bytes format 999,999.99 heading "Bytes|(M)"
col status heading "Status"
col member heading "Log File|Members" format a45
break on grp on bytes on status skip 1
prompt
prompt Online redo log file
prompt -----------------

select
	l.group# grp
	, l.bytes/1024/1024 bytes
	, l.status
	, lf.member
from 	v$log l, v$logfile lf
where l.group# = lf.group#
order by 1
;
clear break;

Rem
Rem Online redo logfile switch frequency
Rem
col dt heading "Begin Time (1 hour)" format a25
col cnt heading "Switch times" format 999
prompt
prompt Switch frequency
prompt -----------------

select
	to_char(trunc(first_time, 'hh'), 'yyyy-mm-dd hh24:mi') dt
	, count(*) cnt
from
	v$loghist
where first_time > sysdate - 30
group by trunc(first_time,'hh')
order by 1
;


Rem
Rem Space usage check
Rem
prompt
prompt Tablespace usage
prompt --------------------------------------------------------------------------------

col tbsn heading "Tablespace|Name" format a20
col bytes heading "Current|Size(M)" format 999,999.99
col max_b heading "Maximum|Size(M)" format 999,999.99
col fre_b heading "Free Space|Size(M)" format 999,999.99
col usg heading "Free Space|persentage" format a10
col em heading "Extent|Management" format a10
col ssm heading "Segment|Management" format a10
select
	tb.tbsn
	, bytes
	, fre_b
	, lpad(to_char(nvl(fre_b,0)/bytes*100, 999.99)||'%',10) usg
	, max_b
from
	(select tablespace_name tbsn, sum(bytes)/1024/1024 bytes
		, sum( decode(AUTOEXTENSIBLE,
			'YES', greatest(bytes, maxbytes),
			bytes))/1024/1024 max_b
		from dba_data_files
		group by tablespace_name
	) tb,
	(select tablespace_name tbsn, sum(bytes)/1024/1024 fre_b
		from dba_free_space
		group by tablespace_name
	) fre
where tb.tbsn = fre.tbsn (+)
order by 4
;


prompt
prompt Table usage
prompt -----------------

set serveroutput on
exec dbms_output.enable(100000000);

declare
  type seg is record (
	seg_owner	dba_segments.owner%type,
	seg_name	dba_segments.segment_name%type,
	par_name	dba_segments.PARTITION_NAME%type,
	seg_type	dba_segments.segment_type%type,
	tbs_name	dba_segments.TABLESPACE_NAME%type
	);
  type usg is record (
	t_bck    number,
 	t_byt     number,
 	u_bck   number,
 	u_byt    number,
 	luefi        number,
 	luebi       number,
 	lub number);
  v_seg		seg;
  v_usg		usg;
  v_tbsname	dba_tablespaces.tablespace_name%type;
  v_tbsseg	dba_tablespaces.SEGMENT_SPACE_MANAGEMENT%type;
  cursor c_tbs is
    select
	tablespace_name, SEGMENT_SPACE_MANAGEMENT
    from
	dba_tablespaces
    where
	CONTENTS = 'PERMANENT'
    order by 1
    ;
  cursor c_seg is
    select
	owner, segment_name, PARTITION_NAME, segment_type
    from
	dba_segments
    where
	owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'WMSYS')
	and tablespace_name = v_tbsname
    order by 1, 2, 3
    ;
begin

  open c_tbs;
  fetch c_tbs into v_tbsname, v_tbsseg;
  while c_tbs%found loop
    dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
    dbms_output.put_line('Tablespace:   '||v_tbsname);
    dbms_output.put_line (
	rpad('Owner',15)
	||rpad('Segment Name', 30)
	||rpad('Par', 8)
	||rpad('Seg Type', 10)
	||rpad('Bytes(K)',10)
	||'Used(K)'
	);
    dbms_output.put_line (
	rpad('-',14, '-')||' '
	||rpad('-',29, '-')||' '
	||rpad('-',7, '-')||' '
	||rpad('-', 9, '-')||' '
	||rpad('-',9, '-')||' '
	||' ------------'
	);

    open c_seg;
    fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;
    while c_seg%found loop
      if v_seg.seg_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',
		'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'CLUSTER','LOB') then
      dbms_space.unused_space (
	v_seg.seg_owner, v_seg.seg_name, v_seg.seg_type
	, v_usg.t_bck, v_usg.t_byt, v_usg.u_bck, v_usg.u_byt
	, v_usg.luefi, v_usg.luebi, v_usg.lub, v_seg.par_name);
      dbms_output.put_line(rpad(v_seg.seg_owner,15)
	||rpad(v_seg.seg_name, 30)
	||rpad(substr(nvl(v_seg.par_name,'NULL'),1,7), 8)
	||rpad(substr(v_seg.seg_type, 1, 9), 10)
	||to_char(v_usg.t_byt/1024, '9,999,999')
	||to_char((v_usg.t_byt-v_usg.u_byt)/1024, '9,999,999')
	);
      end if;
      fetch c_seg into v_seg.seg_owner, v_seg.seg_name, v_seg.par_name, v_seg.seg_type;
    end loop;
    close c_seg;
    fetch c_tbs into v_tbsname, v_tbsseg;
  end loop;
  close c_tbs;

end;
/



spool off;
exit;

脚本效果：

[ora10g@ludatou ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Jul 28 17:25:00 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @check_db_heath.sql


Report produced at 2014-07-28 17:25:04

Basic information:
-------------------------------------------------------------------------------

Database    Database  Open       Force
ID          Name      Mode       Logging
----------- --------- ---------- -------
 2580936880 LU10G     READ WRITE NO

Host       Instance   Instance Archiver
Name       Name       Status   Status   Running Time
---------- ---------- -------- -------- ------------------------------
ludatou    lu10g      OPEN     STOPPED  1 Days 18 Hours 9 Minutes


Check hit ratio
These value expected higher than 90%
-------------------------------------------------------------------------------

Buffer             Block       Consistent     Physical    Phy_Reads    Phy_Reads
Hit Rat             Gets             Gets        Reads       Direct   Direct_LOB
------- ---------------- ---------------- ------------ ------------ ------------
  98.8%          221,523        1,181,248       16,972           34            0

Libray
Hit Ratio
----------
  95.3%

Check session informations
-------------------------------------------------------------------------------

    Sessions     Sessions
     Current   High Water
------------ ------------
           1           10


Session status
----------------

Session       Number
Status   of Sessions
-------- -----------
ACTIVE             1
         -----------
sum                1

Idle sessions
----------------

Redo log files information
-------------------------------------------------------------------------------

Online redo log file
----------------

Log Group       Bytes          Log File
   Number         (M) Status   Members
--------- ----------- -------- ---------------------------------------------
        1       50.00 CURRENT  /oradata/lu10g/redo01.log

        2       50.00 INACTIVE /oradata/lu10g/redo02.log

        3       50.00 INACTIVE /oradata/lu10g/redo03.log


Switch frequency
----------------

Begin Time (1 hour)       Switch times
------------------------- ------------
2014-07-24 13:00                     2
2014-07-26 23:00                     1

Tablespace usage
-------------------------------------------------------------------------------

Tablespace               Current  Free Space Free Space     Maximum
Name                     Size(M)     Size(M) persentage     Size(M)
-------------------- ----------- ----------- ---------- -----------
SYSTEM                    480.00        4.25       .89%   32,767.98
SYSAUX                    260.00        9.38      3.61%   32,767.98
UNDOTBS1                   25.00       21.88     87.50%   32,767.98
USERS                       5.00        4.56     91.25%   32,767.98

Table usage
----------------
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tablespace:   SYSAUX
Owner          Segment Name                  Par     Seg Type  Bytes(K)  Used(K)
-------------- ----------------------------- ------- --------- ---------  ------------
CTXSYS         DR$CLASS                      NULL    TABLE             64        64
CTXSYS         DR$INDEX                      NULL    TABLE             64        64
CTXSYS         DR$INDEX_ERROR                NULL    TABLE             64        24
CTXSYS         DR$INDEX_PARTITION            NULL    TABLE             64        24
CTXSYS         DR$INDEX_SET                  NULL    TABLE             64        64
CTXSYS         DR$INDEX_SET_INDEX            NULL    TABLE             64        24
CTXSYS         DR$INDEX_VALUE                NULL    TABLE             64        64
CTXSYS         DR$NUMBER_SEQUENCE            NULL    TABLE             64        64
CTXSYS         DR$NVTAB                      NULL    TABLE             64        24
CTXSYS         DR$OBJECT                     NULL    TABLE             64        64
CTXSYS         DR$OBJECT_ATTRIBUTE           NULL    TABLE             64        64
CTXSYS         DR$OBJECT_ATTRIBUTE_LOV       NULL    TABLE             64        64
CTXSYS         DR$POLICY_TAB                 NULL    TABLE             64        24
CTXSYS         DR$PREFERENCE                 NULL    TABLE             64        64
CTXSYS         DR$PREFERENCE_VALUE           NULL    TABLE             64        64
CTXSYS         DR$SECTION                    NULL    TABLE             64        24
CTXSYS         DR$SECTION_GROUP              NULL    TABLE             64        64
CTXSYS         DR$STATS                      NULL    TABLE             64        24
CTXSYS         DR$STOPLIST                   NULL    TABLE             64        64
CTXSYS         DR$SUB_LEXER                  NULL    TABLE             64        24
CTXSYS         DR$THS                        NULL    TABLE             64        24
CTXSYS         DR$THS_BT                     NULL    TABLE             64        24
CTXSYS         DR$THS_PHRASE                 NULL    TABLE             64        24
CTXSYS         DR$WAITING                    NULL    TABLE             64        24
CTXSYS         DRC$DEL_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IDX_COLSPEC               NULL    INDEX             64        32
CTXSYS         DRC$IDX_COLUMN                NULL    INDEX             64        32
CTXSYS         DRC$IDX_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXO_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXP_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXS_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$IXS_NAME                  NULL    INDEX             64        32
CTXSYS         DRC$IXX_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$OAT_KEY                   NULL    INDEX             64        32
CTXSYS         DRC$OAT_NAME                  NULL    INDEX             64        32
...............
Database Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li |

遭遇Listener hang 报错TNS-12537

数据库版本10201
客户端报错TNS-12537
具体信息参考如下官方说法：

APPLIES TO:

Oracle Net Services - Version: 10.2.0.4 to 11.2.0.1 - Release: 10.2 to 11.2
Information in this document applies to any platform.
SYMPTOMS

TNS listener hangs every 49.7 days. Parameter RATE_LIMIT is set in the listener.ora file.
Level 16 listener trace for the hang shows the following functions:
[000001 17-MAY-2011 23:30:42:953] nstoSetupTimeout: entry
[000001 17-MAY-2011 23:30:42:953] nstoSetupTimeout: MTO enabled for ctx=0x1002bcaf0, val=-47967511(millisecs)
[000001 17-MAY-2011 23:30:42:953] nstoUpdateActive: entry
[000001 17-MAY-2011 23:30:42:953] nstoUpdateActive: Active timeout is 5 (see nstotyp)
[000001 17-MAY-2011 23:30:42:953] nstoControlMTO: MTO enabled for ctx=0x1002bcaf0, val=-47967511(millisecs)
[000001 17-MAY-2011 23:30:42:953] nstoControlMTO: exit (0)
[000001 17-MAY-2011 23:30:42:954] nsglhc: At Connection Rate Limit. Muted Request.
[000001 17-MAY-2011 23:30:42:954] nsglhe: exit
[000001 17-MAY-2011 23:30:42:954] nsevwait: entry
[000001 17-MAY-2011 23:30:42:954] nsevwait: 4 registered connection(s)
[000001 17-MAY-2011 23:30:42:954] nsevwait: 0 pre-posted event(s)
[000001 17-MAY-2011 23:30:42:954] nsevwait: waiting for transport event (1 thru 5)...

When the listener is hung, client reports error code ORA-12537

CAUSE

The cause of this problem has been identified in Bug:8529537 All New Connections Are refused By ORA-12547 After TNS-01158 in Listener.log. It is caused by that is negative figure (buffer overflow) for inbound_connect_timeout function call.
SOLUTION

Issue is fixed in Patch Set 11.2.0.2 and future release 12.1
One off fixes are available via Patch:8529537
RATE_LIMIT parameter can be disabled as a workaround.

最终去掉RATE_LIMIT参数重启监听解决

Database Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li | |   TNS 12537