12.1中出现大量Result Cache: RC Latch处理

昨天有个朋友找到我说他们的12.1的库在业务高峰期非常慢,希望我们给予优化支持,经过awr分析,定位到问题为latch free问题,具体定位为:Result Cache: RC Latch.
优化之前awr部分信息
awr整体负载情况,证明当前这个库已经比较忙,业务反馈很慢
awr1


addr信息和top wait信息,确定是latch free问题比较突出
awr2
awr3

latch信息统计和ash信息,找出来突出的latch,定位为Result Cache: RC Latch引起该问题
awr4
awr5

补充大量异常sql
awr6

类似sql语句

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring optimizer_features_enable(default) no_parallel */
 SUM(C1) FROM (SELECT /*+ qb_name("innerQuery") INDEX_FFS( "ACCOUNT" "ACC_USER_ID") */ 
1 AS C1 FROM "ACCOUNT" SAMPLE BLOCK(39.3701, 8) SEED(1) "ACCOUNT" WHERE ( "ACCOUNT".USER_ID IS NOT NULL)) innerQuery

查询mos发现
The cause of this issue is automatic dynamic statistics which is enabled by default in 12c automatically decides whether dynamic statistics are useful and which statistics level to use for all SQL statements. It collects dynamic statistics when the optimizer deems it necessary.
When Automatic Dynamic Sampling is used for the SQL statements, it can decide, based upon these statistics, that a better response time could be achieved by using the result cache for those queries. This can cause heavy usage of the result cache leading to the contention on latch free for “Result Cache: RC Latch”.
也就是说,12c在自动采样有改进,而且默认使用result cache特性,从而引起该问题,即使你设置了 RESULT_CACHE_MODE = MANUAL,依旧会有大量动态采样引起 Result Cache: RC Latch,彻底解决给问题就是通过隐含参数禁止Automatic Dynamic Statistics使用result cache

alter system set "_optimizer_ads_use_result_cache" = FALSE;

设置该参数之后效果
这里看,通过上述处理后,系统db time 大量减少,业务反馈已经运行正常
hawr1


latch free和Result Cache: RC Latch已经基本上消失
hawr2
hawr3
hawr4

当然这个异常是由于动态采样导致,可以通过收集数据库统计信息,设置动态采样级别,也可以从一定程度上缓解该情况.
参考mos
Very Long Parse Time for Queries in InMemory Database (Doc ID 2102106.1)
High “Latch Free” Waits with Contention on ‘Result Cache: RC Latch’ when RESULT_CACHE_MODE = MANUAL on Oracle 12c (Doc ID 2002089.1)

发表在 Oracle性能优化 | 标签为 | 留下评论

csc higher than block scn类型坏块修复

数据库虽然正常open了,但是由于system有坏块,导致数据库导出有部分表报错,客户希望通过修复坏块完美解决该问题
exp-ORA-1578


bbed检查system报坏块

C:\Users\FAL>dbv file=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on 星期六 5月 14 15:40:55 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
csc(0x0000.1f8adab3) higher than block scn(0x0000.00000000)
页 122146 失败, 校验代码为 6054


DBVERIFY - 验证完成

检查的页总数: 252160
处理的页总数 (数据): 178921
失败的页总数 (数据): 0
处理的页总数 (索引): 52576
失败的页总数 (索引): 1
处理的页总数 (其他): 3201
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17462
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 529420540 (0.529420540)

bbed修复坏块

C:\Users\FAL>bbed password=blockedit filename=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

BBED: Release 2.0.0.0.0 - Limited Production on Sat May 14 15:37:01 2016

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set blocksize 8192
        BLOCKSIZE       8192

BBED> set block 2
        BLOCK#          2

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 2                                     Dba:0x00000000
------------------------------------------------------------
 Data File Header

 struct kcvfh, 360 bytes                    @0

 ub4 tailchk                                @8188


BBED> set block 122147
        BLOCK#          122147

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 3720 bytes                   @20

 struct kdxle, 32 bytes                     @3740

 b2 kd_off[188]                             @3772

 ub1 freespace[1534]                        @4148

 ub1 rowdata[2442]                          @5682

 ub4 tailchk                                @8188


BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0041dd22
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x6cee
   ub2 spare3_kcbh                          @18       0x0000

BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146

Block Checking: DBA = 4316450, Block Type = KTB-managed data block
Found block already marked corrupted

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED> set offset 8188
        OFFSET          8188

BBED> map
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Index Leaf)

 struct kcbh, 20 bytes                      @0

 struct ktbbh, 3720 bytes                   @20

 struct kdxle, 32 bytes                     @3740

 b2 kd_off[188]                             @3772

 ub1 freespace[1534]                        @4148

 ub1 rowdata[2442]                          @5682

 ub4 tailchk                                @8188


BBED> d
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff060000

 <32 bytes per line>


BBED> set mode edit
        MODE            Edit

BBED> m /x 01 offset 14
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets:   14 to  525           Dba:0x00000000
------------------------------------------------------------------------
 0104ee6c 00000200 00003500 0000b3da 8a1f0000 00009a00 030021dd 41000900
 0700443d 0600531c c000628b 010000c0 00002c3f 5a1f0300 0000ca5e 05002b0a
 c0008685 01000080 000073d7 8a1f0400 200057a2 0500d530 c000a88e 2c000080
 0000b3d7 8a1f0a00 01002848 08001101 c00084be 29000080 0000e3d7 8a1f0800
 0d00d612 06008f0a c000258d 3c000080 000026d8 8a1f0100 1f0063c1 04006007
 c0002887 4b000080 000046d8 8a1f0700 1e005403 0600a707 c000bd8e 40000080
 00006bd8 8a1f0a00 0b000c48 08001301 c00084be 41000080 0000a2d8 8a1f0600
 1b0055e6 0500c508 c0000f8b 23000080 0000fad8 8a1f0300 0600ca5e 0500220a
 c0008685 34000080 000040d9 8a1f0800 1300d412 0600900a c000258d 2e000080
 000064d9 8a1f0600 0b0092e6 0500c608 c0000f8b 1c000080 000089d9 8a1f0a00
 05001d48 08001401 c00084be 05000080 0000aed9 8a1f0400 100030a2 0500d730
 c000a88e 0e000080 000016da 8a1f0800 1700c712 0600910a c000258d 33000080
 000022da 8a1f0200 1100450d 0500930c c000c085 28000080 00002fda 8a1f0200
 0800610d 0500940c c000c085 20000080 00004eda 8a1f0700 12003303 0600aa07
 c000bd8e 0a000120 0f00b7da 8a1f0a00 13000048 08001501 c00084be 07000220
 1e00f5da 8a1f0900 07000b44 0600650f c000968d 05000120 0f0004db 8a1f0300

 <32 bytes per line>

BBED> sum apply
Check value for File 0, Block 122147:
current = 0x6cee, required = 0x6cee

BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0


