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

file 1 block 128 corrupted/坏块恢复—system rollback坏块修复

有个数据库file 1 block 128 坏块导致数据库无法启动报错如下
file-1-block-128

该数据库版本是11.2.0.1,根据我们的经验该block是system rollback 的segment header,以下为我在正常哭查询结果

SQL> select file_id,block_id,blocks from dba_extents where segment_name='SYSTEM';
   FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         1        128          8
         1        136          8
         1        528          8
         1        536          8
         1        544          8
         1        552          8
6 rows selected.

dump file 1 block 128 结果

Dump all the blocks in range:
buffer tsn: 0 rdba: 0x00400080 (1/128)
scn: 0x0000.00000000 seq: 0xff flg: 0x04 tail: 0x00000eff
frmt: 0x02 chkval: 0x1387 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Hex dump of block: st=0, typ_found=1

这里可以看到block scn为0x0000.00000000，而且数据块已经被标记为坏块
dbv检查坏块结果
dbv

从这里可以看出来主要错误是由于Controlscn: 0x0004.119fe191 greater than blockscn: 0x0000.00000000,拷贝system文件到本地，使用bbed修改

bbed修复坏块

H:\temp\SYSTEM01>bbed password=blockedit filename=system01.dbf blocksize=8192
BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 17 00:23:49 2016
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all
        FILE#           0
        BLOCK#          1
        OFFSET          0
        DBA             0x00000000 (0 0,1)
        FILENAME        system01.dbf
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       8192
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> set block 129
        BLOCK#          129
BBED> map
 File: system01.dbf (0)
 Block: 129                                   Dba:0x00000000
------------------------------------------------------------
 Unlimited Undo Segment Header
 struct kcbh, 20 bytes                      @0
 struct ktech, 72 bytes                     @20
 struct ktemh, 16 bytes                     @92
 struct ktetb[6], 48 bytes                  @108
 struct ktuxc, 104 bytes                    @4148
 struct ktuxe[204], 8160 bytes              @4252
 ub4 tailchk                                @8188
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   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       0x1387
   ub2 spare3_kcbh                          @18       0x0000
BBED> set mode edit
        MODE            Edit
BBED> d offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 ff0e0000
 <32 bytes per line>
BBED> m /x 01 offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e0000
 <32 bytes per line>
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> sum apply
Check value for File 0, Block 129:
current = 0x1387, required = 0x1387
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> exit
H:\temp\SYSTEM01>dbv file=SYSTEM01.DBF
DBVERIFY: Release 12.1.0.2.0 - Production on 星期四 3月 17 00:26:26 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = H:\TEMP\SYSTEM01\SYSTEM01.DBF
Controlscn: 0x0004.119fe191 greater than blockscn: 0x0000.00000000
页 128 失败, 校验代码为 14509
页 128 失败, 校验代码为 14509
DBVERIFY - 验证完成
检查的页总数: 209920
处理的页总数 (数据): 132380
失败的页总数 (数据): 0
处理的页总数 (索引): 57168
失败的页总数 (索引): 0
处理的页总数 (其他): 3112
处理的总页数 (段)  : 1
失败的总页数 (段)  : 1
空的页总数: 17260
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 188826853 (5.188826853)
H:\temp\SYSTEM01>bbed password=blockedit filename=system01.dbf
BBED: Release 2.0.0.0.0 - Limited Production on Thu Mar 17 00:26:59 2016
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 129
        BLOCK#          129
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> map
 File: system01.dbf (0)
 Block: 129                                   Dba:0x00000000
------------------------------------------------------------
 Unlimited Undo Segment Header
 struct kcbh, 20 bytes                      @0
 struct ktech, 72 bytes                     @20
 struct ktemh, 16 bytes                     @92
 struct ktetb[6], 48 bytes                  @108
 struct ktuxc, 104 bytes                    @4148
 struct ktuxe[204], 8160 bytes              @4252
 ub4 tailchk                                @8188
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000
BBED> set mode edit
        MODE            Edit
BBED> m /x 0400 offset 12
 File: system01.dbf (0)
 Block: 129              Offsets:   12 to  523           Dba:0x00000000
------------------------------------------------------------------------
 04000104 87130000 00000000 00000000 00000000 00000000 06000000 2f000000
 20100000 05000000 05000000 08000000 2d024000 00000000 05000000 00000000
 00000000 00000000 00000000 00000000 06000000 00000000 00000000 00000040
 81004000 07000000 88004000 08000000 10024000 08000000 18024000 08000000
 20024000 08000000 28024000 08000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0004
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000
BBED> m /x 625a60d0 offset 8
 File: system01.dbf (0)
 Block: 129              Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 625a60d0 10000104 87130000 00000000 00000000 00000000 00000000 06000000
 2f000000 20100000 05000000 05000000 08000000 2d024000 00000000 05000000
 00000000 00000000 00000000 00000000 00000000 06000000 00000000 00000000
 00000040 81004000 07000000 88004000 08000000 10024000 08000000 18024000
 08000000 20024000 08000000 28024000 08000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x0e
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x00400080
   ub4 bas_kcbh                             @8        0xd0605a62
   ub2 wrp_kcbh                             @12       0x0010
   ub1 seq_kcbh                             @14       0x01
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x1387
   ub2 spare3_kcbh                          @18       0x0000
BBED> d offset 8188
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e0000
 <32 bytes per line>
BBED> m /x 010e625a
 File: system01.dbf (0)
 Block: 129              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010e625a
 <32 bytes per line>
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128
Block 128 is corrupt
***
Corrupt block relative dba: 0x00400080 (file 0, block 128)
Bad check value found during verification
Data in bad block -
 type: 14 format: 2 rdba: 0x00400080
 last change scn: 0x0010.34605a62 seq: 0x1 flg: 0x04
 consistency value in tail: 0x5a620e01
 check value in block header: 0x1387, computed block checksum: 0x3470
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0
BBED> sum apply
Check value for File 0, Block 129:
current = 0x27f7, required = 0x27f7
BBED> verify
DBVERIFY - Verification starting
FILE = system01.dbf
BLOCK = 128
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> exit
H:\temp\SYSTEM01>dbv file=SYSTEM01.DBF
DBVERIFY: Release 12.1.0.2.0 - Production on 星期四 3月 17 00:40:38 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = H:\TEMP\SYSTEM01\SYSTEM01.DBF
DBVERIFY - 验证完成
检查的页总数: 209920
处理的页总数 (数据): 132380
失败的页总数 (数据): 0
处理的页总数 (索引): 57168
失败的页总数 (索引): 0
处理的页总数 (其他): 3112
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 17260
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 188826853 (5.188826853)

