了解11g OCM

2012/06/22 by 1 Comment

2007年发售的Oracle Database 11g号称Oracle 20年来最具影响力的产品， 在11gR1中大约引入了400个新功能和特性， 而在11gR2中这数字不会小于200个，且在已发布的2个Patch Set 11.2.0.2 和11.2.0.3中仍在引入新特性。

 

11gR1已经发布了6个年头(2007-2012)， 而11gR2 服役已经第四个年头了(2009-2012)，随着版本10gR2的support lifetime从Premier Support转变成Sustaining Support(Oracle Database 10.2 has now transitioned from Premier Support to Sustaining Support)。

 

越来越多的企业会在部署新系统时优先考虑采用11gR2，以减少software support的支出。  在OTN Forums的版本使用率调查中11.2.0.x已经占有了30.6%份额， 在未来几年中这个数字将超过其他几个版本使用率的总和。

 

 

oracle_database_version_usage_poll

 

 

版本11gR2在国内发力的黄金发展期即将到来， 但是国内DBA对版本11g的所知甚少， 实际上从10g 到 11g的改变并不局限于新特性， 很多同学在实施upgrade to 11g后发现了之前从未有过的不少问题， 这些问题主要集中在 管理、SQL执行计划和新进程的作用上，但不局限于此。

 

实际上Maclean 建议系统地去学习一下11g的新特性， OU(OU指Oracle University，即Oracle大学，原厂培训机构)提供的课程包括：

 

  • Oracle Database 11g: High Availability
  • Oracle Database 11g: Change Management
  • Oracle Database 11g: New Features
  • Oracle Database 11g: Performance
  • Oracle Database 11g: Scheduler
  • Oracle Database 11g: Server Manageability

 

都是值得考虑的课程。

 

当谈及11g ocm的时候， 大家可能都有一些茫然， OCM 还分版本吗？

是的， 最早的OCM在9i时代就有了， 不过在大陆9i的OCM非常少，在2000年左右大陆还没有OCM的考点，考OCM需要去韩国； 这个阶段的OCM当时主要是 Oracle在国内的一些重量级客户，例如移动、联通等，  不过这些第一批获得OCM的精英们后来绝大多数都从移动、联通跳槽到一些金融、银行单位了。   所以过往有一次去移动的一个部门，移动的领导向我们介绍当年这里可是出了好几个OCM， 但好景不长OCM精英纷纷跳槽， 以至于现在他要鼓励手下的员工去考OCP了。

 

OCM 在大陆大举登陆是在10g的时代，也即2005年以后的一段时间。这个时间点大陆在北京、上海、深圳等大城市的OU都有了OCM的考试点， 同时大量乙方、第三方的技术人员或为了自身发展或为了公司资质都投身到了OCM认证考试中来，由于考试人数众多，这导致几乎不变的10g OCM考题或被以帖子或被以博客的形式共享出来。  客观地说10g OCM 的考题在保密的前提下还是能够反映一位Oracle技术人员的知识广度和操作熟练度的， 应变能力也能够大致体现； 但是受限于考试的形式， 仍不无法测量应试者的知识深度和理论是否扎实；虽然如此，但是若能够以正大光明的形式通过10g OCM考试，那么仍能说明一个人有一定的技术功底。

 

说起10g的OCM考试，这里面有一个搞笑的小插曲。在2011年初参加OCM考试的同学可能遇到过尴尬的问题， 安装完成Grid Control/OMS软件后却因为网页证书在2011年过期的问题(之前的考试不会遇到该问题)，无法登陆OMS管理界面，这导致后续的考试环节都无法使用Grid Control完成。 如果这时节是你在考试，打开了GC的界面却发现无法登陆，即便心理素质再好恐怕也难免傻眼。具体由于这个意外造成考试结果如何计算不得而知，  只知道后来的OCM 考试提供一个专门解决该GC问题的patch，考生可能需要apply 这个patch，这算不算是变相地对10g OCM考试的扩展呢？

 

有人说10g OCM考试已经在国内泛滥了，这主要是因为部分帖子和博客披露了大量10g OCM的信息， 同时有不少Oracle WDP或非WDP的培训机构看中了10g OCM考试培训的这块肥肉；现在登录一个Oracle相关主题的门户网站或论坛或多或少都会看到这些机构在打10g OCM考试的广告。 如果说纯粹的应试的话，那么参加这些培训机构的10g OCM考试培训还是很有效的，显然他们把考题直接作为平时培训的练习了；  但如果你真的通过这些机构获得了10g OCM的认证，那么你发生会发现10g OCM不过如此， 和你水平差不多或者远不如你的人在这些机构学上几个月也都能获得10g OCM，  而10g OCM作为一个认证的最高点， 你在获得了它之后还有什么可以去学习、去提高的呢？   你在获得了OCM是否真的达到了这样的一个至高点？你是否还会有动力去学习其实你还不熟悉的包括在OCP范畴内的技术知识呢？ 你在Oracle之路今后要怎么走呢？ 别人是否信服你获得的这个日益有更多人获得的OCM呢？

 

所以Maclean的态度是，谢绝除OU外的一切培训机构!  Maclean 在考10g OCM的时候为了省钱仅参加了原厂2门最便宜的高级课程，加上10g OCM exam考试的费用， 总支出不超过2w￥。 对于Maclean来说 考ocm只是一个强迫自己去系统复习知识的机会， 获得10g OCM认证几乎没有给Maclean带来任何就业上的好处。

 

实际在这几年中10g OCM认证给求职者往往带来负面的影响， 有不少同学在面试中因为从业年数不多且已经获得OCM， 受到面试官的不经意的”调戏”， 已经有不下于2个10g OCM小兄弟由于这个认证带来的”调戏”现象对Maclean诉苦。  我只能告诉他们，世界上伟大的美德是”等待和希望”！

 

