ORACLE DB数据库常见问题解决及诊断技巧集锦 – ORACLE DBA故障修复必备手册

ORACLE DBA故障修复必备手册 本文地址:https://www.askmaclean.com/archives/oracle-troubleshooting.html

 

 

关于Oracle数据库问题诊断信息获取

在对Oracle数据库相关情况及问题进行诊断及信息捕获时, 请:

 

  1. 请确认MAX_DUMP_FILE_SIZE 参数设置, 这将避免用以诊断的追踪文件(trace file)因为空间不足而被截断 !!!

 

  1. 上传alert.log文件。11g中,文本形式的alert.log是放在ADR home相应”trace”目录下。而XML形式的alert.log则是放在ADR home下的”alert”目录下。如需验证ADR home确切位置,可以执行”select * from v$diag_info” 并从其输出中找到。
    请看下页诊断追踪文件目录地址信息。

 

  1. 获取RDA收集信息 (版本4.2x以上更佳). 请查看MOS文档 330363.1314422.1以了解更多信息。

 

  1. 请对所遇问题提供详尽描述以便于分析并更快找到问题原因。

对于 较为严重的问题, 请提供:

  • 相关业务及技术评估
  • 两名24×7联系人,并提供相关人员邮件及电话联系方式(askmaclean.com)。
  • 一名相关管理联系人,需提供其邮件及电话联系方式。

 

问题细节描述:

  • 受影响日期时间
  • 相关报错号及其文本信息。
  • 相关问题影响 – 数据库实例奔溃, 进程失败等.?
  • 错误出现频率?
  • 错误是否可重演。
  • 错误是否总在特定日期时间发生?
  • 错误发生是否与数据库相关活动有关?如备份或高负载处理时.
  • 错误是否总是来自于某个应用或某位用户?
  • 第一次产生此错误是在何时?当时是否正在做何改动?
  • 当时是否有其他错误发生(例如ORA-7445 或 ORA-600错误)?
  • 最近系统是否有任何改变?

 

  1. 如果你还不确定set events操作会带何种影响,请不要设置。

 

  1. 随着Oracle 11g诊断追踪文件默认地址的改变。下表展示了以前(10g以及之前版本)和最新11g(ADR)的跟踪文件目录位置。

 


诊断追踪文件目录地址

 

诊断数据文件 之前版本文件路径 ADR中文件路径
Foreground process traces USER_DUMP_DEST $ADR_HOME/trace
background process traces BACKGROUND_DUMP_DEST $ADR_HOME/trace
Alert log data BACKGROUND_DUMP_DEST $ADR_HOME/alert&trace
Core dumps CORE_DUMP_DEST $ADR_HOME/cdump
Incident Dumps USER|BACKGROUND_DUMP_DEST $ADR_HOME/incident/incdir_n

 

Oracle数据库11g并没有对前台和后台追踪文件做区分。两种类型文件都放在$ADR_HOME/trace目录下。

 

  1. 请注意BDE及其开发需要11g漏洞 IPS打包服务. 在11g中IPS(Incident Packaging Service事件打包服务) 提供了一种对诊断信息打包的简便方法。

 

所有数据库事件(严重错误)的跟踪文件都被存储在Oracle自动诊断库(Automatic Diagnostic Repository: ADR)中。其提供的ADRCI工具被用于打包所有文件并上传给Oracle支持。从OEM中也能找到IPS相关链接服务。请参考以下文档了解更多相关信息:

 

Note:422893.1 – 11g Understanding Automatic Diagnostic Repository.

Note:443529.1 – 11g Quick Steps to Package and Send Critical Error

Note:1091653.1 – 11g Quick Steps – How to create an IPS package using Support Workbench

 

并非11g中所有问题都需生成诊断包. 但对于以下相关11g错误及SR应该考虑获取相关诊断包(askmaclean.com)。
Manageability:

ORA-600 & ORA-7445

ORA-4030 & ORA-4031

 

DB Admin:

ORA-600, ORA-700 & ORA-7445

 

HA:

ORA-600 & ORA-7445

ORA-8103 – Object no longer exists

ORA-1410 – Invalid ROWID

ORA-1578 – Oracle data block corruption

ORA-376 – File cannot be read at this time

ORA-353 – There is a log corruption near a block, change, and time

 

以下是一个例子 à

 

客户看见alert.log中出现以下错误:

Errors in file d:\oracle\diag\rdbms\twn11g\twn11g1\trace\twn11g1_ora_201116.trc:

ORA-00600: internal error code, arguments: [17099], [], [], [], [], [], [], [], [], [], [], []

Errors in file d:\oracle\diag\rdbms\twn11g\twn11g1\trace\twn11g1_ora_201116.trc (incident=19569):

ORA-00600: internal error code, arguments: [17099], [], [], [], [], [], [], [], [], [], [], []

Incident details in: d:\oracle\diag\rdbms\twn11g\twn11g1\incident\incdir_19569\twn11g1_ora_201116_i19569.trc

 

…因此我们看见事件号为 19569.
在正确设置11g环境变量后,通过ADRCI(命令)进入IPS打包处理服务:

 

% adrci

 

因为我们已经知道事件号为19569, 那执行下一步:

 

adrci> ips pack incident 19569

 

…这样就在当前目录下建立了zip压缩包.如果你希望压缩包保存在其它目录中, 那么可以在命令后加上路径信息:

 

adrci> ips pack incident 19569 in D:/tmp/whatever

 

此命令会提示信息产生了包含事件号19569相关信息的IPS打包文件。这个zip压缩文件之后需要被上传到SR上。

 

诊断数据库挂起(HANG)事件

请具体表述问题。并询问用户是否能“解冻”数据库?如何做到的?

  • 使用以下命令生成HANGANALYZE追踪文件 ==>

 

$ sqlplus “/ as sysdba”

SQL> oradebug hanganalyze 3

… Wait 90 seconds (to 2 minutes) to give time to identify process state changes.

SQL> oradebug hanganalyze 3 (level 4 dumps leaf nodes as well and may be useful, but there is more impact on the system)

 

使用以下语法在执行RAC级HANGANALYZE:

 

ORADEBUG setmypid

ORADEBUG setinst all

ORADEBUG -g all hanganalyze 3

 

 

2) 在运行Hanganalyze之后,登入一个新SQL会话来生成系统状态转储(dump)文件==>

 

$ sqlplus “/ as sysdba”

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump systemstate 266

… wait 90 seconds

SQL> oradebug dump systemstate 266

… wait 90 seconds

SQL> oradebug dump systemstate 266

 

Level 266和267包含了一些堆信息。Level 266是对单实例的信息转储,而Level 267则是对RAC系统systemstate转储。请看Note:300870.1对事件10998了解更多信息。

 

**Note: 我们同样可以通过使用Hang File Generator (HANGFG) 工具来完成以上1)和2)中使用hanganalyze和systemstate收集信息的操作。HANGFG工具提供了一系列UNIX shell脚本命令来自动化收集和生成hanganalyze和sysemstate追踪文件。在使用HANGFG生成并收集追踪文件时需要考虑到需要在已处于性能退化的系统中进行诊断的影响。由于影响级别作为此工具参数被传给HANGFG, 所以用户需要做出决定, 在启用此工具时,何种级别的影响是可接受的。当用户选择轻度影响或中等影响(选项1 或2)作为参数时, HANGFG工具同样有能力做出调整以适应用户做出的决定。HANGFG是RAC自识别的,并且能在RAC和非RAC环境中运行。请看Note:362094.1 (the HANGFG User Guide)以了解其相关信息。

 

3) 当hang事件发生后,获取到的Statspack或AWR/ASH(10g/11g)报告是否具有时效性取决于做Statspack快照频繁程度。举个例子,如果你每隔1小时做一次快照,在下一次快照前发生了5分钟的数据库挂起事件。那么快照由于显得时间跨度太长而很难用于分析当时5分钟发生的情况。

 

AWR/ASH报告上的其他相关信息可以在“数据库性能”章节中找到。

 

有时在尝试连接数据库时,你的调试会话也会挂起在那里。这时留给你3种可选方法==>

 

1) 找一个之前已连上的可用会话。

2) 如果你正在使用10g/11g,那么你可以使用-prelim选项来登录数据库:

SQL*Plus. Eg:

sqlplus -prelim / as sysdba

oradebug setmypid

oradebug unlimit;

oradebug dump systemstate 266;

3) 使用操作系统调试器来查看运行进程

  • 获取服务器oracle先关影子进程ID (请注意并非oracle后台进程,调试后台进程可能导致数据库奔溃)…”ps –ef | grep ora”
  • 使用当前系统级调试器(如dbx, adb, gdb等)来获取systemstate:

$ gdb $ORACLE_HOME/bin/oracle <process ID>

(gdb) print ksudss(267)

 

括号中的数字(267)是你希望转储systemstate的级别.

 

**对于RAC挂起事件请看“RAC性能”部分中对于racdiag脚本的使用**

 

通过以下systemstate命令可在同一时点转储所有RAC节点状态:

SQL> oradebug -g all dump systemstate 267

 

总结,你可以上传以下文件:

 

– Hanganalyze输出

– Systemstate 转储(dumps)

– hangfiles.out (如果使用 HANGFG工具命令)

– alert.log

– 在一个很短时间内的Statspack/AWR/ASH 报告

– 可用的系统调试监控输出

 

更多信息,请查阅以下文档:

 


Process Spins (进程消耗CPU资源达100%)

当进程出现spinning现象,可使用以下命令:
(** Busy spin术语解释: 由于程序导致进程不断地检查是否条件为true)

 

sqlplus / as sysdba

SQL> oradebug setospid <OSPID of spinning process>

SQL> oradebug unlimit

SQL> oradebug dump errorstack 3

— wait for 1 minute

SQL> oradebug dump errorstack 3

 

从 pstack命令输出中获取进程call信息:

 

$ pstack <process id of spinning process>

 

可以使用Strace命令来记录系统进程的系统调动及相关进程输入信号等信息:

 

$ strace –p <process id of spinning process>

 

对于RAC系统,可以使用systemstate命令转储相关状态,此命令的使用在“诊断数据库挂起事件”部分有提到。


 ORA-4031错误分析

当试图在共享池中分配大块连续内存而失败时,Oracle会首先从池中清理当前不用的对象从而使得空闲内存碎片(chunk:内存块)得以合并。如果这样仍然没有足够大的单个chunk来满足分配需要,则会产生ORA-04031报错。有许多ORA-04031错误直接原因都是由于共享池的大小或调整不当造成的。

 

Note:ORA-4031错误的进程并不总是内存消耗的元凶。错误的发生仅是因为此进程无法得到所需内存而造成的(askmaclean.com)。

 

如果已经按所有步骤正确设置了共享池大小(SHARD_POOL_SIZE) , 但此问题仍然产生时,除了从应用(例如:使用绑定变量查询替代静态SQL等)入手进行分析解决问题外,也可从其他trace文件中获得共享池的一些快照信息==>

 

修改init.ora参数文件,增加以下事件以从追踪文件中获取相关问题信息:

event = “4031 trace name errorstack level 3”

event = “4031 trace name HEAPDUMP level 3”

 

注意:除非重启实例,否则这个参数文件设置不会起效。从Oracle 9.2.0.5版本起,除了在请求heapdump时使用level 1,2,3 或32 你同样可以使用相同等级并加值536870912.这样将会在此等级上再进一步显示5个最大的subheaps同时每个subheap下显示相关5个最大的heap areas.

