Skip to content

Oracle - 39. page

通过oradebug的方式解决ORA-00600 [2662]

2662出问题的根源是数据文件的某些block的scn比文件头的scn还要大，通常处理的方法就是将当前的scn推进到比block的scn更大的时候就可以.

使用oradebug的方式处理该问题主要流程如下：

1.定位出问题的block,一般为2662错误后面的e为该block dba，查处该block的scn（2662错误后的d）
2.定位当前系统的scn，一般为2662错误后面的b为该current scn
3.核对系统scn与block scn的差值（根据adjust算法得出的scn差值 也可以）
4.使用oradebug 推荐当前系统scn比block还大为止
5.打开数据库，执行重建

Oracle Oracle恢复 数据技术 | Guang Cai Li | |   2662

ORA-1194/00354/00353/00334处理思路：_allow_resetlogs_corruption

遭遇ORA-1194,00354等错误，在线日志损坏，处理思路过程如下，

--告警报错信息
Thu Jul 24 00:16:08 2014
ORA-1194 signalled during: alter database open resetlogs...
Thu Jul 24 00:16:23 2014
ALTER DATABASE RECOVER database until cancel
Thu Jul 24 00:16:23 2014
Media Recovery Start
parallel recovery started with 2 processes
ORA-279 signalled during: ALTER DATABASE RECOVER database until cancel ...
Thu Jul 24 00:18:35 2014
ALTER DATABASE RECOVER LOGFILE '/oradata/ora10g/lu10g/redo03.log'
Thu Jul 24 00:18:35 2014
Media Recovery Log /oradata/ora10g/lu10g/redo03.log
Thu Jul 24 00:18:36 2014
Errors in file /ora10g/admin/lu10g/udump/lu10g_ora_14994.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 1508 change 4305448435 time 07/23/2014 23:56:57
ORA-00334: archived log: '/oradata/ora10g/lu10g/redo03.log'
Recovery interrupted!
Recovered data files to a consistent state at change 10480752

通过告警报错ORA-00354: corrupt redo log block header 从该错误可以看出当前日志的redo block的header出现问题，导致oracle无法读取redo进行恢复。通过以下检查发现数据库文件头都一致，没有需要恢复的文件，但是由于数据库是非正常关闭，last_change scn没有被写入，数据库判断需要恢复，则读取相应的redo log group 3，但是此时该文件已经损坏，尝试清理log，但是因为group 3在宕机时候为current状态，所以不能被清楚，则尝试通过设置_allow_reset_logs_corruption参数让系统跳过该错误，具体如下过程：

--检查文件状态
SQL> select file#,online_status,to_char(change#,'999999999999') from v$recover_file;

no rows selected

