作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

本文永久地址为：https://www.askmac.cn/?p=16596

 

某天与一位新来的销售同事一同去拜访客户，当我们在调研客户数据库系统版本和补丁情况时，销售同事迷惑了。于是，我不得不私下向他简要介绍Oracle数据库版本和补丁的命名规则。他一边听我介绍，一边发出感慨：Oracle版本和补丁也太复杂了！

是啊，像我的销售同事一样，国内很多客户也不了解Oracle复杂的版本和补丁概念，更没有积极主动地进行补丁评估和实施。这也是导致国内很多Oracle数据库系统运行状况不佳的重要原因。

本章就将从Oracle版本和补丁的基本概念讲起，并重点讲述Oracle公司建议的积极、主动地实施补丁计划的策略和方法论，以及相关的最佳实践经验。

 

16.1 关于Bug和补丁的一个典型故事

2011年在走访某省网通公司时，对其网管部门的一个重要生产系统进行了一番调研。以下就是该系统在上午业务高峰时期最消耗时间的部分语句列表：

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
4,517	597	22	205.33	5.72	57h0pvsntqc2p		SELECT related_mscserver, subc…
4,505	557	24	187.70	5.71	8fk8d1qshkkd9		SELECT related_mscserver, subc…
3,836	543	38	100.94	4.86	5s9qphjwbhjqg		DELETE FROM w_kpi_trunkgroup W…
3,830	563	32	119.68	4.85	58qvpgs5n4t2y		DELETE FROM w_kpi_nodeb_rnc_h …
3,239	309	16	202.43	4.10	b8243sy9bdwmc		SELECT territory_id, subcounte…
3,182	297	16	198.91	4.03	3b94wb17m9scp		SELECT territory_id, subcounte…
1,792	188	11	162.87	2.27	gb7c1tx1rjcwk		SELECT distinct ne_id, start_t…

从单条语句执行时间分析，几乎都达到100、200多秒，也就是说好几分钟才运行出来。我的妈呀，这是交易系统啊，前台用户难道就没有抱怨？

仔细分析上述第一个语句：

SQL Id	SQL Text
57h0pvsntqc2p	SELECT related_mscserver, subcounter1, F0059, F0060, F0061, F0063, F0064, F0065, F0067, F0003, F0005, F0008, F0010, F0013, F0015, F0018, F0020, F0025, F0026, F0031, F0032, F0038, F0044, F0045, F0048, F0049, F0051, F0052, F0053, F0055, F0056, F0057 FROM wp_rnc_cause WHERE START_TIME =:1 AND related_mscserver=:2

发现应用开发人员已经在wp_rnc_cause表的START_TIME等字段上建立了索引，Oracle应该通过相关索引进行访问，但实际执行计划如下：

Id	Operation	Name	Rows	Bytes	Cost (%CPU)	Time	Pstart	Pstop
0	SELECT STATEMENT				70886 (100)
1	PARTITION LIST ALL		2179	183K	70886 (1)	00:14:11	1	25
2	TABLE ACCESS FULL	WP_RNC_CAUSE	2179	183K	70886 (1)	00:14:11	1	25

即对wp_rnc_cause表进行不合理的全表扫描。为什么Oracle会选择错误的执行计划呢？仔细分析现象：语句使用了BIND变量，并且wp_rnc_cause表按List进行分区了，再看数据库版本：10.2.0.1，于是猜想可能是撞上Oracle某个Bug了。赶紧上metalink去查询，果然是撞上这个与BIND Peeking功能相关的Bug了：

Bug 4652100: Poor plan with bind peeking for SQL against LIST partitioned tables

该Bug在10.2.0.3 Patchset就修复了。当我与该系统DBA沟通，询问为什么没有安装10g的最新Patchset时，得到的答复是：只要系统没有遇到宕机这样严重问题，否则他们从来不安装什么补丁。—– 这就是国内很多IT系统的现状：很少安装Oracle补丁，更没有积极、主动地制定补丁评估和实施计划，甚至连Oracle版本和补丁的概念都不是很清晰，还担心安装补丁反而导致系统不稳定。一旦真遇到因Oracle Bug所导致的问题，只是一味埋怨Oracle公司，甚至还有点幸灾乐祸：你看，Oracle也有Bug啊。其实，真遇到Bug，最受伤的还是客户自己。

 

Oracle版本和补丁概念

欲有效防范Oracle Bug的发生，加强Oracle版本管理，并及时安装需要的补丁，首先要弄明白Oracle版本和补丁等概念和术语。其实对大部分客户包括DBA而言，这并不是件容易的事情。

Oracle数据库版本命名规则

Oracle公司从9i之后，对Oracle数据库版本命名规则定义如下：

A.B.C.D

其中第一位（A）表示大版本号，第二位（B）表示小版本号。前两位（A.B）合称示主版本号（Major Version），例如9.2、10.1、11.2等。

对数据库产品而言，第三位（C）永远为0，该位是为Oracle其它产品所使用的。

第四位则表示补丁集（Patche Set）号，例如10.2.0.4、10.2.0.5、11.2.0.2、11.2.0.3等。下面将对Patch Set等各种补丁类型进行详细说明。

Oracle补丁术语

以下就是Oracle各种类型补丁的实施策略、修复Bug数量、发布频度，以及具体含义的综述。

类型	实施策略	修复Bug数量	发布频度	含义
Patch Set（补丁集）	主动式	1000个以上	1次/年	补丁集（Patch Set）是Oracle公司经过完整的集成和认证测试之后提供的一组补丁，在 Oracle数据库中以版本的第四位表示。例如11.2版本的第一个Patch Set为11.2.0.2。Patche Set具有累积关系，即10.2.0.5包含了10.2.0.2、10.2.0.3、10.2.0.4的补丁，11.2.0.3包含了11.2.0.2的补丁等。因此，Patche Set可以采取跳跃式安装策略，例如直接在11.2.0.1基础上安装11.2.0.3，无需安装11.2.0.2。 通常而言，除非指定与平台相关的Patch，否则所有平台的相同Patche Set所包含的Patch是一样的。 本表格下面描述的其它 Patch类型都是针对特定Patch Set的。
Update	主动式	50个以内	4次/年 (1月,4月, 7月,10月)	Oracle定期发布的比Patch Set要小的一组补丁，发布周期为一个季度一次(1月、4月、7月、10月)。包括如下两类同时发布的补丁： Critical Patch Updates（简称CPU补丁） – 与安全性相关的一组关键补丁 Patch Set Updates（简称PSU补丁） – 大多数客户比较关注的一组数据库关键补丁，并包括同期的CPU补丁。PSU补丁的推出，使得Oracle数据库版本有了第五位之说。例如针对11.2.0.2，PSU补丁包括11.2.0.2.1、 11.2.0.2.2等。 PSU和CPU补丁都是针对指定Path Set，例如10.2.0.5 、11.2.0.2等版本的 PSU、CPU补丁。PSU和CPU补丁也具有对以前版本的累积关系，例如11.2.0.2.3包括11.2.0.2.1和11.2.0.2.2的补丁。 PSU补丁总是位于Oracle公司的推荐补丁列表之中。 在Windows平台，PSU和CPU补丁的内容则以季度Bundle补丁形式（Quarterly Bundle Patch）进行发布。
Bundle	主动式或被动式	10到100个	根据需要	Bundle补丁则通常是针对某个Oracle特性的一组补丁，该类补丁也具有累积关系 Oracle公司针对Bundle补丁没有定期发布，因此客户主要根据需要采取被动安装的策略。
Interim Patch	被动式	1	根据需要	Interim补丁或称one-off补丁，则是针对某个版本和平台之上一个具体问题的小补丁。 Windows平台没有Interim补丁概念，Windows平台Patch Set补丁集之间的补丁都以Bundle补丁形式发布。

 

16.3 主动安装补丁是防范故障的最有效办法

所谓Bug，就是软件设计和开发过程中存在的一些小错误，而补丁（Patch）就是对这类错误的修复程序。就象我们普通人经常会生点小病一样，我们也经常需要对症下药。但保障身体健康的最有效方法应该是加强体育锻炼、改善营养，保持健康的生活方式，以此来预防疾病发生。与此相似，主动安装补丁也是防范因Bug而导致故障的最有效办法。

在补丁实施策略方面，Oracle公司划分为主动和被动两种。主动式（ Proactive）策略就是按照Oracle公司不同类型补丁定期发布的规律，积极评估和分析，并加以实施的策略。而被动式（Reactive）策略则是针对突发问题而被迫采取的实施策略。显然，被动式策略给系统导致的风险和损失可能更大，例如系统异常宕机等，这种异常故障无论是对客户还是对Oracle技术人员而言，面临的难度和压力也更大。

以下就是Oracle公司曾经统计的在10g平台主动安装不同的Patch Set之后，遇到各种Bug的数量：

 

 

可见，安装的Patch Set越新，遇到的问题逐步下降，系统稳定性也得到显著提高。

除了系统稳定性之外，主动的补丁实施策略还将达到如下目标：

• 主动安装CPU、PSU补丁，将保持系统安全性。
• 符合法律、审计、合规性等业界标准。
• 符合Oracle产品生命周期支持政策。

有关Oracle产品生命周期支持政策详细内容，请访问以下链接：

http://www.oracle.com/us/support/lifetime-support/index.html

该网页详细描述了Oracle针对软件、硬件和操作系统的PS（Premier Support，标准服务）、ES（Extended Support，扩展服务）、SS（Sustaning Support，延伸服务）的含义和内容。例如，以下就是Oracle产品处于PS、ES、SS服务期内所能提供的服务内容：

 

 

 

可见，当产品处于PS支持阶段，得到的服务更全面，而且更省钱。而当产品处于ES、SS支持阶段，得到的服务相对少一些，而且需要花费更多的钱。

因此，积极主动升级版本和安装相关补丁，将及时得到PS更全面、更省钱的服务。

 

16.4 Oracle数据库版本和补丁集发布时间表

欲主动、积极地实施版本和补丁管理，当然需要了解Oracle公司的版本和补丁集发布时间表，方能结合自己应用软件设计、开发进度，合理制定相应的版本和补丁管理计划。

各平台数据库版本发布时间表

以下就是Oracle公司2013年初公布的各平台数据库版本和补丁集发布时间表：

Platform	10.1.0.5	10.2.0.44	10.2.0.53	11.1.0.71	11.2.0.12	11.2.0.2	11.2.0.3	11.2.0.4
Linux x86	30-JAN-2006	22-FEB-2008	30-APR-2010	18-SEP-2008	1-SEP-2009	13-SEP-2010	23-SEP-2011	2HCY2013
Linux x86-64	24-FEB-2006	17-MAR-2008	30-APR-2010	18-SEP-2008	1-SEP-2009	13-SEP-2010	23-SEP-2011	2HCY2013
Linux Itanium9	30-APR-2006	24-SEP-2008	17-MAR-2011	Platform desupported (see Doc ID 1130325.1)	Platform desupported (see Doc ID 1130325.1)	Platform desupported (see Doc ID 1130325.1)	Platform desupported (see Doc ID 1130325.1)	Platform desupported (see Doc ID 1130325.1)
IBM Linux on POWER	Not planned	9-JAN-2009	17-MAR-2011	Not planned	Not planned	Not planned (see Doc ID 1310584.1)	Not planned (see Doc ID 1310584.1)	Not planned (see Doc ID 1310584.1)
IBM Linux on System z	26-AUG-2006	16-DEC-2008	3-JAN-2011	Not planned	Not planned	30-MAR-2011	1-DEC-2011	Q1CY2014
HP-UX PA-RISC (64-bit) See footnote 8 below regarding future support for this platform	05-FEB-2006	02-JUN-2008	15-DEC-2010	11-Nov-2008	20-MAY-2010	15-MAR-2011	16-FEB-2012	Q1CY2014
HP-UX Itanium9	07-JUN-2006	30-APR-2008	3-JUN-2010	06-OCT-2008	22-DEC-2009	19-OCT-2010	29-OCT-2011	2HCY2013
Oracle Solaris SPARC (64-bit)	05-FEB-2006	30-APR-2008	19-MAY-2010	06-OCT-2008	6-Nov-2009	24-SEP-2010	1-OCT-2011	2HCY2013
Oracle Solaris x86-64 (64-bit)	Not planned	13-NOV-2008	19-MAY-2010	Not planned	25-Nov-2009	24-SEP-2010	1-OCT-2011	2HCY2013
IBM AIX on POWER Systems	05-FEB-2006	15-MAY-2008	3-JUN-2010	06-OCT-2008	22-DEC-2009	19-OCT-2010	29-OCT-2011	2HCY2013
Microsoft Windows (32-bit)	13-FEB-2006	17-MAR-2008	19-JUL-2010	10-OCT-2008	5-APR-2010	15-DEC-2010	11-NOV-2011	2HCY2013
Microsoft Windows x64 (64-bit)	Not planned	16-MAY-2008	27-JUL-2010	13-NOV-2008	2-APR-2010	15-DEC-2010	11-NOV-2011	2HCY2013
Microsoft Windows Itanium (64-bit) 9	30-JAN-2006	2-FEB-2009	12-MAY-2011	Not planned (see Doc ID 1307745.1)	Not planned (see Doc ID 1307745.1)	Not planned (see Doc ID 1307745.1)	Not planned (see Doc ID 1307745.1)	Not planned (see Doc ID 1307745.1)
Apple Mac OS X (PowerPC)	08-JAN-2007	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete
Apple Mac OS X (Intel)	Not planned	10-April-2009 Single Instance only	Sched TBA	Not planned	Sched TBA	Sched TBA	Q2CY2012 (Instant Client Only)	TBD
HP Tru64 UNIX	18-OCT-2006	20-FEB-2009	21-Apr-2011	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete
Oracle Solaris x86 (32-bit)	18-JUN-2006	14-Nov-2008 Last patch set for this platform	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete
HP OpenVMS Alpha	15-FEB-2008	15-Dec-2008	31-Oct-2012	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete	Platform Obsolete
HP OpenVMS Itanium	Not planned	15-Dec-2008	31-Oct-2012	Not planned (see Doc ID 1307745.1)	Not planned (see Doc ID 1307745.1)	Not Planned (see Doc ID 1307745.1)	Not Planned (see Doc ID 1307745.1)	Not Planned (see Doc ID 1307745.1)
IBM z/OS on System z	06-MAR-2006	Not planned	26-OCT-2012	Unsupported Platform (see Doc ID 461234.1)	Unsupported Platform (see Doc ID 461234.1)	Unsupported Platform (see Doc ID 461234.1)	Unsupported Platform (see Doc ID 461234.1)	Unsupported Platform (see Doc ID 461234.1)
Platform	10.1.0.5	10.2.0.44	10.2.0.53	11.1.0.71	11.2.0.12	11.2.0.2	11.2.0.3	11.2.0.4

可见：

• 目前最新的版本为2.0.3，11.2.0.4最早将在2013年下半年（2HCY2013）提供。
• Oracle从11g开始已经不支持Linux Itanium、IBM z/OS on System z等平台，并且有些平台本身也已经被淘汰了。

11g补丁集发布计划和服务周期

以下是11g的补丁集发布计划和服务周期表：

 

 

从上述周期图可见：

• 11g的最后一个补丁集将于2013年下半年发布，并且Oracle一直会提供服务到2018年。因此，针对当前的Oracle数据库系统，主动制定升级到2.0.4的升级计划，将会得到Oracle最有效的技术支持和服务。
• Oracle的补丁集通常会间隔12-24个月进行发布，但最后一个补丁集，例如2.0.4可能会延长到25-30个月才发布。
• 当新的补丁集发布之后，Oracle对老补丁集的支持可能仍然会持续1-2年不等，即在老补丁集继续提供相关Bug的补丁。
• PSU和CPU补丁固定每个季度发布一个，因此上述周期图不包括PSU和CPU。

什么叫补丁终止日期（Patching end date）?

所谓补丁终止日期（Patching end date），就是Oracle公司公布在某个补丁集之上发布PSU、CPU和one-off小补丁的最终日期。除非特别说明，该时间表针对所有平台，也就是与平台无关。以下就是Oracle的Patching end date表：

Release	Patching Ends	Exceptions*
11.2.0.4	31-Jan-2018	HP-UX Itanium: Patching ends Jan 2020.  Beginning Feb 1, 2018, Sev 1 fixes only (no PSU or CPU will be produced).
11.2.0.3	TBD6
11.2.0.2	31-Oct-2013	End date extended beyond normal to overlap with 11.2.0.4.
11.2.0.1	13-Sep-2011	Patch end date for Exadata is 30-Apr-2012
11.1.0.7	31-Aug-20157	HP-UX Itanium – Patching ends Dec 2015.  Beginning Sep 1, 2015 Sev 1 fixes only (no PSU or CPU will be produced).
11.1.0.6	18-Sep-2009
10.2.0.5	31-Jul-20137	HP-UX, Linux, and Windows Itanium – patching ends Dec 2015.  Beginning Aug 1, 2013, Sev 1 fixes only (no PSU or CPU will be produced).
10.2.0.4	31-Jul-20115
10.2.0.3	22-Feb-2009	IBM Linux on Power	9-Apr-2009
		IBM Linux on System z	16-May-2009
		Microsoft Windows Itanium (64-bit)	2-May-2009
		HP Tru64 UNIX	20-May-2009
10.1.0.4	30-Jan-2007

相关说明如下：

• 10g版本至少应该安装补丁集到2.0.5，但Oracle也只支持到2013年7月31日。
• 2.0.3的最终支持日期需要等待11.2.0.4发布之后才能确定。

 

16.5 补丁实施那些事

如何降低补丁实施对业务的影响？

为什么很多客户不愿意去主动实施补丁？除了不太了解Oracle版本、补丁等概念之外，在11g之前的版本，安装补丁通常需要关闭数据库，这样对业务连续性会造成一定影响，这也是客户不轻易安装补丁的重要原因。

为降低补丁实施对业务连续性的影响，Oracle从11g之后提供了越来越多的补丁实施技术。以下分别介绍之：

• In-place和 Out-of-place补丁实施方法

在11gR2版本之前，补丁集（Patchset）相对于基础版本没有增加任何功能，例如10.2.0.5与10.2.0.1功能是完全相同的，10.2.0.5只是修复了更多的Bug。再则，10.2.0.5补丁集是直接安装在10.2.0.1的Oracle Home目录中。这种补丁实施方法叫做In-place方法。

在11g R2版本之后，补丁集（Patchset）相对于基础版本增加了新功能，也可以说补丁集成了一个新版本。例如11.2.0.3与11.2.0.1相比，不仅修复了很多Bug，而且完全是一个新版本。再则，11g R2之后补丁集必须安装在一个新的Oracle Home目录中。这种补丁实施方法叫做Out-of-place方法。

为什么Oracle要提供Out-of-place方法？就是为了降低安装补丁集对业务连续性的影响！在Out-of-place补丁实施方法中，可以在将原有版本正常工作的情况下，将新的补丁集安装在新的Oracle Home目录，在安装完成之后，再进行数据库的升级，这样有效提高了业务连续性的影响。同时，Out-of-place方法也是一种提高补丁实施可回退性的做法，因为原有版本在原来的Oracle Home目录中原封未动呢。