BBED> m /x c1da8a1f offset 8
 File: D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF (0)
 Block: 122147           Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 c1da8a1f 00000104 ee6c0000 02000000 35000000 b3da8a1f 00000000 9a000300
 21dd4100 09000700 443d0600 531cc000 628b0100 00c00000 2c3f5a1f 03000000
 ca5e0500 2b0ac000 86850100 00800000 73d78a1f 04002000 57a20500 d530c000
 a88e2c00 00800000 b3d78a1f 0a000100 28480800 1101c000 84be2900 00800000
 e3d78a1f 08000d00 d6120600 8f0ac000 258d3c00 00800000 26d88a1f 01001f00
 63c10400 6007c000 28874b00 00800000 46d88a1f 07001e00 54030600 a707c000
 bd8e4000 00800000 6bd88a1f 0a000b00 0c480800 1301c000 84be4100 00800000
 a2d88a1f 06001b00 55e60500 c508c000 0f8b2300 00800000 fad88a1f 03000600
 ca5e0500 220ac000 86853400 00800000 40d98a1f 08001300 d4120600 900ac000
 258d2e00 00800000 64d98a1f 06000b00 92e60500 c608c000 0f8b1c00 00800000
 89d98a1f 0a000500 1d480800 1401c000 84be0500 00800000 aed98a1f 04001000
 30a20500 d730c000 a88e0e00 00800000 16da8a1f 08001700 c7120600 910ac000
 258d3300 00800000 22da8a1f 02001100 450d0500 930cc000 c0852800 00800000
 2fda8a1f 02000800 610d0500 940cc000 c0852000 00800000 4eda8a1f 07001200
 33030600 aa07c000 bd8e0a00 01200f00 b7da8a1f 0a001300 00480800 1501c000
 84be0700 02201e00 f5da8a1f 09000700 0b440600 650fc000 968d0500 01200f00

 <32 bytes per line>

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0041dd22
   ub4 bas_kcbh                             @8        0x1f8adac1
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x6cee
   ub2 spare3_kcbh                          @18       0x0000


BBED> sum apply
Check value for File 0, Block 122147:
current = 0x7364, required = 0x7364


BBED> verify
DBVERIFY - Verification starting
FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF
BLOCK = 122146


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 1
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

dbv再次验证,坏块完美修复

C:\Users\FAL>dbv file=D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on 星期六 5月 14 22:00:06 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = D:\BAIDUYUNDOWNLOAD\ORADATA\CHEASDB\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 259840
处理的页总数 (数据): 181934
失败的页总数 (数据): 0
处理的页总数 (索引): 57381
失败的页总数 (索引): 0
处理的页总数 (其他): 3218
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17307
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 530218225 (0.530218225)

通过修复之后重新导出数据
exp-ok


参考相似篇章:file 1 block 128 corrupted/坏块恢复—system rollback坏块修复

发表在 非常规恢复 | 标签为 , , , | 留下评论

Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201604

Patchsets

 l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)

 21419221

 11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)

 13390677

 11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER)

 10404530

 11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)

 10098816

 11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER)

 6890831

 10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER)

 8202632

 d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)

 6810189

 e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)

 5337014

 10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)

 4547817

 10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER)

 4505133

 10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER)

 4163362

 10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER)

 3761843

 9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER)

 4547809

 9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER)

 4163445

 9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER)

 3948480

 9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0)

 3501955

 9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER) 

 3095277

 9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER)

 2761332

 9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER)

 2632931

 9.0.1.5 (9.0.1.5 PATCHSET)

 3301544

 9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER)

 2517300

 9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER)

 2271678

 8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER)

 2376472

 8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER)

 2189751

 8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER)

 1909158

PSU, SPU(CPU), Bundle Patches

12.1.0.2

 Description

 PSU

   GI PSU

 Bundle Patch (Windows 32bit & 64bit)

 APR2016

 22291127 (12.1.0.2.160419)

 22646084 (12.1.0.2.160419)

 22809813 (12.1.0.2.160419)

 JAN2016

 21948354 (12.1.0.2.160119)

 22191349 (12.1.0.2.160119)

 22310559 (12.1.0.2.160119)

 OCT2015

 21359755 (12.1.0.2.5)

 21523234 (12.1.0.2.5)

 21821214 (12.1.0.2.10)

 JUL2015

 20831110 (12.1.0.2.4)

 20996835 (12.1.0.2.4)

 21126814 (12.1.0.2.7)

 APR2015

 20299023 (12.1.0.2.3)

 20485724 (12.1.0.2.3)

 20684004 (12.1.0.2.4)

 JAN2015

 19769480 (12.1.0.2.2)

 19954978 (12.1.0.2.2)

 19720843 (12.1.0.2.1)

 OCT2014

 19303936 (12.1.0.2.1)

 19392646 (12.1.0.2.1)

 N/A

 

12.1.0.1

 Description

 PSU

 GI PSU

  Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 APR2016

 22291141 (12.1.0.1.160419)

 i22654153 / k22654166 (12.1.0.1.160419)

22839614 (12.1.0.1.160419)

 JAN2016

 21951844 (12.1.0.1.160119)

 j22191492 / k22191511 (12.1.0.1.160119)

22494866 (12.1.0.2.160119)

 OCT2015

 21352619 (12.1.0.1.9)

 j21551666 / k21551685 (12.1.0.1.9)

21744907 (12.1.0.1.21)

 JUL2015

 20831107 (12.1.0.1.8)

  j20996901 / k20996911 (12.1.0.1.8)

21076681  (12.1.0.1.20)

 APR2015

 20299016 (12.1.0.1.7)

  j20485762 / k19971331 (12.1.0.1.7)

20558101 (12.1.0.1.18)

 JAN2015

 19769486 (12.1.0.1.6)

 j19971324 / k19971331 (12.1.0.1.6)

20160748 (12.1.0.1.16)

 OCT2014

 19121550 (12.1.0.1.5)

 j19392372 / k19392451 (12.1.0.1.5)

19542943 (12.1.0.1.14)

 JUL2014

 18522516 (12.1.0.1.4)

 j18705901 / k18705972 (12.1.0.1.4)

19062327 (12.1.0.1.11)

 APR2014

 18031528 (12.1.0.1.3)

 j18139660 / k18413105  (12.1.0.1.3)

18448604 (12.1.0.1.7)

 JAN2014

 17552800 (12.1.0.1.2)

 17735306 (12.1.0.1.2)

17977915 (12.1.0.1.3)

 OCT2013

 17027533 (12.1.0.1.1)

 17272829 (12.1.0.1.1)

 17363796 (12.1.0.1.1)

 17363795 (12.1.0.1.1)

