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

记录一次由于坏块和不恰当恢复引起各种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 (Windows^64bit)	Bundle Patch (Windows^32bit)
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 (Windows^64bit)	Bundle Patch(Windows^32bit)
JAN2015	19769496 (11.2.0.3.13)	19854461	19971343 (11.2.0.3.13)	20233168	20233167
OCT2014	19121548 (11.2.0.3.12)	19271438	19440385 (11.2.0.3.12)	19618575	19618574
JUL2014	18522512 (11.2.0.3.11)	18681866	18706488 (11.2.0.3.11)	18940194	18940193
APR2014	18031683 (11.2.0.3.10)	18139695	18139678 (11.2.0.3.10)	18372244	18372243
JAN2014	17540582 (11.2.0.3.9)	17478415	17735354 (11.2.0.3.9)	18075406	17906981
OCT2013	16902043 (11.2.0.3.8)	17082364	17272731 (11.2.0.3.8)	17363850	17363844
JUL2013	16619892 (11.2.0.3.7)	16742095	16742216 (11.2.0.3.7)	16803775	16803774
APR2013	16056266 (11.2.0.3.6)	16294378	16083653 (11.2.0.3.6)	16345834	16345833
JAN2013	14727310 (11.2.0.3.5)	14841409	14727347 (11.2.0.3.5)	16042648	16042647
OCT2012	14275605 (11.2.0.3.4)	14390252	14275572 (11.2.0.3.4)	14613223	14613222
JUL2012	13923374 (11.2.0.3.3)	14038787	13919095 (11.2.0.3.3)	14223718	14223717
APR2012	13696216 (11.2.0.3.2)	13632717	13696251 (11.2.0.3.2)	13885389	13885388
JAN2012	13343438 (11.2.0.3.1)	13466801	13348650 (11.2.0.3.1)	13413168	13413167

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


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