这里发现当我们bbed验证非坏块之时,使用dbv检测依旧报坏块,可以看出来dbv的验证比bbed更加严格

分区无法识别导致asm diskgroup无法mount

有客户咨询由于主机重启之后，导致四个磁盘组中的data2磁盘组无法mount(报ORA-15032,ORA-15017,ORA-15063),数据库无法open,让我们帮忙分析解决

Wed Mar 09 18:10:53 2016
NOTE: Assigning number (1,1) to disk (/dev/oracleasm/disks/VOL011)
Wed Mar 09 18:10:53 2016
ERROR: no read quorum in group: required 1, found 0 disks
NOTE: cache dismounting (clean) group 1/0xBD42B778 (DATA2)
NOTE: messaging CKPT to quiesce pins Unix process pid: 45093, image: oracle@BA (TNS V1-V3)
NOTE: dbwr not being msg'd to dismount
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0xBD42B778 (DATA2)
NOTE: cache ending mount (fail) of group DATA2 number=1 incarn=0xbd42b778
NOTE: cache deleting context for group DATA2 1/0xbd42b778
GMON dismounting group 1 at 16 for pid 18, osid 45093
NOTE: Disk DATA2_0001 in mode 0x9 marked for de-assignment
ERROR: diskgroup DATA2 was not mounted
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA2" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA2"
ERROR: ALTER DISKGROUP DATA2 MOUNT  /* asm agent *//* {0:0:431} */

这里很明显由于缺少asm disk导致data2无法正常mount,进一步分析发现data2是有两块磁盘组成

Mon Sep 14 13:14:35 2015
SQL> create diskgroup data2 external redundancy  disk '/dev/oracleasm/disks/VOL010','/dev/oracleasm/disks/VOL011'
NOTE: Assigning number (4,0) to disk (/dev/oracleasm/disks/VOL010)
NOTE: Assigning number (4,1) to disk (/dev/oracleasm/disks/VOL011)
NOTE: initializing header on grp 4 disk DATA2_0000
NOTE: initializing header on grp 4 disk DATA2_0001
NOTE: initiating PST update: grp = 4
Mon Sep 14 13:14:35 2015
GMON updating group 4 at 29 for pid 26, osid 51535
NOTE: group DATA2: initial PST location: disk 0000 (PST copy 0)
NOTE: PST update grp = 4 completed successfully
NOTE: cache registered group DATA2 number=4 incarn=0xea085f62
NOTE: cache began mount (first) of group DATA2 number=4 incarn=0xea085f62
NOTE: cache opening disk 0 of grp 4: DATA2_0000 path:/dev/oracleasm/disks/VOL010
NOTE: cache opening disk 1 of grp 4: DATA2_0001 path:/dev/oracleasm/disks/VOL011
NOTE: cache creating group 4/0xEA085F62 (DATA2)
NOTE: cache mounting group 4/0xEA085F62 (DATA2) succeeded
NOTE: allocating F1X0 on grp 4 disk DATA2_0000
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting (clean) group 4/0xEA085F62 (DATA2)
NOTE: messaging CKPT to quiesce pins Unix process pid: 51535, image: oracle@BA (TNS V1-V3)
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 4/0xEA085F62 (DATA2)
GMON dismounting group 4 at 30 for pid 26, osid 51535
GMON dismounting group 4 at 31 for pid 26, osid 51535
NOTE: Disk DATA2_0000 in mode 0x7e marked for de-assignment
NOTE: Disk DATA2_0001 in mode 0x7e marked for de-assignment
SUCCESS: diskgroup DATA2 was created

结合这部分信息,我们可以确定data2磁盘组是由两个磁盘组构成,分别为VOL010和VOL011,现在由于只发现了VOL011,因此data2磁盘组无法正常mount.观察发现该系统使用的是asmlib,通过oracleasm querydisk命令结合fdisk的盘符,
oracleasm-querydisk

基本上可以确定VOL010丢失应该在mpathb盘(由于只有该盘和分区未被使用,其他盘和分区已经全部被现在可以查询到的asmlib使用作为asmdisk)之上

Disk /dev/mapper/mpathb: 3846.7 GB, 3846677987328 bytes
255 heads, 63 sectors/track, 467665 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000
             Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1               1      267350  2147483647+  ee  GPT
Disk /dev/mapper/mpathbp1: 3846.7 GB, 3846675890176 bytes
255 heads, 63 sectors/track, 467665 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xb84bb99a
                Device Boot      Start         End      Blocks   Id  System
/dev/mapper/mpathbp1p1               1      200513  1610620641   83  Linux
/dev/mapper/mpathbp1p2          200514      267349   536860170   83  Linux
/dev/mapper/mpathbp1p3          267350      467665  1609038270   83  Linux

这里我们发现奇怪现象:mpathb盘先使用parted分为一个mapthbp1分区,然后又使用fdisk分了三个p1p1,p1p2,p1p3三个子分区.然后我们查看/dev/mapper/中的设备情况
mpathb

发现没有p1p1,p1p2,p1p3这三个本该属于mapthb上的子分区.现在基本上明确,是由于对mapthb先使用了parted分区,然后再使用fdisk分区,在操作系统重启之后,无法正常识别相关子分区导致该问题.到此解决该问题的思路有三种.
1. 因为磁盘分区表信息是正常的,就是分区表信息没有同步到操作系统之上,想办法同步过去即可,os部分内容,此处忽略
2. 使用数据文件重组的方式直接对data2这两个asm disk进行重组,这里因为三个子分区未发现,直接对mapthbp1分区进行扫描即可,参考:asm disk header 彻底损坏恢复
3. 因为分区对于asm disk来说主要就是设置了磁盘的偏移量和大小,如果找到磁盘的偏移量,然后确定asm disk大小,直接通过dd命令把该部分dd到新的磁盘设备之上,然后直接mount磁盘组即可,这里重点讲解第三种方法恢复处理
使用dd出来mapthp1的磁盘头,然后使用bbed找出来偏移量,主要依据是第一次出现01820101信息的部分

BBED> d
 File: bp1 (0)
 Block: 64               Offsets:    0 to   63           Dba:0x00000000
------------------------------------------------------------------------
 01820101 00000000 00000080 bc60223c 00000000 00000000 00000000 00000000
 4f52434c 4449534b 564f4c30 31300000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> show all
        FILE#           0
        BLOCK#          64
        OFFSET          0
        DBA             0x00000000 (0 0,64)
        FILENAME        bp1
        BIFILE          bifile.bbd
        LISTFILE
        BLOCKSIZE       512
        MODE            Browse
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           64
        LOGFILE         log.bbd
        SPOOL           No