11.2.0.4

 Description

 PSU

 SPU(CPU)

 GI PSU

 Bundle Patch (Windows 32bit & 64bit)

 APR2016

 22502456 (11.2.0.4.160419)

 22502493 (11.2.0.4.160419)

 22646198 (11.2.0.4.160419)

 22839608 (11.2.0.4.160419)

 JAN2016

 21948347 (11.2.0.4.160119)

 21972320 (11.2.0.4.160119)

 22191577 (11.2.0.4.160119)

 22310544 (11.2.0.4.160119)

 OCT2015

 21352635 (11.2.0.4.8)

 21352646

 21523375 (11.2.0.4.8)

 21821802 (11.2.0.4.20)

 JUL2015

 20760982 (11.2.0.4.7)

 20803583

 20996923 (11.2.0.4.7)

 21469106 (11.2.0.4.18)

 APR2015

 20299013 (11.2.0.4.6)

 20299015

 20485808 (11.2.0.4.6)

 20544696 (11.2.0.4.15)

 JAN2015

 19769489 (11.2.0.4.5)

 19854503

 19955028 (11.2.0.4.5)

 20127071 (11.2.0.4.12)

 OCT2014

 19121551 (11.2.0.4.4)

 19271443

 19380115 (11.2.0.4.4)

 19651773 (11.2.0.4.10)

 JUL2014

 18522509 (11.2.0.4.3)

 18681862

 18706472 (11.2.0.4.3)

 18842982 (11.2.0.4.7)

 APR2014

 18031668 (11.2.0.4.2)

 18139690

 18139609 (11.2.0.4.2)

 18296644 (11.2.0.4.4)

 JAN2014

 17478514 (11.2.0.4.1)

 17551709

 N/A

 17987366 (11.2.0.4.1)

11.2.0.3

 Description

 PSU

 SPU(CPU)

 GI PSU

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 aJUL2015

 20760997 (11.2.0.3.15)

 20803576

 20996944 (11.2.0.3.15)

 21104036

 21104035

 APR2015

 20299017 (11.2.0.3.14)

 20299010

 20485830 (11.2.0.3.14)

 20420395

 20420394

 JAN2015

 19769496 (11.2.0.3.13)

 19854461

 19971343 (11.2.0.3.13)

 20233168

 20233167

 OCT2014

 19121548 (11.2.0.3.12)

 19271438

 19440385 (11.2.0.3.12)

 19618575

 19618574

 JUL2014

 18522512 (11.2.0.3.11)

 18681866

 18706488 (11.2.0.3.11)

 18940194

 18940193

 APR2014

 18031683 (11.2.0.3.10)

 18139695

 18139678 (11.2.0.3.10)

 18372244

 18372243

 JAN2014

 17540582 (11.2.0.3.9)

 17478415

 17735354 (11.2.0.3.9)

 18075406

 17906981

 OCT2013

 16902043 (11.2.0.3.8)

 17082364

 17272731 (11.2.0.3.8)

 17363850

 17363844

 JUL2013

 16619892 (11.2.0.3.7)

 16742095

 16742216 (11.2.0.3.7)

 16803775

 16803774

 APR2013

 16056266 (11.2.0.3.6)

 16294378

 16083653 (11.2.0.3.6)

 16345834

 16345833

 JAN2013

 14727310 (11.2.0.3.5)

 14841409

 14727347 (11.2.0.3.5)

 16042648

 16042647

 OCT2012

 14275605 (11.2.0.3.4)

 14390252

 14275572 (11.2.0.3.4)

 14613223

 14613222

 JUL2012

 13923374 (11.2.0.3.3)

 14038787

 13919095 (11.2.0.3.3)

 14223718

 14223717

 APR2012

 13696216 (11.2.0.3.2)

 13632717

 13696251 (11.2.0.3.2)

 13885389

 13885388

 JAN2012

 13343438 (11.2.0.3.1)

 13466801

 13348650 (11.2.0.3.1)

 13413168

 13413167

11.2.0.2

 Description

 PSU

  SPU(CPU)

 GI PSU

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 aOCT2013

 17082367 (11.2.0.2.12)

 17082375

 17272753 (11.2.0.2.12)

 17363838

 17363837

 JUL2013

 16619893 (11.2.0.2.11)

 16742100

 16742320 (11.2.0.2.11)

 16345852

 16345851

 APR2013

 16056267 (11.2.0.2.10)

 16294412

 16166868 (11.2.0.2.10)

 16345846

 16345845

 JAN2013

 14727315 (11.2.0.2.9)

 14841437

 14841385 (11.2.0.2.9)

 16100399

 16100398

 OCT2012

 14275621 (11.2.0.2.8)

 14390377

 14390437 (11.2.0.2.8)

 14672268

 14672267

 JUL2012

 13923804 (11.2.0.2.7)

 14038791

 14192201 (11.2.0.2.7)

 14134043

 14134042

 APR2012

 13696224 (11.2.0.2.6)

 13632725

 13696242 (11.2.0.2.6)

 13697074

 13697073

 JAN2012

 13343424 (11.2.0.2.5)

 13343244

 13653086 (11.2.0.2.5)

 13413155

 13413154

 OCT2011

 12827726 (11.2.0.2.4)

 12828071

 12827731 (11.2.0.2.4)

 13038788

 13038787

 JUL2011

 12419331 (11.2.0.2.3)

 12419321

 12419353 (11.2.0.2.3)

 12714463

 12714462

 APR2011

 11724916 (11.2.0.2.2)

 11724984

 12311357 (11.2.0.2.2)

 11896292

 11896290

 JAN2011

 10248523 (11.2.0.2.1)

 N/A

 N/A

 10432053

 10432052

11.2.0.1

 Description

 PSU

 CPU

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 aJUL2011

 12419378 (11.2.0.1.6)

 12419278

 12429529

 12429528

 APR2011

 11724930 (11.2.0.1.5)

 11724991

 11731176

 11883240

 JAN2011

 10248516 (11.2.0.1.4)

 10249532

 10432045

 10432044

 OCT2010

 9952216 (11.2.0.1.3)

 9952260

 10100101

 10100100

 JUL2010

 9654983 (11.2.0.1.2)

 9655013

 9736865

 9736864

 APR2010

 9352237 (11.2.0.1.1)

 9369797

 N/A

 N/A

11.1.0.7

 Description

 PSU

 SPU(CPU)

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

JUL2015

 20761024 (11.1.0.7.24)

 20803573

 21104030

 21104029

APR2015

 20299012 (11.1.0.7.23)

 20299020

 20420391

 20420390

JAN2015

 19769499 (11.1.0.7.22)

 19854433

 20126915

 20126914

OCT2014

 19152553 (11.1.0.7.21)

 19274522

 19609034

 19609032

JUL2014

 18522513 (11.1.0.7.20)

 18681875

 18944208

 18944207

APR2014

 18031726 (11.1.0.7.19)

 18139703

 18372258

 18372257

JAN2014

 17465583 (11.1.0.7.18)

 17551415

 17906936

 17906935

OCT2013

 17082366 (11.1.0.7.17)

 17082374

 17363760

 17363759

