标签归档：Oracle Recovery Tools

修改oracle scn小工具(patch scn)

发表于 2022 年 6 月 14 日由惜分飞

在一些情况下(特别是一些数据库非常规恢复场景中),需要修改oracle scn绕过一些错误,让数据库open成功,在以前的版本中我们可以通过event,隐含参数,oradebug等方法进行修改,在一些较新的版本中这些方法都被oracle屏蔽,无法实现oracle scn进行调整,针对这种情况,开发了一个Patch_SCN小程序,实现对oracle数据库的scn进行调整

SQL> select dbms_flashback.get_system_change_number a from dual;

               A
----------------
    107367806959

通过工具查询scn信息,由于oracle的scn是动态的,因此和get_system_change_number 查询值有细微出入

win版本修改scn

通过查询确认scn修改成功

增加一键通过修改控制文件scn实现改变数据库scn的功能—202210增加

linux版本修改SCN
查询当前数据库SCN

SQL> startup mount
ORACLE instance started.

Total System Global Area  551165952 bytes
Fixed Size                  2255112 bytes
Variable Size             369100536 bytes
Database Buffers          171966464 bytes
Redo Buffers                7843840 bytes
Database mounted.

SQL> alter database open;

Database altered.

SQL>  select dbms_flashback.get_system_change_number a from dual;

         A
----------
 248118193

关闭数据库，启动到mount，为修改SCN做准备（为了模拟真实环境，只让程序在mount情况下修改scn，open情况下可以修改但是无实际意义）

SQL> startup mount;
ORACLE instance started.

Total System Global Area  551165952 bytes
Fixed Size                  2255112 bytes
Variable Size             369100536 bytes
Database Buffers          171966464 bytes
Redo Buffers                7843840 bytes
Database mounted.
SQL> select spid from v$process where addr = 
  2     (select paddr from v$session where sid=
  3      (select sid from v$mystat where rownum=1));

SPID
------------------------
21019

进行SCN修改

[oracle@iZbp11c0qyuuo1gr7j98upZ tmp]$ ./Patch_SCN 21019(会话进程号) 300000000(期望修改SCN值)
Machine Code:W0UY-SV09-71CY-IEWA

Please input Key:42FB4ADAB72BB4AD   <----需要联系软件作者惜分飞获取
Confirm modification, please input [Y]...
Y
Modify the Oracle SCN value to:11E1A300:300000000

启动数据库，查询scn

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select dbms_flashback.get_system_change_number a from dual;

         A
----------
 300000244   ---由于数据库启动之后，scn稍微增加，属于正常情况

通过上述测试，证明Patch_SCN可以完美实现linux平台Oracle 数据库的SCN调整工作

该功能的通用版已经包含到oracle racovery tools工具中(注册版可用)

Patch_SCN下载:Patch_SCN下载
Patch_SCN使用说明:Patch_SCN使用说明
OraRecovery下载:OraRecovery下载
OraRecovery使用说明:OraRecovery使用说明

发表在 Oracle, 小工具 | 标签为 Oracle Recovery Tools, oracle scn修改工具, patch scn, win scn修改, 通过控制文件修改数据库scn | 评论关闭

ORA-600 3600恢复—-resetlogs scn异常

发表于 2022 年 1 月 28 日由惜分飞

由于客户误操作,在有文件offline的情况下进行resetlogs操作,导致有文件resetlogs scn不对

尝试offline异常文件,均报ORA-600 3600

---直接offline
Wed Jan 26 11:08:15 2022
ALTER DATABASE RECOVER  database until cancel  
Media Recovery Start
 started logmerger process
Wed Jan 26 11:08:17 2022
Datafile 8 (ckpscn 731239901) is orphaned on incarnation#=2
Media Recovery failed with error 19909
Slave exiting with ORA-283 exception
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_pr00_133504.trc:
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 8 belongs to an orphan incarnation
ORA-01110: data file 8: 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\XIFENFEI.DBF'
Recovery Slave PR00 previously exited with exception 283
ORA-283 signalled during: ALTER DATABASE RECOVER  database until cancel  ...
Wed Jan 26 11:08:31 2022
alter database datafile 8 offline
Wed Jan 26 11:08:31 2022
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133948.trc  (incident=134637):
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_134637\orcl_dbw0_133948_i134637.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133948.trc:
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 133948): terminating the instance due to error 471
Instance terminated by DBW0, pid = 133948

---offline drop
Wed Jan 26 11:09:20 2022
alter database datafile 8 offline drop
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133932.trc  (incident=135837):
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_135837\orcl_dbw0_133932_i135837.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_dbw0_133932.trc:
ORA-00600: internal error code, arguments: [3600], [8], [14], [], [], [], [], [], [], [], [], []
DBW0 (ospid: 133932): terminating the instance due to error 471
Wed Jan 26 11:09:22 2022
Instance terminated by DBW0, pid = 133932

因为resetlogs scn不对,也无法正常重建控制文件,对于这样的case,可以Oracle Recovery Tools进行修复resetlogs scn,然后直接open库

Wed Jan 26 11:15:12 2022
SMON: enabling cache recovery
Dictionary check beginning
Archived Log entry 3 added for thread 1 sequence 381 ID 0x60b930a1 dest 1:
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process QMNC
Wed Jan 26 11:15:15 2022
QMNC started with pid=25, OS id=131784 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

软件下载:OraRecovery下载
使用说明:使用说明

发表在 Oracle | 标签为 ORA-19909, ORA-600 3600, Oracle Recovery Tools | 评论关闭

