月归档：六月 2018

配置Oracle传输数据加密

发表于 2018 年 6 月 24 日 由 惜分飞

随着安全意识和需求的越来越高,有一些客户要求数据库传输数据过程中对其进行加密,防止有人在网络层面通过抓包,获取数据内容,从而保证了在网络层面的数据传输安全.实现这种加密,主要是通过Database Advanced Security组件实现,主要是通过slnet.ora的配置实现数据在服务端和客户端之间传输加密.
数据库安装的安全组件和支持加密算法

[oracle@ora11g ~]$ adapters 

Installed Oracle Net transport protocols are:

    IPC
    BEQ
    TCP/IP
    SSL
    RAW
    SDP/IB

Installed Oracle Net naming methods are:

    Local Naming (tnsnames.ora)
    Oracle Directory Naming
    Oracle Host Naming
    Oracle Names Server Naming

Installed Oracle Advanced Security options are:

    RC4 40-bit encryption
    RC4 56-bit encryption
    RC4 128-bit encryption
    RC4 256-bit encryption
    DES40 40-bit encryption
    DES 56-bit encryption
    3DES 112-bit encryption
    3DES 168-bit encryption
    AES 128-bit encryption
    AES 192-bit encryption
    AES 256-bit encryption
    MD5 crypto-checksumming
    SHA-1 crypto-checksumming
    Kerberos v5 authentication
    RADIUS authentication

启用客户端trace
trace只是为了验证传输加密,在实际生产中该功能需要关闭,不然会比较严重影响性能和导致trace文件过多磁盘空间占用较大问题

Trace_level_client=16
Trace_directory_client=D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\log
Trace_unique_client=on
Trace_timestamp_client=on
Diag_adr_enabled=off
tnsping.trace_directory=D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\log
tnsping.trace_level=admin

捕获非加密情况数据
通过对客户端trace文件的分析,基本上可以发现客户端和服务端数据传输是明码方式进行,也就是说如果有人在这个网络上捕获包,进行分析,可以知道你的应用端和数据库端的所有操作,数据存在安全隐患

----数据库登录
[24-6月 -2018 12:37:55:555] nam_gnsp: Reading parameter "SQLNET.ENCRYPTION_CLIENT" from parameter file
[24-6月 -2018 12:37:55:555] nam_gnsp: Parameter not found
[24-6月 -2018 12:37:55:555] naequad: Using default value "ACCEPTED"
[24-6月 -2018 12:37:55:555] nam_gic: entry
[24-6月 -2018 12:37:55:555] nam_gic: Counting # of items in "SQLNET.ENCRYPTION_TYPES_CLIENT" parameter
[24-6月 -2018 12:37:55:555] nam_gic: Parameter not found
[24-6月 -2018 12:37:55:555] nam_gic: exit
[24-6月 -2018 12:37:55:555] naesno: Using default value "all available algorithms"
[24-6月 -2018 12:37:55:555] naeshow: entry
[24-6月 -2018 12:37:55:555] naeshow: These are the encryption algorithms that the client will accept:
[24-6月 -2018 12:37:55:555] naeshow: Choice 0: no algorithm; encryption inactive
[24-6月 -2018 12:37:55:555] naeshow: Choice 1: 'AES256' (ID 17)
[24-6月 -2018 12:37:55:555] naeshow: Choice 2: 'RC4_256' (ID 6)
[24-6月 -2018 12:37:55:555] naeshow: Choice 3: 'AES192' (ID 16)
[24-6月 -2018 12:37:55:555] naeshow: Choice 4: '3DES168' (ID 12)
[24-6月 -2018 12:37:55:555] naeshow: Choice 5: 'AES128' (ID 15)
[24-6月 -2018 12:37:55:555] naeshow: Choice 6: 'RC4_128' (ID 10)
[24-6月 -2018 12:37:55:555] naeshow: Choice 7: '3DES112' (ID 11)
[24-6月 -2018 12:37:55:555] naeshow: Choice 8: 'RC4_56' (ID 8)
[24-6月 -2018 12:37:55:555] naeshow: Choice 9: 'DES' (ID 2)
[24-6月 -2018 12:37:55:555] naeshow: Choice 10: 'RC4_40' (ID 1)
[24-6月 -2018 12:37:55:555] naeshow: Choice 11: 'DES40' (ID 3)
[24-6月 -2018 12:37:55:555] naeshow: exit


