在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数

发表于 2015 年 5 月 27 日 由 惜分飞

在12C RAC的in memory测试中由于未正确启用并行,导致测试结果flush buffer cache后,总是出现大量并行，通过ORACLE 各方努力最终确定是由于parallel_degree_policy=AUTO和parallel_force_local=false开始未设置正确导致.在rac中需要imdb的朋友请注意这两个参数.

设置表存放中inmemory

 
SQL> alter table CHF.XIFENFEI_888 inmemory;

Table altered.

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 |  2566   (8)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
        213  recursive calls                      
          0  db block gets 
     435058  consistent gets                      
         40  physical reads
      61180  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          5  sorts (memory)
          0  sorts (disk)  
          1  rows processed

SQL> set autot off
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

no rows selected

SQL>  select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA            469827584   3571449856          2853101568 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA            332267520   3571449856          3040182272 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID 
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0

这里可以看到表加载到inmemory需要时间,不是全表扫描一遍之后里面全表载入到in memory中.

查看执行计划确实走inmemory

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
          6  recursive calls                      
          0  db block gets 
     177415  consistent gets                      
          0  physical reads
      23484  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)
          0  sorts (disk)  
          1  rows processed

flush buffer cache后,inmemory执行计划中出现大量物理读

SQL> set autot off
SQL> alter system flush buffer_cache;

System altered.

SQL> /

System altered.

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID 
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------ 
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0 

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
          6  recursive calls                      
          0  db block gets 
     177413  consistent gets                      
     176358  physical reads
      23456  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)
          0  sorts (disk)  
          1  rows processed

SQL> set autot off

再次查询物理读消失

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;

  COUNT(*)          
----------          
  16883988          


Execution Plan
----------------------------------------------------------                      
Plan hash value: 1642441725
                    
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
                    
Note                
-----               
   - automatic DOP: Computed Degree of Parallelism is 2  
   - parallel scans affinitized for inmemory      


Statistics
----------------------------------------------------------                      
          6  recursive calls                      
          0  db block gets 
     177414  consistent gets                      
          0  physical reads
      23448  redo size     
        545  bytes sent via SQL*Net to client     
        552  bytes received via SQL*Net from client      
          2  SQL*Net roundtrips to/from client    
          0  sorts (memory)
          0  sorts (disk)  
          1  rows processed

SQL> set autot off

这里有奇怪点,启用inmemory之后,flush buffer cache后,物理读非常大(基本上和逻辑读一样),不符合常理,因为inmemory和buffer cache是两个独立的东西,就算是flush buffer cache,也不应该导致in memory内的东西失效(而且从v$im_segments中查询是正常的),对于该问题百思不得其解,最后只好寻求inmemory邮件组和GCS帮忙.最终是由于并行相关参数配置导致该问题

SQL> alter system set parallel_force_local=false sid='*';

System altered.

SQL> alter system set parallel_degree_policy=AUTO sid='*'; 

System altered.

修改parallel_force_local和parallel_degree_policy后继续测试

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888'; 

INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE RPT_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE RPT_DATA           1069481984   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0


SQL> set autot on 
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t; 
set autot off 


  COUNT(*)
----------
  16883988


Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        776  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> alter system flush buffer_cache
  2  ;

System altered.

SQL> /

System altered.

SQL> 

SQL> set autot on 
select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t; 
set autot off 
SQL> 

  COUNT(*)
----------
  16883988


Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        776  consistent gets
          2  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> spool off

通过测试证明,在RAC环境中,如果要使用IN MEMORY特性,需要设置parallel_degree_policy=AUTO和parallel_force_local=false之后,才能够真正意义上的启动IM特性,不然只是执行计划中的启用,可能是假象

发表在 ORACLE 12C | 标签为 , , , | 评论关闭

通过with实现对表非法dml操作—解决方案_with_subquery=materialize或者psu(2014.07以后)

发表于 2015 年 5 月 25 日 由 惜分飞

最近网上流传的通过with绕过权限实现非法更新表数据,存在较大风险.对于cpu bug在2014年07月份psu中修复,建议升级对应psu,如果条件不允许,可以通过_with_subquery参数临时规避该风险
数据库版本信息

SQL> select * from v$version;

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

[oracle@localhost ~]$ opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2015-05-25_20-38-37PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/
cfgtoollogs/opatch/lsinv/lsinventory2015-05-25_20-38-37PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1): 

Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.

该数据库版本为11.2.0.4,未安装任何psu补丁

根据恩墨的测试重新bug信息
可以参考原link:Oracle数据库高危漏洞警告！

SQL> conn chf/xifenfei
Connected.
SQL> create table t_dml as select * from dba_users;

Table created.

SQL> create user xifenfei_dml identified by "www.xifenfei.com";