这里基本上可以定位到asm disk header对于mapthbp1的偏移量为32256,dd出来asm disk header分析

使用kfed查看磁盘头信息
kfed
kfed2

现在基本上可以确定,asm disk大小为1572871M,磁盘的偏移量为32256,然后使用dd命令把这部分dd到新的磁盘设备上,然后oracleasm scandisks后
asm-mount
asmcd-lsdg
oracle-open

data2 mount成功,数据库正常open,此数据库完美恢复
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

Solaris rm datafile recovery—利用句柄误删除数据文件恢复

今天早上接到有客户恢复请求，他一不小心在solaris系统中使用rm -rf oradata命令把一个分区下面的所有数据文件全部删除了。现在不知道怎么办,请求我们给予支持.上去检查发现比较幸运,数据库没有crash,看来直接考虑使用句柄进行恢复

root@CNISORCLSVR # uname -a
SunOS CNISORCLSVR 5.9 Generic_112233-08 sun4u sparc SUNW,Sun-Fire-880
root@CNISORCLSVR # ps -ef|grep lgwr
  oracle   597     1  0   Mar 05 ?       17:14 ora_lgwr_xifenfei
    root 28069 28043  0 18:51:17 pts/2    0:00 grep lgwr
root@CNISORCLSVR # ls -ltr
total 189348454
-r--r--r--   1 oracle   dba       657920 Apr 26  2002 12
c---------   1 root     sys       13, 12 Mar 27  2004 8
c---------   1 root     sys       13, 12 Mar 27  2004 10
-rw-r-----   0 oracle   dba      34359730176 Nov 12  2013 291
-rw-r-----   0 oracle   dba      1073750016 Nov 13  2013 293
D---------   1 root     root           0 Mar  5 19:31 11
-rw-r-----   1 oracle   dba         1758 Mar  5 22:04 9
-rw-rw----   1 oracle   dba           24 Mar  5 22:04 13
s---------   0 root     root           0 Mar  8 00:45 14
-rw-r-----   1 oracle   dba      1887444992 Mar 12 03:27 289
-rw-r-----   1 oracle   dba      943726592 Mar 12 11:17 290
-rw-r-----   0 oracle   dba      4294975488 Mar 13 00:09 292
-rw-r-----   0 oracle   dba      268443648 Mar 13 01:33 288
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 279
-rw-r-----   1 oracle   dba      134225920 Mar 13 01:33 278
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 269
-rw-r-----   1 oracle   dba      268443648 Mar 13 01:33 267
-rw-r-----   1 oracle   dba      148119552 Mar 13 01:33 266
-rw-r-----   1 oracle   dba      10493952 Mar 13 01:33 265
-rw-r-----   1 oracle   dba      26222592 Mar 13 01:33 264
-rw-r-----   1 oracle   dba      62922752 Mar 13 01:33 263
-rw-r-----   1 oracle   dba      20979712 Mar 13 01:33 262
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 287
-rw-r-----   1 oracle   dba      209723392 Mar 13 01:33 285
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 283
-rw-r-----   1 oracle   dba      67117056 Mar 13 01:33 282
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 281
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 280
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 276
-rw-r-----   0 oracle   dba      1073750016 Mar 13 01:33 275
-rw-r-----   0 oracle   dba      2214600704 Mar 13 01:33 274
-rw-r-----   0 oracle   dba      134225920 Mar 13 01:33 273
-rw-r-----   0 oracle   dba      536879104 Mar 13 01:33 272
c---------   1 root     sys       13,  2 Mar 13 02:00 5
c---------   1 root     sys       13,  2 Mar 13 02:00 4
c---------   1 root     sys       13,  2 Mar 13 02:00 3
c---------   1 root     sys       13,  2 Mar 13 02:00 2
c---------   1 root     sys       13,  2 Mar 13 02:00 1
c---------   1 root     sys       13,  2 Mar 13 02:00 0
--w-------   1 oracle   dba      4640842 Mar 13 04:43 7
--w-------   1 oracle   dba      4640842 Mar 13 04:43 6
-rw-r-----   0 oracle   dba      1207967744 Mar 13 18:21 271
-rw-r-----   0 oracle   dba      15929974784 Mar 13 18:39 284
-rw-r-----   0 oracle   dba      134225920 Mar 13 18:45 277
-rw-r-----   0 oracle   dba      2122326016 Mar 13 18:46 286
-rw-r-----   0 oracle   dba      9261031424 Mar 13 18:47 270
-rw-r-----   0 oracle   dba      18253619200 Mar 13 18:47 268
-rw-r-----   1 oracle   dba      134225920 Mar 13 18:51 261
-rw-r-----   1 oracle   dba      524296192 Mar 13 18:51 260
-rw-r-----   1 oracle   dba      104858112 Mar 13 18:52 259
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 258
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 257
-rw-r-----   1 oracle   dba      1941504 Mar 13 18:52 256
SQL> select file#,name from v$datafile wehre name like '/disk%';
     FILE# NAME
---------- --------------------------------------------------
         9 /disk/oradata/xifenfei/xifenfei.dbf
        10 /disk/oradata/xifenfei/CSSN.dbf
        11 /disk/oradata/xifenfei/NCSSN.dbf
        12 /disk/oradata/xifenfei/CSIC_RDS.dbf
        13 /disk/oradata/xifenfei/CSIC_CSSN.dbf
        14 /disk/oradata/xifenfei/CNIS_I.dbf
        15 /disk/oradata/xifenfei/CNIS.dbf
        16 /disk/oradata/xifenfei/TRSWCM6_CSSN.dbf
        17 /disk/oradata/xifenfei/TRSWCM6_CSSN_PLUGINS.dbf
        18 /disk/oradata/xifenfei/DIGIREF.dbf
        20 /disk/oradata/xifenfei/TRSWCM.dbf
        21 /disk/oradata/xifenfei/TRSWCM52_NSLC.dbf
        22 /disk/oradata/xifenfei/TRSWCM52_PLUGINS_NSLC.dbf
        24 /disk/oradata/xifenfei/TRSWCM_PLUGINS.dbf
        25 /disk/oradata/xifenfei/CNIS_ALL.dbf
        27 /disk/oradata/xifenfei/undotbs01.dbf
        28 /disk/oradata/xifenfei/TRS_IDS02.dbf
        29 /disk/oradata/xifenfei/xdb02.dbf

在solaris中比较郁闷,虽然进入了fd目录,但是无法知道哪些文件句柄是删除,哪些是正常的,因此没有办法，只能使用lsof进一步分析

root@CNISORCLSVR # pkgadd -d lsof-4.80-sol9-sparc-local
The following packages are available:
  1  IBMlsof     lsof
                 (sparc) 4.80