JUL2013

 16619896 (11.1.0.7.16)

 16742110

 16803788

 16803787

APR2013

 16056268 (11.1.0.7.15)

 16308394

 16345862

 16345861

JAN2013

 14739378 (11.1.0.7.14)

 14841452

 15848067

 15848066

OCT2012

 14275623 (11.1.0.7.13)

 14390384

 14672313

 14672312

 JUL2012

 13923474 (11.1.0.7.12)

 14038803

 14109868

 14109867

 APR2012

 13621679 (11.1.0.7.11)

 13632731

 13715810

 13715809

 JAN2012

 13343461 (11.1.0.7.10)

 13343453

 13460956

 13460955

 OCT2011

 12827740 (11.1.0.7.9)

 12828097

 12914916

 12914915

 JUL2011

 12419384 (11.1.0.7.8)

 12419265

 12695278

 12695277

 APR2011

 11724936 (11.1.0.7.7)

 11724999

 11741170

 11741169

 JAN2011

 10248531 (11.1.0.7.6)

 10249534

 10350788

 10350787

 OCT2010

 9952228  (11.1.0.7.5)

 9952269

 9773825

 9773817

 JUL2010

 9654987 (11.1.0.7.4)

 9655014

 9869912

 9869911

 APR2010

 9352179 (11.1.0.7.3)

 9369783

 9392335

 9392331

 JAN2010

 9209238 (11.1.0.7.2)

 9114072

 9166861

 9166858

 OCT2009

 8833297 (11.1.0.7.1)

 8836375

 8928977

 8928976

 JUL2009

 N/A

 8534338

 8553515

 8553512

 APR2009

 N/A

 8290478

 8343070

 8343061

11.1.0.6

 Description

 CPU

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 aJUL2009

 8534378

 8563155

 8563154

 APR2009

 8290402

 8333657

 8333655

 JAN2009

 7592335

 7631981

 7631980

 OCT2008

 7375639

 7378393

 7378392

 JUL2008

 7150417

 7210197

 7210195

 APR2008

 6864063

 6867180

 6867178

10.2.0.5

 Description

 PSU

 SPU(CPU)

 Bundle Patch (Windows64bit)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 bJUL2015

 20299014 (10.2.0.5.19)

 20299021

 20420387

 20420386

 N/A

 APR2015

 N/A

 N/A

 N/A

 N/A

 N/A

 JAN2015

 19769505 (10.2.0.5.18)

 19854436

 20126868

 20126867

 N/A

 OCT2014

 19274523 (10.2.0.5.17)

 19274521

 19618565

 19618563

 N/A

 JUL2014

 18522511 (10.2.0.5.16)

 18681879

 18940198

 18940196

 N/A

 APR2014

 18031728 (10.2.0.5.15)

 18139709

 18372261

 18372259

 N/A

 JAN2014

 17465584 (10.2.0.5.14)

 17551414

 17906974

 17906972

 N/A

 OCT2014

 17082365 (10.2.0.5.13)

 17082371

 N/A

 17363822

 N/A

 JUL2013

 16619894 (10.2.0.5.12)

 16742123

 16803782

 16803780

 16803781

 APR2013

 16056270 (10.2.0.5.11)

 16270946

 16345857

 16345855

 16345856

 JAN2013

 14727319 (10.2.0.5.10)

 14841459

 15848062

 15848060

 15848061

 OCT2012

 14275629 (10.2.0.5.9)

 14390396

 14553358

 14553356

 14553357

 JUL2012

 13923855 (10.2.0.5.8)

 14038805

 14134053

 14134051

 14134052

 APR2012

 13632743 (10.2.0.5.7)

 13632738

 13654815

 13654814

 13870404

 JAN2012

 13343471 (10.2.0.5.6)

 13343467

 13460968

13460967

 N/A

 OCT2011

 12827745 (10.2.0.5.5)

 12828105

 c12914913

 12914911

 N/A

 JUL2011

 12419392 (10.2.0.5.4)

 12419258

 12429524

 12429523

 N/A

 APR2011

 11724962 (10.2.0.5.3)

 11725006

 12328269

 12328268

 N/A

 JAN2011

 10248542 (10.2.0.5.2)

 10249537

 10352673

 10352672

 N/A

 OCT2010

 9952230 (10.2.0.5.1)

 9952270

 10099855

 10058290

 N/A

10.2.0.4

 Description

 PSU

 SPU(CPU)

 Bundle Patch (Windows32bit)

 Bundle Patch (Windows64bit)

 Bundle Patch (WindowsItanium)

 gJUL2013

 16619897 (10.2.0.4.17)

 16742253

 N/A

 N/A

 N/A

 gAPR2013

 16056269 (10.2.0.4.16)

 16270931

 N/A

 N/A

 N/A

 gJAN2013

 14736542 (10.2.0.4.15)

 14841471

 N/A

 N/A

 N/A

gOCT2012

 14275630 (10.2.0.4.14)

 14390410

 N/A

 N/A

 N/A

gJUL2012

 13923851 (10.2.0.4.13)

 14038814

 N/A

 N/A

 N/A

 aAPR2012

 12879933 (10.2.0.4.12)

 12879926

 13928775

 13928776

 N/A

 JAN2012

 12879929 (10.2.0.4.11)

 12879912

 b13654060

 N/A

 N/A

 OCT2011

 12827778 (10.2.0.4.10)

 12828112

 12914908

 12914910

 12914909

 JUL2011

 12419397 (10.2.0.4.9)

 12419249

 12429519

 12429521

 12429520

 APR2011

 11724977 (10.2.0.4.8)

 11725015

 12328501

 12328503

 12328502

 JAN2011

 10248636 (10.2.0.4.7)

 10249540

 10349197

 10349200

 10349198

 OCT2010

 9952234 (10.2.0.4.6)

 9952272

 10084980

 10084982

 10084981

 JUL2010

 9654991 (10.2.0.4.5)

 9655017

 9777076

 9777078

 9777077

 APR2010

 9352164 (10.2.0.4.4)

 9352191

 9393548

 9393550

 9393549

 JAN2010

 9119284 (10.2.0.4.3)

 9119226

 9169457

 9169460

 9169458

 OCT2009

 8833280 (10.2.0.4.2)

 8836308

 8880857

 8880861

 8880858

 JUL2009

 8576156 (10.2.0.4.1)

 8534387

 8559466

 8559467

 8541782

 APR2009

 N/A

 8290506

 8307237

 8307238

 8333678

 JAN2009

 N/A

 7592346

 7584866

 7584867

 N/A

 OCT2008

 N/A

 7375644

 7386320

 7386321

 N/A

 JUL2008

 N/A

 7150470

 7218676

 7218677

 N/A

