2016年1月 – Database SOS

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加密的几个测试

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

标题：关于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 简单测试

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

标题：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登陆配置

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

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

VMware vSphere6.0 初试

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

标题：VMware vSphere6.0 初试

去年(2015年）12月份的一个恢复,由于是虚拟化环境,中间发生几次虚拟机故障导致恢复无法继续,甚至从头再来,2016年我提醒自己,我可以不动虚拟化,但是我也绝对不能被人忽悠,因此国庆假期,自己初装了下VMware vSphere6.0.安装为文件VMware-VMvisor-Installer-6.0.0.update01-3073146.x86_64.iso,直接可以到vm官网注册下载.为了测试方便,我使用VMware Workstation虚拟了一个200G的硬盘空间,20G的内存的虚拟机用来安装ESXI 6.0(也就是vSphere6.0).
安装相关
安装成功截图