Select package(s) you wish to process (or 'all' to process
all packages). (default: all) [?,??,q]: all
Processing package instance <IBMlsof> from </tmp/lsof-4.80-sol9-sparc-local>
lsof
(sparc) 4.80
Vic Abell
Using </usr/local> as the package base directory.
## Processing package information.
## Processing system information.
## Verifying disk space requirements.
## Checking for conflicts with packages already installed.
The following files are already installed on the system and are being
used by another package:
* /usr/local/bin <attribute change only>
* - conflict with a file which does not belong to any package.
Do you want to install these conflicting files [y,n,?,q] y
## Checking for setuid/setgid programs.
The following files are being installed with setuid and/or setgid
permissions:
 /usr/local/bin/lsof <setgid sys>
 /usr/local/bin/sparcv7/lsof <setgid sys>
 /usr/local/bin/sparcv9/lsof <setgid sys>
Do you want to install these as setuid/setgid files [y,n,?,q] y
Installing lsof as <IBMlsof>
## Installing part 1 of 1.
/usr/local/bin/lsof
/usr/local/bin/sparcv7/lsof
/usr/local/bin/sparcv9/lsof
/usr/local/doc/lsof/00.README.FIRST
/usr/local/doc/lsof/00CREDITS
/usr/local/doc/lsof/00DCACHE
/usr/local/doc/lsof/00DIALECTS
/usr/local/doc/lsof/00DIST
/usr/local/doc/lsof/00FAQ
/usr/local/doc/lsof/00LSOF-L
/usr/local/doc/lsof/00MANIFEST
/usr/local/doc/lsof/00PORTING
/usr/local/doc/lsof/00QUICKSTART
/usr/local/doc/lsof/00README
/usr/local/doc/lsof/00TEST
/usr/local/doc/lsof/00XCONFIG
/usr/local/doc/lsof/lsof.man
/usr/local/man/man8/lsof.8
[ verifying class <none> ]
Installation of <IBMlsof> was successful.
root@CNISORCLSVR # ./lsof -p 597
COMMAND PID   USER   FD   TYPE        DEVICE    SIZE/OFF   NODE NAME
oracle  597 oracle  cwd   VDIR          85,5        2048 106299 /export/home/oracle/app/product/9.2.0/dbs
oracle  597 oracle  txt   VREG          85,5    61272672   2332 /export/home/oracle/app/product/9.2.0/bin/oracle
…………
oracle  597 oracle  260u  VREG          85,5   524296192 106517 /export/home/oracle/oradata/xifenfei/system01.dbf
oracle  597 oracle  261u  VREG          85,5   134225920 106518 /export/home/oracle/oradata/xifenfei/undotbs01.dbf
…………
oracle  597 oracle  268u  VREG        118,70 18253619200  109 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle  597 oracle  269u  VREG        118,70   134225920  110 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
oracle  597 oracle  270u  VREG        118,70  9261031424  111 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)
…………
oracle  597 oracle  293u  VREG        118,70  1073750016   14 /disk (/dev/dsk/c2t600A0B800029CEFA0000036C491B270Bd0s6)

到这一步,基本上定位/disk部分是我们需要恢复的数据,从而可以定位到句柄,然后结合数据文件信息,直接使用cp命令恢复出来文件.然后数据库层面recover并且online.

cd /proc/597/fd
cp 269 /disk/oradata/cnisora2/CSSN.dbf
chown oracle:dba /disk/oradata/xifenfei/CSSN.dbf
SQL> recover datafile 10;
ORA-00283: 恢复会话因错误而取消
ORA-01124: 无法恢复数据文件 10 - 文件在使用中或在恢复中
ORA-01110: 数据文件 10: '/disk/oradata/xifenfei/CSSN.dbf'
SQL> alter database datafile 10 offline;
数据库已更改。
SQL> recover datafile 10;
完成介质恢复。
SQL> alter database datafile 10 online;
数据库已更改。

至此基本上恢复完成,万幸是数据库没有crash,遇到此类问题,千万不要盲目关闭数据库.另外数据库备份重于一切

exp跳过某些表导出数据

有一个需求，某个用户有很多张表，但是只能使用exp导出，而且想跳过其中某几张表，其他对象包括依赖关系都需要。针对这样的情况，通过分析exp的视图脚本，在exu10tabs视图进行修改，跳过某些表即可
修改exu10tabs视图
本测试为11.2.0.4版本，不同版本数据库，可能视图名称和语句有一定出入，请别照搬,exu10tabs在$ORACLE_HOME/rdbms/admin/catexp.sql中

