Database SOS – 第80页 – Professional Database Recovery Support@+8617813235971

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

在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特性,不然只是执行计划中的启用,可能是假象

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

最近网上流传的通过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

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

早上到客户现场,客户告知有一套环境替换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 12C TABLE ACCESS BY INDEX ROWID BATCHED

从Oracle 12C开始执行计划中可能会出现TABLE ACCESS BY INDEX ROWID BATCHED,官方的解释：TABLE ACCESS BY INDEX ROWID BATCHED：means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.主要意思:对于一个块中多个rowid,通过批量减少访问快的次数.而作为12.1的新特性,数据库是通过_optimizer_batch_table_access_by_rowid来控制的

数据库版本12.1

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production                         0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

TABLE ACCESS BY INDEX ROWID BATCHED执行计划

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;
PL/SQL procedure successfully completed.
SQL> SELECT BRAND_ID FROM T_USERTYPE_FULLNO WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 942613467
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  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

_optimizer_batch_table_access_by_rowid参数为true

SQL> col name for a32
SQL> col value for a24
col description for a70
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
SQL> SQL> SQL>   2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  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: _optimizer_batch_table_access_by_rowid
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by_rowid%')
NAME                                 VALUE                    DESCRIPTION
------------------------------------- ------------------------ ----------------------------------------------
_optimizer_batch_table_access_by_rowid TRUE                     enable table access by ROWID IO batching

设置_optimizer_batch_table_access_by_rowid为false,执行计划由TABLE ACCESS BY INDEX ROWID BATCHED变为TABLE ACCESS BY INDEX ROWID

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;
PL/SQL procedure successfully completed.
SQL> alter session set "_optimizer_batch_table_access_by_rowid"=false;
Session altered.
SQL> SELECT BRAND_ID FROM XIFENFEI WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2797551150
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  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

optimizer_features_enable修改为11.2之后,_optimizer_batch_table_access_by_rowid会联锁变为fasle

SQL> alter session set optimizer_features_enable = '11.2.0.3';
Session altered.
SQL> col name for a52
col value for a24
col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_idSQL> SQL> SQL> SQL>   2    3   = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
order by name
  4    5    6    7    8  /
Enter value for param: _optimizer_batch_table_access_by
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by%')
NAME                                       VALUE          DESCRIPTION
------------------------------------------ -------------- -----------------------------------------
_optimizer_batch_table_access_by_rowid     FALSE          enable table access by ROWID IO batching

这里可以看出来,在调整optimizer_features_enable参数后,会直接影响某些数据库相关的优化器参数,例如:_optimizer_batch_table_access_by_rowid

Oracle异常恢复前备份保护现场建议—ASM环境

在上一篇中写道了文件系统的库,在进行异常恢复前的备份方法（Oracle异常恢复前备份保护现场建议—FileSystem环境）,对于asm库,因为asm 里面的数据文件无法直接dd文件头,因此备份方式也有所改变.对于asm是mount,但是数据库不能打开,使用rman或者asm的cp命令全部备份数据文件也来不及或者空间不足,这样的情况下,你可以考虑使用rman或者cp命令备份控制文件和system表空间文件,cp命令备份redo,dd命令备份文件头,来完成asm情况下数据库异常恢复前备份

控制文件备份
11.2及其以后版本使用asmcmd cp命令处理

select 'asmcmd cp '||name||' &&backup_dir/' from v$datafile where ts#=0
union all
select 'asmcmd cp '||name||' &&backup_dir/crontrofile_'||rownum||'.ctl' from v$controlfile
union all
select 'asmcmd cp '||member||' &&backup_dir/'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'/',-1)+1)  FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

其他版本使用rman命令处理

--rman备份控制文件(/tmp目录自己修改)
copy current controlfile to '/tmp/ctl.ctl';
--rman备份system表空间
select 'copy datafile '||file#||' to ''&backup_dir/system_'||file#||'.dbf'';'
from v$datafile where ts#=0;
--redo无法直接备份

备份文件头

[grid@xifenfei ~]$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 1 04:15:18 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL> set lines 150
SQL> select 'dd if='||c.PATH_KFDSK||' of=&&backup_path/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||'_'||
  2  b.NUMBER_KFFIL||'.asm count=1 bs='|| d.AUSIZE_KFGRP||' skip='||a.au_kffxp backup_dd_cmd
  3   FROM x$kffxp a, X$KFFIL  b,X$KFDSK c,X$KFGRP d  WHERE
  4  a.GROUP_KFFXP=b.GROUP_KFFIL
  5  and a.NUMBER_KFFXP=b.NUMBER_KFFIL
  6  and b.FTYPE_KFFIL in(2,12)
  7  and b.NUMBER_KFFIL>255
  8  and a.xnum_kffxp=0
  9  and a.GROUP_KFFXP=c.GRPNUM_KFDSK
 10  and a.disk_kffxp=c.NUMBER_KFDSK
 11  and a.GROUP_KFFXP=d.NUMBER_KFGRP;
Enter value for backup_path: /tmp
old   1: select 'dd if='||c.PATH_KFDSK||' of=&&backup_path/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||'_'||
new   1: select 'dd if='||c.PATH_KFDSK||' of=/tmp/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||'_'||
BACKUP_DD_CMD
------------------------------------------------------------------------------------------------------------------
dd if=/dev/asm-disk1 of=/tmp/1_0_256.asm count=1 bs=1048576 skip=29
dd if=/dev/asm-disk2 of=/tmp/1_1_257.asm count=1 bs=1048576 skip=404
dd if=/dev/asm-disk2 of=/tmp/1_1_258.asm count=1 bs=1048576 skip=641
dd if=/dev/asm-disk1 of=/tmp/1_0_259.asm count=1 bs=1048576 skip=648
dd if=/dev/asm-disk3 of=/tmp/2_0_256.asm count=1 bs=1048576 skip=51

还原文件头

SQL> set lines 150
SQL> select 'dd of='||c.PATH_KFDSK||' if=&&backup_path/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||
  2  '_'||b.NUMBER_KFFIL||'.asm count=1 conv=notrunc bs='|| d.AUSIZE_KFGRP||' seek='||a.au_kffxp restore_dd_cmd
  3   FROM x$kffxp a, X$KFFIL  b,X$KFDSK c,X$KFGRP d  WHERE
  4  a.GROUP_KFFXP=b.GROUP_KFFIL
  5  and a.NUMBER_KFFXP=b.NUMBER_KFFIL
  6  and b.FTYPE_KFFIL in(2,12)
  7  and b.NUMBER_KFFIL>255
  8  and a.xnum_kffxp=0
  9  and a.GROUP_KFFXP=c.GRPNUM_KFDSK
 10  and a.disk_kffxp=c.NUMBER_KFDSK
 11  and a.GROUP_KFFXP=d.NUMBER_KFGRP;
old   1: select 'dd of='||c.PATH_KFDSK||' if=&&backup_path/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||
new   1: select 'dd of='||c.PATH_KFDSK||' if=/tmp/'||a.GROUP_KFFXP||'_'||a.disk_kffxp||
RESTORE_DD_CMD
-----------------------------------------------------------------------------------------------------------------
dd of=/dev/asm-disk1 if=/tmp/1_0_256.asm count=1 conv=notrunc bs=1048576 seek=29
dd of=/dev/asm-disk2 if=/tmp/1_1_257.asm count=1 conv=notrunc bs=1048576 seek=404
dd of=/dev/asm-disk2 if=/tmp/1_1_258.asm count=1 conv=notrunc bs=1048576 seek=641
dd of=/dev/asm-disk1 if=/tmp/1_0_259.asm count=1 conv=notrunc bs=1048576 seek=648
dd of=/dev/asm-disk3 if=/tmp/2_0_256.asm count=1 conv=notrunc bs=1048576 seek=51
SQL>