如果问题可以重现,则可在执行有问题的SQL语句前,在会话级别对事件进行设置:

SQL> alter session set events ‘4031 trace name errorstack level 3’;

SQL> alter session set events ‘4031 trace name HEAPDUMP level 536870914’;

 

Level 536870912 转储5个最大subheaps并且对应每个subheap将显示其5个最大heap areas。由于ORA-04031错误可能在不同池中发生(共享池,大池,java池,流池等),其level值的设置可参照如下:

 

 

Component Level
PGA 1
SGA 2
Large Pool 32
Streams Pool   64
Java Pool      128

 

Note:  如果4031错误出现频繁,在实例级设置此事件(heapdump 536870914)将会产生许多大的trace文件.  这不仅会影响数据库性能而且可能使数据库挂起 (某些情况下可能会使得数据库崩溃).  因此有必要及时使用以下语句关闭此事件追踪:

 

alter system set events ‘4031 trace name HEAPDUMP off’;

 

我们也通过Library cache转储来帮助确认产生ora-4031问题的游标:

 

sqlplus / as sysdba

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug dump library_cache 10

 

请注意:在Oracle 9.2.0.5+, 10g和11g版本中,4031 trace文件默认会在ORA-4031发生时产生并存放于user_dump_dest目录。如果你的数据库版本是其中的一个,那么你就不需要进行相关设置来生成4031 trace 文件。

 

ORA-4031 诊断 à

  • 检查Alert日志并查看错误是否记录。注意不是所有ORA-4031错误都会记录在alert日志中。
  • 如果错误被记录,请检查SGA的哪部分收到此错误。是共享池,大池,java池或streams池?
  • 查询v$sgastat以检查是否有组件表现出非正常增长.
  • 查询v$librarycache并检查:

– 有无无效对象 (多为DDL语句)

– 有无重载 (Library cache可能不够大)

– 内存命中率 (低命中率可能是非共享游标造成的)

  • 检查是否存在高Version Counts的游标。 可通过v$sql_shared_cursor查询. 如果存在某父游标下有许多子游标的情况,检查不可共享的原因. 大量子游标会加快共享池的碎片化. 请确认应用正在使用绑定变量方式查询.

 

更多信息,请看:


ORA-4030 报错

这个错误原因是Oracle服务器进程不能从操作系统上分配出更多内存。含有PGA(程序全局区)的进程其内存的分配取决于服务器的设置。对于dedicated服务器进程,其包含了stack堆栈和UGA(用户全局区), 保存有用户会话信息、游标信息和数据分类排序区。在多线程模式配置(shared服务器)中,UGA是在SGA中进行分配,所以不对ORA-4030报错负责。

 

因此ORA-4030是对需要更多内存(用于stack UGA或PGA)来完成工作的进程而言的。

 

请记住产生ORA-4030错误的进程并非总是内存损耗的元凶,错误的发生仅仅是因为此进程无法取得所需的内存造成的。

 

当你碰到这个错误时,意味着你已不能从系统中分配内存。这可能是由于你的进程本身导致的(如果你的进程正需求很多内存),也可能是其他原因导致操作系统内存被耗尽(如过大的SGA或者太多进程占用了系统的物理内存和虚拟内存)。如何处理此问题?

 

  1. 确定是否还有足够可用内存?使用系统专用工具来检查内存使用情况 (如vmstat, top等).
  2. 是否存在操作系统设定限制? 检查ulimit命令检查当前资源限制.
  3. 是否存在Oracle限制设置? 检查pga_aggregate_target和相关会话的pga内存值.
  4. 找出当前哪个进程正在要求大量内存, 可通过v$statname 和v$sesstat以了解当前最忙的进程.

 

当进程正在稳定增长其所需内存时,我们可以观察它们的运行:

– 通过v$sql_area可以看到哪些命令正被执行:

SQL> select sql_text  from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = <SID>;

– 你也可以通过heapdump转储来查看它们:

SQL> oradebug setorapid 10 (10是Oracle进程Id, 使用setospid 以设置当前调试进程)

SQL> oradebug unlimit

SQL> oradebug dump heapdump 5

– 如果报错问题间歇性发生或者由于进程失败太快以至于难以捕获(但又很可能是内存大量消耗的根本原因),则我们可以设置事件来获取heapdump转储信息:

SQL> alter sssion set events ‘4030 trace name heapdump level 5’;

…或者直接在 init.ora文件中设置event.

 

更多其他相关信息请看:

  • Note:1 – Diagnosing and Resolving ORA-4030 errors
  • Note:1 – FAQ: ORA-4030


ORA-600错误

理解ORA-600错误含义对于评估客户数据库中可能存在的问题非常重要。ORA-600报错的第一个参数指出了问题所在的相关位置 (如 ORA-600 [XXXX]).对于此错误:

 

  • 至少需要提供相关的 trace文件和log文件.
  • 操作系统错误/消息日志 (如果可用的话, 像坏块问题等)

 

对此问题也有一些新工具可用于加快原因查找。工具使用及其他相关信息支持文档请看:

  • Note:390293.1 – Introduction to 600/7445 Internal Error Analysis
  • Note:153788.1 – Troubleshoot an ORA-600 Error Using the ORA-600 Argument Lookup Tool

 

此外,如果可能,请设法提供测试用例,数据库历史改变细节和出错频繁程度等情况。并查找Oracle在当时自动转储的任何system状态信息文件。

 

 


ORA-1801错误

ORA-1801报错是指日期格式字符串太长以至无法做转换处理. 这仅发生在当几个长字符串按字面值被转换为一个日期部分时。如果当前没有相关错误的trace文件, 我们可以设置以下事件来获取errorstack和 heapdump ==>

 

对应实例级需在init.ora中设置:

event=”1801 trace name errorstack level 3; name heapdump level 13325″

或在系统级设置:

alter system set events ‘1801 trace name errorstack level 3; name heapdump level 13325’;

或在会话级设置:

alter session set events ‘1801 trace name errorstack level 3; name heapdump level 13325’;

 


ORA-7445 错误

当Oracle服务器进程做了某些错误的处理(以至于触发了某种形式的信号违背signal violation)时,会抛出ORA-7445这样一个通用错误, (就像UNIX下的SIGSEGV). 对我们来说,当碰到ORA-7445报错的时候,最重要的事情就是从trace文件中找到call stack trace信息,因为它告诉我们相关违背行为是在哪里发生的。

 

  • 提供相关trace 文件
  • 如果可以的话,请一并提供相关操作系统错误消息日志
  • 如果存在相关core文件, 请从其中抓取出stack trace信息。

 

更多相关其它信息请看:

 

另外,如果可能的话,请提供如测试用例,数据库历史改变信息以及错误放生频繁程度信息等相关信息。请查找当时Oracle自动生成的systemstate转储文件。

 

 


其它ORA-NNNN报错

这里我们讨论下如何诊断一些未预料的Oracle错误(ORA-NNNN)。
在诊断问题前,做一些例行询问(按经验总结,尝试形成一种模式). 例如:

  1. 是否总是同一个用户/应用造成的?
  2. 是否是在数据导入时发生?
  3. 问题是在做了哪些操作或修改了哪些之后产生的?

 

之后,我们需要收集更多信息以了解错误是如何产生的,甚至到哪些语句触发了此问题(如果问题是由一个递归SQL语句造成的话)。为了达到此目的我们需要在会话级或实例级建立Errorstack事件. 如果你能很容易地重现问题,那么用会话级,否则则需要在实例级建立事件。

 

因此,假设我们收到一个ORA-904  “invalid column”报错,我们可以使用:

 

alter session set events ‘904 trace name errorstack level 3’;

alter system set events ‘904 trace name errorstack level 3’;

event = “904 trace name errorstack level 3”  (in the init.ora)

 

一旦我们获取了trace文件,我们就能从以下对象中找出实际产生错误的SQL语句:

  1. “当前执行的SQL”
  2. “当前执行的游标”

然后你就能直接通过这些找到的SQL来重现问题。而这已经足以用来确认出原因。

 

更多相关其他信息请看:


Database Performance数据库性能问题

为诊断数据库性能问题所要收集的信息:

  • 提供对以下问题的回答 à
  • 性能退化影响到了哪些方面?
  • 是否DBA能登陆并检查v$视图?
  • 你是否能将性能回复到正常? 如何做到的?
  • 提供STATSPACK/AWR/ADDM 报告以能对性能问题做一些短期快照. 请不要跑得报告时间段过长超出问题发生时间. 对实例来说,如果问题持续了半小时,那么抓取那个时间段中20分钟的状态为佳。如果你能精确把握整个时间过程,那么在性能问题发生前获取第一个快照,然后在产生性能问题的过程段中获取另一个快照。请不要在两个快照间重启实例,因为这将使得获取的结果变得无意义。
  • 你也可以在系统运行得“不错”的时候做一个快照(相同时间长度),这个快照可以作为和有性能问题的快照对比时基准。
  • 如果我们需要一个更短时间片上信息(不如当前当前的分析需要5到10分钟的详细活动信息), 那么可以查看ASH (Active Session History活动的会话历史) 报告输出. ASH报告工具在判断活动会话数量,查询会话从事的任务和在一段时间最活跃的SQL语句时很有用。它特别擅长用于分析短暂的性能问题。  运行此报告的ashrpt.sql脚本可以在 $ORACLE_HOME/rdbms/admin目录中找到。
  • 收集操作系统参数来衡量其活动(在运行正常和性能不佳时):
  • CPU
  • 磁盘I/O
  • Memory/Swap
  • 进程活动
  • 操作系统监控程序OS Watcher输出
  • 提供 2个 Hanganalyze报告和 3 个Systemstate转储信息报告 (具体请看“数据库挂起”部分章节)。
  • 提供系统RDA收集报告。
  • 提供Oracle LTOM工具收集的信息。

 

更多其他相关信息请看:

 

 


监听器挂起及性能问题

当监听器(Listener)发生性能问题时设置一个级别16的客户端连接追踪。

 

在客户端sqlnet.ora文件中设置参数:

TRACE_LEVEL_CLIENT=16

TRACE_FILE_CLIENT=CLIENT

TRACE_DIRECTORY_CLIENT=full path to directory where you want the trace file created.

TRACE_TIMESTAMP_CLIENT=on

TRACE_UNIQUE_CLIENT=on

 

获取这几分钟监听器进程(tnslsnr)的truss命令输出以及(发生问题的时间段)不同时间点上pstack信息输出。

 

====================================================

 

Truss ==> truss -o /tmp/mytruss -faed -p <pid of listener process>

…这个工具会一直保持追踪直到你终止它。可以使用ctrl-c来终止truss。如果你是在后台运行此工具命令,那么可以通过kill来杀掉truss进程以终止它的运行。

 

Process stack ==> pstack <pid of listener process>

…执行3次此命令,之间间隔30秒左右

 

如果可以的话,做一个短暂的监听器追踪:

=============================================

 

在服务器端sqlnet.ora文件中设置参数::

 

TRACE_LEVEL_SERVER=16

TRACE_DIRECTORY_SERVER=<some_known_directory>

TRACE_FILE_SERVER=server

TRACE_TIMESTAMP_SERVER=ON

TRACE_FILELEN_SERVER=<file_size_in_Kbytes>

TRACE_FILENO_SERVER=<number_of_files>

 

…后2个参数是为了避免服务器上的监听器trace文件过多而设的. 举例如果你在客户端设置了以下参数:

TRACE_FILELEN_SERVER=100

TRACE_FILENO_SERVER=2

 

…那么客户端上的2个100K的trace文件会被先后填满。之后客户端会切换回第一个trace文件继续写入trace信息,覆盖原有的trace内容,当写满后,再到第二个trace文件中写,如此往复。因此在trace信息被覆盖前,你需要将这些文件拷贝到其他目录。

 

我们也可以通过使用LSNRCTL 来对监听器进行追踪,并且此工具会一直运行会话结束(除非你主动关闭它).在 LSNRCTL 命令中不存在“=”符号.

 

LSNRCTL set current_listener listenername

LSNRCTL SET TRC_LEVEL 16

…好了,这样LSNRCTL就开始追踪了. 让它跑一会,这样我们就能看到追踪到的行为信息以及相关的延迟了。

 

在跑了至少5分钟后, 关闭LSNRCTL:

LSNRCTL set current_listener listenername

LSNRCTL SET TRC_LEVEL OFF

 

在使用 LSNRCTL进行追踪时, 在listener.ora文件中设置 ADMIN_RESTRICTIONS_listener_name=ON 以禁止运行时其参数的修改. 这样,监听器就会拒绝SET命令对其参数的修改了。更多信息请看 Note:272633.1.

 

如果需要改变listener.ora中包括ADMIN_RESTRICTIONS_listener_name本身以及其他参数的设置。我们将不得不手工修改listener.ora文件并用RELOAD命令使得参数起效。

 

请注意:  服务器端的追踪并不会在你改了Oracle Net(服务器端) tracing参数为OFF后关闭。它会随着客户端tracing的关闭而自动关闭。如果tracing由于疏忽一直保持启用, 则它可能将导致非常巨大trace文件的产生并损耗服务资源。为了正确关闭Oracle Net server tracing, 在设置tracing参数值为OFF后主动 stop并restart Oracle Net server 进程. 这可能会导致一些进程的关闭并引发数据库重启,严重程度取决于当前涉及到的会话数量。

 

因此,你需要上传以下文件:

 

– 客户端sqlnet trace文件

– 对于客户端低效连接的Pstack trace

– 监听器进程的Truss输出

– 3个对监听器进程的Pstack traces (3 of them).

– alert.log

– listener.log

– listener traces

 

更多其他相关信息请看:


SQL性能问题

对于SQL语句性能问题诊断, 我们需要了解:

  • 对于存在性能问题的SQL语句. 你能否通过使得性能回归正常? 如何修改?
  • 提供SQL在良好和较差性能下的执行计划输出.
  • 设置级别12的事件10046追踪以生成trace文件(在良好和较差性能情况下) à

alter session set events ‘10046 trace name context forever, level 12’;

  • 在良好和较差性能情况下的SQL运行 Tkprof输出
  • 在良好和较差性能情况下的SQL运行时间
  • 在执行计划输出中对涉及对象的定义 (如 DDL).
  • 在执行计划输出中对涉及基表的行数统计
  • 在良好和较差性能情况下的事件10053的trace输出à

 

ALTER SESSION SET EVENTS ‘10053 trace name context forever, level 1’;

 

…为了获取有效 10053 trace信息, 就不可避免需要对SQL语句进行硬解析。我们可以通过改变SQL语句的大小写或空格多少来达到此目的或通过清空共享池(在生产环境中并不推荐)来实现。

 

Oracle 10g及以上版本:

  • 你能使用AWR/Statspack报告和awrsqrpt.sql脚本来获取2个AWR快照之间的SQL语句执行计划。从AWR/StatsPack报告中, 定位产生性能问题的语句的SQL_ID并生成以下:

— 对应SQL_ID的AWR SQL报告

— 以SYSDBA登陆SQL*Plus

— SQL> @?/rdbms/admin/awrsqrpt.sql

— 请确保所使用AWR快照都是来自于获取的SQL_ID地方。

 

  • 对AWR报告中相应SQL_ID的查询提供数据字典信息:

— 以SYSDBA 登陆SQL*Plus.

— SQL> SELECT PLAN_TABLE_OUTPUT FROM

TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘<sqlid>’));

 

其它信息收集 (特别对于bug) à

对于执行计划的查询性能问题,请收集所有相关查询对象输出转储和其统计参数,通过使用这些信息,我们可以及时重现这些良好和较差的执行计划。

 

更多其它相关信息请看à

  • Note:21154.1 – EVENT: 10046 “enable SQL statement tracing (including binds/waits)”
  • Note:225598.1 – How to Obtain Tracing of Optimizer Computations (EVENT 10053)
  • Note:41634.1 – TKProf Simplistic Overview
  • Note:372431.1 – TROUBLESHOOTING: Tuning a New Query
  • Note:398838.1 – FAQ: Query Tuning Frequently Asked Questions
  • Note:163563.1 – TROUBLESHOOTING: Advanced Query Tuning
  • Note:208340.1 – Troubleshooting SQL Tuning
  • Note:215187.1 – SQLT (SQLTXPLAIN) – Enhanced Explain Plan and related diagnostic info for one DML SQL statement

 

如果由于数据库升级导致的查询性能退化,请看 à

  • Note:160089.1 – Server Upgrade Results in Slow Query Performance
  • Note 258167.1 – Upgrading from 8.1.X to 9.X – Potential Query Tuning Related Issues
  • Note 295819.1 – Upgrading from 9i to 10g – Potential Query Tuning Related Issues
  • Note:752662.1 – TROUBLESHOOTING Query Tuning
  • Note:748200.1 – Slow Performance of Queries After Upgrading To 10g

 


安装/升级/重连问题

对于数据库在安装或重连时发生问题的信息收集:

  • /tmp/OraInstall/oraInstall.err
  • /tmp/OraInstall/oraInstall.out
  • ../oraInventory/logs/installActions.log
  • $ORACLE_HOME/install/make.log
  • 对于补丁安装的OPATCH工具日志

 

更多其他相关信息请看 à


RAC 性能问题

请具体描述问题. 当提到问题发生时间时,请尽量精确,如某个特定时间等。

 

请提供RAC配置细节:

  • 在集群中节点号
  • 确定是否第三方集群件正和集群就绪服务(CRS)一起被使用
  • 注册盘和表决盘的存储类型
  • 确认ASM是否正被使用
  • 每个节点上数据库实例的数量

 

RAC 诊断(racdiag.sql)脚本被用于提供用户友好向导并对RAC挂起会话及低性能情景进行问题排查。脚本包括了用于判断RAC挂起原因而进行收集大量重要调试信息的相关操作信息。此脚本在转储hanganalyze信息的同时,并会在本地目录中建立一个racdiag<timestamp>.out文件。

 

除了从查询输出中判定锁和等待会话之外,racdiag脚本还会动态生成 systemstate 转储和 hanganalyze信息输出。此外, racdiag还提供了内部连接性能的主要度量标准。

 

从文档 135714.1中对于 racdiag的描述 à 这个脚本应该能在RAC环境中出现会话或系统级挂起时运行。脚本应该以SYS用户运行。在运行racdiag脚本前, catparr.sql应该已经以SYS运行过,以在受影响的数据库中建立相关GV$视图。有一些系统挂起类型事件能够阻止racdiag脚本的运行。 如果此脚本跑不起来,那么建议以SYS用户在每个节点获取systemstate转储信息以帮助调试问题。

 

请上传以下文件:

  1. sql脚本输出文件
  2. 每个节点的AWR/ASH报告或 Statspack输出
  3. 操作系统监控器输出(如果此监控器正运行在当前系统上)

 

相关racdiag脚本及其他信息请看à

  • Note:135714.1 – Script to Collect RAC Diagnostic Information (racdiag.sql)
  • Note:412894.1 – Diagnosing Hangs and Troubleshooting Performance Issues in a RAC Environment
  • Note:556679.1 – Data Gathering for Troubleshooting RAC Issues
  • Note:810394.1 – RAC Assurance Support Team: RAC Starter Kit and Best Practices

 


RAC实例驱逐问题

请具体描述问题. 当提到问题发生时间时,请尽量精确,如某个特定时间等。

请提供RAC配置细节:

  • 集群中的节点数量
  • 是否有第三方集群件正和集群就绪服务(CRS)一起被使用
  • 注册盘和表决盘的存储类型
  • 确认ASM是否正被使用
  • 每个节点上数据库实例的数量

 

对于实例驱逐被踢(Eviction)问题的诊断– 如ORA-29740错误, 请参考以下文档来做数据收集工作:

  • Note 219361.1 – Troubleshooting ORA-29740 in a RAC Environment
  • Note:412884.1 – Data Gathering for Instance Evictions in a RAC environment

 

至少你需要上传:

  1. 对于此类问题,除了上传数据库实例后台进程相关trace文件以外, 也要上传(每个实例)RAC特定后台进程 (如LMON, LMS, LCK, LMD, DIAG 等) 相关trace文件.

注意: 如果问题导致实例重起, 请确保以上上传文件来自重起之前的实例。

  1. 每个实例的告警日志文件
  2. 获取被踢的每个实例AWR/ASH报告或 Statspack输出
  3. 使用pl工具来收集Oracle集群件日志. 更多详情请看文档 330358.1. 运行diagcollection.pl时,使用 ‘–nocore’参数选项可降低输出的文件大小.
  4. 系统消息日志,具体文件位置取决于所使用的操作系统:

– Linux: /var/log/messages

-Windows: 使用事件查看器查看保存的应用日志和系统日志TXT文件。 – Sun: /var/adm/messages

– IBM: /bin/errpt -a (被重定向到messages.out文件)

– HP-UX: /var/adm/syslog/syslog.log

– Tru64: /var/adm/messages

  1. 如果有操作系统监控器运行,则也请获取所有节点的监控器输出

 

注意:Oracle实例被启动后,每个相关后台进程(PMON,SMON,LMS,DIAG等)会产生一个trace文件。之后仅当需要转储trace文件时,才会继续对这个trace 文件进行写入。请不要删除这些文件。如果你删除了属于某个后台进程的Trace文件, Oracle trace信息仍会保持向这个节点位置中写入。这造成了有用的诊断信息被丢失。如果你发现属于某个Oracle数据库后台进程trace文件被删除,请按My Oracle Support文档中提供的步骤来重建它:

Note:394891.1 – How to recreate background trace file(s) that may have been accidentally deleted


Oracle集群就绪服务(CRS)问题

对于CRS问题:

 

  • 如果正在使用第三方提供的clusterware (SFRAC, SUN Cluster等.), 首先判断是否第三方cluster正在运行 (参考文档 803661.1).

 

  • 检查当前CRS是否正在运行:

◦ crsctl check crs

◦ crs_stat –t

 

  • 检查是否所有私有内连接都起了并可ping通

◦ ifconfig –a

◦ traceroute <private interconnect IP>

 

  • 检查表决盘(voting disks)和注册盘(OCR)都可以从所有节点访问到

◦ ocrcheck

◦ ls –l <full path of voting disk>

 

  • 请提供以下信息:
  • sql输出
  • CLUVFY (Cluster 验证工具) 输出.
  • 系统消息日志. 其存放位置取决于当前操作系统:

– Linux: /var/log/messages

– Windows: 使用事件查看器查看保存的应用日志和系统日志TXT文件。

– Sun: /var/adm/messages

– IBM: /bin/errpt -a (重定向到 messages.out文件)

