标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:十月 2016
ORA-20011 KUP-11024错误分析
数据库alert日志出现ORA-20011 KUP-11024等错误
Thu Sep 22 18:00:31 2016 DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout KUP-11024: This external table can only be accessed from within a Data Pump job.
从报错的信息看应该是数据库收集统计信息报错(GATHER_STATS_JOB),但是报错原因是由于访问外部表导致,而该外部表很可能和data pump有关系.
查看trace日志
[oracle@xifenfei]$ more /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc Trace file /u1/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j002_2686.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u1/oracle/pruduct/11.2.0.3 System name: Linux Node name: xifenfei Release: 2.6.32-220.el6.x86_64 Version: #1 SMP Wed Nov 9 08:03:13 EST 2011 Machine: x86_64 Instance name: xifenfei Redo thread mounted by this instance: 1 Oracle process number: 356 Unix process pid: 2686, image: oracle@xifenfei (J002) *** 2016-09-22 18:00:31.939 *** SESSION ID:(835.16363) 2016-09-22 18:00:31.939 *** CLIENT ID:() 2016-09-22 18:00:31.939 *** SERVICE NAME:(SYS$USERS) 2016-09-22 18:00:31.939 *** MODULE NAME:(DBMS_SCHEDULER) 2016-09-22 18:00:31.939 *** ACTION NAME:(ORA$AT_OS_OPT_SY_10669) 2016-09-22 18:00:31.939 ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout KUP-11024: This external table can only be accessed from within a Data Pump job. *** 2016-09-22 18:00:31.939 DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$012D00070001"','""', ...) DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout KUP-11024: This external table can only be accessed from within a Data Pump job. *** 2016-09-22 18:00:31.960 DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DWDBA"','"ET$01D10D4F0001"','""', ...) DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout KUP-11024: This external table can only be accessed from within a Data Pump job.
通过trace文件,我们已经可以明确是由于数据库对DWDBA.ET$012D00070001和DWDBA.ET$01D10D4F0001这两个表收集统计信息时候报的上述alert日志中看到的错误.
查询数据库记录
SYS@xifenfei>select OWNER,OBJECT_NAME,OBJECT_TYPE, status, 2 to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created 3 ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time 4 from dba_objects 5 where object_name like 'ET$%' 6 / OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME --------- ---------------- ------------ ------- ------------------------- ---------------- DWDBA ET$012D00070001 TABLE VALID 10-mar-2016 16:32:25 10-mar-2016 16:32:25 DWDBA ET$01D10D4F0001 TABLE VALID 10-mar-2016 17:29:29 10-mar-2016 17:29:29 SYS@xifenfei> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE 2 from dba_external_tables 3 order by 1,2 4 / OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_ ----------- ------------------------------ ------------------------------ ------- DWDBA ET$012D00070001 EXP_FILE_DIR CLOB DWDBA ET$01D10D4F0001 EXP_FILE_DIR CLOB
到这一步,我们已经完全清楚,ET$012D00070001和ET$01D10D4F0001是两个外部表,由于他们的存在使得收集统计信息异常。
分析ET$012D00070001表
SYS@xifenfei>desc DWDBA.ET$012D00070001 Name Null? Type ----------------------------------------------------- -------- ------------------------------------ STORE_NO NUMBER(3) ITEM_NO NUMBER(6) WORK_DATE DATE DIVISION_NO NUMBER(2) SECTION_NO NUMBER(3) SUP_NO NUMBER(6) GRP_NO NUMBER(3) SUBGRP_NO NUMBER(3) USR VARCHAR2(30) TYPE NUMBER(1) ACTIVE_SELL_PRICE NUMBER(8,2) SELL_PRICE NUMBER(8,2) SELL_VAT NUMBER(1) BUY_PRICE NUMBER(10,4) BUY_VAT NUMBER(1) PROMOTION_NO NUMBER(10) PROM_CLASS VARCHAR2(1) PROM_LEVEL NUMBER(1) STOCK NUMBER(10,3) STOCK_ADJ NUMBER(10,3) RECPT NUMBER(10,3) SALES NUMBER(10,3) STOCK_ADJ_AMNT NUMBER(10,2) RECPT_AMNT NUMBER(10,2) SALES_AMNT NUMBER(10,2) PROF_AMNT NUMBER(10,2) COST_CHANGE NUMBER(10,2) DISC NUMBER(10,3) RTN_QTY NUMBER(9,3) DISC_AMNT NUMBER(10,2) RTN_AMNT NUMBER(10,2) LOSS_AMNT NUMBER(10,2) CREATED_DATE DATE COST NUMBER(10,4) NBR_PK NUMBER(5) NBR_VISIT NUMBER(5) NBR_PK_LINE NUMBER(5) N_N_PROF_AMNT NUMBER(9,2) CON_FORE NUMBER(10,2) CON_FORE_OTH NUMBER(10,2) SALES_B NUMBER(10,3) SALES_AMNT_B NUMBER(10,2) SYS@xifenfei>select count(*) from DWDBA.ET$012D00070001; select count(*) from DWDBA.ET$012D00070001 * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout KUP-11024: This external table can only be accessed from within a Data Pump job.
通过对ET$012D00070001表查询重新了alert日志一样的错误,可以明确定位问题就是由于该外部表异常导致.通过查询mos,确定Bug 10327346 DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)可能导致DBMS_WORKLOAD_CAPTURE无法正常清理掉Data pump的外部表导致出现Datapump出现孤立的外部表对象,从而出现该问题.解决方案就是直接drop 相关外部表.也就是这里的(ET$012D00070001和ET$01D10D4F0001)
Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets—201610
Patchsets |
|
l12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
11.2.0.4 (11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
11.2.0.3 (11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
11.2.0.2 (11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
11.1.0.7 (11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
10.2.0.5 (10.2.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
d10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER) |
|
e10.2.0.3 (10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
10.2.0.2 (10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
10.1.0.5 (10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER) |
|
10.1.0.4 (10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
10.1.0.3 (10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.8 (9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.7 (9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.6 (9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.5 (ORACLE 9I DATABASE SERVER RELEASE 2 – PATCH SET 4 VERSION 9.2.0.5.0) |
|
9.2.0.4 (9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.3 (9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.2.0.2 (9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.0.1.5 (9.0.1.5 PATCHSET) |
|
9.0.1.4 (9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER) |
|
9.0.1.3 (9.0.1.3. PATCH SET FOR ORACLE DATA SERVER) |
|
8.1.7.4 (8.1.7.4 PATCH SET FOR ORACLE DATA SERVER) |
|
8.1.7.3 (8.1.7.3 PATCH SET FOR ORACLE DATA SERVER) |
|
8.1.7.2 (8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER) |
PSU, SPU(CPU), Bundle Patches
|
||||
12.1.0.2 |
||||
Description |
PSU |
GI PSU |
Proactive Bundle Patch |
Bundle Patch(Windows 32bit & 64bit) |
OCT2016 |
24006101 (12.1.0.2.161018) |
24412235 (12.1.0.2.161018) |
24448103 (12.1.0.2.161018) |
24591642(12.1.0.2.161018) |
JUL2016 |
23054246 (12.1.0.2.160719) |
23273629 (12.1.0.2.160719) |
23273686 (12.1.0.2.160719) |
23530387(12.1.0.2.160719) |
APR2016 |
22291127 (12.1.0.2.160419) |
22646084 (12.1.0.2.160419) |
22809813(12.1.0.2.160419) |
|
JAN2016 |
21948354 (12.1.0.2.160119) |
22191349 (12.1.0.2.160119) |
22310559(12.1.0.2.160119) |
|
OCT2015 |
21359755 (12.1.0.2.5) |
21523234 (12.1.0.2.5) |
21744410 (12.1.0.2.13) |
21821214(12.1.0.2.10) |
JUL2015 |
20831110 (12.1.0.2.4) |
20996835 (12.1.0.2.4) |
21188742 (12.1.0.2.10) |
21126814(12.1.0.2.7) |
APR2015 |
20299023 (12.1.0.2.3) |
20485724 (12.1.0.2.3) |
20698050 (12.1.0.2.7) |
20684004(12.1.0.2.4) |
JAN2015 |
19769480 (12.1.0.2.2) |
19954978 (12.1.0.2.2) |
20141343 (12.1.0.2.4) |
19720843(12.1.0.2.1) |
OCT2014 |
19303936 (12.1.0.2.1) |
19392646 (12.1.0.2.1) |
19404326 (12.1.0.2.1) |
N/A |
12.1.0.1 |
||||
Description |
PSU |
GI PSU |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
JUL2016 |
23054354 (12.1.0.1.160719) |
23530410 (12.1.0.1.160719) |
||
APR2016 |
22291141 (12.1.0.1.160419) |
22617408 (12.1.0.1.160419) |
||
JAN2016 |
21951844 (12.1.0.1.160119) |
22494866 (12.1.0.2.160119) |
||
OCT2015 |
21352619 (12.1.0.1.9) |
21744907 (12.1.0.1.21) |
||
JUL2015 |
20831107 (12.1.0.1.8) |
21076681 (12.1.0.1.20) |
||
APR2015 |
20299016 (12.1.0.1.7) |
20558101 (12.1.0.1.18) |
||
JAN2015 |
19769486 (12.1.0.1.6) |
20160748 (12.1.0.1.16) |
||
OCT2014 |
19121550 (12.1.0.1.5) |
19542943 (12.1.0.1.14) |
||
JUL2014 |
18522516 (12.1.0.1.4) |
19062327 (12.1.0.1.11) |
||
APR2014 |
18031528 (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) |
OCT2016 |
24006111 (11.2.0.4.161018) |
24433711 (11.2.0.4.161018) |
24436338 (11.2.0.4.161018) |
24591646 (11.2.0.4.161018) |
JUL2016 |
23054359 (11.2.0.4.160719) |
23177648 (11.2.0.4.160719) |
23274134 (11.2.0.4.160719) |
23530402 (11.2.0.4.160719) |
APR2016 |
22502456 (11.2.0.4.160419) |
22502493 (11.2.0.4.160419) |
22646198 (11.2.0.4.160419) |
22839608 (11.2.0.4.160419) |
JAN2016 |
21948347 (11.2.0.4.160119) |
21972320 (11.2.0.4.160119) |
22191577 (11.2.0.4.160119) |
22310544 (11.2.0.4.160119) |
OCT2015 |
21352635 (11.2.0.4.8) |
21523375 (11.2.0.4.8) |
21821802 (11.2.0.4.20) |
|
JUL2015 |
20760982 (11.2.0.4.7) |
20996923 (11.2.0.4.7) |
21469106 (11.2.0.4.18) |
|
APR2015 |
20299013 (11.2.0.4.6) |
20485808 (11.2.0.4.6) |
20544696 (11.2.0.4.15) |
|
JAN2015 |
19769489 (11.2.0.4.5) |
19955028 (11.2.0.4.5) |
20127071 (11.2.0.4.12) |
|
OCT2014 |
19121551 (11.2.0.4.4) |
19380115 (11.2.0.4.4) |
19651773 (11.2.0.4.10) |
|
JUL2014 |
18522509 (11.2.0.4.3) |
18706472 (11.2.0.4.3) |
18842982 (11.2.0.4.7) |
|
APR2014 |
18031668 (11.2.0.4.2) |
18139609 (11.2.0.4.2) |
18296644 (11.2.0.4.4) |
|
JAN2014 |
17478514 (11.2.0.4.1) |
N/A |
17987366 (11.2.0.4.1) |
11.2.0.3 |
|||||
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows64bit) |
Bundle Patch(Windows32bit) |
aJUL2015 |
20760997 (11.2.0.3.15) |
20996944 (11.2.0.3.15) |
|||
APR2015 |
20299017 (11.2.0.3.14) |
20485830 (11.2.0.3.14) |
|||
JAN2015 |
19769496 (11.2.0.3.13) |
19971343 (11.2.0.3.13) |
|||
OCT2014 |
19121548 (11.2.0.3.12) |
19440385 (11.2.0.3.12) |
|||
JUL2014 |
18522512 (11.2.0.3.11) |
18706488 (11.2.0.3.11) |
|||
APR2014 |
18031683 (11.2.0.3.10) |
18139678 (11.2.0.3.10) |
|||
JAN2014 |
17540582 (11.2.0.3.9) |
17735354 (11.2.0.3.9) |
|||
OCT2013 |
16902043 (11.2.0.3.8) |
17272731 (11.2.0.3.8) |
|||
JUL2013 |
16619892 (11.2.0.3.7) |
16742216 (11.2.0.3.7) |
|||
APR2013 |
16056266 (11.2.0.3.6) |
16083653 (11.2.0.3.6) |
|||
JAN2013 |
14727310 (11.2.0.3.5) |
14727347 (11.2.0.3.5) |
|||
OCT2012 |
14275605 (11.2.0.3.4) |
14275572 (11.2.0.3.4) |
|||
JUL2012 |
13923374 (11.2.0.3.3) |
13919095 (11.2.0.3.3) |
|||
APR2012 |
13696216 (11.2.0.3.2) |
13696251 (11.2.0.3.2) |
|||
JAN2012 |
13343438 (11.2.0.3.1) |
13348650 (11.2.0.3.1) |
11.2.0.2 |
|||||
Description |
PSU |
SPU(CPU) |
GI PSU |
Bundle Patch (Windows64bit) |
Bundle Patch(Windows32bit) |
aOCT2013 |
17082367 (11.2.0.2.12) |
17272753 (11.2.0.2.12) |
|||
JUL2013 |
16619893 (11.2.0.2.11) |
16742320 (11.2.0.2.11) |
|||
APR2013 |
16056267 (11.2.0.2.10) |
16166868 (11.2.0.2.10) |
|||
JAN2013 |
14727315 (11.2.0.2.9) |
14841385 (11.2.0.2.9) |
|||
OCT2012 |
14275621 (11.2.0.2.8) |
14390437 (11.2.0.2.8) |
|||
JUL2012 |
13923804 (11.2.0.2.7) |
14192201 (11.2.0.2.7) |
|||
APR2012 |
13696224 (11.2.0.2.6) |
13696242 (11.2.0.2.6) |
|||
JAN2012 |
13343424 (11.2.0.2.5) |
13653086 (11.2.0.2.5) |
|||
OCT2011 |
12827726 (11.2.0.2.4) |
12827731 (11.2.0.2.4) |
|||
JUL2011 |
12419331 (11.2.0.2.3) |
12419353 (11.2.0.2.3) |
|||
APR2011 |
11724916 (11.2.0.2.2) |
12311357 (11.2.0.2.2) |
|||
JAN2011 |
10248523 (11.2.0.2.1) |
N/A |
N/A |
|
||||
11.2.0.1 |
||||
Description |
PSU |
CPU |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
aJUL2011 |
12419378 (11.2.0.1.6) |
|||
APR2011 |
11724930 (11.2.0.1.5) |
|||
JAN2011 |
10248516 (11.2.0.1.4) |
|||
OCT2010 |
9952216 (11.2.0.1.3) |
|||
JUL2010 |
9654983 (11.2.0.1.2) |
|||
APR2010 |
9352237 (11.2.0.1.1) |
N/A |
N/A |
|
||||
11.1.0.7 |
||||
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
JUL2015 |
20761024 (11.1.0.7.24) |
|||
APR2015 |
20299012 (11.1.0.7.23) |
|||
JAN2015 |
19769499 (11.1.0.7.22) |
|||
OCT2014 |
19152553 (11.1.0.7.21) |
|||
JUL2014 |
18522513 (11.1.0.7.20) |
|||
APR2014 |
18031726 (11.1.0.7.19) |
|||
JAN2014 |
17465583 (11.1.0.7.18) |
|||
OCT2013 |
17082366 (11.1.0.7.17) |
|||
JUL2013 |
16619896 (11.1.0.7.16) |
|||
APR2013 |
16056268 (11.1.0.7.15) |
|||
JAN2013 |
14739378 (11.1.0.7.14) |
|||
OCT2012 |
14275623 (11.1.0.7.13) |
|||
JUL2012 |
13923474 (11.1.0.7.12) |
|||
APR2012 |
13621679 (11.1.0.7.11) |
|||
JAN2012 |
13343461 (11.1.0.7.10) |
|||
OCT2011 |
12827740 (11.1.0.7.9) |
|||
JUL2011 |
12419384 (11.1.0.7.8) |
|||
APR2011 |
11724936 (11.1.0.7.7) |
|||
JAN2011 |
10248531 (11.1.0.7.6) |
|||
OCT2010 |
9952228 (11.1.0.7.5) |
|||
JUL2010 |
9654987 (11.1.0.7.4) |
|||
APR2010 |
9352179 (11.1.0.7.3) |
|||
JAN2010 |
9209238 (11.1.0.7.2) |
|||
OCT2009 |
8833297 (11.1.0.7.1) |
|||
JUL2009 |
N/A |
|||
APR2009 |
N/A |
|
|||
11.1.0.6 |
|||
Description |
CPU |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
aJUL2009 |
|||
APR2009 |
|||
JAN2009 |
|||
OCT2008 |
|||
JUL2008 |
|||
APR2008 |
|
|||||
10.2.0.5 |
|||||
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows64bit) |
Bundle Patch (Windows32bit) |
Bundle Patch(WindowsItanium) |
bJUL2015 |
20299014 (10.2.0.5.19) |
N/A |
|||
APR2015 |
N/A |
N/A |
N/A |
N/A |
N/A |
JAN2015 |
19769505 (10.2.0.5.18) |
N/A |
|||
OCT2014 |
19274523 (10.2.0.5.17) |
N/A |
|||
JUL2014 |
18522511 (10.2.0.5.16) |
N/A |
|||
APR2014 |
18031728 (10.2.0.5.15) |
N/A |
|||
JAN2014 |
17465584 (10.2.0.5.14) |
N/A |
|||
OCT2014 |
17082365 (10.2.0.5.13) |
N/A |
N/A |
||
JUL2013 |
16619894 (10.2.0.5.12) |
||||
APR2013 |
16056270 (10.2.0.5.11) |
||||
JAN2013 |
14727319 (10.2.0.5.10) |
||||
OCT2012 |
14275629 (10.2.0.5.9) |
||||
JUL2012 |
13923855 (10.2.0.5.8) |
||||
APR2012 |
13632743 (10.2.0.5.7) |
||||
JAN2012 |
13343471 (10.2.0.5.6) |
N/A |
|||
OCT2011 |
12827745 (10.2.0.5.5) |
c12914913 |
N/A |
||
JUL2011 |
12419392 (10.2.0.5.4) |
N/A |
|||
APR2011 |
11724962 (10.2.0.5.3) |
N/A |
|||
JAN2011 |
10248542 (10.2.0.5.2) |
N/A |
|||
OCT2010 |
9952230 (10.2.0.5.1) |
N/A |
|
|||||
10.2.0.4 |
|||||
Description |
PSU |
SPU(CPU) |
Bundle Patch (Windows32bit) |
Bundle Patch (Windows64bit) |
Bundle Patch(WindowsItanium) |
gJUL2013 |
16619897 (10.2.0.4.17) |
16742253 |
N/A |
N/A |
N/A |
gAPR2013 |
16056269 (10.2.0.4.16) |
16270931 |
N/A |
N/A |
N/A |
gJAN2013 |
14736542 (10.2.0.4.15) |
14841471 |
N/A |
N/A |
N/A |
gOCT2012 |
14275630 (10.2.0.4.14) |
14390410 |
N/A |
N/A |
N/A |
gJUL2012 |
13923851 (10.2.0.4.13) |
14038814 |
N/A |
N/A |
N/A |
aAPR2012 |
12879933 (10.2.0.4.12) |
N/A |
|||
JAN2012 |
12879929 (10.2.0.4.11) |
N/A |
N/A |
||
OCT2011 |
12827778 (10.2.0.4.10) |
||||
JUL2011 |
12419397 (10.2.0.4.9) |
||||
APR2011 |
11724977 (10.2.0.4.8) |
||||
JAN2011 |
10248636 (10.2.0.4.7) |
||||
OCT2010 |
9952234 (10.2.0.4.6) |
||||
JUL2010 |
9654991 (10.2.0.4.5) |
||||
APR2010 |
9352164 (10.2.0.4.4) |
||||
JAN2010 |
9119284 (10.2.0.4.3) |
||||
OCT2009 |
8833280 (10.2.0.4.2) |
||||
JUL2009 |
8576156 (10.2.0.4.1) |
||||
APR2009 |
N/A |
||||
JAN2009 |
N/A |
N/A |
|||
OCT2008 |
N/A |
N/A |
|||
JUL2008 |
N/A |
N/A |
|
||||
10.2.0.3 |
||||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
Bundle Patch (Windows64bit) |
aJAN2009 |
||||
OCT2008 |
||||
JUL2008 |
||||
APR2008 |
||||
JAN2008 |
||||
OCT2007 |
||||
JUL2007 |
||||
APR2007 |
||||
JAN2007 |
|
||||
10.2.0.2 |
||||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (Windows64bit) |
Bundle Patch (WindowsItanium) |
iJAN2009 |
N/A |
N/A |
N/A |
|
hOCT2008 |
N/A |
N/A |
N/A |
|
hJUL2008 |
N/A |
N/A |
N/A |
|
hAPR2008 |
N/A |
N/A |
N/A |
|
aJAN2008 |
N/A |
N/A |
N/A |
|
fOCT2007 |
||||
JUL2007 |
||||
APR2007 |
||||
JAN2007 |
||||
OCT2006 |
||||
JUL2006 |
||||
APR2006 |
|
||||
10.2.0.1 |
||||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (Windows64bit) |
Bundle Patch (WindowsItanium) |
APR2007 |
N/A |
N/A |
N/A |
|
JAN2007 |
||||
OCT2006 |
||||
JUL2006 |
||||
APR2006 |
||||
JAN2006 |
|
|||
10.1.0.5 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
JAN2012 |
|||
OCT2011 |
|||
JUL2011 |
|||
APR2011 |
|||
JAN2011 |
N/A |
N/A |
N/A |
OCT2010 |
|||
JUL2010 |
|||
APR2010 |
|||
JAN2010 |
|||
OCT2009 |
|||
JUL2009 |
|||
APR2009 |
|||
JAN2009 |
|||
OCT2008 |
|||
JUL2008 |
|||
APR2008 |
|||
JAN2008 |
|||
OCT2007 |
|||
JUL2007 |
|||
APR2007 |
|||
JAN2007 |
|||
OCT2006 |
|||
JUL2006 |
|||
APR2006 |
|||
JAN2006 |
|
|||
10.1.0.4 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
APR2007 |
|||
JAN2007 |
|||
OCT2006 |
|||
JUL2006 |
|||
APR2006 |
|||
JAN2006 |
|||
OCT2005 |
|||
JUL2005 |
|||
APR2005 |
|
|||
10.1.0.3 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
JAN2007 |
N/A |
N/A |
|
OCT2006 |
N/A |
N/A |
|
JUL2006 |
N/A |
N/A |
|
APR2006 |
N/A |
N/A |
|
JAN2006 |
|||
OCT2005 |
|||
JUL2005 |
|||
APR2005 |
|||
JAN2005 |
|
|||
10.1.0.2 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
APR2005 |
|||
JUL2005 |
|||
JAN2005 |
|
|||
9.2.0.8 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
JUL2010 |
|||
APR2010 |
N/A |
||
JAN2010 |
N/A |
||
OCT2009 |
|||
JUL2009 |
|||
APR2009 |
|||
JAN2009 |
|||
OCT2008 |
|||
JUL2008 |
|||
APR2008 |
|||
JAN2008 |
|||
OCT2007 |
|||
JUL2007 |
|||
APR2007 |
|||
JAN2007 |
N/A |
N/A |
N/A |
OCT2006 |
|
|||
9.2.0.7 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
JUL2007 |
|||
APR2007 |
|||
JAN2007 |
|||
OCT2006 |
|||
JUL2006 |
|||
APR2006 |
|||
JAN2006 |
|||
OCT2005 |
|||
JUL2005 |
N/A |
N/A |
|
|||
9.2.0.6 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
OCT2006 |
|||
JUL2006 |
|||
APR2006 |
|||
JAN2006 |
|||
OCT2005 |
|||
JUL2005 |
|||
APR2005 |
|
|||
9.2.0.5 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
OCT2006 |
N/A |
N/A |
|
JUL2006 |
N/A |
N/A |
|
APR2006 |
N/A |
N/A |
|
OCT2005 |
N/A |
N/A |
|
JUL2005 |
|||
APR2005 |
|||
JAN2005 |
|
|||
9.2.0.4 |
|||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
Bundle Patch (WindowsItanium) |
JAN2005 |
|
||
8.1.7.4 |
||
Description |
CPU (Unix/Linux) |
Bundle Patch (Windows32bit) |
JAN2007 |
||
OCT2006 |
||
JUL2006 |
||
APR2006 |
||
JAN2006 |
||
OCT2005 |
||
JUL2005 |
||
APR2005 |
||
JAN2005 |
OJVM PSU Patches
|
|||||
12.1.0.2 |
|||||
Description |
OJVM PSU (Linux/Unix) |
OJVM BP (Windows) |
Combo OJVM + DB PSU |
Combo OJVM + GI PSU |
Generic JDBC |
OCT2016 |
24315824 (12.1.0.2.161018) |
24591630 (12.1.0.2.161018) |
24433133 (12.1.0.2.161018) |
24433148 (12.1.0.2.161018) |
Included in OJVM PSU |
JUL2016 |
23177536 (12.1.0.2.160719) |
23515290 (12.1.0.2.160719) |
23615289 (12.1.0.2.160719) |
23615308 (12.1.0.2.160719) |
23727148 (Included in OJVM PSU) |
APR2016 |
22674709 (12.1.0.2.160419) |
22839633 (12.1.0.2.160419) |
22738582 (12.1.0.2.160419) |
22738641 (12.1.0.2.160419) |
|
JAN2016 |
22139226 (12.1.0.2.160119) |
22311086 (12.1.0.2.160119) |
22191659 (12.1.0.2.160119) |
22191676 (12.1.0.2.160119) |
|
OCT2015 |
21555660 (12.1.0.2.5) |
21788394 (12.1.0.2.4) |
|||
JUL2015 |
21068507 (12.1.0.2.4) |
21153530 (12.1.0.2.3) |
|||
APR2015 |
20415564 (12.1.0.2.3) |
20391199 (12.1.0.2.2) |
|||
JAN2015 |
19877336 (12.1.0.2.2) |
20225938 (12.1.0.2.1) |
|||
OCT2014 (12.1.0.2.1) |
|
|
|||||
12.1.0.1 |
|||||
Description |
OJVM PSU (Linux/Unix) |
OJVM BP (Windows) |
Combo OJVM + DB PSU |
Combo OJVM + GI PSU |
Generic JDBC |
JUL2016 (12.1.0.1.160719) |
23727043 (Included in OJVM PSU) |
||||
APR2016 (12.1.0.1.160419) |
Included in OJVM PSU |
||||
JAN2016 (12.1.0.1.160119) |
|||||
OCT2015 (12.1.0.1.5) |
|||||
JUL2015 (12.1.0.1.4) |
|||||
APR2015 (12.1.0.1.3) |
|||||
JAN2015 (12.1.0.1.2) |
|||||
OCT2014 (12.1.0.1.1) |
|
||||||
11.2.0.4 |
||||||
Description |
OJVM PSU (Linux/Unix) |
OJVM BP (Windows) |
Combo OJVM + DB PSU |
Combo OJVM + DB SPU |
Combo OJVM + GI PSU |
Generic JDBC |
OCT2016 (11.2.0.4.161018) |
Included in OJVM PSU |
|||||
JUL2016 (11.2.0.4.160719) |
23727132 (Included in OJVM PSU) |
|||||
APR2016 (11.2.0.4.160419) |
Included in OJVM PSU |
|||||
JAN2016 (11.2.0.4.160119) |
||||||
OCT2015 (11.2.0.4.5) |
||||||
JUL2015 (11.2.0.4.4) |
||||||
APR2015 (11.2.0.4.3) |
||||||
JAN2015 (11.2.0.4.2) |
||||||
OCT2014 (11.2.0.4.1) |
|
||||||
11.2.0.3 |
||||||
Description |
OJVM PSU (Linux/Unix) |
OJVM BP (Windows) |
Combo OJVM + DB PSU |
Combo OJVM + DB SPU |
Combo OJVM + GI PSU |
Generic JDBC |
JUL2015 (11.2.0.3.4) |
Included in OJVM PSU |
|||||
APR2015 (11.2.0.3.3) |
||||||
JAN2015 (11.2.0.3.2) |
||||||
OCT2014 (11.2.0.3.1) |
|
||||||
11.1.0.7 |
||||||
Description |
OJVM PSU (Linux/Unix) |
OJVM BP (Windows) |
Combo OJVM + DB PSU |
Combo OJVM + DB SPU |
Combo OJVM + GI PSU |
Generic JDBC |
JUL2015 (11.1.0.7.4) |
21068565 |
N/A |
Included in OJVM PSU |
|||
APR2015 (11.1.0.7.3) |
N/A |
|||||
JAN2015 (11.1.0.7.2) |
N/A |
|||||
OCT2014 (11.1.0.7.1) |
N/A |
参考:Quick Reference to Patch Numbers for Database PSU, SPU(CPU), Bundle Patches and Patchsets (文档 ID 1454618.1)
alter database create datafile 导致数据文件丢失恢复
alter database create datafile导致原始数据文件丢失
有客户一个小系统找我们恢复,通过Oracle Database Recovery Check 检测之后我们红框部分发现一奇怪现象
1.文件头fuzzy为NO,不符合数据库异常crash常识,也和其他文件该状态不匹配
2.文件的创建时间,scn均和checkpoint时间,scn一致(也就是说该文件是创建之后就checkpoint,然后就没有其他操作)
3.文件开始应用的归档为5,110和其他数据文件要求的3115相差甚远
结合这些情况,怀疑该文件被重新创建,查找alert日志果如发现如下信息
两个文件通过create datafile创建之后,然后offline操作.通过alert日志核查file 6和8的创建时间和seq信息 1 Fri Jan 16 15:03:36 2015 Thread 1 advanced to log sequence 5 (LGWR switch) Current log# 2 seq# 5 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\MYCHOICE\REDO02.LOG Fri Jan 16 15:13:19 2015 CREATE BIGFILE TABLESPACE "FBAUDIT" DATAFILE 'E:\ZDSoft\ZDFood\databak\FBAUDIT' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO Completed: CREATE BIGFILE TABLESPACE "FBAUDIT" DATAFILE 'E:\ZDSoft\ZDFood\databak\FBAUDIT' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO Sat Feb 07 15:03:46 2015 Thread 1 advanced to log sequence 110 (LGWR switch) Current log# 2 seq# 110 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\MYCHOICE\REDO02.LOG Sat Feb 07 15:20:41 2015 CREATE BIGFILE TABLESPACE "CARD" DATAFILE 'E:\ZDSoft\ZDCARD\databak\CARD1' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO Completed: CREATE BIGFILE TABLESPACE "CARD" DATAFILE 'E:\ZDSoft\ZDCARD\databak\CARD1' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
通过结合alert日志判断,我们可以确定,当前我们Oracle Database Recovery Check检查出来的情况,是由于执行了create datafile命令导致故障前的文件丢失,创建了一个新的数据文件,而由于该库为非归档模式,导致该文件数据无法恢复(备注:不光是非归档模式不行,就算是归档模式,也需要从文件创建到现在的所有归档才行).在大部分生产系统,我相信不可能有这么的归档,因为在执行alter database create datafile命令之时一定要慎重,评估确定是否丢失归档,否则可能导致不可理的损坏).
客户意识到了悲剧的发生,但是希望我们帮忙恢复一张核心数据,用户的余额信息.
对于alter database create datafile丢失文件恢复
通过工具扫描原始文件相关的记录(由于写入大量数据,无法完整恢复,只能通过工具扫描,恢复部分数据)[asm disk header 彻底损坏恢复]
因为原库虽然丢失了这两个文件,但是已经open成功,通过相关的data obj结合这个里面扫描到的文件,抽取出来需要的对象的block,然后对block里面的数据进行读取恢复出来相关数据.在这里我们还有一个难点就是由于这两个文件都是bigfile,给恢复过程增加了难度
至此我们已经实现了对于alter database create datafile导致文件丢失的核心数据的恢复.尽可能的减小的客户的损坏.这种恢复是取决运气,数据在磁盘上的block没有被覆盖.如果覆盖了基本无望.
如果需要数据库恢复,请联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持:
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
再次提醒
1.在数据库出现故障之时,尽可能保护现场,做操作之前要之后后果别百度了就不分青红皂白的直接操作,导致不可逆的破坏,数据可能永久性丢失[Oracle异常恢复前备份保护现场建议—FileSystem环境|Oracle异常恢复前备份保护现场建议—ASM环境
2.使用alter database create datafile命令之前需要慎重,评估是否所有的归档都存在