----数据库执行select * from dual查询
[24-6月 -2018 12:39:38:744] nioqrc: entry
[24-6月 -2018 12:39:38:744] nsbasic_bsd: entry
[24-6月 -2018 12:39:38:744] nsbasic_bsd: tot=0, plen=256.
[24-6月 -2018 12:39:38:744] nttfpwr: entry
[24-6月 -2018 12:39:38:744] nttfpwr: socket 888 had bytes written=256
[24-6月 -2018 12:39:38:744] nttfpwr: exit
[24-6月 -2018 12:39:38:744] nsbasic_bsd: packet dump
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 01 00 00 00 06 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 03 5E 15 61 80 00  |...^.a..|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF 12 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 FE FF FF FF FF FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF 0D 00 00 00 FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 01 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF FE FF FF  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: FF FF FF FF FF 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 12 73 65 6C 65 63 74  |..select|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 20 2A 20 66 72 6F 6D 20  |.*.from.|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 64 75 61 6C 01 00 00 00  |dual....|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 01 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_bsd: exit (0)
[24-6月 -2018 12:39:38:744] nsbasic_brc: entry: oln/tot=0,prd=0

----返回查询结果
[24-6月 -2018 12:39:38:744] nsbasic_brc: packet dump
[24-6月 -2018 12:39:38:744] nsbasic_brc: 01 04 00 00 06 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 10 17 00 00 00 02  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: FC 54 0D 44 40 AD B2 74  |.T.D@..t|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 09 CB A2 01 A7 2D 38 78  |.....-8x|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 76 04 15 03 3B 28 01 00  |v...;(..|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 01 00 00 00 51 01  |......Q.|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 80 00 00 01 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 54 03 01  |.....T..|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 01 00 00 00 01 05 05 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 05 44 55 4D 4D 59  |...DUMMY|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 07 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 07 78 76 04 15 05  |...xv...|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 0F 0C 01 00 00 00 E8 1F  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 02 00 00 00 02 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 06 22  |......."|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 01 00 00 00 00 00 01 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 07 01 58 08  |......X.|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 06 00 7E 2D 18 00 00 00  |..~-....|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 02 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 04 01 00 00 00 13  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 01 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 02 00 00 00 03  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 15  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 01 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00 00 00 00 00  |........|
[24-6月 -2018 12:39:38:744] nsbasic_brc: 00 00 00 00              |....    |
[24-6月 -2018 12:39:38:744] nsbasic_brc: exit: oln=0, dln=250, tot=260, rc=0

启用数据传输加密
这几个参数,每个参数都有几种选项,具体在下文补充说明

--服务端配置sqlnet.ora中配置，并重启监听
SQLNET.ENCRYPTION_SERVER = requested
SQLNET.ENCRYPTION_TYPES_SERVER= (RC4_128)


--客户端sqlnet.ora中配置
SQLNET.ENCRYPTION_CLIENT = requested
SQLNET.ENCRYPTION_TYPES_CLIENT= (RC4_128)

捕获加密情况数据

----登录操作
[24-6月 -2018 12:45:16:786] nam_gnsp: Reading parameter "SQLNET.ENCRYPTION_CLIENT" from parameter file
[24-6月 -2018 12:45:16:786] nam_gnsp: Found value "requested"
[24-6月 -2018 12:45:16:786] nam_gic: entry
[24-6月 -2018 12:45:16:786] nam_gic: Counting # of items in "SQLNET.ENCRYPTION_TYPES_CLIENT" parameter
[24-6月 -2018 12:45:16:786] nam_gic: Found 1 items
[24-6月 -2018 12:45:16:786] nam_gic: exit
[24-6月 -2018 12:45:16:786] nam_gnsp: Reading parameter "SQLNET.ENCRYPTION_TYPES_CLIENT" from parameter file
[24-6月 -2018 12:45:16:786] nam_gnsp: Found value "RC4_128"
[24-6月 -2018 12:45:16:786] naeshow: entry
[24-6月 -2018 12:45:16:786] naeshow: These are the encryption algorithms that the client will accept:
[24-6月 -2018 12:45:16:786] naeshow: Choice 0: 'RC4_128' (ID 10)
[24-6月 -2018 12:45:16:786] naeshow: Choice 1: no algorithm; encryption inactive

----发送查询select * from dual
[24-6月 -2018 12:49:51:528] nttwr: socket 848 had bytes written=277
[24-6月 -2018 12:49:51:528] nttwr: exit
[24-6月 -2018 12:49:51:528] nspsend: packet dump
[24-6月 -2018 12:49:51:528] nspsend: 01 15 00 00 06 00 00 00  |........|
[24-6月 -2018 12:49:51:528] nspsend: 00 00 DF CE 9E 46 DB 71  |.....F.q|
[24-6月 -2018 12:49:51:528] nspsend: CE 88 43 9E CA 43 F8 BE  |..C..C..|
[24-6月 -2018 12:49:51:528] nspsend: E8 ED 07 F1 2E 93 B6 2C  |.......,|
[24-6月 -2018 12:49:51:528] nspsend: E9 5A E5 D8 06 8B DB EE  |.Z......|
[24-6月 -2018 12:49:51:528] nspsend: 66 9E B5 BB 24 C0 5E 4C  |f...$.^L|
[24-6月 -2018 12:49:51:528] nspsend: 33 9C 81 10 18 0F BE 30  |3......0|
[24-6月 -2018 12:49:51:528] nspsend: 79 56 4C D8 4A F9 4D 78  |yVL.J.Mx|
[24-6月 -2018 12:49:51:528] nspsend: 6A 42 24 89 D4 0D 1F 92  |jB$.....|
[24-6月 -2018 12:49:51:528] nspsend: 44 24 DA 42 15 49 22 09  |D$.B.I".|
[24-6月 -2018 12:49:51:528] nspsend: FE AF 07 EA 01 36 83 D4  |.....6..|
[24-6月 -2018 12:49:51:528] nspsend: D6 B3 16 BD 1E B2 88 93  |........|
[24-6月 -2018 12:49:51:528] nspsend: 29 39 DB 44 86 E5 C6 F2  |)9.D....|
[24-6月 -2018 12:49:51:528] nspsend: DF 87 90 4B 6E 5A 66 D3  |...KnZf.|
[24-6月 -2018 12:49:51:528] nspsend: B1 1A 3A 34 01 A9 C2 F1  |..:4....|
[24-6月 -2018 12:49:51:528] nspsend: C7 08 06 50 2B BB C4 5E  |...P+..^|
[24-6月 -2018 12:49:51:528] nspsend: C0 80 D7 72 E4 D8 C0 B6  |...r....|
[24-6月 -2018 12:49:51:528] nspsend: C4 31 90 9A 3C 83 B0 16  |.1..<...|
[24-6月 -2018 12:49:51:528] nspsend: D1 AE 82 56 39 46 08 20  |...V9F..|
[24-6月 -2018 12:49:51:528] nspsend: DE D2 DC 6B BC 5F BF 7E  |...k._.~|
[24-6月 -2018 12:49:51:528] nspsend: 25 2A 31 D7 A3 60 CE 7A  |%*1..`.z|
[24-6月 -2018 12:49:51:528] nspsend: 42 58 0A 43 11 F5 D6 0A  |BX.C....|
[24-6月 -2018 12:49:51:528] nspsend: 31 C8 96 38 F3 C2 90 6A  |1..8...j|
[24-6月 -2018 12:49:51:528] nspsend: 64 50 46 B0 A2 EF 1E 9A  |dPF.....|
[24-6月 -2018 12:49:51:528] nspsend: 86 11 67 C9 9B CD 8C 2B  |..g....+|
[24-6月 -2018 12:49:51:528] nspsend: 2A 02 0D 9B C3 C5 D3 23  |*......#|
[24-6月 -2018 12:49:51:528] nspsend: 75 4C 4A 5B A3 85 80 C5  |uLJ[....|
[24-6月 -2018 12:49:51:528] nspsend: FA F0 6C C3 23 72 D5 28  |..l.#r.(|
[24-6月 -2018 12:49:51:528] nspsend: 0E 16 C7 C8 1B 3D CC B8  |.....=..|
[24-6月 -2018 12:49:51:528] nspsend: 06 FA D7 FB 03 EB E6 41  |.......A|
[24-6月 -2018 12:49:51:528] nspsend: 05 54 B0 5A 18 CC 7F E7  |.T.Z....|
[24-6月 -2018 12:49:51:528] nspsend: 2D 36 C4 4A E6 B2 7F F6  |-6.J....|
[24-6月 -2018 12:49:51:528] nspsend: 1E 10 5B 20 CF 60 FA D3  |..[..`..|
[24-6月 -2018 12:49:51:528] nspsend: 6E B1 F7 9E 35 92 27 BA  |n...5.'.|
[24-6月 -2018 12:49:51:528] nspsend: 9A 4A F1 00 01           |.J...   |
[24-6月 -2018 12:49:51:528] nspsend: 277 bytes to transport
[24-6月 -2018 12:49:51:528] nspsend: normal exit

