2018年 – 第6页 – Database SOS

有一朋友数据库经常crash,让我帮忙分析和解决该问题
数据库版本

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

alert日志报错信息

Mon Apr 23 02:14:18 2018
Process 0x0x10f33262f8 appears to be hung while dumping
Current time = 464149508, process death time = 464089392 interval = 60000
Called from location UNKNOWN:UNKNOWN
Attempting to kill process 0x0x10f33262f8 with OS pid = 30813
OSD kill succeeded for process 0x10f33262f8
Instance Critical Process (pid: 9, ospid: 30813, DBRM) died unexpectedly
Mon Apr 23 02:14:21 2018
System state dump requested by (instance=1, osid=30789 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_diag_30809_20180423021421.trc
Mon Apr 23 02:14:22 2018
PMON (ospid: 30789): terminating the instance due to error 56710
Mon Apr 23 02:14:22 2018
opiodr aborting process unknown ospid (27086) as a result of ORA-1092
Mon Apr 23 02:14:28 2018
Instance terminated by PMON, pid = 30789

而在类似报错之前，一般有swap不足的报错

Mon Apr 23 02:03:54 2018
WARNING: Heavy swapping observed on system in last 5 mins.
pct of memory swapped in [2.01%] pct of memory swapped out [0.51%].
Please make sure there is no memory pressure and the SGA and PGA
are configured correctly. Look at DBRM trace file for more details.
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbrm_30813.trc  (incident=854536):
ORA-00700: soft internal error, arguments: [kskvmstatact: excessive swapping observed], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_854536/orcl_dbrm_30813_i854536.trc
Mon Apr 23 02:04:02 2018
Dumping diagnostic data in directory=[cdmp_20180423020402], requested by (instance=1, osid=30813 (DBRM))

从这里报错看，由于系统内存不足，导致大量使用swap，从而引起oracle进程被kill

分析系统内存使用情况

[www.xifenfei.com@Oracle ~]$ more /proc/meminfo
MemTotal:       66109924 kB
MemFree:          359848 kB
Buffers:            9308 kB
Cached:          1848504 kB
SwapCached:       172800 kB
Active:          1060368 kB
Inactive:        1156100 kB
Active(anon):     999208 kB
Inactive(anon):  1104860 kB
Active(file):      61160 kB
Inactive(file):    51240 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:      33554428 kB
SwapFree:       30516280 kB
Dirty:                68 kB
Writeback:             0 kB
AnonPages:        190936 kB
Mapped:          1152196 kB
Shmem:           1745380 kB
Slab:              70900 kB
SReclaimable:      25640 kB
SUnreclaim:        45260 kB
KernelStack:        5728 kB
PageTables:        92488 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    35152108 kB
Committed_AS:   70923356 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      267468 kB
VmallocChunk:   34359442996 kB
HardwareCorrupted:     0 kB
AnonHugePages:     18432 kB
HugePages_Total:   30720
HugePages_Free:    30720
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        8192 kB
DirectMap2M:     2088960 kB
DirectMap1G:    65011712 kB
[www.xifenfei.com@Oracle ~]$ free -m
             total       used       free     shared    buffers     cached
Mem:         64560      64200        359       1682          9       1801
-/+ buffers/cache:      62389       2170
Swap:        32767       2977      29790

比较明显系统总共内存64G,配置了60G大页,但是数据库没有使用该大页

数据库使用内存情况

SQL> show sga;
Total System Global Area 7.1672E+10 bytes
Fixed Size                  3719544 bytes
Variable Size            2684358280 bytes
Database Buffers         6.8719E+10 bytes
Redo Buffers              264712192 bytes

比较明显按照上述配置,一共就只有4G的空闲内存,但是oracle sga占用7G,出现大量换页是必然.错误也明显想让数据库使用大页,但是由于配置不当导致数据库无法使用大页而使用系统除大页之外的内存,从而引起系统异常.
这里也说明12c的提示有明显的改善,通过alert的错误提示基本上就可以确定是swap不足导致.

ORA-600 17182导致oracle异常

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-600 17182导致oracle异常

正常运行的数据库突然爆ORA-00600 17182,然后直接crash,以前遇到过类似的case:分享一例由于主库逻辑坏块导致dataguard容灾失效,这又是一例数据库正常crash之后无法启动成功的case

Tue May 22 08:32:12 2018
Archived Log entry 84344 added for thread 1 sequence 90196 ID 0x103430df dest 1:
Tue May 22 09:05:42 2018
Thread 1 cannot allocate new log, sequence 90198
Private strand flush not complete
  Current log# 4 seq# 90197 mem# 0: +DATA/xifenfei/onlinelog/group_4.279.887464919
Thread 1 advanced to log sequence 90198 (LGWR switch)
  Current log# 2 seq# 90198 mem# 0: +DATA/xifenfei/onlinelog/group_2.263.887465041
Tue May 22 09:05:46 2018
Archived Log entry 84345 added for thread 1 sequence 90197 ID 0x103430df dest 1:
Tue May 22 09:07:42 2018
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_16297.trc  (incident=592822):
ORA-00600: 内部错误代码, 参数: [17182], [0x7FE274EADBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_592822/xifenfei_ora_16297_i592822.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:07:45 2018
Dumping diagnostic data in directory=[cdmp_20180522090745], requested by (instance=1, osid=16297), summary=[incident=592822].
Tue May 22 09:07:46 2018
Sweep [inc][592822]: completed
Sweep [inc2][592822]: completed
Tue May 22 09:08:29 2018
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_ora_16297.trc  (incident=592824):
ORA-07445: 出现异常错误: 核心转储 [kghrcdepth()+168] [SIGSEGV] [ADDR:0x7FE2766ADD04] [PC:0x2C2B886] [Invalid permissions for mapped object] []
ORA-00600: 内部错误代码, 参数: [kghrcdepth:ds], [0x7FE274EADBE8], [], [], [], [], [], [], [], [], [], []
ORA-00600: 内部错误代码, 参数: [17182], [0x7FE274EADBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_592824/xifenfei_ora_16297_i592824.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:08:31 2018
Dumping diagnostic data in directory=[cdmp_20180522090831], requested by (instance=1, osid=16297), summary=[incident=592823].
Tue May 22 09:08:44 2018
Block recovery from logseq 90198, block 37639 to scn 161030804187
Recovery of Online Redo Log: Thread 1 Group 2 Seq 90198 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_2.263.887465041
Block recovery completed at rba 90198.97219.16, scn 37.2117014236
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pmon_7690.trc  (incident=592118):
ORA-00600: internal error code, arguments: [17182], [0x7F96BDA2AA70], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_592118/xifenfei_pmon_7690_i592118.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97E6B5C, kghpmfal()+216] [flags: 0x0, count: 1]
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_pmon_7690.trc  (incident=592119):
ORA-07445: exception encountered: core dump [kghpmfal()+216] [SIGSEGV] [ADDR:0x0] [PC:0x97E6B5C] [SI_KERNEL(general_protection)] []
ORA-00600: internal error code, arguments: [17182], [0x7F96BDA2AA70], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_592119/xifenfei_pmon_7690_i592119.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:08:45 2018
Dumping diagnostic data in directory=[cdmp_20180522090845], requested by (instance=1, osid=7690 (PMON)), summary=[incident=592118].
Tue May 22 09:08:47 2018
Sweep [inc][592824]: completed
Sweep [inc][592823]: completed
Sweep [inc][592119]: completed
Sweep [inc][592118]: completed
Sweep [inc2][592824]: completed
Sweep [inc2][592119]: completed
Sweep [inc2][592118]: completed
Tue May 22 09:08:47 2018
ARC2 (ospid: 7834): terminating the instance due to error 472
Instance terminated by ARC2, pid = 7834

无法正常open

Completed: ALTER DATABASE   MOUNT
Tue May 22 09:26:44 2018
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 15 processes
Started redo scan
Completed redo scan
 read 12232 KB redo, 4787 data blocks need recovery
Started redo application at
 Thread 1: logseq 90199, block 233846
Recovery of Online Redo Log: Thread 1 Group 3 Seq 90199 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_3.262.887465049
Completed redo application of 10.34MB
Completed crash recovery at
 Thread 1: logseq 90199, block 258311, scn 161030851622
 4787 data blocks read, 4787 data blocks written, 12232 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
Tue May 22 09:26:45 2018
ARC0 started with pid=48, OS id=18632
Tue May 22 09:26:46 2018
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 advanced to log sequence 90200 (thread open)
Tue May 22 09:26:46 2018
ARC1 started with pid=49, OS id=18636
Tue May 22 09:26:46 2018
ARC2 started with pid=50, OS id=18640
Tue May 22 09:26:46 2018
ARC3 started with pid=51, OS id=18644
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 90200
  Current log# 5 seq# 90200 mem# 0: +DATA/xifenfei/onlinelog/group_5.280.887465135
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue May 22 09:26:46 2018
SMON: enabling cache recovery
[18512] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2704839736 end:2704839986 diff:250 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
Archived Log entry 84347 added for thread 1 sequence 90199 ID 0x103430df dest 1:
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Tue May 22 09:26:47 2018
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p019_18664.trc  (incident=624628):
ORA-00600: internal error code, arguments: [17182], [0x7F7A4A50DBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_624628/xifenfei_p019_18664_i624628.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Starting background process QMNC
Tue May 22 09:26:48 2018
QMNC started with pid=71, OS id=18737
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97ECF6C, kghalo()+570] [flags: 0x0, count: 1]
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p019_18664.trc  (incident=624629):
ORA-00600: internal error code, arguments: [17182], [0x7F7A4A50DBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_624629/xifenfei_p019_18664_i624629.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:26:49 2018
Tue May 22 09:26:50 2018
Starting background process EMNC
Tue May 22 09:26:50 2018
EMNC started with pid=76, OS id=18814
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97ECF6C, kghalo()+570] [flags: 0x0, count: 2]
Completed: ALTER DATABASE OPEN
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_p019_18664.trc  (incident=624630):
ORA-07445: exception encountered: core dump [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECF6C] [SI_KERNEL(general_protection)] []
ORA-07445: exception encountered: core dump [kghalo()+570] [SIGSEGV] [ADDR:0x0] [PC:0x97ECF6C] [SI_KERNEL(general_protection)] []
ORA-00600: internal error code, arguments: [17182], [0x7F7A4A50DBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_624630/xifenfei_p019_18664_i624630.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:27:01 2018
Block recovery from logseq 90200, block 59 to scn 161030851961
Recovery of Online Redo Log: Thread 1 Group 5 Seq 90200 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_5.280.887465135
Block recovery stopped at EOT rba 90200.935.16
Block recovery completed at rba 90200.935.16, scn 37.2117062009
Starting background process CJQ0
Tue May 22 09:27:01 2018
SMON: slave died unexpectedly, downgrading to serial recovery
Tue May 22 09:27:01 2018
CJQ0 started with pid=56, OS id=18922
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x9823AA3, kghalo()+567] [flags: 0x0, count: 1]
Errors in file /u01/app/diag/rdbms/posdg/posdg/trace/posdg_p019_11656.trc  (incident=1136658):
ORA-07445: exception encountered: core dump [kghalo()+567] [SIGSEGV] [ADDR:0x0] [PC:0x9823AA3] [SI_KERNEL(general_protection)] []
ORA-00600: internal error code, arguments: [17182], [0x7F813F61DBF8], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/posdg/posdg/incident/incdir_1136658/posdg_p019_11656_i1136658.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/diag/rdbms/posdg/posdg/trace/posdg_ora_10925.trc:
ORA-00600: internal error code, arguments: [2252], [49410], [2147581953], [3726], [1009467392], [], [], [], [], [], [], []
Errors in file /u01/app/diag/rdbms/posdg/posdg/trace/posdg_ora_10925.trc:
ORA-00600: internal error code, arguments: [2252], [49410], [2147581953], [3726], [1009467392], [], [], [], [], [], [], []
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_18434.trc  (incident=624292):
ORA-00600: internal error code, arguments: [17182], [0x7F7488BDD7A0], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_624292/xifenfei_smon_18434_i624292.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x97E64D7, kghalf()+537] [flags: 0x0, count: 1]
Errors in file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_smon_18434.trc  (incident=624293):
ORA-07445: exception encountered: core dump [kghalf()+537] [SIGSEGV] [ADDR:0x0] [PC:0x97E64D7] [SI_KERNEL(general_protection)] []
ORA-00600: internal error code, arguments: [17182], [0x7F7488BDD7A0], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/diag/rdbms/xifenfei/xifenfei/incident/incdir_624293/xifenfei_smon_18434_i624293.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Tue May 22 09:27:03 2018
Dumping diagnostic data in directory=[cdmp_20180522092703], requested by (instance=1, osid=18434 (SMON)), summary=[incident=624292].
PMON (ospid: 18383): terminating the instance due to error 474
Tue May 22 09:27:05 2018
opiodr aborting process unknown ospid (18839) as a result of ORA-1092
System state dump requested by (instance=1, osid=18383 (PMON)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_diag_18402_20180522092705.trc
Tue May 22 09:27:05 2018
ORA-1092 : opitsk aborting process
Instance terminated by PMON, pid = 18383

通过对于启动过程的观察,比较明显,由于数据库无法正常回滚,导致smon进程异常,从而使得数据库无法启动成功.恢复方法比较简单,就是对异常事务进行提交或者跳过即可

truncate IDL_UB1$恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：truncate IDL_UB1$恢复

世界之大无奇不有,已经记不清这是第几个客户咨询IDL_UB1$ 被truncate之后导致数据库无法启动的case了.idl_ub1$表是用来存储PL/SQL的代码单元的，包括DIANA等，IDL在这里代表Interface Definition Language. 在数据库的启动过程中通过10046跟踪可以知道,有类似:select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#的查询语句,由于该表被truncate之后,导致数据库启动无法绕过该sql,而hang住无法完全open成功.下午闲着没事通过模拟,可以对该故障实现正常open,并且导出数据
模拟业务数据

create user xff identified by oracle;
grant dba to xff;
conn xff/oracle
create table t_xifenfei as select * from dba_objects;
create index i_xifenfei on t_xifenfei(object_id);
create view v_xifenfei as select * from t_xifenfei;
create or replace procedure proc1(
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as
v_name varchar2(20);
begin
 v_name :='xifenfei';
 para3 := v_name;
dbms_output.put_line('para3:'||para3);
end;
/
alter system checkpoint;

创建xff账户,并且创建有代表性的表,索引,存储过程,视图等.

模拟truncate IDL_UB1$表

SQL> conn / as sysdba
Connected.
SQL> truncate table IDL_UB1$;
truncate table IDL_UB1$
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 31325
Session ID: 177 Serial number: 7

重启数据库

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 7499329536 bytes
Fixed Size                  2267832 bytes
Variable Size            1409287496 bytes
Database Buffers         6073352192 bytes
Redo Buffers               14422016 bytes
Database mounted.

数据库在mount之后,一直处于hang住状态,查看alert日志

Sun May 20 17:02:34 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98D94A7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /home/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_31325.trc  (incident=21781):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98D94A7] [Address not mapped to object] []
Incident details in: /home/u01/app/oracle/diag/rdbms/test/test/incident/incdir_21781/test_ora_31325_i21781.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun May 20 17:02:37 2018
Sweep [inc][21781]: completed
Sweep [inc2][21781]: completed
Sun May 20 17:02:37 2018
Dumping diagnostic data in directory=[cdmp_20180520170237], requested by (instance=1, osid=31325), summary=[incident=21781].
Sun May 20 17:02:55 2018
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x18] [PC:0x98D94A7, hshuid()+273] [flags: 0x0, count: 1]
Errors in file /home/u01/app/oracle/diag/rdbms/test/test/trace/test_m000_31373.trc  (incident=21821):
ORA-07445: exception encountered: core dump [hshuid()+273] [SIGSEGV] [ADDR:0x18] [PC:0x98D94A7] [Address not mapped to object] []
Incident details in: /home/u01/app/oracle/diag/rdbms/test/test/incident/incdir_21821/test_m000_31373_i21821.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Sun May 20 17:02:56 2018
Dumping diagnostic data in directory=[cdmp_20180520170256], requested by (instance=1, osid=31373 (M000)), summary=[incident=21821].

这类问题比较明显,正常方法无法打开,通过工具分析system文件,发现虽然truncate IDL_UB1$操作报错了,但是IDL_UB1$和对应的index I_IDL_UB11 obj#,dataobj#均已经改变,而且相关对象的segment header也变化为新dataobj#(truncate之后的),也就是说truncate在数据库中的主要更改操作已经完成.现在在缺少记录的情况下,数据库执行如下sql无法获取记录,从而无法open

PARSING IN CURSOR #140342551421712 len=132 dep=2 uid=0 oct=3 lid=0 tim=1526843464635335 hv=4260389146 ad='21af73218' sqlid='cvn54b7yz0s8u'
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
END OF STMT
PARSE #140342551421712:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635335
BINDS #140342551421712:
 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=7fa40bec7d80  bln=22  avl=03  flg=05
  value=1310
 Bind#1
  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=7fa40bec7d50  bln=24  avl=01  flg=05
  value=0
 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=7fa40bec7d20  bln=24  avl=06  flg=05
  value=184549376
EXEC #140342551421712:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635449
FETCH #140342551421712:c=0,e=4,p=0,cr=1,cu=0,mis=0,r=0,dep=2,og=4,plh=3246118364,tim=1526843464635462
STAT #140342551421712 id=1 cnt=0 pid=0 pos=1 obj=225 op='TABLE ACCESS BY INDEX ROWID IDL_UB1$ (cr=1 pr=0 pw=0 time=4 us cost=3 size=44 card=2)'
STAT #140342551421712 id=2 cnt=0 pid=1 pos=1 obj=236 op='INDEX RANGE SCAN I_IDL_UB11 (cr=1 pr=0 pw=0 time=4 us cost=2 size=0 card=2)'
CLOSE #140342551421712:c=0,e=2,dep=2,type=0,tim=1526843464635496

通过对数据库采用技术欺骗手段,让数据库启动相关sql能够获取到记录(和正常查询的相同),从而实现数据库正常open

SQL> startup mount
ORACLE instance started.
Total System Global Area 7499329536 bytes
Fixed Size                  2267832 bytes
Variable Size            1409287496 bytes
Database Buffers         6073352192 bytes
Redo Buffers               14422016 bytes
Database mounted.
SQL> alter database open;
Database altered.

open成功之后,后台报大量的ORA-08103: object no longer exists,通过分析是由于truncate IDL_UB1$没有完全成功,导致出现该错误.解决方法就是对Oracle数据字典进行人工更新,把没有完成的truncate操作在数据库中给予完成.
导出数据
exp导出数据成功

但是expdp无法执行

[oracle@bogon oradata]$ expdp '"/ as sysdba"' schemas=xff file=1.dmp
Export: Release 11.2.0.4.0 - Production on Sun May 20 17:10:53 2018
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
UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1

暂时未去研究对这个表进行重建,使用exp导出然后再imp导入是比较理想的办法

恶意删除bootstrap$导致数据库无法正常启动

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：恶意删除bootstrap$导致数据库无法正常启动

有客户10.2.0.5的数据库关闭之后,无法正常启动报ORA-00704 ORA-00702错误.

Fri May 18 22:42:26  2018
ALTER DATABASE OPEN
Fri May 18 22:42:27  2018
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Fri May 18 22:42:27  2018
Started redo scan
Fri May 18 22:42:27  2018
Completed redo scan
 1 redo blocks read, 0 data blocks need recovery
Fri May 18 22:42:27  2018
Started redo application at
 Thread 1: logseq 2, block 2, scn 8448162573
Fri May 18 22:42:27  2018
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
  Mem# 0: D:\DATABASE\xifenfei\REDO02.LOG
Fri May 18 22:42:27  2018
Completed redo application
Fri May 18 22:42:27  2018
Completed crash recovery at
 Thread 1: logseq 2, block 3, scn 8448182575
 0 data blocks read, 0 data blocks written, 1 redo blocks read
Fri May 18 22:42:28  2018
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=23, OS id=3188
ARC1 started with pid=24, OS id=3168
ARC2 started with pid=25, OS id=996
ARC3 started with pid=26, OS id=432
ARC4 started with pid=27, OS id=3728
Fri May 18 22:42:28  2018
ARC0: Archival started
ARC1: Archival started
ARC5 started with pid=28, OS id=2876
Fri May 18 22:42:28  2018
ARC2: Archival started
ARC3: Archival started
ARC4: Archival started
ARC5: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Fri May 18 22:42:28  2018
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: D:\DATABASE\xifenfei\REDO03.LOG
Successful open of redo thread 1
Fri May 18 22:42:28  2018
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Fri May 18 22:42:28  2018
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Fri May 18 22:42:28  2018
ARC2: Becoming the heartbeat ARCH
Fri May 18 22:42:28  2018
SMON: enabling cache recovery
Fri May 18 22:42:28  2018
Errors in file d:\oracle\product\10.2.0\admin\xifenfei\udump\xifenfei_ora_3148.trc:
ORA-00704: 引导程序进程失败
ORA-00702: 引导程序版本 '' 与版本 '8.0.0.0.0' 不一致
Fri May 18 22:42:28  2018
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 3148
ORA-1092 signalled during: ALTER DATABASE OPEN...

根据以前恢复经验ORA-00702: bootstrap verison ” inconsistent with version ’8.0.0.0.0′,很可能是由于bootstrap$表异常了.
通过dbv检查system文件确认没有坏块

通过bbed分析,确认记录被删除
把数据文件拷贝到本地,通过bbed进行分析,确认记录丢失

BBED> map
 File: d:/system01.dbf (0)
 Block: 379                                   Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 48 bytes                     @20
 struct kdbh, 14 bytes                      @68
 struct kdbt[1], 4 bytes                    @82
 sb2 kdbr[24]                               @86
 ub1 freespace[1158]                        @134
 ub1 rowdata[6896]                          @1292
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[6875]
-------------
ub1 rowdata[6875]                           @8167     0x3c
BBED> x /rnnc
rowdata[6875]                               @8167
-------------
flag@8167: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@8168: 0x01
cols@8169:    0

故障原因跟踪
有人在数据库中注入了恶意脚本，导致数据库删除了bootstrap$中数据,关闭之后无法正常启动

处理方法
通过oracle bbed 修复数据字典,正常启动数据库

使用_unnest_subquery优化sql

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：使用_unnest_subquery优化sql

一个复杂的sql查询,使用了大量EXISTS和NOT EXISTS 关联导致sql执行效率低下,这里挑选出来最核心的部分进行演示

SQL> explain plan for   select
  2   a.aab034, a.aac001
  3    from si_dp.ac01_ac02 a
  4   where exists (select 1
  5            from ic40
  6           where aac001 = a.aac001
  7             and aae045 <= '201803'
  8             and aae120 = '0')
  9     and not exists (select 1
 10            from ic15
 11           where aac001 = a.aac001
 12             and aae002 <= '201803')
 13     and not EXISTS (select aab001
 14            from ab01
 15           where aab019 in ('91', '93')
 16             AND aab001 = a.aab001)
 17    and exists (select 1
 18            from ac13
 19           where aac001 = a.aac001
 20             and aae140 = '11'
 21             and aae114 in ('0', '1')
 22             and aae002 <= '201803')
 23     AND EXISTS (SELECT 1
 24            FROM AC13
 25           WHERE AAC001 = A.AAC001
 26             and aae140 = '11'
 27             AND AAE143 = '02'
 28             AND AAE003 < '201707'
 29             AND AAE002 BETWEEN '201801' AND '201803'
 30             and aae114 = '1')
 31     AND not EXISTS (SELECT 1
 32         FROM AC13
 33           WHERE AAC001 = A.AAC001
 34             and aae140 = '11'
 35          AND AAE002 < '201801')
 36     AND not EXISTS (SELECT 1
 37            FROM ac02
 38           WHERE AAC001 = A.AAC001
 39             and aae140 = '11'
 40             AND AAE036 < date '2018-1-1');
Explained.
Elapsed: 00:00:00.36
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1 |   202 |       | 11172   (2)|
|   1 |  NESTED LOOPS SEMI                |                    |     1 |   202 |       | 11172   (2)|
|   2 |   NESTED LOOPS ANTI               |                    |     1 |   175 |       | 11168   (2)|
|   3 |    NESTED LOOPS SEMI              |                    |     1 |   150 |       | 11164   (2)|
|   4 |     NESTED LOOPS ANTI             |                    |     1 |   126 |       | 11160   (2)|
|   5 |      NESTED LOOPS SEMI            |                    |     1 |   104 |       | 11158   (2)|
|   6 |       NESTED LOOPS ANTI           |                    |     1 |    67 |       | 11145   (2)|
|   7 |        HASH JOIN ANTI             |                    |     1 |    50 |  8640K| 11143   (2)|
|   8 |         TABLE ACCESS FULL         | AC01_AC02          |   245K|  5755K|       |   356   (2)|
|   9 |         TABLE ACCESS FULL         | AC02               |   559K|    13M|       |  9346   (2)|
|  10 |        TABLE ACCESS BY INDEX ROWID| AB01               |     2 |    34 |       |     2   (0)|
|  11 |         INDEX UNIQUE SCAN         | PK_AB01            |     1 |       |       |     1   (0)|
|  12 |       TABLE ACCESS BY INDEX ROWID | AC13               |   325K|    11M|       |    13   (0)|
|  13 |        INDEX RANGE SCAN           | I_AC13_AAE143      |   446 |       |       |     4   (0)|
|  14 |      INDEX RANGE SCAN             | PK_IC15            |  1771K|    37M|       |     2   (0)|
|  15 |     TABLE ACCESS BY INDEX ROWID   | IC40               |    17M|   395M|       |     4   (0)|
|  16 |      INDEX RANGE SCAN             | PK_IC40            |     1 |       |       |     3   (0)|
|  17 |    TABLE ACCESS BY INDEX ROWID    | AC13               |    51M|  1236M|       |     4   (0)|
|  18 |     INDEX RANGE SCAN              | RELATION_233112_FK |     3 |       |       |     3   (0)|
|  19 |   TABLE ACCESS BY INDEX ROWID     | AC13               |    52M|  1350M|       |     4   (0)|
|  20 |    INDEX RANGE SCAN               | RELATION_233112_FK |     3 |       |       |     3   (0)|
-----------------------------------------------------------------------------------------------------

这条sql,在一个10.2.0.3的系统中执行了十几个小时无法出结果,开发商反馈,该大部分客户的11.2的环境中,大概十几分钟出结果.从来没有遇到此类情况.让我们给他优化sql.看到这个sql,第一反应就是很可能大量的NESTED LOOPS效率低下,怀疑统计信息错误,结果收集完统计信息之后,执行计划依旧,我就在思考怎么调整sql,让其不这样大量嵌套执行.想起来的_unnest_subquery是控制子查询嵌套转换的,从9i开始默认为true,尝试设置为false测试.

SQL> alter session set "_unnest_subquery"=false;
Session altered.
Elapsed: 00:00:00.00
SQL> explain plan for   select
  2   a.aab034, a.aac001
  3    from si_dp.ac01_ac02 a
  4   where exists (select 1
  5            from ic40
  6           where aac001 = a.aac001
  7             and aae045 <= '201803'
  8             and aae120 = '0')
  9     and not exists (select 1
 10            from ic15
 11           where aac001 = a.aac001
 12             and aae002 <= '201803')
 13     and not EXISTS (select aab001
 14            from ab01
 15           where aab019 in ('91', '93')
 16             AND aab001 = a.aab001)
 17    and exists (select 1
 18            from ac13
 19          where aac001 = a.aac001
 20             and aae140 = '11'
 21             and aae114 in ('0', '1')
 22             and aae002 <= '201803')
 23     AND EXISTS (SELECT 1
 24            FROM AC13
 25           WHERE AAC001 = A.AAC001
 26             and aae140 = '11'
 27             AND AAE143 = '02'
 28             AND AAE003 < '201707'
 29             AND AAE002 BETWEEN '201801' AND '201803'
 30             and aae114 = '1')
 31     AND not EXISTS (SELECT 1
 32            FROM AC13
 33           WHERE AAC001 = A.AAC001
 34             and aae140 = '11'
 35             AND AAE002 < '201801')
 36     AND not EXISTS (SELECT 1
 37            FROM ac02
 38           WHERE AAC001 = A.AAC001
 39             and aae140 = '11'
 40             AND AAE036 < date '2018-1-1');
Explained.
Elapsed: 00:00:00.07
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |   185K|    19M|       |  2991K  (2)|
|   1 |  FILTER                       |                  |       |       |       |            |
|   2 |   HASH JOIN RIGHT SEMI        |                  |   185K|    19M|    16M|   758K  (3)|
|   3 |    TABLE ACCESS BY INDEX ROWID| AC13             |   353K|    12M|       |  4556   (1)|
|   4 |     INDEX SKIP SCAN           | I_AC13_AAB001    | 23608 |       |       |  2287   (1)|
|   5 |    HASH JOIN SEMI             |                  |   201K|    14M|    11M|   751K  (3)|
|   6 |     HASH JOIN SEMI            |                  |   201K|  9452K|  8640K|   123K  (3)|
|   7 |      TABLE ACCESS FULL        | AC01_AC02        |   245K|  5755K|       |   357   (2)|
|   8 |      TABLE ACCESS FULL        | IC40             |    21M|   481M|       | 86122   (3)|
|   9 |     TABLE ACCESS FULL         | AC13             |    52M|  1350M|       |   530K  (3)|
|  10 |   INDEX RANGE SCAN            | PK_IC15          |     2 |    44 |       |     3   (0)|
|  11 |   VIEW                        | index$_join$_009 |     1 |    17 |       |     3  (34)|
|  12 |    HASH JOIN                  |                  |       |       |       |            |
|  13 |     INDEX RANGE SCAN          | PK_AB01          |     1 |    17 |       |     2   (0)|
|  14 |     INLIST ITERATOR           |                  |       |       |       |            |
|  15 |      INDEX RANGE SCAN         | IDX_AB01_AAB019  |     1 |    17 |       |     8   (0)|
|  16 |   TABLE ACCESS BY INDEX ROWID | AC13             |     2 |    50 |       |     5   (0)|
|  17 |    INDEX RANGE SCAN           | I_AC13_SEARCH    |   152 |       |       |     4   (0)|
|  18 |   TABLE ACCESS BY INDEX ROWID | AC02             |     1 |    26 |       |     4   (0)|
|  19 |    INDEX RANGE SCAN           | PK_AC02          |     1 |       |       |     3   (0)|
-----------------------------------------------------------------------------------------------

让开发设置该参数,然后执行sql,结果3分钟不到出结果,非常圆满完成任务.该sql还有进一步优化空间,但是考虑到已经满足要求,不再折腾.

ORA-19821故障分析

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-19821故障分析

数据库报错
数据库启动报ORA-00283和ORA-19821错

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-19821: an intentionally corrupt log file was found
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-19821: an intentionally corrupt log file was found

Thu May 03 12:06:51 2018
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: D:\APP\SOHTDB\ORADATA\xifenfei\REDO01.LOG
Media Recovery failed with error 19821
Errors in file d:\app\sohtdb\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_pr00_660.trc:
ORA-00283: recovery session canceled due to errors
ORA-19821: an intentionally corrupt log file was found
Slave exiting with ORA-283 exception
Errors in file d:\app\sohtdb\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_pr00_660.trc:
ORA-00283: recovery session canceled due to errors
ORA-19821: an intentionally corrupt log file was found
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

ORA-19821报错原因
这个错误相对比较少见，查询mos，由于设置了_disable_logging = TRUE导致该问题

检查alert日志,_disable_logging参数确实被认为设置为true了.

解决方法
根据官方的描述,这样的情况无法常规恢复,但是我们知道设置这个参数是为了不产生日志,因此出现这种情况,只能通过隐含参数,禁止数据库进行实例恢复,强制打开数据库.在这样的过程中非常容易遭遇类似ORA-600 2662的错误.

设置这个参数是为了不产生日志，当数据库非干净关闭(主机断电，数据库crash，shutdown abort等）,就非常可能导致数据库无法正常启动.***千不可万不能在生产环境中设置_disable_logging = TRUE***
参考文档:Ora-19821 during the recovery (Doc ID 1217143.1)
Init.ora Parameter “_DISABLE_LOGGING” [Hidden] Reference Note (Doc ID 29552.1)

Oracle 数据文件大小为0kb或者文件丢失恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 数据文件大小为0kb或者文件丢失恢复

接到一个朋友恢复请求,由于rose频繁切换导致文件系统部分数据文件变化为0kb和文件丢失.
故障现象
部分数据文件变化为0kb和文件丢失.

这里比较明显,数据库的users03变为了0kb和users04丢失.数据库alert日志报错信息如下:

Completed: alter database mount exclusive
alter database open
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_dbw0_12008.trc:
ORA-01157: ????/?????? 7 - ??? DBWR ????
ORA-01110: ???? 7: 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF'
ORA-27047: ??????????
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 38) 已到文件结尾。
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_dbw0_12008.trc:
ORA-01157: ????/?????? 8 - ??? DBWR ????
ORA-01110: ???? 8: 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS04.DBF'
ORA-27041: ??????
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_12040.trc:
ORA-01157: ????/?????? 7 - ??? DBWR ????
ORA-01110: ???? 7: 'E:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS03.DBF'
ORA-1157 signalled during: alter database open...
Fri May 04 09:35:10 2018
Checker run found 2 new persistent data failures

alert日志的报错也比较明显,users03是文件超过了大小(大小为0kb,读取之后肯定超过大小),users04提示无法打开文件(文件在文件系统层面已经丢失).现在问题比较明显由于文件系统故障导致文件大小为0和丢失

碎片扫描恢复
常规的方法肯定无法恢复,比较好的方法只能是底层碎片扫描重组,结合多种扫描工具,最后发现一个做底层恢复的朋友的工具效果不错,扫描结果如下

通过工具分析坏块情况

C:\Users\Administrator>dbv FiLe=D:\0504\ORCL_TS.4_FILE.7_10.ora
DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 5月 5 08:52:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = D:\0504\ORCL_TS.4_FILE.7_10.ora
………………
页 382565 标记为损坏
Corrupt block relative dba: 0x01c5d665 (file 7, block 382565)
Completely zero block found during dbv:
页 382566 标记为损坏
Corrupt block relative dba: 0x01c5d666 (file 7, block 382566)
Completely zero block found during dbv:
页 382567 标记为损坏
Corrupt block relative dba: 0x01c5d667 (file 7, block 382567)
Completely zero block found during dbv:
DBVERIFY - 验证完成
检查的页总数: 1374720
处理的页总数 (数据): 27582
失败的页总数 (数据): 0
处理的页总数 (索引): 20114
失败的页总数 (索引): 0
处理的页总数 (其他): 1319752
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 1
标记为损坏的总页数: 7271
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 228271996 (0.228271996)
C:\Users\Administrator>dbv FiLe=D:\0504\ORCL_TS.4_FILE.8_8.ora
DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 5月 5 08:52:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - 开始验证: FILE = D:\0504\ORCL_TS.4_FILE.8_8.ora
DBVERIFY - 验证完成
检查的页总数: 1136896
处理的页总数 (数据): 36639
失败的页总数 (数据): 0
处理的页总数 (索引): 57038
失败的页总数 (索引): 0
处理的页总数 (其他): 1043218
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 1
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 228271997 (0.228271997)
C:\Users\Administrator>

这里通过分析恢复的两个文件总的block数量2511618，其中连续损坏7271个block损坏,由于出现问题之后,数据库被offline这两个文件继续启动运行了几个小时,导致少量block被覆盖,恢复软件直接置空.后续的恢复比较顺利,正常open数据库,然后处理坏块对象(正好不是业务核心表的lob字段,所有部分丢失影响不是非常大).

温馨提醒:
1. 数据文件和备份不要放在同一个阵列上,更不能是同一个分区（卷）上
2. 出现此类问题之后,应当理解停止对该分区的任何写操作,方式丢失或者大小为0KB的文件被覆盖.
如果需要专业ORACLE数据库恢复技术支持，请联系我们
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com

Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases—201804

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases—201804

Patchsets
12.1.0.2 (12.1.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER)	21419221
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
^A10.2.0.4 (10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER)	6810189
^B10.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

12.2.0.1
Description	Database Update	GI Update	Windows Bundle Patch
APR2018 (12.2.0.1.180417)	27674384	27468969	27426753
JAN2018 (12.2.0.1.180116)	27105253	27100009	27162931
NOV2017 (12.2.0.1.171121)	NA	27010638	NA
OCT2017 (12.2.0.1.171017)	26710464	26737266	26758841
AUG2017 (12.2.0.1.170814)	26609817	26610291	26204214
JUL2017 (12.2.0.1.170718)	26123830	26133434	26204212

12.1.0.2
Description	PSU	GI PSU	Proactive Bundle Patch	Bundle Patch(Windows 32bit & 64bit)
APR2018 (12.1.0.2.180417)	27338041	27468957	27486326	27440294
JAN2018 (12.1.0.2.180116)	26925311	27010872	27010930	27162953
OCT2017 (12.1.0.2.171017)	26713565	26635815	26635880	26720785
AUG2017(12.1.0.2.170814)	26609783	26610308	26610322	26161726
JUL2017 (12.1.0.2.170718)	25755742	25901062	26022196	26161724
APR2017 (12.1.0.2.170418)	25171037	25434003	25433352	25632533
JAN2017 (12.1.0.2.170117)	24732082	24917825	24968615	25115951
OCT2016 (12.1.0.2.161018)	24006101	24412235	24448103	24591642
JUL2016 (12.1.0.2.160719)	23054246	23273629	23273686	23530387
APR2016 (12.1.0.2.160419)	22291127	22646084	22899531	22809813
JAN2016 (12.1.0.2.160119)	21948354	22191349	22243551	22310559
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
Description	PSU	A	B	Windows 64 bit	Windows 32 bit
JUL2016 (12.1.0.1.160719)	23054354	23273935	23273958	23530410
APR2016 (12.1.0.1.160419)	22291141	22654153	22654166	22839614
JAN2016 (12.1.0.1.160119)	21951844	22191492	22191511	22494866
OCT2015	21352619(12.1.0.1.9)	21551666(12.1.0.1.9)	21551685(12.1.0.1.9)	21744907 (12.1.0.1.21)
JUL2015	20831107(12.1.0.1.8)	20996901(12.1.0.1.8)	20996911(12.1.0.1.8)	21076681 (12.1.0.1.20)
APR2015	20299016(12.1.0.1.7)	20485762(12.1.0.1.7)	19971331(12.1.0.1.7)	20558101 (12.1.0.1.18)
JAN2015	19769486(12.1.0.1.6)	19971324(12.1.0.1.6)	19971331(12.1.0.1.6)	20160748 (12.1.0.1.16)
OCT2014	19121550(12.1.0.1.5)	19392372(12.1.0.1.5)	19392451(12.1.0.1.5)	19542943 (12.1.0.1.14)
JUL2014	18522516(12.1.0.1.4)	18705901(12.1.0.1.4)	18705972(12.1.0.1.4)	19062327 (12.1.0.1.11)
APR2014	18031528(12.1.0.1.3)	18139660(12.1.0.1.3)	18413105(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)
APR2018 (11.2.0.4.181017)	27338049	26474853	27475913	27381640
JAN2018 (11.2.0.4.180116)	26925576	N/A	27107360	27162965
OCT2017 (11.2.0.4.171017)	26392168	26474853	26635745	26581376
AUG2017 (11.2.0.4.170814)	26609445	N/A	26610246	26194138
JUL2017 (11.2.0.4.170718)	25869727	25879656	26030799	26194136
APR2017 (11.2.0.4.170418)	24732075	25369547	25476126	25632525
JAN2017	N/A	N/A	N/A	N/A
OCT2016 (11.2.0.4.161018)	24006111	24433711	24436338	24591646
JUL2016 (11.2.0.4.160719)	23054359	23177648	23274134	23530402
APR2016 (11.2.0.4.160419)	22502456	22502493	22646198	22839608
JAN2016 (11.2.0.4.160119)	21948347	21972320	22191577	22310544
OCT2015	21352635 (11.2.0.4.8)	21352646	21523375 (11.2.0.4.8)	21821802 (11.2.0.4.20)
JUL2015	20760982 (11.2.0.4.7)	20803583	20996923 (11.2.0.4.7)	21469106 (11.2.0.4.18)
APR2015	20299013 (11.2.0.4.6)	20299015	20485808 (11.2.0.4.6)	20544696 (11.2.0.4.15)
JAN2015	19769489 (11.2.0.4.5)	19854503	19955028 (11.2.0.4.5)	20127071 (11.2.0.4.12)
OCT2014	19121551 (11.2.0.4.4)	19271443	19380115 (11.2.0.4.4)	19651773 (11.2.0.4.10)
JUL2014	18522509 (11.2.0.4.3)	18681862	18706472 (11.2.0.4.3)	18842982 (11.2.0.4.7)
APR2014	18031668 (11.2.0.4.2)	18139690	18139609 (11.2.0.4.2)	18296644 (11.2.0.4.4)
JAN2014	17478514 (11.2.0.4.1)	17551709	N/A	17987366 (11.2.0.4.1)

11.2.0.3
Description	PSU	SPU(CPU)	GI PSU	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)
JUL2015	20760997 (11.2.0.3.15)	20803576	20996944 (11.2.0.3.15)	21104036	21104035
APR2015	20299017 (11.2.0.3.14)	20299010	20485830 (11.2.0.3.14)	20420395	20420394
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)
OCT2013	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)
JUL2011	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

