分类目录归档：Oracle

给你的rman备份集加上密码锁

发表于 2015 年 1 月 29 日 由 惜分飞

数据的安全越来越重要,不是说你的生产库安全，你的数据就一定安全了，rman备份也是泄露数据的一个重要地方,如果别人拿到了你的备份集,一样等同入侵了你的生产库。为了rman备份的安全，最简单方式就是使用set encryption方式在rman备份过程中设置密码,需要版本为10.2及其以后企业版版,另外如果需要备份到带库只能使用oracle自己的osb(Oracle Secure Backup),注意rman只有backupset可以加密,copy无法进行加密
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0

支持rman加密算法

SQL> select ALGORITHM_NAME
  2  from V$RMAN_ENCRYPTION_ALGORITHMS;

ALGORITHM_NAME
----------------------------------------------------------------
AES128
AES192
AES256

调整加密算法

RMAN> show  ENCRYPTION ALGORITHM;

RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256';

new RMAN configuration parameters:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
new RMAN configuration parameters are successfully stored

RMAN> show ENCRYPTION ALGORITHM;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE ENCRYPTION ALGORITHM 'AES256';

创建新测试数据文件
我们这里测试的是对新创建的5号文件进行加密备份和还原

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf

SQL> create tablespace rman_xifenfei datafile 
  2 '/u01/app/oracle/oradata/orcl/xifenfei01.dbf' size 100M;

Tablespace created.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /u01/app/oracle/oradata/orcl/system01.dbf
         2 /u01/app/oracle/oradata/orcl/sysaux01.dbf
         3 /u01/app/oracle/oradata/orcl/undotbs01.dbf
         4 /u01/app/oracle/oradata/orcl/users01.dbf
         5 /u01/app/oracle/oradata/orcl/xifenfei01.dbf

SQL> create table chf.t_xifenfei tablespace rman_xifenfei 
  2  as select * from dba_objects;

Table created.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     86721

rman加密备份

RMAN> set encryption on identified by 'www.xifenfei.com' only;

executing command: SET encryption

RMAN> backup datafile 5;

Starting backup at 28-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: starting piece 1 at 28-JAN-15
channel ORA_DISK_1: finished piece 1 at 28-JAN-15
piece handle=/u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp tag=TAG20150128T230115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-JAN-15

准备恢复测试

RMAN> sql 'alter database datafile 5 offline';

sql statement: alter database datafile 5 offline

[oracle@localhost ~]$ rm /u01/app/oracle/oradata/orcl/xifenfei01.dbf
[oracle@localhost ~]$ ls /u01/app/oracle/oradata/orcl/xifenfei01.dbf
ls: /u01/app/oracle/oradata/orcl/xifenfei01.dbf: No such file or directory

rman恢复测试

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jan 28 23:02:24 2015

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

connected to target database: ORCL (DBID=1378620768)

RMAN> list backup of datafile 5;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    10.94M     DISK        00:00:01     28-JAN-15      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150128T230115
        Piece Name: /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 54057180   28-JAN-15 /u01/app/oracle/oradata/orcl/xifenfei01.dbf

--未输入密码
RMAN> restore datafile 5;

Starting restore at 28-JAN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=492 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/28/2015 23:02:52
ORA-19870: error while restoring backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

--设置错误密码
RMAN> SET DECRYPTION IDENTIFIED BY 'www.orasos.com';

executing command: SET decryption

RMAN> restore datafile 5;

Starting restore at 28-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/28/2015 23:03:31
ORA-19870: error while restoring backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

--设置正确密码
RMAN> SET DECRYPTION IDENTIFIED BY 'www.xifenfei.com';

executing command: SET decryption

RMAN> restore datafile 5;

Starting restore at 28-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp
channel ORA_DISK_1: piece handle=/u01/2015_01_28/o1_mf_nnndf_TAG20150128T230115_bdkyfvlw_.bkp tag=TAG20150128T230115
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 28-JAN-15

验证数据还原

RMAN> recover datafile 5;

Starting recover at 28-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 28-JAN-15

RMAN> sql 'alter database datafile 5 online';

sql statement: alter database datafile 5 online

RMAN> exit

Recovery Manager complete.
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 28 23:05:55 2015

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 count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     86721

至此我们可以看到,最简单的rman加密备份和加密恢复测试完成,在使用set encryption加密后,如果不输入或者错误的输入密码无法使用备份集,从而确保了备份集的安全.

发表在 Oracle 安全 | 标签为 , , | 评论关闭

通过DBMS_CRYPTO包对表敏感字段进行加密

发表于 2015 年 1 月 28 日 由 惜分飞

在安全越来越重视的近体，我们不少时候需要对数据库中的某个表的敏感列数据(银行卡,身份证号码,金额等)进行加密，方式数据泄密,在11.2.0.4中可以通过dbms_crypto包方式实现,增加oracle的加密效率,本文提供处理思路,其他可以根据需求尽情发挥
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
[/shell]
<strong>创建加密函数</strong>
1
SQL> create or replace function f_Encrypt_number(number_in in varchar2) return raw is
  2  number_in_raw RAW(128):=UTL_I18N.STRING_TO_RAW(number_in,'AL32UTF8');
  3  key_number number(32):=32432432343243279898;
  4  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  5  encrypted_raw RAW(128);
  6  begin
  7  encrypted_raw:=dbms_crypto.Encrypt(src=>number_in_raw,typ=>DBMS_CRYPTO.DES_CBC_PKCS5,key=>key_raw);
  8  return encrypted_raw;
  9  end;
 10  /

