损坏的主文件表_普通数据文件损坏恢复实验记录(有备份的情况)

最新推荐文章于 2020-12-06 03:18:54 发布

weixin_39882317

最新推荐文章于 2020-12-06 03:18:54 发布

阅读量632

点赞数

文章标签：损坏的主文件表

本文链接：https://blog.csdn.net/weixin_39882317/article/details/111391001

版权

本文详述了在有备份情况下，如何恢复Oracle数据库中普通数据文件的损坏，包括非数据文件头部和数据文件头部损坏的场景。通过RMAN备份、BBED工具模拟数据块损坏，并展示了恢复过程及不同环境下的差异。

摘要由CSDN通过智能技术生成

普通数据文件损坏恢复实验记录(有备份的情况)

前些天写了《关键数据文件损坏恢复实验记录》，今天就看一下普通数据文件损坏恢复的情况。

普通数据文件包括：非关键数据文件、非临时表空间和只读表空间的数据文件，普通文件的损坏不会导致数据库实例崩溃、也没有数据库不恢复就无法启动的说法，顶多会影响用户数据不可访问。

我们可以在OPEN状态下进行恢复普通数据文件。

sys@MAA> select name from v$datafile where file# not in (1,3);

NAME

----------------------------------------------------------------------------------------------------

+MSDATA/maa/datafile/sysaux.261.792009871

+MSDATA/maa/datafile/users.264.792009897

+MSDATA/maa/datafile/l.266.798569345

+MSDATA/maa/datafile/l.267.798570165

+MSDATA/maa/datafile/l.268.798572257

本文讨论有备份情况下的普通数据文件恢复

RMAN> backup tablespace luocs;

Starting backup at 21-DEC-2012 07:53:26

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=323 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00035 name=/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

input datafile file number=00034 name=/u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

channel ORA_DISK_1: starting piece 1 at 21-DEC-2012 07:53:31

channel ORA_DISK_1: finished piece 1 at 21-DEC-2012 07:53:35

piece handle=/u01/app/oracle/product/11.2.0.1/dbhome_1/dbs/01ntdb3r_1_1 tag=TAG20121221T075331 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 21-DEC-2012 07:53:35

操作环境：

• OS : CentOS Linux 5.6 64Bit

• DB Type : Single Instance Database

• DB Version : 11.2.0.1

下面我构造下面几个场景：

1）非数据文件头部损坏的场景

2）数据文件头部损坏的场景

场景1：制造数据坏块儿，非文件头部损坏

sys@SA7N67B11G> col FILE_NAME for a65

sys@SA7N67B11G> select file_id, file_name from dba_data_files where tablespace_name='LUOCS';

FILE_ID FILE_NAME

---------- -----------------------------------------------------------------

34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

-- 我的系统里有LUOCS用户，其默认表空间为LUOCS

luocs用户创建表，灌入数据

luocs@SA7N67B11G> create table ltb(id number, name varchar2(200));

luocs@SA7N67B11G> insert into ltb select object_id, object_name from all_objects;

luocs@SA7N67B11G> commit;

查询一条记录

luocs@SA7N67B11G> select rowid, a.* from ltb a where name='LTB';

ROWID ID NAME

------------------ ---------- ------------------------------

AAAW+0AAjAAAAIMAC9 94132 LTB

利用下面脚本，我们创建一个通过ROWID查找其记录的数据存储信息

create or replace function get_rowid(l_rowid in varchar2) return varchar2 is

ls_my_rowid varchar2(200);

rowid_type number;

object_number number;

relative_fno number;

block_number number;

row_number number;

begin

dbms_rowid.rowid_info(l_rowid,

rowid_type,

object_number,

relative_fno,

block_number,

row_number);

ls_my_rowid := 'Row_id type is :' || to_char(rowid_type) || chr(10) ||

'Object# is :' || to_char(object_number) || chr(10) ||

'Relative_fno is :' || to_char(relative_fno) || chr(10) ||

'Block number is :' || to_char(block_number) || chr(10) ||

'Row number is :' || to_char(row_number);

return ls_my_rowid;

end;

luocs@SA7N67B11G> select get_rowid('AAAW+0AAjAAAAIMAC9') ROW_ID from dual;

ROW_ID

----------------------------------------------------------------------------------------------------

Row_id type is :1

Object# is :94132

Relative_fno is :35