----接收数据
[24-6月 -2018 12:49:51:528] nttrd: exit
[24-6月 -2018 12:49:51:528] nsprecv: 262 bytes from transport
[24-6月 -2018 12:49:51:528] nsprecv: tlen=262, plen=262, type=6
[24-6月 -2018 12:49:51:528] nsprecv: packet dump
[24-6月 -2018 12:49:51:528] nsprecv: 01 06 00 00 06 00 00 00  |........|
[24-6月 -2018 12:49:51:528] nsprecv: 00 00 63 2C 04 99 D8 25  |..c,...%|
[24-6月 -2018 12:49:51:528] nsprecv: 40 38 A0 30 AE 3D 24 05  |@8.0.=$.|
[24-6月 -2018 12:49:51:528] nsprecv: 97 E4 70 E9 1D 76 8B A0  |..p..v..|
[24-6月 -2018 12:49:51:528] nsprecv: 38 AD 7E D3 B1 A1 BD B7  |8.~.....|
[24-6月 -2018 12:49:51:528] nsprecv: 29 1F 0C 19 9A 0B FD D0  |).......|
[24-6月 -2018 12:49:51:528] nsprecv: F3 13 51 E0 78 C2 7C 7D  |..Q.x.|}|
[24-6月 -2018 12:49:51:528] nsprecv: 37 BC AA 25 23 20 FF 05  |7..%#...|
[24-6月 -2018 12:49:51:528] nsprecv: E0 F6 AF C3 53 C9 50 2F  |....S.P/|
[24-6月 -2018 12:49:51:528] nsprecv: 68 0D F1 CB CE B8 90 6B  |h......k|
[24-6月 -2018 12:49:51:528] nsprecv: 90 CC B2 B5 DF D8 C3 BC  |........|
[24-6月 -2018 12:49:51:529] nsprecv: F0 7A 37 E7 1B FA 3E 6B  |.z7...>k|
[24-6月 -2018 12:49:51:529] nsprecv: 13 90 7A 10 1C 6D C5 40  |..z..m.@|
[24-6月 -2018 12:49:51:529] nsprecv: BC E0 B9 4F 69 10 49 4D  |...Oi.IM|
[24-6月 -2018 12:49:51:529] nsprecv: B9 78 2E 28 B3 8C 05 53  |.x.(...S|
[24-6月 -2018 12:49:51:529] nsprecv: 18 99 B2 AF 46 AE 1D D7  |....F...|
[24-6月 -2018 12:49:51:529] nsprecv: 0E 19 56 28 7A B6 16 72  |..V(z..r|
[24-6月 -2018 12:49:51:529] nsprecv: 46 57 C9 7A 1C DB D1 A2  |FW.z....|
[24-6月 -2018 12:49:51:529] nsprecv: A2 35 B7 DD 63 EA 5E 07  |.5..c.^.|
[24-6月 -2018 12:49:51:529] nsprecv: F9 E1 4E 54 D0 57 63 9D  |..NT.Wc.|
[24-6月 -2018 12:49:51:529] nsprecv: 12 B0 7B 6F 0C DC 98 90  |..{o....|
[24-6月 -2018 12:49:51:529] nsprecv: C1 71 3B BD DA 27 03 CE  |.q;..'..|
[24-6月 -2018 12:49:51:529] nsprecv: 4B FA 5D 64 31 17 D1 CC  |K.]d1...|
[24-6月 -2018 12:49:51:529] nsprecv: 49 A7 A7 47 D8 26 81 23  |I..G.&.#|
[24-6月 -2018 12:49:51:529] nsprecv: BC 9E 54 76 C9 98 07 CF  |..Tv....|
[24-6月 -2018 12:49:51:529] nsprecv: CF 97 A4 70 57 68 80 16  |...pWh..|
[24-6月 -2018 12:49:51:529] nsprecv: 45 14 3D 32 06 3E 1A 06  |E.=2.>..|
[24-6月 -2018 12:49:51:529] nsprecv: 0A D6 8F 51 29 D3 A4 2E  |...Q)...|
[24-6月 -2018 12:49:51:529] nsprecv: 06 0C 03 B1 7A D1 5B 3B  |....z.[;|
[24-6月 -2018 12:49:51:529] nsprecv: 22 9A 6B 0F CD F7 EA 8C  |".k.....|
[24-6月 -2018 12:49:51:529] nsprecv: D7 8C CE F8 B6 9E 80 01  |........|
[24-6月 -2018 12:49:51:529] nsprecv: 95 1A D8 35 31 84 D6 42  |...51..B|
[24-6月 -2018 12:49:51:529] nsprecv: 7C CC 2D 31 00 01        ||.-1..  |
[24-6月 -2018 12:49:51:529] nsprecv: normal exit

这里可以发现,通过配置相关传输加密之后,客户端trace看到的已经是加密之后的数据,也就是说这样可以确保数据传输安全,网络上就算捕获这些包,也无法知道应用端和数据库端交互的内容.

补充说明
1. ENCRYPTION_SERVER和ENCRYPTION_CLIENT参数对应关系
SQLNET.ENCRYPTION_SERVICE-CLIENT

2. ENCRYPTION_TYPES_SERVER和ENCRYPTION_TYPES_CLIENT采用加密算法选择,可以选择adapters查看到的一种或者多种,默认为支持所有加密算法
3. 对于jdbc应用来说,可以参考下列配置方式

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;
import java.sql.*;
import java.util.*;

class Employee1{
public static void main (String args []) throws Exception
{
OracleDataSource ods = new OracleDataSource();
Properties props = new Properties();

props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL, "REQUIRED");
props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES, "(AES256,AES192,AES128)");

ods.setURL("jdbc:oracle:thin:@//yourHost:port/dbServiceName");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setConnectionProperties(props);
Connection conn = ods.getConnection();

// Create a Statement
Statement stmt = conn.createStatement ();
// Select the ENAME column from the EMP table
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getString (1));

rset.close();
stmt.close();
conn.close();
}
}
发表在 Oracle 安全 | 标签为 , , | 评论关闭