User created.

SQL> grant create session to xifenfei_dml;

Grant succeeded.

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> 

SQL> grant select on chf.t_dml to xifenfei_dml;

Grant succeeded.

SQL> conn xifenfei_dml/"www.xifenfei.com"
Connected.

SQL>  select count(*) from chf.t_dml;

  COUNT(*)
----------
        32

SQL>  select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
SYSTEM                                  5

SQL> update chf.t_dml set username='www.xifenfei.com' where user_id = 5;
update chf.t_dml set username='www.xifenfei.com' where user_id = 5
           *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;

1 row updated.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where rownum <= 2;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0
www.xifenfei.com                        5

SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select username,user_id from chf.t_dml where user_id=5;

no rows selected

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from chf.t_dml;

  COUNT(*)
----------
        40

这里确实证明了,在没有dml情况下,可以通过with方式实现dml操作,从而实现无更改记录用户实现dml操作,数据库存在安全隐患,通过查询mos等相关信息,确定该bug影响数据库11.2.0.3，11.2.0.4,12.1.0.1等常见版本

对于不能及时升级的用户使用_with_subquery参数临时规避该bug
这个隐含参数的含义是在用with子句查询的时候，将 查询结果物化成temp表，（其实这也是我们常用with子句的目的，物化、缓存结果集）

SQL> conn / as sysdba
Connected.
SQL> col name for a52
col value for a24
SQL> SQL> col description for a50
set linesize 150
SQL> SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
 where a.inst_id = USERENV ('Instance')
  3    4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
/  8  
Enter value for param: _WITH_SUBQUERY
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_WITH_SUBQUERY%')

NAME                                                 VALUE                    DESCRIPTION
---------------------------------------------------- ------------------------ ------------------------------
_with_subquery                                       OPTIMIZER                WITH subquery transformation



SQL> alter system  set "_with_subquery"=materialize; 

System altered.
                             

SQL> alter system  set "_with_subquery"=materialize; 

System altered.

SQL> insert into  (with tmp as (select * from chf.t_dml)
  2   select * from tmp) select * from chf.t_dml where rownum<10;
