resetlogs方式打开数据库,某数据文件需要recover,恢复失败的处理方式

简介:

1 数据文件头部和恢复相关的几个偏移量说明: 
         ub4 kcvfhcpc                             @140      0x00000308------检查点计数
         ub4 kcvfhccc                             @148      0x00000307------总是比检查点计算少1
         ub4 kcvcptim                             @492      0x2f9af923-----检查点时间
         ub4 kscnbas                              @484      0x8013ea80-------- scn的低位
         ub2 kscnwrp                              @488      0x0000--------- scn的高位 
   ub4 kcvfhrlc                             @112      0x2f9af2a9------resetlogs count
   struct kcvfhrls, 8 bytes                 @116     
         ub4 kscnbas                              @116      0x8013e6b3-------- resetlogs scn
         ub2 kscnwrp                              @120      0x0000
如果是resetlogs 方式打开数据库对于recover失败的数据文件其数据文件中偏移量 @140、@148、@492、@484、@112、@116应该和其它不需要恢复的数据文件保持一致
其它情况数据文件中偏移量 可能仅需要@140、@148、@492、@484和其它不需要恢复的数据文件保持一致
2重建控制文件的语句:
[oracle@oracle ~]$ cat /oracle/control.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/app/db1/dbs/log1CRM.dbf',
    '/oracle/CRM2/CRM/redo01b.log'
  ) SIZE 200M,
  GROUP 2 (
    '/oracle/app/db1/dbs/log2CRM.dbf',
    '/oracle/CRM2/CRM/redo02b.log'
  ) SIZE 50M,
  GROUP 3 (
    '/oracle/CRM2/CRM/redo03.log',
    '/oracle/CRM2/CRM/redo03b.log'
  ) SIZE 200M,
  GROUP 4 (
    '/oracle/CRM2/CRM/redo04.log',
    '/oracle/CRM2/CRM/redo04b.log'
  ) SIZE 200M,
  GROUP 5 (
    '/oracle/CRM2/CRM/redo05.log',
    '/oracle/CRM2/CRM/redo05b.log'
  ) SIZE 200M,
  GROUP 6 (
    '/oracle/CRM2/CRM/redo06.log',
    '/oracle/CRM2/CRM/redo06b.log'
  ) SIZE 200M
DATAFILE
  '/oracle/CRM2/system1.dbf',
  '/oracle/CRM2/CRM/sysaux01.dbf',
  '/oracle/CRM2/CRM/users01.dbf',
  '/oracle/CRM2/zxa.dbf',
  '/oracle/CRM2/CRM/undotbs2.dbf',
  '/oracle/CRM2/zxc.dbf',
  '/oracle/CRM2/CRM/zxbig1.dbf'
CHARACTER SET ZHS16GBK
;
[oracle@oracle ~]$ exit
exit
3 开始重建控制文件产生missingsnnnn 文件
SQL> @/oracle/control.sql
ORACLE instance started.
Total System Global Area  322961408 bytes
Fixed Size                  2020480 bytes
Variable Size              92277632 bytes
Database Buffers          222298112 bytes
Redo Buffers                6365184 bytes
Control file created.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2148806610 generated at 11/07/2012 12:15:58 needed for thread
1
ORA-00289: suggestion : /oracle/archive/1_3_798683817.dbf
ORA-00280: change 2148806610 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/app/db1/dbs/MISSING00002         RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
4 重命名missingnnnn数据文件
SQL> alter tablespace zx rename datafile '/oracle/app/db1/dbs/MISSING00002' to '/oracle/CRM2/zxb.dbf';
Tablespace altered.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
5 执行恢复报错如下:(前面几个步骤就是为了构造这个错误)
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [krhpfh_03-1202], [fno =], [2],
[fhcrt =], [797369834], [cptim =], [0], []
ORA-01110: data file 2: '/oracle/CRM2/zxb.dbf'
6 bbed参数和文件列表如下:
[oracle@oracle ~]$ cat bbed.para
blocksize=8192
mode=edit
listfile=/oracle/filelist
[oracle@oracle ~]$ cat /oracle/filelist
1 /oracle/CRM2/system1.dbf 503316480
2 /oracle/CRM2/zxb.dbf 10485760
3 /oracle/CRM2/CRM/sysaux01.dbf 304087040
4 /oracle/CRM2/CRM/users01.dbf 5242880
5 /oracle/CRM2/zxa.dbf 104857600
6 /oracle/CRM2/CRM/undotbs2.dbf 209715200
7 /oracle/CRM2/zxc.dbf 10485760
8 /oracle/CRM2/CRM/zxbig1.dbf 2147483648
7 关闭数据库启动bbed对数据文件2进行调整
[oracle@oracle ~]$ bbed parfile=bbed.para
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Nov 7 12:25:14 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /oracle/CRM2/system1.dbf                                         61440
     2  /oracle/CRM2/zxb.dbf                                              1280
     3  /oracle/CRM2/CRM/sysaux01.dbf                                    37120
     4  /oracle/CRM2/CRM/users01.dbf                                       640
     5  /oracle/CRM2/zxa.dbf                                             12800
     6  /oracle/CRM2/CRM/undotbs2.dbf                                    25600
     7  /oracle/CRM2/zxc.dbf                                              1280
     8  /oracle/CRM2/CRM/zxbig1.dbf                                     262144