truncate table 无论drop storage还是reuse storage不影响数据恢复

发表于 2018 年 6 月 21 日 由 惜分飞

今天有朋友咨询,truncate table有drop storage和reuse storage方式,是否影响后续的数据恢复(在没有其他覆盖的情况下),我做了一个简单的测试证明,这些都不影响truncate table的数据库恢复
创建测试环境

SQL> create table t_xifenfei tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL> create table t_xifenfei2  tablespace USERNEW
  2   as select * from dba_objects;

Table created.

SQL>  create table t_xifenfei3  tablespace USERNEW
  2  as select * from dba_objects;

Table created.

SQL> alter system checkpoint;

System altered.

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     86348

SQL>  select count(*) from t_xifenfei2;

  COUNT(*)
----------
     86349

SQL>  select count(*) from t_xifenfei3;

  COUNT(*)
----------
     86350

SQL> select object_id,data_object_id,object_name from dba_objects where object_name like 't_xifenfei%';

 OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
     88205          88205 t_xifenfei
     88206          88206 t_xifenfei2
     88207          88207 t_xifenfei3

SQL> truncate table t_xifenfei;

Table truncated.

SQL> truncate table t_xifenfei2 drop storage;

Table truncated.

SQL> truncate table t_xifenfei3 reuse storage;