10.2.0.5
Description	PSU	SPU(CPU)	Bundle Patch (Windows 64bit)	Bundle Patch (Windows 32bit)	Bundle Patch (Windows Itanium)
JUL2015	20299014(10.2.0.5.19)	20299021	20420387	20420386	N/A
APR2015	N/A	N/A	N/A	N/A	N/A
JAN2015	19769505(10.2.0.5.18)	19854436	20126868	20126867	N/A
OCT2014	19274523(10.2.0.5.17)	19274521	19618565	19618563	N/A
JUL2014	18522511(10.2.0.5.16)	18681879	18940198	18940196	N/A
APR2014	18031728(10.2.0.5.15)	18139709	18372261	18372259	N/A
JAN2014	17465584(10.2.0.5.14)	17551414	17906974	17906972	N/A
OCT2013	17082365(10.2.0.5.13)	17082371	N/A	17363822	N/A
JUL2013	16619894(10.2.0.5.12)	16742123	16803782	16803780	16803781
APR2013	16056270(10.2.0.5.11)	16270946	16345857	16345855	16345856
JAN2013	14727319(10.2.0.5.10)	14841459	15848062	15848060	15848061
OCT2012	14275629(10.2.0.5.9)	14390396	14553358	14553356	14553357
JUL2012	13923855(10.2.0.5.8)	14038805	14134053	14134051	14134052
APR2012	13632743(10.2.0.5.7)	13632738	13654815	13654814	13870404
JAN2012	13343471(10.2.0.5.6)	13343467	13460968	13460967	N/A
OCT2011	12827745(10.2.0.5.5)	12828105	N/A	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)
JUL2013	16619897 (10.2.0.4.17)	16742253	N/A	N/A	N/A
APR2013	16056269 (10.2.0.4.16)	16270931	N/A	N/A	N/A
JAN2013	14736542 (10.2.0.4.15)	14841471	N/A	N/A	N/A
OCT2012	14275630 (10.2.0.4.14)	14390410	N/A	N/A	N/A
JUL2012	13923851 (10.2.0.4.13)	14038814	N/A	N/A	N/A
APR2012	12879933 (10.2.0.4.12)	12879926	13928775	13928776	N/A
JAN2012	12879929 (10.2.0.4.11)	12879912	13654060	N/A	N/A
OCT2011	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)
JAN2009	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
OCT2008	7375660	N/A	N/A	N/A
JUL2008	7154083	N/A	N/A	N/A
APR2008	6864071	N/A	N/A	N/A
JAN2008	6646850	N/A	N/A	N/A
OCT2007	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