– HP-UX: /var/adm/syslog/syslog.log

– Tru64: /var/adm/messages 如果监控工具正在运行,其操作系统监控输出将存放在此目录中

  • 从每个cluster节点上的三个目录树中收集所有CRS日志文件, trace文件和core转储文件 :

– $CRS_HOME (css/log css/init crs/log crs/init evm/log evm/init srvm/log racg/dump log)

– $ORACLE_HOME (racg/dump admin/*/hdump)

– $ORACLE_BASE (admin/*/hdump)

…也可以用另一种方法获取打包的trace文件:

$CRS_HOME/bin/diagcollect.pl

 

其他相关信息请看:

  • Note 339939.1 – Running Cluster Verification Utility to Diagnose Install Problems
  • Note 272332.1 – CRS 10g Diagnostic Collection Guide
  • Note 330358.1 – CRS 10gR2/ 11gR1/ 11gR2 Diagnostic Collection Guide
  • Note:289690.1 – Data Gathering for Troubleshooting CRS Issues
  • Note:783456.1 – CRS Diagnostic Data Gathering: A Summary of Common tools and their Usage
  • Note:556679.1 – Data Gathering for Troubleshooting RAC Issues
  • Note:357808.1 – Diagnosability for CRS / EVM / RACG as directed by Oracle support


Data Guard 日志传输和物理应用

– 确认归档日志是否成功被传送到备库。

– 判断Data Guard设置是非级联设置的还是级联设置,物理备库设置还是逻辑备库设置。- 确认主库是归档日志模式并已开启自动归档。

– 确认归档有足够空间。

– 判断是否产生错误的条件都满足。

– 确认备库处于mounted状态。

– 确认managed recovery 正在运行.

 

其他相关信息请看:

  • Note:237213.1 – Troubleshooting Data Guard
  • Note:312434.1 – Oracle10g Data Guard SQL Apply Troubleshooting
  • Note:814417.1 – Dataguard Information gathering to upload with the Service Requests
  • Note:241374.1 – Script to Collect Data Guard Primary Site Diagnostic Information
  • Note:241438.1 – Script to Collect Data Guard Physical Standby Diagnostic Information


Data Guard网络故障

– 检查系统瓶颈: CPU (sar -u), I/O (iostat), Memory (vmstat)

– 数据库等待事件 (ARCH 传输, SYNC传输等.)

– 查看Statspack Stats报告 (重做率”redo rate”, 事务率”transaction rate”, 平均重做写大小”average redo write size”,…)

– 检查网络 (备库是否可连?, 是否有网络错误?, 网络带宽是否足够?)

– 检查alert.log (主库和备库)文件中的所有错误.

– 检查系统消息日志中的错误. 日志文件位置取决于你所用的系统:

– Linux: /var/log/messages

– Windows: 应用日志和系统日志被保存为 .TXT文件并使用事件查看器查看

– Sun: /var/adm/messages

– IBM: /bin/errpt -a (重定向到文件 messages.out)

– HP-UX: /var/adm/syslog/syslog.log

– Tru64: /var/adm/messages

 

更多其他信息请看:

 

 


Streams 错误及问题

对于问题解决及原因分析,请à

 

  • 具体描述问题。
  • 描述Streams设置信息 – local capture or downstream capture, number of Streams database, unidirectional or bi-directional replication, etc.
  • 来自源和目标系统的Alert 文件.
  • 当报告Streams问题时,请提供源和目标数据库健康检查报告 (请看 Note 273674.1)
  • 运行Streams 故障发现查询脚本. 参考 Note 729860.1.
  • 对于STREAMS 性能问题:

◦ 请参考Streams Performance recommendations (Note 335516.1).

◦ 请参看Note 290605.1在源和目标数据库运行STRMMON.

◦ 生成源及目标数据库当时时段AWR/ASH/ADDM报告.

 

更多其他信息请看à

  • Note:746247.1 – Troubleshooting Streams Capture when status is Paused For Flow Control
  • Note:335502.1 – How To Reinstantiate a Single Table in a Streams Environment
  • Note:749036.1 – How to re-synchronize the streams replicated objects online
  • Note:405541.1 – Print the LCR’s listed in the Error Queue
  • Note:291686.1 – LogMiner Utility Release 8.1.x – 10g

 

 


Streams 应用(Apply)

当对Streams Apply进程进行问题诊断时,我们需要注意以下3种情况:

 

1) 没有事务到达目的地

  • 检查apply进程状态并确认其是启用的
  • 确认apply进程是否还没使事务出队列。
  • 检查队列的传播是否有发送事务到应用端
  • 检查相关规则是否有在global, schema和table每个级别上正确建立以控制(获取capture, 传播propagation, 应用apply)
  • 检查 init.ora文件
  • 检查BACKGROUND_DUMP_DEST下的trace文件

 

2) 事务到达了目的地端, 但没有出队列.

  • 确认是否 apply进程没有让任何事务出队列
  • 检查出错队列
  • 检查是否有定义冲突的方案
  • 检查对象实例化SCN
  • 检查当前数据库 SCN值
  • 检查BACKGROUND_DUMP_DEST下的trace文件

 

3) 事务到达目的地端并出队列, 但没有被应用.

  • 检查apply进程延迟
  • 检查出错队列
  • 检查是否有定义冲突的方案
  • 检查对象实例化SCN
  • 检查当前数据库 SCN值
  • 检查BACKGROUND_DUMP_DEST下的trace文件

 

注意: 任何情况下, 请通过Streams健康检查脚本以提供报告输出。

 

请看以下文档以便了解与上文相关的更详细信息 à

  • Note:230898.1 – How To Troubleshoot the Streams Apply Process
  • Note:273674.1 – Streams Configuration Report and Health Check Script
  • Note:730036.1 – Overview for Troubleshooting Streams Performance Issues
  • Note:418755.1 – 10gR2 Streams Recommended Configuration
  • Note:789913.1 – Streams Troubleshooting guide step by step
  • Note:779801.1 – Streams Conflict Resolution
  • Note:265201.1 – Troubleshooting Streams Apply Error ORA-1403, ORA-26787 or ORA-26786

 

 


高级队列传播(AQ propagation)问题

如果你碰到高级队列传播问题 (很慢或卡住), 请收集以下信息:

 

sqlplus / as sysdba

 

oradebug setospid  <spid of the j00 background process executing the schedule>

oradebug unlimit

oradebug Event 10046 trace name context forever, level 12

–等待10分钟

oradebug Event 10046 trace name context off

exit

 

sqlplus / as sysdba

oradebug setospid  <spid of the j00  background process executing the schedule>

oradebug unlimit

oradebug Event 24040 trace name context forever, level 10

–等待10分钟

oradebug Event 24040 trace name context off

exit

 

oracledebug tracefile_name命令能显示trace文件在哪里生成

 

当传播问题发生,我们需要从所有实例中获取相关systemstate转储信息à

 

sqlplus / as sysdba

oradebug setmypid

oradebug unlimit

oradebug -g all dump systemstate 266

— 等大约2分钟

oradebug -g all dump systemstate 266

 

 

相关其他信息请看 à

  • Note:233099.1 – Troubleshooting Advanced Queuing Propagation
  • Note:102926.1 – Performance Tuning Advanced Queuing Databases and Applications


复制问题(Replication)

相关信息请看:

  • Note:1035874.6 – Troubleshooting Guide: Replication Propagation
  • Note:122039.1 – Troubleshooting Basics for Advanced Replication
  • Note:231499.1 – Multi-Master Replication: Diagnostic Information Required & What To Check

 

 


 ASM 问题诊断 (包括在空闲空间存在并可用时的 ASM磁盘空间耗尽报错)

====================================================================

 

▪请执行My Oracle Support Note:351117.1 中的ASM调试脚本 (收集诊断ASM空间问题所需信息)

 

▪ 提供ASM alert.log文件, traces 及操作系统消息文件 (可能同时需要数据库和 ASM alerts 和traces文件)

 

更多其他信息请看à

  • Note:340417.1 – Data Gathering for Troubleshooting ASM Issues
  • Note:309815.1 – Configuring Oracle ASMLib on Multipath Disks
  • Note:284646.1 – Creating and using the kfed utility to view ASM disk header

 

 


消息网关(Message Gateway)问题

当消息网关不处理任何信息,请获取以下诊断信息进行分析:

 

对Hang问题及 系统State进行信息收集及转储à

 

sqlplus / as sysdba

oradebug setmypid

oradebug unlimit

oradebug –g all hanganalyze 3;

 

等待30秒后再次运行hanganalyze…
oradebug setmypid

oradebug unlimit

oradebug –g all hanganalyze 3;

 

Sqlplus / as sysdba;

oradebug setmypid;

oradebug unlimit;

oradebug –g all dump systemstate 266;

 

等待30秒后再次运行hanganalyze…

 

oradebug setmypid;

oradebug unlimit;

oradebug –g all dump systemstate 266;

 

当消息网关不处理消息时,扩大gateway日志级别以收集更多gateway进程相关信息。

 

sqlplus / as sysdba;

SELECT * FROM MGW_GATEWAY;

exec DBMS_MGWADM.SET_LOG_LEVEL (3);

 

上传MGW trace文件.

 

获取MGW Java进程相关thread转储文件:

 

ps -ef | grep mgwextproc

 

使用以下脚本命令得到JVM下的thread转储信息

 

for pids in `ps -ef | grep mgwextproc | awk ‘{print $2}’`

do

echo $pids

kill -3 $pids

done

Trace文件会在当前工作目录中产生。请上传这些trace文件。

 

 

ORA-3113错误

ORA-3113错误通常产生自Oracle客户端工具。此错误意味着客户端未能和Oracle影子进程进行通讯。此错误常常需要我们收集更多的信息来帮助分析原因,且原因也多种多样。因此这种“oracle影子进程通讯失败”错误是一种“普遍涉及”类型。ORA-3113常发生在当由于某些原因致Oracle服务器进程死亡时。

 

我们需要更多信息用来判断错误成因:

 

  1. 错误是发生在尝试建立数据库连接时或在一个已经建立的连接上?
  2. 在USER_DUMP_DEST(或11g上的$ADR_HOME/trace)目录下存在相关失败会话的trace文件。
  3. 此错误是因为特定命令造成的或是随机产生的? 如果是由于特定命令造成的,在会话级别开启 SQL_TRACE以找到问题命令.

 

其他相关信息,请看 à

 

 


数据库/实例崩溃

为方便解决及原因分析, 请提供以下内容 à

 

  • BACKGROUND_DUMP_DEST或$ADR_HOME/trace下的Alert.log文件
  • Alert log文件中最新涉及的错误(如ORA-600或ORA-7445)相关的trace文件.
  • 当时Oracle自动收集的系统状态转储文件
  • RDA (Remote Diagnostic Agent远程诊断代理)输出,可参考Note 314422.1

 


数据库块讹误(Corruption)

有很多原因可能造成坏块,其包括:

– IO硬件损坏

– 操作系统问题

– Oracle造成的问题

– 对数据库中已使用”UNRECOVERABLE”或”NOLOGGING”关键字命令操作的数据恢复(这种情况会提示ORA-1578错误)

 

Oracle发现并报错的时间可能晚于数据最初产生讹误的时间。