• 滚动升级（Rolling Upgradable）方法

所谓滚动升级（Rolling Upgradable）方法，主要是在RAC环境下，Oracle可支持挨个节点轮流安装补丁或升级的做法，例如先将节点1的RAC、ASM、Clusterware停下来，并安装相应的补丁，而其它节点保持正常对外服务。当第一个节点完成补丁安装工作并重启之后，再在节点2进行类似的工作，依次完成所有节点的补丁安装。Oracle可支持在短时间内的各节点Clusterware、RAC等版本的不一致。这样，通过滚动升级方法，Oracle最大限度地保障了业务连续性。

• 在线（Online）补丁实施方法

Oracle在11g R2版本之后还推出了所谓在线（Online）或者热（Hot）补丁实施方法，即在不停止数据库软件运行的情况下，直接安装one-off补丁。

以下就是Oracle官方给出的不同Patch类型的安装工具，安装方法是In-place或者Out-of-place，以及是否支持滚动升级：

 

Patch type	Tool	Method	Rolling Upgradable
Patchset	OUI	Out-of-place	Yes
Patch bundle	OPatch/Enterprise Manager	In-place	Most (check)
One-off patch	OPatch/Enterprise Manager	In-place	Most (check)

可见，补丁集（Patchset）通过OUI图像化工具安装，安装方法是Out-of-place，并且支持滚动升级。而Patch Bundel和One-off Patch则是通过OPatch工具，或者OEM进行安装，这些补丁安装方法都是In-place，而且大部分都支持滚动升级。通过如下命令，可确认该补丁是否支持滚动升级（Rolling Upgradable）？

— unzip patch到某个目录

— cd到该目录

$ ORACLE_HOME/OPatch/opatch query – is_rolling_patch <patch_loction>

代价不菲的升级操作

2012年某银行实施了第一套10g数据库升级到11g。虽然作为解决方案顾问，本人并未参与到实际的升级测试和实施之中，但升级之后第一天本人就接到了该银行技术人员的电话：“怎么搞的，你们11g怎么吃内存这么多?每个Server Process进程比原来大了好多倍，幸亏我们先把机器内存扩容了，否则系统要崩溃的！”

我突然想到前一年某电信公司CRM系统升级也遇到了同样的问题，甚至比该银行更不幸，直接导致了系统宕机。原因如何呢？《11gR2/Aix – Dedicated Server Processes Have Large Usla Heap Segment Compared To Older Versions (Doc ID 1260095.1)》详细描述了原委及解决方案。简言之，就是11g R2为了支持在线（Online）补丁实施功能，在AIX平台的每个Server Process需要记录更多的信息，因此内存从10g之前的几十KB，涨到了7MB!如何解决呢？去掉该功能呗。以下就是更详细的描述：

• 问题现象

通过AIX如下命令：

$ svmon -P PID（PID为某个Server Process的ID号）

…

  89b38e  80020014 work USLA heap                   sm   1904     0    0    1904  

可观察到USLA heap区域增长到1904*4K = 7MB左右，其中1904表示内存页面数，4K表示一个内存页面大小。

• 问题原因

原因就是为了支持在线（Online）补丁实施功能，Server Process连接时采用了“-bexpful”和“ –brtllib”选项，也就是通过更多内存来保留在线补丁升级所涉及的shared library等文件信息。

• 解决方式

解决方式是将AIX升级到AIX 6.1 TL07版本或AIX 7.1版本，并安装Oracle补丁13443029。

若AIX版本低于AIX 6.1 TL07版本或AIX 7.1版本，则安装Oracle补丁10190759，安装该补丁的作用就是去掉在线补丁实施功能。

安装补丁的风险

为什么很多用户除非出问题，否则不愿意主动安装补丁？的确，有很多客户安装了一些不必要的补丁之后，反而导致了一些新问题。部分原因也的确是Oracle针对各类补丁的测试强度不一而导致。以下就是Oracle针对各类补丁的测试情况：

Test	Interim	PSU/CPU	Bundle Patch	Patch Set
Install	Yes	Yes	Yes	Yes
Fix Verification	If possible	If applicable and possible	If applicable and possible	No
Basic DB Activity	No	Optional	Desired	Yes
Basic Application Functionality	No	Optional	Desired	Yes
Basic Load Test	No	Optional	Desired	Yes
Complete App Functional Flow	No	No	No	Yes
DB & App Performance & stress testing	No	No	No	Yes

可见，补丁集（PatchSet）的确是Oracle各类补丁中，进行各类测试最全面的，包括数据库基本功能测试、基础压力测试、完整的应用功能兼容性测试、数据库和应用的性能和压力测试等，从而稳定性、健壮性是最好的。因此，在Oracle推出新的补丁集（PatchSet）之后，建议客户应尽快考虑升级到这个新的补丁集（PatchSet）。

其次，PSU/CPU补丁，以及主要是针对Windows平台的Bundle Patch，虽然没有补丁集（PatchSet）测试这么全面，但毕竟这些补丁是定期（每个季度）发布的一些常见问题的修复，因此也应积极、主动考虑这些补丁的评估和实施。

而Interim补丁，或One-off补丁，则应视情况而定了。如果的确发生了与某个Patch相关的问题，则一定要及时安装这个补丁，否则就无需无病乱投医了。凡药三分毒呢。呵呵。

如何提高Bug和补丁分析和实施能力？最好的办法还是解铃还需系铃人，找Oracle原厂服务啊，这是最正宗的出处。呵呵。

 

一位客户的SR处理

2013年，采购了Exadata服务器的国内某客户不幸发生了多起严重程度不一的故障。该客户具有比较强的技术水准和服务意识，针对这些问题，他们充分利用Oracle的后台服务资源，在my.oracle.support（metalink）网站分别建立了相应的SR。但这些问题的处理却因多方因素，进展不一，有些问题甚至长达数月都未得到彻底解决，客户对Oracle服务充满了抱怨。

于是，为安抚客户情绪，我和服务销售一起去走访客户了。其实，我们还有”不可告人”的目的：那就是怀揣着 “趁火打劫”的心理，看看有没有机会兜售更高级服务，呵呵。

在本书，我们还是从问题本身着手来分析一些技术原委，以及处理SR的经验。由于这些问题几乎都与Oracle  Bug和补丁相关，因此，我把这个案例放在本章了。

 

几个月都没有进展的问题 —- 客户郁闷死了

该客户采用了Active Data Guard（ADG）作为生产系统的容灾和查询系统。不幸的是，ADG作为11g新技术，又是在Exadata上面部署，发生了一个看似很传统，但实际上完全是一个新的ORA-1555错误。客户为此创建了SR:3-7782930901，但该SR自9月8日建立，到写作本节的11月21日，期间经历了很多的反反复复，例如Oracle后台技术人员一会儿怀疑是Bug 16745102，一会儿又给了个将DELAY参数设置为30分钟的Workaround，但仍然没有找到问题的根源。

目前的状态还是让客户提供如下信息：

 

Please provide the requested data and answers to below questions:

1)
output of

select * from v$undostat;

from each machine (all nodes, primary and standby)

2) Since when is this error being observed? Was there some specific event
(maintenance, switchover between primary and standby, application/db/OS
upgrade, patch) after which the issue started occurring? Or did it start
suddenly without anything having changed from the setup of their system?

Further please provide
3) AWR reports from each machine (all nodes, primary and standby)

4) Does this issue happen only on one specific instance (node2)?
If so, what is specific for this node (configuration, application use)?

/

即一方面让客户提供生产和容灾两个节点的v$undostat视图信息，另一方面询问客户在发生ORA-1555错误之前，是否做了什么特别的操作？例如生产和容灾系统切换?应用/数据库/操作系统升级？打补丁等。同时还要生产和容灾系统的AWR报告，以及询问该错误是否只发生在一个节点，等等，等等。

试想这个SR在长达几个月的处理中，Oracle后台技术人员不断让客户收集相关数据，SR都升级到最高级别1级了，但却一直没有给出问题的根源分析，客户的可怜和悲催心情，可想而知，本人也只能深表同情。就在那天现场安抚时，我也只能对客户提出如下诚恳的建议：你就按人家的建议继续提供信息吧，而且千万别迟缓，否则你的SR老处于“Customer Working”状态，哪天人家把你的SR关闭了，就是你自己的责任了。唉！

问题已经解决了 — 客户还是不放心

• 问题现象

客户ADG环境在2013年9月5日中午12：00，突然出现数据库Hang情况，连select 1 from dual都无法返回，同时alert.log记录有大量ORA-1555错误（事后证明，ORA-1555与数据库Hang没有直接关系）。

• 问题分析和诊断过程

客户在第一时间创建了SR：3-7774805121。Oracle后台技术人员在接到SR之后的第一时间就直接电话客户，得知了系统基本配置：两台1/2配置的 Exadata服务器搭建了ADG环境，数据库版本为11.2.0.3，也已经知晓客户在重起容灾系统之后问题消失，但客户希望能找出问题根源。

于是，Oracle后台技术人员请求客户先上传相关节点的alert.log日志文件。根据alert.log日志文件信息，又请求客户上传相关trace文件、OSWatcher日志文件，以及事故发生前后的AWR报告。但客户由于对OSWatcher工具不熟悉，于是请求Oracle现场技术支持团队（ACS）到现场协助工作，将oswvmstat.zip、oswtop.zip、oswslabinfo.zip等日志信息上传给Oracle后台。

Oracle后台进一步要求客户在容灾数据库执行如下一些操作，确认数据库状态，并上传进一步的trace文件：

1. Please execute select * from v$database for the standby database into an Excel file and upload.

   2. The following SQL is used to track if database recovery progress is hanging.
   SQL> select * from v$recovery_progress order by start_time;

   3. Please zip and upload the following files from instance 1 so I will try to find where it hang:
   /u01/app/oracle/diag/rdbms/fossstd/fossdb1/trace/fossdb1_ora_89823.trc
   /u01/app/oracle/diag/rdbms/fossstd/fossdb1/trace/fossdb1_ora_95210.trc
   /u01/app/oracle/diag/rdbms/fossstd/fossdb1/trace/fossdb1_ora_96306.trc

   4. Please zip and upload the following files from instance 2:
   /u01/app/oracle/diag/rdbms/fossstd/fossdb2/trace/fossdb2_ora_112518.trc
   /u01/app/oracle/diag/rdbms/fossstd/fossdb2/trace/fossdb2_ora_1169.trc
   /u01/app/oracle/diag/rdbms/fossstd/fossdb2/trace/fossdb2_ora_1177.trc

 

终于，Oracle后台技术专家在对这些system state dump文件的分析中，发现了问题的蛛丝马迹：

 

 

SO: 0x843f69648, type: 78, owner: 0x834f3f1b0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x8288d7330, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8547, pg=0

LibraryObjectLock: Address=0x843f69648 Handle=0x857d42810 Mode=X CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0 <— exclusive mode

User=0x82cdfacd0 Session=0x82cdfacd0 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=2
LibraryHandle: Address=0x857d42810 Hash=d306ca9c LockMode=X PinMode=0 LoadLockMode=0 Status=0 <— owned by process LGWR
ObjectName: Name=SYS.fossdb

 

• 问题原因和解决方式

原来是一个Bug！

Keywords: standby library cache lock LibraryObjectLock sys.
Found:Bug 16753640 : ALL STANDBY NODES OF ADG WERE HUNG WITH “LIBRARY CACHE LOAD LOCK”

—> Bug 16717701 : ADG SHOULD GET THE INSTANCE PARSE LOCK WITH A TIMEOUT

而且该问题是修复Bug 11664426之后带出来的问题，呵呵，也就是按下葫芦起了瓢。以下就是Oracle专家的详细描述：

This fix supersedes the fix done in bug 11664426.

This only happens when the fix of bug 11664426 is in place which is the case for 11.2.0.3
or for customers who applied an one off patch for bug 11664426.

A Standby Active Dataguard database may encounter severe library cache lock contention with
possible deadlock causing the database to hang when there are a lot of DDLs on the Primary
Database.

This fix may not help much if dealing with pure contention as opposed to deadlock. But if the ADG instance
is running a RAC configuration, then it might be worthwhile applying this fix even in non-deadlock situations.

Rediscovery Notes:
If the fix of bug 11664426 is present then the ADG hang scenario shows as LGWR holding
the DBINSTANCE namespace library cache lock for the instance in X mod. LGWR itself
may appear idle but other sessions will be blocked waiting on “library cache lock” for
the DBINSTANCE namespace lock.

Workaround
Cancel the media recovery and restart.

该问题将在11.2.0.4以及12.2版本才能解决，目前的Workaround是取消Media Recovery操作，并重新启动容灾节点。

至此，Oracle后台认为针对该问题已经给出了解决方案，希望客户认可并关闭此SR。

 

• 客户的进一步要求

显然，客户难以接受这种处理方式，升级到11.2.0.4毕竟是一件浩大的工程，而重起容灾节点更是无法接受，鬼知道容灾节点什么时候会出现Hang的情况？于是时隔多日之后，客户要求Oracle提供一个基于11.2.0.3的该Bug的补丁，也就是要求Oracle做backport工作。

于是，在客户将 SR升级到1级，并且与Oracle后台进行一番“讨价还价”，客户也提供现有系统补丁情况之后，Oracle终于承诺满足客户需求，做backport工作去了。接下来，Oracle后台在经过BugDB、BDE等一番流程之后，Oracle还算给力，仅仅在一周之后，就提供了该Bug在11.2.0.3版本的补丁。

最终，客户下载了Oracle专门给他们开发这个补丁，并在生产系统直接进行实施，问题再未爆发。

• 启示及题外的话

首先，该问题从爆发到原因确认仅有4天时间，除去等待客户提供相关诊断信息的时间，Oracle后台的分析、诊断效率还是比较高的。当然，Oracle ACS现场的信息采集和问题分析，以及与后台专家的紧密配合，对促进该问题的最终解决也是功不可没的。

其次，在整个SR处理中，客户与Oracle后台服务（PS）、现场服务（ACS ）紧密配合，客户不仅应Oracle要求，及时提供了各类诊断数据，而且在Oracle已经提供了解决方式的情况下，大胆提出自己的进一步诉求，即请求Oracle专门开发针对这个问题的补丁，最终最大限度地满足了自己的服务需求。

题外的话是：当本人在那次走访该客户时，客户对该问题的解决却仍然是放心不下、惴惴不安。我问他为什么？他说：“现在Hang的问题是暂时没有出现了，不知道以后会不会还出现。”我的妈呀，我不得不给他做安抚工作了：第一， Oracle专家已经根据各种诊断信息，准确地分析出是一个Bug了。第二，Oracle甚至已经为这个Bug专门为你们开发了补丁。第三，你们安装这个补丁之后问题也不再重现了。Oracle是有理有据在处理这个问题，还有什么不放心呢？”

我在这番解释之后，客户依然是半信半疑，还在那儿跟Oracle，也是跟自己较劲，于是我只好在邮件中继续给他做心理疏导工作了：

“请相信Oracle Metalink给出的分析和建议是有官方和法律意义的，这也是Oracle作为原厂服务区别于第三方服务的一个特点。即便问题重现，Oracle也可以继续分析，不会放弃的，但建议客户自己别放弃。”

客户的回复是：“嗯，这点我绝对相信，要不然我们不会关闭SR的。 只是说现在还无法验证而已。”

我的再次回复是：“SR代表官方，SR中的描述本身就是一种承诺和验证。如果再爆发同样问题，再继续跟踪。”

客户最后缄默了，也真心希望他能释然了。呵呵。

 

本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述
1.	My Oracle Support	《Lifetime Support and Support Policies – Oracle Database Overview[ID 1351163.1]》	该文档是Oracle公司关于数据库产品服务周期和支持政策的官方文档，包括Premier、Extended、Sustaining等服务的服务期限和服务政策的详细描述。
2.	My Oracle Support	《Release Schedule of Current Database Releases [ID 742060.1]》	欲了解Oracle数据库在不同平台的各个版本发布计划和服务期限，从而合理规划自己的Oracle数据库版本和升级计划吗？请看这篇文档。
3.	My Oracle Support	《Oracle Recommended Patches — Oracle Database [ID 756671.1]》	该文档描述了Oracle官方建议的在数据库不同版本下应该安装的补丁列表。包括常见补丁，与RAC、Data Guard、Exadata、E-Business Suite相关补丁等。
4.	My Oracle Support	《Introduction to Oracle Recommended Patches [ID 756388.1]》	此文档列出了Oracle数据库、OEM、中间件（OFM）、Exalogic、Solaris、E-Business Suite等产品推荐安装补丁的文档链接。
5.	My Oracle Support	《Patch Set Updates for Oracle Products [ID 854428.1]》	这是一篇全面介绍PSU补丁的文档。
6.	My Oracle Support	《Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)》	在Metalink中搜索需要的PSU、CPU等补丁号，并不是一件容易的事情。该文档就给出了一个各版本下PSU、CPU、GI PSU、Windows下Bundle Patch的清单，方便大家快速查找相应的补丁。
7.	My Oracle Support	《Oracle 11g Release 2 (11.2) Support Status and Alerts [ID 880782.1 ]》	该文档汇集了Oracle数据库11.2版本有关信息。例如各平台和版本的认证信息、各Patchset修复的补丁列表等。
8.	My Oracle Support	《Oracle Database Support Newsletter[ID 230.1]》	这是Oracle标准服务（Premier Support）有关Oracle数据库的定期技术通讯。既有版本和补丁发布的最新消息，更有数据库的最新发展技术研讨和介绍。
9.	Oracle 11g R2联机文档	《Oracle® Database Upgrade Guide》	这是Oracle联机文档中有关数据库升级的专题文档，其中也不乏版本和补丁的描述。
10.	My Oracle Support	《Do Patchset Updates (PSU’s) Change the Oracle Release Version/Fifth Digit? [ID 861152.1]》	PSU是数据库版本的第5位，但Oracle并不直接显示数据库版本的第5位。如何知道什么安装了PSU补丁？请看这篇文档介绍的办法。
11.	My Oracle Support	《Patching & Maintenance Advisor: Database (DB) Oracle Database 11.2.0.x [ID 331.1]》	这是一篇介绍Oracle数据库版本和补丁管理的实施方法论文章。例如如何采取主动预防式策略，如何从评估、计划、测试、实施等全生命周期角度去进行版本和补丁管理。
12.	My Oracle Support	《Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms [ID 161549.1]》	这是一篇全面介绍Windows平台的Oracle数据库、网络和Grid Agent的版本和补丁的官方文档。

 

 

 

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

 

 

本文永久地址：https://www.askmac.cn/?p=16585

 

 

如同我们每年都要进行健康检查，防患于未然一样，数据库系统也一直处于动态变化之中，也应该定期进行健康检查。