CREATE OR REPLACE VIEW exu10tabs (
                objid, dobjid, name, owner, ownerid, tablespace, tsno, fileno,
                blockno, audit$, comment$, clusterflag, mtime, modified, tabno,
                pctfree$, pctused$, initrans, maxtrans, degree, instances,
                cache, tempflags, property, deflog, tsdeflog, roid, recpblk,
                secondaryobj, rowcnt, blkcnt, avgrlen, tflags, trigflag,
                objstatus, xdbool)
      AS                                                      /* Heap tables */
        SELECT
                o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
                t$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
                NVL(t$.bobj#, 0), o$.mtime,
                DECODE(BITAND(t$.flags, 1), 1, 1, 0), NVL(t$.tab#, 0),
                MOD(t$.pctfree$, 100), t$.pctused$, t$.initrans, t$.maxtrans,
                NVL(t$.degree, 1), NVL(t$.instances, 1),
                DECODE(BITAND(t$.flags, 8), 8, 1, 0),
                MOD(TRUNC(o$.flags / 2), 2), t$.property,
                DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
                t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
                NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
                t$.flags, t$.trigflag, o$.status,
                (SELECT COUNT(*)
                    FROM sys.opqtype$ opq$
                    WHERE opq$.obj# = o$.obj# AND
                          BITAND(opq$.flags, 32) = 32 )
        FROM    sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
                sys.com$ c$
        WHERE   t$.obj# = o$.obj# AND
                t$.ts# = ts$.ts# AND
                u$.user# = o$.owner# AND
                o$.obj# = c$.obj#(+) AND
                c$.col#(+) IS NULL AND
                BITAND(o$.flags,128) != 128 AND      /* Skip recycle bin */
                BITAND(t$.property, 64+512) = 0 AND /*skip IOT and ovflw segs*/
                BITAND(t$.flags, 536870912) = 0    /* skip IOT mapping table */
                and o$.name not in('T_XIFENFEI','T_ORASOS')  --增加需要跳过表
      UNION ALL                                         /* Index-only tables */
        SELECT  o$.obj#, o$.dataobj#, o$.name, u$.name, o$.owner#, ts$.name,
                i$.ts#, t$.file#, t$.block#, t$.audit$, c$.comment$,
                NVL(t$.bobj#, 0), o$.mtime,
                DECODE(BITAND(t$.flags, 1), 1, 1, 0),
                NVL(t$.tab#, 0), 0, 0, 0, 0,
                NVL(t$.degree, 1), NVL(t$.instances, 1),
                DECODE(BITAND(t$.flags, 8), 8, 1, 0),
                MOD(TRUNC(o$.flags / 2), 2), t$.property,
                DECODE(BITAND(t$.flags, 32), 32, 1, 0), ts$.dflogging, o$.oid$,
                t$.spare1, DECODE(BITAND(o$.flags, 16), 16, 1, 0),
                NVL(t$.rowcnt, -1), NVL(t$.blkcnt, -1), NVL(t$.avgrln, -1),
                t$.flags, t$.trigflag, o$.status,
                (SELECT COUNT(*)
                    FROM sys.opqtype$ opq$
                    WHERE opq$.obj# = o$.obj# AND
                          BITAND(opq$.flags, 32) = 32 )
        FROM    sys.tab$ t$, sys.obj$ o$, sys.ts$ ts$, sys.user$ u$,
                sys.com$ c$, sys.ind$ i$
        WHERE   t$.obj# = o$.obj# AND
                u$.user# = o$.owner# AND
                o$.obj# = c$.obj#(+) AND
                c$.col#(+) IS NULL AND
                BITAND(o$.flags,128) != 128 AND      /* Skip recycle bin */
                BITAND(t$.property, 64+512) = 64 AND /* IOT, but not overflow*/
                t$.pctused$ = i$.obj# AND/* For IOTs, pctused has index obj# */
                i$.ts# = ts$.ts#

准备测试条件
创建用户xifenfei,在该用户下面创建四个表,其中有t_xifenfei和t_orasos需要跳过

[oracle@localhost ~]$ ss
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 21:38:18 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user xifenfei identified by oracle;
User created.
SQL> grant dba to xifenfei;
Grant succeeded.
SQL> conn xifenfei/oracle
Connected.
SQL> create table t_xifenfei as select * from dba_tables;
Table created.
SQL> create table t_xifenfei_exp as select * from dba_tables;
Table created.
SQL> create table t_orasos as select * from dual;
Table created.
SQL> create table xff_t_orasos as select * from dual;
Table created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_ORASOS                       TABLE
T_XIFENFEI                     TABLE
T_XIFENFEI_EXP                 TABLE
XFF_T_ORASOS                   TABLE

exp导出数据测试

[oracle@localhost ~]$ exp xifenfei/oracle owner=xifenfei file=/tmp/www.xifenfei.com.dmp
Export: Release 11.2.0.4.0 - Production on Sun Feb 21 21:40:23 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user XIFENFEI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user XIFENFEI
About to export XIFENFEI's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export XIFENFEI's tables via Conventional Path ...
. . exporting table                 T_XIFENFEI_EXP       3374 rows exported
. . exporting table                   XFF_T_ORASOS          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

通过测试，我们发现，在xifenfei用户中有四个表，按照我们的设想跳过了事先配置的表.通过修改exu10tabs脚本，完美实现exp跳部分表

How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository—重建awr

由于某种原因，比如数据异常断电，导致awr数据严重不一致，awr部分表损坏等情况，需要重建awr，可以参考如下步骤进行重建,本文主要针对目前主流的10g和11g版本数据库,12c未进行测试
停止awr自动收集信息
方法1:参数调整

sqlplus /nolog
connect / as sysdba
create pfile='/tmp/pfile.xifenfei' from spfile;
alter system set shared_pool_size = 200m scope = spfile;
alter system set db_cache_size = 300m scope = spfile;
alter system set java_pool_size = 100m scope = spfile;
alter system set large_pool_size = 50m scope = spfile;
--内存值可以根据实际情况调整
alter system reset sga_target  scope = spfile sid='*';
alter system set statistics_level=basic scope=spfile;
--11G
alter system reset memory_target scope= spfile sid='*';
alter system reset memory_max_target scope=spfile sid='*';
alter system set sga_target=0 scope= spfile;
alter system set memory_target=0 scope= spfile;
--RAC
alter system set cluster_database = false scope = spfile;

方法2：使用包/参数

For 10g, you need to download the package DBMS_AWR.DISABLE_AWR available at Note 436386.1 Package
for disabling AWR without a Diagnostic Pack license in Oracle To
install, run the package as SYS from SQL*Plus:
@dbmsnoawr.plb
To execute the package, use the command:
begin dbms_awr.disable_awr(); end;
For 11g, use the parameter control_management_pack_access to disable it
alter system set control_management_pack_access = NONE scope = both;

方法1:需要重启数据库
如果选择方法2,忽略此步骤

sqlplus /nolog
connect / as sysdba
shutdown immediate
startup restrict

删除AWR

start ?/rdbms/admin/catnoawr.sql
--由于Bug 5376177在10.2.0.1/2中可能没有catnoawr.sql文件，可以从10.2.0.3/4中拷贝过来
alter system flush shared_pool;
--验证awr数据数据删除情况
select table_name from dba_tables where table_name like 'WRM$_%' or table_name like 'WRH$_%';
如果有记录存在，使用drop table 语句删除

创建AWR

start ?/rdbms/admin/catawrtb.sql
start ?/rdbms/admin/utlrp.sql
--11G
start ?/rdbms/admin/execsvrm.sql
alter package dbms_swrf_internal compile;
alter package dbms_swrf_internal compile body;
start ?/rdbms/admin/execsvrm.sql

重启数据库
方法1对应处理

create spfile from pfile='/tmp/pfile.xifenfei';
shutdown immediate
startup

方法2对应处理

--11g
alter system set control_management_pack_access = 'DIAGNOSTIC+TUNING' scope = both;
shutdown immediate
startup
--10g
@dbmsnoawr.plb
begin dbms_awr.enable_awr();end;

处理无效对象

spool objects.lst
set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version
from dba_registry
order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type
from dba_objects
where status='INVALID' order by owner,object_type;
select owner,object_type,count(*)
from dba_objects
where status='INVALID'
group by owner,object_type order by owner,object_type ;
spool off
alter package <schema name>.<package_name> compile;
alter package <schema name>.<package_name> compile body;
alter view <schema name>.<view_name> compile;
alter trigger <schema).<trigger_name> compile;

测试AWR

--收集快照
exec dbms_workload_repository.create_snapshot;
--wait for 5 min
exec dbms_workload_repository.create_snapshot;
--生成awr报告
start $ORACLE_HOME/rdbms/admin/awrrpt.sql

注意BUG
Bug:17063159 CATNOAWR.SQL NOT DROPPING ALL AWR TABLES
Bug:10211252 ‘DROP TABLE WRM$_WR_USAGE MISSING IN CATNOAWR.SQL
Bug:9150463 CANNOT RECREATE THE AWR ON R11.1

参考文档
How to Recreate The AWR ( AUTOMATIC WORKLOAD ) Repository ? (Doc ID 782974.1)
How to Recreate Tables in the SYSAUX Tablespace (Doc ID 333665.1)

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

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)
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 (Windows^64bit)	Bundle Patch (Windows^32bit)
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)
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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^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 (Windows^64bit)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
^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 (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
^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 (Windows^32bit)	Bundle Patch (Windows^Itanium)	Bundle Patch (Windows^64bit)
^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 (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
ⁱJAN2009	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 (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2005	4193293	4181849	4213305
JUL2005	4400766	4388944	4388948
JAN2005	4003051	4104364	4083038


*9.2.0.8*
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
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 (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2005	4002994	4104369	4083202


*8.1.7.4*
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)
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
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
JAN2016 (12.1.0.1.160119)	22139235	22311072	22191711	22191721	Included in OJVM PSU
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
JAN2016 (11.2.0.4.160119)	22139245	22311053	22378146	22378121	22378167	Included in OJVM PSU
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

注意：从2016年1月份开始，cpu，db psu，gi psu，Bundle Patch都变为版本号+年月日的格式.

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

ORA-00354 ORA-00353 ORA-00312异常处理

数据库启动报错
WIN平台oracle 9.2.0.6版本数据库redo log block header损坏，ORA-00354 ORA-00353 ORA-00312错误导致数据库无法启动

SQL >alter database open;
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1892904 change 281470950178815
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\ZOYO\REDO03.LOG'

Sun Jan 24 15:44:05 2016
Database mounted in Exclusive Mode.
Completed: alter database mount exclusive
Sun Jan 24 15:44:05 2016
alter database open
Sun Jan 24 15:44:05 2016
Beginning crash recovery of 1 threads
Sun Jan 24 15:44:05 2016
Started redo scan
ORA-354 signalled during: alter database open...
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 3
Sun Jan 24 15:44:32 2016
ALTER DATABASE CLOSE NORMAL
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

通过分析,确定损坏的redo03为当前redo,无法使用正常方法打开,加上_allow_resetlogs_corruption参数,尝试打开库,依旧失败

数据库报ORA-600 2662错误

Sun Jan 24 16:26:30 2016
SMON: enabling cache recovery
Sun Jan 24 16:26:30 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc:
ORA-00600: 内部错误代码，参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], []
Sun Jan 24 16:26:31 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_640.trc:
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码，参数: [2662], [0], [31563641], [0], [31563654], [4194721], [], []
Sun Jan 24 16:26:31 2016
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 640
ORA-1092 signalled during: alter database open resetlogs...

ORA 600 2662的错误处理
根据经验,这个错误只需要推scn即可,可以通过bbed,隐含参数,event,oradebug,修改控制文件等方法进行,推scn之后,数据库报熟悉的ORA-00604 ORA-00607 ORA-600 4194错误,以前我们遇到的block大部分是128,这次报异常block为9.实际中跟版本有关系,在ORACLE 9.2.0.6中该错误为file 1 block 9.大部分版本为128

Sun Jan 24 16:29:39 2016
SMON: enabling cache recovery
Sun Jan 24 16:29:39 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc:
ORA-00600: 内部错误代码，参数: [4194], [14], [5], [], [], [], [], []
Sun Jan 24 16:29:39 2016
Doing block recovery for fno: 1 blk: 401
Sun Jan 24 16:29:39 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG
Doing block recovery for fno: 1 blk: 9
Sun Jan 24 16:29:40 2016
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
  Mem# 0 errs 0: D:\ORACLE\ORADATA\ZOYO\REDO01.LOG
Sun Jan 24 16:29:40 2016
Errors in file d:\oracle\admin\zoyo\udump\zoyo_ora_3432.trc:
ORA-00604: 递归 SQL 层 1 出现错误
ORA-00607: 当更改数据块时出现内部错误
ORA-00600: 内部错误代码，参数: [4194], [14], [5], [], [], [], [], []
Error 604 happened during db open, shutting down database
USER: terminating instance due to error 604
Instance terminated by USER, pid = 3432

ORA-00604 ORA-00607 ORA-600 4194分析trace文件

*** 2016-01-24 16:29:40.031
Recovery of Online Redo Log: Thread 1 Group 1 Seq 2 Reading mem 0
Block image after block recovery:
buffer tsn: 0 rdba: 0x00400009 (1/9)
scn: 0x0000.01e112e1 seq: 0x01 flg: 0x04 tail: 0x12e10e01
frmt: 0x02 chkval: 0xba76 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x00400191  ext#: 4      blk#: 0      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 4
                   Unlocked
     Map Header:: next  0x00000000  #extents: 6    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x0040000a  length: 7
   0x00400011  length: 8
   0x00400181  length: 8
   0x00400189  length: 8
   0x00400191  length: 8
   0x00400199  length: 8
  TRN CTL:: seq: 0x008e chd: 0x0060 ctl: 0x0024 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400191.008e.04 scn: 0x0000.01ded29c
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400191.008e.04 ext: 0x4  spc: 0x1c3e
    uba: 0x00000000.002f.21 ext: 0x5  spc: 0x1334
    uba: 0x00000000.002e.37 ext: 0x4  spc: 0x788
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

从这里可以确定undo segment header中的分配block记录有问题,清除ktuxc.fbp.fbp[N].kuba.kdba相关记录,数据库正常打开

    . struct ktuxc  kernel transaction undo xaction table control with 15 members
    . {
    .   struct kscn   scn with 3 members
    .   {
04148     ub4           bas      = 0X9CD2DE01 = 31380124
04152     ub2           wrp      = 0X0000 = 0
04154     cc32          pad      = 0X0000 = 0
    .   }
    .   struct kuba   uba with 4 members
    .   {
04156     kdba          dba      = 0X91014000 = 0x00400191 file 1 block 401
04160     ub2           seq      = 0X8E00 = 142
04162     ub1           rec      = 0X04 = 4
04163     cc16          pad      = 0X00 = 0
    .   }
04164   sb2           flg      = 0X0100 = 1
04166   ub2           seq      = 0X8E00 = 142
04168   sb2           nfb      = 0X0100 = 1
04170   cc32          pad1     = 0X0000 = 0
04172   ub4           inc      = 0X00000000 = 0
04176   sb2           chd      = 0X6000 = 96
04178   sb2           ctl      = 0X2400 = 36
04180   ub2x          mgc      = 0X0280 = 0x8002
04182   ub2           ver      = 0X0100 = 1
04184   ub2           xts      = 0X6800 = 104
04186   cc32          pad2     = 0X0000 = 0
04188   ub4           opt      = 0XFEFFFF7F = 2147483646
    .   ktufb fbp[5] (array with 5 elements)
    .     struct fbp   [0] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04192         kdba          dba      = 0X91014000 = 0x00400191 file 1 block 401
04196         ub2           seq      = 0X8E00 = 142
04198         ub1           rec      = 0X04 = 4
04199         cc16          pad      = 0X00 = 0
    .       }
04200       sb2           ext      = 0X0400 = 4
04202       sb2           spc      = 0X3E1C = 7230
    .     }
    .     struct fbp   [1] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04204         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04208         ub2           seq      = 0X2F00 = 47
04210         ub1           rec      = 0X21 = 33
04211         cc16          pad      = 0X00 = 0
    .       }
04212       sb2           ext      = 0X0500 = 5
04214       sb2           spc      = 0X3413 = 4916
    .     }
    .     struct fbp   [2] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04216         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04220         ub2           seq      = 0X2E00 = 46
