恶意破坏的Oracle数据库恢复案例分享

感谢中亦安图提供机会和大家分享了《恶意破坏的Oracle数据库恢复案例分享》主题ppt分享如下:
1
2
3
4
5


PPT下载:恶意破坏的Oracle数据库恢复案例分享

发表在 Oracle | 留下评论

12c ORA-01113 ORA-01110 恢复

数据库版本
VERSION


数据库启动报错

Completed: ALTER DATABASE   MOUNT
Thu Aug 17 12:34:52 2017
alter database open
Thu Aug 17 12:34:52 2017
Ping without log force is disabled
.
Thu Aug 17 12:34:52 2017
Errors in file D:\APP\ORACLE\diag\rdbms\XIFENFEI\XIFENFEI\trace\XIFENFEI_ora_5960.trc:
ORA-01113: 文件 5 需要介质恢复
ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF'
ORA-1113 signalled during: alter database open

客户尝试恢复

SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes
Fixed Size                  3842760 bytes
Variable Size            1778388280 bytes
Database Buffers         8489271296 bytes
Redo Buffers               29708288 bytes
数据库装载完毕。
SQL> alter  database open;
alter  database open
*
第 1 行出现错误:
ORA-01113: 文件 5 需要介质恢复
ORA-01110: 数据文件 5: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF'

SQL> alter database datafile 5 offline drop;

数据库已更改。

SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01113: 文件 6 需要介质恢复
ORA-01110: 数据文件 6: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF'


SQL> recover datafile 6;
ORA-00283: 恢复会话因错误而取消
ORA-00322: 日志 3 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG'
ORA-00322: 日志 3 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 3 线程 1: 'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'

使用Oracle Database Recovery Check检测结果
check_ctl
check_resulte


尝试恢复

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG'
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG'


SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG
ORA-00310: archived log contains sequence 716; sequence 717 required
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LOG'


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:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'


SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR

C
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG
ORA-00310: archived log contains sequence 715; sequence 717 required
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'


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:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'


SQL> recover database until cancel
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.ARC
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG
已应用的日志。
完成介质恢复。

SQL> alter database datafile 5 online;

数据库已更改。