数据库健康检查应该包括哪些内容？其宗旨和策略是什么？客户、Oracle自身技术人员对数据库健康检查如何看待？Oracle有哪些健康检查新工具？这些就是本章将要展开的话题。

什么是数据库健康检查？

数据库健康检查 = 常规体检

生活在现代社会尤其生活在大都市快节奏下的人们，越来越关爱自己的身体，不太令人放心的食品安全，更有严重恶化的环境等因素，都对我们的身体带来负面影响。于是，每年的例行常规体检成了很多人生活中的一个重要内容。尽管每年的体检报告内容大同小异，但大家仍然还是不敢掉以轻心，尽可能地防患于未然。

数据库系统也运行在一个不断变化的世界里，硬件方面可能出现各种服务器、存储、网络故障，数据量、访问量也在不断变化，应用更是在不断地推陈出新，以满足业务不断发展的需求。数据库系统的生存环境、运行状况到底如何？是否有问题和隐患？这是广大客户，特别是DBA们非常关注和担忧的事情。

于是与常规体检类似，定期进行数据库健康检查（Database Health Check），成了保障IT系统平稳运行的一个重要任务，也成了Oracle服务的一项常规内容。

“数据库健康检查就是Ctrl+C、Ctrl+V”？

数年前与一位当时在Oracle服务部门从事实施工作的女同事聊天。在谈到数据库健康检查时，她一脸的无奈：“数据库健康检查就是Ctrl+C、Ctrl+V，一天检查好几个系统，烦死了”。的确，Oracle服务部门已经将数据库健康检查服务做得非常规范化、程式化了，通过RDA、AWR等工具抓取相关系统大量信息之后，然后就是在检查报告模版里开始拷贝、粘贴的工作。

由于该工作成了例行工作，客户也由最初的新鲜感到产生一定的审美疲劳了，于是要求Oracle公司一天检查好几套，甚至上十套系统，工程师真成了整天都在Ctrl+C、Ctrl+V了，甚至难免会出现张冠李戴的情况。“我们银行业务系统的检查报告怎么出现了电信计费系统？”“你们工程师能不能更敬业点？”这是我亲耳听到的客户抱怨。

虽然的确有少数工程师不够职业，但也不能完全责备工程师。客户一天要求我们检查10几套库，我们只能拷贝、粘贴了，哪有时间和精力去做更深层次、更个性化的分析工作？更无暇与架构设计、应用开发等更多人员进行沟通，出点错也在所难免。关键是客户自己也将该工作视为例行公事了，一个季度检查一次，客户往往将报告束之高阁，只数报告数量，并不仔细阅读。检查报告本身也被工程师做成了党八股，数据库健康检查工作真成了弃之可惜、食之无味的鸡肋了，呵呵。

 

13.2 多年前一次健康检查

基于Oracle工具的数据库健康检查

针对数据库常规健康检查工作，Oracle公司早就提出了规范化的检查项目，甚至嵌入到了相关产品中。例如，当年在9i OEM里面就有一个菜单项“Health Check”，通过该工具可进行规范化的常规检查，包括数据库配置和运行状况检查，并能生成图文并茂的HTML报告，甚至支持中文。以下就是该报告的目录：

 

目录

例程

数据库和例程信息

数据库选项

SGA 信息

初始化参数

方案

方案对象概要 (非 SYS 和 SYSTEM)

无效对象

过程对象错误

索引尚未分析的已分析表

主关键字被禁用的表
SYSTEM 表空间中的用户对象

安全性

一般用户帐户信息

用户角色

用户表空间限额

存储

控制文件

表空间

数据文件

回退段

重做日志

归档日志

最近的控制文件备份

最近的数据文件备份

备份集

映像副本

可见，检查内容涵盖例程（Instance）、方案（Schema）、安全性、存储等多个方面。例如以下是数据库和例程检查项目的详细信息：

DB 名称	CDCCPC
全局名	CDCCPC
DB 版本	Oracle9i Enterprise Edition Release 9.2.0.2.0 – 64bit Production
主机名	CCPCA
例程名	CDCCPC
例程启动时间	18-六月 -2003
限制模式	NO
归档日志模式	ARCHIVELOG
只读模式	NO

如果通过OEM工具直接生成检查报告，一天真能生成十几个报告。数年前，我就是通过该工具，为某银行客户的数据库系统，辗转好几个城市，进行数据库巡检工作。为配合这次巡检，该银行还专门派了一位技术人员与我同行，一方面让他负责进行一些协调工作，另一方面也让他了解Oracle公司进行健康检查的套路。刚开始，他也是充满好奇和新鲜，连眼睛都不眨地仔细观察我的检查过程，特别是脚本，并详细阅读我写的报告。但到了最后一个城市，我的报告也快变成党八股了，他也觉得索然无味，也不奉陪我了，最后只数数报告数量了。呵呵。

 

毕竟Oracle工具只能针对一个系统的通用状况进行检查，若想发现具体问题，特别是发现与应用相关的问题，需要更多的细心分析，以及与客户、应用开发商和其它厂商的沟通。以下就是这次检查中，我基于Oracle工具产生的HTML标准格式的健康检查报告，在更多方面进行强化之后形成的检查报告文档目录：

文档控制… 2

前言… 4

健康检查和性能分析概述… 5

目的… 5

健康检查内容… 5

性能分析内容… 5

健康检查和性能分析的依据… 5

健康检查和性能总体情况… 6

健康检查分析… 7

数据库例程检查… 7

数据库对象检查… 15

数据库安全性检查… 17

数据库存储的检查… 20

数据库备份的检查… 23

数据库性能分析… 25

数据库主要性能指标… 25

主要等待事件分析… 25

主要应用分析… 26

存在问题及解决途径… 27

数据库备份问题… 27

大量重复SQL语句… 28

对大额交易来帐历史表的全表扫描… 29

无效Package包… 29

Catalog 数据库没有进行逻辑备份… 30

系统参数的调整建议… 30

统计信息未定期收集… 31

物理空间管理效率不高… 31

Agent程序无法启动… 31

数据库性能分析和监测建议… 33

每日的工作… 33

每周的工作… 33

每月的工作… 34

其它的工作… 34

总结… 35

限于篇幅，在本书无法展开检查报告的整个内容，下面将就报告中几个重要问题展开叙述。

发现了重大隐患

就在这次检查工作中，我们通过Oracle产生的报告发现了该系统备份没有成功的问题，更发现了可能导致生产系统宕机和挂起的重大隐患。以下就是详细过程：

• 备份集检查

备份碎片名称	完成时间	备份类型	设备类型	备份集时间戳	备份集计数
c-2151840866-20030713-00	13-七月 –2003	Full Backup	SBT_TAPE	499226687	496
CDCCPC_499226683_ffes361r_1_1	13-七月 -2003	Archivelog Backup	SBT_TAPE	499226683	495
CDCCPC_499226416_fees35pg_1_1	13-七月 -2003	Full Backup	SBT_TAPE	499226416	494

检查结果：7月13日备份集正常。但数据库自7月13日以来，由于磁带库管理软件TSM客户端的口令过期，导致RMAN物理备份没有成功。

• 问题原因和后果

该问题如果不及时解决，会最终导致生产系统停机。事件的因果关系如下：

TSM客户端的口令过期 –>导致RMAN无法备份到磁带库 -> 导致归档日志文件无法清除 -> 导致归档日志满 -> 导致Oracle数据库被挂起 -> 导致CICS宕机。

该系统自7月13日以来，TSM客户端的口令已经过期，目前归档日志文件已达到68%。如果不及时处理，预计再过一周左右，会导致Oracle数据库被挂起和CICS宕机。

• 解决途径

请IBM或集成商提供修改TSM客户端口令和期限的命令。同时，保障磁带库的状态正常。

• 数据库物理备份的检查

方法1：在数据库服务器中，查看/oracle/sql/rman/full_backup.log文件，可检查前一天数据库的物理备份是否正常。

例如，TSM客户端的口令过期的错误信息如下：

ORA-19511：Error received from media manager layer, error text:

ANS1352E(RC52) Session rejected : Password has expired

磁带库没有mount的错误信息如下：

ORA-19511: Error received from media manager layer, error text:

ANS1312E (RC12)   Server media mount not possible

方法2：在RMAN中检查最新备份集的时间，命令如下：

$ rman catalog rman/rman@rcv target /

RMAN> list backup;

请检查数据文件、归档日志和SPFILE的备份集时间。

记得在这次检查工作中，主管领导得知系统可能宕机的重大风险后，被吓得脸色惨白，呵呵。当时他说：“我们这个系统虽然交易量不大，但交易金额一天就几十个亿啊，如果宕机，损失不敢想像。”幸亏我们通过健康检查提前一周发现并排除了该风险，同时提出了改进和防范措施。

其它几个典型问题

• 无效Package包问题

问题描述：系统中有多个无效的Package包。该系统将使用到DBMS_STATS和DBMS_JOB包。

问题原因：经分析，原因是在安装时将9.2.0.1升级到9.2.0.2之后，没有运行utlrp.sql。

解决办法：这些包对目前的生产系统没有影响，但建议还是将上述包重新编译，以防未来使用到这些package时，系统报错。解决办法有两种：

运行utlrp.sql。具体步骤如下：

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus “/as sysdba”

SQL> @utlrp.sql

或直接重新编译上述包。命令如下：

$ sqlplus “/as sysdba”

SQL> alter package <package名称> compile;

SQL> alter package <package名称> compile body;

• Agent程序无法启动问题

问题描述：数据库服务器上的agent程序主要为OEM提供有关操作系统级的性能数据，以及执行OEM的有关作业操作等。目前agent程序无法启动，出现如下错误：

exec():0509-036 Cannot load program agentctl because of the following errors:

0509-150 Dependent module /oracle/app/oracle/product/9.2.0/lib/libvppdc.so could not be loaded.

0509-103 The module has an invalid magic number.

问题原因：Oracle 9i中的agentctl为32位程序，而环境变量没有设置32位的lib库路径。

解决办法：在.profile中将LIBPATH进行如下修改：

LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:/usr/lib

即LIBPATH环境变量增加32位的lib库路径（本文永久地址：https://www.askmac.cn/?p=16585）。

 

13.3最近一次健康检查

Oracle公司的数据库健康检查

• 检查内容

通常而言，Oracle服务部门对一套数据库进行全面检查需要3个工作日，但很多情况下，客户会要求我们在更短的时间检查更多的系统，于是有了标准版本和简化版本之分。以下就是两个版本的检查项目区别：

检查内容	标准版本	简化版本
硬件配置	Yes	No
主机配置	Yes	No
内存参数	Yes	No
信号量	Yes	No
系统配置	Yes	No
操作系统补丁	Yes	No
硬盘可用空间	Yes	No
数据库配置	Yes	No
数据库版本	Yes	No
数据库产品选项	Yes	No
数据库参数	Yes	No
运行日志和跟踪文件	Yes	No
控制文件	Yes	No
Redo log 文件	Yes	No
归档Redo log 文件	Yes	No
数据文件	Yes	No
表空间	Yes	No
回滚段管理	Yes	No
数据库对象	Yes	No
安全性管理	Yes	No
SQL*Net	Yes	No
监听器的设置	Yes	No
TNSNAMES	Yes	No
数据库备份和恢复概况	Yes	No
备份	Yes	No
恢复	Yes	No
操作系统性能	Yes	Yes
网络性能	Yes	No
数据库索引及行链	Yes	No
数据库性能	Yes	Yes
建议	Yes	Yes
主要关注	Yes	Yes
附录 A: ‘init.ora’ parameter  files	Yes	No

• 检查方法
  • ORACLE 工具RDA( REMOTE DIAGNOSTIC AGENT) 进行系统信息收集.
  • 操作系统工具和命令检查操作系统。
  • SQL命令检查数据库配置，SQL命令在ORACLE 工具SQL*PLUS中运行。
  • ORACLE 工具AWR 进行数据库性能资料的收集.
  • ORACLE工具OEM(ORACLE ENTERPRISE MANAGER)/PERFORMANCE MANAGER 进行数据库运行情况的监控及分析

典型的数据库健康检查

最近，针对某银行数据仓库系统，我的一位同事就是基于上述Oracle标准模板，并通过RDA、AWR、SQL*Plus等工具，开展了一次典型的健康检查。由于时间有限，他只对如下内容进行了检查：

• 主机配置
• 操作系统性能
• 数据库配置
• 数据库性能

整个检查报告略，以下仅摘要该报告汇总的问题和建议：

No.	问题描述	参考章节	建议解决时间
1	夜间4：00-9:30CPU和内存资源不足。建议议适当增加CPU和内存资源，或者优化CPU资源使用。	5	立即
2	当前数据库的最后一个版本是10.2.0.5建议升级到最后一个补丁集。	6	近期
3	数据库处于非归档模式，产品数据库通常建议运行于归档模式。	6	立即
4	2:30-4:30有部份语句，消耗系统大量的IO和CPU资源，建议请应用对语句进行优化。而且在该时间点CPU和IO资源都表现不足。相关语句优化详见相关章节	7	立即

我的“数据库健康检查”

针对相同系统，作为服务解决方案顾问，我在一天时间内，也对该系统进行了一次调研工作，旨在分析该系统存在的问题，为客户提出系统改进和优化服务方案。需要说明的是，我是在完全不知晓我的同事刚刚已经进行了一次健康检查工作情况下，开展我的调研工作的。

以下就是我罗列的该系统问题：

• 硬件需要扩容

该系统现有配置为IBM 570 3C/18GB，而且运行了两个数据库事例，而数据容量已经达到3TB。

作为典型的数据仓库系统，该系统的大部份应用都是大批量数据处理，特别是夜间的ETL批处理，资源消耗非常大。因此，我们首先建议，在条件许可的情况下，先进行硬件资源扩容，将极大地满足大批量数据处理的需求，而且为并行处理等技术的运用奠定基础。这也是简单、快捷而有一定成效的方法。

• 参数配置问题

该系统目前分配给最主要实例ridspd的SGA和PGA分别只有4GB和2GB，各项指标表明：内存资源明显不够。因此，建议结合上述硬件扩容建议，对相关参数进行调整。

• 数据库版本和补丁问题

该系统目前数据库版本为10.2.0.4，并且没有安装PSU和其它推荐小补丁，使得Oracle数据库系统本身运行存在一定风险。建议升级到10g R2最后一个版本10.2.0.5，并且安装相应的PSU和其它推荐小补丁。

• SQL应用问题

经分析，该系统最消耗资源语句基本为数据仓库大批量数据处理语句，其中大部分应用设计质量较高，但部分语句存在一定的质量问题。例如若干表缺乏合理的索引策略，导致全表扫描，以及索引效率不高等问题。

• 表空间设计和备份时间问题

该系统运行在非归档模式下，主要数据存储在RIDSDAT、RIDSTMP、RIDSIDX等少数表空间上，导致目前的备份方案主要为全库冷备份方案，达到10余个小时之上。

因此，如何根据应用情况，进行表空间的细粒度设计，合理设计备份方案，也是该系统需要改进的重要方面。

• 统计信息采集问题

由于数据量太大，该系统的自动统计信息采集功能被关闭。但目前缺乏全面、定制化的统计信息采集方案，导致部分SQL语句由于统计信息不准确，使得Oracle优化器没有产生最佳的执行计划。

• 分区状况不合理

根据分析，该系统虽然进行了部分分区，但分区方法比较简单，也没有实施索引分区技术。因此，可以结合SQL应用优化进行全面的分区方案完善和实施工作。

• 空间碎片严重

该系统存在大量DML操作，导致空间碎片问题严重。空间碎片问题不仅导致空间浪费，而且也导致访问效率的低下。

• I/O 负载不均衡

该系统目前采用了普通文件系统技术，底层存储进行了条带化处理。但由于存储不断扩容，而没有进行I/O重新条带化，导致I/O 负载不均衡。如果采用Oracle自动存储管理（ASM）技术，将有效解决这种问题。

 

13.4 关于健康检查的点评

下面结合上述最近一次我和同事的健康检查工作，对健康检查特别是Oracle公司现有的检查内容、方法等进行一番点评和建议。

首先，我和同事都英雄所见略同地发现了一些相似问题，并提出了相同建议。例如，发现系统硬件资源不够，需要扩容；数据库版本需要安装补丁集；应用存在一定问题；数据库运行在非归档模式；等等。

其次，实事求是而言，我的分析内容和角度比我的同事，也就是Oracle公司现有的检查模板内容更丰富和更全面一些，也更有效地分析出了该系统更具体、更深层次的问题。

下面针对数据库健康检查工作进行进一步点评：

• 应增加更多检查和分析内容

现有规范化检查报告应从更多角度和层次，涵盖更多检查和分析内容。例如，数据库分区技术是Oracle最能体现海量数据库处理能力的关键技术，健康检查应包括客户应用系统分区技术的实施分析。再则，采集和及时更新统计信息，是确保Oracle采用CBO优化器，进而在整体上保证应用性能最佳的重要手段，健康检查应包括统计信息采集方案实施情况的分析。另外，数据库碎片是数据库系统不可避免的问题，健康检查应在碎片指标分析、碎片整理技术方案等方面给客户数据库以专题分析和建议。

• 应突出问题，不要罗列数据

Oracle公司提交的报告往往都是一个系统数十页，但大部分内容都是天下太平、一切正常的检查结果。虽然也在报告前面部分进行了问题总结，但一方面这些所谓问题分析其实并不全面、深刻，另一方面客户更是为后面大量罗列的数据弄得昏昏欲睡，更如白开水一般。即便报告里的确藏有真知灼见，但也被这些信息“垃圾”所淹没了。难怪乎客户会将Oracle检查报告束之高阁，视为鸡肋了。

• 深入应用、贴近客户

由于时间有限，沟通不够，Oracle提交的报告经常对应用分析缺乏深度，甚至仅仅是罗列若干Top-SQL语句，然后扔下一句：“上述SQL语句资源消耗非常高，请应用开发团队进行深入分析，并给出优化建议。”

这与某庸医在诊断书上大笔一挥：“该病人高烧不退，原因不明，请病人加强自我调养”，有何差异？

客户要求的是你不仅给我找出这些最消耗资源语句，更应该通过与开发人员沟通，共同给出具体解决建议，例如增加索引、调整语句编写方式、收集统计信息、使用SQL Profile、增加或调整分区方案等等，甚至进行优化前后的对比测试，以及最终实施建议。

• 高度、态度和角度

这么多年来，数据库健康检查工作已经成为客户不满意、工程师厌倦，人见人烦的鸡肋。如何走出这种困境和怪圈，其实需要客户和Oracle公司共同努力，特别是Oracle公司应主动、积极地加以改进，并求得客户的理解和支持，更应在高度、态度和角度方面去下功夫。

首先，Oracle数据库健康检查不应局限于硬件、操作系统、数据库配置这么低层次的东西，而应在整个系统架构、业务和应用分析的高度去把握和展开分析工作。