Table truncated.

使用dul进行恢复

  
DUL>  bootstrap;
DUL> scan database;

scanning database...
scanning database finished.

DUL> unload table sys.t_xifenfei object 88205;

Unloading table: t_xifenfei,object ID: 88205
Unloading segment,storage(Obj#=88205 DataObj#=88205 TS#=6 File#=5 Block#=1410 Cluster=0)
86348 rows unloaded

DUL> unload table sys.t_xifenfei2 object 88206;

Unloading table: t_xifenfei2,object ID: 88206
Unloading segment,storage(Obj#=88206 DataObj#=88206 TS#=6 File#=5 Block#=2690 Cluster=0)
86349 rows unloaded

DUL> unload table sys.t_xifenfei3 object 88207;

Unloading table: t_xifenfei3,object ID: 88207
Unloading segment,storage(Obj#=88207 DataObj#=88207 TS#=6 File#=5 Block#=3970 Cluster=0)
86350 rows unloaded

这里证明truncate table不管是drop storage还是reuse storage或者默认,在没有被覆盖的情况下，数据均完全恢复出来

发表在 Oracle备份恢复 | 标签为 , , | 评论关闭

OERR: ALL 600 Ora 600 Layers

发表于 2018 年 6 月 19 日 由 惜分飞 
** 0000 ** Service Layer
========================
   KIESCBAS    (1)                              Component notifier component   
   KIESDBAS    (100)                                         Debug component   
   KIESEBAS    (300)                                         Error component   
   KIESLBAS    (500)                                          Lock component   
   KIESMBAS    (700)                                        Memory component   
   KIESPBAS    (900)                             System Parameters component   
   KIESSBAS    (1100)                          System State object component   
   KIESGBAS    (1110)               Generic Linked List management component   
   KIESQBAS    (1140)                                      Enqueue component   
   KIESIBAS    (1180)                               Instance Locks component   
   KIESUBAS    (1200)                            User State object component   
   KIESABAS    (1400)                                   Async Msgs component   
   KIESKBAS    (1700)                                  license Key component   
   KIESRBAS    (1800)                        Instance Registration component
   KIESOBAS    (1850)                                 I/O Services component

** 2000 ** Cache Layer Component Base internal error values
===========================================================
   KIECOBAS    (2000)                                     Cache Op component   
   KIECCBAS    (2100)                            Control File mgmt component   
   KIECMBAS    (2200)                                        Misc (SCN etc.)   
   KIECHBAS    (2400)                             Buffer Instance Hash Table   
   KIECRBAS    (2600)                                    Redo file component   
   KIECFBAS    (2800)                                      Db file component   
   KIECABAS    (3000)                             Redo Application component   
   KIECBBAS    (3200)                                    Buffer manager base   
   KIECZBAS    (3400)               Archival & media recovery component base   
   KIECLBAS    (3500)                           direct Loader component base   
   KIECVBAS    (3600)                                recoVery component base   
   KIECKBAS    (3700)                                  Thread component base   
   KIECKBAS    (3800)                   Compatibility segment component base   
   KIECBBAS    (3900)              Buffer working set manager component base

** 4000 ** Transaction Layer   
============================
       Transaction Layer Component Base internal error values:   
   KIETUBAS    (4000)                             Transaction Undo Component   
      internal errors 4100-4199 used by transaction undo component as well     
   KIETPBAS    (4210)                                   Transaction Parallel
   KIETLBAS    (4250)                                       Transaction List
   KIETSBAS    (4300)                                    Transaction Segment   
   KIETCBAS    (4400)                          Transaction Control Component   
      internal errors 4450-4499 used by transaction distributed component      
   KIETBBAS    (4500)                            Transaction Block Component   
   KIETABAS    (4600)                            Transaction Table Component   
   KIETRBAS    (4800)                              Query Row Cache Component   
   KIETMBAS    (4900)                          Transaction Monitor Component   
   KIETSBAS    (4950)                              Query Bootstrap Component
   KIETEBAS    (5000)                                     Transaction Extent

** 6000 ** Data Layer
=====================

** 8000 ** Access Layer
=======================

   Data Layer Component Base internal error values:   
   8000...8199 reserved for data layer   
   8100...8149 reserved for index component
   8100 - 8109 used in KAU
   8110 - 8124 used in KKRI, KDIC (create index)

** 9000 ** Parallel Server
==========================
   KIECLBAS    (9000)                            KCL component: 9000 .. 9100

** 10000 ** Control Layer   
=========================

** 12000 ** User/Oracle Interface Layer   
=======================================
    User/Oracle Interface & SQL Layer "components" (in a loose sense)
   KIEUTBAS   (12000)                         TAC* component: 12000 .. 12009   
   KIEURBAS   (12010)                         SORT component: 12010 .. 12199   
   KIEUPBAS   (12200)                         OPI* component: 12200 .. 12299   
   KIEUSBAS   (12300)      PRS, APA, EVA, EXP, QBA component: 12300 .. 12399   
   KIEUWBAS   (12400)    QKA, MSQ, and row source components: 12400 .. 12499
                              reserved for rix: 12480 .. 12499   
   KIEUVBAS   (12600)                         *DRV component: 12600 .. 12799   
   KIEUDBAS   (12800)          KKDL, KKDC and KKM components: 12800 .. 12999   
   KIEUXBAS   (13000                     DELEXE, UPDEXE, CRI: 13000 .. 13079   
                                                        REF : 13080 .. 13089   
                                                        WSM : 13090 .. 13099   
   KIEUABAS   (13100)                                 OPIAMR: 13100 .. 13149   
   KIEULBAS   (13200)                            ACL and ATB: 13200 .. 13230   
   KIEUZBAS   (13250)                       AUD, AUSDRV, AOP: 13250 .. 13300
   KIEUOBAS   (13301)             KOK* and object extensions: 13301 .. 13499
   KIELCBAS   (16000)                            loader column array builder   

** 14000 ** System-dependent "Layer"   
====================================
   System Dependent Layer Component Base internal error values:   
   KIESFBAS   (14000)                                         File component   
   KIESCBAS   (14100)                                  Concurrency component   
   KIESPBAS   (14200)                                      Process component   
   KIESXBAS   (14300)                            Exception-handler component   
   KIESMBAS   (14500)                                       Memory component   

   ** Note that multiple ports can use the same internal error numbers.
      We allow this because we don't want to waste numbers on port internal
      errors, something which is very rarely used.  In addition it is
      likely that any bug that is reporting a port internal error to the
      generic (portable) coding group is going to have to supply a lot of
      documentation - so if two ports use the same number, it should never
      confuse anybody.
  
** 15000 ** Security Layer   
==========================
    15000 - 15079 reserved for KZ*, SEC, and GRA;
    15080 - 15099 reserved for KZL;
    15100 - 15159 reserved for KKP;
    15160 - 15199 reserved for KKO and KKE;
        15190 .. 15199 reserved for KKOCRI
    15200 - 15229 reserved for KKS;
    15230 - 15259 reserved for KKT;
    15260 - 15399 reserved for KKD;
    15400 - 15409 reserved for KKY;
    15410 - 15499 reserved for KKX (and PSD);
    15500 - 15549 reserved for KKM;
    15550 - 15599 reserved for KKR; (and KXH)
    15600 - 15619 reserved for KKF; (parallel query)
    15620 - 15639 reserved for KKZ (snapshots);
    15640 - 15659 reserved for KKXA;
    15660 - 15669 reserved for KKJ (job queue);
    15670 - 15679 reserved for KKFS (parallel query)
    15680 - 15689 reserved for KKRT;
    15690 - 15699 reserved for other KK*;
    
    15700 - 15799 reserved for KXFP; (parallel query)
    15800 - 15809 reserved for KXFX; (parallel query)
    15810 - 15819 reserved for KXFQ; (parallel query)
    15820 - 15849 reserved for other KXF*; (parallel query)
    15850 - 15859 reserved for KSX*;
    15860 - 15999 reserved for other KX*;
    
** 16000 ** Loader "Layer"   
==========================
    16000 - 16149 reserved for the direct loader;
    16150 - 16199 reserved for the c level call interface to direct loader;
    16200 - 16229 reserved for KQL;
    16230 - 16249 reserved for KQLR;
    16250 - 16299 reserved for KQA;
    16300 - 16399 reserved for KM*;
    16400 - 16449 reserved for KKB;
    16450 - 16499 reserved for KQLM;
    16500 - 16549 reserved for KQD;
    16550 - 16559 reserved for KQF;
    16600 - 16650 reserved for KQLS;
    16651 - 16700 reserved for KQLD;
    16701 - 16750 reserved for KQLB;

** 17000 ** Generic "Layer"   
===========================
    17000 - 17089 reserved for KGL;
    17090 - 17099 reserved for KGE;
    17100 - 17199 reserved for KGH;
    17200 - 17249 reserved for KGLR/KGLRO;
    17250 - 17269 reserved for KGP;
    17270 - 17299 reserved for KGI;
    17300 - 17399 reserved for KGHX;
    17500 - 17624 reserved for I/O subsystem KG components (KGFD, KGFF, KGK)
    17625 - 17639 reserved for KGL3;
    17640 - 17989 reserved for other KG*;
    17990 - 17999 reserved for PL/SQL;
    
** 18000 **   K2 (2-phase commit),subdiv'd in k2.h: 18000 .. 18499   
==================================================================
    18000 - 18999 reserved for K2*;

** 19000 ** Object Layer   
========================
    19000 - 19999 reserved for KO*;

    20000 - 20399 reserved for bitmap index KDIB*;
    20400 - 20499 reserved for KKPO;;
    20500 - 20999 reserved for SMR;

** 21000 ** Replication Layer
=============================
    21000 - 21999 reserved for KN*;

** 23000 ** OLTP Layer
======================
    23000 - 23999 reserved for KW*;
    24000 - 24049 reserved for QOL;
发表在 Oracle | 评论关闭