你好,游客 登录 注册 搜索
背景:
阅读新闻

无归档情况下使用BBED处理ORA-01113错误

[日期:2015-05-19] 来源:Linux社区  作者:lanston [字体: ]

在丢失归档情况下,恢复时常会遇到ORA-01113错误,以下实验模拟表空间offline,然后在丢失归档文件的情况下使用BBED修改文件头信息,最后恢复数据文件;
数据库版本:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

数据文件信息:

SQL>  select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;
    FILE# NAME                                CHECKPOINT_CHANGE# CHECKPOINT_TIME    STATUS  BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
        1 /u01/app/oradata/sydb/system01.dbf            3161898 2015-04-13 20:46:37 SYSTEM        8192
        2 /u01/app/oradata/sydb/sysaux01.dbf            3161898 2015-04-13 20:46:37 ONLINE        8192
        3 /u01/app/oradata/sydb/undotbs01.dbf            3161898 2015-04-13 20:46:37 ONLINE        8192
        4 /u01/app/oradata/sydb/users01.dbf              3161898 2015-04-13 20:46:37 ONLINE        8192
        5 /disk2/oradata/sydb/tbs01.dbf                  3161898 2015-04-13 20:46:37 ONLINE        8192
        6 /disk2/oradata/sydb/tbs02.dbf                  3161898 2015-04-13 20:46:37 ONLINE        8192
        7 /disk2/oradata/sydb/tbs03.dbf                  3161898 2015-04-13 20:46:37 ONLINE        8192
        8 /tmp/tbs_tmp.dbf                              3161898 2015-04-13 20:46:37 ONLINE        8192

 

offline数据文件7并删除归档模拟ora-01113:

 

SQL> alter database datafile 7 offline;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'

SQL> select file#,change#,time from v$recover_file;
    FILE#    CHANGE# TIME
---------- ---------- ---------
        7    3161898 13-APR-15

 

使用BBED需要修改的内容有:
ub4 kscnbas @484    #最后检查的SCN
 ub4 kcvcptim @492    #检查时间
ub4 kcvfhcpc @140  #检查点发生次数
ub4 kcvfhccc @148  #未知,但值一直小于kcvfhcpc 1

 

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
    1  /u01/app/oradata/sydb/system01.dbf                              92160
    2  /u01/app/oradata/sydb/sysaux01.dbf                              71680
    3  /u01/app/oradata/sydb/undotbs01.dbf                              52480
    4  /u01/app/oradata/sydb/users01.dbf                                1600
    5  /disk2/oradata/sydb/tbs01.dbf                                  221696
    6  /disk2/oradata/sydb/tbs02.dbf                                    14592
    7  /disk2/oradata/sydb/tbs03.dbf                                    2560
    8  /tmp/tbs_tmp.dbf                                                  1280

 

 先确认好system表空间文件信息:

 

BBED> set dba 1,1 block 1
        DBA            0x00400001 (4194305 1,1)
        BLOCK#          1
BBED> print kcvfhckp
struct kcvfhckp, 36 bytes                  @484   
  struct kcvcpscn, 8 bytes                @484   
      ub4 kscnbas                          @484      0x003043a9
      ub2 kscnwrp                          @488      0x0000
  ub4 kcvcptim                            @492      0x34453174

BBED> dump/v dba 1,1 offset 484 count 30
 File: /u01/app/oradata/sydb/system01.dbf (1)
 Block: 1      Offsets:  484 to  513  Dba:0x00400001
-------------------------------------------------------
 a9433000 00000000 74314534 01000000 l ....t1E4....
 ab000000 2e080000 1000866b 0200    l .......k..

BBED> dump/v dba 1,1 offset 492 count 30
 File: /u01/app/oradata/sydb/system01.dbf (1)
 Block: 1      Offsets:  492 to  521  Dba:0x00400001
-------------------------------------------------------
 74314534 01000000 ab000000 2e080000 l t1E4........
 1000866b 02000000 00000000 0000    l ...k.......…

BBED> print kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000237