SQL> recover database until cancel;
ORA-00279: 更改 9843709 (在 08/17/2017 07:04:02 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_717_%U_.AR

C
ORA-00280: 更改 9843709 (用于线程 1) 在序列 #717 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LOG
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'


ORA-01112: 未启动介质恢复


SQL> recover datafile 5;
ORA-00283: 恢复会话因错误而取消
ORA-00264: 不要求恢复


SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,FUZZY,
  4  count(*) ROW_NUM
  5  FROM v$datafile_header
  6  GROUP BY status, checkpoint_change#, checkpoint_time,fuzzy
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME     FUZ          ROW_NUM
------- ------------------ ------------------- --- ----------------
ONLINE             9850826 2017-08-17 08:15:45 NO                 1
ONLINE             9857411 2017-08-17 08:15:45 NO                 7
ONLINE             9857411 2017-08-17 09:00:48 NO                 2

SQL> set numw 16
SQL> SELECT status,
  2  checkpoint_change#,
  3  checkpoint_time,last_change#,
  4  count(*) ROW_NUM
  5  FROM v$datafile
  6  GROUP BY status, checkpoint_change#, checkpoint_time,last_change#
  7  ORDER BY status, checkpoint_change#, checkpoint_time;

STATUS  CHECKPOINT_CHANGE# CHECKPOINT_TIME         LAST_CHANGE#          ROW_NUM

------- ------------------ ------------------- ---------------- ----------------
ONLINE             9857411 2017-08-17 09:00:48          9850826                1
ONLINE             9857411 2017-08-17 09:00:48          9857411                8
SYSTEM             9857411 2017-08-17 09:00:48          9857411                1

SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-01152: 文件 1 没有从过旧的备份中还原
ORA-01110: 数据文件 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF'
[/shell]
这里比较明显,由于controlfile的scn 大于db的scn,从而出现了ORA-01152的错误,重试重建控制文件
<br>
<strong>重建控制文件</strong>
1
SQL> alter database backup controlfile to trace as 'd:\app\ctl.txt';

数据库已更改。


SQL> startup nomount;
ORACLE 例程已经启动。

Total System Global Area      10301210624 bytes
Fixed Size                        3842760 bytes
Variable Size                  1778388280 bytes
Database Buffers               8489271296 bytes
Redo Buffers                     29708288 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "XIFENFEI" NORESETLOGS  NOARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 (
  9      'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG',
 10      'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LO
G'
 11    ) SIZE 50M BLOCKSIZE 512,
 12    GROUP 2 (
 13      'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV68H_.LOG',
 14      'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_2_DN9MV69G_.LO
G'
 15    ) SIZE 50M BLOCKSIZE 512,
 16    GROUP 3 (
 17      'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6BX_.LOG',
 18      'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_3_DN9MV6DW_.LO
G'
 19    ) SIZE 50M BLOCKSIZE 512
 20  DATAFILE
 21    'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9MN5OT_.DBF',
 22    'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSAUX_DN9MK6B3_.DBF',
 23    'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_UNDOTBS1_DN9MQJFK_.DBF',
 24    'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_USERS_DN9MQH75_.DBF',
 25    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE06',
 26    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE05',
 27    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE04',
 28    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE03',
 29    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE02',
 30    'D:\APP\ORACLE\PRODUCT\12.1.0\DBHOME_1\DATABASE\XIFENFEIFILE01'
 31  CHARACTER SET ZHS16GBK
 32  ;

控制文件已创建。

尝试恢复

SQL> recover database;
完成介质恢复。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [4194], [41], [36], [], [], [], [], 
[], [], [], [], []
进程 ID: 2864
会话 ID: 62 序列号: 54236

本来到这一步,错误比较明显,undo异常,这类直接对undo进行处理即可,可是运气不太好

异常crash之后redo损坏

SQL> startup pfile='d:/app/pfile.txt'
ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes
Fixed Size                  3842760 bytes
Variable Size            1778388280 bytes
Database Buffers         8489271296 bytes
Redo Buffers               29708288 bytes
数据库装载完毕。
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 60 更改 23924938639111 时间 08/17/2017 21:36:16
ORA-00312: 联机日志 1 线程 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
ORA-00312: 联机日志 1 线程 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/201721:36:16
ORA-00312: online log 1 thread 1:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'
ORA-00312: online log 1 thread 1:
'D:\APP\ORACLE\ORADATA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV652_.LOG'


SQL> recover database until cancel;
ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的
ORA-00289: 建议:
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O1_MF_1_718_%U_.ARC
ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 60 change 23924938639111 time 08/17/2017 21:36:16
ORA-00334: archived log:
'D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ONLINELOG\O1_MF_1_DN9MV661_.LOG'

到这一步,只能通过屏蔽oracle 事务前滚,强制拉库恢复。

SQL> startup pfile='d:/app/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.0301E+10 bytes
Fixed Size                  3842760 bytes
Variable Size            1778388280 bytes
Database Buffers         8489271296 bytes
Redo Buffers               29708288 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 10050832 (在 08/17/2017 21:36:13 生成) 对于线程 1 是必需的
ORA-00289: 建议: D:\APP\ORACLE\FAST_RECOVERY_AREA\XIFENFEI\ARCHIVELOG\2017_08_17\O
1_MF_1_718_%U_.ARC
ORA-00280: 更改 10050832 (用于线程 1) 在序列 #718 中


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
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: 'D:\APP\ORACLE\ORADATA\XIFENFEI\DATAFILE\O1_MF_SYSTEM_DN9M
N5OT_.DBF'


ORA-01112: 未启动介质恢复


SQL> alter database open resetlogs;

数据库已更改。
发表在 Oracle备份恢复 | 标签为 , | 留下评论

mount: wrong fs type, bad option, bad superblock恢复Oracle

有朋友找到我们,说对lv进行收缩操作之后,导致文件系统无法mount,提示超级块损坏.
尝试mount失败

[root@GZGSDB data]# mount /dev/vg_gzgsdb/lv_home /home
mount: wrong fs type, bad option, bad superblock on /dev/mapper/vg_gzgsdb-lv_home,
       missing codepage or helper program, or other error
       In some cases useful info is found in syslog - try
       dmesg | tail  or so

系统日志报错

Aug 16 21:23:24 GZGSDB kernel: EXT4-fs (dm-5): ext4_check_descriptors: Block bitmap for group 1 not in group (block 0)!
Aug 16 21:23:24 GZGSDB kernel: EXT4-fs (dm-5): group descriptors corrupted!

主要操作
cz


这里可以看出来,明显操作错误,在没有收缩文件系统之前,直接收缩的lv.
list_history


这里可以看出来,在发生故障之后,又做了很多操作,包括fsck和testdisk等,最终无法恢复,请求我们协助处理。

找出来备份超级块

[root@GZGSDB dul]# dumpe2fs /dev/vg_gzgsdb/lv_home |grep superblock
dumpe2fs 1.41.12 (17-May-2010)
ext2fs_read_bb_inode: A block group is missing an inode table 
  Primary superblock at 0, Group descriptors at 1-52
  Backup superblock at 32768, Group descriptors at 32769-32820
  Backup superblock at 98304, Group descriptors at 98305-98356
  Backup superblock at 163840, Group descriptors at 163841-163892
  Backup superblock at 229376, Group descriptors at 229377-229428
  Backup superblock at 294912, Group descriptors at 294913-294964
  Backup superblock at 819200, Group descriptors at 819201-819252
  Backup superblock at 884736, Group descriptors at 884737-884788
  Backup superblock at 1605632, Group descriptors at 1605633-1605684
  Backup superblock at 2654208, Group descriptors at 2654209-2654260
  Backup superblock at 4096000, Group descriptors at 4096001-4096052
  Backup superblock at 7962624, Group descriptors at 7962625-7962676
  Backup superblock at 11239424, Group descriptors at 11239425-11239476
  Backup superblock at 20480000, Group descriptors at 20480001-20480052
  Backup superblock at 23887872, Group descriptors at 23887873-23887924
  Backup superblock at 71663616, Group descriptors at 71663617-71663668
  Backup superblock at 78675968, Group descriptors at 78675969-78676020
  Backup superblock at 102400000, Group descriptors at 102400001-102400052
  Backup superblock at 214990848, Group descriptors at 214990849-214990900

使用fsck修复

[root@GZGSDB data]# fsck -y /dev/vg_gzgsdb/lv_home 
fsck from util-linux-ng 2.17.2
e2fsck 1.41.12 (17-May-2010)
fsck.ext4: Group descriptors look bad... trying backup blocks...
fsck.ext4: The ext2 superblock is corrupt when using the backup blocks
fsck.ext4: going back to original superblock
fsck.ext4: Device or resource busy while trying to open /dev/mapper/vg_gzgsdb-lv_home
Filesystem mounted or opened exclusively by another program?
--指定超级块恢复
[root@GZGSDB data]# fsck -y -b 102400000 /dev/vg_gzgsdb/lv_home 
…………
Illegal block #0 (1296647770) in inode 354315.  CLEARED.
Illegal block #1 (1398362886) in inode 354315.  CLEARED.
Illegal block #3 (453538936) in inode 354315.  CLEARED.
Illegal block #5 (808333361) in inode 354315.  CLEARED.
Illegal block #6 (775434798) in inode 354315.  CLEARED.
Illegal block #8 (1180306180) in inode 354315.  CLEARED.
Illegal block #9 (1413893971) in inode 354315.  CLEARED.
Illegal block #10 (1229347423) in inode 354315.  CLEARED.
Illegal block #11 (1498613325) in inode 354315.  CLEARED.
Illegal indirect block (1296389203) in inode 354315.  CLEARED.
Inode 354315 is too big.  Truncate? yes

Block #1074301965 (69632) causes directory to be too big.  CLEARED.
Warning... fsck.ext4 for device /dev/mapper/vg_gzgsdb-lv_home exited with signal 11.

[root@GZGSDB data]# mount /dev/vg_gzgsdb/lv_home /home
mount: wrong fs type, bad option, bad superblock on /dev/mapper/vg_gzgsdb-lv_home,
       missing codepage or helper program, or other error
       In some cases useful info is found in syslog - try
       dmesg | tail  or so

至此基本上可以判断,直接修复该文件系统,让其正常mount的概率很小.直接通过lun层面来恢复.

通过工具解析lun
disk_size


比较明显,客户经过一系列操作现在的现象是lv 1.03T,文件系统只有821G,明显和客户给我反馈的操作不符(应该是lv 821G),证明客户做了大量操作,已经导致文件系统损坏
inode_recovery


由于文件系统严重异常,工具获取到的文件都是没有名称,直接从inode里面读取的数据.获取到这些数据之后,然后结合oracle的特性,判断出来对应的文件号关系(这里有大量文件重复);另外有个别文件通过inode恢复丢失,通过底层碎片重组进行恢复出来文件,然后恢复出来其中数据(asm disk header 彻底损坏恢复).这个客户比较幸运,system文件在另外一个分区中,不然工作量会大很多.
再次提醒:对lv操作一定要谨慎,特别是lvreduce操作,另外出现发生误操作之后,应该第一时间保护现场,而不是百度着去乱操作,可能导致故障更加悲剧

发表在 Linux, 非常规恢复 | 标签为 , | 留下评论

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

 

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

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 RU

GI RU

Windows Bundle Patch

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)

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

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)

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)

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