SQL> select file#,to_char(checkpoint_change#,'999999999999'),
2 to_char(last_change#,'999999999999') from v$datafile;

FILE# TO_CHAR(CHECK TO_CHAR(LAST_
---------- ------------- -------------
1 10480749
2 10480749
3 10480749
4 10480749
SQL> /

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 31 52428800 1 YES INACTIVE 10391212 19-JUL-14
3 1 33 52428800 1 NO CURRENT 10480310 23-JUL-14
2 1 32 52428800 1 YES INACTIVE 10427919 20-JUL-14

SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance lu10g (thread 1)
ORA-00312: online log 3 thread 1: '/oradata/ora10g/lu10g/redo03.log'

--在pfile中加入参数
_allow_resetlogs_corruption=true
_allow_error_simulation=TRUE

重启数据库以resetlog方式打开，得到以下告警信息：

Thu Jul 24 00:45:45 2014
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Thu Jul 24 00:45:45 2014
Errors in file /ora10g/admin/lu10g/udump/lu10g_ora_16481.trc:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
Archive all online redo logfiles failed:265
RESETLOGS after incomplete recovery UNTIL CHANGE 10480752
Resetting resetlogs activation ID 2561579617 (0x98ae9a61)
Thu Jul 24 00:45:47 2014
Setting recovery target incarnation to 3
Thu Jul 24 00:45:47 2014
Assigning activation ID 2580873586 (0x99d50172)
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=16, OS id=16490
Thu Jul 24 00:45:47 2014
ARC0: Archival started
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=16492
Thu Jul 24 00:45:47 2014
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /oradata/ora10g/lu10g/redo01.log
Successful open of redo thread 1
Thu Jul 24 00:45:47 2014
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jul 24 00:45:47 2014
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
Thu Jul 24 00:45:47 2014
ARC1: Becoming the heartbeat ARCH
Thu Jul 24 00:45:47 2014
SMON: enabling cache recovery
Thu Jul 24 00:45:47 2014
Errors in file /ora10g/admin/lu10g/udump/lu10g_ora_16481.trc:
ORA-00600: internal error code, arguments: [2662], [0], [10480758], [0], [10480787], [4194313], [], []
Thu Jul 24 00:45:48 2014
Errors in file /ora10g/admin/lu10g/udump/lu10g_ora_16481.trc:
ORA-00600: internal error code, arguments: [2662], [0], [10480758], [0], [10480787], [4194313], [], []
Thu Jul 24 00:45:48 2014
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 16481
ORA-1092 signalled during: alter database open resetlogs...

至此数据库2662错误已经出来了，那就好办了，2662的处理办法参考手工推进SCN的办法以及《谈谈adjust_scn的计算方法》

Oracle恢复 数据技术 | Guang Cai Li | |   _allow_resetlogs_corruption

手工推进oracle scn方式（1）Oradebug 推进scn

环境：LINUX_x86

这里需要注意地方为:

1.我的系统为32bit的小端系统.
2.大小端的SCN_WRAP和SCN_BASE是相反的.在大端中,kcsgscn_的前4字节为SCN_BASE,5-8字节为SCN_WRAP.
3.在32bit系统中寻址只能是4字节,而64位系统可以8字节.

该测试为32bit 小端系统,环境如下:

[root@ludatou ~]# file /sbin/init
/sbin/init: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped

[root@ludatou ~]# su - ora10g
[ora10g@ludatou ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 24 01:58:10 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup mount
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267260 bytes
Variable Size             100665796 bytes
Database Buffers          213909504 bytes
Redo Buffers                2924544 bytes
Database mounted.

检查当前scn情况并以oradebug的方式推进scn:

--当前scn
SQL> select to_char(checkpoint_change#,'XXXXXXXXXXXXXXXX') from v$database;

TO_CHAR(CHECKPOIN
-----------------
A03E2F

SQL> oradebug setmypid
Statement processed.

--获取内存scn对应
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 20009034
--因为32bit系统所以采用4字节寻址,更改scn_wrap
SQL> oradebug poke 0x20009228 4 0x01
BEFORE: [20009228, 2000922C) = 00000000
AFTER: [20009228, 2000922C) = 00000001
--更改scn_base
SQL> oradebug poke 0x2000922C 4 0x01
BEFORE: [2000922C, 20009230) = 00000000
AFTER: [2000922C, 20009230) = 00000001
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000001 00000001 00000000 00000000 00000000 00000000 00000000 20009034
SQL> oradebug poke 0x2000922C 4 0xA03E2F
BEFORE: [2000922C, 20009230) = 00000001
AFTER: [2000922C, 20009230) = 00A03E2F
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000001 00A03E2F 00000000 00000000 00000000 00000000 00000000 20009034
--更改完成后打开数据
SQL> alter database open;

Database altered.
--可以发现scn已经推进了wrap+1位
SQL> select to_char(current_scn,'XXXXXXXXXXXXXXXX') from v$database;

TO_CHAR(CURRENT_S
-----------------
100A04185

 

Oracle恢复 数据技术 | Guang Cai Li | |   Oradebug 推进scn

2014年7月最新的PSU和CPU补丁信息

关于数据库的补丁需要注意的地方：

 1.12.1.0.1.4 PSU依然是个non-composite的补丁

 2.没有关于Client-only installations 的新补丁发布

Patch Information 12.1.0.1.4 Comments
Final Patch –
PSU On-Request platforms

Table 8 describes the available patches for Oracle Database 12.1.0.1.4.

Table 8 Patch Availability for Oracle Database 12.1.0.1.4

Product Home Patch Advisory Number Comments
Oracle Database home Database 12.1.0.1.4 PSU Patch 18522516, orGI 12.1.0.1.4 PSU Patch 18705901 for Linux x86-64, Solaris X64 & Solaris SPARC, or

GI 12.1.0.1.4 PSU Patch 18705972 for AIX, HP IA & zLinux

Microsoft Windows 32-Bit & x86-64 BP 11 Patch 19062327, or later;

Quarterly Full Stack download for Exadata (July 2014) 12.1.0.1 BP Patch 19069261

 CVE-2013-3751, CVE-2013-3774, CVE-2014-4236, CVE-2014-4237, CVE-2014-4245 GI 12.1.0.1.4 PSU Patch 18705901 is applicable to Exadata database servers running Oracle Database 12.1.0.1. For more information, see Note 888828.1GI 12.1.0.1.4 PSU Patch 18705972 for AIX, HP IA & zLinux only contains DB PSU 12.1.0.1.4 sub-patch and no clusterware sub-patches

3.1.4.3 Oracle Database 11.2.0.4

Table 9 describes the Error Correction information for Oracle Database 11.2.0.4.

Table 9 Error Correction information for Oracle Database 11.2.0.4

Patch Information 11.2.0.4 Comments
Final Patch January 2018
SPU On-Request platforms HP-UX PA RISCIBM: Linux on System Z

32-bit client-only platforms except Linux x86
PSU On-Request platforms 32-bit client-only platforms except Linux x86

Table 10 describes the available patches for Oracle Database 11.2.0.4.

Table 10 Patch Availability for Oracle Database 11.2.0.4

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.2.0.4 SPU Patch 18681862, orDatabase 11.2.0.4.3 PSU Patch 18522509, or

GI 11.2.0.4.3 PSU Patch 18706472, or

Microsoft Windows (32-Bit) & x64 (64-Bit) BP 7 Patch 18842982, or later;

Quarterly Database Patch for Exadata (July 2014) 11.2.0.4.9 BP Patch 18840215, or

Quarterly Full Stack download for Exadata (July 2014) BP Patch 19067488

 CVE-2014-4236, CVE-2014-4237, CVE-2014-4245

3.1.4.4 Oracle Database 11.2.0.3

Table 11 describes the Error Correction information for Oracle Database 11.2.0.3.

Table 11 Error Correction information for Oracle Database 11.2.0.3

Patch Information 11.2.0.3 Comments
Final Patch July 2015
SPU On-Request platforms HP-UX PA RISCIBM: Linux on System Z

32-bit client-only platforms except Linux x86
PSU On-Request platforms 32-bit client-only platforms except Linux x86

Table 12 describes the available patches for Oracle Database 11.2.0.3.

Table 12 Patch Availability for Oracle Database 11.2.0.3

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.2.0.3 SPU Patch 18681866, orDatabase 11.2.0.3.11 PSU Patch 18522512, or

GI 11.2.0.3.11 PSU Patch 18706488, or

Quarterly Database Patch for Exadata (July 2014) 11.2.0.3.24 BP Patch 18835772, or

Quarterly Full Stack download for Exadata (July 2014) 11.2.0.3 BP Patch 19067489, or

Microsoft Windows 32-Bit BP 32 Patch 18940193, orlater

Microsoft Windows x86-64 BP 32 Patch 18940194, orlater

 CVE-2014-4245
Oracle Database home CPU Patch 13705478 Released April 2012 OC4J 10.1.3.4 one-off patch (Special OPatch needed, see README)

3.1.4.5 Oracle Database 11.1.0.7

Table 13 describes the Error Correction information for Oracle Database 11.1.0.7.

Table 13 Error Correction information for Oracle Database 11.1.0.7

Patch Information 11.1.0.7 Comments
Final Patch July 2015
SPU On-Request platforms –
PSU On-Request platforms –

Table 14 describes the available patches for Oracle Database 11.1.0.7.

Table 14 Patch Availability for Oracle Database 11.1.0.7

Product Home Patch Advisory Number Comments
Oracle Database home Database 11.1.0.7 SPU Patch 18681875, orDatabase 11.1.0.7.20 PSU Patch 18522513, or

Microsoft Windows (32-Bit) BP 57 Patch 18944207, orlater

Microsoft Windows x86-64 BP 57 Patch 18944208, orlater

 CVE-2014-4245 Patches also applicable to Fusion Middleware 11.1.1.x and 11.1.2.x installations
Oracle Database home OHT BP Patch 16801095 Released July 2013 OHT BP 10.1.3.5 for DB Control
Oracle Database home CPU Patch 13705478 Released April 2012 OC4J 10.1.3.3 one-off patch (Special OPatch needed, see README)
Oracle CRS home CRS 11.1.0.7.7 PSU Patch 11724953 Released April 2011 Non-security content only
Oracle Database home CPU Patch 9288120 Released April 2011 Database UIXFor Oracle Secure Enterprise Search 11.1.2.x installations, follow the instructions in Note 1359600.1
Oracle Database home CPU Patch 10073948 Released April 2011 Enterprise Manager Database Control UIXNot applicable to Oracle Secure Enterprise Search 11.1.2.x
Oracle Database home CPU Patch 11738232 Released April 2011 Warehouse BuilderNot applicable to Oracle Secure Enterprise Search 11.1.2.x

Oracle Oracle 排障配置与调整 数据技术 | Guang Cai Li |