注意系统bug—linux在E5、E5 V2、E7 V2 cpu之上的bug 765720

今天晚上群里面兄弟说了一个linux 6上面bug,会导致系统在运行200天以上(hardware uptime),然后进行热重启后,可能在几分钟或者几个小时内出发该bug,导致系统异常.

主要影响条件为:
Red Hat Enterprise Linux 6.1 (kernel-2.6.32-131.26.1.el6 and newer)
Red Hat Enterprise Linux 6.2 (kernel-2.6.32-220.4.2.el6 and newer)
Red Hat Enterprise Linux 6.3 (kernel-2.6.32-279 series)
Red Hat Enterprise Linux 6.4 (kernel-2.6.32-358 series)
Any Intel® Xeon® E5, Intel® Xeon® E5 v2, or Intel® Xeon® E7 v2 series processor
从这里可以看出来该问题主要影响E5、E5 V2、E7 V2 cpu上的redhat 6.1-6.4版本,在6.5版本中修复,具体参考:bug 765720
另外对已ORACLE Linux,如果使用EL Kernel影响和redhat一致,如果使用Unbreakable Enterprise Kernel则在6.2版本中进行了修复该问题。
MOS上类似文章：Oracle Linux 6 RHCK system hang: processes blocked in ext4_file_open(), pick_next_task_fair()

补充说明：
1. 在Red Hat/OEL 5.x版本中不存在。
2. 在32和64位操作系统都有可能发生
3. 鉴于该bug短期内无法修复,而且真的发生了,考虑冷重启主机,临时规避

再次提醒:系统版本选定也很重要,大家在选择Linux版本之时尽量选择避开该bug(el kernel 6.5及其以后版本,uek kernel 6.2及其以后版本)。个人倾向：如果是部署ORACLE db,而且还是redhat系列Linux,更加倾向OEL(省事,相信Oracle)

ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
ORACLE 12C IDENTITY
GENERATED ALWAYS AS IDENTITY方式测试

C:\Users\ffcheng>sqlplus chf/xifenfei@pdb
SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
上次成功登录时间: 星期五 12月 19 2014 21:00:26 +08:00
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
XFF_PDB@CHF> select * from v$version;
BANNER                                                 CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.          0
1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production                      0
CORE    12.1.0.2.0      Production                          0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ          0
ction
NLSRTL Version 12.1.0.2.0 - Production                      0
XFF_PDB@CHF>  create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na
me varchar2(100));
表已创建。
XFF_PDB@CHF> select object_name,object_type from user_objects;
OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
ISEQ$$_91982    SEQUENCE
T_XIFENFEI      TABLE
XFF_PDB@CHF> set long 10000
XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_XIFENFEI"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE
   9999999999999999999999999999 INCREMENT BY 1 START WITH 1
   CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "INMEMORY"
XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM');
INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM')
*
第 1 行出现错误:
ORA-32795: 无法插入到“始终生成”身份列
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM');
已创建 1 行。
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM');
已创建 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> col name for a30
XFF_PDB@CHF> select * from t_xifenfei;
        ID NAME
---------- ------------------------------
         1 WWW.XIFNEFEI.COM
         2 WWW.orasos.COM
XFF_PDB@CHF> update t_xifenfei set id=3 where id=2;
update t_xifenfei set id=3 where id=2
                      *
第 1 行出现错误:
ORA-32796: 无法更新“始终生成”身份列
XFF_PDB@CHF> delete from t_xifenfei where id=1;
已删除 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual;
   NEXTVAL
----------
         3
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM');
已创建 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> select * from t_xifenfei;
        ID NAME
---------- ------------------------------
         2 WWW.orasos.COM
         4 WWW.ORASOS.COM
XFF_PDB@CHF> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1
2';
会话已更改。
XFF_PDB@CHF>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM');
已创建 1 行。
XFF_PDB@CHF> COMMIT;
提交完成。
--跟踪trace文件
PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460
ad='7ff05d11a18' sqlid='21uzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM')
END OF STMT
PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)'
STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE  ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)'
WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971
XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI
TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);
表已更改。
XFF_PDB@CHF> drop sequence ISEQ$$_91982;
drop sequence ISEQ$$_91982
              *
第 1 行出现错误:
ORA-32794: 无法删除系统生成的序列
XFF_PDB@CHF> drop table t_xifenfei;
表已删除。
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
XFF_PDB@CHF> purge table t_xifenfei;
表已清除。
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
未选定行

这里的出来几个结论：
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

GENERATED BY DEFAULT AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI
TY,name varchar2(100)) tablespace users;
表已创建。
XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com');
已创建 1 行。
XFF_PDB@CHF> col name for a20
XFF_PDB@CHF> select * from t_xifenfei2;
        ID NAME
---------- --------------------
         1 www.xifenfei.com
         1 www.orasos.com
XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com');
insert into t_xifenfei2 values (null,'www.xifenfei.com')
                                *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("CHF"."T_XIFENFEI2"."ID")
XFF_PDB@CHF> desc t_xifenfei2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

得出结论：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A
S IDENTITY,name varchar2(100)) tablespace users;
表已创建。
XFF_PDB@CHF> desc t_xifenfei3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei3;
        ID NAME
---------- --------------------
         1 www.xifenfei.com

测试结论：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值

传统自增长列实现方法

XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace
users;
表已创建。
XFF_PDB@CHF> create sequence xff_sequence
  2  increment by 1
  3  minvalue 1
  4  nomaxvalue
  5  start with 1
  6  cache 20
  7  order;
序列已创建。
XFF_PDB@CHF> create or replace trigger xifenfei_id
  2  before insert on t_xifenfei4
  3  for each row
  4  begin
  5  select xff_sequence.nextval into :new.id from dual;
  6  end;
  7  /
触发器已创建
XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei4;
        ID NAME
---------- --------------------
         1 www.xifenfei.com

记录一次由于坏块和不恰当恢复引起各种ORA-600案例

朋友让我帮忙处理一个不能open的库,打开alert日志一看,傻眼了,里面是各种ORA-600的错误应有尽有,被折腾的够惨
故障后重启,无法启动主要表现在block坏块,引起的各种ORA-600等错误