由于并非总能了解当时坏块产生的原因,而多数情况下,关键业务需求数据库立即响应并再次开始运行,因此以下步骤用于处理当前问题:

 

  • 判断数据讹误问题的影响程度及范围,是临时的还是永久不可恢复的错误。如果坏块大范围出现或者错误在各处不断产生,那么就需要先集中精力先确定问题原因(如检查硬件设备等)。这一点很重要,如果你的硬件本身出了问题,那怎么操作都无法恢复系统。

 

  • 替换或移走(可能)存在问题的硬件设备。

 

  • 判断哪些数据库对象收到了影响。

 

  • 选择最恰当的数据库恢复/数据解救方案。

 

 

为方便解决及原因分析, 请提供以下内容à

 

  • BACKGROUND_DUMP_DEST 或 $ADR_HOME/trace下的Alert.log文件
  • Alert log文件中最新涉及的错误(如ORA-600或ORA-7445)相关的trace文件
  • Block转储及其他相关诊断信息,可参考 Note 28814.1
  • RDA (Remote Diagnostic Agent远程诊断代理)输出,可参考Note 314422.1

 

更多相关信息请看à

  • Note:1088018.1 – Master Note for Handling Oracle Database Corruption Issues
  • Note:28814.1 – Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g/11g
  • Note:412566.1 – Basics of Debugging/Getting Dumps on Windows and Unix Platforms for Internals/Corruption Analysts
  • Note:76375.1 – Prevention, Detection and Repair of Database Corruption

 

 


RMAN 报错

问题解决及原因分析à

 


Oracle企业管理器 / 网格控制(OEM/Grid Control)

 

为方便问题解决及原因分析,请à

 

  • 具体描述问题
  • 对于EM Agent问题:

– 打包压缩并上传$AGENT_HOME/sysman/log中的文件

– $AGENT_HOME/sysman/config/emd.properties文件

Note 229624.1 – How to Log and Trace the EM 10g Management Agents

  • 对于OMS错误及问题:

– 打包压缩并上传$OMS_HOME/sysman/log中的文件

– $AGENT_HOME/sysman/config/emoms.properties文件

Note 229627.1 How to Log and Trace the EM 10g Management Service

Note 421053.1– EMDiagkit Download and Master Index

 

更多相关信息请看 à

 

 


SQL*Net 报错

问题解决及原因分析à

 

  • Note:16564.1 – SQL*Net V2 on Unix – A Quick Guide to Setting Up Client Side Tracing
  • Note:219968.1 – SQL*Net, Oracle Net Services – Tracing and Logging at a Glance
  • Note:67983.1 – Oracle Net Performance Tuning

 

 


补丁应用问题

问题解决及原因分析à

 

为了调试检查补丁问题,需要设置:

◦ export OPATCH_DEBUG=TRUE

◦ export PERL_DL_DEBUG=1

 

请看相关信息 à

 

 


JDBC事件

问题解决及原因分析 à

 

 

 


Memory Leaks内存泄漏

内存泄漏是指当进程始终不能归还其做临时使用而分配的内存时,可用内存在这种分配下逐渐损失的情况。内存泄漏最终可导致可用内存不断变少最终耗尽。如果不做检查,那些正受到内存泄漏的进程将不断增长对内存的需求直到其达到当前系统对用户模式下进程设置所允许的最大值。因此,对内存基线需求和内存泄漏之间的判断就很必要了。当然内存使用的增长并不一定归因于内存泄漏。

 

问题解决及原因分析à

 

  • Note:477521.1 – How To Troubleshoot Memory Leaks on Microsoft Windows
  • Note:477522.1 – How To Troubleshoot Memory Leaks on UNIX
  • Note:1003841.1 – Diagnosing swap full problems/possible memory leak issues
  • Note:166490.1 – Diagnosing Oracle memory on HP using GLANCE
  • Note:163763.1 – Diagnosing Oracle memory on Sun Solaris using PMAP
  • Note:166491.1 – Diagnosing Oracle Memory on AIX using SVMON
  • Note:403584.1 – Understanding and Diagnosing ORA-600 [729] Space Leak Errors

 

 

 

 

 

ORA-00600[qcsfbdnp:1]错误解析

ORA-00600[qcsfbdnp:1]错误解析 ,该错误主要对应  bug 13496884  ORA-600 [qcsfbdnp:1] from Merge Statement with Bind  Variables

Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 12.1
Versions confirmed as being affected
  • 11.2.0.3
  • 11.2.0.2
Platforms affected Generic (all / most platforms affected)

Fixed:

The fix for 13496884 is first included in
  • 12.1.0.1 (Base Release)
  • 11.2.0.4 (Server Patch Set)
  • 11.2.0.3.6 Database Patch Set Update
  • 11.2.0.3 Bundle Patch 16 for Exadata Database
  • 11.2.0.3 Patch 18 on Windows Platforms

Interim patches may be available for earlier versions – click here to check.

Symptoms:

Related To:

  • Internal Error May Occur (ORA-600)
  • ORA-1008
  • ORA-600 [qcsfbdnp:1]
  • Merge SQL (MERGE .. USING)
  • MERGE

Description

A MERGE statement containing bind variable references sometimes fails
reporting an ORA-600 [qcsfbdnp:1] or ORA-1008 errors.
 
Note:
 The fix replaces the fix from bug:10087130
 




qcsfbdnp 其中QCS代表 Query Compile Semantic Analysis,即查询编译时的语义分析。由于这个语义分析过程无法绕过,所以该问题主要通过打补丁来解决, 可选的几个补丁方案:

 

This problem has been investigated and resolved in:
Bug:13496884 – ORA-600 [QCSFBDNP:1]

Bug:13496884 is fixed in 12.1 and 11.2.0.4.

SOLUTION

To implement a solution for Bug:13496884, please consider any of below alternatives:

  • Upgrade to RDBMS 11.2.0.4 or higher in which Bug:13496884 is fixed.

OR

  • Install 11.2.0.3.6 Database Patch Set Update or later.
  • For Windows platform bug is fixed starting with 11.2.0.3 Patch 18

OR

  • Check for the availability of one-off Patch:13496884 for your platform on My Oracle Support

ORA-1122, ORA-1110, ORA-120X相关错误

ORA-1122, ORA-1110, ORA-120X相关错误

 

 

Problem Description:
====================

You are receiving the following errors:

ORA-01122: “database file %s failed verification check”
Cause: The information in this file is inconsistent with information
from the control file. See accompanying message for reason.
Action: Make certain that the db files and control files are the correct
files for this database.

ORA-01110:”data file %s: ‘%s'”
Cause: Reporting file name for details of another error
Action: See associated error message
01110, 00000, “data file %s: ‘%s'”

And one of the following:

ORA-01201: “file size %s in header does not match size %s in control file”
Cause: The file sizes in the control file and in the file header do not
match. One of them is probably a corrutped value.
Action: Replace the corrupted file with a good one and do recovery as
needed.

ORA-01202: “wrong incarnation of this file – wrong creation time”
Cause: The creation time in the file header is not the same as the
creation time in the control file. This is probably a copy of a
file that was dropped.
Action: Restore a current copy of the data file and do recovery as needed.

ORA-01203: “wrong incarnation of this file – wrong creation SCN”
Cause: The creation SCN in the file header is not the same as the
creation SCN in the control file. This is probably a copy of a
file that was dropped.
Action: Restore a current copy of the data file and do recovery as needed.

ORA-01204: “file number is %s rather than %s – wrong file”
Cause: The file number in the file header is not correct. This is probably
a restored backup of the wrong file, but from the same database.
Action: Restore a copy of the correct data file and do recovery as needed.

ORA-01205: “not a data file – type number in header is %s”
Cause: The file type in the header is not correct for a data file. This
is probably a log file or control file. If the type is not a small
non-zero positive number then the header is corrupted.

ORA-01206: “file is not part of this database – wrong database id”
Cause: The database id in the file header does not match the database id
in the control file. The file may be from a different database, or
it may not be a database file at all. If the database was rebuilt,
this may be a file from before the rebuild.
Action: Restore a copy of the correct data file and do recovery as needed.

You may notice that the file Oracle is complaining about is offline in
v$datafile.

Solution Description:
=====================

In order to understand why the file is inconsistent with the control file/
rest of the database, please follow these steps:

1. Take note of the status of the datafiles in v$datafile.See which ones are
online and which ones are offline. If the database will not start, mount the
database to get this information.

2. Take a file header dump of all the database files.
CONNECT INTERNAL, issue the following command:
alter session set events ‘immediate trace name file_hdrs level 10’;

3.This will generate a trace file in the directory for your user dumps.
You can find this by issuing SHOW PARAMETER USER_DUMP_DEST in sqldba.

4. Contact Oracle Worldwide Customer Support to determine what procedures to
take next.

5. At this time, you may also want to assess how important this datafile is,
as this will also determine what steps to follow. For instance what
tablespace does that datafile belong to? If it belongs to an index or temp
tablespace, the resolution may be simpler than if the datafile belongs to
the system or rollback or data tablespace.

NOTE: Before following the next steps, verify with Customer Support that these
are the correct steps for you to follow.

6. If a file has a status of offline:

If the information in the file is needed, you will need to restore the file
from backup and recover the file. If this is to be done while the database is
shutdown and mounted, the file will need to be brought online in order to
apply recovery to it.

If the database is open while the file is recovered, the file should remain
offline during recovery and then be brought online after recovery is complete.

7. If the file the error is complaining about is online:

If the database is not open, offline it, try to startup and then follow step
6.

If the database is open and the information in the tablespace is not needed,
take the datafile offline and see if you are able to drop the tablespace.
Explanation:
============

All the errors above indicate that the file header has been changed in some
way that is no longer recognizable by Oracle. Usually this occurs for reasons
such as:

– file has been touched at the OS level by a user or another application
– a UNIX command (like DD or cp or mv…) was used on the file
– file was incorrectly restored from a backup(perhaps it belongs to another
database)

In most cases, the file header is not repairable and a backup of the datafile
will have to be restored.

 

dbms_hm.run_check遇到ORA-00604、ORA-01427

11.2.0.3 下尝试使用11g health monitor新特性时出现了ORA-00604、ORA-01427, 查询MOS发现 (Bug 12385172: ORA-01427 WHEN EXECUTING DBMS_HM.RUN_CHECK),当 DB中存在case when then的function index时会触发该BUG:

 

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','check-2');
BEGIN dbms_hm.run_check('Dictionary Integrity Check','check-2'); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

 

 

可以通过以下脚本找出 DB中case when then类型的函数索引:

 

 

-- Determine DDL statements (note: this will take a while to return results!)

 set long 100000

 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

 -- Checking the DDL statement
 col DDL form a100 word_wrapped
 select dbms_metadata.get_ddl(RTRIM(UPPER(object_type)),
                              RTRIM(UPPER(object_name)),
                              RTRIM(UPPER(owner))) DDL
  from DBA_OBJECTS
 where object_type='INDEX'
   and object_id
    in (select x from (select obj# x, obj#||','||intcol#,  count(obj#||','||intcol#)
          from ICOLDEP$
         group by obj#, obj#||','||intcol# having count(*) > 1)
 );

 

 

对于安装了APEX 组件或者在DBCA创建数据库时选择了General Purpose从Seed中clone数据库而非Custom Database的DB ,都会创建有”APEX_030200″.”WWV_FLOW_WORKSHEETS_UNQ_IDX”、”APEX_030200″.”WWV_FLOW_WS_UNQ_ALIAS_IDX”、”APEX_030200″.”WWV_FLOW_WORKSHEET_RPTS_UK” 三个函数索引。

如果没有实际使用APEX组件的话,我们可以直接DROP掉APEX_030200:

 

SQL> drop user "APEX_030200" cascade;

User dropped.

SQL> set long 100000
SQL>
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);