04222         ub1           rec      = 0X37 = 55
04223         cc16          pad      = 0X00 = 0
    .       }
04224       sb2           ext      = 0X0400 = 4
04226       sb2           spc      = 0X8807 = 1928
    .     }
    .     struct fbp   [3] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04228         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04232         ub2           seq      = 0X0000 = 0
04234         ub1           rec      = 0X00 = 0
04235         cc16          pad      = 0X00 = 0
    .       }
04236       sb2           ext      = 0X0000 = 0
04238       sb2           spc      = 0X0000 = 0
    .     }
    .     struct fbp   [4] with 3 members
    .     {
    .       struct kuba   uba with 4 members
    .       {
04240         kdba          dba      = 0X00000000 = 0x00000000 file 0 block 0
04244         ub2           seq      = 0X0000 = 0
04246         ub1           rec      = 0X00 = 0
04247         cc16          pad      = 0X00 = 0
    .       }
04248       sb2           ext      = 0X0000 = 0
04250       sb2           spc      = 0X0000 = 0
    .     }
    . }

Sun Jan 24 16:44:52 2016
SMON: enabling tx recovery
Sun Jan 24 16:44:52 2016
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN

关于wallet加密的几个测试

TDE中比较核心部分为wallet,对于这部分进行测试,对钱包加密有更加深刻的理解.
wallet随库启动本质