10.2.0.3

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 Bundle Patch (Windows64bit)

 aJAN2009

 7592354

 7631956

 7631958

 7631957

 OCT2008

 7369190

 7353782

 7353784

 7353785

 JUL2008

 7150622

 7252496

 7252497

 7252498

 APR2008

 6864068

 6867054

 6867055

 6867056

 JAN2008

 6646853

 6637237

 6637238

 6637239

 OCT2007

 6394981

 6430171

 6430173

 6430174

 JUL2007

 6079591

 6116131

 6038242

 6116139

 APR2007

 5901891

 5948242

 5916262

 5948243

 JAN2007

 5881721

 5846376

 5846377

 5846378

10.2.0.2

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

  Bundle Patch (Windows64bit)

 Bundle Patch (WindowsItanium)

 iJAN2009

 7592355

 N/A

 N/A

 N/A

 hOCT2008

 7375660

 N/A

 N/A

 N/A

 hJUL2008

 7154083

 N/A

 N/A

 N/A

 hAPR2008

 6864071

 N/A

 N/A

 N/A

 aJAN2008

 6646850

 N/A

 N/A

 N/A

 fOCT2007

 6394997

 6397028

 6397030

 6397029

 JUL2007

 6079588

 6013105

 6013121

 6013118

 APR2007

 5901881

 5912173

 5912179

 5912176

 JAN2007

 5689957

 5716143

 5699839

 5699824

 OCT2006

 5490848

 5502226

 5500921

 5500894

 JUL2006

 5225799

 5251025

 5251028

 5251026

 APR2006

 5079037

 5140461

 5140567

 5140508

10.2.0.1

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (Windows64bit)

 Bundle Patch (WindowsItanium)

 APR2007

 5901880

 N/A

 N/A

 N/A

 JAN2007

 5689937

 5695784

 5695786

 5695785

 OCT2006

 5490846

 5500927

 5500954

 5500951

 JUL2006

 5225798

 5239698

 5239701

 5239699

 APR2006

 5049080

 5059238

 5059261

 5059251

 JAN2006

 4751931

 4751539

 4770480

 4751549

10.1.0.5

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

  Bundle Patch (WindowsItanium)

 JAN2012

 13343482

 13413002

 13413003

 OCT2011

 12828135

 12914905

 12914906

 JUL2011

 12419228

 12429517

 12429518

 APR2011

 11725035

 11731119

 11731120

 JAN2011

 N/A

 N/A

 N/A

 OCT2010

 9952279

 10089559

 10089560

 JUL2010

 9655023

 9683651

 9683652

 APR2010

 9352208

 9390288

 9390289

 JAN2010

 9119261

 9187104

 9187105

 OCT2009

 8836540

 8785211

 8785212

 JUL2009

 8534394

 8656224

 8656226

 APR2009

 8290534

 8300356

 8300360

 JAN2009

 7592360

 7486619

 7586049

 OCT2008

 7375686

 7367493

 7367494

 JUL2008

 7154097

 7047034

 7047037

 APR2008

 6864078

 6867107

 6867108

 JAN2008

 6647005

 6637274

 6637275

 OCT2007

 6395024

 6408393

 6408394

 JUL2007

 6079585

 6115804

 6115818

 APR2007

 5901877

 5907304

 5907305

 JAN2007

 5689908

 5716295

 5634747

 OCT2006

 5490845

 5500883

 5500885

 JUL2006

 5225797

 5251148

 5251140

 APR2006

 5049074

 5057606

 5057609

 JAN2006

 4751932

 4882231

 4882236

10.1.0.4

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 APR2007

 5901876

 5909871

 5909879

 JAN2007

 5689894

 5695771

 5695772

 OCT2006

 5490844

 5500878

 5500880

 JUL2006

 5225796

 5239736

 5239737

 APR2006

 5049067

 5059200

 5059227

 JAN2006

 4751928

 4751259

 4745040

 OCT2005

 4567866

 4579182

 4579188

 JUL2005

 4392423

 4440706

 4404600

 APR2005

 4210374

 4287619

 4287611

10.1.0.3

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 JAN2007

 5923277

 N/A

 N/A

 OCT2006

 5566825

 N/A

 N/A

 JUL2006

 5435164

 N/A

 N/A

 APR2006

 5158022

 N/A

 N/A

 JAN2006

 4751926

 4741077

 4741084

 OCT2005

 4567863

 4567518

 4567523

 JUL2005

 4392409

 4389012

 4389014

 APR2005

 4193286

 4269715

 4158888

 JAN2005

 4003062

 4074232

 3990812

10.1.0.2

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 APR2005

 4193293

 4181849

 4213305

 JUL2005

 4400766

 4388944

 4388948

 JAN2005

 4003051

 4104364

 4083038

9.2.0.8

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 JUL2010

 9655027

 9683644

 9683645

 APR2010

 9352224

 9390286

 N/A

 JAN2010

 9119275

 9187106

 N/A

 OCT2009

 8836758

 8785185

 8785186

 JUL2009

 8534403

 8427417

 8427418

 APR2009

 8290549

 8300340

 8300346

 JAN2009

 7592365

 7703210

 7703212

 OCT2008

 7375695

 7394394

 7394402

 JUL2008

 7154111

 7047026

 7047029

 APR2008

 6864082

 6867138

 6867139

 JAN2008

 6646842

 6637265

 6637266

 OCT2007

 6395038

 6417013

 6417014

 JUL2007

 6079582

 6130293

 6130295

 APR2007

 5901875

 5916268

 5916275

 JAN2007

 N/A

 N/A

 N/A

 OCT2006

 5490859

 5652380

 5639519

9.2.0.7

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 JUL2007

 6079579

 6146759

 6146748

 APR2007

 5901872

 5907274

 5907275

 JAN2007

 5689875

 5654905

 5654909

 OCT2006

 5490841

 5500873

 5500874

 JUL2006

 5225794

 5250980

 5250981

 APR2006

 5049060

 5064365

 5064364

 JAN2006

 4751923

 4751528

 4741074

 OCT2005

 4567854

 4579590

 4579599

 JUL2005

 4547566

 N/A

 N/A

9.2.0.6

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 OCT2006

 5490840

 5500865

 5500871

 JUL2006

 5225793

 5239794

 5239793

 APR2006

 5049051

 5059614

 5059615

 JAN2006

 4751921

 4751261

 4751262

 OCT2005

 4567846

 4579093

 4579097

 JUL2005

 4392392

 4445852

 4401917

 APR2005

 4193295

 4269928

 4213298

9.2.0.5

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 OCT2006

 5689708

 N/A

 N/A

 JUL2006

 5435138

 N/A

 N/A

 APR2006

 5219762

 N/A

 N/A

 OCT2005

 4560421

 N/A

 N/A

 JUL2005

 4392256

 4387563

 4391819

 APR2005

 4193299

 4195791

 4214192

 JAN2005

 4003006

 4104374

 3990809

9.2.0.4

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 Bundle Patch (WindowsItanium)

 JAN2005

 4002994

 4104369

 4083202