扯了许多无关的话题， 实际我的这篇文章是要介绍11g OCM的。 在9i OCM已退役， 10g OCM几乎已经在国内烂大街的今天， 11g OCM仍是一片乐土和净土。

 

说11g OCM是一片净土的原因是，11g OCM没有任何被过分共享的资源，我写的《11g OCM Upgrade Exam Tips》 一文提供了一些11g OCM考试的准备技巧； 同时具我所知，到目前为止全世界没有那一家培训机构能够做11g OCM认证的考试培训(可能需要排除OU)， 这保证了考试的公正性， 还了OCM认证一片净土。

 

 

11g OCM的花名册可以在OTN 11g OCM profile中找到，记录在册的大约有76人，绝大多数是Oracle原厂的员工或者OU的讲师，印度和欧美的Oracle技术人员占了绝大部分。

 

关于11g ocm的难度，可以参考上海OU资深讲师包光磊同学的博客，还是比较刺激的(http://toddbao.itpub.net/post/41112/500149):

 

 

 

11g OCM认证的获得路径目前有3条，即：

 

路径1，适合没有获得过9i/10g ocm的新晋同学： 11g ocp的获得者=>  参加2门原厂的11g高级课程  => 通过Oracle Database 11g Certified Master Exam2天的考试 =>  submit course form=> Submit Fulfillment Kit Request => 最后获得11g ocm  的证书和纪念服。

 

 

路径2，适合10g ocm： 已获得10g ocm认证=> 通过Oracle Database 11g Certified Master Upgrade Exam 1天的升级考试=> Submit Fulfillment Kit Request => 最后获得11g ocm  的证书和纪念服。

 

 

 

路径3，适合9i OCM: 已获得9i OCM => 通过1Z0-048 Oracle Database 10g R2: Administering RAC 或者 1Z0-058 Oracle Real Application Clusters 11g Release 2 and Grid Infrastructure Administration 考试 ，这2门考试在考试机构就可以考，不需要去OU =>  参加 Oracle Database 11g Certified Master Upgrade Exam(1天的升级考试) 或者Oracle Database 11g Certified Master Exam(2天的原生态11g OCM考试)，一般都会选择1天的升级考试  =>  Submit Fulfillment Kit Request => 最后获得11g ocm  的证书和纪念服。

 

 

关于获得11g ocm认证路径的更多信息 ，可以参考： Oracle Database 11g Certified Master track page   和 oracle 官方的认证博客 certification blog  。

 

11g OCM送的纪念服如下图(被我妈妈转送给邻居家的大妈了)：

 

Filed Under: Oracle, Oracle 11g新特性 Tagged With: ,

【推荐】DBA必须了解的11g中的一些变化

2012/06/16 by Leave a Comment

推荐一下老张的这张PPT 涵盖了11g中比较显著的一些变化，是DBA应当掌握的， 再推荐一下Thomas Zhang的博客，http://tomszrp.itpub.net/，很多内容都是独家的！

 

 

Filed Under: Oracle, Oracle 11g新特性

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

2012/04/30 by Leave a Comment

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

 

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn

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

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

 

 

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

 

 

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

 set long 100000

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

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

 

 

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

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

 

SQL> drop user "APEX_030200" cascade;

User dropped.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

no rows selected

 

 

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

 

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

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

 

 

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

 

 

SQL> oradebug setmypid;
Statement processed.

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

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

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

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

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

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

 

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

 

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

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

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

 

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

 

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

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

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

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

     75601
     75599
     75594
     75605

 

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

 

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

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

8 rows selected.

 

 

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

 

SQL> drop index oe.WHS_LOCATION_IX;

Index dropped.

SQL> drop index oe.ORD_CUSTOMER_IX;

Index dropped.

SQL> drop index oe.CUST_ACCOUNT_MANAGER_IX;

Index dropped.

SQL> drop index oe.PROD_SUPPLIER_IX;

Index dropped.

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

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

 

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

 

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

PL/SQL procedure successfully completed.

SQL> set pause on;
SQL> spool dic_check
SQL> SET LONG 100000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET PAGESIZE 100
SQL> SET LINESIZE 512
SQL> SELECT DBMS_HM.GET_RUN_REPORT('CHECK-MAC5') FROM DUAL;

DBMS_HM.GET_RUN_REPORT('CHECK-MAC5')
-----------------------------------------------------
Basic Run Information
 Run Name                     : check-mac5
 Run Id                       : 1301
 Check Name                   : Dictionary Integrity Check
 Mode                         : MANUAL
 Status                       : COMPLETED
 Start Time                   : 2012-04-30 09:33:28.540140 -04:00
 End Time                     : 2012-04-30 09:33:32.303679 -04:00
 Error Encountered            : 0
 Source Incident Id           : 0
 Number of Incidents Created  : 0

Input Paramters for the Run
 TABLE_NAME=ALL_CORE_TABLES
 CHECK_MASK=ALL

Run Findings And Recommendations
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1302
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgAB7 - description: Synonymn
               APEX is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1305
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWhAAu - description: Synonymn
               APEXWS is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1308
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgACO - description: Synonymn
               APEX_ACTIVITY_LOG is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1311
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgABl - description: Synonymn
               APEX_ADMIN is referenced
 Finding
 Finding Name  : Dictionary Inconsistency
 Finding ID    : 1314
 Type          : FAILURE
 Status        : OPEN
 Priority      : CRITICAL
 Message       : SQL dictionary health check: syn$.owner fk 95 on object SYN$
               failed
 Message       : Damaged rowid is AAAABEAABAAANWgACB - description: Synonymn
               APEX_APPLICATION is referenced

 

 

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

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , , ,

Oracle Database 11g R2 在Oracle Linux 6上获得认证

2012/04/02 by 3 Comments

在美国时间的2012年的3月22日Oracle官方认证了oracle Linux 6上的Oracle Database 11gR2 ，这要说起有那么一点点绕口，但是我相信既然这些产品都已经是oracle的了，那么今后的Certification认证速度将更那么及时一点。  好了 ， OEL 6+ DB 11gR2的组合认证是我们等了很久的了，实际上相信有不少朋友已经成功安装并且在使用这样的组合了。

Oracle官方声明将继续支持客户在RHEL 6上运行Oracle database 11gR2 ，且保持与Redhat Linux的兼容性(当然如果你想要11gR2的完整体验，例如smart flash cache特性的话，那么你仍需要Oracle Linux 6 )：

 

  • Oracle Database 11g Release 2 (R2) and Oracle Fusion Middleware 11g Release 1 (R1) are immediately available on Oracle Linux 6 with the Unbreakable Enterprise Kernel.
  • Oracle Database 11g R2 and Oracle Fusion Middleware 11g R1 will be available on Red Hat Enterprise Linux 6 (RHEL6) and Oracle Linux 6 with the Red Hat Compatible Kernel in 90 days.
  • Oracle offers direct Linux support to customers running RHEL6, Oracle Linux 6, or a combination of both.
  • Oracle Linux will continue to maintain compatibility with Red Hat Linux.

 

现在从MOS的Certifications专栏上可以发现版本 11.2.0.3 在Oracle Linux 6上获得了certified (Oracle Database 11.2.0.3.0 is certified on Linux x86-64 Oracle Linux 6)，但是版本 11.2.0.2 没有通过certified:

 

Oracle Database 11.2.0.3.0 with Linux x86-64 Oracle Linux 6

Note: Currently, only Unbreakable Enterprise Kernel R.1 (UEK 1) is certified

Product: For general information relating to certification for the Oracle Database product, including virtualization, interoperability, binary compatibiliy, general release and patch set information, see Core Database Certification Information (Doc ID 1306539.1).
Oracle R Enterprise Server is supported on Linux x86-64. The following are the supported operating system versions:
Oracle Linux 5 Update 6 or higer
Red Hat Enterprise Linux 5 Update 6 or higherOracle R Enterprise Client is supported on Linux x86-64 and Windows x86-64. The following are the supported operating system versions:
Oracle Linux 5 Update 6 or higher (64-bit)
Red Hat 5 Update 6 or higher (64-bit)
Microsoft Windows XP with Service Pack 3 or higher (32-bit and 64-bit)
Microsoft Windows 7 Service Pack 1 or higher (32-bit and 64-bit)

Platform: For details about certification of all Oracle Database releases on Linux x86-64, click here.

Certification: For details specific to the certification of Oracle Database Release 11.2 on Linux x86-64, click here.

 

Support Information

Oracle Database 11.2.0.3.0

End of Premier Support End of Error Correction End of Extended Support End of Sustaining Support
31-Jan-2015 Not Set 31-Jan-2018 Indefinite
Filed Under: Linux, Oracle, Oracle 11g新特性 Tagged With:

Richard Niemiec又一力作《Oracle Database 11g Release 2 Performance Tuning Tips & Techniques》

2012/03/14 by 15 Comments

对Oracle数据库调优性能浓厚的朋友肯定没有错过TUSC著名的Richard Niemiec的<Oracle Database 9i/10g Performance Tuning & Tips & Techniques> ，这2本书目前有中文译版； 这2本书包含了 Richard Niemiec 丰富的Oracle Database 调优经验、详细的案例研究。 Richard Niemiec是一名世界顶尖的Oracle优化专家，同时他也是一位Oracle认证的管理大师OCM， 他的书为优化人员提供了大量的提示和技巧：

 

 

 

 

随着10g 版本正式进入Sustaining Support， 12c R1版本可能在12年末、13年初release ， 现在正是 11g Release 2开始在国内大范围推广使用的时段。

 

如果你现在对 11g 的Tuning 新特性还不甚了解，包括如：adaptive Cursor Sharing、Result Cache、SQL Performance Analayze …. 。 你完全需要一本 11g 调优的完全指南，  而 Richard Niemiec 的 <Oracle Database 11g Release 2 Performance Tuning Tips & Techniques> 就是这样一本好书， 奉上封面和章节目录， 虽然这本书目前还没有中译版，但是相信很快可见：

 

 

 

 

·        Chapter 1   11g R1 & R2 New Features: Rewritten completely for basic Oracle11gR2 new features

·        Chapter 2   Basic Index Principles: Expanded to cover all of the index types and tested for 11gR2

·        Chapter 3   Disk Implementation: Updated for 11gR2: ASM, LMT, Auto UNDO, and improved I/O sections

·        Chapter 4   Initialization Parameters: Added MEMORY_TARGET and updated parameters for 11gR2

·        Chapter 5   Enterprise Manager & Grid Control: Added 11g screen shots, Grid Control, and some Exadata

·        Chapter 6   Explain Plan, SQL Plan Management, DBMS_MONITOR, and TRCSESS

·        Chapter 7   Using Hints for Tuning: Added new hints and updated others—the best resource for hints!

·        Chapter 8   Basic Query Tuning: Updated for 11gR2; added Result Cache and SQL Performance Analyzer

·        Chapter 9   Table Joins & Advanced Tuning: Updated for 11gR2; updated block tuning and added DB Replay

·        Chapter 10   PL/SQL Tuning: Expanded again as PL/SQL tuning expands; added 11gR2 changes

·        Chapter 11   Exadata, RAC, & Parallel Features: Added Exadata, improved RAC, & updated Parallel Query

·        Chapter 12   The V$ Views: Expanded again to show more V$ view queries, especially those new in 11gR2

·        Chapter 13   The X$ Tables: Expanded X$ queries, trace section, and X$ naming conventions

·        Chapter 14   AWR Report & Statspack: Updated AWR, Statspack for 11gR2; added mutexes & block tuning

·        Chapter 15   Quick System Review: Updated for 11gR2 and for much larger systems

·        Chapter 16   Monitoring the System Using Unix Utilities: Unix chapter updated to include more commands

·        Appendix A   Key Initialization Parameters: Updated queries for 11gR2 & new Top 25 initialization parameters

·        Appendix B   V$ Views: Updated for 11gR2 with updated V$ view queries

·        Appendix C   X$ Tables: Updated for 11gR2 with updated X$ queries

Filed Under: Oracle, Oracle 11g新特性, Oracle SQL性能调优 Tagged With: ,

收到了TUSC寄来的V$ View For Oracle Database 11g

2012/03/06 by 4 Comments

V$ view For oracle 10g版本的总览图相信大家都有看过， 而 这个11g版本的 我专门问了一下TUSC ，目前只有hard copy 版本的， 一起欣赏下吧：

 

IMG_20120306_105051

 

IMG_20120306_104935

Filed Under: Oracle, Oracle 11g新特性 Tagged With: ,

11gR2新特性:Heavy swapping observed on system in last 5 mins.

2012/01/09 by Leave a Comment

在11gR2中DBRM(database resource manager，11gR2中新的后台进程，见《Learning 11g New Background Processes》)会在Alert.log告警日志中反映OS操作系统最近5分钟是否有剧烈的swap活动了， 具体的日志如下：

 

WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [3.07%] pct of memory swapped out [4.44%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.

 

进一步诊断可以观察DBRM后台进程的trace：

 

[oracle@vrh2 trace]$ cat VPROD2_dbrm_5466.trc
Trace file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_dbrm_5466.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1
System name:    Linux
Node name:      vrh2.oracle.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: VPROD2
Redo thread mounted by this instance: 2
Oracle process number: 7
Unix process pid: 5466, image: oracle@vrh2.oracle.com (DBRM)

*** 2011-12-29 22:08:14.627
*** SESSION ID:(165.1) 2011-12-29 22:08:14.627
*** CLIENT ID:() 2011-12-29 22:08:14.627
*** SERVICE NAME:() 2011-12-29 22:08:14.627
*** MODULE NAME:() 2011-12-29 22:08:14.627
*** ACTION NAME:() 2011-12-29 22:08:14.627

kgsksysstop: blocking mode (2) timestamp: 1325214494612191
kgsksysstop: successful
kgsksysresume: successful

*** 2011-12-29 22:08:43.869
PQQ: Active Services changed
PQQ: Old service table
SvcIdx  SvcId Active ActDop
     5      5      1      0
     6      6      1      0
PQQ: New service table
SvcIdx  SvcId Active ActDop
     1      1      1      0
     2      2      1      0
     5      5      1      0
     6      6      1      0
2012-01-02 01:49:39.805820 : GSIPC:KSXPCB: msg 0x9bc353f0 status 34, type 12, dest 1, rcvr 0

*** 2012-01-02 01:49:54.509
PQQ: Skipping service checks
Trace file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_dbrm_5466.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1
System name:    Linux
Node name:      vrh2.oracle.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: VPROD2
Redo thread mounted by this instance: 2
Oracle process number: 7
Unix process pid: 5466, image: oracle@vrh2.oracle.com (DBRM)

*** 2012-01-03 03:05:54.518
*** SESSION ID:(165.1) 2012-01-03 03:05:54.518
*** CLIENT ID:() 2012-01-03 03:05:54.518
*** SERVICE NAME:() 2012-01-03 03:05:54.518
*** MODULE NAME:() 2012-01-03 03:05:54.518
*** ACTION NAME:() 2012-01-03 03:05:54.518

PQQ: Skipping service checks
kgsksysstop: blocking mode (2) timestamp: 1325577954530079
kgsksysstop: successful
kgsksysresume: successful

*** 2012-01-03 03:05:59.270
PQQ: Active Services changed
PQQ: Old service table
SvcIdx  SvcId Active ActDop
     5      5      1      0
     6      6      1      0
PQQ: New service table
SvcIdx  SvcId Active ActDop
     1      1      1      0
     2      2      1      0
     5      5      1      0
     6      6      1      0
PQQ: Checking service limits

*** 2012-01-07 02:06:51.856
PQQ: Skipping service checks
PQQ: Checking service limits

*** 2012-01-08 23:12:11.302
PQQ: Skipping service checks
Heavy swapping observed in last 5 mins:    [pct of total memory][bytes]

*** 2012-01-09 22:39:51.619
total swpin [ 3.07%][124709K], total swpout [ 4.44%][180120K]
vm stats captured every 30 secs for last 5 mins:
swpin:                 swpout:  
[ 0.27%][     11096K]  [ 0.25%][     10451K]
[ 0.27%][     11240K]  [ 0.29%][     12000K]
[ 0.29%][     12001K]  [ 0.02%][       853K]
[ 0.16%][      6849K]  [ 0.02%][       966K]
[ 0.53%][     21604K]  [ 0.09%][      4031K]
[ 0.10%][      4415K]  [ 0.03%][      1414K]
[ 0.43%][     17808K]  [ 0.37%][     15016K]
[ 0.64%][     25972K]  [ 1.61%][     65515K]
[ 0.26%][     10560K]  [ 0.88%][     36051K]
[ 0.07%][      3164K]  [ 0.83%][     33823K]

 

可以看到dbrm收集到了短期内的swapin和swapout数据，这样便于我们诊断由swap造成的性能或者hang问题。

 

解决OS 系统严重swap的一些思路：

1.  诊断是否存在内存泄露的进程，解决内存泄露
2.  调优SGA/PGA ，减少oracle对内存的占用
3.  利用  echo 3 > /proc/sys/vm/drop_caches 命令可以暂时释放一些cache的内存
4. 调整系统VM内存管理参数， 例如Linux上sysctl.conf中的以下几个参数

vm.min_free_kbytes   :Raising the value in /proc/sys/vm/min_free_kbytes will cause the system to start reclaiming memory at an earlier time than it would have before.

vm.vfs_cache_pressure :        At the default value of vfs_cache_pressure = 100 the kernel will attempt to reclaim dentries and inodes at a “fair” rate with respect to pagecache and swapcache reclaim. Decreasing vfs_cache_pressure causes the kernel to prefer to retain dentry and inode caches. Increasing vfs_cache_pressure beyond 100 causes the kernel to prefer to reclaim dentries and inodes.

vm.swappiness  : default 60 ;Apparently /proc/sys/vm/swappiness on Red Hat Linux allows the admin to tune how aggressively the kernel swaps out processes’ memory. Decreasing the  swappiness setting may result in improved Directory performance as the kernel
holds more of the server process in memory longer before swapping it out.

设置以下值，减少out of memory的可能性:

# Oracle-Validated setting for vm.min_free_kbytes is 51200 to avoid OOM killer
vm.min_free_kbytes = 51200
#vm.swappiness = 40
vm.vfs_cache_pressure = 200

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

11g新特性:hangdiag.sql实例hang诊断脚本

2012/01/09 by Leave a Comment

11g中引入了hangdiag.sql 这个脚本来帮助Oracle Support 用来收集诊断实例hang住所需要的diagnostic信息，这个脚本直接存放在11g 以后的$ORACLE_HOME/rdbms/admin目录下，无需用户另外下载。

 

该脚本用于收集单实例的hang诊断信息还是很不错的，如果是RAC环境的话推荐使用racdiag.sql这个脚本。

 

值得一提的是 hangdiag.sql会做 11g中独有的(oradebug dump hangdiag_header 1)并将v$wait_chain的源信息x$ksdhng_chains文本形式输出， 之后会做ashdump和systemstate(level 267 可能耗时较长)的dump转储操作，这样获得的trace文件信息就很全面了。

 

hangdiag.sql:

 

[oracle@vrh2 ~]$ cat $ORACLE_HOME/rdbms/admin/hangdiag.sql
Rem
Rem $Header: hangdiag.sql 08-jun-2007.02:06:43 amysoren Exp $
Rem
Rem hangdiag.sql
Rem
Rem Copyright (c) 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      hangdiag.sql - Hang Diagnosis script
Rem
Rem    DESCRIPTION
Rem      It is generally useful (but not necessary) to run this script from a
Rem      prelim mode connection.
Rem
Rem      This script dumps data required to diagnose hangs:
Rem      1. ASH dump
Rem      2. Systemstate dump with short callstacks
Rem      3. Hang analysis results (x$ksdhng_chains)
Rem
Rem    NOTES
Rem      It is required to set PID using oradebug setmypid/setospid/setorapid
Rem      before invoking this script. 
Rem
Rem      "oradebug tracefile_name" gives the file name including the path of
Rem      the trace file containing the dumps.
Rem
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    amysoren    06/08/07 - direct_access command syntax change
Rem    avaliani    05/17/07 - bug 5499564: add x$ksdhng_chains
Rem    amysoren    01/10/07 - add ashdump, systemstate dump
Rem    amysoren    01/10/07 - Created
Rem

-- begin hang diag dump
oradebug dump hangdiag_header 1

-- dump hang analysis chains
oradebug direct_access enable trace
oradebug direct_access disable reply
oradebug direct_access set content_type = 'text/plain'
oradebug direct_access select * from x$ksdhng_chains

-- dump ash data
oradebug dump ashdump 5

-- dump systemstate with short callstacks
oradebug dump systemstate 267

x$ksdhng_chains内部视图的字段如下

SQL> desc x$ksdhng_chains;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 CHAIN_ID                                           NUMBER
 CHAIN_IS_CYCLE                                     NUMBER
 CHAIN_SIGNATURE                                    VARCHAR2(801)
 CHAIN_SIGNATURE_HASH                               NUMBER
 INSTANCE                                           NUMBER
 OSID                                               VARCHAR2(25)
 PID                                                NUMBER
 SID                                                NUMBER
 SESS_SERIAL#                                       NUMBER
 BLOCKER_IS_VALID                                   NUMBER
 BLOCKER_INSTANCE                                   NUMBER
 BLOCKER_OSID                                       VARCHAR2(25)
 BLOCKER_PID                                        NUMBER
 BLOCKER_SID                                        NUMBER
 BLOCKER_SESS_SERIAL#                               NUMBER
 BLOCKER_CHAIN_ID                                   NUMBER
 IN_WAIT                                            NUMBER
 TIME_SINCE_LAST_WAIT_SECS                          NUMBER
 WAIT_ID                                            NUMBER
 WAIT_EVENT                                         NUMBER
 WAIT_EVENT_TEXT                                    VARCHAR2(64)
 P1                                                 NUMBER
 P1_TEXT                                            VARCHAR2(64)
 P2                                                 NUMBER
 P2_TEXT                                            VARCHAR2(64)
 P3                                                 NUMBER
 P3_TEXT                                            VARCHAR2(64)
 IN_WAIT_SECS                                       NUMBER
 TIME_REMAINING_SECS                                NUMBER
 NUM_WAITERS                                        NUMBER
 ROW_WAIT_OBJ#                                      NUMBER
 ROW_WAIT_FILE#                                     NUMBER
 ROW_WAIT_BLOCK#                                    NUMBER
 ROW_WAIT_ROW#                                      NUMBER

 

 

使用范例如下：

 

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn  

SQL> oradebug setmypid;
Statement processed.
SQL>
SQL>
SQL> @?/rdbms/admin/hangdiag    
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
SQL> oradebug tracefile_name;
/s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ora_22612.trc

Sample output 示例输出如下：

Processing Oradebug command 'setmypid'

Oradebug command 'setmypid' console output: <none>

Processing Oradebug command 'dump hangdiag_header 1'
Hang diagnosis dumps

Oradebug command 'dump hangdiag_header 1' console output: <none>
Result of fixed table query: select * from x$ksdhng_chains
ADDR    = 7FA5333FDC08
INDX    = 0
INST_ID = 2
CHAIN_ID        = 1
CHAIN_IS_CYCLE  = 0
CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention'
CHAIN_SIGNATURE_HASH    = 373050554
INSTANCE        = 2
OSID    = 26593
PID     = 47
SID     = 188
SESS_SERIAL#    = 1879
BLOCKER_IS_VALID        = 1
BLOCKER_INSTANCE        = 2
BLOCKER_OSID    = 25967
BLOCKER_PID     = 43
BLOCKER_SID     = 176
BLOCKER_SESS_SERIAL#    = 9631
BLOCKER_CHAIN_ID        = 0
IN_WAIT = 1
TIME_SINCE_LAST_WAIT_SECS       = 0
WAIT_ID = 8
WAIT_EVENT      = 234
WAIT_EVENT_TEXT = enq: TM - contention
P1      = 1414332422
P1_TEXT = name|mode
P2      = 79775
P2_TEXT = object #
P3      = 0
P3_TEXT = table/partition
IN_WAIT_SECS    = 692
TIME_REMAINING_SECS     = -1
NUM_WAITERS     = 0
ROW_WAIT_OBJ#   = 79775
ROW_WAIT_FILE#  = 0
ROW_WAIT_BLOCK# = 0
ROW_WAIT_ROW#   = 0

ADDR    = 7FA5333FDC08
INDX    = 1
INST_ID = 2
CHAIN_ID        = 1
CHAIN_IS_CYCLE  = 0
CHAIN_SIGNATURE = 'SQL*Net message from client'<='enq: TM - contention'
CHAIN_SIGNATURE_HASH    = 373050554
INSTANCE        = 2
OSID    = 25967
PID     = 43
SID     = 176
SESS_SERIAL#    = 9631
BLOCKER_IS_VALID        = 0
BLOCKER_INSTANCE        = 0
BLOCKER_OSID    =
BLOCKER_PID     = 0
BLOCKER_SID     = 0
BLOCKER_SESS_SERIAL#    = 0
BLOCKER_CHAIN_ID        = 0
IN_WAIT = 1
TIME_SINCE_LAST_WAIT_SECS       = 0
WAIT_ID = 148
WAIT_EVENT      = 352
WAIT_EVENT_TEXT = SQL*Net message from client
P1      = 1650815232
P1_TEXT = driver id
P2      = 1
P2_TEXT = #bytes
P3      = 0
P3_TEXT =
IN_WAIT_SECS    = 568
TIME_REMAINING_SECS     = -1
NUM_WAITERS     = 1
ROW_WAIT_OBJ#   = -1
ROW_WAIT_FILE#  = 0
ROW_WAIT_BLOCK# = 0
ROW_WAIT_ROW#   = 0

2 rows selected

Processing Oradebug command 'dump ashdump 5'
ASH dump
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER BEGIN>>>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table <ashdump>
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file <ashldr.ctl> as below
----------------------------------------------------------------
load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(
SNAP_ID  CONSTANT 0           ,
DBID                          ,
INSTANCE_NUMBER               ,
SAMPLE_ID                     ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   ,
SESSION_ID                    ,
SESSION_SERIAL#               ,
SESSION_TYPE                  ,
USER_ID                       ,
SQL_ID                        ,
SQL_CHILD_NUMBER              ,
SQL_OPCODE                    ,
FORCE_MATCHING_SIGNATURE      ,
TOP_LEVEL_SQL_ID              ,
TOP_LEVEL_SQL_OPCODE          ,
SQL_PLAN_HASH_VALUE           ,
SQL_PLAN_LINE_ID              ,
SQL_PLAN_OPERATION#           ,
SQL_PLAN_OPTIONS#             ,
SQL_EXEC_ID                   ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   ,
PLSQL_ENTRY_OBJECT_ID         ,
PLSQL_ENTRY_SUBPROGRAM_ID     ,
PLSQL_OBJECT_ID               ,
PLSQL_SUBPROGRAM_ID           ,
QC_INSTANCE_ID                ,
QC_SESSION_ID                 ,
QC_SESSION_SERIAL#            ,
EVENT_ID                      ,
SEQ#                          ,
P1                            ,
P2                            ,
P3                            ,
WAIT_TIME                     ,
TIME_WAITED                   ,
BLOCKING_SESSION              ,
BLOCKING_SESSION_SERIAL#      ,
BLOCKING_INST_ID              ,
CURRENT_OBJ#                  ,
CURRENT_FILE#                 ,
CURRENT_BLOCK#                ,
CURRENT_ROW#                  ,
TOP_LEVEL_CALL#               ,
CONSUMER_GROUP_ID             ,
XID                           ,
REMOTE_INSTANCE#              ,
TIME_MODEL                    ,
SERVICE_HASH                  ,
PROGRAM                       ,
MODULE                        ,
ACTION                        ,
CLIENT_ID                     ,
MACHINE                       ,
PORT                          ,
ECID
)
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data=<this_trace_filename> errors=1000000
---------------------------------------------------
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP HEADER END>>>
<<<ACTIVE SESSION HISTORY - PROCESS TRACE DUMP BEGIN>>>
####
881465081,2,2082369,"01-10-2012 00:49:06.871520000",188,1879,1,0,"562cub3hk2tjy",
0,26,0,"",0,0,0,0,0,33554433,"01/10/2012 00:37:27",0,0,0,0,0,0,0,668627480,9,1414332422,
79775,0,0,0,176,9631,2,79775,0,0,0,94,12553,,0,1024,3427055676,"sqlplus@vrh2.oracle.com (TNS V1-V3)",
"sqlplus@vrh2.oracle.com (TNS V1-V3)","","","vrh2.oracle.com",0,""
.......................................

Processing Oradebug command 'dump systemstate 267'
===================================================
SYSTEM STATE (level=11, with short stacks)
------------
System global information:
.............................
call stack performance statistics:
total                  : 0.030000 sec              
setup                  : 0.000000 sec              
stack unwind           : 0.000000 sec              
symbol translation     : 0.030000 sec
printing the call stack: 0.000000 sec              
printing frame data    : 0.000000 sec              
printing argument data : 0.000000 sec              
----- End of Call Stack Trace -----

 

好了 ， 就这么简单， 把trace文件打个zip包传到 My Oracle Support上去吧， 当然你也可以找我！我叫 Maclean.Liu!

Filed Under: Oracle, Oracle 11g新特性, Oracle脚本script Tagged With: ,

11g新特性:X$DBGALERTEXT一个很酷的内部视图

2012/01/06 by Leave a Comment

以往我们为了管理数据库的便利，总是希望能够利用SQL查询Oracle关键的Alert.log告警日志，在11g以前可以通过创建Alert.log内容的外部表来实现查询日志内容的目的的。

 

从11g 开始引入了新的ADR(Automatic Diagnostic Repository ADR is a file-based repository for database diagnostic data, such as traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more. It has a unified directory structure across multiple instances and multiple products stored outside of any database. It is,therefore, available for problem diagnosis when the database is down.)自动诊断仓库特性，默认的Alert.log转换为LOG.XML的形式，默认存放在$ADR_HOME/alert&trace目录下，并且为日志条目增加了如Level之类的属性，使得告警日志能够为Oracle Support提供更多有用的信息。

 

 

得益于XML格式的日志，可以更方便地将数据库告警日志内容转换为行列数。 所以从11g开始 ， 我们甚至于不需要去手动创建外部表了，Oracle 直接提供了X$DBGALERTEXT 这一内部视图来方便我们访问Alert文本：

 

 

SQL>desc X$DBGALERTEXT;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ADDR                                               RAW(8)
 INDX                                               NUMBER
 INST_ID                                            NUMBER
 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE
 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE
 ORGANIZATION_ID                                    VARCHAR2(64)
 COMPONENT_ID                                       VARCHAR2(64)
 HOST_ID                                            VARCHAR2(64)
 HOST_ADDRESS                                       VARCHAR2(46)
 MESSAGE_TYPE                                       NUMBER
 MESSAGE_LEVEL                                      NUMBER
 MESSAGE_ID                                         VARCHAR2(64)
 MESSAGE_GROUP                                      VARCHAR2(64)
 CLIENT_ID                                          VARCHAR2(64)
 MODULE_ID                                          VARCHAR2(64)
 PROCESS_ID                                         VARCHAR2(32)
 THREAD_ID                                          VARCHAR2(64)
 USER_ID                                            VARCHAR2(64)
 INSTANCE_ID                                        VARCHAR2(64)
 DETAILED_LOCATION                                  VARCHAR2(160)
 PROBLEM_KEY                                        VARCHAR2(64)
 UPSTREAM_COMP_ID                                   VARCHAR2(100)
 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)
 EXECUTION_CONTEXT_ID                               VARCHAR2(100)
 EXECUTION_CONTEXT_SEQUENCE                         NUMBER
 ERROR_INSTANCE_ID                                  NUMBER
 ERROR_INSTANCE_SEQUENCE                            NUMBER
 VERSION                                            NUMBER
 MESSAGE_TEXT                                       VARCHAR2(2048)
 MESSAGE_ARGUMENTS                                  VARCHAR2(128)
 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)
 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)
 PARTITION                                          NUMBER
 RECORD_ID                                          NUMBER

