ITPub博客

首页 > 数据库 > Oracle > [20160329]bbed修复offline的数据文件.txt

[20160329]bbed修复offline的数据文件.txt

原创 Oracle 作者:lfree 时间:2016-03-29 15:18:25 0 删除 编辑

[20160329]bbed修复offline的数据文件.txt

--测试数据库,不小心将一个数据文件offline了,archivelog也删除了(主要磁盘空间紧张,做了一次整理)。
--自己测试修复看看,顺便做一个记录。

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                             TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- -------------------------------- ----------------
    1        13227498080 2016-03-29 11:44:35                7       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/system01.dbf   SYSTEM
    2        13227498080 2016-03-29 11:44:35             1834       13227286650 ONLINE               995 NO  /mnt/ramdisk/book/sysaux01.dbf   SYSAUX
    3        13227498080 2016-03-29 11:44:35           923328       13227286650 ONLINE               915 NO  /mnt/ramdisk/book/undotbs01.dbf  UNDOTBS1
    4        13227498080 2016-03-29 11:44:35            16143       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/users01.dbf    USERS
    5        13227498080 2016-03-29 11:44:35           952916       13227286650 ONLINE               912 NO  /mnt/ramdisk/book/example01.dbf  EXAMPLE
    6        13227498080 2016-03-29 11:44:35          1314508       13227286650 ONLINE               928 NO  /mnt/ramdisk/book/sugar01.dbf    SUGAR
    7        13227287392 2016-03-25 17:13:07      13227207527       13227286650 OFFLINE               20 YES /mnt/ramdisk/book/tea01.dbf      TEA
7 rows selected.

--我估计CHECKPOINT_CHANGE# 修改为13227498080 就ok了。测试看看。安全起见做一个备份:
$ cp tea01.dbf_ORG tea01.dbf

SYS@book> @ &r/10to16 13227287392

10 to 16 HEX   REVERSE16
-------------- -----------------------------------
0000314686360 0x60636814-03000000

SYS@book> @ &r/10to16 13227498080

10 to 16 HEX   REVERSE16
-------------- -----------------------------------
00003146b9a60 0x609a6b14-03000000

SCOTT@book> @ &r/bbvi 7 1
BVI_COMMAND
--------------------------------------------------
bvi -b 8192 -s 8192 /mnt/ramdisk/book/tea01.dbf

$ cp tea01.dbf tea01.dbf_ORG

2.使用bvi打开,修改0x60636814=>0x609a6b14

BBED> set dba 7,1
        DBA             0x01c00001 (29360129 7,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x14686360

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x146b9a60

--换1种修改模式算是学习。使用bbed的assign命令。
BBED> assign file 7 block 1 offset 484= file 1 block 1 offset 484;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 pad                                     @484      0x60

BBED> set dba 1,1
        DBA             0x00400001 (4194305 1,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x146b9a60

BBED> set dba 7,1
        DBA             0x01c00001 (29360129 7,1)

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas
ub4 kscnbas                                 @484      0x14686360

--仔细检查发现没改,仅仅修改最后1个字节,实际上这里正好一样(看不出来)。
BBED> help assign
ASSIGN[/x|d|u|o] <target spec>=<source spec>
<target spec> : [ DBA | FILE | FILENAME | BLOCK | OFFSET | symbol | *symbol ]
<source spec> : [ value | <target spec options> ]

BBED> assign dba 7,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas = dba 1,1 kcvfh.kcvfhckp.kcvcpscn.kscnbas;
ub4 kscnbas                                 @484      0x146b9a60

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 1,1
ub4 kscnbas                                 @484      0x146b9a60

BBED> p kcvfh.kcvfhckp.kcvcpscn.kscnbas dba 7,1
ub4 kscnbas                                 @484      0x146b9a60

--ok现在一样了。
BBED> sum apply dba 7,1
Check value for File 7, Block 1:
current = 0xdedb, required = 0xdedb

BBED> verify  dba 7,1
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/tea01.dbf
BLOCK = 1


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

3.重启到mount状态:
SYS@book> SELECT file#, CHECKPOINT_CHANGE#, CHECKPOINT_TIME,CREATION_CHANGE#  , RESETLOGS_CHANGE#,status, CHECKPOINT_COUNT,fuzzy,name,tablespace_name  FROM v$datafile_header;
FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME     CREATION_CHANGE# RESETLOGS_CHANGE# STATUS  CHECKPOINT_COUNT FUZ NAME                            TABLESPACE_NAME
----- ------------------ ------------------- ---------------- ----------------- ------- ---------------- --- ------------------------------- ----------------
    1        13227498080 2016-03-29 11:44:35                7       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/system01.dbf  SYSTEM
    2        13227498080 2016-03-29 11:44:35             1834       13227286650 ONLINE               995 NO  /mnt/ramdisk/book/sysaux01.dbf  SYSAUX
    3        13227498080 2016-03-29 11:44:35           923328       13227286650 ONLINE               915 NO  /mnt/ramdisk/book/undotbs01.dbf UNDOTBS1
    4        13227498080 2016-03-29 11:44:35            16143       13227286650 ONLINE               999 NO  /mnt/ramdisk/book/users01.dbf   USERS
    5        13227498080 2016-03-29 11:44:35           952916       13227286650 ONLINE               912 NO  /mnt/ramdisk/book/example01.dbf EXAMPLE
    6        13227498080 2016-03-29 11:44:35          1314508       13227286650 ONLINE               928 NO  /mnt/ramdisk/book/sugar01.dbf   SUGAR
    7        13227498080 2016-03-25 17:13:07      13227207527       13227286650 OFFLINE               20 YES /mnt/ramdisk/book/tea01.dbf     TEA
7 rows selected.
--现在一致了,recover看看。

SYS@book> recover datafile 7;
Media recovery complete.

alter database open read only ;

SYS@book> select owner,segment_name from dba_segments where tablespace_name='TEA';
OWNER  SEGMENT_NAME
------ --------------------
SCOTT  EMPX

SYS@book> select * from scott.empx ;
select * from scott.empx
                    *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/mnt/ramdisk/book/tea01.dbf'

SYS@book> alter database datafile 7 online ;
Database altered.

SYS@book> select * from scott.empx where rownum<=1;
       EMPNO ENAME      JOB                MGR HIREDATE                     SAL         COMM       DEPTNO
------------ ---------- --------- ------------ ------------------- ------------ ------------ ------------
        7369 YYYY       CLERK             7902 1980-12-17 00:00:00          800                        20

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2071731/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2903
  • 访问量
    6678811