其次，Oracle技术人员的确应有更积极、主动的态度。健康检查应该有模板，但客户系统千变万化，就象一千人读《红楼梦》有一千种解读一样，我们应不拘泥于模板，而是真正以客户系统为中心，以高度负责任的态度，特别是主动加强与客户架构设计、应用开发、测试、运行维护管理人员的沟通，全面细致地开展这项工作。

第三，在具体实施工作中，应从更多角度，运用更多方法和工具，以科学严谨、求真务实的精神去开展工作。真正深入进去一件事情了，你就一定会喜欢上它。请允许我用如下警句与同事们共勉：

做我所爱，爱我所做。

 

13.5 11g健康检查新特性：Health Monitor

采用新的技术去不断丰富健康检查内容和检查手段，是提高健康检查工作质量的重要方面。下面将介绍11g健康检查新特性：Health Monitor的基本原理、使用方式、报告内容等。

Health Monitor概述

Health Monitor是 Oracle 11g之后推出的进行数据库健康检查的新工具。该工具可检查数据文件坏块（包括逻辑和物理坏块）、UNDO和REDO文件坏块、数据字典坏块等。

该工具可运行在被动（Reactive）和手工（Manual）两种模式，前者是系统在发生重大故障时自动进行检查的模式。后者则是DBA通过DBMS_HM包或 OEM图形界面两种方式手工运行该工具，并产生报告的工作模式。

该工具可在数据库打开或NOMOUNT状态下运行，前者称之为DB-online方式，后者称之为DB-offline方式。

Health Monitor检查内容

Health Monitor可检查如下方面内容：

• DB Structure Integrity Check（数据库结构完整性检查）

该检查将对数据库文件完整性进行检查，并在报告中描述可能存在的不可访问文件、坏块和不一致性。在DB-online方式下，Oracle检查控制文件中罗列的所有数据文件和日志文件。在DB-offline方式下，Oracle只检查控制文件。

• Data Block Integrity Check（数据块完整性检查）

该检查将在数据块级检查坏块问题，例如checksum问题、head/tail不匹配问题、逻辑不一致性问题等。坏块信息将被记录在V$DATABASE_BLOCK_CORRUPTION中。该检查不包括数据块之间和段之间的问题。

• Redo Integrity Check（日志完整性检查）

该检查将对联机日志和归档日志文件进行完整性检查，包括能否访问、是否有坏块等，并在报告中汇总这些问题。

• Undo Segment Integrity Check（回退段完整性检查）

该检查将对UNDO表空间进行回退段完整性检查，例如可能存在的UNDO逻辑坏块。一旦发现UNDO坏块，Oracle将通过PMON和SMON进程去恢复被损坏的事务。如果恢复失败，Oracle将UNDO坏块信息保存在v$corrupt_xid_list视图之中。通过强制commit操作，大多数UNDO坏块可以得到恢复。

• Transaction Integrity Check（事务完整性检查）

该检查与回退段完整性检查相似，区别在于事务完整性检查只对指定的事务进行检查。

• Dictionary Integrity Check（数据字典完整性检查）

该检查将对核心数据字典进行完整性检查。例如：tab$, clu$, fet$, uet$, seg$, undo$, ts$, file$, obj$, ind$, icol$, col$, user$, con$, cdef$, ccol$, bootstrap$, objauth$, ugroup$, tsq$, syn$, view$, typed_view$, superobj$, seq$, lob$, coltype$, subcoltype$, ntab$, refcon$, opqtype$, dependency$, access$, viewcon$, icoldep$, dual$, sysauth$, objpriv$, defrole$, and ecol$.

Health Monitor的使用

Oracle可通过DBMS_HM包或 OEM图形界面两种方式手工运行Health Monitor工具，限于篇幅，本书只介绍DBMS_HM包使用方式：

• 使用举例

通过DBMS_HM.RUN_CHECK，可进行指定项目的检查，例如：

exec DBMS_HM.RUN_CHECK(‘DB Structure Integrity Check’, ‘check1’);

上述语句对数据库结构完整性（DB Structure Integrity）进行检查，检查结果保存在名称为check1的报告中。

• 查询检查项

执行如下语句，可查询所有可检查的项目，例如：

SQL> SELECT name FROM v$hm_check WHERE internal_check=’N’;

 

NAME

—————————————————————

DB Structure Integrity Check

CF Block Integrity Check

Data Block Integrity Check

Redo Integrity Check

Transaction Integrity Check

Undo Segment Integrity Check

Dictionary Integrity Check

ASM Allocation Check

• 查询输入参数

有些检查项需要输入参数，以下语句可了解输入参数含义：

SELECT c.name check_name, p.name parameter_name, p.type,p.default_value, p.description

FROM v$hm_check_param p, v$hm_check c

WHERE p.check_id = c.id and c.internal_check = ‘N’

ORDER BY c.name;

查询结果如下：

	CHECK_NAME	PARAMETER_NAME	TYPE	DEFAULT_VALUE	DESCRIPTION
1	ASM Allocation Check	ASM_DISK_GRP_NAME	DBKH_PARAM_TEXT		ASM 组名
2	CF Block Integrity Check	CF_BL_NUM	DBKH_PARAM_UB4		控制文件块号
3	Data Block Integrity Check	BLC_DF_NUM	DBKH_PARAM_UB4		文件号
4	Data Block Integrity Check	BLC_BL_NUM	DBKH_PARAM_UB4		块号
5	Dictionary Integrity Check	CHECK_MASK	DBKH_PARAM_TEXT	ALL	检查掩码
6	Dictionary Integrity Check	TABLE_NAME	DBKH_PARAM_TEXT	ALL_CORE_TABLES	表名
7	Redo Integrity Check	SCN_TEXT	DBKH_PARAM_TEXT	0	最新良好重做的 SCN (如果已知)
8	Transaction Integrity Check	TXN_ID	DBKH_PARAM_TEXT		事务处理 ID
9	Undo Segment Integrity Check	USN_NUMBER	DBKH_PARAM_TEXT		还原段号

Health Monitor报告的生成

Oracle可通过DBMS_HM包、OEM和ADRCI工具三种方式生成Health Monitor报告，并可支持纯文本、HTML、　XML等格式。以下仅介绍通过DBMS_HM包生成纯文本格式的Health Monitor报告（本文永久地址：https://www.askmac.cn/?p=16585）。

通过以下命令可生成纯文本格式的Health Monitor报告：

SQL> SET LONG 100000

SQL> SET LONGCHUNKSIZE 1000

SQL> SET PAGESIZE 1000

SQL> SET LINESIZE 512

SQL> SELECT DBMS_HM.GET_RUN_REPORT(‘CHECK1’) FROM DUAL;

 

DBMS_HM.GET_RUN_REPORT(‘CHECK1’)

—————————————————————

Basic Run Information

Run Name                     : check1

Run Id                       : 61

Check Name                   : Dictionary Integrity Check

Mode                         : MANUAL

Status                       : COMPLETED

Start Time                   : 2012-11-29 17:00:58.551000 +08:00

End Time                     : 2012-11-29 17:01:00.908000 +08: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

Health Monitor相关视图

除了Health Monitor生成的检查报告之外，通过查询如下一些Health Monitor视图，也能得到更多检查信息。

如下查询可了解曾经进行的各种Health Monitor检查历史情况：

SELECT run_id, name, check_name, run_mode, src_incident FROM v$hm_run;

查询结果如下：

	RUN_ID	NAME	CHECK_NAME	RUN_MODE	SRC_INCIDENT
1	21	my_run	Dictionary Integrity Check	MANUAL	0
2	61	check1	Dictionary Integrity Check	MANUAL	0
3	81	check2	DB Structure Integrity Check	MANUAL	0
4	101	check3	Data Block Integrity Check	MANUAL	0
5	141	check4	Redo Integrity Check	MANUAL	0
6	161	check5	Transaction Integrity Check	MANUAL	0
7	1	HM_RUN_1	DB Structure Integrity Check	REACTIVE	0

如下查询可了解某次检查中发现的问题：

SELECT type, description FROM v$hm_finding WHERE run_id = 1;

查询结果如下：

	TYPE	DESCRIPTION
1	FAILURE	控制文件需要介质恢复
2	FAILURE	系统数据文件 1: ‘C:\11.2.0.1\ORADATA\LYJ\SYSTEM01.DBF’ 需要介质恢复
3	FAILURE	一个或多个非系统数据文件需要介质恢复
4	FAILURE	数据文件 2: ‘C:\11.2.0.1\ORADATA\LYJ\SYSAUX01.DBF’ 需要介质恢复
5	FAILURE	数据文件 3: ‘C:\11.2.0.1\ORADATA\LYJ\UNDOTBS01.DBF’ 需要介质恢复

 

13.6本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述
1.	My Oracle Support	《How to Perform a Health Check on the Database [ID 122669.1]》	如何进行数据库健康检查？检查的方法、内容？这篇文档给出了一个官方建议。
2.	My Oracle Support	《RACcheck – RAC Configuration Audit Tool (Doc ID 1268927.1)》	一个被新工具替换的工具。新工具叫做：ORAchk
3.	My Oracle Support	《”hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g (Doc ID 136697.1)》	一个主动检查数据字典问题的Oracle官方详细脚本。
4.	My Oracle Support	《Identify Data Dictionary Inconsistency (Doc ID 456468.1)》	如何检查Oracle数据库数据字典的一致性？请看这篇文章吧。
5.	My Oracle Support	《Script to Install the “hOut” Helper Package (“hout.sql”) (Doc ID 101468.1)》	又一个用于健康检查的Oracle官方详细脚本。
6.	Oracle联机文档	《Oracle Administrator’s Guide》第九章之“Running Health Checks with Health Monitor”小节	该小节详细介绍了11g新特性：Health Monitor。
7.	My Oracle Support	《11g New Feature: Health monitor (Doc ID 466920.1)》	Metalink中介绍11g新特性：Health Monitor的官方文档。

 

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

本文永久地址：https://www.askmac.cn/archives/luomin-fix-oracle-error.html

 

虽然Oracle数据库的故障千奇百怪，甚至让客户有种防不胜防的感觉，但还是有很多故障是比较常见的，这些问题也是我们Oracle服务部门在客户现场经常遇见，也经常处理的问题。

事实上，针对这些常见问题，Oracle公司不仅提供了诊断和解决问题的思路和方式，甚至针对具体问题，提供了专门的官方处理文档。如果我们能在平日的运行维护工作中，提前预读这些文档，甚至自己编写相应的故障处理手册，一旦这些常见故障真正发生时，我们就不会那么手足无措，即便不一定完全胸有成竹，也至少可以做到一定的心中有数了，就像打仗一定要有作战预案、一定要打有准备之战一样。

本章就将介绍这些常见故障的诊断和处理过程。例如ORA-00600、内存不够、数据库空间不够、snapshot too old、UNDO表空间无法扩展等。

 

ORA-00600：内部错误

什么是ORA-00600错误?

ORA-00600是常见的一类错误，以下是Oracle 11g中关于该错误的官方描述：

 

 

ORA-00600: internal error code, arguments: [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string], [string] 
Cause: This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. The first argument is the internal message number. This argument and the database version number are critical in identifying the root cause and the potential impact to your system.
Action: Visit My Oracle Support to access the ORA-00600 Lookup tool (reference Note 600.1) for more information regarding the specific ORA-00600 error encountered. An Incident has been created for this error in the Automatic Diagnostic Repository (ADR). When logging a service request, use the Incident Packaging Service (IPS) from the Support Workbench or the ADR Command Interpreter (ADRCI) to automatically package the relevant trace information (reference My Oracle Support Note 411.1). The following information should also be gathered to help determine the root cause: 
- changes leading up to the error
- events or unusual circumstances leading up to the error
- operations attempted prior to the error
- conditions of the operating system and databases at the time of the error Note: The cause of this message may manifest itself as different errors at different times. Be aware of the history of errors that occurred before this internal error.

即ORA-00600是Oracle软件当遇到内部不一致或其它异常情况时发出的内部错误，通常而言该错误与Oracle Bug相关，但也不尽然，也可能与操作系统、资源缺乏、硬件故障等相关。

ORA-00600错误信息通常包括一组以中括号形式的参数信息，例如：

 

 

 

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

 

这些参数是一组内部数字或字符串，其中第一个参数非常重要，表示错误信息是在哪个代码中发生的，这是诊断ORA-00600错误的关键信息。其它参数则提供了进一步的诊断信息，例如内部变量值等。另外，数据库版本信息对问题诊断也非常关键。

 

ORA-00600错误的诊断方法

ORA-00600错误实际包含的内容非常广泛，但诊断方法却有一定规律。

• 检查log和trace文件

ORA-OO600错误发生时，Oracle通常会在alert.log文件中进行记录。因此，ORA-OO600错误诊断的第一个动作就是去查看alert.log文件。同时，alert.log文件中会显示与该错误相关的更详细的trace文件信息。

如果是11g之前版本，假设错误是因为用户进程所导致，trace文件将位于USER_DUMP_DEST目录。假设错误是因为后台进程（如PMON、SMON）所导致，trace文件将位于BACKGROUND_DUMP_DEST目录。如果是11g之后版本，trace文件将位于DIAGNOSTIC_DEST参数所定义的目录。

假设trace文件结束处出现“MAX DUMP FILE SIZE EXCEEDED”，则说明因为MAX_DUMP_FILE_SIZE参数设置过小或者没有设置成“unlimited”，导致trace文件被截断了。这样可能导致非常重要的诊断信息没有记录下来，因此应调整MAX_DUMP_FILE_SIZE参数，确保诊断信息的完整。

• 访问600/7445/700诊断工具

为提高ORA-00600/ORA-7445/ORA-00700等内部错误的诊断效率，Oracle公司在metalink中提供了这类错误的诊断工具，可通过ID 153788.1访问该工具，以下就是其界面：

 

即输入ORA-00600的第一个参数以及数据库版本号，再点击“Loop-up Error”，就可有效检索出与该类错误相关的诊断信息。如果未检索出有效信息，则可以点击“Do a general Search for Knowledge”进行更广泛的搜索。

同时，可在上述页面的“Search by Stack Trace”区域中输入Trace文件中Call Stack相关信息进行更准确的检索。具体办法是将Trace文件中以“—– Call Stack Trace —–”开头到“Argument/Register Dump”结束的内容，或者将“—– Call Stack Trace —–”开头的前15-20行内容复制粘贴到“Search by Stack Trace”区域中，然后点击“Search Call　Stack Trace”按钮进行检索。首发于askmac.cn

 

• 求助Oracle Support

ORA-00600毕竟是Oracle内部错误信息，而且很可能是Oracle Bug。因此，如果可能，尽量还是通过创建SR求助Oracle Support，得到官方的权威分析和解决方式建议。为此，建议在SR至少提供如下信息给Oracle Support：

• log日志文件
• 相关的trace文件或者11g的incident package。
• 版本和补丁信息
• 发生此次ORA-00600错误之间的任何配置或应用变更信息
• RDA报告或者OCM信息。

ORA-00600错误诊断举例

假设我们遇见了“ORA-00600 [729]　［space leak］”错误，我们在600/7445/700诊断工具中输入第一个参数“729”，以及数据库版本号，我们将快速搜索出相应的文章：ORA-600 [729] “UGA Space Leak” [ID 31056.1]。该文章对该错误的描述如下：

• 问题原因和影响

该错误表示UGA内存泄露了，该错误是由于Oracle内部内存管理程序导致，但不会导致数据损坏，也只是在logoff操作时才发生。总之，影响较小，内存泄露也比较少，基本可以忽略。

• 解决方式

通过设置如下参数：

event = “10262 trace name context forever, level 4000”

并重新启动数据库。该参数将使得低于4000字节的内存泄露信息被忽略掉，不再写入alert.log文件。建议不要将level设置为1，这样将关闭内存泄露检查功能。

 

ORA-04030：PGA内存不够

什么是ORA-04030错误?

ORA-04030错误表示Oracle后台服务进程（Server Process）从操作系统分配不到内存了，这部分内存主要是PGA内存。在专用连接模式（Dedicated）下，Oracle PGA内存包括如下部件：

 

 

其中SQL Area包括Sort Area、Hash join Area、Bitmap merge Area、Bitmap Create Area等。

而在多线程连接模式（Multithread）或共享服务器连接模式（Shared Server）下，UGA将从SGA中进行分配，因此UGA不会导致ORA-04030错误。

ORA-04030错误的诊断

ORA-04030错误原因是多方面的，例如：应用本身内存申请过多、操作系统内存耗尽、操作系统内存分配限制。以下以UNIX操作系统为环境进行深入探讨：

• 是否有空闲内存？

通过操作系统提供的工具：top、vmstat等，可以监控当前内存使用情况，例如，以下是在Linux平台top工具的显示结果：

 

 

top - 10:17:09 up  1:27,  4 users,  load average: 0.07, 0.12, 0.05
Tasks: 110 total,   4 running, 105 sleeping,   0 stopped,   1 zombie
Cpu(s):     0.3% user,   1.6% system,      0.0% nice,                98.0% idle
Mem:  1033012k total,      452520k used,    580492k free,       59440k buffers
Swap:  1052248k total,       0k used,  1052248k free,   169192k cached
               .....

可见该系统还有580492k空闲内存。

• 操作系统内存分配有限制？

如果有空闲内存，则可能是操作系统内存分配有限制了。通过操作系统的limit或ulimit命令，将显示操作系统内存分配限制。以下是Linux平台的执行情况：

 

aroelant@aroelant-be:~> ulimit -a
core file size      (blocks, -c)    0
data seg size       (kbytes, -d)    unlimited
file size        (blocks, -f)    unlimited
max locked memory    (kbytes, -l)    unlimited
max memory size      (kbytes, -m)    unlimited
open files             (-n)    1024
pipe size       (512 bytes, -p)    8
stack size        (kbytes, -s)    unlimited
cpu time        (seconds, -t)    unlimited
max user processes         (-u)    7168
virtual memory      (kbytes, -v)    unlimited

请注意，有些操作系统的unlimited设置并非是无限制的，而实际上是由于历史原因而导致的2G限制。因此，如果内存不够，最好按需要的实际上限进行设置。

• Oracle内存分配有限制？

Oracle从9i开始提供了PGA内存自动管理功能，即通过设置PGA_AGGREGATE_TARGET参数，为一个实例提供了所有可使用的PGA内存。以下就是查询所有会话所使用的PGA内存情况：

 

select sum(value)/1024/1024 Mb
 from v$sesstat s, v$statname n
 where n.STATISTIC# = s.STATISTIC# and
 name = 'session pga memory';

另外，Oracle有一个内部参数_PGA_MAX_SIZE，该参数缺省值为200MB，表示每个进程所能分配的最大PGA内存。

• 哪些进程消耗了大量PGA内存？

许多大型PL/SQL程序、大数据量排序操作等，的确需要消耗大量PGA内存，如何找到这些操作，请执行如下语句：

 

col name format a30
select sid, name, value
  from v$statname n, v$sesstat s
 where n.STATISTIC# = s.STATISTIC#
   and name like 'session%memory%'
 order by 3 asc;

上述语句根据PGA消耗大小，由大到小排序。进一步，根据上述语句返回的sid号，查询到正在执行的相应SQL语句：

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