参考:Assistant: Download Reference for Oracle Database/GI PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (文档 ID 2118136.2)

.ALCO 比特币加密勒索恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：.ALCO 比特币加密勒索恢复

最近又朋友咨询了另外一个win平台被比特币勒索加密后缀名为:.ALCO+的oracle数据库恢复请求.

经过分析发现该病毒比以往的更加恶心,对文件头和尾都进行了间隔方式加密

分析结果证明,ALCO+对oracle文件头和尾的318 block分别进行间隔加密
通过我们的分析,对于该类的故障,也能够有比较好的恢复结果

WRI$_ADV_OBJECTS表过大,导致SYSAUX表空间不足

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：WRI$_ADV_OBJECTS表过大,导致SYSAUX表空间不足

12.2的sysaux表空间使用过大

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0
PL/SQL Release 12.2.0.1.0 - Production                                                    0
CORE    12.2.0.1.0      Production                                                        0
TNS for Linux: Version 12.2.0.1.0 - Production                                            0
NLSRTL Version 12.2.0.1.0 - Production                                                    0
SQL> @tbs
Name      TS Type      All Size(MB)   Max Size(MB)  Free Size(MB)  Max Free Pct. Free Max Free%
--------- ---------- -------------- -------------- -------------- --------- --------- ---------
SYSAUX    PERMANENT          32,760         32,768             26        34         0         0
USERS     PERMANENT           1,784         32,768             85    31,069         5        95
SYSTEM    PERMANENT             860         32,768             10    31,917         1        97
R_INDEX   PERMANENT           5,900        229,376            927   224,403        16        98
RICHMAN   PERMANENT           3,000        196,608          1,895   195,503        63        99
UNDOTBS1  UNDO                1,600         32,768          1,560    32,728        97       100
6 rows selected.