BBED> dump/v dba 1,1 offset 140 count 30
 File: /u01/app/oradata/sydb/system01.dbf (1)
 Block: 1      Offsets:  140 to  169  Dba:0x00400001
-------------------------------------------------------
 37020000 fe522034 36020000 00000000 l 7...6.......
 00000000 00000000 00000000 0000    l ...........…

BBED> print kcvfhccc
ub4 kcvfhccc                                @148      0x00000236

BBED> dump/v dba 1,1 offset 148 count 30
 File: /u01/app/oradata/sydb/system01.dbf (1)
 Block: 1      Offsets:  148 to  177  Dba:0x00400001
-------------------------------------------------------
 36020000 00000000 00000000 00000000 l 6...............
 00000000 00000000 00000000 0000    l ..............

 

再确认数据文件7信息:

 

BBED> print kcvfhckp
struct kcvfhckp, 36 bytes                  @484   
  struct kcvcpscn, 8 bytes                @484   
      ub4 kscnbas                          @484      0x00303f2a
      ub2 kscnwrp                          @488      0x0000
  ub4 kcvcptim                            @492      0x34452bad
  ub2 kcvcpthr                            @496      0x0001
BBED> dump/v dba 7,1 offset 484 count 30
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1      Offsets:  484 to  513  Dba:0x01c00001
-------------------------------------------------------
 2a3f3000 00000000 ad2b4534 01000000 l *?0.........
 a8000000 65000000 10000000 0200    l e.........
 <16 bytes per line>

BBED> dump/v dba 7,1 offset 492 count 30
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1      Offsets:  492 to  521  Dba:0x01c00001
-------------------------------------------------------
 ad2b4534 01000000 a8000000 65000000 l ....e...
 10000000 02000000 00000000 0000    l ...........…

BBED> print kcvfhcpc
ub4 kcvfhcpc                                @140      0x00000164
BBED> dump/v dba 7,1 offset 140
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1      Offsets:  140 to  169  Dba:0x01c00001
-------------------------------------------------------
 64010000 fe522034 63010000 00000000 l d...c.......
 00000000 00000000 00000000 0000    l ...........…

BBED> print kcvfhccc
ub4 kcvfhccc                                @148      0x00000163

BBED> dump/v dba 7,1 offset 148
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1      Offsets:  148 to  177  Dba:0x01c00001
-------------------------------------------------------
 63010000 00000000 00000000 00000000 l c...............
 00000000 00000000 00000000 0000    l ..............

 

00303f2a 刚好是v$recover_file 中查询到的CHANGE#值

SQL> select to_number('00303f2a','xxxxxxxxx') from dual;
TO_NUMBER('00303F2A','XXXXXXXXX')
---------------------------------
                          3161898

修改值:

 

BBED> modify /x a943 dba 7,1 offset 484
BBED> set offset +2
        OFFSET          486
BBED> modify /x 3000
BBED> dump /v dba 7,1 offset 484
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1      Offsets:  484 to  513  Dba:0x01c00001
-------------------------------------------------------
 a9433000 00000000 ad2b4534 01000000 l ........
 a8000000 65000000 10000000 0200    l e......…

BBED> modify /x 74314534 dba 7,1 offset 492
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1                Offsets:  492 to  521          Dba:0x01c00001
------------------------------------------------------------------------
 74314534 01000000 a8000000 65000000 10000000 02000000 00000000 0000

BBED> modify /x 37020000 dba 7,1 offset 140
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1                Offsets:  140 to  169          Dba:0x01c00001
------------------------------------------------------------------------
 37020000 fe522034 63010000 00000000 00000000 00000000 00000000 0000

BBED> modify /x 36020000 dba 7,1 offset 148
 File: /disk2/oradata/sydb/tbs03.dbf (7)
 Block: 1                Offsets:  148 to  177          Dba:0x01c00001
------------------------------------------------------------------------
 36020000 00000000 00000000 00000000 00000000 00000000 00000000 0000

BBED> sum apply
Check value for File 7, Block 1:
current = 0x7b4d, required = 0x7b4d

 

确认修改:

 

