标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:五月 2015
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified 恢复思路
今天一网友找到我,说数据库恢复在推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已经非常大,距离天花板较近
这里最大允许的推进的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参数
在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('%¶m%') 7 order by name / 8 Enter value for param: _WITH_SUBQUERY old 6: and upper(a.ksppinm) LIKE upper('%¶m%') 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