PL/SQL procedure successfully completed.

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);

PL/SQL procedure successfully completed.

SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

PL/SQL procedure successfully completed.

SQL>
SQL> -- Checking the DDL statement
SQL> col DDL form a100 word_wrapped
SQL> select dbms_metadata.get_ddl(RTRIM(UPPER(object_type)),
  2                               RTRIM(UPPER(object_name)),
  3                               RTRIM(UPPER(owner))) DDL
  4   from DBA_OBJECTS
  5  where object_type='INDEX'
  6    and object_id
  7     in (select x from (select obj# x, obj#||','||intcol#,  count(obj#||','||intcol#)
  8           from ICOLDEP$
  9          group by obj#, obj#||','||intcol# having count(*) > 1)
 10  );

no rows selected

 

 

再次尝试测试health check dictionary 发现问题仍存在:

 

SQL>  exec dbms_hm.run_check('Dictionary Integrity Check','check-mac3');
BEGIN dbms_hm.run_check('Dictionary Integrity Check','check-mac3'); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

 

 

到这一步决定自己来诊断这个ORA-01427错误的根源, 因为是递归SQL层出现故障,所以这里我们可以用到ERRORSTACK来深入了解问题:

 

 

SQL> oradebug setmypid;
Statement processed.

SQL> oradebug event 1427 trace name errorstack level 4;
Statement processed.

/* 以上我们设置当触发1427错误事件时TRACE level 4的错误堆栈ERRORSTACK */

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','check-mac4');
BEGIN dbms_hm.run_check('Dictionary Integrity Check','check-mac4'); END;

*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.DBMS_HM", line 191
ORA-06512: at line 1

/* 触发ORA-01427 错误 将生成相关TRACE 信息*/

SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_7781.trc

 

来进一步观察生成的TRACE文件:

 

*** 2012-04-30 09:20:55.438
dbms_hm: (In run_check)
Begin dbkhicd_run_check
dbkh_run_check_internal: BEGIN; check_namep=Dictionary Integrity Check, run_namep=check-mac4
dbkh_run_check_internal: BEGIN; timeout=0
dbkh_run_check_internal: AFTER RUN CREATE; run_id=1281

*** 2012-04-30 09:20:55.603
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=4, mask=0x0)
----- Error Stack Dump -----
ORA-01427: single-row subquery returns more than one row
----- Current SQL Statement for this session (sql_id=gxjzd1s7m8xfj) -----
select 52, rowid, 'ind$.obj#'
  from IND$
 where obj# < 0
union all
select 57, rowid, 'ind$.type#'
  from IND$
 where type# not between 1 and 9
union all
select 58, rowid, 'ind$.pctfree$'
  from IND$
 where pctfree$ not between 0 and 99
union all
select 59, rowid, 'ind$.analyzetime <= SYSDATE'
  from IND$
 where analyzetime > SYSDATE
union all
select 51, rowid, 'ind$.obj# pk'
  from IND$
 where obj# is null