SQL> set linesize 100 pagesize 1400
SQL> select originating_timestamp, message_group, problem_key, message_text
  2    from X$DBGALERTEXT
  3   where message_text like '%ORA-00600%'
  4     and rownum < 10;

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_GROUP
----------------------------------------------------------------
PROBLEM_KEY
----------------------------------------------------------------
MESSAGE_TEXT
----------------------------------------------------------------------------------------------------
10-NOV-11 03.15.52.025 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11106):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

10-NOV-11 03.15.54.882 AM -05:00
Generic Internal Error
ORA 600
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_ora_5547.trc  (incident=11108):
ORA-00600: internal error code, arguments: [], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.07.918 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc  (incident=16965):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.13.11.260 AM -05:00

Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_smon_4689.trc:
ORA-01595: error freeing extent (3) of rollback segment (10))
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.16.56.765 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16997):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.17.00.852 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_4935.trc  (incident=16998):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.07.859 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=16999):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.20.09.654 AM -05:00
Generic Internal Error
ORA 600 [ORA-00600: internal error code, arguments: [4194], [],
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_mmon_5029.trc  (incident=17000):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

20-NOV-11 12.22.43.632 AM -05:00
Generic Internal Error
ORA 600 [4194]
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD1/trace/VPROD1_m000_5137.trc  (incident=17141):
ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []

 

X$DBGALERTEXT的内容极其丰富，包括message记录发生的日期、发生问题的进程号、trace日志的位置、问题的关键(problem key)，可以为我们分析数据库日志提供极为强大的接口，节约大量的时间。

 

X$DBGALERTEXT, that allows the log.xml file to be queried like a table. The X$DBGALERTEXT table is effectively indexed by the date the alert was raised so queries against this table based on a date range, which we are doing here, is efficient and performant.

In 11g, X$DBGALERTEXT now exists as a table. You can query the table for the specific columns needed.

 

 

 

但是请注意X$DBGALERTEXT的数据实际来源于ADR中的log.xml文件， 若xml被删除或者内容缺失均会影响到X$DBGALERTEXT的信息完整性：

 

SQL> select * from v$version;

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

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     51740

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log.xml

SQL> select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
     34005

SQL> ! echo > /s01/orabase/diag/rdbms/vprod/VPROD1/alert/log_1.xml      

SQL>  select count(*) from X$DBGALERTEXT;

  COUNT(*)
----------
         0

 

可以看到X$DBGALERTEXT的数据来源于 log.xml及其归档， 在11g中 当log.xml的大小超过10MB时，Oracle会将其内容归档到如log_$N.xml这样的归档文件中，并清空当前的log.xml的内容。

 

通过X$DBGALERTEXT内部视图接口， 我们可以实现一些原本想都不敢想的日志分析目的, 例如我要找出最近30天内出现过的Internal Errors：

 

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 30
  4     and message_group = 'Generic Internal Error';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.10.998 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27673):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.13.105 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27674):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.12.14.107 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27675):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.12.16.017 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_smon_23422.trc
 (incident=27676):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.25.562 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29076):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.26.989 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_mmon_30374.trc
 (incident=29077):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.40.29.020 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29244):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.40.31.595 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30561.trc
 (incident=29245):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

