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,如需转载请自行联系原作者

目录
相关文章
|
存储 JSON 关系型数据库
【干货满满】解密 API 数据解析:从 JSON 到数据库存储的完整流程
本文详解电商API开发中JSON数据解析与数据库存储的全流程,涵盖数据提取、清洗、转换及优化策略,结合Python实战代码与主流数据库方案,助开发者构建高效、可靠的数据处理管道。
|
存储 缓存 数据库
数据库数据删除策略:硬删除vs软删除的最佳实践指南
在项目开发中,“删除”操作常见但方式多样,主要分为硬删除与软删除。硬删除直接从数据库移除数据,操作简单、高效,但不可恢复;适用于临时或敏感数据。软删除通过标记字段保留数据,支持恢复和审计,但增加查询复杂度与数据量;适合需追踪历史或可恢复的场景。两者各有优劣,实际开发中常结合使用以满足不同需求。
1603 4
|
存储 数据管理 数据库
数据字典是什么?和数据库、数据仓库有什么关系?
在数据处理中,你是否常困惑于字段含义、指标计算或数据来源?数据字典正是解答这些问题的关键工具,它清晰定义数据的名称、类型、来源、计算方式等,服务于开发者、分析师和数据管理者。本文详解数据字典的定义、组成及其与数据库、数据仓库的关系,助你夯实数据基础。
数据字典是什么?和数据库、数据仓库有什么关系?
|
数据采集 关系型数据库 MySQL
python爬取数据存入数据库
Python爬虫结合Scrapy与SQLAlchemy,实现高效数据采集并存入MySQL/PostgreSQL/SQLite。通过ORM映射、连接池优化与批量提交,支持百万级数据高速写入,具备良好的可扩展性与稳定性。
|
人工智能 Java 关系型数据库
使用数据连接池进行数据库操作
使用数据连接池进行数据库操作
296 11
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
961 174
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL数据库的WAL日志与数据写入的过程
PostgreSQL中的WAL(预写日志)是保证数据完整性的关键技术。在数据修改前,系统会先将日志写入WAL,确保宕机时可通过日志恢复数据。它减少了磁盘I/O,提升了性能,并支持手动切换日志文件。WAL文件默认存储在pg_wal目录下,采用16进制命名规则。此外,PostgreSQL提供pg_waldump工具解析日志内容。
1188 0
|
存储 SQL Java
数据存储使用文件还是数据库,哪个更合适?
数据库和文件系统各有优劣:数据库读写性能较低、结构 rigid,但具备计算能力和数据一致性保障;文件系统灵活易管理、读写高效,但缺乏计算能力且无法保证一致性。针对仅需高效存储与灵活管理的场景,文件系统更优,但其计算短板可通过开源工具 SPL(Structured Process Language)弥补。SPL 提供独立计算语法及高性能文件格式(如集文件、组表),支持复杂计算与多源混合查询,甚至可替代数据仓库。此外,SPL 易集成、支持热切换,大幅提升开发运维效率,是后数据库时代文件存储的理想补充方案。

热门文章

最新文章