校验代码为 6054 坏块故障修复

发表于 2021 年 12 月 4 日由惜分飞

有客户反馈数据库操作报错,希望我们给予处理,报错信息如下:

通过对system01.dbf数据文件分析

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 20:45:23 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBV-00200: 块 DBA 4330012 已标记为损坏
csc(0x0000.1686df22) higher than block scn(0x0000.00000000)
页 135708 失败, 校验代码为 6054


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 93105
失败的页总数 (数据): 0
处理的页总数 (索引): 31365
失败的页总数 (索引): 1
处理的页总数 (其他): 43622
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16228
标记为损坏的总页数: 1
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378591519 (0.378591519)

确认block 135708 有问题,确认起对象为I_OBJ5

SQL> SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME
  2    FROM DBA_EXTENTS A
  3   WHERE FILE_ID = &FILE_ID
  4     AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
输入 file_id 的值:  1
原值    3:  WHERE FILE_ID = &FILE_ID
新值    3:  WHERE FILE_ID = 1
输入 block_id 的值:  135708
原值    4:    AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
新值    4:    AND 135708 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1

OWNER                          SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME                PARTITION_NAME
------------------------------ --------------- ------------------ ------------------------------ -----------------
SYS                            I_OBJ5          INDEX
                    SYSTEM

这个对象为核心对象obj$的index,无法直接rebuild,当然可以通过参考以前blog文档进行重建:bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决,直接对该坏块进行修复.

BBED> p kcbh
struct kcbh, 20 bytes                       @0
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0042121c
   ub4 bas_kcbh                             @8        0x00000000
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0xff
   ub1 flg_kcbh                             @15       0x04 (KCBHFCKV)
   ub2 chkval_kcbh                          @16       0xe8c4
   ub2 spare3_kcbh                          @18       0x0000

BBED> m /x 23df8616
 File: H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF (0)
 Block: 135709           Offsets:    8 to  519           Dba:0x00000000
------------------------------------------------------------------------
 23df8616 00000104 44fe0000 02000000 28000000 22df8616 00000000 02000200
 00000000 0a001800 f7dc1b00 8002c000 bdd80100 00e00000 f331b114 0a001f00
 14081e00 4505c000 2c103b00 01200000 2adf8616 00008009 01000000 3f00a200
 35074010 00000000 e1b24100 56694100 06000000 641f0000 57119211 cd11d208
 97080812 43127e12 b912f412 2f130d09 e013bd09 1b14cc14 0715ab07 5c087d15
 b715f215 a3167007 3507de16 19175417 21088f17 ca170518 4018330a 1f0b7b18
 2c196719 a219dd19 181a531a 8e1a4709 031be607 f809791b b41bef1b 2a1c651c
 db1ce20d 8c1d310d c71d021e 3d1e781e b31e8209 291f291f 291f291f 291f291f
 291f291f 291f0000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

然后dbv报以下错误

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 22:06:49 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF
itl[2] has higher commit scn(0x0000.1686df2a) than block scn (0x0000.1686df23)
页 135708 失败, 校验代码为 6056


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 95152
失败的页总数 (数据): 0
处理的页总数 (索引): 34569
失败的页总数 (索引): 1
处理的页总数 (其他): 38386
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16213
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378683101 (0.378683101)

再次对其进行修复

BBED> m /x 22df8616
 File: H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF (0)
 Block: 135709           Offsets:   88 to  599           Dba:0x00000000
------------------------------------------------------------------------
 22df8616 00008009 01000000 3f00a200 35074010 00000000 e1b24100 56694100
 06000000 641f0000 57119211 cd11d208 97080812 43127e12 b912f412 2f130d09
 e013bd09 1b14cc14 0715ab07 5c087d15 b715f215 a3167007 3507de16 19175417
 21088f17 ca170518 4018330a 1f0b7b18 2c196719 a219dd19 181a531a 8e1a4709
 031be607 f809791b b41bef1b 2a1c651c db1ce20d 8c1d310d c71d021e 3d1e781e
 b31e8209 291f291f 291f291f 291f291f 291f291f 291f0000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes per line>

再次dbv检查修复成功

C:\Users\XFF>dbv file=H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF

DBVERIFY: Release 11.2.0.4.0 - Production on 星期六 12月 4 22:30:14 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - 开始验证: FILE = H:\BAIDUNETDISK\GS6.0BAK20211202\SYSTEM01.DBF


DBVERIFY - 验证完成

检查的页总数: 184320
处理的页总数 (数据): 95152
失败的页总数 (数据): 0
处理的页总数 (索引): 34569
失败的页总数 (索引): 0
处理的页总数 (其他): 38386
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 16213
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 378683101 (0.378683101)

对于这类的坏块,也可以通过我们的Oracle recovery tools进行快速恢复

发表在 Oracle备份恢复 | 标签为 bbed 修复system, bbed修复坏块, csc higher than block scn, has higher commit scn, Oracle Recovery Tools | 评论关闭

17813235971

QQ 咨询

标签归档：Oracle Recovery Tools

修改oracle scn小工具(patch scn)

ORA-600 3600恢复—-resetlogs scn异常

校验代码为 6054 坏块故障修复

文章分类

最近发表

标签归档：Oracle Recovery Tools

修改oracle scn小工具(patch scn)

ORA-600 3600恢复—-resetlogs scn异常

校验代码为 6054 坏块故障修复

标签云

文章分类

最近发表