通过如下设置，可生成相关dump信息，供Oracle Support人员分析内存超量分配问题：

SQL> oradebug setorapid 10 (this is for the oracle pid, use setospid for the os process id)
SQL> oradebug unlimit
SQL> oradebug dump heapdump 5
SQL> oradebug tracefile_name (shows the path and filename information)
SQL> oradebug close_trace

若问题是偶发的，也可通过设置如下event来捕获相关信息：

-- 会话级
SQL> alter session set events '4030 trace name heapdump level 5';
-- 实例级
SQL> ALTER SYSTEM SET EVENT='4030 trace name heapdump level 5' scope=spfile;

在9.2.0.5之后，可以将level设置为536870917，将收集到更多dump信息。

如何解决或避免ORA-04030错误

• 首先，还是老生常谈的应用优化。例如能否降低语句的排序数据量，是否可以避免排序等。
• 适当扩大PGA区域。
• 合理设置操作系统和Oracle对内存使用限制。
• 确保有足够的物理内存。

 

ORA-04031：shared pool内存不够

什么是ORA-04031错误?

当Oracle不能从共享内存区（shared pool）分配一大块连续内存时，Oracle首先尝试刷新（Flush）当前已经不使用的共享内存，并将这些内存进行合并。如果依然不能分配到需要的内存时，则会报ORA-04031错误。以下就是该错误的官方描述：

ORA-04031: unable to allocate string bytes of shared memory (“string”,”string”,”string”,”string”)

Cause: More shared memory is needed than was allocated in the shared pool or Streams pool.

Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.

可见，ORA-4031错误不仅涉及shared pool，也包括large pool、java pool、streams pool等共享内存不够的问题。该类错误是一类比较常见的错误，本人在metalink搜索与之相关的文章就达到400多篇。其中与shared pool相关的错误原因诊断更复杂一些，因此本书重点介绍与shared pool相关的分析诊断和解决方法。

与shared pool相关的参数

欲深入了解ORA-4031在shared pool方面的错误原因，需要先了解与shared pool相关的如下参数：

• SHARED_POOL_SIZE

该参数指定了shared pool缓存区的大小，单位为Byte，或者为K、M、G等。

• SHARED_POOL_RESERVED_SIZE

通过该参数在shared pool中可指定一片预留空间，从而满足一些大的连续空间需求。该参数与下述的SHARED_POOL_RESERVED_MIN_ALLOC参数的综合使用，可以有效避免shared pool缓存区碎片问题的发生。

通常该参数应足够大到避免Oracle进行刷新（Flush）操作，但为合理使用内存资源，Oracle一般建议SHARED_POOL_RESERVED_SIZE参数设置为SHARED_POOL_SIZE的10%。

• SHARED_POOL_RESERVED_MIN_ALLOC

首先需要说明的是该参数在8i之后即变为内部隐含参数了，也就是变为_SHARED_POOL_RESERVED_MIN_ALLOC了。即该参数通常不需要客户来定义，但为诊断ORA-4031错误，我们还是不妨介绍该参数的含义。

该参数表示每次从预留内存中分配的最小内存。若扩大该值，意味着Oracle从预留内存可分配的次数将减少，也意味着Oracle需要更多的预留内存。虽然为隐含参数，但通过如下语句能查询到该参数的当前值：

SELECT nam.ksppinm NAME,val.ksppstvl VALUE

FROM x$ksppi nam,x$ksppsv val

WHERE nam.indx = val.indx

AND nam.ksppinm LIKE ‘%shared%’

ORDER BY 1;

10g/11g的自动内存管理与ORA-4031错误

Oracle 10g和11g分别推出了自动内存共享管理（Automatic Shared Memory Management，ASMM）和自动内存管理（Automatic Memory Management，AMM）功能。在这种自动管理模式下，Oracle可对shared pool, buffer cache,  java pool 和 large pool进行自动管理，甚至在11g中可在SGA和PGA之间也进行自动管理。当需要从shared pool中分配内存而不够时，Oracle可自动从SGA其它区域甚至 PGA（11g）中分配内存。因此，在这种模式下，发生ORA-4031错误的可能性大大降低。

为使用ASMM特性，可设置SGA_TARGET参数。为使用AMM特性，可设置 MEMORY_TARGET参数。通过V$SGA_DYNAMIC_COMPONENTS、 V$SGA_RESIZE_OPS、V$MEMORY_DYNAMIC_COMPONENTS、V$MEMORY_RESIZE_OPS等视图，可了解当前的内存参数配置和自动调整情况。

尽管建议采用自动内存管理特性，但Oracle还是建议继续设置SHARED_POOL_SIZE、SHARED_POOL_RESERVED_SIZE等参数，从而确保shared pool的最小空间，避免过于频繁地自动调整内存，从而导致系统负载的增加。

ORA-04031错误的诊断

通常而言，ORA-04031错误的原因主要分为shared pool空间不够和shared pool空间碎片问题严重两类。以下就对这两类问题进行深入诊断：

• shared pool空间不够

首先通过查询V$SHARED_POOL_RESERVED视图，如果满足如下条件：

REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE  > SHARED_POOL_RESERVED_MIN_ALLOC

上述条件中REQUEST_FAILURES > 0，表示Oracle在预留的shared pool中申请不到空间了。而LAST_FAILURE_SIZE  > SHARED_POOL_RESERVED_MIN_ALLOC，则表示最后一次申请失败的空间大于可分配的最小内存。此时说明shared pool空间可能的确不够了。

Oracle官方的建议是首先考虑降低SHARED_POOL_RESERVED_MIN_ALLOC参数值，这样可以在预留的shared pool空间中保存更多的对象。其次，再考虑扩大SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE值。

但本人建议直接尝试扩大SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE值，因为SHARED_POOL_RESERVED_MIN_ALLOC参数毕竟已经是隐含参数，一般不需要客户去设置了。

• shared pool碎片问题

如果不是上述shared pool空间不够问题，并且满足如下条件：

REQUEST_FAILURES > 0 and LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

最后一次申请失败的空间小于可分配的最小内存，也就是说不是空间不够。此时则是shared pool碎片问题了，导致该问题的原因通常是应用本身所导致，例如没有合理使用绑定变量等。下面将就这类问题的诊断和解决进行更深入的探讨。

需要说明的是，上述诊断方式依然适合于10g/11g之后自动内存管理功能。

ORA-04031错误的解决

• shared pool空间不够问题的解决

通过如下查询：

SELECT SUM(pins) “EXECUTIONS”,

SUM(reloads) “CACHE MISSES WHILE EXECUTING”,

SUM(reloads)/SUM(pins)*100 “Hit Ratio”

FROM v$librarycache;

该查询是计算shared pool命中率的公式。如果Hit Ratio > 1%，则需要考虑扩大SHARED_POOL_SIZE参数。

• shared pool碎片问题的解决

如前所述，如果shared pool空间足够，但依然发生ORA-4031错误，则通常是以下原因导致的shared pool碎片问题：

• 没有合理使用绑定变量
• SQL语句没有共享
• 太多不必要的 parse操作，包括soft parse
• SESSION_CACHED_CURSORS参数设置太高

为确定上述具体应用问题，可执行如下查询：

SELECT SUBSTR(sql_text,1,40) “SQL”, COUNT(*), SUM(executions) “TotExecs”

FROM v$sqlarea

WHERE executions < 5

GROUP BY SUBSTR(sql_text,1,40)

HAVING count(*) > 30

ORDER BY 2;

上述语句可显示使用常量，适合于绑定变量改造的SQL语句。

ORA-04031错误和Large Pool

Large Pool主要用于多线程连接（Multi-threaded）模式、XA协议分布式处理、RMAN备份恢复、并行处理等应用场景。Large Pool与其它内存区域管理不同之处是没有采用LRU算法，也就是说Large Pool中的内容不会被Oracle淘汰出去，只会在会话级被分配和释放。如果Large Pool空间不够，则会显示如下错误：

ORA-04031: unable to allocate XXXX bytes of shared memory (“large pool”,”unknown object”,”session heap”,”frame”)

通过如下查询，可显示large pool使用情况：

SELECT pool, name, bytes

FROM v$sgastat

WHERE pool = ‘large pool’;

解决与large pool相关的ORA-04031错误的通常做法就是扩大Large_Pool_Size参数。

ORA-04031错误和Shared Pool Flushing操作

在上述解决与shared pool相关的ORA-04031错误中，最有效的办法还是应用改造，特别是合理使用绑定变量。另外，可以通过设置CURSOR_SHARING为Similar/Force来强制使用系统绑定变量，但该解决办法将导致所有使用常量的语句都变为系统绑定变量，可能导致执行计划出现异常。因此，该办法也需谨慎使用，特别是需要经过测试验证其有效性和稳定性。

如果上述解决办法无法合理实施，但shared pool碎片问题的确比较严重，则可以考虑刷新shared pool的操作，即：

alter system flush shared_pool;

但该操作需考虑如下因素：

• Shared pool刷新操作将导致除正在使用的Cursor之外的所有Cursor被淘汰出Library Cache。这样大部分SQL和PL/SQL将不得不重新进行硬解析（Hard Parse），从而导致系统CPU利用率的提高，以及内存Latch操作负载的增加。
• 如果Shared pool碎片问题的确是应用没有合理使用绑定变量所导致，刷新操作也只是短时间缓解一下问题。随着运行时间的增长，Shared pool碎片问题又会依然如故。
• 当shared pool特别大时，刷新操作可能导致系统出现短暂的挂起现象。因此，最好在非业务高峰时候进行该操作。

ORA-04031错误的高级诊断方法

如果上述诊断和分析还不能有效解决ORA-04031错误，则建议通过Metalink求助于Oracle Support专家了。通常，老外会让你进行如下设置，以便问题重现时，收集更多诊断信息，以便于问题分析和定位。

设置如下初始化参数：

event = “4031 trace name errorstack level 3”

event = “4031 trace name HEAPDUMP level 2”

设置这些参数需要数据库重启。或者在会话级进行如下设置：

SQL> ALTER SESSION SET EVENTS ‘4031 trace name errorstack level 3’;

SQL> ALTER SESSION SET EVENTS ‘4031 trace name heapdump level 536870914’;

当错误重现时，将在USER_DUMP_DEST目录或者11g的ADR中产生相应的trace文件，赶紧将这些文件上传到相应的SR中，让老外去分析和解决。

一个案例

很久以前的2004年，本人曾在一个项目上进行了一次比较专题的ORA-04031问题的处理，现摘取其中主要内容如下：

• 问题现象

XX生产数据库系统在2004年1月30日频繁出现ORA-04031错误，表现为shared pool内存不够。现有shared_pool有关的参数设置如下：

shared_pool_size             300000000

shared_pool_reserved_size    25000000

在试图扩大shared_pool_size参数（800M、500M、350M…）时，出现如下错误：

ORA-27123：unable to attach to shared memory segment

• ORA-27123的处理建议

这是由于在 32位的Oracle环境中，当SGA区大于256M时，需要重新定位（relocate）SGA区。否则会出现ORA-27123错误。考虑32位Oracle早已是被淘汰技术，而且限于篇幅，本书不介绍此问题的解决了。

• 对大对象的处理建议

其实，该系统当年在扩大shared_pool_size参数之后，仍然出现ORA-04031错误。这是就是shared pool的碎片问题了。为缓解碎片问题，特别是无法为大对象分配shared pool的问题，我们建议将大对象进行常驻的方法。以下就是相关脚本：

set pagesize 0;

set feedback off;

spool xhs_keep.sql;

 

select * from (

select ‘exec dbms_shared_pool.keep(‘||chr(39)||owner||’.’||name||chr(39)||’);’

from v$db_object_cache

where type in(‘PACKAGE’,’PACKAGE BODY’,’FUNCTION’,’PROCEDURE’)

order by sharable_mem desc)

where rownum <=200;

 

spool off;

• 避免使用匿名package

在2004年1月30日出现ORA-04031错误的日志中，出现匿名package无法申请到shared pool：

ORA-04031: unable to allocate 4200 bytes of shared memory (“shared pool”,”unknown object”,”sga heap”,”library cache”)

建议尽量减少使用匿名package，并转换为存储过程，从而有利于管理和维护。

• 建议对shared pool定期flush

如果频繁出现ORA-04031错误，建议DBA定期对shared pool进行flush操作：

SQL> alter system flush shared_pool;

此操作能避免ORA-04031错误，但应用系统性能会有短时间的影响。

• 增加对ORA-4031错误的跟踪

如果上述建议仍然不能解决ORA-4031错误。建议在init.ora文件中增加如下设置，以便在错误重现时能产生更多的trace文件，供Oracle Support进行深入分析。

event = “4031 trace name errorstack level 3”

event = “4031 trace name HEAPDUMP level 2”

 

11.4空间不够的问题

随着数据量的不断增长，数据库经常会出现空间不够的问题。导致空间不够的原因很多，解决办法也不是一味地增加空间。以下就是根据Oracle知识库的相关文章，对空间不够问题进行的分析和处理。

错误信息

针对不同数据对象的空间不够问题，Oracle提供了不同的错误信息：

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s

ORA-1651: unable to extend save undo segment by %s in tablespace %s

ORA-1652: unable to extend temp segment by %s in tablespace %s

ORA-1653: unable to extend table %s.%s by %s in tablespace %s

ORA-1654: unable to extend index %s.%s by %s in tablespace %s

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s

ORA-1658: unable to create INITIAL extent for segment in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond %s in tablespace %s

ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s

ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s

… …

Oracle错误信息手册中针对上述大部分问题，基本都给出了增加数据文件的如下建议：

Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

但实际上，导致空间不够的原因很多，应深入分析，并给出相应的解决方案。

诊断步骤

空间不够“unable to extend”的根本原因是无法为数据段（Segment）分配连续的空间。因此，应按如下步骤进行诊断：

1. 确定最大的可用连续空闲空间

首先对报错误的表空间，执行如下语句：

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = ‘<tablespace name>’;

如果针对TEMP临时表空间，则执行如下语句：

select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header ;

2. 确定NEXT_EXTENT值

如果是基于数据字典管理表空间并且PCT_INCREASE = 0，或者是基于本地化管理并且采用UNIFORM管理的表空间，则执行如下语句：

SELECT NEXT_EXTENT, PCT_INCREASE

FROM DBA_SEGMENTS

WHERE SEGMENT_NAME = <segment name>

AND SEGMENT_TYPE = <segment type>

AND OWNER = <owner>

AND TABLESPACE_NAME = <tablespace name>;

其中：

• <segment type>表示数据段类型。即出错信息中表示的：CLUSTER、INDEX、INDEX PARTITION、LOB PARTITION、LOBINDEX、LOBSEGMENT、NESTED TABLE、 ROLLBACK、TABLE、TABLE PARTITION、TYPE2 UNDO、TYPE2 UNDO (ORA-1651)等。
• <segment name>包含在出错信息中。

如果是基于本地化管理并且采用SYSTEM/AUTOALLOCATE管理的表空间，则没有合适的语句来确定NEXT_EXTENT值。但可以根据出错信息中显示的无法分配多少数据块数，再乘以该表空间的数据块大小，来确定NEXT_EXTENT值。

如果是基于数据字典管理表空间并且PCT_INCREASE > 0，则按照如下公式计算：

extent size = next_extent * (1 + (pct_increase/100)

例如，假设next_extent = 512000，pct_increase = 50 ，则：

next extent size = 512000 * (1 + (50/100)) = 512000 * 1.5 = 768000

针对“ORA-01650 Rollback Segment”错误，Oracle新版本回退段的PCT_INCREASE永远为0。针对“ORA-01652 Temporary Segment”，临时段的PCT_INCREASE为TEMP表空间相应的缺省存储参数。当然，诊断临时表空间不足的更有效办法，是找到相应的排序SQL语句，并对这些语句进行尽可能的优化。

3. 确定表空间是否是AUTOEXTENSIBLE，并且已经达到上限

针对数据文件，执行如下语句：

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_data_files WHERE tablespace_name='<tablespace name> ‘;

针对临时文件，执行如下语句：

SELECT file_name, bytes, autoextensible, maxbytes FROM dba_temp_files WHERE tablespace_name='<tablespace name> ‘;

解决步骤

在完成上述诊断分析之后，可按如下步骤进行解决：

1. 如果NEXT EXTENT大于最大的可用连续空闲空间，则应先手工进行表空间的相邻空间空间的压缩。如果空间依然不够，考虑后续步骤。
2. 增加数据文件或临时文件。
3. 如果表空间是AUTOEXTENSIBLE，考虑扩大MAXSIZE参数。
4. 考虑碎片整理。碎片整理详细内容，请见本书第十三章。
5. 如果NEXT EXTENT小于最大的可用连续空闲空间，但仍然显示空间不够，那就是Oracle闹鬼了，赶紧联系Oracle Support，在metalink中创建SR吧。

详细命令如下：

• 手工进行表空间的相邻空间空间的压缩

ALTER TABLESPACE <tablespace name> COALESCE;

• 将数据文件/临时文件修改为AUTOEXTEND

ALTER DATABASE DATAFILE|TEMPFILE ‘<full path and name>’ AUTOEXTEND ON MAXSIZE <integer> <k | m | g | t | p | e>;

建议最好设置MAXSIZE参数，避免空间被消耗殆尽。

• 增加数据文件/临时文件

ALTER TABLESPACE <tablespace name> ADD DATAFILE|TEMPFILE ‘<full path and file name>’ SIZE <integer> <k | m | g | t | p | e>;

• 若为数据字典管理表空间，降低NEXT_EXTENT/PCT_INCREASE

针对非临时和非分区数据段：

ALTER <SEGMENT TYPE> <segment_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

针对非临时和分区数据段：

ALTER TABLE <table_name> MODIFY PARTITION <partition_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

针对临时数据段：

ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer>);

• 对数据文件/临时文件进行RESIZE操作

ALTER DATABASE DATAFILE|TEMPFILE ‘<full path and file name>’ RESIZE <integer> <k | m | g | t | p | e>;

 

11.5 ORA-00376: 数据库文件不可读

故障现象

由于硬件、操作系统、集群软件、存储系统、人为操作失误等各方面原因，可能导致某个数据库文件出现异常，导致Oracle数据库无法读取这些文件，出于数据保护目的，Oracle会自动将这些文件设置为OFFLINE状态，并在alert.log文件中记录类似如下的错误信息：

ORA-00376: file 9 cannot be read at this time

ORA-01110: data file 9: ‘/u01/test.dbf’

ORACLE Instance PROD (pid = 8) – Error 376 encountered while recovering transaction (25, 37) on object 109359.

Tue Jul 12 10:35:46 2011

故障诊断

检查相关数据文件是否为Offline状态

SQL>Select status,file#,name from v$datafile where file#=<file value reported in ORA-01110>

例如，上例中：

SQL>Select status,file#,name from v$datafile where file#=9

STATUS FILE# NAME

———- ——- — ———

Recover 9 /u01/test.dbf