Mon Mar 02 16:09:27 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 23 processes
Started redo scan
Completed redo scan
 read 962 KB redo, 256 data blocks need recovery
Started redo application at
 Thread 1: logseq 726, block 37343
Recovery of Online Redo Log: Thread 1 Group 3 Seq 726 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/oa/redo03.log
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 1673 OF FILE 3
Completed redo application of 0.27MB
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 3104 OF FILE 3
Mon Mar 02 16:09:27 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 3613 OF FILE 3
Mon Mar 02 16:09:28 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 272 OF FILE 3
Mon Mar 02 16:09:28 2015
RECOVERY OF THREAD 1 STUCK AT BLOCK 2512 OF FILE 3
Hex dump of (file 2, block 92889) in trace file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc
Corrupt block relative dba: 0x00816ad9 (file 2, block 92889)
Bad header found during preparing block for write
Data in bad block:
 type: 0 format: 0 rdba: 0x6ad90000
 last change scn: 0x0000.00c6a052 seq: 0x1 flg: 0x00
 spare1: 0x6 spare2: 0xa2 spare3: 0x5d7e
 consistency value in tail: 0xa0520001
 check value in block header: 0x0
 block checksum disabled
Mon Mar 02 16:09:28 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p007_4196.trc  (incident=3833):
ORA-00600: internal error code, arguments: [4502], [1], [], [], [], [], [], [], [], [], [], []
Mon Mar 02 16:09:28 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p013_4208.trc  (incident=3881):
ORA-00600: internal error code, arguments: [2037], [4259067], [4244307968], [159], [243], [0], [2162032704], [100728832], [], [], [], []
Slave exiting with ORA-1172 exception
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p009_4200.trc:
ORA-01172: recovery of thread 1 stuck at block 3613 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p001_4184.trc:
ORA-01172: recovery of thread 1 stuck at block 2512 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p021_4224.trc:
ORA-10388: parallel query server interrupt (failure)
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p021_4224.trc:
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc  (incident=3697):
ORA-00600: internal error code, arguments: [kcbzpbuf_1], [4], [1], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_3697/oa_dbw2_4158_i3697.trc
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0xD2DDB7, kcbs_shrink_pool()+705] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_mman_4152.trc  (incident=3673):
ORA-07445: exception encountered: core dump [kcbs_shrink_pool()+705] [SIGSEGV] [ADDR:0x0] [PC:0xD2DDB7] [SI_KERNEL(general_protection)] []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_3673/oa_mman_4152_i3673.trc
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_dbw2_4158.trc:
Mon Mar 02 16:09:34 2015
Instance terminated by DBW2, pid = 4158

第二次重启后增加新错误ORA-00600[17182]

Mon Mar 02 16:39:50 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_p002_4321.trc  (incident=4993):
ORA-00600: internal error code, arguments: [17182], [0x7F548C2BDBA8], [], [], [], [], [], [], [], [], [], []

进行了一些恢复处理后,日志中报错
主要体现在进行了不完全恢复,而且应该是对redo进行了重命名或者redo头损坏锁引起的一系列提示

Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 962 KB redo, 256 data blocks need recovery
Started redo application at
 Thread 1: logseq 726, block 37343
Recovery of Online Redo Log: Thread 1 Group 3 Seq 726 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/oa/redo03.log
RECOVERY OF THREAD 1 STUCK AT BLOCK 1673 OF FILE 3
Aborting crash recovery due to error 1172
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-01172: recovery of thread 1 stuck at block 1673 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-01172: recovery of thread 1 stuck at block 1673 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: alter  database open...
Tue Mar 03 11:17:59 2015
Sweep [inc][17178]: completed
Sweep [inc][17177]: completed
Sweep [inc2][17178]: completed
Tue Mar 03 11:18:00 2015
ALTER DATABASE RECOVER  database until cancel
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 24 slaves
ORA-279 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
ALTER DATABASE RECOVER    CONTINUE DEFAULT
Tue Mar 03 11:18:06 2015
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-00266: name of archived log file needed
ORA-266 signalled during: ALTER DATABASE RECOVER    CONTINUE DEFAULT  ...
ALTER DATABASE RECOVER CANCEL
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
Slave exiting with ORA-1547 exception
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_pr00_6701.trc:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf'
ORA-10879 signalled during: ALTER DATABASE RECOVER CANCEL ...
Tue Mar 03 11:18:06 2015
Checker run found 4 new persistent data failures
Tue Mar 03 11:18:13 2015
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 12986989
Resetting resetlogs activation ID 3278679642 (0xc36cae5a)
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:
ORA-00367: checksum error in log file header
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/oa/redo01.log'
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6644.trc:

再一步折腾,增加了_allow_resetlogs_corruption= TRUE之后数据库报ORA-600[2662]

Tue Mar 03 11:19:26 2015
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6864.trc  (incident=18195):
ORA-00600: internal error code, arguments: [2662], [0], [13007002], [0], [13016626], [4194545], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/oa/oa/incident/incdir_18195/oa_ora_6864_i18195.trc
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6864.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [13007002], [0], [13016626], [4194545], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 6864): terminating the instance due to error 704
Instance terminated by USER, pid = 6864
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (6864) as a result of ORA-1092
Tue Mar 03 11:19:29 2015
ORA-1092 : opitsk aborting process

进一步折腾,可以看出来undo已经被其offline,无法正常访问,导致系统报ORA-704和ORA-00376

Wed Mar 04 21:10:58 2015
SMON: enabling cache recovery
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17074.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/oa/undotbs01.dbf'
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17074.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/oa/undotbs01.dbf'
Error 704 happened during db open, shutting down database
USER (ospid: 17074): terminating the instance due to error 704
Instance terminated by USER, pid = 17074
ORA-1092 signalled during: alter database open...
opiodr aborting process unknown ospid (17074) as a result of ORA-1092
Wed Mar 04 21:11:00 2015
ORA-1092 : opitsk aborting process

通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)检测结果见附件(xifenfei_db_recover_20150304),这里可以知道undo 不知道怎么折腾的数据文件scn较大而且还offline,
通过一些列方法(bbed,隐含参数等)调整数据库scn,强制启动数据库,报如下错误