awrinfo查看

********************************************************
(1b) SYSAUX occupants space usage (v$sysaux_occupants)
********************************************************
|
| Occupant Name        Schema Name               Space Usage
| -------------------- -------------------- ----------------
| SM/ADVISOR           SYS                       30,422.9 MB
| SM/OPTSTAT           SYS                        1,222.7 MB
| SM/AWR               SYS                          588.2 MB
| SM/OTHER             SYS                          152.4 MB
**********************************
(4) Space usage by non-AWR components (> 500K)
**********************************
COMPONENT        MB SEGMENT_NAME                                      SEGMENT_TYPE
--------- --------- ------------------------------------------------- -------------
NON_AWR    15,675.0 SYS.WRI$_ADV_OBJECTS                              TABLE
NON_AWR     8,764.0 SYS.WRI$_ADV_OBJECTS_IDX_01                       INDEX
NON_AWR     5,959.0 SYS.WRI$_ADV_OBJECTS_PK                           INDEX
NON_AWR       488.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST                INDEX
NON_AWR       249.0 SYS.I_WRI$_OPTSTAT_H_ST                           INDEX

这里为ADVISOR功能模块导致，而且主要是WRI$_ADV_OBJECTS表及其索引
分析主要对象

SQL>  COL SEGMENT_NAME FORMAT A30
SQL>  COL OWNER FORMAT A10
SQL>  COL TABLESPACE_NAME FORMAT A10
SQL>  COL SEGMENT_TYPE FORMAT A15
SQL> SELECT * FROM (SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,BYTES/1024/1024
 2  "SIZE(MB)",SEGMENT_TYPE FROM DBA_SEGMENTS WHERE
 3  TABLESPACE_NAME='SYSAUX' ORDER BY BYTES DESC) WHERE ROWNUM<=10;