8.1.7.4

 Description

 CPU (Unix/Linux)

 Bundle Patch (Windows32bit)

 JAN2007

 5689799

 5686514

 OCT2006

 5490835

 5496067

 JUL2006

 5225788

 5236412

 APR2006

 5045247

 5057601

 JAN2006

 4751906

 4751570

 OCT2005

 4560405

 4554818

 JUL2005

 4392446

 4437058

 APR2005

 4193312

 4180163

 JAN2005

 4002909

 3921893

OJVM PSU Patches

12.1.0.2

 Description

 OJVM PSU (Linux/Unix)

 OJVM BP (Windows)

 Combo OJVM + DB PSU

 Combo OJVM + GI PSU

 APR2016

 22674709 (12.1.0.2.160419)

 22839633 (12.1.0.2.160419)

 22738582 (12.1.0.2.160419)

 22738641 (12.1.0.2.160419)

 JAN2016

 22139226 (12.1.0.2.160119)

 22311086 (12.1.0.2.160119)

 22191659 (12.1.0.2.160119)

 22191676 (12.1.0.2.160119)

 OCT2015

 21555660 (12.1.0.2.5)

 21788394 (12.1.0.2.4)

 21520444

 21523260

 JUL2015

 21068507 (12.1.0.2.4)

 21153530 (12.1.0.2.3)

 21150768

 21150782

 APR2015

 20415564 (12.1.0.2.3)

 20391199 (12.1.0.2.2)

 20834354

 20834538

 JAN2015

 19877336 (12.1.0.2.2)

 20225938 (12.1.0.2.1)

 20132434

 20132450

 OCT2014 (12.1.0.2.1)

 19282028

 19791366

 19791375

12.1.0.1

 Description

 OJVM PSU (Linux/Unix)

 OJVM BP (Windows)

 Combo OJVM + DB PSU

 Combo OJVM + GI PSU

 Generic JDBC

 APR2016 (12.1.0.1.160419)

 22674703

 22839627

 22738678

 22738715

 Included in OJVM PSU

 JAN2016 (12.1.0.1.160119)

 22139235

 22311072

 22191711

 22191721

 OCT2015 (12.1.0.1.5)

 21555669

 21788365

 21744318

 21744328

 JUL2015 (12.1.0.1.4)

 21068523

 21153513

 21150806

 21150817

 APR2015 (12.1.0.1.3)

 20406245

 20225909

 20834568

 20834579

 JAN2015 (12.1.0.1.2)

 19877342

 20225916

 20132482

 20132489

 OCT2014 (12.1.0.1.1)

 19282024

 19801531

 19791363

 19791360

 19852357

11.2.0.4

 Description

 OJVM PSU (Linux/Unix)

 OJVM BP (Windows)

  Combo OJVM + DB PSU

 Combo OJVM + DB SPU

 Combo OJVM + GI PSU

 Generic JDBC

 APR2016 (11.2.0.4.160419)

 22674697

 22617408

 22738777

 22738732

 22738793

 Included in OJVM PSU

 JAN2016 (11.2.0.4.160119)

 22139245

 22311053

 22378146

 22378121

 22378167

 OCT2015 (11.2.0.4.5)

 21555791

 21788344

 21744343

 21744335

 21744348

 JUL2015 (11.2.0.4.4)

 21068539

 21153498

 21150851

 21150829

 21150864

 APR2015 (11.2.0.4.3)

 20406239

 20225988

 20834611

 20834597

 20834621

 JAN2015 (11.2.0.4.2)

 19877440

 20225982

 20132580

 20132517

 20132615

 OCT2014 (11.2.0.4.1)

 19282021

19799291

 19791364

 19791358

 19791420

19852360

11.2.0.3

 Description

 OJVM PSU (Linux/Unix)

 OJVM BP (Windows)

Combo OJVM + DB PSU

 Combo OJVM + DB SPU

Combo OJVM + GI PSU

 Generic JDBC

 JUL2015 (11.2.0.3.4)

21068553

 21153470

21150891

21150885

 21150904

 Included in OJVM PSU

 APR2015 (11.2.0.3.3)

20406220

 20391185

20834670

20834653

 20834686

 JAN2015 (11.2.0.3.2)

19877443

 20227195

20132646

20132635

 20132651

 OCT2014 (11.2.0.3.1)

19282015

 19806120

19791427

19791426

19791428

19852361

11.1.0.7

Description

OJVM PSU (Linux/Unix)

 OJVM BP (Windows)

Combo OJVM + DB PSU

Combo OJVM + DB SPU

 Combo OJVM + GI PSU

Generic JDBC

 JUL2015 (11.1.0.7.4)

 21068565

 21153423

 21150939

 21150929

  N/A

  Included in OJVM PSU

 APR2015 (11.1.0.7.3)

 20406213

 20391156

 20834724

 20834712

  N/A

 JAN2015 (11.1.0.7.2)

 19877446

 20227146

 20132677

 20132669

  N/A

 OCT2014 (11.1.0.7.1)

 19282002

 19806118

 19791436

 19791434

  N/A

 19852363

参考:Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)

发表在 Oracle | 留下评论

分析drop col对于数据存储块做了什么

oracle 的alter table drop col具体内部是对于数据存储块操作的,如果drop col之后dul之类的工具是否可以恢复,这里我通过具体测试,结合bbed,dump block等方法来说明该问题
1.创建测试表,并写入硬盘

SQL> create table xff.t_xifenfei as select object_id,owner,object_name from dba_objects;

Table created.

SQL> desc xff.t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)


SQL> alter system checkpoint;

System altered.

SQL> alter system checkpoint;

System altered.

2.找出来测试表一个block分析drop col对于存储的影响

SQL> select   rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno,object_id
  4    5  from xff.t_xifenfei where rownum<5;

ROWID                 REL_FNO    BLOCKNO      ROWNO  OBJECT_ID
------------------ ---------- ---------- ---------- ----------
AAAZ9wAAEAAAJojAAA          4      39459          0         20
AAAZ9wAAEAAAJojAAB          4      39459          1         46
AAAZ9wAAEAAAJojAAC          4      39459          2         28
AAAZ9wAAEAAAJojAAD          4      39459          3         15

3. dump block,并且记录该block 1,2,和最后一条记录

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 39459;

System altered.

SQL> oradebug TRACEFILE_NAME
/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14069.trc


block_row_dump:
tab 0, row 0, @0x1f70
tl: 16 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 15
col  1: [ 3]  53 59 53
col  2: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f5e
tl: 18 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 2f
col  1: [ 3]  53 59 53
col  2: [ 7]  49 5f 55 53 45 52 31
…………
tab 0, row 288, @0x589
tl: 22 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 3]  c2 03 5b
col  1: [ 3]  53 59 53
col  2: [10]  49 5f 4a 4f 42 5f 4e 45 58 54

4. 使用bbed查看该block 1,2,和最后一条记录