BBED> show 
        FILE#           1
        BLOCK#          1
        OFFSET          0
        DBA             0x00400001 (4194305 1,1)
        FILENAME        /oracle/CRM2/system1.dbf
        BIFILE          bifile.bbd
        LISTFILE        /oracle/filelist
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No
BBED> dump /v dba 1,1 offset 140 count 20
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  140 to  159  Dba:0x00400001
-------------------------------------------------------
 1d030000 e7969b2f 1c030000 00000000 l ....?./........
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 140 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  140 to  159  Dba:0x00800001
-------------------------------------------------------
 1d030000 36969b2f 1c030000 00000000 l ....6../........
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 1,1 offset 148 count 20;
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  148 to  167  Dba:0x00400001
-------------------------------------------------------
 1c030000 00000000 00000000 00000000 l ................
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 148 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  148 to  167  Dba:0x00800001
-------------------------------------------------------
 1c030000 00000000 00000000 00000000 l ................
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 1,1 offset 492 count 20 
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  492 to  511  Dba:0x00400001
-------------------------------------------------------
 f2979b2f 01000000 01000000 02000000 l ?./............
 10006960                            l ..i`
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 492 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  492 to  511  Dba:0x00800001
-------------------------------------------------------
 7e969b2f 01000000 03000000 41010000 l ~../........A...
 1000403d                            l ..@=
 <16 bytes per line>
BBED> modify /x f2979b2f
BBED-00209: invalid number (f2979b2f)

BBED> modify /x f297
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  492 to  511           Dba:0x00800001
------------------------------------------------------------------------
 f2979b2f 01000000 03000000 41010000 1000403d
 <32 bytes per line>
BBED> set offset +2
        OFFSET          494
BBED> modify /x 9b2f
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  494 to  513           Dba:0x00800001
------------------------------------------------------------------------
 9b2f0100 00000300 00004101 00001000 403d0200
 <32 bytes per line>
BBED> dump /v dba 2,1 offset 492 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  492 to  511  Dba:0x00800001
-------------------------------------------------------
 f2979b2f 01000000 03000000 41010000 l ?./........A...
 1000403d                            l ..@=
 <16 bytes per line>
BBED> dump /v dba 1,1 offset 484 count 20
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  484 to  503  Dba:0x00400001
-------------------------------------------------------
 d42f1480 00000000 f2979b2f 01000000 l ?......?./....
 01000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 484 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  484 to  503  Dba:0x00800001
-------------------------------------------------------
 d22f1480 00000000 f2979b2f 01000000 l ?......?./....
 03000000                            l ....
 <16 bytes per line>
BBED> modify /x d42f1480
BBED-00209: invalid number (d42f1480)

BBED> modify /x d42f
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  484 to  503           Dba:0x00800001
------------------------------------------------------------------------
 d42f1480 00000000 f2979b2f 01000000 03000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          486
BBED> modify /x 1480
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  486 to  505           Dba:0x00800001
------------------------------------------------------------------------
 14800000 0000f297 9b2f0100 00000300 00004101
 <32 bytes per line>
BBED> dump /v offset 484 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  484 to  503  Dba:0x00800001
-------------------------------------------------------
 d42f1480 00000000 f2979b2f 01000000 l ?......?./....
 03000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 1,1  offset 112 count 20
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  112 to  131  Dba:0x00400001
-------------------------------------------------------
 f8969b2f d32f1480 00000000 00000000 l ?./?..........
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 112 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  112 to  131  Dba:0x00800001
-------------------------------------------------------
 a9f29a2f b3e61380 00000000 00000000 l ?./虫..........
 00000000                            l ....
 <16 bytes per line>
BBED> modify /x f8969b2f
BBED-00209: invalid number (f8969b2f)

BBED> modify /x f896
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  112 to  131           Dba:0x00800001
------------------------------------------------------------------------
 f8969a2f b3e61380 00000000 00000000 00000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          114
BBED> modify /x 9b2f
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  114 to  133           Dba:0x00800001
------------------------------------------------------------------------
 9b2fb3e6 13800000 00000000 00000000 00000000
 <32 bytes per line>
BBED> dump /v offset 112
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  112 to  131  Dba:0x00800001
-------------------------------------------------------
 f8969b2f b3e61380 00000000 00000000 l ?./虫..........
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 1,1 offset 116 count 20
 File: /oracle/CRM2/system1.dbf (1)
 Block: 1       Offsets:  116 to  135  Dba:0x00400001
-------------------------------------------------------
 d32f1480 00000000 00000000 00000000 l ?..............
 00000000                            l ....
 <16 bytes per line>
BBED> dump /v dba 2,1 offset 116 count 20
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  116 to  135  Dba:0x00800001
-------------------------------------------------------
 b3e61380 00000000 00000000 00000000 l 虫..............
 00000000                            l ....
 <16 bytes per line>
BBED> modify /x d32f1480
BBED-00209: invalid number (d32f1480)

BBED> modify /x d32f
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  116 to  135           Dba:0x00800001
------------------------------------------------------------------------
 d32f1380 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> set offset +2
        OFFSET          118
BBED> modify /x 1480
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1                Offsets:  118 to  137           Dba:0x00800001
------------------------------------------------------------------------
 14800000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> dump /v offset 116
 File: /oracle/CRM2/zxb.dbf (2)
 Block: 1       Offsets:  116 to  135  Dba:0x00800001
-------------------------------------------------------
 d32f1480 00000000 00000000 00000000 l ?..............
 00000000                            l ....
 <16 bytes per line>
BBED> sum apply
Check value for File 2, Block 1:
current = 0x6d37, required = 0x6d37
BBED> exit
[oracle@oracle ~]$ exit
exit

SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     RECOVER
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
SQL> recover datafile 2
Media recovery complete.
SQL> select file#,name,status from v$datafile;
     FILE# NAME                                     STATUS
---------- ---------------------------------------- -------
         1 /oracle/CRM2/system1.dbf                 SYSTEM
         2 /oracle/CRM2/zxb.dbf                     OFFLINE
         3 /oracle/CRM2/CRM/sysaux01.dbf            ONLINE
         4 /oracle/CRM2/CRM/users01.dbf             ONLINE
         5 /oracle/CRM2/zxa.dbf                     ONLINE
         6 /oracle/CRM2/CRM/undotbs2.dbf            ONLINE
         7 /oracle/CRM2/zxc.dbf                     ONLINE
         8 /oracle/CRM2/CRM/zxbig1.dbf              ONLINE
8 rows selected.
SQL> alter database datafile 2 online;
Database altered.









本文转自 zhangxuwl 51CTO博客,原文链接:http://blog.51cto.com/jiujian/1052934,如需转载请自行联系原作者

目录
相关文章
|
26天前
|
存储 人工智能 Cloud Native
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
在9月20日2024云栖大会上,阿里云智能集团副总裁,数据库产品事业部负责人,ACM、CCF、IEEE会士(Fellow)李飞飞发表《从数据到智能:Data+AI驱动的云原生数据库》主题演讲。他表示,数据是生成式AI的核心资产,大模型时代的数据管理系统需具备多模处理和实时分析能力。阿里云瑶池将数据+AI全面融合,构建一站式多模数据管理平台,以数据驱动决策与创新,为用户提供像“搭积木”一样易用、好用、高可用的使用体验。
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
|
2月前
|
SQL 关系型数据库 MySQL
数据库导入SQL文件:全面解析与操作指南
在数据库管理中,将SQL文件导入数据库是一个常见且重要的操作。无论是迁移数据、恢复备份,还是测试和开发环境搭建,掌握如何正确导入SQL文件都至关重要。本文将详细介绍数据库导入SQL文件的全过程,包括准备工作、操作步骤以及常见问题解决方案,旨在为数据库管理员和开发者提供全面的操作指南。一、准备工作在导
294 0
|
28天前
|
SQL 关系型数据库 数据库
国产数据实战之docker部署MyWebSQL数据库管理工具
【10月更文挑战第23天】国产数据实战之docker部署MyWebSQL数据库管理工具
94 4
国产数据实战之docker部署MyWebSQL数据库管理工具
|
5天前
|
Oracle 关系型数据库 数据库
Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
一台Oracle数据库打开报错,报错信息: “system01.dbf需要更多的恢复来保持一致性,数据库无法打开”。管理员联系我们数据恢复中心寻求帮助,并提供了Oracle_Home目录的所有文件。用户方要求恢复zxfg用户下的数据。 由于数据库没有备份,无法通过备份去恢复数据库。
|
25天前
|
关系型数据库 分布式数据库 数据库
云栖大会|从数据到决策:AI时代数据库如何实现高效数据管理?
在2024云栖大会「海量数据的高效存储与管理」专场,阿里云瑶池讲师团携手AMD、FunPlus、太美医疗科技、中石化、平安科技以及小赢科技、迅雷集团的资深技术专家深入分享了阿里云在OLTP方向的最新技术进展和行业最佳实践。
|
2月前
|
SQL 关系型数据库 MySQL
|
2月前
|
人工智能 Cloud Native 容灾
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
云数据库“再进化”,OB Cloud如何打造云时代的数据底座?
|
2月前
|
SQL 存储 关系型数据库
数据储存数据库管理系统(DBMS)
【10月更文挑战第11天】
104 3
|
2月前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
34 2
|
2月前
|
SQL 存储 关系型数据库
添加数据到数据库的SQL语句详解与实践技巧
在数据库管理中,添加数据是一个基本操作,它涉及到向表中插入新的记录