可见，9号文件状态为Recover，需要进行Recover操作。进一步查询该文件的当前SCN号：

SQL>column checkpoint_change# format 99999999999999999

SQL>select file#, status, fuzzy, checkpoint_time, checkpoint_change#,resetlogs_change#, resetlogs_time from v$datafile_header where file#=9

FILE# STATUS FUZ CHECKPOIN CHECKPOINT_CHANGE# RESETLOGS_CHANGE# RESETLOGS
———- ——- — ——— —————— —————– ———
9 OFFLINE YES 20-JUL-11 8517808305328 4263932 21-JUN-11

这样，上述9号文件的当前SCN为8517808305328，我们需要通过该SCN号之后的日志文件对9号文件进行recover操作。

故障恢复

查询包含上述SCN号的归档日志，并通过该归档日志至当前日志对该文件进行恢复：

SQL>Select sequence#,name from v$archived_log where 8517808305328 + 1 between first_change# and next_change# ;

 

SEQUENCE# NAME

—— ——————————————-

68         /u01/app/archivelog/O1_MF_1_68_72FLC6FO_.ARC

即针对9号文件，从68号归档日志至当前日志进行恢复，并将该文件恢复为Online状态。

SQL>Select name,sequence# from v$archived_log where sequence# >=68 ;

 

— 确保68号至当前归档日志都存在

 

 

SQL> recover datafile 9;

ORA-00279: change 8517808305328 generated at 07/20/2011 14:45:13 needed for thread 1
ORA-00289: suggestion :
/u01/app/archivelog/O1_MF_1_68_%U_.ARC

 

ORA-00280: change 8517808305328 for thread 1 is in sequence #68

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.

Media recovery complete.

 

SQL>Alter database datafile 9 online ;

故障根源分析及防范

如上所述，ORA-00376错误通常是由硬件、操作系统、集群软件、存储系统、人为操作失误等各方面原因而导致。因此，当ORA-00376错误发生时，一定要通过操作系统类似errpt命令去查找硬件和操作系统等环境是否存在问题，或者相关系统软件是否有Bug存在。

例如，本人曾在AIX平台就遭遇过一次此故障，就是通过IBM HACMP命令对LV设备做扩容操作时，触发了HACMP一个Bug而导致该LV不可见，触发了ORA-00376错误。另外，在该次故障中，客户对LV进行的扩容操作是对联机操作情况下进行的，即在数据库仍然对外服务的情况下进行的。因此，如果业务允许，建议大家尽量在停止对外服务的情况下，进行这类数据库变更操作。

 

11.6 ORA-01555: 快照太旧

什么是ORA-01555错误?

ORA-01555是Oracle数据库运行过程中常见的一个错误。以下就是Oracle关于ORA-01555错误的经典描述：

ORA-01555: snapshot too old (rollback segment too small)

Cause: rollback records needed by a reader for consistent read are

overwritten by other writers

简单而言，就是为保证一致性读的回退段数据被其它写进程所覆盖了。以下就是一个典型例子：

• 时间点1：会话1对 A表开始进行查询操作。
• 时间点2：会话2修改 A表的记录X。
• 时间点3：当会话1的查询语句查询到记录X时，通过SCN号发现记录X被修改了，而且修改的时间（时间点2）晚于时间点1。这样，Oracle将通过保存在UNDO中记录X修改前的数据（简称Before Image），来进行一致性读取。
• 时间点4：会话2修改 A表的记录Y，并且进行了commit操作。这样，该事务的slot数据就可以被Oracle覆盖了。
• 时间点5：会话2修改 A表的记录Z，并且进行了commit操作。此时，由于UNDO表空间的空间不足，记录Y的修改前的数据被Oracle覆盖了。
• 时间点6：当会话1的查询语句查询到记录Y时，通过SCN号发现记录Y被修改了，而且修改的时间（时间点6）晚于时间点1。于是，Oracle将通过保存在UNDO中记录Y修改前的数据（简称Before Image），来进行一致性读取。但此时记录Y修改前的数据已经在时间点5被覆盖了。因此，系统将报ORA-01555错误！

ORA-01555错误的原因和解决方案比较复杂，Oracle有关该错误处理的文章也比较多。本书我们一方面主要针对9i之后的自动UNDO管理技术（Automatic UNDO Management），另一方面也只针对普通表的ORA-01555错误处理，而不关注LOB等特殊对象的ORA-01555错误处理。

如何获取ORA-01555错误相关信息？

首先，分别从应用会话窗口和alert.log中分别获取相关信息。

例如，应用会话窗口显示错误信息：

ORA-01555: snapshot too old: rollback segment number 9 with name “_SYSSMU1$” too small

Alert.log中显示：

ORA-01555 caused by SQL statement below (Query Duration=9999 sec, SCN:0x000.008a7c2d)

其次，通过alert.log确定QUERY DURATION。上例中为9999秒。

第三，从应用会话信息中确定undo segment名称。例如：_SYSSMU1$。

最后，确定UNDO表空间的UNDO_RETENTION值。

SQL> show parameter undo_retention

如何解决ORA-01555错误？

1. 如果QUERY DURATION > UNDO_RETENTION

此时，Oracle无法保证当提交的事务过期，也就是超过UNDO_RETENTION时间之后，还能确保数据的一致性读取。

这种情况下，最有效的解决办法是优化查询语句，降低语句的QUERY DURATION时间。如果无法优化了，则只能参考QUERY DURATION时间值来扩大UNDO_RETENTION值，确保Oracle保存更长时间的UNDO信息。

扩大UNDO_RETENTION值，意味着需要更多的UNDO表空间，下面还将介绍UNDO表空间的计算方法。

2. 如果QUERY DURATION <= UNDO_RETENTION

在这种情况下，通常而言是UNDO表空间满了。如何进一步确定UNDO表空间是否满了呢？执行如下脚本：

set pagesize 25
set linesize 120

select inst_id,
to_char(begin_time,’MM/DD/YYYY HH24:MI’) begin_time,
UNXPSTEALCNT “# Unexpired|Stolen”,
EXPSTEALCNT “# Expired|Reused”,
SSOLDERRCNT “ORA-1555|Error”,
NOSPACEERRCNT “Out-Of-space|Error”,
MAXQUERYLEN “Max Query|Length”
from gv$undostat
where begin_time between
to_date(‘<start time of the ORA-1555 query>’,’MM/DD/YYYY HH24:MI:SS’)
and
to_date(‘<time when ORA-1555 occured>’,’MM/DD/YYYY HH24:MI:SS’)
order by inst_id, begin_time;

其中：

• UNXPSTEALCNT字段表示提交的Transaction Slots没有超出UNDO_RETENTION时间，也就是处于unexpired状态，但由于UNDO表空间满了，而被 Oracle覆盖了的次数。
• <start time of the ORA-1555 query>时间可通过ORA-1555发生时间减去QUERY DURATION而得到。例如：

Tue May 26 16:16:57 2009
ORA-01555 caused by SQL statement below (SQL ID: 54yn3n36w24ft, Query Duration=922 sec, SCN: 0x0007.8a55f4e3)

922秒为15分22秒。这样 ORA-1555开始发生的时间为 May 26 16:01:35 2009（16:01:35 = 16:16:57 – 15:22）。

如何计算UNDO表空间大小？

UNDO表空间的计算公式如下：

UndoSpace = UR * (UPS * DBS)

其中：

• UR = UNDO_RETENTION参数，单位为秒。
• UPS = 每秒产生的UNDO数据块数量。
• DBS = DB_BLOCK_SIZE参数。

上述UNDO_RETENTION、DB_BLOCK_SIZE可通过初始化参数文件获取，而UPS则可以通过查询v$undostat视图而获得。Oracle建议查询业务高峰时段产生的UNDO数据块数量。为此，执行如下查询：

SELECT undoblks / ((end_time – begin_time) * 86400) “Peak Undo Block Generation”

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat);

最终，计算高峰时段所需UNDO表空间大小的语句如下：

SELECT (UR * (UPS * DBS)) AS “Bytes”

FROM (SELECT value AS UR FROM v$parameter WHERE name = ‘undo_retention’),

(SELECT undoblks / ((end_time – begin_time) * 86400) AS UPS

FROM v$undostat

WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),

(SELECT block_size AS DBS

FROM dba_tablespaces

WHERE tablespace_name =

(SELECT UPPER(value)

FROM v$parameter

WHERE name = ‘undo_tablespace’));

 

11.7 ORA-30036: UNDO表空间无法扩展

什么叫ORA-30036错误?

ORA-30036也是Oracle数据库运行过程中常见的一个错误。以下就是Oracle关于ORA-30036错误的经典描述：

Error: ORA-30036 (ORA-30036)

Text: unable to extend segment by %s in undo tablespace ‘%s’

—————————————————————————

Cause: the specified undo tablespace has no more space available.

Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

该错误表示就是UNDO表空间不够了，简单解决办法就是对UNDO表空间进行扩容。但如同Oracle其它空间不够的类似错误一样，扩容并非唯一解决办法。

UNDO表空间分配算法

欲深入了解ORA-30036错误原因和解决办法，其实应从深入了解UNDO表空间分配算法开始。以下就是该算法主要思路：

1. 如果当前的UNDO extent还有空间，则从中分配新的数据块。
2. 否则，假设下一个extent过期（expired）了，则跳到（wrap）下一个extent，并且返回其第一个数据块。
3. 假设下一个extent为非过期（unexpired）的，则尝试从UNDO表空间分配新的空间。假设 UNDO表空间足够，则分配新的extent给Undo segment，并且返回新extent的第一个数据块。
4. 如果UNDO表空间不够了，则从offline状态的Undo Segment中去偷取过期（expired）的extent，分配给Undo segment，并且返回该extent的第一个数据块。
5. 如果offline状态的Undo Segment中没有过期（expired）的extent，则从Online状态的Undo Segment中偷取过期（expired）的extent，分配给Undo segment，并且返回该extent的第一个数据块。
6. 如果Undo表空间的数据文件是可扩展的，则扩展Undo表空间的数据文件，并且从中分配新的extent给Undo segment，以及返回该extent的第一个数据块。
7. 降低Undo保存期限参数（undo_retention）10%，并从释放的空间中偷取extent。
8. 从offline状态的Undo Segment中偷取非过期（unexpired）的extent。
9. 重复使用现有Undo Segment中非过期（unexpired）的extent。如果所有extent都处于忙碌状态，即都包含了未提交的信息，则跳到第10步。否则，跳到(wrap)下一个extent。
10. 从online状态的Undo Segment中偷取非过期（unexpired）的extent。
11. 如果上述所有尝试都失败了，则Oracle报错：ORA-30036！

诊断和解决办法

1. 首先，查询UNDO表空间的空间使用状况：

select sum(bytes) from dba_free_space where tablespace_name=’UNDOTBS1′;

select sum(bytes) from dba_data_files where tablespace_name=’UNDOTBS1′;

2. 确定UNDO表空间的数据文件是否为可扩展的：

select autoextensible from dba_data_files where tablespace_name=’UNDOTBS1′;

3. 按状态统计Undo Extents：

SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;

如果没有过期（expired）而只有非过期（unexpired）的Undo Extent，以及Active Extents，则Undo表空间的确太小，需要对Undo表空间大小进行重新规划并扩容。关于Undo表空间大小的计算方法，请见本章前述内容。在10g中还可以通过OEM中的Undo Advisor特性来进行Undo表空间的规划。

假设Undo表空间不够，则Oracle会尝试偷取非过期（unexpired）的Undo Extent，此时可能会导致ORA-1555错误。如果也没有非过期（unexpired）的Undo Extent，则的确需要对Undo表空间进行扩容。

10g中可以为Undo表空间指定Guaranteed Undo Retention特性。例如：

create undo tablespace undotbs1 datafile ‘undotbs1.dbf’size 1000M autoextend on

retention guarantee;

这样，Oracle就不会重复使用非过期（unexpired）的Undo Extent。因此，此时只能对Undo表空间扩容了。

关于Bug 5442919

如果有过期（expired）的Undo Extent，意味着这些extent是可以被重用的。但系统却报出ORA-30036，则很有可能是撞上Oracle Bug 5442919了。以下就是满足该Bug的所有条件：

1. undo_management=auto。
2. Undo表空间包含的数据文件均不能自动扩展。
3. DML操作失败并报ORA-30036错误，同时被写入log日志文件。而且alert.log中重复显示”Failure to extend rollback segment <us#>”，其中<us#>为相同值。
4. 实例运行时间达到1小时以上。
5. 系统存在大量offline的Undo Segment，例如1000个以上。

select count(*) from dba_rollback_segs where status=’OFFLINE’;

6. Undo表空间满了。
7. 存在大量过期（expired）或者非过期（unexpired）的Undo Extent。

select sum(bytes) “UNEXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’UNEXPIRED’;

select sum(bytes) “EXPIRED BYTES” from dba_undo_extents where tablespace_name=’UNDOTBS1’and status=’EXPIRED’;

该Bug在10.2.0.4以及11g中就已经修复了。在之前的版本，例如9i和10.2.0.1/2/3中，在某些平台可以向Oracle服务部门申请补丁回退（Backport），但这些版本早已经过了Oracle产品服务期，估计已经很难得到Oracle服务部门支持了。

 

11.8 日志切换频度过高问题

10多年之后才搞明白的一个原理

本人自从1988年开始接触并使用Oracle数据库，但直到1999年在某网站从事专职 DBA之后，才弄明白Oracle一个基本原理：我们都知道如果通过Word编写文档，当我们按“保存”按钮时，Word会将最新的文档存到硬盘文件系统中去了。但是，在我们执行Oracle SQL语句或应用，提交（Commit）一个事务之后，Oracle并没有马上把数据真正写到硬盘上去，而只是把该事务的DML/DDL语句写到联机日志文件去了。

下图先描述了Oracle的Redo log buffer、LGWR进程和Redo Log日志文件的工作原理图：

 

 

即当Oracle在执行DML/DDL语句时，先将这些语句写入到SGA的Redo Log Buffer之中。当发生如下几种情况时，才通过LGWR进程将这些语句，也就是日志项（Redo Log Entry）写入到Redo Log日志文件之中：

• 发生Commit语句时
• 每间隔3秒
• Redo Log Buffer被写满1M之后
• Redo Log Buffer被写满1/3之后
• 当DBWn进程将被修改的数据写入到硬盘，而这些数据对应的redo log项还没有被写入redo log文件时

也就是说，当执行Commit语句之后，Oracle只是把该事务的DML/DDL语句写到联机日志文件去了，而并没有马上把数据真正写到硬盘上去。那什么时候Oracle才真正把修改的数据写入到硬盘呢？一种情况下是在redo log一个（组）日志文件被写满，切换到另一个（组）redo log时候，此时叫做Log Switch。另一个就是在CHECKPOINT事件发生时。

Oracle这种“延迟＋批量”进行数据库操作的目的就是降低因频繁commit操作而产生的小事务对I/O读写操作的冲击，而是到事务堆积到一定程度，具体而言就是写满一个日志文件或发生CHECKPOINT事件发生时，才成批地进行数据库写操作，降低存储操作次数，提高整体处理性能。

这样，联机日志文件大小成了影响数据库日常交易操作性能的一个重要因素。联机日志文件设计比较大，显然能降低Log Switch次数，即降低I/O次数。但凡事都有两面性，如果设计过大，一旦数据库发生故障，特别是实例宕机而重启时，smon后台进程需要恢复的日志太多，故障恢复时间可能很长，从而影响整个系统的高可用性和故障恢复能力。

因此，联机日志文件大小的设计是需要综合平衡、全面考虑的。这就是Oracle作为企业级软件供应商考虑问题的角度和全面性。

本节下面内容主要讨论因联机日志文件太小，而引发的日志文件切换太频繁问题的诊断，以及可能造成的严重后果。

日志文件切换太频繁问题的诊断和解决

首先，关于日志文件大小的设置，Oracle公司并没有提出官方的建议，因为不同应用系统特点不同，Oracle很难给出一个文件大小建议的确切数据。但是，Oracle 公司给出了日志切换时间的建议。即：在数据库平时正常工作情况下，日志文件切换频度不应该低于15分钟，而在数据库工作高峰期，切换频度不应该低于5分钟。

如何诊断？一方面通过检查alert.log日志文件。当日志文件切换时，Oracle都会将切换时间记录在alert.log中。因此，粗略检查一下alert.log，就能看出redo log切换频度。

另一方面，通过检查alert.log文件，如果频繁出现“checkpoint not complete”报警信息，也说明日志产生非常多，系统都无法顺利完成checkpoint操作了。

再则，通过v$log、v$log_history等视图和如下语句，就能了解更精确的信息。例如日志文件切换和生成速度、大小等信息：

SQL> select group#,bytes from v$log;

SQL> select to_char(FIRST_TIME, ‘yyyy-mm-dd hh24’), count(*)

from v$log_history

where thread# = 1

group by to_char(FIRST_TIME, ‘yyyy-mm-dd hh24’)

order by to_char(FIRST_TIME, ‘yyyy-mm-dd hh24’);

上述第二个语句就是查询每小时的联机日志产生个数。根据这些视图，可以进行各种日志文件产生量的统计分析。以下就是我的同事通过这些视图，以及Oracle相关工具，对某系统的日志文件产生量进行的统计分析：

 

 

可见，该系统当前日志文件切换分布在0～5分钟之间的量，已经占到全部时段的77%。也就是说，该系统日志文件太小切换频度太高，导致存储I/O操作太多，是影响系统整体性能的重要因素。

上述系统的问题原因其实很简单，就是在数据库最初设计时，设计人员没有深入研究日志文件工作原理，更没有仔细规划日志文件大小，而是采用了缺省的配置：50M。再次验证：企业级数据库就需要专业化的精细化设计。

如何改造？就是新建一批更大的日志文件组，并通过Log Switch操作，逐渐切换到新的日志文件组，并删除老的、小的日志文件组。以下就是大致过程：

――　新建一批更大的日志文件组

alter database add logfile group 4 (‘/home1/oradata/DMCEDMAA/redo04.log’) size 256M;

alter database add logfile member  ‘/home3/DMCEDMAA/oradata/redo04_A.log’ to group 4;

 

alter database add logfile group 5 (‘/home1/oradata/DMCEDMAA/redo05.log’) size 256M;

alter database add logfile member  ‘/home3/DMCEDMAA/oradata/redo05_A.log’ to group 5;

 

alter database add logfile group 6 (‘/home1/oradata/DMCEDMAA/redo06.log’) size 256M;

alter database add logfile member  ‘/home3/DMCEDMAA/oradata/redo06_A.log’ to group 6;

 

――　强行切换到新日志文件组

alter system switch logfile;

alter system switch logfile;

alter system switch logfile;

 

――　删除旧日志文件组

alter database drop logfile group 1;

alter database drop logfile group 2;

alter database drop logfile group 3;