发表在 Oracle | 留下评论

ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_383″

12.2.0.1版本数据库报类似ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_383″错误

Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10213.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_383"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

trace文件信息

Trace file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei/trace/xifenfei_j000_10379.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label:    RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME:    /u01/app/oracle/product/12.2.0/db_1
System name:    Linux
Node name:      yoridb2
Release:        3.10.0-514.26.2.el7.x86_64
Version:        #1 SMP Tue Jul 4 15:04:05 UTC 2017
Machine:        x86_64
Instance name: xifenfei
Redo thread mounted by this instance: 1
Oracle process number: 148
Unix process pid: 10379, image: oracle@yoridb2 (J000)


*** 2017-08-04T14:41:26.486692+08:00
*** SESSION ID:(508.52539) 2017-08-04T14:41:26.486716+08:00
*** CLIENT ID:() 2017-08-04T14:41:26.486722+08:00
*** SERVICE NAME:(SYS$USERS) 2017-08-04T14:41:26.486727+08:00
*** MODULE NAME:(DBMS_SCHEDULER) 2017-08-04T14:41:26.486732+08:00
*** ACTION NAME:(ORA$AT_OS_OPT_SY_384) 2017-08-04T14:41:26.486738+08:00
*** CLIENT DRIVER:() 2017-08-04T14:41:26.486743+08:00

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_384"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

错误比较明显,是在执行统计信息分析的时候报错了,报错原因是由于SCHEDULER的task name无效.

解决方法

[oracle@yoridb2 trace]$ ss

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 4 23:40:33 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4  

no rows selected

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
   and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');  4  

NAME
--------------------------------------------------------------------------------
CTIME     HOW_CREATED
--------- ------------------------------
AUTO_STATS_ADVISOR_TASK
04-AUG-17 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
04-AUG-17 CMD
发表在 ORA-xxxxx | 标签为 , | 留下评论