insert into  (with tmp as (select * from chf.t_dml)
             *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> delete (with tmp as (select user_id,username from chf.t_dml)
  2   select user_id,username from tmp)  where user_id=5;
delete (with tmp as (select user_id,username from chf.t_dml)
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> update(with tmp as (select user_id,username from chf.t_dml)
  2  select user_id,username from tmp) set username='www.xifenfei.com' where user_id=5;
update(with tmp as (select user_id,username from chf.t_dml)
      *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

该漏洞在2014年7月的CPU中被修正,以下psu中包含了该cpu补丁,如果条件允许,建议尽快升级如下版本

Version 12.1.0.1.4 or later
Version 11.2.0.4.3 or later
Version 11.2.0.3.11 or later
Version 11.1.0.7.20 or later
发表在 Oracle, Oracle Bug | 标签为 | 评论关闭

init.cssd startcheck—HP Service Guard未启动导致CRS无法正常启动

发表于 2015 年 5 月 25 日 由 惜分飞

早上到客户现场,客户告知有一套环境替换OCR和VOTEDISK之后,crs无法启动,让我看看。环境:HP RAC(只用一个节点)+10.2.0.5 Oracle 数据库
start crs显示正常,但是无法启动

# /app/oracle/product/10.2.0/crs/bin/crsctl start crs
Attempting to start CRS stack 
The CRS stack will be started shortly

# ps -ef|grep crs
    root  6461     1  0  May 19  ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 29719 23678  0 10:04:51 pts/tc    0:00 grep crs

也无任何日志

[xifenfei01][orawj][/root/xifenfei]#ls -ltr
total 148
drwxr-x---   2 oracle     dba             96 May 15  2014 admin
drwxr-x---   2 root       dba             96 May 15  2014 crsd
drwxr-x---   2 oracle     dba             96 May 15  2014 evmd
drwxrwxr-t   5 oracle     dba           1024 Jun  4  2014 racg
drwxr-x---   5 oracle     dba           1024 May 17 22:50 cssd
-rw-rw-r--   1 root       dba          61568 May 24 15:26 alertxifenfei01.log
drwxr-x---   2 oracle     dba           3072 May 24 15:43 client
[xifenfei01][orawj][/root/xifenfei]#date
Mon, May 25, 2015 11:30:09 AM

表决磁盘和OCR信息

[xifenfei01][orawj][/root/xifenfei]#ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :    1441492
         Used space (kbytes)      :       5972
         Available space (kbytes) :    1435520
         ID                       : 1714667730
         Device/File Name         : /dev/vgc01/rCMPR_VGC01_OCR1
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/vgc02/rCMPR_VGC02_OCR2
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

[xifenfei01][orawj][/root/xifenfei]#crsctl query css votedisk
 0.     0    /dev/vgc01/rCMPR_VGC01_VOTE1
 1.     0    /dev/vgc02/rCMPR_VGC02_VOTE2
 2.     0    /dev/vgc03/rCMPR_VGC03_VOTE3

located 3 votedisk(s).

ocr.loc文件路径

# more /var/opt/oracle/ocr.loc
#Device/file /dev/vgc02/rCMPR_VGC02_OCR2 getting replaced by device /dev/vgc02/rCMPR_VGC02_OCR2 
ocrconfig_loc=/dev/vgc01/rCMPR_VGC01_OCR1
ocrmirrorconfig_loc=/dev/vgc02/rCMPR_VGC02_OCR2
local_only=false

这里可以看出来表决磁盘和ocr等相关信息正常

显示init.cssd startcheck进程

[xifenfei01][orawj][/root/xifenfei]#ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 26820 26792  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 26791     1  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 27183 23698  0 10:50:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 26792     1  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal
    root 23698     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 26816 26791  0 10:49:53 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
  oracle 20534 11033  0 11:30:35 pts/ta    0:00 grep init

这里的init.cssd startcheck大部分情况下，是由于不能访问存储或者第三方集群件无法访问导致

查看vg状态

VG Name                     /dev/vgc01
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      9      
Open LV                     9      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    736     
Free PE                     2463    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000        

VG Name                     /dev/vgc02
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      9      
Open LV                     9      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    736     
Free PE                     2463    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000        

VG Name                     /dev/vgc03
VG Write Access             read/write     
VG Status                   available                 
Max LV                      255    
Cur LV                      6      
Open LV                     6      
Max PV                      255    
Cur PV                      1      
Act PV                      1      
Max PE per PV               3200         
VGDA                        2   
PE Size (Mbytes)            32              
Total PE                    3199    
Alloc PE                    448     
Free PE                     2751    
Total PVG                   0        
Total Spare PVs             0              
Total Spare PVs in use      0                     
VG Version                  1.0       
VG Max Size                 25500g     
VG Max Extents              816000

这里可以看到，三个存放表决磁盘和ocr的vg都是available的

看votedisk和ocr权限

# ls -l /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
crw-r-----   1 oracle     dba         64 0x020008 May 24 14:40 /dev/vgc01/rCMPR_VGC01_OCR1
crw-r-----   1 oracle     dba         64 0x020009 May 24 14:41 /dev/vgc01/rCMPR_VGC01_VOTE1
crw-r-----   1 oracle     dba         64 0x030008 May 24 14:41 /dev/vgc02/rCMPR_VGC02_OCR2
crw-r-----   1 oracle     dba         64 0x030009 May 24 14:41 /dev/vgc02/rCMPR_VGC02_VOTE2
crw-r-----   1 oracle     dba         64 0x040006 May 24 14:41 /dev/vgc03/rCMPR_VGC03_VOTE3

直接修改权限为777,然后尝试

# chmod 777 /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
#  ls -l /dev/vgc0*/rCMPR*|grep -v .dbf|grep -v .log|grep -v .ctl
crwxrwxrwx   1 oracle     dba         64 0x020008 May 24 14:40 /dev/vgc01/rCMPR_VGC01_OCR1
crwxrwxrwx   1 oracle     dba         64 0x020009 May 24 14:41 /dev/vgc01/rCMPR_VGC01_VOTE1
crwxrwxrwx   1 oracle     dba         64 0x030008 May 24 14:41 /dev/vgc02/rCMPR_VGC02_OCR2
crwxrwxrwx   1 oracle     dba         64 0x030009 May 24 14:41 /dev/vgc02/rCMPR_VGC02_VOTE2
crwxrwxrwx   1 oracle     dba         64 0x040006 May 24 14:41 /dev/vgc03/rCMPR_VGC03_VOTE3

kill相关进程重试

# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root  6458     1  0  May 19  ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 20975     1  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 20976     1  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal
    root 21006 20976  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 20997 20975  0 10:40:11 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 21152 23678  0 10:40:18 pts/tc    0:00 grep init

vi /etc/inittab
#h1:3:respawn:/sbin/init.d/init.evmd run >/dev/null 2>&1 </dev/null
#h2:3:respawn:/sbin/init.d/init.cssd fatal >/dev/null 2>&1 </dev/null
#h3:3:respawn:/sbin/init.d/init.crsd run >/dev/null 2>&1 </dev/null

# /sbin/init q
# ps -ef|grep init.c | grep -v grep | awk '{print $2}' |xargs kill -9 
# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 21744 23678  1 10:42:31 pts/tc    0:00 grep init

重新启动init进程

vi /etc/inittab
h1:3:respawn:/sbin/init.d/init.evmd run >/dev/null 2>&1 </dev/null 
h2:3:respawn:/sbin/init.d/init.cssd fatal >/dev/null 2>&1 </dev/null 
h3:3:respawn:/sbin/init.d/init.crsd run >/dev/null 2>&1 </dev/null 
~
# /sbin/init q
# ps -ef|grep init
    root     1     0  0  May 19  ?         0:03 init
    root   119     0  0  May 19  ?         0:00 pagetable_init_daemon
    root   115     0  0  May 19  ?         0:00 mdep_initiator_thread
    root 23737 23706  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23731 23698  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23706     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.crsd run
    root 23698     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.evmd run
    root 23887 23678  1 10:45:28 pts/tc    0:00 grep init
    root 23746 23700  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd startcheck
    root 23700     1  0 10:45:23 ?         0:00 /bin/sh /sbin/init.d/init.cssd fatal

证明修改lv权限,问题依旧,不是votedisk和ocr的权限和所有者导致,通过dd和strings读相关文件，发现都OK.

调试/sbin/init.d/init.cssd startcheck进程

[xifenfei01][orawj][/root/xifenfei]#sh -x  /sbin/init.d/init.cssd startcheck
+ ORA_CRS_HOME=/app/oracle/product/10.2.0/crs
+ ORACLE_USER=oracle
+ ORACLE_HOME=/app/oracle/product/10.2.0/crs
+ export ORACLE_HOME
+ export ORA_CRS_HOME
+ export ORACLE_USER
+ DISABLE_OPROCD=false
+ OPROCD_DEFAULT_TIMEOUT=1000
+ OPROCD_DEFAULT_MARGIN=500
+ OPROCD_CHECK_TIMEOUT=2000
+ OPROCD_STOP_TIMEOUT=2000
+ OPROCD_DEFAULT_HISTORGRAM=
+ HOSTN=/bin/hostname
+ EXPRN=/usr/bin/expr
+ CUT=/usr/bin/cut
+ AWK=/bin/awk
+ ECHO=echo
+ TR=/bin/tr
+ /bin/uname
+ [ SunOS = HP-UX ]
+ /bin/uname
+ [ Linux = HP-UX ]
+ + /bin/hostname
HOST=xifenfei01
+ + /usr/bin/expr xifenfei01 : .*
len1=8
+ + /usr/bin/expr match xifenfei01 [0-9]*\.[0-9]*\.[0-9]*\.[0-9]*
len2=0
+ [ 8 != 0 ]
+ + echo xifenfei01
+ /usr/bin/cut -d. -f1
HOST=xifenfei01
+ + echo xifenfei01
+ /bin/tr [:upper:] [:lower:]
HOST=xifenfei01
+ PS=/bin/ps
+ PSE=/bin/ps -e
+ PSEF=/bin/ps -ef
+ HEAD=/bin/head
+ GREP=/bin/grep
+ KILL=/bin/kill
+ KILLTERM=/bin/kill -TERM
+ KILLDIE=/bin/kill -9
+ KILLCHECK=/bin/kill -0 5852
+ SLEEP=/bin/sleep
+ NULL=/dev/null
+ UNAME=/bin/uname
+ CAT=/bin/cat
………………
+ eval /bin/true
+ /bin/true
+ [ 0 != 0 ]
+ eval /bin/ps -ef | /bin/grep '/usr/lbin/cm[g]msd' 1>/dev/null 2>/dev/null
+ /bin/grep /usr/lbin/cm[g]msd
+ /bin/ps -ef
+ 1> /dev/null 2> /dev/null
+ RC=1
+ [ 1 -ne 0 ]
+ /bin/logger -puser.err Oracle Cluster Ready Services waiting for HP-UX Service Guard to start.
+ /bin/sleep 60

这里可以通过-x调试shell脚本，发现crs在等待HP-UX Service Guard启动,从而可以确定是由于HP-UX Service Guard未启动

检查HP-UX Service Guard是否启动

[xifenfei01][orawj][/root/xifenfei]#cmviewcl 

CLUSTER           STATUS       
crmdb_b_cluster   down         
  
  NODE           STATUS       STATE        
  xifenfei01       down         unknown      
  crmdbb02       down         unknown      
    
UNOWNED_PACKAGES

    PACKAGE        STATUS           STATE            AUTO_RUN    NODE        
    pkg1           down             halted           enabled     unowned     
    pkg2           down             halted           enabled     unowned

通过这里,结合客户描述(只启动了一个节点，另外一个节点的vg未激活)，可以判断出来由于只使用一个节点,在未启动Service Guard的情况下,直接激活vg,由于Service Guard未启动导致crs无法启动

发表在 Oracle RAC | 标签为 , | 评论关闭