2015年5月 – Database SOS

今天一网友找到我,说数据库恢复在推scn的过程中遇到了ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified错误无法解决,让我给予支持.这不禁让我想起,现在由于数据库bug和dblink原因导致了很多数据库scn很大,距离天花板非常近,从而使得数据库恢复过程中无法直接简单的推scn,这里正好结合该例子,简单说明下ORA-01052故障的处理.类似文档以前也写过:ORA-01052发生原因的类似文章

由于坏块导致数据库进行实例恢复无法进行

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 1901 KB redo, 276 data blocks need recovery
Started redo application at
 Thread 1: logseq 1004, block 172771
Recovery of Online Redo Log: Thread 1 Group 2 Seq 1004 Reading mem 0
  Mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO02.LOG
Fri May 29 10:59:56 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:00 2015
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x2048] [PC:0x6215134, __intel_new_memcpy()+260]
Fri May 29 11:00:12 2015
Trace dumping is performing id=[cdmp_20150529110012]
Fri May 29 11:00:12 2015
Slave exiting with ORA-1172 exception
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_p007_1612.trc:
ORA-01172: 线程 1 的恢复停止在块 439938 (在文件 19 中)
ORA-01151: 如果需要, 请使用介质恢复以恢复块和还原备份
Fri May 29 11:00:27 2015
ORA-01578: ORACLE 数据块损坏 (文件号 19, 块号 450245)
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10564: tablespace PSTORE
ORA-01110: 数据文件 19: 'F:\APP\ADMINISTRATOR\ORADATA\XFF\PSTORE_02.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 91642
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [_intel_new_memcpy()+260] [ACCESS_VIOLATION] [ADDR:0x2048]
[PC:0x6215134] [UNABLE_TO_READ] []
Fri May 29 11:00:27 2015
Aborting crash recovery due to slave death, attempting serial crash recovery
RECOVERY OF THREAD 1 STUCK AT BLOCK 439938 OF FILE 19
Fri May 29 11:00:45 2015
Trace dumping is performing id=[cdmp_20150529110045]
Aborting crash recovery due to error 1172
ORA-1172 signalled during: alter database open...

设置_allow_resetlogs_corruption并resetlogs尝试打开数据库

Assigning activation ID 4272042346 (0xfea2316a)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: F:\APP\ADMINISTRATOR\ORADATA\XFF\REDO01.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 29 11:30:47 2015
SMON: enabling cache recovery
Fri May 29 11:30:47 2015
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc  (incident=181236):
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Incident details in: f:\app\administrator\diag\rdbms\XFF\XFF\incident\incdir_181236\XFF_ora_3004_i181236.trc
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Errors in file f:\app\administrator\diag\rdbms\XFF\XFF\trace\XFF_ora_3004.trc:
ORA-00704: ????????
ORA-00704: ????????
ORA-00600: ??????, ??: [2662], [3360], [2233437186], [3360], [2235447064], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 3004): terminating the instance due to error 704
Instance terminated by USER, pid = 3004
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (3004) as a result of ORA-1092

这里可以看到数据库通过设置_allow_resetlogs_corruption参数，进行不完全恢复,跳过数据库启动的实例恢复,然后强制拉库,然后遭遇大家熟悉的ORA-600[2662]错误,使得恢复失败,根据经验,通过推scn来绕过该错误

使用_minimum_giga_scn尝试推SCN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--------------------------------
*._minimum_giga_scn=13443
--------------------------------
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

通过运行Oracle Database Recovery Check检查发现数据库的scn已经非常大,距离天花板较近
_minimum_giga_scn
这里最大允许的推进的scn为13442.7,但是常规的最小的推scn的方法最小值为1024*1024*1024的倍数,因此这里遇到麻烦.
这里数据库遭遇了ORA-01052错误,导致推scn不成功,数据库无法正常启动.出现这类情况,由于scn可以增加的空间非常小,因此可以使用使用oradebug修改数据库scn或者直接修改控制文件scn的方式来精确控制推scn的值(可以实现任何值的scn增加,只要不超过天花板),也可以通过方法修改数据库scn距离天花板的距离,从而实现大幅度使用_minimum_giga_scn来推scn.另外还有一种解决方法:由于ORA-01052是由于scn过大导致(超过了数据库现在的天花板scn),因此出现了ORA-01052.所以另外一种变通的方法,就是通过调整数据库的天花板scn,从而使得_minimum_giga_scn可以继续推scn.在本次恢复中使用最为简单的增加天花板scn的方式来恢复(不过该方法恢复之后需要重建库,其实已经使用了隐含参数屏蔽redo恢复,本身就建议重建库保证数据字典一致性)

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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：在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以后)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：通过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无法正常启动

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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环境

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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环境

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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恢复误删除磁盘组

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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

月度归档：5月 2015