11.1.0.7
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)
^{^{^{^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 (Windows^64bit)	Bundle Patch (Windows^32bit)
^aJUL2009	8534378	8563155	8563154
APR2009	8290402	8333657	8333655
JAN2009	7592335	7631981	7631980
OCT2008	7375639	7378393	7378392
JUL2008	7150417	7210197	7210195
APR2008	6864063	6867180	6867178


10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch (Windows^64bit)	Bundle Patch (Windows^32bit)	Bundle Patch(Windows^Itanium)
^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 (Windows^32bit)	Bundle Patch (Windows^64bit)	Bundle Patch(Windows^Itanium)
^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 (Windows^32bit)	Bundle Patch (Windows^Itanium)	Bundle Patch (Windows^64bit)
^aJAN2009	7592354	7631956	7631958	7631957
OCT2008	7369190	7353782	7353784	7353785
JUL2008	7150622	7252496	7252497	7252498
APR2008	6864068	6867054	6867055	6867056
JAN2008	6646853	6637237	6637238	6637239
OCT2007	6394981	6430171	6430173	6430174
JUL2007	6079591	6116131	6038242	6116139
APR2007	5901891	5948242	5916262	5948243
JAN2007	5881721	5846376	5846377	5846378


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


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


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


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


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


10.1.0.2
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
APR2005	4193293	4181849	4213305
JUL2005	4400766	4388944	4388948
JAN2005	4003051	4104364	4083038


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


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


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


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


9.2.0.4
Description	CPU (Unix/Linux)	Bundle Patch (Windows^32bit)	Bundle Patch (Windows^Itanium)
JAN2005	4002994	4104369	4083202


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

OJVM PSU Patches


12.1.0.2
Description	OJVM PSU (Linux/Unix)	OJVM BP (Windows)	Combo OJVM + DB PSU	Combo OJVM + GI PSU	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

windows rman自动备份并传输到远程服务器处理方法

在linux中，要使用rman备份后传输到远程服务器上,可以选择ftp,scp,nfs等方式实现,在win主机上可以配置ftp或者共享实现.linux的解决方法已经很多,这里重点提供win上面实现rman备份且传输到远程服务器的解决方法,简单实现异地备份方法:
1.win配置共享目录，而且设置远程服务器有写权限,如果省事可以配置everyone有读写权限
2.创建相关备份目录,这里主要是rmanfile,rmanscript,rmanlog
3.编写rman备份脚本

CONFIGURE RETENTION POLICY TO REDUNDANCY = 7;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup  as compressed backupset database format 'E:\backup_db\rmanfile\full_%T_%U.rman';
sql 'alter system archive log current';
backup  as compressed backupset archivelog  all format 'E:\backup_db\rmanfile\arch_%T_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'E:\backup_db\rmanfile\ctl_%T_%U.rman' current controlfile;
backup spfile format 'E:\backup_db\rmanfile\spfile_%T_%U.rman' ;
exit;

4.调用rman备份脚本

rman target / cmdfile=E:\backup_db\scriptfile\backup_db.rman
log=E:\backup_db\logfile\rmanlog_%date:~0,4%%date:~5,2%%date:~8,2%.log

5.拷贝到远程脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

copy /y e:\backup_db\rmanfile\*_%date:~0,4%%date:~5,2%%date:~8,2%_*.RMAN \\192.168.13.40\oracle_backup

6.删除远程服务器N天前备份脚本
需要注意是按照备份集中的日期作为标记来删除的，也就是说，一次备份最好不要跨天

@echo off
set DaysAgo=5
call :DateToDays %date:~0,4% %date:~5,2% %date:~8,2% PassDays
set /a PassDays-=%DaysAgo%
call :DaysToDate %PassDays% DstYear DstMonth DstDay
del \\192.168.13.40\oracle_backup\*_%DstYear%%DstMonth%%DstDay%_*.RMAN
goto :eof
:DateToDays %yy% %mm% %dd% days
setlocal ENABLEEXTENSIONS
set yy=%1&set mm=%2&set dd=%3
if 1%yy% LSS 200 if 1%yy% LSS 170 (set yy=20%yy%) else (set yy=19%yy%)
set /a dd=100%dd%%%100,mm=100%mm%%%100
set /a z=14-mm,z/=12,y=yy+4800-z,m=mm+12*z-3,j=153*m+2
set /a j=j/5+dd+y*365+y/4-y/100+y/400-2472633
endlocal&set %4=%j%&goto :EOF
:DaysToDate %days% yy mm dd
setlocal ENABLEEXTENSIONS
set /a a=%1+2472632,b=4*a+3,b/=146097,c=-b*146097,c/=4,c+=a
set /a d=4*c+3,d/=1461,e=-1461*d,e/=4,e+=c,m=5*e+2,m/=153,dd=153*m+2,dd/=5
set /a dd=-dd+e+1,mm=-m/10,mm*=12,mm+=m+3,yy=b*100+d-4800+m/10
(if %mm% LSS 10 set mm=0%mm%)&(if %dd% LSS 10 set dd=0%dd%)
endlocal&set %2=%yy%&set %3=%mm%&set %4=%dd%&goto :EOF

7.配置计划任务,让定时执行相关脚本

升级数据库到10.2.0.5遭遇ORA-00918: column ambiguously defined

一个数据库从10201升级到10205之后,出现ORA-00918错误,查询mos发现在以前版本中是bug,Oracle好像在10205中把它修复了,结果就是以前应用的sql无法正常执行.这次升级的结果就是客户晚上3点联系开发商紧急修改程序。再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

执行报错ORA-00918
多个表JOIN连接,由于在select中的列未指定表名，而且该列在多个表中有，因此在10205中报ORA-00918错误，Oracle认为在以前的版本中是 Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN. 升级到10.2.0.5, 11.1.0.7 and 11.2.0.2版本，需要注意此类问题。修复bug没事,但是修复了之后导致系统需要修改sql才能够运行,确实让人很无语

SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm,
  4             bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
           bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
           *
ERROR at line 4:
ORA-00918: column ambiguously defined
SQL> select COLUMN_NAME,TABLE_NAME from DBA_tab_columns where column_name='BQ'
  2  AND TABLE_NAME IN('YZ','ZY','PC');
COLUMN_NAME                    TABLE_NAME
------------------------------ ------------------------------
BQ                             ZY
BQ                             YZ

10.2.0.1中执行正常

E:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 1月 3 14:09:51 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb, zyh,ch,xm
,
  4             bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), '
')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
已选择19804行。
执行计划
----------------------------------------------------------
ERROR:
ORA-00604: 递归 SQL 级别 2 出现错误
ORA-16000: 打开数据库以进行只读访问
SP2-0612: 生成 AUTOTRACE EXPLAIN 报告时出错
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      41945  consistent gets
          0  physical reads
          0  redo size
    2075973  bytes sent via SQL*Net to client
      14989  bytes received via SQL*Net from client
       1322  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      19804  rows processed

10.2.0.5库中同名列增加表名前缀执行OK


SQL> set autot trace
SQL> set lines 100
SQL> SELECT   yz_id, item_code, DECODE (yzlx, 0, '长期医嘱', '临时医嘱') yzlx,
  2             item_name, gg, sl || sldw sl, zyjs, yf, a.pc, zbj, zbh,
  3             TO_CHAR (dcl, 'fm9999990.009') || dcldw dcl, a.bz, lb,zyh,ch,xm,
  4             a.bq, cfh, lrysdm, lrysxm, lrrq, hdrdm, hdrxm, hdrq, sender_code,
  5             sender_name, send_date, tzysdm, tzysxm, tzrq, ksrq, zxfy,
  6             lb_yp_yl, zsq_code
  7        FROM op.yz a LEFT OUTER JOIN op.pc b
  8             ON NVL (TRIM (UPPER (a.pc)), ' ') = NVL (TRIM (UPPER (b.pc)), ' ')
  9             LEFT JOIN op.zy p ON a.zyh = p.zyh
 10       WHERE p.cy='在院' AND p.new_patient='1'
 11         AND upper(nvl(p.bj,1))<> 'Y'
 12         AND (state = '已核对')
 13         AND is_in_bill IS NULL
 14    ORDER BY ksrq, yz_id ;
20629 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3468887510
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    10 |  2580 |  2968   (2)| 00:00:36 |
|   1 |  SORT ORDER BY                |            |    10 |  2580 |  2968   (2)| 00:00:36 |
|*  2 |   HASH JOIN OUTER             |            |    10 |  2580 |  2967   (2)| 00:00:36 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| YZ         |     3 |   672 |    42   (0)| 00:00:01 |
|   4 |     NESTED LOOPS              |            |    10 |  2390 |  2963   (2)| 00:00:36 |
|*  5 |      TABLE ACCESS FULL        | ZY         |     3 |    45 |  2917   (2)| 00:00:36 |
|*  6 |      INDEX RANGE SCAN         | DZBLYZ_ZYH |   118 |       |     2   (0)| 00:00:01 |
|   7 |    TABLE ACCESS FULL          | PC         |    33 |   627 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(NVL(TRIM(UPPER("A"."PC")),' ')=NVL(TRIM(UPPER("B"."PC"(+))),' '))
   3 - filter("A"."STATE"='已核对' AND "A"."IS_IN_BILL" IS NULL)
   5 - filter("P"."CY"='在院' AND UPPER(NVL("P"."BJ",'1'))<>'Y' AND
              "P"."NEW_PATIENT"='1')
   6 - access("A"."ZYH"="P"."ZYH")
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      42121  consistent gets
          0  physical reads
          0  redo size
    2181383  bytes sent via SQL*Net to client
      15617  bytes received via SQL*Net from client
       1377  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      20629  rows processed

Bug 5368296: SQL NOT GENERATING ORA-918 WHEN USING JOIN
Bug 12388159 : SQL REPORTING ORA00918 AFTER UPGRADE TO 10.2.0.5.0
再次提醒：再小的系统数据库升级都需要做,功能测试,SPA测试,确保升级后功能和性能都正常.

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

Patchsets

PSU, SPU(CPU), Bundle Patches

12.1.0.2

12.1.0.1

11.2.0.4

11.2.0.3

11.2.0.2

11.2.0.1

11.1.0.7

11.1.0.6

10.2.0.5

10.2.0.4

10.2.0.3

10.2.0.2

10.2.0.1

10.1.0.5

10.1.0.4

10.1.0.3

10.1.0.2

9.2.0.8

9.2.0.7

9.2.0.6

9.2.0.5

9.2.0.4

8.1.7.4