Wed Mar 04 22:50:23 2015
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 3nkd3g3ju5ph1, SCN: 0x0000.4000003e):
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 17807): terminating the instance due to error 704
Instance terminated by USER, pid = 17807
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (17807) as a result of ORA-1092

根据经验,该错误怀疑是文件头scn不够大,块延迟清理导致,进一步增加scn尝试,最后依旧是ORA-00704/ORA-00604/ORA-01555错误

Wed Mar 04 22:50:23 2015
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 3nkd3g3ju5ph1, SCN: 0x0000.4000003e):
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Errors in file /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_17807.trc:
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
Error 704 happened during db open, shutting down database
USER (ospid: 17807): terminating the instance due to error 704
Instance terminated by USER, pid = 17807
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (17807) as a result of ORA-1092

根据经验,在scn上做手脚估计难以解决给问题,对其启动过程做10046和errorstack分析发现

PARSING IN CURSOR #3 len=202 dep=2 uid=0 oct=3 lid=0 tim=1425481940448439 hv=3819099649 ad='64ff91af8' sqlid='3nkd3g3ju5ph1'
select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
END OF STMT
PARSE #3:c=1000,e=334,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=0,tim=1425481940448439
BINDS #3:
 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f5b3253a6f0  bln=22  avl=01  flg=05
  value=0
 Bind#1
  oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7f5b3253a6b8  bln=32  avl=06  flg=05
  value="PROPS$"
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f5b3253a688  bln=24  avl=02  flg=05
  value=1
EXEC #3:c=0,e=640,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,plh=2853959010,tim=1425481940449147
WAIT #3: nam='db file sequential read' ela= 5 file#=1 block#=345 blocks=1 obj#=37 tim=1425481940449186
WAIT #3: nam='db file sequential read' ela= 4 file#=1 block#=44528 blocks=1 obj#=37 tim=1425481940449221
WAIT #3: nam='db file sequential read' ela= 3 file#=1 block#=5505 blocks=1 obj#=37 tim=1425481940449247
*** 2015-03-04 23:12:20.450
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 2
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10_3550978943$" too small
----- Current SQL Statement for this session (sql_id=g64r07v2jn8nq) -----
SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR'

这里可以发现是数据库在启动的过程中需要执行SELECT NULL FROM PROPS$ WHERE NAME=’BOOTSTRAP_UPGRADE_ERROR’语句，而该语句递归调用了select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null 语句。既然这样通过一些方法避免数据库启动之时查询SELECT NULL FROM PROPS$ WHERE NAME=’BOOTSTRAP_UPGRADE_ERROR’语句，果然数据库启动成功。

知识点补充
ORA-600 [4502] [a]

Arg [a] ITL entry with a lock count
Meaning: During ITL cleanout we clear all row locks but the ITL entry
	 still thinks there is an uncleared lock. Ie: ITL has a locked
	 row but there are no locked rows in the block

大体意思是数据库发现undo 的itl已经被清除，但是block中的itl依然存在，从而出现ORA-600[4502],引起该问题除bug外主要原因是坏块

ORA-600 [2037] [a] [b] {c} [d] [e] [f] [g]

Arg [a] Relative Data Block Address (RDBA) that the redo vector is for
Arg [b] The Block format
Arg {c} RDBA in the block itself
Arg [d] The block type
Arg [e] The sequence number
Arg [f] Flags, if set
Arg [g] The return value from the block head/tail checker.
DESCRIPTION:
  During recovery we are examining a block to ensure that it is not
  corrupt prior to applying any change vectors.
  The block has failed this check and this exception is raised

大体意思是在恢复过程中，正在检查的块，以确保它在应用任何变化向量之前不损坏。如果检查失败排除该异常ORA-600[2037],引起该问题除bug外主要原因是坏块

ORA-600 [kcbzpbuf_1],[a],[b]

Arg [a] Corruption reason
Arg [b] Calculate checksum flag
Corruption reason:
#define KCBH_GOOD    0                                     /* block is valid */
#define KCBH_ZERO    1             /* block header was entirely zero on disk */
#define KCBH_BROKEN  2      /* corruption could be from a partial disk write */
#define KCBH_CHKVAL  3               /* The check value for the block failed */
#define KCBH_CORRUPT 4     /* this is the wrong block or is not a data block */
#define KCBH_ZERONG  5               /* all zero block and it is not allowed */
Calculate checksum flag:
The possible values are 1 (Generate Checksum - db_block_checksum is enabled - default value)
                        0 (do not generate checksum - db_block_checksum=false)

kcbzpbuf_1是该错误的源码函数

ORA-600 [17182] [a] [b] {c} [d] [e]

DESCRIPTION:
  Oracle has detected that the magic number in a memory chunk header has been overwritten.
  This is a heap (in memory) corruption and there is no underlying data corruption.
  The error may occur in the one of the process specific heaps
  (the Call heap, PGA heap, or session heap) or in the shared heap (SGA).

ORACLE 发现在内存中重要的块头被重新,但是没有基础数据损坏,大部分和数据块或者内存损坏有关系.

ORA-600 [4552] [a] [b] {c} [d] [e]

DESCRIPTION:
  This assertion is raised because we are trying to unlock the rows in a
  block, but receive an incorrect block type.
  The second argument is the block type received.

ORACLE尝试对某行进行解锁但是接收到了不正确的数据块类型,Arg [b]是接收到的数据块类型

ORA-600 [2662] [a] [b] {c} [d] [e]

DESCRIPTION:
  A data block SCN is ahead of the current SCN.
  The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN
  stored in a UGA variable.
  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662]
  internal error.
ARGUMENTS:
  Arg [a]  Current SCN WRAP
  Arg [b]  Current SCN BASE
  Arg {c}  dependent SCN WRAP
  Arg [d]  dependent SCN BASE
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

主要的含义就是oracle文件头scn比某个block dependent scn小从而出现该问题

给你的dmp文件(datapump)加上密码锁