再次说明，至于新的日志文件到底设置到多大，Oracle并没有给出明确建议。笔者的建议是：首先，需要考虑日志文件大小对联机交易和数据库恢复能力两方面都有影响，切忌片面追求单一目标，例如盲目追求联机交易性能，而将日志文件设置太大。其次，逐步扩大日志文件大小，满足Oracle切换频度和切换时间建议值即可，即平时15分钟，高峰5分钟。

最严重情况：宕机！

就在撰写此章时，本人耳闻了一个重要客户RAC数据库异常宕机的严重故障。故障的起因是硬件报错，即光纤出现问题，但给Oracle带来的影响是：硬件故障期间，应用正在进行批处理操作，产生了大量日志，而日志文件设置过小，日志文件切换太频繁，阻塞了Oracle其它操作，Oracle居然把CHKP后台进程杀掉，最终导致数据库宕机。

以前知道日志文件设置太小，会引起数据库I/O操作太多，影响性能。这次居然导致数据库宕机了，这可是前所未闻，也让我开眼了，呵呵。以下就是该故障的详细过程，

• 故障现象

某客户的RAC系统出现硬件报错，后确认是光纤出现问题。系统报错信息如下：

[jtdba@sxyxydb2 bdump]$errpt | more

IDENTIFIER TIMESTAMP  T C RESOURCE_NAME  DESCRIPTION

DCB47997   0930121413 T H hdisk142       DISK OPERATION ERROR

3074FEB7   0930121413 T H fscsi2         ADAPTER ERROR

F7FA22C9   0929074513 I O SYSJ2          UNABLE TO ALLOCATE SPACE IN FILE SYSTEM

36A12C28   0929064313 T S VRTS:VXFS

…　…

即节点2连接存储的链路出现硬件报错，造成IO效率下降。在硬件故障期间，节点2的Oracle实例居然宕机了，以下就是alert.log记录的当时错误信息：

… …

Sun Sep 29 03:21:08 2013

Errors in file /u01/oracle/admin/chnldb/bdump/chnldb2_dbw0_1979152.trc:

ORA-00240: control file enqueue held for more than 120 seconds

Sun Sep 29 03:28:18 2013

System State dumped to trace file /u01/oracle/admin/chnldb/bdump/chnldb2_diag_1876292.trc

Sun Sep 29 03:28:26 2013

Errors in file /u01/oracle/admin/chnldb/bdump/chnldb2_diag_1876292.trc:

ORA-00494: enqueue [CF] held for too long (more than 900 seconds) by ‘inst 2, osid 1979152’

Sun Sep 29 03:28:33 2013

… …

• 故障原因分析

根据上述ORA-00494错误信息，我的同事很快在metalink中找到了《Database Crashes With ORA-00494 (Doc ID 753290.1)》，结合客户系统实际情况，给出了如下分析结论：

当硬件故障发生期间，数据库正在进行大批量数据处理，由于日志文件太小，导致REDO切换频繁，而CHECK POINT 、DBWR、LGWR进程都需要进行大量I/O操作，I/O写入速度很低。也使得Oracle访问控制文件操作超时，超过了900秒，在上述红色错误信息中，实例2的1979152进程阻塞了控制文件资源（CF enqueue）。此时，Oracle LGWR进程开始杀阻塞进程1979152，而1979152进程正是CKPT 后台进程，因此最终导致节点2宕机。

• 故障解决方式

当然在此次事故中，硬件光纤故障是“罪魁祸首”，在硬件厂商修复相关问题之后，系统很快平稳下来。但数据库日志文件设置太小，导致切换频度过高和过多的I/O操作，也在此次事故中起到了推波助澜的作用，并最终导致宕机。因此，Oracle建议适当扩大日志文件大小，确保日志文件切换频度满足Oracle建议值。Oracle甚至建议：如果采用Oracle相关智能建议工具（Advisor），可以通过查询V$INSTANCE_RECOVERY视图的OPTIMAL_LOGFILE_SIZE字段值，参考Oracle给出的日志文件大小建议。

 

11.9 故障处理的感和悟

故障诊断需要一颗伟大的心脏！

先回顾本人在《品悟性能优化》一书中针对故障诊断所叙述的一些观点：

• 故障诊断与性能优化一样都需要专门的技能，故障诊断包括的范围应该更宽泛一些， 例如：数据库异常宕机；数据故障或坏块；内存泄露；数据库被挂起… …。
• 某种程度上故障诊断对技术人员的心理素质要求更高一些。故障诊断需要一棵伟大的心脏！
• 故障诊断尽管也遵循“捕获现场信息和症状->分析原因->获得解决方案或Workaround->现场实施并分析结果”的普遍规律，但故障诊断涉及的问题领域、运用到的技术和技能太广泛了。

可见，故障诊断真是一门需要技术、方法、经验和心理素质皆备的学科。相比《品悟》一书，本书在故障诊断领域更为浓墨重彩一些，上一章和本章就分别以案例叙述不同故障的处理方法和思路，并介绍了数据库常见诊断工具的使用、常见故障分析等内容。

下面还将结合上述内容，针对故障处理发出更多的感和悟。

更多的感和悟

• 谋定而动

一旦故障发生，特别是出现数据库宕机、Hang、数据坏块、数据文件损坏、数据库无法启动和打开等严重故障时，首先应保持一颗冷静的心和清醒的头脑，其次，应判断故障的影响范围，特别是对业务的影响，初步确定解决方案，例如若数据库宕机，先尝试重新启动实例并打开数据库；若数据坏块，初步确定坏块所影响的数据对象，并初步确定恢复方案等。再则，尽量先考虑恢复业务，例如，将损坏的数据文件隔离（Offline），先打开数据库等。

总之，即便客户特别是领导已经是火急火燎，也应谋定而动，切忌自己乱了分寸，毛毛躁躁。

• 故障信息采集的重要性

就像看病就诊需要做各种检查一样，故障诊断、分析和处理同样需要全面的故障信息。因此，一方面当故障发生时，一定要注意保留故障现场信息，例如各种前台报错信息，alert.log、crsd.log等日志信息和trace文件信息，另一方面就像本书前一章所介绍的一样，主动、全面、准确地掌握一些常用的故障诊断工具的使用，将有效提高问题的诊断、分析和解决效率，也能加快与Oracle后台技术人员的沟通效率。

• 故障处理的主动性

故障的发生是任何人都不愿意看到的，故障的类型也是多种多样的。但针对一些诸如ORA-4030、ORA-4031、ORA-1555等常见错误，其实Oracle公司都提供了一些规范化的分析和处理流程文档。如果我们能在平日就研究这些文档，并形成自己的问题处理预案，那就像打仗需要提前准备作战方案一样，即便不是百战不殆，至少也能大大提高这些常见故障的处理效率。

• 寻求Oracle官方技术支持

数据库出现故障的原因千奇百怪，也涉及硬件、系统软件、Oracle数据库软件、应用软件等各个层面。就像前一章介绍的第一个案例一样，我们可以根据故障情况，先从Oracle错误信息文档开始，也可自己去Metalink等Oracle官方技术支持站点去查找原因和解决方式。如果都未奏效，或者故障非常严重、情形非常紧急，特别是在怀疑可能是Oracle产品本身问题的情况下，则建议迅速通过在Metalink中创建SR，而寻求Oracle官方技术支持。Oracle全球技术支持中心毕竟有庞大的技术专家资源，也有大量的案例经验，如果是产品问题，更可与Oracle研发部门直接沟通，毕竟解铃还需系铃人。此时，就需要提高SR处理效率，提高与后台技术专家的沟通效率。这些内容本章就不详述了。

 

11.10本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述
1.	My Oracle Support	《ORA-600/ORA-7445/ORA-700 Error Look-up Tool (Doc ID 153788.1)》	专门介绍ORA-00600/ORA-7445/ORA-00700等内部错误的诊断工具的官方文档。
2.	My Oracle Support	《ORA-600 [729] “UGA Space Leak” (Doc ID 31056.1)》	一篇介绍ORA-600 [729]错误处理的文档。
3.	My Oracle Support	《Diagnosing and Resolving ORA-4030 errors [ID 233869.1]》	PGA不够导致ORA-4030常见错误的文档：原理、原因、诊断和处理方法。
4.	My Oracle Support	《Master Note for Diagnosing OS Memory Problems and ORA-4030 (Doc ID 1088267.1)》	该文档包含了更多有关ORA-4030错误处理的文档。
5.	My Oracle Support	《Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] (Doc ID 146599.1)》	常见的ORA-4031错误的处理文档，还有视频哦。
6.	My Oracle Support	《Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1)》	常见的ORA-4031错误处理的又一篇文档，更多的原理介绍。
7.	My Oracle Support	《Master Note for Diagnosing ORA-4031 (Doc ID 1088239.1)》	该文档包含了更多有关ORA-4031错误处理的文档。
8.	My Oracle Support	《Resolving ORA-00376 Error Encountered in database (Doc ID 1339985.1)》	数据文件不可访问ORA-00376错误的标准处理文档。
9.	My Oracle Support	《TROUBLESHOOTING GUIDE (TSG) – UNABLE TO CREATE / EXTEND Errors (Doc ID 1025288.6)》	无法创建或扩展空间的原因和处理方法。
10.	My Oracle Support	《Master Note: Troubleshooting Oracle Tablespace Management (Doc ID 1522807.1)》	但凡遇到表空间管理方面问题，这篇文章都值得参考。包括SYSTEM、SYSAUX、Temporary、UNDO，以及普通表空间，内含很多链接文章。
11.	My Oracle Support	《Overview Of ORA-01653: Unable To Extend Table %s.%s By %s In Tablespace %s (Doc ID 151994.1)》	表空间不够了，简单的增加增加数据文件吗？不尽然，这篇文章介绍了多种可能的处理方法：增加数据文件，Resize数据文件，设置自动扩展，碎片管理… …
12.	My Oracle Support	《ORA-01555 “Snapshot too old” – Detailed Explanation [ID 40689.1]》	ORA-01555（快照太旧）一类经典错误的详细原理解释。
13.	My Oracle Support	《How To Size UNDO Tablespace For Automatic Undo Management [ID 262066.1]》	如何计算UNDO表空间大小？这篇文章给出了详细的计算过程和公式。
14.	My Oracle Support	《TROUBLESHOOTING GUIDE (TSG) – ORA-1555 [ID 467872.1]》	有关ORA-1555处理的一篇指导性官方文档。
15.	My Oracle Support	《Full UNDO Tablespace In 10gR2 [ID 413732.1]》	10g以上版本的UNDO表空间经常处于100%使用率状态，这是问题吗？需要为UNDO扩容吗？这不是问题，是正常现象，除非遇到了ORA-1555或ORA-30036错误。该文章进行了详细的原理解释。
16.	My Oracle Support	《Troubleshooting ORA-30036 – Unable To Extend Undo Tablespace [ID 460481.1]》	这是对UNDO表空间无法扩展错误的诊断和处理的标准文档。
17.	My Oracle Support	《Database Crashes With ORA-00494 (Doc ID 753290.1)》	日志文件太小，导致REDO切换频繁，最终居然会导致数据库实例宕机。这篇文章就描述了这样一件典型故障。

 

作者为: 

SHOUG成员 – ORACLE ACS高级顾问罗敏

本文永久地址：https://www.askmac.cn/?p=16572

IT系统不仅需要存储和处理大量的传统结构化数据，而且对各类半结构化，例如XML文档、Word文档等，以及非结构化的图片、图像、视频等信息的处理需求也日益增长。Oracle自8i开始就推出了大对象（LOB）技术，用于存储半结构化和非结构化的数据。

本章将首先介绍传统LOB技术的运用，并总结传统LOB技术的不足，然后将介绍Oracle 11g新一代的大对象处理技术：SecureFiles，以及将传统LOB向SecureFiles进行迁移的相关技术，最后介绍相关案例和进一步的参考资料。

传统LOB技术的运用

LOB字段分为存储二进制的BLOB字段、存储字符类型的CLOB、存储国家字符集的NCLOB，以及存储外部文件的BFILE等类型。LOB字段的设计和使用并不复杂，例如，以下就是创建一个包含LOB字段表的语句：

 

 

CREATE TABLE print_media
    ( product_id        NUMBER(6)
    , ad_id             NUMBER(6)
    , ad_composite      BLOB
    , ad_sourcetext     CLOB
    , ad_finaltext      CLOB
    , ad_fltextn        NCLOB
    , ad_textdocs_ntab  textdoc_tab
    , ad_photo          BLOB
    , ad_graphic        BFILE);

LOB表物理设计基本原则

LOB字段在物理设计和应用开发中，都具有独特的技术特征。以下是Oracle顾问根据国内外不同项目实施LOB字段的经验，提出的LOB表物理设计基本原则。这些原则的贯彻，将有效提高LOB字段的可管理型和性能：

 

• LOB表空间设计

为方便管理和处理的高性能，建议所有LOB字段单独建立表空间，并为每个LOB数据段单独命名。LOB表空间和数据段命名规则如下：

类型	命名
LOB表空间	TS_<基表名>_<LOB字段名>
LOB数据段	SEG_<基表名>_<LOB字段名>

如果在LOB字段建立索引，Oracle将LOB索引与LOB数据共同存储在LOB表空间。因此不需要建立LOB索引表空间。

另外建议LOB表空间的物理参数设计如下：

 

管理方式	分配方式	Uniform Size(M)	段空间管理
LOCAL	UNIFORM	256	AUTO
LOCAL	UNIFORM	256	AUTO

 

• ENABLE/DISABLE STORAGE IN ROW的确定

通过ENABLE/DISABLE STORAGE IN ROW的设置，当LOB字段值（包括控制信息）小于4K时，Oracle可将LOB字段值直接保存在基表或LOB段。

ENABLE STORAGE IN ROW为缺省值，其优点如下：

• 当大量LOB字段值小于4K时，则直接读取基表的记录，就可读取到LOB字段，减少了I/O次数，而且空间利用率较高。
• 当LOB字段值大于4K时，虽然LOB字段值被存储在LOB段，但LOB的控制信息依然存储在基表记录中，能加速LOB字段的访问。

DISABLE STORAGE IN ROW的优点如下：

• 当对基表的操作较多时，例如大量SELECT、UPDATE等，全表扫描、按范围扫描（range scans）时，基表不包括LOB字段值，处理效率更高。
• 基表和LOB字段值完全分开存储，有利于数据的管理。

基于上述特点分析，我们通常建议所有LOB表均采用DISABLE STORAGE IN ROW技术，即基表记录和LOB字段值完全分开存储，代价是空间消耗较大。

• CHUNK的确定

CHUNK值表示通过OCI或PL/SQL等访问LOB字段时，一次读取或写入LOB的数据量，缺省为一个数据块。CHUNK一般是数据块的倍数。

在某系统中，我们建议将CHUNK设定每个LOB字段的平均长度或频率最高的长度。即数据包的平均长度，例如，1M，2M等。

• PCTVERSION的确定

当LOB字段值（文稿）被修改时，Oracle将在LOB数据段保存原来的LOB值版本，以提供读取的一致性。

PCTVERSION表示了老版本LOB的消耗空间比例，缺省值为10%。PCTVERSION值主要取决于如下两个因素：

• LOB字段的修改频率？
• 被修改LOB字段的读取频率？

根据上述原理，我们为该系统的LOB字段的PCTVERSION值的确定，制定如下设置原则：

 

LOB值修改频率	读被修改的LOB频率	PCTVERSION
高	高	20
高	低	10
低	高	10
低	低	5
没有	没有	0

 

• CACHE/NOCACHE/CACHE READS的确定

Oracle在对LOB字段的处理时，可提供如下三种内存处理的方式：

• CACHE：将LOB字段值存储在内存中。
• NOCACHE：不将LOB字段值存储在内存中。或者存储在LRU列表的尾端，从而会容易被淘汰出内存。
• CACHE READS：只有在对LOB读取处理时，才存储在内存。

根据上述原理，我们在该系统建立如下原则：

CACHE方式	读频度	写频度
CACHE	高	高
NOCACHE	低	低
CACHE READS	高	低

 

• LOGGING/NOLOGGING的确定

同普通表的处理类似，LOB表日志方式的设计决定了日志文件的产生量和数据的可恢复性。以下是该系统LOB表日志文件的设计原则建议：

• 一般正常处理情况下，所有LOB表均设置为LOGGING方式。
• 在批量装载和插入LOB数据之前，建议将LOB表设置为NOCACHE NOLOGGING方式，从而降低LOG的产生量，从而保障数据物理备份的可行性。

传统LOB字段设计举例

根据客户提供的LOB表结构设计和处理特性分析，遵循上述LOB字段的设计原则，我们对该系统的LOB表字段设计如下：

 

表名称	LOB字段	LOB表空间名	LOB数据段名	大小（G）	CHUNK	PCTVERSION	CACHE
TB_URLCONTENT	F_CONTENTS CLOB	TS_TB_URLCONTENT_F_CONTENTS	SEG_TB_URLCONTENT_F_CONTENTS	23	4096	5	CACHE READS
	F_SNAPSHOT CLOB	TS_TB_URLCONTENT_F_SNAPSHOT	SEG_TB_URLCONTENT_F_SNAPSHOT	23	4096
TB_MATERIAL	F_CONTENTS CLOB	TS_TB_MATERIAL_F_CONTENTS	SEG_TB_MATERIAL_F_CONTENTS	16	4096	20	CACHE
TB_URLIMAGE	F_PICS BLOB	TS_TB_URLIMAGE_F_PICS	SEG_TB_URLIMAGE_F_PICS	92	16384	10	CACHE READS
…	…	…	…	…	…	…	…

 

以下就是LOB表的创建语句样本：

 

CREATE TABLE TB_URLCONTENT (
... ...
F_CONTENTS	CLOB,
F_SNAPSHOT	CLOB,
... ...
)
LOB (F_CONTENTS) STORE AS SEG_TB_URLCONTENT_F_CONTENTS (
TABLESPACE TS_TB_URLCONTENT_F_CONTENTS 
CHUNK 4096
PCTVERSION 5
CACHE READS
LOGGING
STORAGE(MAXEXTENTS UNLIMITED)
DISABLE STORAGE IN ROW);

传统LOB技术的不足

通过上述传统LOB技术的使用，可见欲做到充分满足LOB字段的可管理性和性能需求，还是需要下一番功夫的，而且传统LOB技术本身的如下局限，已很难满足高速增长的需求。

• 首先，传统LOB字段技术缺乏去重、压缩等功能，导致空间消耗过大。首发于askmac.cn

例如，我们在分析某系统过程中发现，该系统数据库总容量640GB，其中LOB字段共消耗了600GB，最主要的两个LOB字段就消耗了550GB，占了数据库总量的85%以上，而且还在高速增长中。实际上，该系统的LOB字段存在大量重复数据和可压缩空间。

• LOB字段的大量物理属性难于设计和管理

传统LOB字段有CHUNK、LOGGING/NOLOGGING、PCTVERSION、CACHE/NOCACHE/CACHE READS、ENABLE/DISABLE STORAGE IN ROW等大量物理属性。根据每个LOB字段访问特征的不同，合理地设计这些物理属性，才能在存储效率、访问性能等方面达到良好的效果。但国内大部分采用LOB字段的IT系统，几乎缺乏这些物理属性的定制化设计，而是采用Oracle的缺省物理属性，这种状况很难满足需求。例如，CACH参数缺省值为NO，即Oracle在缺省情况下，读取LOB字段时没有进行缓存，显然不能满足LOB字段读写频度较高的需求。