备份还原文件头测试–通过测试证明该方法备份文件头是ok的
关闭数据库,使用dd备份文件头

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 1 04:21:49 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

dul查看当前dbname值为XIFENFEI

[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.6.5 - Internal Only - on Fri May  1 04:37:43 2015
with 64-bit io functions
Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
Disk group DATA, dul group_cid 0
Discovered disk /dev/asm-disk1 as diskgroup DATA, disk number 0 size 3922 Mb File1 starts at 2, dul_disk_cid 0
Discovered disk /dev/asm-disk2 as diskgroup DATA, disk number 1 size 3922 Mb without File1 meta data, dul_disk_cid 1
Disk group XIFENFEI, dul group_cid 1
Discovered disk /dev/asm-disk3 as diskgroup XIFENFEI, disk number 0 size 4439 Mb File1 starts at 2, dul_disk_cid 2
DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty
Probing for attributes in File9, the attribute directory, for disk group DATA
attribute name "_extent_sizes", value "1 4 16"
attribute name "_extent_counts", value "20000 20000 2147483647"
Oracle data file size 775954432 bytes, block size 8192
Found db_id = 1495013434
Found db_name = XIFENFEI   <-----db name
DUL: Error: Filedir block not allocated, file does not exist
DUL: Error: Could not load asm meta data for group XIFENFEI file 9
Probing for filenames in File6, the alias directory, for disk group XIFENFEI
+XIFENFEI/XIFENFEI/DATAFILE/XIFENFEI.256.878397315
Probing for database datafiles in File1, the file directory,  for disk group XIFENFEI
File 256 datafile size 104865792, block size 8192
Disk group XIFENFEI has one file of type datafile

使用dd备份1文件头

[oracle@xifenfei tmp]$ dd if=/dev/asm-disk1 of=/tmp/1_0_256.asm count=1 bs=1048576 skip=29
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0168209 seconds, 62.3 MB/s

尝试把dbname从XIFENFEI修改为ORCL

SQL> select dump('XIFENFEI',16) from dual;
DUMP('XIFENFEI',16)
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
SQL> SELECT DUMP('ORCL',16) FROM DUAL;
DUMP('ORCL',16)
-------------------------
Typ=96 Len=4: 4f,52,43,4c
SQL>

bbed修改XIFENFEI为ORCL

[oracle@xifenfei tmp]$ bbed filename='/tmp/1_0_256.asm' mode=edit
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri May 1 04:24:06 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 1
        BLOCK#          1
BBED> map
 File: /tmp/1_0_256.asm (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header
 struct kcvfh, 860 bytes                    @0
 ub4 tailchk                                @8188
BBED> p kcvfhhdr
struct kcvfhhdr, 76 bytes                   @20
   ub4 kccfhswv                             @20       0x00000000
   ub4 kccfhcvn                             @24       0x0b200400
   ub4 kccfhdbi                             @28       0x591c183a
   text kccfhdbn[0]                         @32      X
   text kccfhdbn[1]                         @33      I
   text kccfhdbn[2]                         @34      F
   text kccfhdbn[3]                         @35      E
   text kccfhdbn[4]                         @36      N
   text kccfhdbn[5]                         @37      F
   text kccfhdbn[6]                         @38      E
   text kccfhdbn[7]                         @39      I
BBED> d seek 32
 File: /tmp/1_0_256.asm (0)
 Block: 1                seeks:   32 to   63           Dba:0x00000000
------------------------------------------------------------------------
 58494645 4e464549 12040000 00720100 00200000 01000300 00000000 00000000
 <32 bytes per line>

dd把修改的block还原到asm中

[oracle@xifenfei dul]$ dd of=/dev/asm-disk1 if=/tmp/1_0_256.asm count=1 conv=notrunc bs=1048576 seek=29
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.00253244 seconds, 414 MB/s

dul验证dbname 修改为ORCL成功

[oracle@xifenfei dul]$ ./dul
Data UnLoader: 10.2.0.6.5 - Internal Only - on Fri May  1 04:41:33 2015
with 64-bit io functions
Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
Disk group DATA, dul group_cid 0
Discovered disk /dev/asm-disk1 as diskgroup DATA, disk number 0 size 3922 Mb File1 starts at 2, dul_disk_cid 0
Discovered disk /dev/asm-disk2 as diskgroup DATA, disk number 1 size 3922 Mb without File1 meta data, dul_disk_cid 1
Disk group XIFENFEI, dul group_cid 1
Discovered disk /dev/asm-disk3 as diskgroup XIFENFEI, disk number 0 size 4439 Mb File1 starts at 2, dul_disk_cid 2
DUL: Warning: Dictionary cache DC_ASM_EXTENTS is empty
Probing for attributes in File9, the attribute directory, for disk group DATA
attribute name "_extent_sizes", value "1 4 16"
attribute name "_extent_counts", value "20000 20000 2147483647"
Oracle data file size 775954432 bytes, block size 8192
Found db_id = 1495013434
Found db_name = ORCL   <----修改后的dbname
DUL: Error: Filedir block not allocated, file does not exist
DUL: Error: Could not load asm meta data for group XIFENFEI file 9
Probing for filenames in File6, the alias directory, for disk group XIFENFEI
+XIFENFEI/XIFENFEI/DATAFILE/XIFENFEI.256.878397315
Probing for database datafiles in File1, the file directory,  for disk group XIFENFEI
File 256 datafile size 104865792, block size 8192
Disk group XIFENFEI has one file of type datafile

对于asm无法mount情况下备份asm disk header
asm磁盘的备份主要是备份磁盘头100M空间,使用dd命令直接备份

set lines 150
set pages 1000
select 'dd if='||path||' of=&asmbackup_dir/'||group_number||'_'||disk_number||'.asm bs=1048576
count=100' from v$asm_disk;

set lines 150
set pages 1000
select 'dd of='||path||' if=&asmbackup_dir/'||group_number||'_'||disk_number||'.asm bs=1048576
count=100 conv=notrunc' from v$asm_disk;

asmlib需要注意把ORCL:替换为/dev/oracleasm/disks/对应目录.

Oracle异常恢复前备份保护现场建议—FileSystem环境

无论是在各种会议上,还是在朋友/网友私下请教Oracle数据库恢复的问题之时,我都强调,如果你没有十足的把握,请你对您的现场进行备份,确保别对现场进行二次损坏。你不能恢复数据库,但绝对不能再次破坏数据库,给二次恢复增加难度.这里对恢复前备份提供一些指导思想和简单脚本,希望对大家有帮助.

哪些文件需要备份
熟悉数据库恢复的朋友可能都情况,Oracle在异常恢复的过程中主要修改的是system表空间里面数据,其他数据文件,redo数据,控制文件(当然由于redo,undo导致其他数据文件内部的block也可能发生改变)。在备份时间,备份空间允许的情况下,是对这些文件全部备份为好

完整备份文件

set lines 150
set pages 10000
select name from v$datafile
union all
select name from v$controlfile
union all
select member from v$logfile;

有些情况下:比如如果全部备份时间过长,备份空间不足等情况下,我们该如何备份,尽量减少因为异常恢复导致对原环境的损坏.备份最核心的system表空间,数据文件头,redo file,control file等数据,由于这个不是简单的拷贝操作,因此在生成备份语句同时,也生成还原语句,切不可生成了备份语句后,无恢复语句,导致后面还原故障现场难度增大.

无法全备情况下linux/unix数据库恢复前备份

set lines 150
set pages 10000
select 'dd if='||name||' of=&&back_dir/'||ts#||'_'||file#||'.dbf bs=1048576 count=10'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' of=&&back_dir/'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd if='||name||' of=&&back_dir/control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd if='||member||' of=&&back_dir/'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'/',-1)+1)  FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

无法全备情况下linux/unix使用备份还原

set lines 150
set pages 1000
select 'dd of='||name||' if=&&back_dir/'||ts#||'_'||file#||'.dbf bs=1048576 count=10 conv=notrunc'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' if=&&back_dir/'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd of='||name||' if=&&back_dir/control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd of='||member||' if=&&back_dir/'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'/',-1)+1)    FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

由于win路径斜杠不一样(/和\的区别),因此在无法全备情况下win备份语句

set lines 150
set pages 10000
select 'dd if='||name||' of=&&back_dir\'||ts#||'_'||file#||'.dbf bs=1048576 count=10'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' of=&&back_dir\'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd if='||name||' of=&&back_dir\control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd if='||member||' of=&&back_dir\'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'\',-1)+1)   FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

在无法全备情况下win还原语句

set lines 150
set pages 1000
select 'dd of='||name||' if=&&back_dir\'||ts#||'_'||file#||'.dbf bs=1048576 count=10 conv=notrunc'
from v$datafile where ts#<>0
union all
select 'dd if='||name||' if=&&back_dir\'||ts#||'_'||file#||'.dbf' from v$datafile where ts#=0
union all
select 'dd of='||name||' if=&&back_dir\control0'||rownum||'.ctl' from v$controlfile
union all
select 'dd of='||member||' if=&&back_dir\'||thread#||'_'||a.group#||'_'||sequence#||'_'||substr(member,
instr(member,'\',-1)+1)    FROM v$log a, v$logfile b WHERE a.group# = B.GROUP#;

这里提供win环境下dd命令程序win环境dd命令工具

备注:对于asm情况异常情况恢复,备份情况请不要参考该文章,具体请见后续文章,具体见Oracle异常恢复前备份保护现场建议—ASM环境

kfed恢复误删除磁盘组

在某些情况下,可能因为误操作,不小先drop diskgroup,这个时候千万别紧张,出现此类故障,可以通过kfed进行完美恢复(数据0丢失).如果进一步损坏了相关asm disk,那后续恢复就很麻烦了,可能需要使用dul扫描磁盘来进行抢救性恢复,而且可能导致数据丢失.
创建测试磁盘组xifenfei

[grid@xifenfei ~]$ sqlplus / as sysasm
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 30 15:12:08 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL>  select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 CANDIDATE
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER
SQL> create diskgroup xifenfei external redundancy disk '/dev/asm-disk3';
Diskgroup created.
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
XIFENFEI_0000                  /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

使用/dev/asm-disk3这个磁盘创建磁盘组xifenfei

创建表,存储在xifenfei磁盘组中

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 30 15:14:55 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create tablespace xifenfei datafile '+xifenfei' size 100M;
Tablespace created.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/xifenfei/datafile/system.256.878224279
+DATA/xifenfei/datafile/sysaux.257.878224279
+DATA/xifenfei/datafile/undotbs1.258.878224279
+DATA/xifenfei/datafile/users.259.878224279
+XIFENFEI/xifenfei/datafile/xifenfei.256.878397315
SQL> create table t_xifenfei tablespace xifenfei
  2  as select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     86259

通过在磁盘组中创建表空间,从而实现表xifenfei存放在测试磁盘组中

尝试删除磁盘组xifenfei

SQL> drop diskgroup xifenfei;
drop diskgroup xifenfei
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "XIFENFEI" contains existing files
SQL> drop diskgroup xifenfei  including contents;
drop diskgroup xifenfei  including contents
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15027: active use of diskgroup "XIFENFEI" precludes its dismount
[grid@xifenfei ~]$ asmcmd
ASMCMD> lsof
DB_Name   Instance_Name  Path
xifenfei  xifenfei       +data/xifenfei/controlfile/current.260.878224379
xifenfei  xifenfei       +data/xifenfei/datafile/sysaux.257.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/system.256.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/undotbs1.258.878224279
xifenfei  xifenfei       +data/xifenfei/datafile/users.259.878224279
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_1.261.878224381
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_2.262.878224383
xifenfei  xifenfei       +data/xifenfei/onlinelog/group_3.263.878224385
xifenfei  xifenfei       +data/xifenfei/tempfile/temp.264.878224395
xifenfei  xifenfei       +xifenfei/xifenfei/datafile/xifenfei.256.878397315

由于xifenfei磁盘组被实例使用,因此磁盘组无法删除,报ORA-15027错误
由于xifenfei磁盘组中有文件,因此磁盘组无法删除,报ORA-15053错误
如果这两个阻止你误删除磁盘组的警告依然不能救你,那我也不好多说啥了,只能向我一样继续往下

关闭数据库实例,删除磁盘组

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> drop diskgroup xifenfei;
drop diskgroup xifenfei
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "XIFENFEI" contains existing files
SQL> drop diskgroup xifenfei  including contents;
Diskgroup dropped.
SQL>  select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 FORMER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER
SQL> alter diskgroup xifenfei mount;
alter diskgroup xifenfei mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "XIFENFEI" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"XIFENFEI"

磁盘组被drop之后,无法正常mount,mount之时报ORA-15063凑无

kfed恢复删除磁盘组

[grid@xifenfei ~]$ kfed read /dev/asm-disk3 >/tmp/disk3-0-0
[grid@xifenfei ~]$ kfed  read /dev/asm-disk3  blkn=1 >/tmp/disk3-0-1
[grid@xifenfei ~]$ kfed  read /dev/asm-disk3  aun=1 >/tmp/disk3-1-0
通过vi修改这些/tmp/disk3-*中的部分值
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3 text=/tmp/disk3-0-0
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3  blkn=1 text=/tmp/disk3-0-1
[grid@xifenfei ~]$ kfed merge /dev/asm-disk3 aun=1 text=/tmp/disk3-1-0

查询修复后的asm disk

SQL> col path for a30
SQL> set lines 150
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
                               /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

尝试mount xifenfei 磁盘组

SQL> alter diskgroup xifenfei mount;
Diskgroup altered.
SQL> select name,path,header_status from v$asm_disk;
NAME                           PATH                           HEADER_STATU
------------------------------ ------------------------------ ------------
XIFENFEI_0000                  /dev/asm-disk3                 MEMBER
DATA_0000                      /dev/asm-disk1                 MEMBER
DATA_0001                      /dev/asm-disk2                 MEMBER

测试恢复后磁盘组

SQL> startup
ORACLE instance started.
Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             306186224 bytes
Database Buffers          637534208 bytes
Redo Buffers                6041600 bytes
Database mounted.
Database opened.
SQL>  select count(*) from t_xifenfei;
  COUNT(*)
----------
     86259

这里证明,当磁盘组被误删除后,立即停止进一步损坏,可以通过kfed进行完美恢复
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

dbms_diskgroup拷贝block/datafile

对于asm而言,如果我们要操作里面的数据文件,虽然从11.2开始有asmcmd的cp可能进行拷贝,但是如果想对数据文件中的某个block进行拷贝出来asm到文件系统(或者拷贝某个文件系统的block到asm中),还是比较麻烦的事情(请见:bbed修改ASM中数据)。其实oracle官方提供了dbms_diskgroup这个包,可以通过sqlplus直接操作asm里面的block/datafile,非常方便,这里简单列举几个例子:

dbms_diskgroup获取asm中文件属性

SQL> declare
  2  v_filename varchar2(4000);
  3  v_filetype number;
  4  v_filesize number;
  5  v_lbks number;
  6  v_typename varchar2(4000);
  7  begin
  8  dbms_output.enable(5000);
  9  v_filename := '&file_name';
 10  dbms_diskgroup.getfileattr(v_filename,v_filetype,v_filesize,v_lbks);
 11  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 12  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 13  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 14  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 15  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 16  dbms_output.put_line('File: '||v_filename); dbms_output.new_line;
 17  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 18  dbms_output.put_line('Size (Logical Block Size): '||v_filesize); dbms_output.new_line;
 19  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 20  end;
 21  /
Enter value for file_name: +DATA/xifenfei/datafile/system.256.878224279
old   9: v_filename := '&file_name';
new   9: v_filename := '+DATA/xifenfei/datafile/system.256.878224279';
File: +DATA/xifenfei/datafile/system.256.878224279
Type: 12 Data File Copy
Size (Logical Block Size): 94720
Logical Block Size: 8192
PL/SQL procedure successfully completed.

创建测试表t_xifenfei

SQL> create table t_xifenfei tablespace users
  2  as select 'www.xifenfei.com' xifenfei from dual;
Table created.
SQL> select rowid,xifenfei from t_xifenfei;
ROWID              XIFENFEI
------------------ ----------------
AAAVU3AAEAAAACrAAA www.xifenfei.com
SQL> select
  2   dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid )block_no
  4  from t_xifenfei;
   REL_FNO   BLOCK_NO
---------- ----------
         4        171
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
+DATA/xifenfei/datafile/users.259.878224279

dbms_diskgroup拷贝asm datafile 4 block 171

SQL> declare
  2  v_AsmFilename varchar2(4000);
  3  v_FsFilename varchar2(4000);
  4  v_offstart number;
  5  v_numblks number;
  6  v_filetype number;
  7  v_filesize number;
  8  v_lbks number;
  9  v_typename varchar2(4000);
 10  v_pblksize number;
 11  v_handle number;
 12  begin
 13  dbms_output.enable(500000);
 14  v_AsmFilename := '&ASM_File_Name';
 15  v_offstart := '&block_to_extract';
 16  v_numblks := '&number_of_blocks_to_extract';
 17  v_FsFilename := '&FileSystem_File_Name';
 18  dbms_diskgroup.getfileattr(v_AsmFilename,v_filetype,v_filesize,v_lbks);
 19  dbms_diskgroup.open(v_AsmFilename,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);
 20  dbms_diskgroup.close(v_handle);
 21  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 22  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 23  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 24  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 25  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 26  dbms_output.put_line('File: '||v_AsmFilename); dbms_output.new_line;
 27  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 28  dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;
 29  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 30  dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;
 31  dbms_diskgroup.patchfile(v_AsmFilename,v_filetype,v_lbks,v_offstart,0,v_numblks,v_FsFilename,v_filetype,1,1);
 32  end;
 33  /
Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279
old  14: v_AsmFilename := '&ASM_File_Name';
new  14: v_AsmFilename := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for block_to_extract: 171
old  15: v_offstart := '&block_to_extract';
new  15: v_offstart := '171';
Enter value for number_of_blocks_to_extract: 1
old  16: v_numblks := '&number_of_blocks_to_extract';
new  16: v_numblks := '1';
Enter value for filesystem_file_name: /tmp/xifenfei.dbf
old  17: v_FsFilename := '&FileSystem_File_Name';
new  17: v_FsFilename := '/tmp/xifenfei.dbf';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size (in logical blocks): 640
Logical Block Size: 8192
Physical Block Size: 512
PL/SQL procedure successfully completed.
[grid@xifenfei ~]$ ls -l /tmp/xifenfei.dbf
-rw-r----- 1 grid oinstall 16384 Apr 28 15:55 /tmp/xifenfei.dbf

这里注意拷贝出来的block size 为8192,由于默认写了block 0信息，因此这里显示大小为2*block size=16384

bbed修改拷贝出来block内容

SQL> select dump('xifenfei',16) from dual;
DUMP('XIFENFEI',16)
-------------------------------------
Typ=96 Len=8: 78,69,66,65,6e,66,65,69
SQL> select dump('XIFENFEI',16) from dual;
DUMP('XIFENFEI',16)
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
[oracle@xifenfei tmp]$ bbed filename='/tmp/xifenfei.dbf' blocksize=8192
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Tue Apr 28 16:24:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        /tmp/xifenfei.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> map
 File: /tmp/xifenfei.dbf (0)
 Block: 1                                     Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 96 bytes                     @20
 struct kdbh, 14 bytes                      @124
 struct kdbt[1], 4 bytes                    @138
 sb2 kdbr[1]                                @142
 ub1 freespace[8024]                        @144
 ub1 rowdata[20]                            @8168
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8168     0x2c
BBED> d /v offset 8168
 File: /tmp/xifenfei.dbf (0)
 Block: 1       Offsets: 8168 to 8191  Dba:0x00000000
-------------------------------------------------------
 2c000110 7777772e 78696665 6e666569 l ,...www.xifenfei
 2e636f6d 020624bc                   l .com..$.
 <16 bytes per line>
BBED> r /x c
BBED-00200: invalid keyword (r)
BBED> x /rc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    1
col   0[16] @8171: www.xifenfei.com
BBED> set mode edit
        MODE            Edit
BBED> set offset 8171
        OFFSET          8171
BBED> set count 32
        COUNT           32
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8171 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 10777777 2e786966 656e6665 692e636f 6d020624 bc
 <32 bytes per line>
BBED>
BBED> set offset +5
        OFFSET          8176
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8176 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 78696665 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> m /x 58494645
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8176 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 58494645 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> set offset +4
        OFFSET          8180
BBED> d
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 6e666569 2e636f6d 020624bc
 <32 bytes per line>
BBED> m /x 4e464549
 File: /tmp/xifenfei.dbf (0)
 Block: 1                Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 4e464549 2e636f6d 020624bc
 <32 bytes per line>
BBED> d /v offset 8168
 File: /tmp/xifenfei.dbf (0)
 Block: 1       Offsets: 8168 to 8191  Dba:0x00000000
-------------------------------------------------------
 2c000110 7777772e 58494645 4e464549 l ,...www.XIFENFEI
 2e636f6d 020624bc                   l .com..$.
 <16 bytes per line>
BBED> x /rc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    1
col   0[16] @8171: www.XIFENFEI.com
BBED> sum apply
Check value for File 0, Block 1:
current = 0x3060, required = 0x3060

这里通过bbed把拷贝出来的datafile 4 block 171中的www.xifenfei.com修改为www.XIFENFEI.com

dbms_diskgroup拷贝os block to asm datafile 4 block 171

SQL> declare
  2  v_FsFileName varchar2(4000);
  3  v_AsmFileName varchar2(4000);
  4  v_FsFileType number;
  5  v_AsmFileType number;
  6  v_offstart number;
  7  v_filesize number;
  8  v_lbks number;
  9  v_typename varchar2(4000);
 10  v_handle number;
 11  error number;
 12  txt varchar2(4000);
 13  begin
 14  dbms_output.enable(500000);
 15  v_FsFileName := '&file_with_patched_block';
 16  v_AsmFileName := '&file_to_patch_in_ASM';
 17  v_offstart := '&block_to_patch';
 18  dbms_diskgroup.getfileattr(v_AsmFileName,v_AsmFileType,v_filesize,v_lbks);
 19  select decode(v_AsmFileType,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 20  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 21  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',
 22  17,'Flashback Log File',
 23  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 24  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 25  dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;
 26  dbms_output.put_line('Type: '||v_AsmFileType||' '||v_typename); dbms_output.new_line;
 27  dbms_output.put_line('Size: '||v_filesize); dbms_output.new_line;
 28  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 29  dbms_diskgroup.patchfile(v_FsFileName,12,v_lbks,1,0,1,v_AsmFileName,v_AsmFileType,v_offstart,0);
 30  end;
 31  /
Enter value for file_with_patched_block: /tmp/xifenfei.dbf
old  15: v_FsFileName := '&file_with_patched_block';
new  15: v_FsFileName := '/tmp/xifenfei.dbf';
Enter value for file_to_patch_in_asm: +DATA/xifenfei/datafile/users.259.878224279
old  16: v_AsmFileName := '&file_to_patch_in_ASM';
new  16: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for block_to_patch: 171
old  17: v_offstart := '&block_to_patch';
new  17: v_offstart := '171';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size: 640
Logical Block Size: 8192
PL/SQL procedure successfully completed.

验证修改block是否正确

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  952020992 bytes
Fixed Size                  2258960 bytes
Variable Size             306186224 bytes
Database Buffers          637534208 bytes
Redo Buffers                6041600 bytes
Database mounted.
Database opened.
SQL> select rowid,xifenfei from t_xifenfei;
ROWID              XIFENFEI
------------------ ----------------
AAAVU3AAEAAAACrAAA www.XIFENFEI.com

dbms_diskgroup拷贝asm datafile to os

SQL> declare
  2  v_AsmFileName varchar2(4000);
  3  v_FsFileName varchar2(4000);
  4  v_filetype number;
  5  v_filesize number;
  6  v_lbks number;
  7  v_typename varchar2(4000);
  8  v_pblksize number;
  9  v_handle number;
 10  begin
 11  dbms_output.enable(500000);
 12  v_AsmFileName := '&ASM_file_name';
 13  v_FsFileName := '&FileSystem_file_name';
 14  dbms_diskgroup.getfileattr(v_AsmFileName,v_filetype,v_filesize,v_lbks);
 15  dbms_diskgroup.open(v_AsmFileName,'r',v_filetype,v_lbks,v_handle,v_pblksize,v_filesize);
 16  dbms_diskgroup.close(v_handle);
 17  select decode(v_filetype,1,'Control File',2,'Data File',3,'Online Log File',4,'Archive Log',5,'Trace File',6,'Temporary File',
 18  7,'Not Used',8,'Not Used',9,'Backup Piece',10,'Incremental Backup Piece',11,'Archive Backup Piece',12,'Data File Copy',
 19  13,'Spfile',14,'Disaster Recovery Configuration',15,'Storage Manager Disk',16,'Change Tracking File',17,'Flashback Log File',
 20  18,'DataPump Dump File',19,'Cross Platform Converted File',20,'Autobackup',21,'Any OS file',22,'Block Dump File',
 21  23,'CSS Voting File',24,'CRS') into v_typename from dual;
 22  dbms_output.put_line('File: '||v_AsmFileName); dbms_output.new_line;
 23  dbms_output.put_line('Type: '||v_filetype||' '||v_typename); dbms_output.new_line;
 24  dbms_output.put_line('Size (in logical blocks): '||v_filesize); dbms_output.new_line;
 25  dbms_output.put_line('Logical Block Size: '||v_lbks); dbms_output.new_line;
 26  dbms_output.put_line('Physical Block Size: '||v_pblksize); dbms_output.new_line;
 27  dbms_diskgroup.patchfile(v_AsmFileName,v_filetype,v_lbks,1,0,v_filesize,v_FsFileName,2,1,1);
 28  end;
 29  /
Enter value for asm_file_name: +DATA/xifenfei/datafile/users.259.878224279
old  12: v_AsmFileName := '&ASM_file_name';
new  12: v_AsmFileName := '+DATA/xifenfei/datafile/users.259.878224279';
Enter value for filesystem_file_name: /tmp/users01.dbf
old  13: v_FsFileName := '&FileSystem_file_name';
new  13: v_FsFileName := '/tmp/users01.dbf';
File: +DATA/xifenfei/datafile/users.259.878224279
Type: 12 Data File Copy
Size (in logical blocks): 640
Logical Block Size: 8192
Physical Block Size: 512
PL/SQL procedure successfully completed.
[grid@xifenfei ~]$ ls -l /tmp/users01.dbf
-rw-r----- 1 grid oinstall 5251072 Apr 28 16:39 /tmp/users01.dbf

通过上述几个简单例子说明:dbms_diskgroup可以看asm file的属性,可以拷贝asm中的datafile中的某个block到os,也可以从os拷贝到asm,可以从asm中直接拷贝文件文件到os等功能

Oracle 12.1.0.2 的OLAP API组件无效的处理过程

数据库从11.2.0.3.x升级到12.1.0.2.3之时出现如下错误,Oracle OLAP API组件无效

Oracle Database 12.1 Post-Upgrade Status Tool           04-21-2015 10:58:50
Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS
Oracle Server                          UPGRADED      12.1.0.2.0  00:36:20
JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:08:19
Oracle Real Application Clusters          VALID      12.1.0.2.0  00:00:02
Oracle Workspace Manager                  VALID      12.1.0.2.0  00:01:25
OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:24
OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00
Oracle OLAP API
    ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], []
Oracle OLAP API                         INVALID      12.1.0.2.0  00:01:37
Oracle XDK                                VALID      12.1.0.2.0  00:00:56
Oracle Text                               VALID      12.1.0.2.0  00:01:14
Oracle XML Database                       VALID      12.1.0.2.0  00:03:13
Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:18
Oracle Multimedia                         VALID      12.1.0.2.0  00:03:03
Spatial                                UPGRADED      12.1.0.2.0  00:06:41
Final Actions                                                    00:10:10
Total Upgrade Time: 01:24:48

这里主要是OLAP API组件无效,OLAP Catalog组件官方已经有明确说明,12c里面已经不支持,可以升级之后把其卸载。分析alert日志，发现ora-600[qkaQknLTPruneKaf:1]错误

Tue Apr 21 10:15:55 2015
SERVER COMPONENT id=CATPROC: timestamp=2015-04-21 10:15:54
Tue Apr 21 10:16:06 2015
SERVER COMPONENT id=RDBMS: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:16:06
Tue Apr 21 10:16:53 2015
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:24:27 2015
SERVER COMPONENT id=JAVAVM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:24:27
Tue Apr 21 10:24:44 2015
### queuing purge of JIT compilation due to creation of 700010c40827818 oracle/xml/util/XMLUtil
### queuing purge of JIT compilation due to creation of 700010c508b7910 oracle/xdb/XMLType
Tue Apr 21 10:25:10 2015
### jox_purge_jit pid 41746460 pdb 0
Tue Apr 21 10:25:27 2015
SERVER COMPONENT id=XML: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:27
Tue Apr 21 10:25:33 2015
Shared IO Pool defaulting to 512MB. Trying to get it from Buffer Cache for process 42139652.
Tue Apr 21 10:25:52 2015
SERVER COMPONENT id=APS: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:25:52
SERVER COMPONENT id=AMD: status=OPTION OFF, version=11.2.0.3.0, timestamp=2015-04-21 10:25:53
Tue Apr 21 10:27:08 2015
SERVER COMPONENT id=CONTEXT: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:27:08
Tue Apr 21 10:27:55 2015
XDB installed.
XDB initialized.
Tue Apr 21 10:30:22 2015
SERVER COMPONENT id=XDB: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:22
Tue Apr 21 10:30:42 2015
SERVER COMPONENT id=CATJAVA: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:30:42
Tue Apr 21 10:32:07 2015
SERVER COMPONENT id=OWM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:07
SERVER COMPONENT id=RAC: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:32:10
Tue Apr 21 10:35:15 2015
SERVER COMPONENT id=ORDIM: status=VALID, version=12.1.0.2.0, timestamp=2015-04-21 10:35:15
Tue Apr 21 10:37:59 2015
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
  Current log# 9 seq# 15 mem# 0: /oradata/redo/zjrpt/redo09.dbf
Tue Apr 21 10:38:02 2015
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 10 seq# 16 mem# 0: /oradata/redo/zjrpt/redo10.dbf
Tue Apr 21 10:41:58 2015
SERVER COMPONENT id=SDO: status=UPGRADED, version=12.1.0.2.0, timestamp=2015-04-21 10:41:58
Tue Apr 21 10:42:58 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_39125322.trc  (incident=48369):
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc
Tue Apr 21 10:44:09 2015
Errors in file /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/incident/incdir_48369/zjrpt1_ora_39125322_i48369.trc:
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/oradata/redo/zjrpt/redo14.dbf'
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
Tue Apr 21 10:44:09 2015
Dumping diagnostic data in directory=[cdmp_20150421104409], requested by (instance=1, osid=14811583), summary=[incident=48369].
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue Apr 21 10:44:09 2015
SERVER COMPONENT id=XOQ: status=INVALID, version=12.1.0.2.0, timestamp=2015-04-21 10:44:11
Tue Apr 21 10:44:12 2015
SERVER COMPONENT id=ACTIONS_BGN: timestamp=2015-04-21 10:44:12
Tue Apr 21 10:52:06 2015
SERVER COMPONENT id=CATREQ_BGN: timestamp=2015-04-21 10:52:06
Tue Apr 21 10:54:06 2015
SERVER COMPONENT id=CATREQ_END: timestamp=2015-04-21 10:54:06
Tue Apr 21 10:54:23 2015
SERVER ACTION=UPGRADE id=: Upgraded from 11.2.0.3.0
SERVER COMPONENT id=ACTIONS_END: timestamp=2015-04-21 10:54:23
SERVER COMPONENT id=UPGRD_END: timestamp=2015-04-21 10:54:23

这里提示比较明显,XOQ组件由于ORA-600错误,未升级成功

分析trace文件

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u04/oracle/app/oracle/product/12.1
System name: AIX
Node name: zjddrpt5
Release: 1
Version: 7
Machine: 00CB9D064C00
Instance name: zjrpt1
Redo thread mounted by this instance: 1
Oracle process number: 40
Unix process pid: 36765856, image: oracle@zjddrpt5 (TNS V1-V3)
*** 2015-04-21 11:27:29.567
*** SESSION ID:(2258.29003) 2015-04-21 11:27:29.567
*** CLIENT ID:() 2015-04-21 11:27:29.567
*** SERVICE NAME:(SYS$USERS) 2015-04-21 11:27:29.567
*** MODULE NAME:(catcon(pid=3473480)) 2015-04-21 11:27:29.567
*** CLIENT DRIVER:(SQL*PLUS) 2015-04-21 11:27:29.567
*** ACTION NAME:(non-CDB::@cmpupmsc.sql) 2015-04-21 11:27:29.567
[TOC00000]
Jump to table of contents
Dump continued from file: /u04/oracle/app/oracle/diag/rdbms/zjrpt/zjrpt1/trace/zjrpt1_ora_36765856.trc
[TOC00001]
ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], [], [], [], [], [], [], []
[TOC00001-END]
[TOC00002]
========= Dump for incident 192322 (ORA 600 [qkaQknLTPruneKaf:1]) ========
*** 2015-04-21 11:27:29.615
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=5s1pbm9ykxmjx) -----
SELECT COUNT(*) FROM DBA_TAB_PRIVS WHERE GRANTEE='OLAP_XS_ADMIN' AND ((PRIVILEGE='SELECT' AND OWNER='SYS' AND
TABLE_NAME='XS$OLAP_POLICY' AND COMMON='YES') OR (PRIVILEGE='SELECT' AND OWNER='SYS' AND
TABLE_NAME='DBA_ROLES' AND COMMON='YES') OR (PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XDS'
AND COMMON='YES'))
[TOC00004]
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
700010c76ef4290 171 procedure SYS.XOQ_VALIDATE
700010c67f9aa90 2 anonymous block
[TOC00004-END]
[TOC00003-END]
[TOC00005]
----- Call Stack Trace -----
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
<- ksfdmp <- dbgexPhaseII <- dbgexProcessError <- dbgeExecuteForError <- dbgePostErrorKGE
<- 1244 <- dbkePostKGE_kgsf <- kgeadse <- kgerinv_internal <- kgerinv
<- kgeasnmierr <- qkaQknLTPruneKaf <- 780 <- qkaQknPruneKaf <- qknProjPushNode_Int
<- qknProjPushNode <- qkeWalkAllQueryNode <- qknProjPushStmt <- qkeProjPrune <- qkadrv2
<- opitca <- kksFullTypeCheck <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock
<- kksfbc <- kkspbd0 <- kksParseCursor <- opiosq0 <- opipls
<- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2
<- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_EXECC
<- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe
<- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
<- ssthrdmain <- main <- start <-