union all
select 51, rowid, 'ind$.obj# pk'
  from IND$
 where 1 > (select obj# from IND$ group by obj# having count(*) > 1)
union all
select 53, rowid, 'ind$.dataobj# range'
  from IND$
 where 1 >
       (select dataobj# from IND$ group by dataobj# having count(*) > 1)
union all
select 54, rowid, 'ind$.ts# fk'
  from IND$
 where (ts#) in (select ts#
                   from IND$
                  where (ts#) not in (select ts# from ts$)
                    and ts# != 2147483647)
union all
select 55, rowid, 'ind$.ts,file,block fk'
  from IND$
 where (ts#, file#, block#) in (select ts#, file#, block#
                                  from IND$
                                 where (ts#, file#, block#) not in
                                       (select ts#, file#, block# from seg$)
                                   and file# != 0
                                   and block# != 0)
union all
select 56, rowid, 'ind$.obj# fk_obj$'
  from IND$
 where (obj#) in
       (select obj# from IND$ where (obj#) not in (select obj# from obj$))

----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb1269160       191  package body SYS.DBMS_HM
0xb1d9f600         1  anonymous block

 

实际触发ORA-01427的是一条较长的递归SQL语句,该SQL由多个部分UNION ALL组合而成负责检测IND$基表是否存在逻辑不一致, 实际检测可以发现真真存在问题的是 这一段SQL:

 

select 53, rowid, 'ind$.dataobj# range'
  from IND$
 where 1 >
       (select dataobj# from IND$ group by dataobj# having count(*) > 1)

ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

SQL>  select dataobj# from IND$ group by dataobj# having count(*) > 1;

  DATAOBJ#
----------

     75601
     75599
     75594
     75605

 

IND$ 基表上居然存在多条dataobj#重复的记录,我们来看看是哪些对象:

 

select /*+ first_rows */
 owner, object_name, data_object_id
  from dba_objects
 where data_object_id in
       (select dataobj# from IND$ group by dataobj# having count(*) > 1)
       order by 3 ;

OWNER                          OBJECT_NAME                    DATA_OBJECT_ID
------------------------------ ------------------------------ --------------
SYS                            SYS_C0010990                            75594
OE                             WHS_LOCATION_IX                         75594
OE                             ORD_CUSTOMER_IX                         75599
SYS                            SYS_IOT_TOP_75598                       75599
SYS                            SYS_IOT_TOP_75600                       75601
OE                             CUST_ACCOUNT_MANAGER_IX                 75601
OE                             PROD_SUPPLIER_IX                        75605
SYS                            SYS_IOT_TOP_75603                       75605

8 rows selected.

 

 

OE这个Sample Schema下的多个索引居然和SYS用户的一些索引的DATA_OBJECT_ID重号; 我们不可能去改动SYS下的对象,而OE这个Schema则无关紧要,删除这些OE下的问题索引:

 

SQL> drop index oe.WHS_LOCATION_IX;

Index dropped.

SQL> drop index oe.ORD_CUSTOMER_IX;

Index dropped.

SQL> drop index oe.CUST_ACCOUNT_MANAGER_IX;

Index dropped.

SQL> drop index oe.PROD_SUPPLIER_IX;

Index dropped.

SQL> select dataobj# from IND$ group by dataobj# having count(*) > 1;

  DATAOBJ#
----------

 

再次测试后成功执行Dictionary Integrity Check

 

SQL> exec dbms_hm.run_check('Dictionary Integrity Check','check-mac5');
PL/SQL procedure successfully completed.
SQL> set pause on;
SQL> spool dic_check
SQL> SET LONG 100000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET PAGESIZE 100
SQL> SET LINESIZE 512
SQL> SELECT DBMS_HM.GET_RUN_REPORT('CHECK-MAC5') FROM DUAL;
DBMS_HM.GET_RUN_REPORT('CHECK-MAC5')
-----------------------------------------------------
Basic Run Information
 Run Name                     : check-mac5
 Run Id                       : 1301
 Check Name                   : Dictionary Integrity Check
 Mode                         : MANUAL
 Status                       : COMPLETED
 Start Time                   : 2012-04-30 09:33:28.540140 -04:00
 End Time                     : 2012-04-30 09:33:32.303679 -04:00
 Error Encountered            : 0
 Source Incident Id           : 0
 Number of Incidents Created  : 0
Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL
Run Findings And Recommendations
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1302
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgAB7 - description: Synonymn
               APEX is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1305
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWhAAu - description: Synonymn
               APEXWS is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1308
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgACO - description: Synonymn
               APEX_ACTIVITY_LOG is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1311
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgABl - description: Synonymn
               APEX_ADMIN is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1314
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgACB - description: Synonymn
               APEX_APPLICATION is referenced

 

 

这个case希望大家能了解的是对于ORA-00604这类递归SQL层的错误,报错信息本身给出的诊断信息是不完整的,需要我们通过一些工具来深入了解实际引发错误的是哪一条SQL语句,这些Recusive SQL出错的主要原因往往是BUG、或者数据字典存在不一致。如何在脱离MOS和SR帮助的情况下,安全地WorkAround绕过这个错误。

如何诊断ORA-125XX连接问题

以下这个Action Script是我收集的在解决ORA-125XX(如ORA-12560)这类网络链接故障时的一些思路,主要包括 现有的网络配置(client & server side)、监听日志、SQLNET Client trace等信息– How to troubleshooting ORA-125** connection issues:

 

ORA-12560

 

# CHECK FIREWALL, WINDOWS FIREWALL , ANTI-Virus Software First !
ping hostname
tnsping TNS
trcroute TNS
telnet  <hostname> <port>  

tracert hostname

client side
sqlplus scott/tiger@TNS

&
server side
sqlplus scott/tiger@TNS

cat /etc/hosts
cat /etc/resolv.conf
cat /etc/nsswitch.conf
ipconfig -a
ping 127.0.0.1

$ORACLE_HOME/network/admin/sqlnet.ora
$ORACLE_HOME/network/admin/tnsnames.ora
$ORACLE_HOME/network/admin/listener.ora
$ORACLE_HOME/network/admin/endpoints_listener.ora
$ORACLE_HOME/network/log/*
sqlnet.log listener.log
/var/log/messages
/var/adm/messages
errpt -a

ls -ld $ORACLE_HOME
netstat -rn
ps -ef | grep -i tns
lsnrctl status {listener_name}
lsnrvtl services {listener_name}

ulimit -a

1. Complete database alert log.

2. If the database was not restarted from the time of last occurance of the
issue,

select * from v$resource_limit

3. RAM and SWAP configured on the server.

4. ulimit settings for oracle user:

ulimit -aS
ulimit -aH

5. Kernel parameter settings:

/etc/sysctl.conf

dblogin

show parameter cluster_database

show parameter listener

$srvctl config vip -n {nodename}

$lsnrctl status listener

agent.log and the crsd.log ..

crsd agent log and the crsd.log
$crsctl getperm resource ora.LISTENER.lsnr

sql net client trace , Client side tracing is done by adding the following syntax to the client’s sqlnet.ora file:

We will need a timestamped matching set of client/listener sqlnet traces while error is reproduced in order to find the root cause of the issue.

++ Enable client sqlnet tracing.
=======================

To do this add the following to client sqlnet.ora:

TRACE_LEVEL_CLIENT=16
TRACE_UNIQUE_CLIENT=TRUE
TRACE_DIRECTORY_CLIENT=path
TRACE_FILE_CLIENT=client
TRACE_TIMESTAMP_CLIENT=ON

replace path with a local directory for the trace files. (for example c:\temp)
Do a test connection from the problematic client and check if the trace files are created.
Upload the traces containing the error to me on metalink.

++ Enable listener sqlnet tracing.
==========================
To do this edit the listener.ora and add,

TRACE_LEVEL_{listener name}=16
TRACE_TIMESTAMP_{listener name}=TRUE
TRACE_DIRECTORY_{listener name}=/tmp {– this can be any directory other than a top level directory like / or c:\

Replace {listener name} with the name of the listener. For example if your listener was called LISTENER then TRACE_LEVEL_LISTENER=16

You need to restrict the amount of disk space used by the tracing then you must also set,

TRACE_FILELEN_{listener name}=500000 {– size of the files in K
TRACE_FILENO_{listener name}=10 {– number of files

This will limit the traces to 10 files of around 500Mb, so 5000Mb in total. When the 10th file is full it will reuse file number one.
You will need to stop/start the listener for this to take effect.
When the problem reproduces please can you upload the listener trace and the listener log.

Trace_level_client=16
Trace_directory_client={path_to_the_trace_directory} # use the full path to the trace directory
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off

trace Local listener or SCAN listeners
TRACE_LEVEL_{listener_name}= 16
TRACE_TIMESTAMP_{listener_name}=on
TRACE_DIRECTORY_{listener_name}={path_to_the_trace_directory}

truss -o /tmp/lisener.out -fae lsnrctl start {listener_name}

Some Useful Note:

Note.444705.1 TroubleShooting Guide For ORA-12514 TNS listener could not resolve SERVICE_NAME given in connect descriptor
Note.761740.1 Technicians Unable To Receive Orders While MWM Components Display ODBC Errors And Are Connected
Note.119007.1 ORA-12560: Administering the Listener on UNIX – Troubleshooting
Note 276812.1 TNS-12542 Error When Executing Batch Jobs or in High Transaction Environment
Note.219208.1 Ext/Pub Client Connection via Connect Manager Fails with TNS-12564
Note.393941.1 Ext/Mod ORA-12564 Reported When Using 10g Connection Manager
Note.1116960.1 ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
Note.550859.1 Abstract TROUBLESHOOTING GUIDE TNS-12518 TNS listener could not hand off client connection.
Note.207303.1 Client / Server / Interoperability Support Between Different Oracle Versions
Note.119706.1 Troubleshooting Guide TNS-12535 or ORA-12535 or ORA-12170 Errors

For database links between different Oracle versions connections must be supported in BOTH directions in the matrix found in Note 207303.1
eg: As 9.2 -} 7.3.4 is not supported then database links between these version
are not supported in either direction.
You are trying to connect two versions (client-server) that are not certified (as confirmed by Note 207303.1) and between which exist many technical incompatibilities.

CLIENT — LISTENER — SERVER RESULT

8 11.1 8 OK
9 11.1 9 OK
10 11.1 10 OK
11 11.1 11 OK

8 11.2 8 FAILS
9 11.2 9 OK
10 11.2 10 OK
11 11.2 11 OK

9 11.1 8 OK
10 11.1 8 OK
11 11.1 8 OK

9 11.2 8 FAILS
10 11.2 8 FAILS
11 11.2 8 FAILS

The relevant relationship that appears to be at issue is LISTENER and DATABASE. Client version is not a factor.

But if the ultimate outcome is that the 11.2 (11gR2) LISTENER is indicated (though I still haven’t seen documentation of this) as not compatible with use on a ORACLE 8i (8.1.7.0) DATABASE, then we’ll capture that here and move on. I would, however, like to see some evidence of this, if it is available. I can find notes in the KB about 10gR2’s listener not supporting 8i database, and I can find notes about 11gR1 having resolved that regression. But I can find nothing regarding listener/database compatibility that mentions 11gR2, that would explain our results.
Clients should be complied with Servers , For Sever 11.2 the only supported clients are 11.2.0 , 11.1.0 , 10.2.0 : 10g end MUST be at 10.2.0.2 (or higher) respectively in order to use PLSQL between those versions. See Note:4511371.8 for more details and finally 10.1.0.5 only with extended support .

On the other Side in order to connect from listener to DB server in a supported way , Listener version should be greater than or equal to the server version .

Note 207303.1 should still be followed.

理解IMPDP ORA-19505、ORA-31640错误

若在Oracle RAC集群数据库中,则可能Data Pump Worker数据泵工作进程在非发起本Expdp的节点上被分配并工作,除非指定了EXPDP参数CLUSTER=NO。 若启动该Workder进程的节点上相应的data pump目录没有合适的权限,则remote node上的Worker将无法正常工作。 可以利用cluster=no参数来Workaround这个由于RAC引起的ORA-19505错误。

[oracle@lab1 ~]$ oerr ora 19505
19505, 00000, “failed to identify file \”%s\””
// *Cause: call to identify the file returned an error
// *Action: check additional messages, and check if the file exists

 

关于EXPDP/IMPDP的CLUSTER参数说明如下:

CLUSTER
Default: YES
Purpose
Determines whether Data Pump can use Oracle Real Application Clusters (Oracle
RAC) resources and start workers on other Oracle RAC instances.
Syntax and Description
CLUSTER=[YES | NO]
To force Data Pump Import to use only the instance where the job is started and to
replicate pre-Oracle Database 11g release 2 (11.2) behavior, specify CLUSTER=NO.
To specify a specific, existing service and constrain worker processes to run only on
instances defined for that service, use the SERVICE_NAME parameter with the
CLUSTER=YES parameter.
Use of the CLUSTER parameter may affect performance because there is some
additional overhead in distributing the import job across Oracle RAC instances.

 

关于该ORA-19505更多信息参考以下Note:

We did expdp on AIX server with mutliple files using filesetsize=10G parameter 
and when we are importing into LInux database we are facing below errors:
ORA-31693: Table data object "DBO_ACTIONAL_PROD"."ACT_SUBN_STATS_H_" failed to load/unload and is 
being skipped due to error:
ORA-31640: unable to open dump file "/u01/app/oracle/export/utl01p/act04.dmp" for read
ORA-19505: failed to identify file "/u01/app/oracle/export/utl01p/act04.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
 
When you are using a cluster database it is possible for datapump workers to start on 
any of the available nodes unless you specify cluster=N. If the nodes that start the 
workers do not have access to the datapump directory they will be unable to process 
the files. This is proved out when you specify cluster=N to remove the other nodes from the equation.
 
Use cluster=N to run the import from a single node of the RAC cluster which has file access.

ORA-01075,ORA-09925 Read-only file system问题一例

OEL 5.7 + ORACLE RAC 11.2.0.2 在运行过程中系统监控台出现journal commit I/O error的信息:

SQL> oradebug 
Message from syslogd@ at Fri Oct 14 21:17:08 2011 ...
vrh1 kernel: journal commit I/O error
[root@vrh1 ~]# tail /var/log/messages
Oct 14 21:02:49 vrh1 kernel: [Oracle OKS] ODLM hash size 62500
Oct 14 21:02:49 vrh1 kernel: OKSK-00004: Module load succeeded. Build information:   
(LOW DEBUG) USM_11.2.0.3.0_LINUX.X64_110803.1 2011/08/04 10:23:50
Oct 14 21:02:50 vrh1 kernel: ADVMK-00001: Module load succeeded. Build information:  
(LOW DEBUG) - USM_11.2.0.3.0_LINUX.X64_110803.1 built on 2011/08/04 10:28:15.
Oct 14 21:02:51 vrh1 kernel: [Oracle ACFS] FCB hash size 62500
Oct 14 21:02:51 vrh1 kernel: [Oracle ACFS] buffer cache size 276MB (41713 buckets)
Oct 14 21:02:51 vrh1 kernel: [Oracle ACFS] DLM hash size 62500
Oct 14 21:02:51 vrh1 kernel: ACFSK-0037: Module load succeeded. Build information:   
(LOW DEBUG) USM_11.2.0.3.0_LINUX.X64_110803.1 2011/08/04 10:32:50
Oct 14 21:02:51 vrh1 kernel: OKSK-00010: Persistent OKS log opened at /g01/11.2.0.3/grid/log/vrh1/acfs/acfs.log.0.
Oct 14 21:03:46 vrh1 avahi-daemon[3666]: Registering new address record for 192.168.1.162 on eth0.
Oct 14 21:17:08 vrh1 kernel: attempt to access beyond end of devic
[root@vrh1 ~]# mount
/dev/mapper/VolGroup00-LogVol00 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
/dev/sda1 on /boot type ext3 (rw)
tmpfs on /dev/shm type tmpfs (rw)
tmpfs on /dev/shm type tmpfs (rw,size=1500m)
none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)
sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)
mount: warning: /etc/mtab is not writable (e.g. read-only filesystem).
It's possible that information reported by mount(8) is not
up to date. For actual information about system mount points
check the /proc/mounts file.
[root@vrh1 ~]# cat /proc/mounts 
rootfs / rootfs rw 0 0
/dev/root / ext3 ro,data=ordered 0 0
/dev /dev tmpfs rw 0 0
/proc /proc proc rw 0 0
/sys /sys sysfs rw 0 0
/proc/bus/usb /proc/bus/usb usbfs rw 0 0
devpts /dev/pts devpts rw 0 0
/dev/sda1 /boot ext3 rw,data=ordered 0 0
tmpfs /dev/shm tmpfs rw 0 0
tmpfs /dev/shm tmpfs rw 0 0
none /proc/sys/fs/binfmt_misc binfmt_misc rw 0 0
sunrpc /var/lib/nfs/rpc_pipefs rpc_pipefs rw 0 0
/etc/auto.misc /misc autofs rw,fd=7,pgrp=3377,timeout=300,minproto=5,maxproto=5,indirect 0 0
-hosts /net autofs rw,fd=13,pgrp=3377,timeout=300,minproto=5,maxproto=5,indirect 0 0

 

root根目录变成read-only,无法写文件系统,Oracle因为无法写audit文件也无法正常登陆 :

 

[root@vrh1 ~]# pwd
/root
[root@vrh1 ~]# touch a
touch: cannot touch `a': Read-only file system
[oracle@vrh1 ~]$ sp
SQL*Plus: Release 11.2.0.3.0 Production on Fri Oct 14 21:17:52 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
ERROR:
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 30: Read-only file system
Additional information: 9925
ORA-01075: you are currently logged on
此时adrci 诊断工具也会变得无法使用
[oracle@vrh1 ~]$ adrci
ADRCI: Release 11.2.0.3.0 - Production on Fri Oct 14 21:26:43 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/s01/orabase"
adrci> show home
ADR Homes: 
diag/tnslsnr/vrh1/listener
diag/rdbms/vprod/VPROD1
diag/rdbms/g11r2/G11R2
adrci> set home diag/rdbms/vprod/VPROD1
adrci> show alert 
ADR Home = /s01/orabase/diag/rdbms/vprod/VPROD1:
*************************************************************************
DIA-48001: internal error code, arguments: [dbgvcis_ostream_write_1], [], [], [], [], [], [], []
DIA-48121: error with opening the ADR stream file [/tmp/alert_6894_47621_VPROD1_1.ado] [0]
DIA-48180: OS open system call failure
Linux-x86_64 Error: 30: Read-only file system
Additional information: 1

此时可以尝试remount root文件系统:

[root@vrh1 ~]# mount -o remount,rw /
mount: block device /dev/VolGroup00/LogVol00 is write-protected, mounting read-only
remount 不成功可能是文件系统存在corruption了
[root@vrh1 ~]# dmesg
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 539784035, count = 1
Aborting journal on device dm-0.
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 1885630053, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 808464416, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 808464432, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 808464432, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 1714630704, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 808585830, count = 1
EXT3-fs error (device dm-0): ext3_free_blocks: Freeing blocks not in datazone - block = 825045297, count = 1
EXT3-fs error (device dm-0): ext3_free_bloc
EXT3-fs error (device dm-0) in ext3_free_blocks_sb: Journal has aborted
EXT3-fs error (device dm-0) in ext3_reserve_inode_write: Journal has aborted
EXT3-fs error (device dm-0) in ext3_reserve_inode_write: Journal has aborted
EXT3-fs error (device dm-0) in ext3_orphan_del: Journal has aborted
EXT3-fs error (device dm-0) in ext3_truncate: Journal has aborted
journal commit I/O error
ext3_abort called.
EXT3-fs error (device dm-0): ext3_journal_start_sb: Detected aborted journal
Remounting filesystem read-only
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user
ext3_abort called.
EXT3-fs error (device dm-0): ext3_remount: Abort forced by user

dmesg发现EXT-3 文件系统存在问题。

使用fsck命令修复文件系统:

[root@vrh1 ~]# fsck
fsck 1.39 (29-May-2006)
e2fsck 1.39 (29-May-2006)
/dev/VolGroup00/LogVol00: recovering journal
Resize inode not valid.  Recreate? yes
/dev/VolGroup00/LogVol00 was not cleanly unmounted, check forced.
Pass 1: Checking inodes, blocks, and sizes
Root inode is not a directory.  Clear? yes
Reserved inode 3 () has invalid mode.  Clear? yes
Inode 3, i_blocks is 8, should be 0.  Fix? yes
Reserved inode 4 () has invalid mode.  Clear? yes
Inode 4, i_blocks is 8, should be 0.  Fix? yes
Reserved inode 6 () has invalid mode.  Clear? yes
Inode 6, i_blocks is 8, should be 0.  Fix? yes
Inode 8, i_blocks is 0, should be 262416.  Fix? yes
Reserved inode 9 () has invalid mode.  Clear? yes
Inode 9, i_blocks is 16, should be 0.  Fix? yes
Reserved inode 10 () has invalid mode.  Clear? yes
Inode 10, i_blocks is 16, should be 0.  Fix? yes
Inode 196716 has illegal block(s).  Clear? yes
Illegal block #12 (3560079808) in inode 196716.  CLEARED.
Illegal block #14 (1316876525) in inode 196716.  CLEARED.
Illegal block #15 (1316876163) in inode 196716.  CLEARED.
Illegal block #16 (1311179080) in inode 196716.  CLEARED.
Illegal block #37 (185253578) in inode 196716.  CLEARED.
Illegal block #44 (3560079808) in inode 196716.  CLEARED.
Illegal block #46 (1316876321) in inode 196716.  CLEARED.
Illegal block #47 (1316876163) in inode 196716.  CLEARED.
Illegal block #48 (1311179080) in inode 196716.  CLEARED.
Illegal block #69 (185253579) in inode 196716.  CLEARED.
Illegal block #76 (3560079808) in inode 196716.  CLEARED.
Too many illegal blocks in inode 196716.
Clear inode? yes
Inode 196728 has illegal block(s).  Clear? yes
Illegal block #11276 (3560079780) in inode 196728.  CLEARED.
Illegal block #11278 (1316876361) in inode 196728.  CLEARED.
Illegal block #11279 (1316876102) in inode 196728.  CLEARED.
Illegal block #11280 (1312304746) in inode 196728.  CLEARED.
Illegal block #11301 (185250670) in inode 196728.  CLEARED.
Illegal block #11308 (3560079780) in inode 196728.  CLEARED.
Illegal block #11310 (1316876361) in inode 196728.  CLEARED.
Illegal block #11311 (1316876102) in inode 196728.  CLEARED.
Illegal block #11312 (1312304746) in inode 196728.  CLEARED.
Illegal block #11333 (185250671) in inode 196728.  CLEARED.
Illegal block #11340 (3560079780) in inode 196728.  CLEARED.
Too many illegal blocks in inode 196728.
Clear inode? yes
Inode 196684 has illegal block(s).  Clear? yes
Illegal block #14 (1318562824) in inode 196684.  CLEARED.
Illegal block #15 (1315154713) in inode 196684.  CLEARED.
Illegal block #16 (1307022373) in inode 196684.  CLEARED.
Illegal block #37 (3017086173) in inode 196684.  CLEARED.
Illegal block #46 (1318562824) in inode 196684.  CLEARED.
Illegal block #47 (1313011230) in inode 196684.  CLEARED.
Illegal block #48 (1313011230) in inode 196684.  CLEARED.
Illegal block #54 (1969383788) in inode 196684.  CLEARED.
Illegal block #55 (778332533) in inode 196684.  CLEARED.
Illegal block #56 (825126771) in inode 196684.  CLEARED.
Illegal block #69 (80758554) in inode 196684.  CLEARED.

ORA-12500 TNS-00510一例

在AIX 5.3+ 9.2.0.8中listener遇到以下错误:

 

TNS-12500: TNS:listener failed to start a dedicated server process
TNS-12540: TNS:internal limit restriction exceeded
TNS-12560: TNS:protocol adapter error
TNS-00510: Internal limit restriction exceeded

 

 

TNS-00510说明是由于操作系统资源不足引起的TNS-12500, 即资源不足导致监听器不足以启动一个独占服务进程, 这种资源不足可能是由于空闲内存不足所引起的, 也有可能由例如AIX上的maxuproc参数引起,具体诊断思路如下:

 

nmon/topas
lsattr -El sys0
errpt -a 
show parameter sga
show parameter pga
查询listener.log的小,过大的listener.log也可能引起该问题
若是windows 32bit需要特殊处理 ,启动AWE/PAE

解决ORA-14098分区交换索引不匹配错误

上周在客户一套BRM系统上执行分区交换Exchange Partition操作的时候出现了ORA-14098错误,该错误是由于分区表上的LOCAL分区索引与非分区表上的索引不匹配造成的,我们来看一下这个错误:

[oracle@rh2 ~]$ oerr ora 14098
14098, 00000, "index mismatch for tables in ALTER TABLE EXCHANGE PARTITION"
// *Cause:  The two tables specified in the EXCHANGE have indexes which are
//          not equivalent
// *Action: Ensure that the indexes for the two tables have indexes which
//          follow this rule
//          For every non partitioned index for the non partitioned table,
//          there has to be an identical LOCAL index on the partitioned
//          table and vice versa. By identical, the column position, type
//          and size have to be the same.
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION

如果表上有很多的索引,以至于你无法确定到底是哪个索引引发了ORA-14098错误,那么我们可以通过trace的方式来协助定位到具体的索引:

SQL>  select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com
SQL> alter session set max_dump_file_size = unlimited;
Session altered.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter session set events '14098 trace name errorstack forever, level 4';
Session altered.
##SQL> alter system flush buffer_cache;
System altered.
Rerun Exchange Partition DDL 
SQL> ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES;
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
11g中直接查询v$diag_info就可以得到trace的路径,10g执行gettracename.sql
SELECT    d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/s01/admin/G10R2/udump/g10r2_ora_17749.trc
==========================10046/errorstack trace contents========================
PARSING IN CURSOR #1 len=127 dep=0 uid=64 oct=15 lid=64 tim=1277655207436065 hv=1207961095 ad='9098f018'
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
END OF STMT
PARSE #1:c=0,e=1145,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1277655207436059
BINDS #1:
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=98001 op='INDEX FULL SCAN SALES_UNID_TMP (cr=1 pr=0 pw=0 time=39 us)'
*** 2011-06-17 21:55:32.417
ksedmp: internal or fatal error
ORA-14098: index mismatch for tables in ALTER TABLE EXCHANGE PARTITION
Current SQL statement for this session:
ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with table SALES_TMP
INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES
我们可以在trace中看到在出现ORA-14098错误前,正在对索引SALES_UNID_TMP的Fast Full Scan

除了通过10046/errorstack的trace信息诊断外,更多的问题可以直接从DDL语句中发现,在以上示例中非分区表的DDL语句:

-- Create table
create table SALES_TMP
(
UNI_ID        NUMBER NOT NULL,
PROD_ID       NUMBER not null,
CUST_ID       NUMBER not null,
TIME_ID       DATE not null,
CHANNEL_ID    NUMBER not null,
PROMO_ID      NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD   NUMBER(10,2) not null
);
create index SALES_CHANNEL_TMP       ON SALES_TMP (CHANNEL_ID) ;
create index SALES_CUST_TMP          ON SALES_TMP (CUST_ID)    ;
create index SALES_UNID_TMP          ON SALES_TMP  (UNI_ID,TIME_ID);  --注意细节该索引是非UNIQUE的
create index SALES_PROD_TMP          ON SALES_TMP (PROD_ID)    ;
create index SALES_PROMO_TMP         ON SALES_TMP (PROMO_ID)   ;
create index SALES_TIME_TMP          ON SALES_TMP (TIME_ID)    ;

下为分区表的DDL语句:

-- Create table
create table SALES
(
UNI_ID        NUMBER NOT NULL,
PROD_ID       NUMBER not null,
CUST_ID       NUMBER not null,
TIME_ID       DATE not null,
CHANNEL_ID    NUMBER not null,
PROMO_ID      NUMBER not null,
QUANTITY_SOLD NUMBER(10,2) not null,
AMOUNT_SOLD   NUMBER(10,2) not null
)
partition by range (TIME_ID)
...............
create index SALES_CHANNEL       ON SALES (CHANNEL_ID) LOCAL;
create index SALES_CUST          ON SALES (CUST_ID)    LOCAL;
create UNIQUE index SALES_UNID   ON SALES (UNI_ID,TIME_ID) LOCAL;      -- 对应的索引是UNIQUE的
create index SALES_PROD          ON SALES (PROD_ID)    LOCAL;
create index SALES_PROMO         ON SALES (PROMO_ID)   LOCAL;
create index SALES_TIME          ON SALES (TIME_ID)    LOCAL;

解决ORA-14098错误的要点是要找出引发错误的原因。当我们交换分区的时候,我们要确保所有交换表上的索引和分区表上的本地索引匹配。这意味着如果在分区表上有N个LOCAL INDEXES,那么在交换表上就应当有N个等价的索引。这里的等价要求存在映射关系的2个索引,在列的位置、类型、大小及UNIQUE/NON-UNIQUE都要一致。

可以利用如下SQL语句来找出分区表和交换表上索引的差异:

set linesize 160 pagesize 1400
col TABLE_NAME for a30
col INDEX_NAME for a30
col COLUMN_NAME for a30
col COLUMN_POSITION for 99
col COLUMN_LENGTH for 99
col CHAR_LENGTH for 99
col DESCEND for a4
Select TABLE_NAME,INDEX_NAME, COLUMN_NAME,COLUMN_POSITION, COLUMN_LENGTH, CHAR_LENGTH, DESCEND
FROM SYS.DBA_IND_COLUMNS DICN
WHERE INDEX_OWNER = '&own'
and DICN.TABLE_NAME in ('&TABNAME1','&TABNAME2')
ORDER BY  INDEX_NAME, COLUMN_POSITION
/
select TABLE_NAME, INDEX_NAME, INDEX_TYPE, UNIQUENESS, PARTITIONED
from dba_indexes
where owner='&OWNER'
and TABLE_NAME in ('&TABNAME1', '&TABNAME2')
order by index_name
/

也可以使用Toad的Single Schema Object Compare功能来对比检验索引:
single_object_compare

对于存在主键的分区表,可以在主键上以DISABLE VALIDATE方式创建unique constraint约束,以代替全局的主键索引。若交换表(Exchange Table)上存在主键索引的话,那么建议在交换前暂时将该索引drop掉,待交换完成后再重建。

如果实在无法解决该ORA-14098错误,那么可以尝试使用EXCLUDING INDEXES子句以跳过索引维护,而在交换完成后重建相关失效索引。

owner = '&OWNER1'

沪ICP备14014813号

沪公网安备 31010802001379号