[oracle@localhost ~]$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:25:28 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set block 39459
        BLOCK#          39459

BBED> map
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459                                 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[289]                              @142     

 ub1 freespace[821]                         @720     

 ub1 rowdata[6647]                          @1541    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[6631]
-------------
ub1 rowdata[6631]                           @8172     0x2c

BBED> x /rncc
rowdata[6631]                               @8172    
-------------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x00
cols@8174:    3

col    0[2] @8175: 20 
col    1[3] @8178: SYS
col    2[5] @8182: ICOL$


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8172 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c000302 c1150353 59530549 434f4c24 02067576 

 <32 bytes per line>

BBED> p *kdbr[1]
rowdata[6613]
-------------
ub1 rowdata[6613]                           @8154     0x2c

BBED> x /rncc
rowdata[6613]                               @8154    
-------------
flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8155: 0x00
cols@8156:    3

col    0[2] @8157: 46 
col    1[3] @8160: SYS
col    2[7] @8164: I_USER1


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8154 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c000302 c12f0353 59530749 5f555345 52312c00 0302c115 03535953 0549434f 
 4c240206 7576 

 <32 bytes per line>

BBED> p *kdbr[288]
rowdata[0]
----------
ub1 rowdata[0]                              @1541     0x2c

BBED> x /rncc
rowdata[0]                                  @1541    
----------
flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1542: 0x00
cols@1543:    3

col    0[3] @1544: 290 
col    1[3] @1548: SYS
col   2[10] @1552: I_JOB_NEXT


BBED> set count 32
        COUNT           32

BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 1541 to 1572           Dba:0x00000000
------------------------------------------------------------------------
 2c000303 c2035b03 5359530a 495f4a4f 425f4e45 58542c00 0303c203 5a035359 

 <32 bytes per line>

5. 删除中间列,并且写入硬盘

SQL> ALTER TABLE XFF.T_XIFENFEI DROP COLUMN owner;

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

6. 查询确定相同行所在block没有发生改变

SQL> select   rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno,object_id
  4    5  from xff.t_xifenfei where rownum<5;

ROWID                 REL_FNO    BLOCKNO      ROWNO  OBJECT_ID
------------------ ---------- ---------- ---------- ----------
AAAZ9wAAEAAAJojAAA          4      39459          0         20
AAAZ9wAAEAAAJojAAB          4      39459          1         46
AAAZ9wAAEAAAJojAAC          4      39459          2         28
AAAZ9wAAEAAAJojAAD          4      39459          3         15

7. drop col之后dump block继续分析

SQL> alter system dump datafile 4 block 39459;

System altered.

SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------
/home/u01/diag/rdbms/orcl/orcl/trace/orcl_ora_14784.trc

SQL> 

tab 0, row 0, @0x1f70
tl: 12 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f5e
tl: 14 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
…………
tab 0, row 288, @0x589
tl: 18 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 3]  c2 03 5b
col  1: [10]  49 5f 4a 4f 42 5f 4e 45 58 54

8. 使用bbed查看drop col后的数据存储情况

$ bbed password=blockedit blocksize=8192 filename=/usr/local/oradata/qsng/users01.dbf 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Apr 3 22:31:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set block 39459
        BLOCK#          39459

BBED> map 
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459                                 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[289]                              @142     

 ub1 freespace[821]                         @720     

 ub1 rowdata[6647]                          @1541    

 ub4 tailchk                                @8188    


BBED> p *kdbr[0]
rowdata[6631]
-------------
ub1 rowdata[6631]                           @8172     0x2c

BBED> x /rncc
rowdata[6631]                               @8172    
-------------
flag@8172: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8173: 0x02
cols@8174:    2

col    0[2] @8175: 20 
col    1[5] @8178: ICOL$


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8172 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c020202 c1150549 434f4c24 434f4c24 0106de78 

 <32 bytes per line>

BBED> p *kdbr[1]
rowdata[6613]
-------------
ub1 rowdata[6613]                           @8154     0x2c

BBED> x /rncc
rowdata[6613]                               @8154    
-------------
flag@8154: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8155: 0x02
cols@8156:    2

col    0[2] @8157: 46 
col    1[7] @8160: I_USER1


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 8154 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 2c020202 c12f0749 5f555345 52315345 52312c02 0202c115 0549434f 4c24434f 
 4c240106 de78 

 <32 bytes per line>

BBED> p *kdbr[288]
rowdata[0]
----------
ub1 rowdata[0]                              @1541     0x2c

BBED> set count 32
        COUNT           32

BBED> x /rncc
rowdata[0]                                  @1541    
----------
flag@1541: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1542: 0x02
cols@1543:    2

col    0[3] @1544: 290 
col   1[10] @1548: I_JOB_NEXT


BBED> d
 File: /usr/local/oradata/qsng/users01.dbf (0)
 Block: 39459            Offsets: 1541 to 1572           Dba:0x00000000
------------------------------------------------------------------------
 2c020203 c2035b0a 495f4a4f 425f4e45 58544e45 58542c02 0203c203 5a09495f 

 <32 bytes per line>

通过上述测试可以得出如下结论:
1. drop col是真的把对应列存储在block中的内容除掉,而且把后面的列的内容前移了,并且以前多于的内容(因为一行内容前移,后面就出现空闲记录不设置为空,而就是最初内容,下次如果行长度发生改变的时候使用,就和类似update把列修改短了一样)
2. drop col只是导致一行的长度变短,但是每行的偏移量未发生改变,也就是说,每行所在的偏移量没有改变,drop col之后,每行后面多了一些空闲空间
3. 根据上面分析的原理,drop col 是真的从block内部把这一列的数据使用后面列的数据覆盖了,因此从原理上而言,dul无法恢复drop col的数据(最后一列有可能可以恢复,因为他不会被覆盖),对于drop col,只能是通过备份不完全恢复,全库闪回,dg延迟应用等方法解决

发表在 Oracle | 标签为 , | 留下评论

ADHU(ASM Disk Header Utility)—asm disk header备份恢复工具

adhu(ASM Disk Header Utility)作为oracle asm中和kfed,amdu齐名的asm三大恢复神器之一,没有被oracle大力推广(属于内部工具),随着kfed功能增强和asm disk header自动备份功能的完善,adhu oracle基本上停止的开发支持,可以用来作为10.2.0.5之前asm版本的磁盘头保护工具
adhu预览
这里可以通过shell封装的utildhu调用adhu程序,实现更加人性化和自动化操作,它含有install,check,repair三个命令参数.

[root@xff1 tmp]# su - grid
xff1:/home/grid> cd /tmp/adhu/
xff1:/tmp/adhu> ls -l
total 68
-rwxr-xr-x 1 grid oinstall 18902 Nov  1  2008 adhu
-rw-r--r-- 1 grid oinstall  1970 Nov  1  2008 README
-rwxr-xr-x 1 grid oinstall  6964 Mar 21 16:20 utildhu
-rw-r--r-- 1 root root     12634 Mar 21 16:05 utildhu.zip
xff1:/tmp/adhu> ./utildhu 
Usage: utildhu install/check/repair [device name]