• LOB字段存在一些局限

传统LOB字段存在一些明显的局限。例如存储数据容量有限，通常是几兆数据；CHUNK属性不仅为定长，而且有上限（32K），容易导致大量碎片；LOB字段对OLTP应用支持不好；在 RAC环境下，LOB字段会导致应用扩展性不好，等等。

 

新一代大对象处理技术: SecureFiles

为有效解决现有LOB字段技术存在的上述问题，Oracle 公司在11g版本中推出了针对 LOB字段处理的新技术：SecureFiles。该技术在性能、可管理性、易用性等方面，具有如下具体特点和优势：

• 提供数据去重、压缩和透明加密功能

SecureFiles不仅可以有效降低LOB字段存储空间消耗，提高了访问效率，而且提高了LOB字段的数据安全性。

以上述某系统为例，我们将其中一个100GB的LOB字段转换为SecureFiles，并采用压缩技术之后，最终只消耗30GB空间，大大压缩了存储空间。

• 新的网络协议

SecureFiles提供一种新的Client/Server方式的内部读写机制，有效提高了大量数据传输的效率。

• 简化物理属性设计和管理

SecureFiles提供了大量自动化的物理属性机制，免去了大量物理属性设计和管理工作。例如：CHUNK属性为可变长，最大能支持到64M；Oracle能自动进行碎片整理；SecureFiles还自动进行redo和undo的管理，避免大量不必要的redo和 undo信息的产生。

由于SecureFiles技术的推出，Oracle从11g开始将传统LOB技术称之为BASICFILE。

 

SecureFiles相关技术细节

• 启用SecureFiles技术

通过新的初始化参数DB_SECUREFILE，DBA可控制如何使用SecureFiles。DB_SECUREFILE的取值和含义如下：

• PERMITTED（缺省值）：允许使用SecureFiles技术。
• ALWAYS：将所有LOB字段缺省创建为SecureFiles。但如果该表所在表空间不是ASSM（Automatic Segment Space Management）表空间,则将LOB字段创建为BASICFILE。
• NERVER：禁止使用SecureFiles技术。所有定义为SecureFiles的LOB字段，将被创建为BASICFILE。如果定义了与SecureFiles相关的选项（例如：去重、压缩、加密等），将导致异常。
• IGNORE：忽略SecureFiles关键字和所有相关的选项。
• 创建表举例

以下语句创建一个包含LOB字段的表，并且具有去重功能，另外具有缓存、不保留日志的功能。

 

 

CREATE TABLE func_spec(
 id number, doc CLOB) 
 LOB(doc) STORE AS SECUREFILE 
 (DEDUPLICATE LOB CACHE NOLOGGING);

 

以下语句创建一个包含LOB字段的表，并且具有高密度压缩、但不去重功能，另外具有缓存、不保留日志的功能。

 

CREATE TABLE test_spec (
 id number, doc  CLOB) 
 LOB(doc) STORE AS SECUREFILE 
 (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING);  

以下语句创建一个包含LOB字段的表，并且具有透明加密功能。

CREATE TABLE design_spec (id number, doc  CLOB) 
 LOB(doc) STORE AS SECUREFILE (ENCRYPT);  

• 修改表举例

以下语句关闭去重功能：

ALTER TABLE t1 MODIFY LOB(a) ( KEEP_DUPLICATES );

以下语句开启去重功能：

ALTER TABLE t1 MODIFY LOB(a) ( DEDUPLICATE LOB );

以下语句关闭压缩功能：

ALTER TABLE t1 MODIFY LOB(a) ( NOCOMPRESS );

以下语句针对某个分区开启压缩功能：

ALTER TABLE t1 MODIFY PARTITION p1 LOB(a) ( COMPRESS HIGH );

• SecureFiles技术的监控

通过查询*_SEGMENTS、*_LOBS、*_LOB_PARTITIONS、*_PART_LOBS等视图，可了解LOB字段的相关信息。例如，如下语句将查询SECF_TBS2表空间中的LOB字段信息：

 

 

SQL> SELECT segment_name, segment_type, segment_subtype 
  2  FROM dba_segments 
  3  WHERE tablespace_name = 'SECF_TBS2'
  4  AND segment_type = 'LOBSEGMENT'
  5  /
 
SEGMENT_NAME                 SEGMENT_TYPE        SEGMENT_SU
---------------------------- ------------------  ----------
SYS_LOB0000071583C00004$$    LOBSEGMENT          SECUREFILE

另外，通过DBMS_LOB.GETOPTIONS可查询SecureFiles类型的LOB字段的相关选项，例如是否去重、压缩或加密。通过DBMS_LOB.SETOPTIONS可进行相应的设置工作。通过DBMS_SPACE.SPACE_USAGE可查询SecureFiles类型的LOB字段的空间消耗情况。

上述操作的详细语句略，详细情况请见Oracle 11g联机文档的《Oracle® Database PL/SQL Packages and Types Reference》有关DBMS_LOB包的说明。

SecureFiles迁移方法

通过普通技术，例如CTAS/ITAS、Export/Import等可以将传统LOB字段转换为SecureFiles，但这些技术可能导致业务停顿时间过长。下面介绍两种更有效的技术：

• 在线重定义技术

通过在线重定义技术(Online Redefinition)，可在业务不停顿的情况下，将传统LOB字段转换为SecureFiles。例如：

 

REM Grant privileges required for online redefinition.
GRANT EXECUTE ON DBMS_REDEFINITION TO pm;
GRANT ALTER ANY TABLE TO pm;
GRANT DROP ANY TABLE TO pm;
GRANT LOCK ANY TABLE TO pm;
GRANT CREATE ANY TABLE TO pm;
GRANT SELECT ANY TABLE TO pm;
REM Privileges required to perform cloning of dependent objects.
GRANT CREATE ANY TRIGGER TO pm;
GRANT CREATE ANY INDEX TO pm;
CONNECT pm
DROP TABLE cust;
CREATE TABLE cust(c_id NUMBER PRIMARY KEY,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
);
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
-- Creating Interim Table
-- There is no need to specify constraints because they are
-- copied over from the original table.
CREATE TABLE cust_int(c_id NUMBER NOT NULL,
c_zip NUMBER,
c_name VARCHAR(30) DEFAULT NULL,
c_lob CLOB
) LOB(c_lob) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING);
DECLARE
col_mapping VARCHAR2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'c_id c_id , '||
'c_zip c_zip , '||
'c_name c_name, '||
'c_lob c_lob';
DBMS_REDEFINITION.START_REDEF_TABLE('pm', 'cust', 'cust_int', col_mapping);
END;
/
DECLARE
error_count pls_integer := 0;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('pm', 'cust', 'cust_int',
    1, TRUE,TRUE,TRUE,FALSE, error_count);
DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('pm', 'cust', 'cust_int');
-- Drop the interim table
DROP TABLE cust_int;
DESC cust;
-- The following insert statement fails. This illustrates
-- that the primary key constraint on the c_id column is
-- preserved after migration.
INSERT INTO cust VALUES(1, 94065, 'hhh', 'ttt');
SELECT * FROM cust;

虽然在线重定义技术需要额外的空间，但仍然建议优先考虑该技术的运用。
 分区交换技术
通过分区交换技术，也可在尽量减少业务不停顿，将传统LOB字段转换为SecureFiles。该技术具有如下特点：
 与分区技术结合，需要该表最大分区的额外空间。
 在分区交换时，可同步进行本地化索引的维护。
 按分区进行操作，缩短维护窗口。
详细操作过程略。

 

 

SecureFiles实施案例

我们在上述曾介绍过，某系统数据库总容量640GB，其中使用传统LOB技术的字段共消耗了600GB，最主要的两个LOB字段就消耗了550GB，占了数据库总量的85%以上，而且还在高速增长中。实际上，该系统的LOB字段存在大量重复数据和可压缩空间。

于是，我们将这两个表采用传统的CTAS技术，将传统LOB字段转换为SecureFiles字段，结果是这两个表空间又550GB下降为160GB，空间几乎下降了70%！

以下就是详细脚本：

 

 

CREATE TABLE FORESTFIREEW.T_FORECAST_THEMATIC_NEW 
   (	THEMATIC_ID VARCHAR2(36) NOT NULL ENABLE, 
	RESULT_ID VARCHAR2(36) NOT NULL ENABLE, 
	THEMATIC_TYPE VARCHAR2(10) NOT NULL ENABLE, 
	DATA_NO NUMBER(10,0), 
	DATA_DATE CHAR(8), 
	DATA_HOUR CHAR(2), 
	DATA_TYPE VARCHAR2(10), 
	DATA_VALUE NUMBER(8,2), 
	DATA_LEVEL NUMBER(2,0), 
	GEOMETRY CLOB, 
	 CONSTRAINT PK_FORECAST_THEMATIC_NEW PRIMARY KEY (THEMATIC_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  
  PARTITION BY RANGE (DATA_DATE) 
 (PARTITION SYS_P27  VALUES LESS THAN ('20120101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2011 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  , 
 PARTITION SYS_P28  VALUES LESS THAN ('20130101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2012 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)  , 
 PARTITION SYS_P29  VALUES LESS THAN ('20140101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2013 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P30  VALUES LESS THAN ('20150101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2014 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P31  VALUES LESS THAN ('20160101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2015 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) , 
 PARTITION SYS_P32  VALUES LESS THAN (MAXVALUE) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2016 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) ); 
 
 
 insert /*+ append */into T_FORECAST_THEMATIC_NEW nologging select * from T_FORECAST_THEMATIC;
 
   CREATE TABLE FORESTFIREEW.T_LIVE_THEMATIC_NEW
   (	THEMATIC_ID VARCHAR2(36) NOT NULL ENABLE, 
	RESULT_ID VARCHAR2(36) NOT NULL ENABLE, 
	THEMATIC_TYPE VARCHAR2(10) NOT NULL ENABLE, 
	DATA_NO NUMBER(10,0), 
	DATA_DATE CHAR(8), 
	DATA_HOUR CHAR(2), 
	DATA_VALUE NUMBER(8,2), 
	DATA_LEVEL NUMBER(2,0), 
	GEOMETRY CLOB, 
	 CONSTRAINT PK_LIVE_THEMATIC_NEW PRIMARY KEY (THEMATIC_ID)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING) 
  PARTITION BY RANGE (DATA_DATE) 
 (PARTITION SYS_P51  VALUES LESS THAN ('20120101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2011 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P52  VALUES LESS THAN ('20130101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2012 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P53  VALUES LESS THAN ('20140101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2013 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P54  VALUES LESS THAN ('20150101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2014 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P55  VALUES LESS THAN ('20160101') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2015 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING), 
 PARTITION SYS_P56  VALUES LESS THAN (MAXVALUE) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE FORESTFIREEW2016 
 LOB (GEOMETRY) STORE AS SECUREFILE (COMPRESS HIGH KEEP_DUPLICATES CACHE NOLOGGING)); 

 insert /*+ append */into T_LIVE_THEMATIC_NEW nologging select * from T_LIVE_THEMATIC;

本章参考资料及进一步读物

本章参考资料及进一步读物：

序号	资料类别	资料名称	资料概述
1.	Oracle 11g R2联机文档	《Oracle® Database SecureFiles and Large Objects Developer’s Guide》	这是Oracle 11g联机文档中专门介绍SecureFiles和大对象开发的专著，从事大对象开发的人员必读之物。
2.	Oracle大学教材	《Oracle® Database 11g New Features》的第10课	这是Oracle大学教材。欲看到图文并茂的该文档，只能报名参加该课程的培训了。
3.	My Oracle Support	《Master Note: Overview of Oracle Large Objects (BasicFiles LOBs and SecureFiles LOBs) (Doc ID 1490228.1)》	欲全面了解传统的BasicFiles大对象和新一代SecureFiles大对象，这篇文档就是主目录。
4.	My Oracle Support	《Summary Note Index for BasicFiles(LOB’s/BLOB’s/CLOB’s/NCLOB’s,BFILES) and SecureFiles (Doc ID 198160.1)》	又一个介绍传统的BasicFiles大对象和新一代SecureFiles大对象的文档集结地。
5.	My Oracle Support	《LOB Performance Guideline (Doc ID 268476.1)》	这篇文档针对传统BasicFiles大对象的性能问题进行了全面分析。既有设计问题，也有Oracle产品Bug问题，好好看看吧。其实更好的办法就是升级到11g，并采用SecureFiles技术。
6.	My Oracle Support	《LOBS – Storage, Redo and Performance Issues (Doc ID 66431.1)》	这篇文档介绍了传统BasicFiles大对象的存储特性、Redo等内部信息，以及可能存在的一些性能问题。重复一下：别纠结这些问题了，还是升级到11g，并采用SecureFiles技术吧。
7.	My Oracle Support	《POOR PERFORMANCE WITH LOB INSERTS (Doc ID 978045.1)》	LOB字段插入比较慢，什么原因？这篇文章给出了一个原因，那就是Recylebin！解决办法：关掉Recylebin！
8.	My Oracle Support	《Troubleshooting Guide (TSG) – Large Objects (LOBs) (Doc ID 846562.1)》	这篇文档介绍了如何诊断分析LOB字段故障的思路和方法。例如，如何确定问题？如何收集故障信息？如何研究和分析问题？如何提供解决方案？如何验证解决效果？这可是LOB字段故障诊断的宝典！

 

 

动态统计信息（Dynamic Statistics）是一个新的概念。在11g的数据库，我们知道的动态采样（dynamic sampling）是在优化sql语句之前收集最基本的对象的统计信息。
12c优化器会判断当前有效的统计信息是否足够，否则使用动态统计信息。动态统计信息是一个持久的统计信息，会存储在统计仓储中，因此可能会被其他的查询语句使用。在12c中，
优化器会判断是否动态统计信息是有用的，是否动态采样是正确的方法，并能自动决定动态采样的级别。

 

动态统计信息 ：

During the compilation of a SQL statement, the optimizer decides whether to use dynamic statistics by considering whether the available statistics are sufficient to generate a good execution plan. If the available statistics are not enough, then dynamic statistics are used. Dynamic statistics are persistent and may be used by other queries. One type of dynamic statistic is the information gathered by dynamic sampling. Traditionally, dynamic sampling would automatically occur only if one or more of the tables in the query did not have statistics. Dynamic sampling gathered basic statistics on these tables before optimizing the statement. Now, the optimizer automatically decides if dynamic statistics are useful for all SQL statements and if dynamic sampling is the right approach.

动态采样：

If it is, the optimizer also determines what dynamic sampling level is used

Dynamic statistics are automatically used when the optimizer deems it necessary and the resulting statistics are persistent in the statistics repository making them available to other queries.

“the optimizer automatically decides if dynamic statistics are useful for all SQL statements and if dynamic sampling is the right approach.If it is, the optimizer also determines what dynamic sampling level is used.”

动态统计信息的级别说明，可以参照下面的文档：

http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL451

这个新的特性是缺省的行为。 这个特征在12c上主要体现在优化器更加智能，动态统计信息可以持久化影响后来的sql执行上，这些都是11g所没有的，。
收集动态统计信息，可以通过设置OPTIMIZER_DYNAMIC_SAMPLING=0禁止掉。谢谢。

 

SCOPE

This Document concerns itself with highly desirable patches and related fixes in 12.1.0.2 that are not included in PSU updates, either because they contain optimizer layer fixes or because the PSU that will contain them has not yet been released.

 

 

DETAILS

While we try and include important fixes in the PSUs that are released on a quarterly basis, there are restrictions as to what can go into a PSU. One of the restrictions is fixes that may change execution plans, i.e. optimizer layer fixes. The reason for this is that it reduces the amount of testing required to go from one PSU to another.  Other fixes may not yet have been released in a PSU.

Please consider applying the following patches to avoid certain performance, ORA-600/ORA-7445, and wrong results issues.

Patches relevant for all platforms

For best results, install the latest 12.1.0.2 PSU as described in Document 756671.1, as it fixes many issues not listed in this note.  Install the patches listed here for the relevent PSU:

No PSU	PSU 1	2	3	4	5	160119	160419	160719	161018	170117	Bugs Fixed
Patch 19855835 for 12.1.0.2.0											Document 19855835.8 Upgrade slow when reorganizing large stats history tables NB: Only applicable for upgrades from 11.2.0.3 or below. Apply before running the 12.1.0.2 upgrade script. There is no benefit to applying it later on.
Patch 20879889 for 12.1.0.2.0							Included in PSU 160419 and above				Document 20879889.8 Open cursor leak from DML on table with a materialized view log
Patch 22837323 for 12.1.0.2.0					Included in PSU 5 and above						Document 20476175.8 High VERSION_COUNT (in V$SQLAREA) for query with OPT_PARAM(‘_fix_control’) hint
					Patch 22860122 for 12.1.0.2.5		Patch 23043224 for 12.1.0.2.160419				Document 20807398.8 ORA-600 [kgl-hash-collision] with fix to bug 20465582 installed
											Document 21826068.8 Wrong Results when _optimizer_aggr_groupby_elim=true
Patch 23665623 for 12.1.0.2.0											Document 23665623.8 ORA-7445 kkeics with fix for bug 21091518 (supersedes Document 21091518.8 Extend fix of bug 18304693 to Partition Views)
Patch 24739928 for 12.1.0.2.0	Request Patch 24739928 for 12.1.0.2.1	Patch 24739928 for 12.1.0.2.2	Patch 24739928 for 12.1.0.2.3	Patch 24739928 for 12.1.0.2.4			Patch 24739928 for 12.1.0.2.160419	Patch 24739928 for 12.1.0.2.160719		Included in PSU 170117 and above	Document 24739928.8 ORA-7445 [kglMutexCleanupAll] with fix for Bug 13542050 (supersedes Document 13542050.8 A mutex related hang with holder around 65534 (0xfffe))
Patch 18430870 for 12.1.0.2.0											Document 18430870.8 Adaptive Plan and Left Join Give Wrong Result
Patch 18650065 for 12.1.0.2.0							Patch 18650065 for 12.1.0.2.160419				Document 18650065.8 Wrong Results on Query with Subquery Using OR EXISTS or Null Accepting Semijoin
Patch 19174639 for 12.1.0.2.0											Document 19174639.8 Plan regression in 11.2.0.4 – OJPPD not occurring when expected
Patch 21171382 for 12.1.0.2.0											Document 21171382.8 Enh: AUTO_STAT_EXTENSIONS preference on DBMS_STATS

 

Other fixes relevant for all platforms

Set the parameter “_optimizer_aggr_groupby_elim”=false and “_optimizer_reduce_groupby_key”=false system-wide to prevent several wrong results bugs, including “Wrong Results with filtering on an aggregation expression” (Document 23147905.8) and “Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2” (Document 20634449.8):