[oracle@localhost wallets]$ ls -ltr
total 8
-rw------- 1 oracle oinstall 3637 Jan  5 23:11 ewallet.p12
[oracle@localhost wallets]$
[oracle@localhost wallets]$
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:16:13 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
[oracle@localhost wallets]$ orapki wallet create -pwd xifenfei123 -wallet /home/u01/oracle/network/wallets  -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@localhost wallets]$ ls -ltr
total 16
-rw------- 1 oracle oinstall 3637 Jan  5 23:11 ewallet.p12
-rw------- 1 oracle oinstall 3715 Jan  5 23:20 cwallet.sso
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:21:04 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
[oracle@localhost wallets]$ ls
cwallet.sso  ewallet.p12
[oracle@localhost wallets]$ rm cwallet.sso
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:30:55 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
SQL>  alter system set wallet open identified by "xifenfei123";
System altered.
SQL>  select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

通过测试我们发现当钱包中含cwallet.sso之时,wallet就会随库启动而open,当cwallet.sso被删除之后,wallet无法随库启动而open,由此可见,wallet是否随库启动而open取决于cwallet.sso文件.

修改wallet密码

[oracle@localhost wallets]$ orapki wallet change_pwd -wallet /home/u01/oracle/network/wallets
> -oldpwd xifenfei123 -newpwd www.xifenfei.com
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:35:01 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set wallet close identified by "xifenfei123";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
SQL> alter system set wallet open identified by "xifenfei123";
alter system set wallet open identified by "xifenfei123"
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> alter system set wallet open identified by "www.xifenfei.com";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost wallets]$ ls -ltr
total 8
-rw------- 1 oracle oinstall 3638 Jan  5 23:34 ewallet.p12

wallet文件丢失

[oracle@localhost wallets]$ mv ewallet.p12 ewallet.p12_bak
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:36:55 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 Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set wallet close identified by "www.xifenfei.com";
System altered.
SQL> alter system set wallet open identified by "www.xifenfei.com";
alter system set wallet open identified by "www.xifenfei.com"
*
ERROR at line 1:
ORA-28367: wallet does not exist
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei.com";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei.com"
*
ERROR at line 1:
ORA-28362: master key not found
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei"
*
ERROR at line 1:
ORA-28353: failed to open wallet

如果wallet文件丢失,没有办法open钱包,也就是说加密数据无法读取.备份ewallet.p12文件非常重要

Oracle TDE 简单测试

从ORACLE 10.2开始提供了一个新的特性，让你只需要做如下动作：你可以不写一行代码，只需要声明你需要加密某列。当用户插入数据的时候，数据库透明的加密数据然后存储加密后的数据。同样的，当用户读取数据时，数据库自动进行节目。由于加解密操作对应用程序来说都是透明的，不需要应用程序修改代码，因此这个特性就叫做：透明数据加密（TDE）。
TDE实施
sqlnet.ora中增加

ENCRYPTION_WALLET_LOCATION=
   (SOURCE=(METHOD=FILE)(METHOD_DATA=
      (DIRECTORY=/home/u01/oracle/network/wallets)))

重启监听

lsnrctl stop
lsnrctl start

配置钱包