$utildhu install 
Will gather a list of member ASM disks and create
the backup directory  ./HeaderBackup
The ./HeaderBackup directory will contain the 
backup header of every asm disk in this database

$utildhu  check
Will run /tmp/adhu/adhu for every disk discovered by
$utildhu install and will email recipients configured 
in RECIPIENTS if there are errors in the disk header

It is hoped that the user will enter valid RECIPIENT
email addresses, and will place this utility in $ORA_ASM_HOME

$utildhu  repair <device name>
Will repair the device provided using the backup 
header blocks that have been copied previously.
This does assume that you have backup header blocks
in ./HeaderBackup

Sample crontab entry to run a check every 5 minutes
#Minute (0-59)  Hour (0-23)  Day of Month (1-31)  
Month (1-12 or Jan-Dec)  Day of Week (0-6 or Sun-Sat)  Command
0,5,10,15,20,25,30,35,40,45,50,55 * * * * *  utildhu check

Please read the README for more information

adhu install
install主要是实现utildhu.config配置文件生成和第一次asm 磁盘头备份

xff1:/tmp/adhu> ./utildhu install
xff1:/tmp/adhu> ls -l
total 64
-rwxr-xr-x 1 grid oinstall 18902 Nov  1  2008 adhu
drwxr-xr-x 2 grid oinstall  4096 Mar 21 16:23 HeaderBackup
-rw-r--r-- 1 grid oinstall  1117 Mar 21 16:23 persistent-log.utildhu
-rw-r--r-- 1 grid oinstall  1970 Nov  1  2008 README
-rwxr-xr-x 1 grid oinstall  6964 Mar 21 16:20 utildhu
-rw-r--r-- 1 grid oinstall   243 Mar 21 16:23 utildhu.config
-rw-r--r-- 1 grid oinstall   710 Mar 21 16:23 utildhu.out
-rw-r--r-- 1 root root     12634 Mar 21 16:05 utildhu.zip
xff1:/tmp/adhu> cd HeaderBackup/
xff1:/tmp/adhu/HeaderBackup> ls -ltr
total 12
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 oradata2p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 16:23 ocrvotep1
xff1:/tmp/adhu/HeaderBackup> more ../utildhu.config 
/dev/mapper/oradata1p1                                                          
/dev/mapper/oradata2p1                                                          
/dev/mapper/ocrvotep1                                                           
xff1:/tmp/adhu>  more ../persistent-log.utildhu
Mon Mar 21 16:23:29 CST 2016
ASM Disk Header Check Utility Installed on 
Devices configured are:
/dev/mapper/oradata1p1                                                          
/dev/mapper/oradata2p1                                                          
/dev/mapper/ocrvotep1                                                           
ADHU: /dev/mapper/oradata1p1: Status 0x01 Mon Mar 21 16:23:29 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: valid disk header found
ADHU: /dev/mapper/oradata1p1: backup block updated

###ADHU check run at Mon Mar 21 16:23:29 CST 2016  NO ERRORS FOUND

xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 oradata2p1

adhu check
对于正常的asm disk,主要是为了生成新的磁盘头备份

xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:04 oradata2p1
xff1:/tmp/adhu> ./utildhu check
xff1:/tmp/adhu> ls -l HeaderBackup/
total 16
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 ocrvotep1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 oradata1p1
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:08 oradata1p1.corrupt
-rw-r--r-- 1 grid oinstall 4096 Mar 21 23:11 oradata2p1

adhu repair
repair主要是修复磁盘头,当asm 磁盘头损坏之时,可以通过这个命令实现磁盘头修复

xff1:/tmp/adhu> dd if=/dev/zero of=/dev/mapper/oradata1p1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000282838 s, 14.5 MB/s
xff1:/tmp/adhu> ./utildhu check
xff1:/tmp/adhu> tail -f persistent-log.utildhu

###ADHU check run at Mon Mar 21 23:04:04 CST 2016  ERRORS FOUND
ADHU: /dev/mapper/oradata1p1: Status 0x08 Mon Mar 21 23:04:04 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: corrupt disk header encountered
ADHU: /dev/mapper/oradata1p1: valid backup block found
ADHU: /dev/mapper/oradata1p1: CORRUPT HEADER NOT REPAIRED

xff1:/tmp/adhu> kfed read /dev/mapper/oradata1p1 
kfbh.endian:                          0 ; 0x000: 0x00
kfbh.hard:                            0 ; 0x001: 0x00
kfbh.type:                            0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt:                          0 ; 0x003: 0x00
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:                       0 ; 0x008: file=0
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
7F6BD9981400 00000000 00000000 00000000 00000000  [................]
  Repeat 255 times
KFED-00322: Invalid content encountered during block traversal: [kfbtTraverseBlock][Invalid OSM block type][][0]

xff1:/tmp/adhu> sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 23:07:25 2016

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 Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data dismount;
alter diskgroup data dismount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15027: active use of diskgroup "DATA" precludes its dismount


SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15013: diskgroup "DATA" is already mounted


SQL>  alter diskgroup data dismount force;

Diskgroup altered.

SQL> alter diskgroup data mount;
alter diskgroup data mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15040: diskgroup is incomplete


xff1:/tmp/adhu> ./utildhu repair /dev/mapper/oradata1p1 
DEVICE  /dev/mapper/oradata1p1 REPAIRED AT  Mon Mar 21 23:06:06 CST 2016
ADHU: /dev/mapper/oradata1p1: Status 0x04 Mon Mar 21 23:06:06 2016
ADHU: /dev/mapper/oradata1p1: Diskgroup:DATA Disk:DATA_0000 #0
ADHU: /dev/mapper/oradata1p1: corrupt disk header encountered
ADHU: /dev/mapper/oradata1p1: valid backup block found
ADHU: /dev/mapper/oradata1p1: disk header repaired

xff1:/tmp/adhu> sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 23:08:48 2016

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 Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup data mount;

Diskgroup altered.

adhu直接使用
adhu [-dir dirname ] [-repair] [-quiet] [-readonly] [-syslog mask ] devname
默认情况下adhu将disk header备份为当前目录下的备份文件。 使用-dir选项可以指定存放的目录。
当需要使用adhu去修复一个损坏的asm disk header时使用-repair 选项。
-quiet 选项将过滤所有正常的输出信息,若执行成功则不打印任何输出。
-readonly选项 以只读方式来打开disk device,这样备份block将不被写入,而备份文件将在可能的情况下写入。
-syslog选项控制是否写出结果到系统日志和标准输出。
devname代表为asm disk的设备文件,asm头的备份文件将以该device name为基础,并存放在当前目录或者-dir指定的目录。

发表在 Oracle ASM | 标签为 , , , , | 留下评论