从oracle 11.1.0.7开始oracle 支持data pump导出加密,从而实现dmp文件安全.不会因为dmp文件丢失而导致数据泄露.涉及数据泵加密参数主要有：ENCRYPTION,ENCRYPTION_ALGORITHM,ENCRYPTION_MODE,ENCRYPTION_PASSWORD几个参数.这里测试的是使用最简单方式实现datapump加密功能,如果需要更好的数据安全可以考虑实时密码钱包

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.
ENCRYPTION为加密dmp文件加密部分，其参数值可以有ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE，
如果只有ENCRYPTION_PASSWORD指定值，那么ENCRYPTION默认值为ALL
如果ENCRYPTION_PASSWORD和ENCRYPTION均为指定，那么默认值为NONE

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.
ENCRYPTION_ALGORITHM是指加密算法，参数值可以有AES128, AES192 and AES256。默认值为AES128

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].
ENCRYPTION_MODE指定加密方式，其参数值有DUAL, PASSWORD and TRANSPARENT。默认值为TRANSPARENT
DUAL表示你指定的加密的dmp文件在导入的时候可以通过密码方式或者加密钱包方式导入
PASSWORD表示指定密码方式创建dmp文件，你导入也需要提供密码
TRANSPARENT需要加密钱包方式导出和导入

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.
ENCRYPTION_PASSWORD指定加密密码

创建测试表

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 14:52:32 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> conn chf/xifenfei
Connected.
SQL> create table xifenfei (id number,name varchar2(50));
Table created.
SQL> insert into xifenfei values(&id,'&name');
Enter value for id: 1
Enter value for name: xifenfei
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(1,'xifenfei')
1 row created.
SQL> /
Enter value for id: 2
Enter value for name: www.xifenfei.com
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(2,'www.xifenfei.com')
1 row created.
SQL> /
Enter value for id: 3
Enter value for name: www.orasos.com
old   1: insert into xifenfei values(&id,'&name')
new   1: insert into xifenfei values(3,'www.orasos.com')
1 row created.
SQL> commit;
Commit complete.
SQL> col name for a50
SQL> set lines 100
SQL> select * from xifenfei;
        ID NAME
---------- --------------------------------------------------
         1 xifenfei
         2 www.xifenfei.com
         3 www.orasos.com

创建目录

SQL> create directory dir_xff as '/tmp';
Directory created.

不加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff dumpfile=none.dmp logfile=none.log
Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:29:13 2015
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
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei
directory=dir_xff dumpfile=none.dmp logfile=none.log
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 "CHF"."XIFENFEI"                            5.460 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/none.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:29:37 2015 elapsed 0 00:00:14

数据字典和数据都加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=ALL dumpfile=ALL.dmp
logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com
Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:06 2015
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
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=ALL
dumpfile=ALL.dmp logfile=ALL.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=********
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 "CHF"."XIFENFEI"                            5.468 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/ALL.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:13 2015 elapsed 0 00:00:06

数据加密导出

[oracle@localhost ~]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=DATA_ONLY
dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com
Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:33:52 2015
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
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=DATA_ONLY
dumpfile=DATA_ONLY.dmp logfile=DATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=********
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 "CHF"."XIFENFEI"                            5.468 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/DATA_ONLY.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:33:57 2015 elapsed 0 00:00:04

数据字典加密导出

[oracle@localhost tmp]$ expdp chf/xifenfei tables=xifenfei directory=dir_xff  ENCRYPTION=METADATA_ONLY
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=www.xifenfei.com
Export: Release 11.2.0.4.0 - Production on Wed Feb 11 15:50:00 2015
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
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff ENCRYPTION=METADATA_ONLY
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_MODE=PASSWORD ENCRYPTION_PASSWORD=********
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 "CHF"."XIFENFEI"                            5.460 KB       3 rows
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /tmp/METADATA_ONLY.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 11 15:50:04 2015 elapsed 0 00:00:03

删除测试表

SQL> drop table xifenfei purge;
Table dropped.

导入数据未输入密码

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff
dumpfile=METADATA_ONLY.dmp logfile=xifenfei.log
Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:03:13 2015
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
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.

导入数据密码错误

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.orasos.com
Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:05:46 2015
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
ORA-39002: invalid operation
ORA-39176: Encryption password is incorrect.

导入数据密码正确