这里可以大概看出来，出现该问题，可能和SYS.XOQ_VALIDATE有关系.既然OLAP组件无效,那么我们是否可以尝试删除组件,然后重装OLAP组件
卸载OLAP组件

sqlplus /nolog
SQL> conn / as sysdba
SQL> spool remove_olap.log
----> Remove OLAP Catalog
SQL> @?/olap/admin/catnoamd.sql
----> Remove OLAP API
SQL> @?/olap/admin/olapidrp.plb
SQL> @?/olap/admin/catnoxoq.sql
----> Deinstall APS - OLAP AW component
SQL> @?/olap/admin/catnoaps.sql
----> Recompile invalids
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

安装OLAP组件

SQL> @?/olap/admin/olap.sql SYSAUX TEMP

分析安装日志发现创建用户错误

old 3: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name = upper(''&1'')
and contents = ''PERMANENT''' into :default_ts;
new 3: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name =
upper(''SYSAUX'') and contents = ''PERMANENT''' into :default_ts;
old 10: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name = upper(''&2'')
and contents = ''TEMPORARY''' into :temp_ts;
new 10: execute immediate 'select tablespace_name from dba_tablespaces where tablespace_name =
upper(''TEMP'') and contents = ''TEMPORARY''' into :temp_ts;
PL/SQL procedure successfully completed.
Session altered.
old 10: || ' default tablespace ' || '&1'
new 10: || ' default tablespace ' || 'SYSAUX'
old 11: || ' temporary tablespace ' || '&2'
new 11: || ' temporary tablespace ' || 'TEMP'
old 12: || ' quota unlimited on ' || '&1';
new 12: || ' quota unlimited on ' || 'SYSAUX';
DECLARE
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
ORA-06512: at line 8
…………………………
ERROR at line 1:
ORA-01435: user does not exist