[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 PrXIFENFEIction on Tue Jan 5 14:43:18 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
[oracle@localhost wallets]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 2845 Jan  5 14:43 ewallet.p12

TDE加密测试

SQL> conn XIFENFEI/oracle
Connected.
SQL>  create table CUST_PAYMENT_INFO
  2                     (first_name varchar2(11),
  3                      last_name varchar2(10),
                    order_number number(13),
                    CREDIT_CARD_NUMBER varchar2(20) ENCRYPT NO SALT);  4    5
Table created.
SQL>  insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');
1 row created.
SQL> insert into cust_payment_info values ('Chris', 'White', 10002, '5122-3580-4608-2560');
1 row created.
SQL>  insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595-9689-4375-7920');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from USER_ENCRYPTED_COLUMNS;
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ENCRYPTION_ALG                SAL INTEGRITY_AL
----------------------------- --- ------------
CUST_PAYMENT_INFO              CREDIT_CARD_NUMBER
AES 192 bits key              NO  SHA-1
SQL> conn / as sysdba
Connected.
SQL> select * from XIFENFEI.cust_payment_info;
FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMBER
----------- ---------- ------------ --------------------
Jon         Oldfield          10001 5446-9597-0881-2985
Chris       White             10002 5122-3580-4608-2560
Alan        Squire            10003 5595-9689-4375-7920
SQL> ALTER SYSTEM SET WALLET close IDENTIFIED BY  "welcome1";
System altered.
SQL> select * from XIFENFEI.cust_payment_info;
select * from XIFENFEI.cust_payment_info
                  *
ERROR at line 1:
ORA-28365: wallet is not open

验证TDE加密数据

--创建测试数据
SQL> create table XIFENFEI.CUST_PAYMENT_INFO2
  2                     (first_name varchar2(11),
  3                      last_name varchar2(10),
  4                      order_number number(13),
                    CREDIT_CARD_NUMBER varchar2(20));  5
Table created.
SQL> insert into XIFENFEI.cust_payment_info2 values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');
insert into XIFENFEI.cust_payment_info2 values ('Chris', 'White', 10002, '5122-3580-4608-2560');
1 row created.
SQL>
1 row created.
SQL> insert into XIFENFEI.cust_payment_info2 values ('Alan', 'Squire', 10003, '5595-9689-4375-7920');
1 row created.
SQL> commit;
Commit complete.
SQL> select CREDIT_CARD_NUMBER,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
  4    5  from XIFENFEI.cust_payment_info2;
CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2
SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
/home/u01/oradata/qsng/users01.dbf
SQL> alter system checkpoint;
System altered.
--使用bbed直接查看数据文件中数据
[oracle@localhost oracle]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited PrXIFENFEIction on Tue Jan 5 22:06:59 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 157716
        BLOCK#          157716
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[3]                                @118
 ub1 freespace[7943]                        @124
 ub1 rowdata[121]                           @8067
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[80]
-----------
ub1 rowdata[80]                             @8147     0x2c
BBED> x /rccnc
rowdata[80]                                 @8147
-----------
flag@8147: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8148: 0x01
cols@8149:    4
col    0[3] @8150: Jon
col    1[8] @8154: Oldfield
col    2[4] @8163: 10001
col   3[19] @8168: 5446-9597-0881-2985
--证明没有加密数据文件中数据,可以直接查看
SQL> ALTER TABLE XIFENFEI.CUST_PAYMENT_INFO2 MODIFY (CREDIT_CARD_NUMBER ENCRYPT NO SALT);
Table altered.
SQL> insert into XIFENFEI.cust_payment_info2 values ('xifenfei', 'XFF', 10004, 'WWW.XIFENFEI.COM');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>  alter system checkpoint;
System altered.
SQL> select CREDIT_CARD_NUMBER,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
  4    5  from XIFENFEI.cust_payment_info2;
CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2
WWW.XIFENFEI.COM     AAAZXdAAEAAAmgWAAA          4     157718          0
BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 157716
        BLOCK#          157716
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[3]                                @118
 ub1 freespace[7723]                        @124
 ub1 rowdata[341]                           @7847
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[146]
------------
ub1 rowdata[146]                            @7993     0x2c
BBED> x /rccnc
rowdata[146]                                @7993
------------
flag@7993: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7994: 0x02
cols@7995:    4
col    0[3] @7996: Jon
col    1[8] @8000: Oldfield
col    2[4] @8009: 10001
col   3[52] @8014: g隐.1Y.>.焦右.l.0赌鉣X.^._K泅Dn&.蜥._sR^....
BBED> set block 157718
        BLOCK#          157718
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157718                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[1]                                @118
 ub1 freespace[7994]                        @120
 ub1 rowdata[74]                            @8114
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8114     0x2c
BBED> x /rccnc
rowdata[0]                                  @8114
----------
flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8115: 0x01
cols@8116:    4
col    0[8] @8117: xifenfei
col    1[3] @8126: XFF
col    2[4] @8130: 10004
col   3[52] @8135: 籕G蠖璆]Qu]..o._証?.湮`.C..)S....&...Z

通过测试可以发现两点:
1. TDE加密之后,数据无法通过数据文件获取,也就是说在没有钱包的情况下,就算有数据文件,也无法直接获取数据
2. 当对表进行alter语句设置加密之后,原表中数据已经进行加密,而且后续插入数据也加密

TDE加密后数据导出问题

--TDE加密之后,数据无法通过exp导出
[oracle@localhost network]$ exp XIFENFEI/oracle file=/tmp/1.dmp tables=CUST_PAYMENT_INFO1
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:09:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column CREDIT_CARD_NUMBER in table
  XIFENFEI.CUST_PAYMENT_INFO1 is not supported. The table will not be exported.
Export terminated successfully with warnings.
--TDE加密之后,数据使用expdp导出需要使用
[oracle@localhost network]$ expdp XIFENFEI/oracle dumpfile=1.dmp tables=CUST_PAYMENT_INFO1
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:10:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=1.dmp tables=CUST_PAYMENT_INFO1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO1"                  6.406 KB       3 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/1.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Jan 5 16:10:43 2016 elapsed 0 00:00:11
--指定ENCRYPTION_MODE=TRANSPARENT使用钱包加密方式
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT
> ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:45:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT
  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:45:06 2016 elapsed 0 00:00:03
--使用ENCRYPTION_MODE=PASSWORD方式导出
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD
>ENCRYPTION_PASSWORD=www.xifenfei.com  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:46:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD
  ENCRYPTION_PASSWORD=******** ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:46:21 2016 elapsed 0 00:00:03

钱包随库一起open

[oracle@localhost wallets]$ orapki wallet create -pwd welcome1 -wallet /home/u01/oracle/network/wallets  -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
--注意随库open之后钱包无法关闭
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> alter system set wallet close identified by "welcome1";
alter system set wallet close identified by "welcome1"
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>  alter system set wallet close;
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

vSphere ssh登陆配置

vSphere(esxi)在某些情况下需要通过ssh登陆,比如由于某种原因,你无法通过控制台直接拷贝文件下来,需要人工ssh登陆进行操作.通过查询资料基本上确定如下方法可以实现需求:
1. vSphere启动ssh服务
F2–>[System Customization]–>[Troubleshooting Options]–>[Enable SSH] 或者通过VMware vSphere Client实现
ssh-esxi1

2. 配置连接方式
认证方式:选择challenge-response (keyboard-interactive)
ssh-esxi2

3. 登陆验证结果
ssh-esxi3