[oracle@localhost tmp]$ impdp chf/xifenfei tables=xifenfei directory=dir_xff
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=www.xifenfei.com
Import: Release 11.2.0.4.0 - Production on Wed Feb 11 16:06:00 2015
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
Master table "CHF"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "CHF"."SYS_IMPORT_TABLE_01":  chf/******** tables=xifenfei directory=dir_xff
dumpfile=METADATA_ONLY.dmp logfile=METADATA_ONLY.log ENCRYPTION_PASSWORD=********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHF"."XIFENFEI"                            5.460 KB       3 rows
Job "CHF"."SYS_IMPORT_TABLE_01" successfully completed at Wed Feb 11 16:06:04 2015 elapsed 0 00:00:02

验证数据

[oracle@localhost tmp]$ sqlplus chf/xifenfei
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 11 16:06:09 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> set lines 150
SQL> col name for a50
SQL> select * from xifenfei;
        ID NAME
---------- --------------------------------------------------
         1 xifenfei
         2 www.xifenfei.com
         3 www.orasos.com

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

数据的安全越来越重要,不是说你的生产库安全，你的数据就一定安全了，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加密后,如果不输入或者错误的输入密码无法使用备份集,从而确保了备份集的安全.

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

在安全越来越重视的近体，我们不少时候需要对数据库中的某个表的敏感列数据(银行卡,身份证号码,金额等)进行加密，方式数据泄密,在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>

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.

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

有网友咨询数据库启动报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 异常恢复案例汇总

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

 

Patchsets
 l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)  17694377
 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)
 JAN2015  19769480 (12.1.0.2.2)  19954978 (12.1.0.2.2)  19720843 (12.1.0.2.1)
 OCT2014  19303936 (12.1.0.2.1)  19392646 (12.1.0.2.1)  N/A

 

12.1.0.1
 Description  PSU  GI PSU   Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 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)
 JAN2015  19769489 (11.2.0.4.5)  19854503  19955028 (11.2.0.4.5)  20127071
 OCT2014  19121551 (11.2.0.4.4)  19271443  19380115 (11.2.0.4.4)  19651773
 JUL2014  18522509 (11.2.0.4.3)  18681862  18706472 (11.2.0.4.3)  18842982
 APR2014  18031668 (11.2.0.4.2)  18139690  18139609 (11.2.0.4.2)  18296644
 JAN2014  17478514 (11.2.0.4.1)  17551709  N/A  17987366

 

11.2.0.3
 Description  PSU  SPU(CPU)  GI PSU  Bundle Patch (Windows64bit)  Bundle Patch(Windows32bit)
 JAN2015  19769496 (11.2.0.3.13)  19854461  19971343 (11.2.0.3.13)  20233168  20233167
 OCT2014  19121548 (11.2.0.3.12)  19271438  19440385 (11.2.0.3.12)  19618575  19618574
 JUL2014  18522512 (11.2.0.3.11)  18681866  18706488 (11.2.0.3.11)  18940194  18940193
 APR2014  18031683 (11.2.0.3.10)  18139695  18139678 (11.2.0.3.10)  18372244  18372243
 JAN2014  17540582 (11.2.0.3.9)  17478415  17735354 (11.2.0.3.9)  18075406  17906981
 OCT2013  16902043 (11.2.0.3.8)  17082364  17272731 (11.2.0.3.8)  17363850  17363844
 JUL2013  16619892 (11.2.0.3.7)  16742095  16742216 (11.2.0.3.7)  16803775  16803774
 APR2013  16056266 (11.2.0.3.6)  16294378  16083653 (11.2.0.3.6)  16345834  16345833
 JAN2013  14727310 (11.2.0.3.5)  14841409  14727347 (11.2.0.3.5)  16042648  16042647
 OCT2012  14275605 (11.2.0.3.4)  14390252  14275572 (11.2.0.3.4)  14613223  14613222
 JUL2012  13923374 (11.2.0.3.3)  14038787  13919095 (11.2.0.3.3)  14223718  14223717
 APR2012  13696216 (11.2.0.3.2)  13632717  13696251 (11.2.0.3.2)  13885389  13885388
 JAN2012  13343438 (11.2.0.3.1)  13466801  13348650 (11.2.0.3.1)  13413168  13413167

 

11.2.0.2
 Description  PSU   SPU(CPU)  GI PSU  Bundle Patch (Windows64bit)  Bundle Patch(Windows32bit)
 aOCT2013  17082367 (11.2.0.2.12)  17082375  17272753 (11.2.0.2.12)  17363838  17363837
 JUL2013  16619893 (11.2.0.2.11)  16742100  16742320 (11.2.0.2.11)  16345852  16345851
 APR2013  16056267 (11.2.0.2.10)  16294412  16166868 (11.2.0.2.10)  16345846  16345845
 JAN2013  14727315 (11.2.0.2.9)  14841437  14841385 (11.2.0.2.9)  16100399  16100398
 OCT2012  14275621 (11.2.0.2.8)  14390377  14390437 (11.2.0.2.8)  14672268  14672267
 JUL2012  13923804 (11.2.0.2.7)  14038791  14192201 (11.2.0.2.7)  14134043  14134042
 APR2012  13696224 (11.2.0.2.6)  13632725  13696242 (11.2.0.2.6)  13697074  13697073
 JAN2012  13343424 (11.2.0.2.5)  13343244  13653086 (11.2.0.2.5)  13413155  13413154
 OCT2011  12827726 (11.2.0.2.4)  12828071  12827731 (11.2.0.2.4)  13038788  13038787
 JUL2011  12419331 (11.2.0.2.3)  12419321  12419353 (11.2.0.2.3)  12714463  12714462
 APR2011  11724916 (11.2.0.2.2)  11724984  12311357 (11.2.0.2.2)  11896292  11896290
 JAN2011  10248523 (11.2.0.2.1)  N/A  N/A  10432053  10432052
 

11.2.0.1
 Description  PSU  CPU  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 aJUL2011  12419378 (11.2.0.1.6)  12419278  12429529  12429528
 APR2011  11724930 (11.2.0.1.5)  11724991  11731176  11883240
 JAN2011  10248516 (11.2.0.1.4)  10249532  10432045  10432044
 OCT2010  9952216 (11.2.0.1.3)  9952260  10100101  10100100
 JUL2010  9654983 (11.2.0.1.2)  9655013  9736865  9736864
 APR2010  9352237 (11.2.0.1.1)  9369797  N/A  N/A
 

11.1.0.7
 Description  PSU  SPU(CPU)  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
bJAN2015  19769499 (11.1.0.7.22)  19854433  20126915  20126914
bOCT2014  19152553 (11.1.0.7.21)  19274522  19609034  19609032
bJUL2014  18522513 (11.1.0.7.20)  18681875  18944208  18944207
bAPR2014  18031726 (11.1.0.7.19)  18139703  18372258  18372257
bJAN2014  17465583 (11.1.0.7.18)  17551415  17906936  17906935
bOCT2013  17082366 (11.1.0.7.17)  17082374  17363760  17363759
bJUL2013  16619896 (11.1.0.7.16)  16742110  16803788  16803787
bAPR2013  16056268 (11.1.0.7.15)  16308394  16345862  16345861
bJAN2013  14739378 (11.1.0.7.14)  14841452  15848067  15848066
 bOCT2012  14275623 (11.1.0.7.13)  14390384  14672313  14672312
 JUL2012  13923474 (11.1.0.7.12)  14038803  14109868  14109867
 APR2012  13621679 (11.1.0.7.11)  13632731  13715810  13715809
 JAN2012  13343461 (11.1.0.7.10)  13343453  13460956  13460955
 OCT2011  12827740 (11.1.0.7.9)  12828097  12914916  12914915
 JUL2011  12419384 (11.1.0.7.8)  12419265  12695278  12695277
 APR2011  11724936 (11.1.0.7.7)  11724999  11741170  11741169
 JAN2011  10248531 (11.1.0.7.6)  10249534  10350788  10350787
 OCT2010  9952228  (11.1.0.7.5)  9952269  9773825  9773817
 JUL2010  9654987 (11.1.0.7.4)  9655014  9869912  9869911
 APR2010  9352179 (11.1.0.7.3)  9369783  9392335  9392331
 JAN2010  9209238 (11.1.0.7.2)  9114072  9166861  9166858
 OCT2009  8833297 (11.1.0.7.1)  8836375  8928977  8928976
 JUL2009  N/A  8534338  8553515  8553512
 APR2009  N/A  8290478  8343070  8343061
 

11.1.0.6
 Description  CPU  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)
 aJUL2009  8534378  8563155  8563154
 APR2009  8290402  8333657  8333655
 JAN2009  7592335  7631981  7631980
 OCT2008  7375639  7378393  7378392
 JUL2008  7150417  7210197  7210195
 APR2008  6864063  6867180  6867178
 

10.2.0.5
 Description  PSU  SPU(CPU)  Bundle Patch (Windows64bit)  Bundle Patch (Windows32bit)  Bundle Patch(WindowsItanium)
abJUL2013  16619894 (10.2.0.5.12)  16742123  16803782  16803780  16803781
 bAPR2013  16056270 (10.2.0.5.11)  16270946  16345857  16345855  16345856
 bJAN2013  14727319 (10.2.0.5.10)  14841459  15848062  15848060  15848061
 bOCT2012  14275629 (10.2.0.5.9)  14390396  14553358  14553356  14553357
 bJUL2012  13923855 (10.2.0.5.8)  14038805  14134053  14134051  14134052
 bAPR2012  13632743 (10.2.0.5.7)  13632738  13654815  13654814  13870404
 JAN2012  13343471 (10.2.0.5.6)  13343467  b13460968 b13460967  N/A
 bOCT2011  12827745 (10.2.0.5.5)  12828105  c12914913  12914911  N/A
 JUL2011  12419392 (10.2.0.5.4)  12419258  12429524  12429523  N/A
 APR2011  11724962 (10.2.0.5.3)  11725006  12328269  12328268  N/A
 JAN2011  10248542 (10.2.0.5.2)  10249537  10352673  10352672  N/A
 OCT2010  9952230 (10.2.0.5.1)  9952270  10099855  10058290  N/A
 

10.2.0.4
 Description  PSU  SPU(CPU)  Bundle Patch (Windows32bit)  Bundle Patch (Windows64bit)  Bundle Patch(WindowsItanium)
 bgJUL2013  16619897 (10.2.0.4.17)  16742253  N/A  N/A  N/A
 bgAPR2013  16056269 (10.2.0.4.16)  16270931  N/A  N/A  N/A
 bgJAN2013  14736542 (10.2.0.4.15)  14841471  N/A  N/A  N/A
bgOCT2012  14275630 (10.2.0.4.14)  14390410  N/A  N/A  N/A
bgJUL2012  13923851 (10.2.0.4.13)  14038814  N/A  N/A  N/A
 abAPR2012  12879933 (10.2.0.4.12)  12879926  13928775  13928776  N/A
 JAN2012  12879929 (10.2.0.4.11)  12879912  b13654060  N/A  N/A
 bOCT2011  12827778 (10.2.0.4.10)  12828112  12914908  12914910  12914909
 JUL2011  12419397 (10.2.0.4.9)  12419249  12429519  12429521  12429520
 APR2011  11724977 (10.2.0.4.8)  11725015  12328501  12328503  12328502
 JAN2011  10248636 (10.2.0.4.7)  10249540  10349197  10349200  10349198
 OCT2010  9952234 (10.2.0.4.6)  9952272  10084980  10084982  10084981
 JUL2010  9654991 (10.2.0.4.5)  9655017  9777076  9777078  9777077
 APR2010  9352164 (10.2.0.4.4)  9352191  9393548  9393550  9393549
 JAN2010  9119284 (10.2.0.4.3)  9119226  9169457  9169460  9169458
 OCT2009  8833280 (10.2.0.4.2)  8836308  8880857  8880861  8880858
 JUL2009  8576156 (10.2.0.4.1)  8534387  8559466  8559467  8541782
 APR2009  N/A  8290506  8307237  8307238  8333678
 JAN2009  N/A  7592346  7584866  7584867  N/A
 OCT2008  N/A  7375644  7386320  7386321  N/A
 JUL2008  N/A  7150470  7218676  7218677  N/A
 

10.2.0.3
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)  Bundle Patch (Windows64bit)
 aJAN2009  7592354  7631956  7631958  7631957
 OCT2008  7369190  7353782  7353784  7353785
 JUL2008  7150622  7252496  7252497  7252498
 APR2008  6864068  6867054  6867055  6867056
 JAN2008  6646853  6637237  6637238  6637239
 OCT2007  6394981  6430171  6430173  6430174
 JUL2007  6079591  6116131  6038242  6116139
 APR2007  5901891  5948242  5916262  5948243
 JAN2007  5881721  5846376  5846377  5846378
 

10.2.0.2
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)   Bundle Patch (Windows64bit)  Bundle Patch (WindowsItanium)
 iJAN2009  7592355  N/A  N/A  N/A
 hOCT2008  7375660  N/A  N/A  N/A
 hJUL2008  7154083  N/A  N/A  N/A
 hAPR2008  6864071  N/A  N/A  N/A
 aJAN2008  6646850  N/A  N/A  N/A
 fOCT2007  6394997  6397028  6397030  6397029
 JUL2007  6079588  6013105  6013121  6013118
 APR2007  5901881  5912173  5912179  5912176
 JAN2007  5689957  5716143  5699839  5699824
 OCT2006  5490848  5502226  5500921  5500894
 JUL2006  5225799  5251025  5251028  5251026
 APR2006  5079037  5140461  5140567  5140508
 

10.2.0.1
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (Windows64bit)  Bundle Patch (WindowsItanium)
 APR2007  5901880  N/A  N/A  N/A
 JAN2007  5689937  5695784  5695786  5695785
 OCT2006  5490846  5500927  5500954  5500951
 JUL2006  5225798  5239698  5239701  5239699
 APR2006  5049080  5059238  5059261  5059251
 JAN2006  4751931  4751539  4770480  4751549
 

10.1.0.5
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)   Bundle Patch (WindowsItanium)
 JAN2012  13343482  13413002  13413003
 OCT2011  12828135  12914905  12914906
 JUL2011  12419228  12429517  12429518
 APR2011  11725035  11731119  11731120
 JAN2011  N/A  N/A  N/A
 OCT2010  9952279  10089559  10089560
 JUL2010  9655023  9683651  9683652
 APR2010  9352208  9390288  9390289
 JAN2010  9119261  9187104  9187105
 OCT2009  8836540  8785211  8785212
 JUL2009  8534394  8656224  8656226
 APR2009  8290534  8300356  8300360
 JAN2009  7592360  7486619  7586049
 OCT2008  7375686  7367493  7367494
 JUL2008  7154097  7047034  7047037
 APR2008  6864078  6867107  6867108
 JAN2008  6647005  6637274  6637275
 OCT2007  6395024  6408393  6408394
 JUL2007  6079585  6115804  6115818
 APR2007  5901877  5907304  5907305
 JAN2007  5689908  5716295  5634747
 OCT2006  5490845  5500883  5500885
 JUL2006  5225797  5251148  5251140
 APR2006  5049074  5057606  5057609
 JAN2006  4751932  4882231  4882236
 

10.1.0.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 APR2007  5901876  5909871  5909879
 JAN2007  5689894  5695771  5695772
 OCT2006  5490844  5500878  5500880
 JUL2006  5225796  5239736  5239737
 APR2006  5049067  5059200  5059227
 JAN2006  4751928  4751259  4745040
 OCT2005  4567866  4579182  4579188
 JUL2005  4392423  4440706  4404600
 APR2005  4210374  4287619  4287611
 

10.1.0.3
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JAN2007  5923277  N/A  N/A
 OCT2006  5566825  N/A  N/A
 JUL2006  5435164  N/A  N/A
 APR2006  5158022  N/A  N/A
 JAN2006  4751926  4741077  4741084
 OCT2005  4567863  4567518  4567523
 JUL2005  4392409  4389012  4389014
 APR2005  4193286  4269715  4158888
 JAN2005  4003062  4074232  3990812
 

10.1.0.2
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 APR2005  4193293  4181849  4213305
 JUL2005  4400766  4388944  4388948
 JAN2005  4003051  4104364  4083038
 

9.2.0.8
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JUL2010  9655027  9683644  9683645
 APR2010  9352224  9390286  N/A
 JAN2010  9119275  9187106  N/A
 OCT2009  8836758  8785185  8785186
 JUL2009  8534403  8427417  8427418
 APR2009  8290549  8300340  8300346
 JAN2009  7592365  7703210  7703212
 OCT2008  7375695  7394394  7394402
 JUL2008  7154111  7047026  7047029
 APR2008  6864082  6867138  6867139
 JAN2008  6646842  6637265  6637266
 OCT2007  6395038  6417013  6417014
 JUL2007  6079582  6130293  6130295
 APR2007  5901875  5916268  5916275
 JAN2007  N/A  N/A  N/A
 OCT2006  5490859  5652380  5639519
 

9.2.0.7
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JUL2007  6079579  6146759  6146748
 APR2007  5901872  5907274  5907275
 JAN2007  5689875  5654905  5654909
 OCT2006  5490841  5500873  5500874
 JUL2006  5225794  5250980  5250981
 APR2006  5049060  5064365  5064364
 JAN2006  4751923  4751528  4741074
 OCT2005  4567854  4579590  4579599
 JUL2005  4547566  N/A  N/A
 

9.2.0.6
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 OCT2006  5490840  5500865  5500871
 JUL2006  5225793  5239794  5239793
 APR2006  5049051  5059614  5059615
 JAN2006  4751921  4751261  4751262
 OCT2005  4567846  4579093  4579097
 JUL2005  4392392  4445852  4401917
 APR2005  4193295  4269928  4213298
 

9.2.0.5
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 OCT2006  5689708  N/A  N/A
 JUL2006  5435138  N/A  N/A
 APR2006  5219762  N/A  N/A
 OCT2005  4560421  N/A  N/A
 JUL2005  4392256  4387563  4391819
 APR2005  4193299  4195791  4214192
 JAN2005  4003006  4104374  3990809
 

9.2.0.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)  Bundle Patch (WindowsItanium)
 JAN2005  4002994  4104369  4083202
 

8.1.7.4
 Description  CPU (Unix/Linux)  Bundle Patch (Windows32bit)
 JAN2007  5689799  5686514
 OCT2006  5490835  5496067
 JUL2006  5225788  5236412
 APR2006  5045247  5057601
 JAN2006  4751906  4751570
 OCT2005  4560405  4554818
 JUL2005  4392446  4437058
 APR2005  4193312  4180163
 JAN2005  4002909  3921893

OJVM PSU Patches

 

12.1.0.2
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows)  Combo OJVM + DB PSU  Combo OJVM + GI PSU  Combo OJVM +  DB BP
 JAN2015  19877336 (12.1.0.2.2)  20225938 (12.1.0.2.1)  20132434  20132450  20132462
 OCT2014 (12.1.0.2.1)  19282028  19791366  19791375  19791399

 

12.1.0.1
 Description  OJVM PSU (Linux/Unix)  OJVM BP (Windows)  Combo OJVM + DB PSU  Combo OJVM + GI PSU  Combo OJVM +  DB BP  Generic JDBC
 JAN2015 (12.1.0.1.2)  19877342  20225916  20132482  20132489  N/A
 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  Combo OJVM +  DB BP  Generic JDBC
 JAN2015 (11.2.0.4.2)  19877440  20225982  20132580  20132517  20132615 N/A
 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  Combo OJVM +  DB BP  Generic JDBC
 JAN2015 (11.2.0.3.2) 19877443  20227195 20132646 20132635  20132651  N/A
 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 Combo OJVM +  DB BP Generic JDBC
 JAN2015 (11.1.0.7.2)  19877446  20227146  20132677  20132669  N/A
 OCT2014 (11.1.0.7.1)  19282002  19806118  19791436  19791434  19852363

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

10g dataguard 只读和应用归档互换脚本

在Oracle 11g中能够通过active dataguard特性实现备库只读而且同步应用日志,10g及其以前版本,在应用日志的时候库是不能把打开到read only模式,对于一些实时性要求不是特别高的应用,比如他们可以实现在白天只读,查询业务,晚上应用日志.如果人工每天做类似处理工作量太大,这里因为有朋友需要,基于win平台写了类似功能脚本,通过计划任务调用来实现白天只读,晚上应用日志的功能
数据库从应用归档模式转换为只读模式

--change_open.bat
sqlplus / as sysdba @C:\oracle\product\script\change_open.sql
--change_open.sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
alter database open read only;
exit;

数据库从只读模式转换到应用归档模式

--change_mount.bat
sqlplus / as sysdba @C:\oracle\product\script\change_mount.sql
--change_mount.sql
shutdown immediate;
startup mount ;
alter system register;
alter system register;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT
LOGFILE DISCONNECT FROM SESSION;
conn sys/oracle@ntsy as sysdba
alter system set log_archive_dest_state=defer;
alter system set log_archive_dest_state=enable;
alter system switch logfile;
exit;

物理备库在read only时报ORA-01552错误处理

物理备库在read only时报ORA-01552错误

Tue Jan 06 11:53:38 中国标准时间 2015
alter database open read only
Tue Jan 06 11:53:38 中国标准时间 2015
SMON: enabling cache recovery
Tue Jan 06 11:53:39 中国标准时间 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Completed: alter database open read only
Tue Jan 06 11:54:04 中国标准时间 2015
Errors in file c:\oracle\product\10.2.0\admin\ntsy\udump\ntsy_ora_9080.trc:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01552: 非系统表空间 'MY_SPACE' 不能使用系统回退段
ORA-06512: 在 line 2

分析trace文件

*** ACTION NAME:() 2015-01-06 11:54:04.828
*** MODULE NAME:(sqlplus.exe) 2015-01-06 11:54:04.828
*** SERVICE NAME:(SYS$USERS) 2015-01-06 11:54:04.828
*** SESSION ID:(1284.9) 2015-01-06 11:54:04.828
Error in executing triggers on connect internal
*** 2015-01-06 11:54:04.828
ksedmp: internal or fatal error
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01552: 非系统表空间 'MY_SPACE' 不能使用系统回退段
ORA-06512: 在 line 2
*** 2015-01-06 11:54:05.843
Process diagnostic dump for ORACLE.EXE (MMNL), OS id=10492,
pid: 13, proc_ser: 1, sid: <no session>

这里可以看出来，是由于执行触发器导致该问题,根据经验第一感觉很可能是logon之类的触发器导致。

查询触发器

SQL> select trigger_name,trigger_type,OWNER from dba_triggers where owner='OP';
TRIGGER_NAME                   TRIGGER_TYPE     OWNER
------------------------------ ---------------- ------------------------------
LOGAD                          AFTER EVENT      OP
TR_TRACE_DDL                   AFTER EVENT      OP

只有这两个触发器是基于事件的,另外从名字和dba_source中确定

SQL> select text from dba_source where name='LOGAD';
TEXT
--------------------------------------------------------------------------------
TRIGGER "OP".logad after logon on database
begin
insert into logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'), SYSDATE,SYS_CO
NTEXT('USERENV','IP_ADDRESS')) ;
end;
已选择6行。
SQL> select text from dba_source where name='TR_TRACE_DDL';
TEXT
--------------------------------------------------------------------------------
TRIGGER "OP".tr_trace_ddl
AFTER ddl
ON database
DECLARE
sql_text ora_name_list_t;
state_sql ddl$trace.ddl_sql%TYPE;
BEGIN
FOR i IN 1..ora_sql_txt(sql_text) LOOP
state_sql := state_sql||sql_text(i);
END LOOP;
TEXT
--------------------------------------------------------------------------------
INSERT INTO ddl$trace(login_user,audsid,machine,ipaddress,
schema_user,schema_object,ddl_time,ddl_sql)
VALUES(ora_login_user,userenv('SESSIONID'),sys_context('userenv','host'),
sys_context('userenv','ip_address'),ora_dict_obj_owner,ora_dict_obj_name,SYSDATE
,state_sql);
EXCEPTION
WHEN OTHERS THEN
-- sp_write_log('捕获DDL语句异常错误：'||SQLERRM);
null;
END tr_trace_ddl;

基本上确定LOGAD是登录触发器,tr_trace_ddl是记录ddl触发器,那现在问题应该出在LOGAD的触发器上.因为该触发器在备库上当有用户登录之时,他也会工作插入记录到logad表中,由于数据库是只读,因此就出现了类似ORA-01552错误

解决方法
在触发器中加判断数据库角色条件,当数据库为物理备库之时才执行dml操作

SQL> CREATE OR REPLACE TRIGGER "OP".logad
  2  AFTER LOGON on database
  3  declare
  4  db_role varchar2(30);
  5  begin
  6  select database_role into db_role from v$database;
  7  If db_role <> 'PHYSICAL STANDBY' then
  8  insert into op.logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'),
  9  SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS')) ;
 10  end if;
 11  end;
 12  /
Warning: Trigger created with compilation errors.
SQL> show error;
Errors for TRIGGER "OP".logad:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/1      PL/SQL: SQL Statement ignored
4/40     PL/SQL: ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant select on v_$database to op;
Grant succeeded.
SQL> CREATE OR REPLACE TRIGGER "OP".logad
  2  AFTER LOGON on database
  3  declare
  4  db_role varchar2(30);
  5  begin
  6  select database_role into db_role from v$database;
  7  If db_role <> 'PHYSICAL STANDBY' then
  8  insert into op.logad values (SYS_CONTEXT('USERENV', 'SESSION_USER'),
  9 SYSDATE,SYS_CONTEXT('USERENV','IP_ADDRESS')) ;
 10  end if;
 12  end;
 12  /
Trigger created.

数据库open正常

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel
Tue Jan 06 13:51:20 中国标准时间 2015
alter database open read only
Tue Jan 06 13:51:21 中国标准时间 2015
SMON: enabling cache recovery
Tue Jan 06 13:51:21 中国标准时间 2015
Database Characterset is ZHS16GBK
Opening with internal Resource Manager plan
replication_dependency_tracking turned off (no async multimaster replication found)
Physical standby database opened for read only access.
Tue Jan 06 13:51:23 中国标准时间 2015
db_recovery_file_dest_size of 102400 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Tue Jan 06 13:51:23 中国标准时间 2015
Completed: alter database open read only