Block number is :524

Row number is :189

-- ID为94132的记录在35号文件524号块儿

我们通过BBED工具破坏524号块儿

[oracle@localhost ~]$ cat bbed.par

34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

[oracle@localhost lib]$ bbed listfile=/home/oracle/bbed.par blocksize=8192 mode=edit

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Fri Dec 21 08:02:33 2012

************* !!! For Oracle Internal Use only !!! ***************

BBED> info

File# Name Size(blks)

----- ---- ----------

34 /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf 0

35 /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf 0

BBED> show

FILE# 34

BLOCK# 1

OFFSET 0

DBA 0x08800001 (142606337 34,1)

FILENAME /u01/app/oracle/oradata/SA7N67B11G/luocs01.dbf

BIFILE bifile.bbd

LISTFILE /home/oracle/bbed.par

BLOCKSIZE 8192

MODE Edit

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

目前是没有坏块儿的

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs01.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

我强制copy块儿

BBED> copy file 34 block 235 to file 35 block 524

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y

File: /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf (35)

Block: 524 Offsets: 0 to 511 Dba:0x08c0020c

------------------------------------------------------------------------

06a20000 eb008008 0df62301 00000104 aa060000 01000000 d8640100 0df62301

00000000 03003201 e0008008 ffff0000 00000000 00000000 00000000 00800000

c0d92301 0a001a00 c52f0100 bd04c000 093b1b00 00a00000 c4f32301 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00015100

0000b400 1104cc1e cc1e0000 51000100 02000300 04000500 06000700 08000900

0a000b00 0c000d00 0e000f00 10001100 12001300 14001500 16001700 18001900

1a001b00 1c001d00 1e001f00 20002100 22002300 24002500 26002700 28002900

2a002b00 2c002d00 2e002f00 30003100 32003300 34003500 36003700 38003900

3a003b00 3c003d00 3e003f00 40004100 42004300 44004500 46004700 48004900

4a004b00 4c004d00 4e004f00 5000ffff 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply

Check value for File 35, Block 524:

current = 0x06aa, required = 0x06aa

这时候检查到有一个数据坏块儿了

BBED> verify

DBVERIFY - Verification starting

FILE = /u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf

BLOCK = 524

Block 524 is corrupt

Corrupt block relative dba: 0x0880020c (file 0, block 524)

Bad header found during verification

Data in bad block:

type: 6 format: 2 rdba: 0x088000eb

last change scn: 0x0000.0123f60d seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xf60d0601

check value in block header: 0x6aa

computed block checksum: 0x0

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 : 1

Total Blocks Influx : 0

Message 531 not found; product=RDBMS; facility=BBED

为了看到效果，我们需要刷新高速缓冲区

sys@SA7N67B11G> alter system flush buffer_cache;

System altered.

我们查该块儿上的记录的时候会报错

luocs@SA7N67B11G> select rowid, a.* from ltb a where name='LTB';

select rowid, a.* from ltb a where name='LTB'

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 35, block # 524)

ORA-01110: data file 35: '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf'

告警日志输出：

Hex dump of (file 35, block 524) in trace file /u01/app/oracle/diag/rdbms/SA7N67B11G/SA7N67B11G/trace/luocs11g_ora_18320.trc

Corrupt block relative dba: 0x08c0020c (file 35, block 524)

Bad header found during multiblock buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x0880020c

last change scn: 0x0000.0123f60d seq: 0x1 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0xf60d0601

check value in block header: 0xc245

computed block checksum: 0x0

Reading datafile '/u01/app/oracle/oradata/SA7N67B11G/luocs02.dbf' for corruption at rdba: 0x08c0020c (fi

最低0.47元/天解锁文章

weixin_39882317

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
损坏的主文件表_普通数据文件损坏恢复实验记录(有备份的情况)

普通数据文件损坏恢复实验记录(有备份的情况)前些天写了《关键数据文件损坏恢复实验记录》，今天就看一下普通数据文件损坏恢复的情况。普通数据文件包括：非关键数据文件、非临时表空间和只读表空间的数据文件，普通文件的损坏不会导致数据库实例崩溃、也没有数据库不恢复就无法启动的说法，顶多会影响用户数据不可访问。我们可以在OPEN状态下进行恢复普通数据文件。sys@MAA> select name fro...
复制链接

扫一扫