通过分析olap对应的创建语句,发现会在olap安装过程中,如果没有olapsys用户,数据库会自己创建该用户

DECLARE
  isthere     NUMBER;
BEGIN
  select count(*) into isthere from dba_users where username ='OLAPSYS';
  if isthere = 0 then
    execute immediate 'create user olapsys identified by no_password'
                  || ' password expire account lock'
                  || ' default tablespace ' || '&1'
                  || ' temporary tablespace ' || '&2'
                  || ' quota unlimited on ' || '&1';
  end if;
END;
/

这里比较明显的显示了创建用户olapsys 状态为Lock，密码为no_password,根据错误提示,应该是该密码不符合要求,对其进行测试

zjddrpt5:/u04/oracle/app/oracle/product/12.1/olap/admin$sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 22 16:40:25 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options
SQL> create user xifenfei identified by no_password;
create user xifenfei identified by no_password
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20008: Password must contain at least one digit, one character
SQL> create user xifenfei identified by "1qsx!qaz";
User created.

分析密码验证原因

SQL> select * from dba_profiles where profile='DEFAULT';
PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT                                              COM
------------------------------ -------------------------------- -------- -------------------------------------------------- ---
DEFAULT                        COMPOSITE_LIMIT                  KERNEL   UNLIMITED                                          NO
DEFAULT                        SESSIONS_PER_USER                KERNEL   UNLIMITED                                          NO
DEFAULT                        CPU_PER_SESSION                  KERNEL   UNLIMITED                                          NO
DEFAULT                        CPU_PER_CALL                     KERNEL   UNLIMITED                                          NO
DEFAULT                        LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED                                          NO
DEFAULT                        LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED                                          NO
DEFAULT                        IDLE_TIME                        KERNEL   UNLIMITED                                          NO
DEFAULT                        CONNECT_TIME                     KERNEL   UNLIMITED                                          NO
DEFAULT                        PRIVATE_SGA                      KERNEL   UNLIMITED                                          NO
DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_LIFE_TIME               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_REUSE_TIME              PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_REUSE_MAX               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_VERIFY_FUNCTION         PASSWORD VERIFY_FUNCTION_11G                                NO
DEFAULT                        PASSWORD_LOCK_TIME               PASSWORD UNLIMITED                                          NO
DEFAULT                        PASSWORD_GRACE_TIME              PASSWORD UNLIMITED                                          NO
16 rows selected.