08-DEC-11 03.41.17.338 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29260):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
08-DEC-11 03.41.18.668 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_m001_30640.trc
 (incident=29261):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [
], [], [], []

10-DEC-11 01.45.51.556 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33838):

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [],
[], [], [], [], [], [], []

10-DEC-11 01.45.52.304 AM -05:00
Errors in file /s01/orabase/diag/rdbms/vprod/VPROD2/trace/VPROD2_ckpt_1026.trc
(incident=33839):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [],
[], [], [], []

 

或者我要审计最近2天内管理员执行过的DDL语句:

 

SQL> select originating_timestamp, message_text
  2    from X$DBGALERTEXT
  3   where originating_timestamp > sysdate - 2
  4     and message_group = 'admin_ddl';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
--------------------------------------------------------------------------------
07-JAN-12 02.05.48.770 AM -05:00
alter database open

07-JAN-12 02.05.48.781 AM -05:00
ORA-1531 signalled during: alter database open...

 

Metalink文档How to – Monitor Non Critical 11g Database Alert Log Errors Using a SQL UDM [ID 961682.1] 介绍了一种利用X$DBGALERTEXT实现计算告警日志信息度量的方法。

 

Oracle Freelist上对于X$DBGALERTEXT在近期也有精彩的讨论，值得移步一看。