SQL> select * from v$recover_file;
    FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
        7 OFFLINE OFFLINE UNKNOWN ERROR                                            3163049 13-APR-15
SQL>  column name format a35
SQL>  select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;
    FILE# NAME                                CHECKPOINT_CHANGE# CHECKPOINT_TIME    STATUS  BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
        1 /u01/app/oradata/sydb/system01.dbf            3163049 2015-04-13 21:11:16 SYSTEM        8192
        2 /u01/app/oradata/sydb/sysaux01.dbf            3163049 2015-04-13 21:11:16 ONLINE        8192
        3 /u01/app/oradata/sydb/undotbs01.dbf            3163049 2015-04-13 21:11:16 ONLINE        8192
        4 /u01/app/oradata/sydb/users01.dbf              3163049 2015-04-13 21:11:16 ONLINE        8192
        5 /disk2/oradata/sydb/tbs01.dbf                  3163049 2015-04-13 21:11:16 ONLINE        8192
        6 /disk2/oradata/sydb/tbs02.dbf                  3163049 2015-04-13 21:11:16 ONLINE        8192
        7 /disk2/oradata/sydb/tbs03.dbf                  3161898 2015-04-13 20:46:37 RECOVER      8192
        8 /tmp/tbs_tmp.dbf                              3163049 2015-04-13 21:11:16 ONLINE        8192

 

控制文件的信息没有被修改也无法通过BBED修改,所以此时无法恢复data file 7;

 

SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'

SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 7 failed verification check
ORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf'
ORA-01207: file is more recent than control file - old control file

 

通过重建控制文件恢复数据文件7:

 

SQL> alter database backup controlfile to trace;
SQL> shutdown immediate
SQL>STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SYDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oradata/sydb/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oradata/sydb/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oradata/sydb/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oradata/sydb/system01.dbf',
  '/u01/app/oradata/sydb/sysaux01.dbf',
  '/u01/app/oradata/sydb/undotbs01.dbf',
  '/u01/app/oradata/sydb/users01.dbf',
  '/disk2/oradata/sydb/tbs01.dbf',
  '/disk2/oradata/sydb/tbs02.dbf',
  '/disk2/oradata/sydb/tbs03.dbf',
  '/tmp/tbs_tmp.dbf'
CHARACTER SET UTF8
;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/disk2/oradata/sydb/tbs03.dbf''
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.

SQL>  column name format a35
SQL>  select file#,name, CHECKPOINT_CHANGE#,to_char(CHECKPOINT_TIME,'yyyy-mm-dd hh24:mi:ss')checkpoint_time,status,BLOCK_SIZE from v$datafile;

    FILE# NAME                                CHECKPOINT_CHANGE# CHECKPOINT_TIME    STATUS  BLOCK_SIZE
---------- ----------------------------------- ------------------ ------------------- ------- ----------
        1 /u01/app/oradata/sydb/system01.dbf            3183058 2015-04-13 22:11:12 SYSTEM        8192
        2 /u01/app/oradata/sydb/sysaux01.dbf            3183058 2015-04-13 22:11:12 ONLINE        8192
        3 /u01/app/oradata/sydb/undotbs01.dbf            3183058 2015-04-13 22:11:12 ONLINE        8192
        4 /u01/app/oradata/sydb/users01.dbf              3183058 2015-04-13 22:11:12 ONLINE        8192
        5 /disk2/oradata/sydb/tbs01.dbf                  3183058 2015-04-13 22:11:12 ONLINE        8192
        6 /disk2/oradata/sydb/tbs02.dbf                  3183058 2015-04-13 22:11:12 ONLINE        8192
        7 /disk2/oradata/sydb/tbs03.dbf                  3183058 2015-04-13 22:11:12 ONLINE        8192
        8 /tmp/tbs_tmp.dbf                              3183058 2015-04-13 22:11:12 ONLINE        8192

 

注:不同情况,有可能还是无法打开数据库,比如报(ORA-01113: file 1 needs media recovery),尝试使用NORESETLOGS方式重建控制文件,然后在执行Media Recovery 。

--Then end

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-05/117690.htm

linux
相关资讯       ORA-01113 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款