因为数据库在创建用户之时未指定profile,而默认的profile密码设置了验证,因此olap安装脚本创建olapsys用户的密码为no_password无法成功,因此该用户无法创建.基于此，那我尝试人工创建olapsys用户,然后再执行olap.sql 试试看

SQL> create user olapsys identified by "1qsx!qaz" password
   2 expire account lock default tablespace SYSAUX temporary tablespace TEMP  quota unlimited on SYSAUX;
User created.
SQL> @?/olap/admin/olap.sql SYSAUX TEMP

分析执行日志文件,发现均为对象not exist情况的错误,类似

ORA-04043: object COAD_ADVICE_T does not exist
ORA-04043: object COAD_ADVICE_REC does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist
ORA-01432: public synonym to be dropped does not exist
ORA-00942: table or view does not exist

这里证明安装过程未出错,查询无效对象

select owner,object_name,object_type from dba_objects where status=’INVALID’
[/sql]
也未发现任何sys/system/olapsys中发现任何无效对象,但是OLAP API依然无效,陷入了误解中

SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
SDO
Spatial
12.1.0.2.0                     VALID
ORDIM
Oracle Multimedia
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
XDB
Oracle XML Database
12.1.0.2.0                     VALID
CONTEXT
Oracle Text
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
12.1.0.2.0                     VALID
OWM
Oracle Workspace Manager
12.1.0.2.0                     VALID
CATALOG
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
Oracle Database Catalog Views
12.1.0.2.0                     VALID
CATPROC
Oracle Database Packages and Types
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
JAVAVM
JServer JAVA Virtual Machine
12.1.0.2.0                     VALID
XML
Oracle XDK
12.1.0.2.0                     VALID
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
CATJAVA
Oracle Database Java Packages
12.1.0.2.0                     VALID
APS
OLAP Analytic Workspace
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
12.1.0.2.0                     VALID
XOQ
Oracle OLAP API
12.1.0.2.0                     INVALID
RAC
COMP_ID
------------------------------
COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ ----------------------
Oracle Real Application Clusters
12.1.0.2.0                     VALID
13 rows selected.