Filed Under: Oracle, Oracle 11g新特性 Tagged With: ,

11g新特性

2012/01/04 by 2 Comments

以下是Maclean Liu所编写的Oracle 11g新特性的文章汇总列表：

 

11g新特性:A useful View V$DIAG_INFO
11gR2新特性:LMHB Lock Manager Heart Beat后台进程
给11gR2 RAC添加LISTENER监听器并静态注册
11.2 中Oracle Cluster Registry(OCR)可选的存储设备
11.2.0.3 实例启动现在提供Large Pages Information大内存页信息了
Does Duplicate Target Database need Pre-existing DB backup?
11gR2新特性:STANDBY_MAX_DATA_DELAY
11g 新特性IGNORE_ROW_ON_DUPKEY_INDEX提示
11g新特性SQL执行计划管理(SQL Plan Management)
11g新特性-在线实施补丁online patching
11g新特性:Rolling Upgrade With Physical Standby
11g新特性-SQL PLUS 错误日志
11g新特性:Note raised when explain plan for create index
11g compression 新特性
11g新特性之闪回事务处理取消
11g新动态性能视图V$SQL_MONITOR,V$SQL_PLAN_MONITOR
11g默认审计选项
Examine 11g automatic block Corruption recovery
Duplicate standby database from active database
11g Database Installation flow
Setup Oracle Direct NFS Client
Recreate failovered primary database using Flashback Database
11g中的db_block_checking参数
11.2.0.2补丁集安装体验
Oracle 11g中数据库能有多大？
undo backup optimization does not work on 11.2.0.1?
11g新特性之IO校准(IO Calibration)
11g中AWR新快照视图
11g r2中对闪回数据归档的增强
11g Release 2 enhanced Tablespace Point In Time Recovery
11g新特性:Streams同步捕获
Applying online patch on 11gr2
How to recover from root.sh on 11.2 Grid Infrastructure Failed
Uninstall/Remove 11.2.0.2 Grid Infrastructure & Database in Linux
为11.2.0.2 Grid Infrastructure添加节点
Find password cracker in 11g
11g Multi-Column Correlation Stats and Dynamic Sampling
11g内存管理新特性的internal表现
Oracle database 11g release2发布
How to check and disable Adaptive Cursor Sharing in 11g
How does SGA/PGA allocate on 11g AMM?
crsctl status resource -t -init in 11.2.0.2 grid infrastructure
Learning 11g New Background Processes

Filed Under: Oracle, Oracle 11g新特性 Tagged With: ,

沪ICP备14014813号-2

沪公网安备 31010802001379号