Function created.

测试加密函数

SQL> select f_Encrypt_number('wwww.xifenfei.com') from dual;

F_ENCRYPT_NUMBER('WWWW.XIFENFEI.COM')
--------------------------------------------------------------------------------
003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058

创建解密函数

SQL> create or replace function f_decrypt_number (encrypted_raw IN RAW)
  2  return varchar2 is
  3  decrypted_raw raw(48);
  4  key_number number(32):=32432432343243279898;
  5  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  6  begin
  7  decrypted_raw := DBMS_CRYPTO.DECRYPT
  8  (
  9  src => encrypted_raw,
 10  typ => DBMS_CRYPTO.DES_CBC_PKCS5,
 11  key => key_raw
 12  );
 13  return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 14  END;
 15  /

Function created.

测试解密函数

SQL> select f_decrypt_number('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058') from dual;

F_DECRYPT_NUMBER('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058')
--------------------------------------------------------------------------------
wwww.xifenfei.com

创建表综合测试

SQL> create table xifenfei_crypto
  2  (id number, name varchar2(20),en_name raw(128)) ;

Table created.

SQL> insert into xifenfei_crypto (id,name) select object_id,object_name from dba_objects where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei_crypto;

        ID NAME                 EN_NAME
---------- -------------------- ------------------------------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        41 I_IND1
        54 I_CDEF2

9 rows selected.

SQL> update xifenfei_crypto set en_name=f_Encrypt_number(name);

9 rows updated.

SQL> commit;

Commit complete.

SQL> select * from xifenfei_crypto;

        ID NAME                 EN_NAME
---------- -------------------- --------------------------------------------------
        20 ICOL$                FE17B031331839A9
        46 I_USER1              FEF96765B1E2C53C
        28 CON$                 0283FCE900ACED5C
        15 UNDO$                20DD92762F199436
        29 C_COBJ#              A0CB43E2EA6BA889
         3 I_OBJ#               F2DE1B9C8A39AA3D
        25 PROXY_ROLE_DATA$     62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81
        41 I_IND1               3F4C3C186F8E2F52
        54 I_CDEF2              CA23D202802BD3AC

9 rows selected.

SQL> select id,name,f_decrypt_number(EN_NAME) de_name,en_name from  xifenfei_crypto;

        ID NAME                 DE_NAME                        EN_NAME
---------- -------------------- ------------------------------ --------------------------------------------------
        20 ICOL$                ICOL$                          FE17B031331839A9
        46 I_USER1              I_USER1                        FEF96765B1E2C53C
        28 CON$                 CON$                           0283FCE900ACED5C
        15 UNDO$                UNDO$                          20DD92762F199436
        29 C_COBJ#              C_COBJ#                        A0CB43E2EA6BA889
         3 I_OBJ#               I_OBJ#                         F2DE1B9C8A39AA3D
        25 PROXY_ROLE_DATA$     PROXY_ROLE_DATA$               62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81
        41 I_IND1               I_IND1                         3F4C3C186F8E2F52
        54 I_CDEF2              I_CDEF2                        CA23D202802BD3AC

9 rows selected.
发表在 Oracle 安全 | 标签为 , , , | 评论关闭

11.1.0.7版本也会出现access$表丢失导致数据库无法启动

发表于 2015 年 1 月 27 日 由 惜分飞

有网友咨询数据库启动报ora-01092：ORACLE 实例终止。强制断开连接，请求帮忙处理
数据库版本

Trace file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU                 : 1 - type 8664, 1 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:7605M/10239M, Ph+PgF:11979M/20477M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 18
Windows thread id: 5648, image: ORACLE.EXE (SHAD)

open数据库报ORA-01092: ORACLE 实例终止。强制断开连接

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE 实例终止。强制断开连接

alert日志

Thread 1 opened at log sequence 1008
  Current log# 3 seq# 1008 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO03.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3964.trc:
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在
Error 704 happened during db open, shutting down database
USER (ospid: 3964): terminating the instance due to error 704
Instance terminated by USER, pid = 3964
ORA-1092 signalled during: ALTER DATABASE OPEN...
ORA-1092 : opiodr aborting process unknown ospid (3384_3964)

做10046分析日志

PARSE ERROR #1:len=56 dep=1 uid=0 oct=3 lid=0 tim=1796038335 err=942
select order#,columns,types from access$ where d_obj#=:1

*** 2015-01-27 21:24:50.794
----- Error Stack Dump -----
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00942: 表或视图不存在

通过这里可以知道数据库在启动的过程中由于无法访问access$表从而出现ORA-00942错误,又是由于该sql是数据库内部调用因为出现ORA-00604错误.
出现该错误的原因是由于:BUG:12733463 – ORA-704, ORA-604 AND ORA-942 ON TABLE ACCESS$ DURING STARTUP
官方提供方法

1. Shutdown (abort) the instance and clean up any OS structures used by the instance. 
    Eg: Ensure there is no shared memory, semaphores etc.. left lying around

2. Retry the startup.

3. If the error persists try and recover the database or recover from a backup.

惜分飞处理方法

startup  upgrade

 create table access$
 ( d_obj#        number not null,
   order#        number not null,
   columns       raw(126),
   types         number not null)
   storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

create index i_access1 on
  access$(d_obj#, order#)
  storage (initial 10k next 100k maxextents unlimited pctincrease 0)
/

以前类似文章:Oracle 异常恢复案例汇总

发表在 Oracle备份恢复 | 标签为 , , , , | 一条评论