到这一步已经超过了我的能力和大连gcs的能力范围,sr升级到美国olap研发团队,配合他们进行分析

CREATE OR REPLACE PROCEDURE xoq_validate_verbose IS
compat VARCHAR2(30);
dummy_num NUMBER;
dummy_out_1_str VARCHAR2(100);
dummy_out_2_str VARCHAR2(100);
ok BOOLEAN := TRUE;
BEGIN
-- check compatible
SELECT value INTO compat FROM v$parameter WHERE name='compatible';
IF NOT (substr(compat,1,3) >= '9.2' OR substr(compat,1,2) >= '10') THEN
ok := FALSE;
END IF;
dbms_output.put_line('compatible:'||compat||' ok:'|| case when ok then 'True' else 'False' end);
IF ok THEN
--check for errors during installation/upgrade
BEGIN
SELECT 0 INTO dummy_num from sys.registry$error
WHERE identifier='XOQ'AND rownum <=1;
-- at least one install error was found so component is invalid
ok := FALSE;
dbms_output.put_line('xoq errors during installation/upgrade. Query sys.registry$error WHERE identifier euqal to XOQ. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('No install errors were found so component remains valid. ok:'|| case when ok then 'True' else 'False' end);
NULL;
END;
END IF;
IF ok THEN
-- check that dependent component XDB is valid
IF dbms_registry.is_valid('XDB', dbms_registry.release_version) != 1 THEN
ok := FALSE;
dbms_output.put_line('dependent component XDB is invalid. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
IF ok THEN
-- check that expected XDB resources are there
IF NOT (dbms_xdb.existsresource('/OLAP_XDS/dsclass.xml') AND
dbms_xdb.existsresource('/olap_data_security/public/acls') AND
dbms_xdb.existsresource('/xds/dsd')) THEN
ok := FALSE;
dbms_output.put_line('expected XDB resources are not there. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
IF ok THEN
-- check that installed library is valid
BEGIN
SELECT 0 INTO dummy_num FROM DBA_LIBRARIES
WHERE STATUS = 'INVALID' AND rownum <=1 AND
OWNER='SYS' AND LIBRARY_NAME = 'DBMS_OLAPI_LIB';
-- at least one object is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('installed library is invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid objects were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check very basic OLAP API function (including load of shared library)
BEGIN
dummy_num := OlapiBootstrap2(compat, dummy_out_1_str, dummy_out_2_str);
EXCEPTION
WHEN OTHERS THEN
ok := FALSE;
DBMS_OUTPUT.PUT_LINE(sqlerrm);
dbms_output.put_line('Error during OlapiBootstrap2. ok:'|| case when ok then 'True' else 'False' end);
END;
END IF;
IF ok THEN
-- check that Java classes are loaded successfully
BEGIN
SELECT 0 INTO dummy_num FROM dba_objects
WHERE owner = 'SYS' AND
status = 'INVALID' AND
object_type = 'JAVA CLASS' AND
object_name LIKE 'oracle/AWXML/%';
-- at least one class is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('oracle/AWXML/.. Java class invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid components were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check that installed types, packages, and procedures are valid
BEGIN
SELECT 0 INTO dummy_num FROM DBA_OBJECTS
WHERE STATUS = 'INVALID' AND rownum <=1 AND
OWNER='SYS' AND OBJECT_NAME IN
('DBMS_CUBE_ADVISE','DBMS_CUBE_ADVISE_SEC','DBMS_CUBE',
'DBMS_CUBE_EXP','GENDATABASEINTERFACE','GENCONNECTIONINTERFACE',
'GENSERVERINTERACE','GENMDMPROPERTYIDCONSTANTS',
'GENMDMCLASSCONSTANTS','GENMDMOBJECTIDCONSTANTS',
'GENMETADATAPROVIDERINTERFACE','GENCURSORMANAGERINTERFACE',
'GENDATATYPEIDCONSTANTS','GENDEFINITIONMANAGERINTERFACE',
'GENDATAPROVIDERINTERFACE','DBMS_AW_XML','DBMS_CUBE_UTIL',
'COAD_ADVICE_T','COAD_ADVICE_REC','GENOLAPIEXCEPTION',
'GENINTERFACESTUB', 'GENINTERFACESTUBSEQUENCE',
'GENRAWSEQUENCE','GENWSTRINGSEQUENCE',
'DBMS_CUBE_UTIL_EXT_MD_T','DBMS_CUBE_UTIL_EXT_MD_R',
'OLAPIHANDSHAKE2','OLAPIBOOTSTRAP2');
-- at least one object is invalid so component is invalid
ok := FALSE;
dbms_output.put_line('Olap type, packages or procedure is invalid. ok:'|| case when ok then 'True' else 'False' end);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- no invalid objects were found so component remains valid
NULL;
END;
END IF;
IF ok THEN
-- check for expected role
BEGIN
SELECT 0 INTO dummy_num FROM DBA_ROLES
WHERE ROLE = 'OLAP_XS_ADMIN';
EXCEPTION
WHEN NO_DATA_FOUND THEN
ok := FALSE;
dbms_output.put_line('OLAP_XS_ADMIN role is missing. ok:'|| case when ok then 'True' else 'False' end);
END;
END IF;
IF ok THEN
SELECT COUNT(*) INTO dummy_num FROM DBA_TAB_PRIVS WHERE GRANTEE='OLAP_XS_ADMIN' AND (
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='AWM_CREATEXDSFOLDER') OR
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_ROLES') OR
(PRIVILEGE='SELECT' AND OWNER='SYS' AND TABLE_NAME='DBA_XDS_INSTANCE_SETS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XDS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_DATA_SECURITY_EVENTS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_PRIVID_LIST') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_ROLESET_EVENTS_INT') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_EVENTS') OR
(PRIVILEGE='EXECUTE' AND OWNER='SYS' AND TABLE_NAME='DBMS_XS_SECCLASS_INT') OR
(PRIVILEGE='EXECUTE' AND OWNER='XDB' AND TABLE_NAME='DBMS_XDB') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XDB$ACL') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$DATA_SECURITY') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$PRINCIPALS') OR
(PRIVILEGE='DELETE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='INSERT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='UPDATE' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS') OR
(PRIVILEGE='SELECT' AND OWNER='XDB' AND TABLE_NAME='XS$SECURITYCLASS'));
IF dummy_num != 26 THEN
ok := FALSE;
dbms_output.put_line('OLAP_XS_ADMIN does necessary privs. ok:'|| case when ok then 'True' else 'False' end);
END IF;
END IF;
END;
/
SQL> set serveroutput on size 10000
SQL> exec xoq_validate_verbose;
compatible:12.1.0.2.0 ok:True
xoq errors during installation/upgrade. Query sys.registry$error WHERE
identifier euqal to XOQ. ok:False
PL/SQL procedure successfully completed.
SQL> set long 12000
SQL> set lines 1200
SQL> set pages 1200
select * from sys.registry$error WHERE identifier ='XOQ';
SQL>
USERNAME                                                                                                                                                                                                                                                         TIMESTAMP
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------
SCRIPT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IDENTIFIER                                                                                                                                                                                                                                                       MESSAGE                                                                          STATEMENT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              ORA-00600: internal error code, arguments: [qkaQknLTPruneKaf:1], [], [], [], [], begin
                                                                                                                                                                                                                                                                  [], [], [], [], [], [], []                                                       xoq_validate;
                                                                                                                                                                                                                                                                                                                                                  exception
                                                                                                                                                                                                                                                                                                                                                  when others then
                                                                                                                                                                                                                                                                                                                                                   sys.dbms_registry.invalid('XOQ');
                                                                                                                                                                                                                                                                                                                                                  end;
SYS                                                                                                                                                                                                                                                              21-APR-15 11.28.35.000000 AM
/u04/oracle/app/oracle/product/12.1/olap/admin/xoqroles.sql
XOQ                                                                                                                                                                                                                                                              [], [], [], [], [], [], [], []                                                   as above

继续处理

SQL> delete from sys.registry$error where identifier='XOQ';
2 rows deleted.
SQL> commit;
Commit complete.
SQL> exec xoq_validate_verbose ;
compatible:12.1.0.2.0 ok:True
No install errors were found so component remains valid. ok:True
expected XDB resources are not there. ok:False
PL/SQL procedure successfully completed.
SQL> select * from sys.registry$error WHERE identifier ='XOQ';
no rows selected
@?/olap/admin/xoqrelod.sql;
@?/rdbms/admin/utlrp.sql
  SQL> select comp_name, status, substr(version,1,10) as version from dba_registry;
COMP_NAME                                           STATUS                 VERSION
--------------------------------------------------- ---------------------- --------------------
Spatial                                             VALID                  12.1.0.2.0
Oracle Multimedia                                   VALID                  12.1.0.2.0
Oracle XML Database                                 VALID                  12.1.0.2.0
Oracle Text                                         VALID                  12.1.0.2.0
Oracle Workspace Manager                            VALID                  12.1.0.2.0
Oracle Database Catalog Views                       VALID                  12.1.0.2.0
Oracle Database Packages and Types                  VALID                  12.1.0.2.0
JServer JAVA Virtual Machine                        VALID                  12.1.0.2.0
Oracle XDK                                          VALID                  12.1.0.2.0
Oracle Database Java Packages                       VALID                  12.1.0.2.0
OLAP Analytic Workspace                             VALID                  12.1.0.2.0
Oracle OLAP API                                     VALID                  12.1.0.2.0
Oracle Real Application Clusters                    VALID                  12.1.0.2.0
13 rows selected.

至此处理完成OLAP API组件终于变成VALID状态.出现此类问题,是否由于设置了default profile文件的密码验证函数导致,还是oracle 12.1.0.2.3的bug导致不能确定.这里到这里让我知道,在数据库升级过程中,最好检查default profile中密码验证配置,免得导致用户无法创建的错误(在升级中有些组件需要重建用户)

通过Administration Assistant for Windows配置win服务和实例关联性

在一些win系统的Oracle数据库中,大家都知道,Oracle启动前需要先启动服务,但是偶尔还有这两种需求:
1. 启动Oracle实例服务,但是不想启动数据库实例(特别是在有些情况下,数据库因为某种错误一启动到open直接报错,可能导致系统僵死
2. 在主机关闭或者服务关闭(重启)之时,希望数据库能够正常关闭后,而不是直接终止实例.
由于win平台的特殊性,Oracle也对其进行了特殊处理,提供了专门的工具(Administration Assistant for Windows)处理此类问题:
启动Administration Assistant for Windows

选择需要配置的数据库服务

右键选择启动/关闭选项

配置服务启动时是否启动数据库实例

配置服务关闭时是否关闭数据库以及关闭数据库的方式

通过上述类似配置可以控制在Oracle服务启动之时实例是否启动,在Oracle服务关闭之时实例是否关闭(以及关闭的方式),建议配置在关闭服务之时,使用immediate(立即关闭)方式关闭数据库,确保数据库安全