SEGMENT_NAME                   OWNER      Name         SIZE(MB) SEGMENT_TYPE
------------------------------ ---------- ---------- ---------- ---------------
WRI$_ADV_OBJECTS               SYS        SYSAUX          15675 TABLE
WRI$_ADV_OBJECTS_IDX_01        SYS        SYSAUX           8764 INDEX
WRI$_ADV_OBJECTS_PK            SYS        SYSAUX           5959 INDEX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS        SYSAUX            488 INDEX
I_WRI$_OPTSTAT_H_ST            SYS        SYSAUX            249 INDEX
SYS_LOB0000007350C00005$$      SYS        SYSAUX       133.1875 LOBSEGMENT
SYS_LOB0000010641C00038$$      SYS        SYSAUX       110.1875 LOBSEGMENT
WRH$_SQL_PLAN                  SYS        SYSAUX             64 TABLE
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST  SYS        SYSAUX             51 INDEX
SYS_LOB0000067470C00006$$      MDSYS      SYSAUX        50.1875 LOBSEGMENT
10 rows selected.

这里也比较明显主要是由于WRI$_ADV_OBJECTS表及其index占用空间较多导致.WRI$_ADV_OBJECTS表主要是12.2新特性Optimizer Statistics Advisor功能使用到的表,用来存储相关数据

清理WRI$_ADV_OBJECTS相关数据

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/
EXEC DBMS_STATS.INIT_PACKAGE();
ALTER TABLE WRI$_ADV_OBJECTS MOVE;
ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD;
ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD;

禁用Optimizer Statistics Advisor Task

DECLARE
filter1 CLOB;
BEGIN
filter1 := DBMS_STATS.CONFIGURE_ADVISOR_RULE_FILTER('AUTO_STATS_ADVISOR_TASK','EXECUTE',NULL,'DISABLE');
END;
/

参考文档：SYSAUX Tablespace Grows Rapidly After Upgrading Database to 12.2.0.1 Due To Statistics Advisor (Doc ID 2305512.1)

年度归档：2018