Oracle数据库坏块处理

最新推荐文章于 2024-08-18 10:54:35 发布

君子不怨天

最新推荐文章于 2024-08-18 10:54:35 发布

阅读量542

点赞数 3

分类专栏： Oracle 文章标签： oracle 数据库 rman

本文链接：https://blog.csdn.net/ly7472712/article/details/115326262

版权

Oracle 专栏收录该内容

59 篇文章 13 订阅

订阅专栏

本文详细介绍了如何处理Oracle数据库中的坏块问题，包括先进行数据库备份，然后模拟坏块，最后使用RMAN恢复坏块的全过程。通过查询V$DATABASE_BLOCK_CORRUPTION和DBA_EXTENTS等视图定位问题，成功修复了数据表中的损坏块。

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

Oracle坏块处理实验

数据库坏块测试：
1、备份数据
2、制造坏块
3、恢复坏块

一、rman备份数据

1、数据库开启归档模式：

SQL> startup mount							#将数据库加载至mount状态
ORACLE instance started.

Total System Global Area  2555445248 bytes
Fixed Size		   			 2255952 bytes
Variable Size		 	  1056965552 bytes
Database Buffers	 	  1476395008 bytes
Redo Buffers		   		19828736 bytes
Database mounted.

SQL> archive log list						#查看数据库归档日志状态
Database log mode	       No Archive Mode	#非归档模式
Automatic archival	       Disabled			#自动归档未开启
Archive destination	       /u01/app/oracle/product/11.2.0.4/dbs/arch
Oldest online log sequence     144
Current log sequence	       151

SQL> alter database archivelog;				#修改数据库为归档模式

Database altered.

SQL> alter system archive log start;		#归档日志启动

System altered.

SQL> archive log list;						#再次查看归档日志状态
Database log mode	       Archive Mode		#数据库处于归档模式
Automatic archival	       Enabled			#自动归档已开启
Archive destination	       /u01/app/oracle/product/11.2.0.4/dbs/arch
Oldest online log sequence     144
Next log sequence to archive   151
Current log sequence	       151
SQL> alter database open;					#修改数据库为open状态

Database altered.

SQL>

2、使用rman备份数据库

#rman连接Oracle数据库
[orcl][oracle@shuaige ~]$ rman target /			#启动rman命令窗口

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 26 08:55:27 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1582977425)

RMAN>  list backup of database;					#检查是否有过期或无效的备份

using target database control file instead of recovery catalog
specification does not match any backup in the repository


#开始备份数据库
RMAN> run {
shutdown immediate;								#关闭数据库
startup mount;									#将数据库加载为mount状态
allocate channel d1 type disk;					#分配通道
backup full database							#数据库全库备份
include current controlfile						#控制文件备份
format '/u01/app/oracle/product/11.2.0.4/dbs/%d_%s_%p_%t_%T';#指定备份路径及格式
release channel d1;								#释放通道
alter database open;							#开启数据库
2> 3> 4> 5> 6> 7> 8> 9> 10> }

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2555445248 bytes

Fixed Size                     2255952 bytes
Variable Size               1056965552 bytes
Database Buffers            1476395008 bytes
Redo Buffers                  19828736 bytes

allocated channel: d1
channel d1: SID=1145 device type=DISK

Starting backup at 2021-03-26 09:18:29
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/undotbs02.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/qianyitbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel d1: starting piece 1 at 2021-03-26 09:18:29
channel d1: finished piece 1 at 2021-03-26 09:19:54
piece handle=/u01/app/oracle/product/11.2.0.4/dbs/ORCL_1_1_1068196709_20210326 tag=TAG20210326T091829 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:25
channel d1: starting full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 2021-03-26 09:19:57
channel d1: finished piece 1 at 2021-03-26 09:19:58
piece handle=/u01/app/oracle/product/11.2.0.4/dbs/ORCL_2_1_1068196794_20210326 tag=TAG20210326T091829 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-26 09:19:58

released channel: d1

database opened

RMAN> list backup;								#查询备份结果


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    1.10G      DISK        00:01:16     2021-03-26 09:19:45
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20210326T091829
        Piece Name: /u01/app/oracle/product/11.2.0.4/dbs/ORCL_1_1_1068196709_20210326
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/system01.dbf
  2       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/sysaux01.dbf
  3       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/undotbs01.dbf
  4       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/users01.dbf
  5       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/undotbs02.dbf
  6       Full 1425101    2021-03-26 09:18:19 /u01/app/oracle/oradata/orcl/qianyitbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
2       Full    9.36M      DISK        00:00:04     2021-03-26 09:19:58
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20210326T091829
        Piece Name: /u01/app/oracle/product/11.2.0.4/dbs/ORCL_2_1_1068196794_20210326
  SPFILE Included: Modification time: 2021-03-26 09:18:30
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1425101      Ckp time: 2021-03-26 09:18:19

RMAN>

其中脚本format的变量说明：

%d ：数据库ID
%s ：备份集号
%p ：备份集的片号
%t ：备份时间
%T ：年月日格式
%u ：产生唯一的文件名称；
%c ：第几份备份；

二、制造数据库坏块

1、查询测试表

SQL> SELECT * FROM QIANYI.T_STU;

     STUID STUNAME
---------- --------------------
	 1 XIAOHONG
	 2 XIAOMING

SQL>

dbms_rowid包可以通过表中各条记录的唯一rowid号

#获取表的ROWID_BLOCK_NUMBER和rowid_relative_fno
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) 
from qianyi.t_stu;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
				 135					6
				 135					6

SQL> 
#通过dba_segments 查询表的块信息
SQL>  select owner,segment_name,header_file,header_block,blocks from dba_segments where owner='QIANYI' and segment_name='T_STU';

OWNER			       SEGMENT_NAME						HEADER_FILE HEADER_BLOCK  BLOCKS
---------------------- -------------------------------- ----------- ------------ ---------
QIANYI			       T_STU								6	      130	   		8

SQL>

2、手动制造坏块

[orcl][oracle@shuaige ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Mar 26 23:53:29 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1582977425)

RMAN> recover datafile 6 block 130 clear;

Starting recover at 2021-03-26 23:53:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1155 device type=DISK
Finished recover at 2021-03-26 23:53:36

RMAN>

3、刷新Oracle缓存：

SQL> alter system flush buffer_cache;

System altered.

SQL>

4、再次查询该表发现数据块已损坏

SQL> select * from qianyi.t_stu;
select * from qianyi.t_stu
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 130)
ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/qianyitbs01.dbf'


SQL>

1、修复数据库坏块

1、首先说明一下在不知道是那张表的情况下，根据坏块查询问题表

#v$database_block_corruption视图可以查询哪些数据文件坏块
SQL>  select * from v$database_block_corruption;

    FILE#     BLOCK#	 BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- --------- ------------------ ---------
	 6	  		130	       	1	   	 	   1424224 CHECKSUM

SQL> 

#根据file_id和block_id查询坏块数据那一张段（表）
SQL> select tablespace_name,segment_type,owner,segment_name,BLOCKS,BLOCK_ID 
from dba_extents 
where file_id=6 and BLOCK_ID=136;

TABLESPACE_NAME 	   SEGMENT_TYPE	  OWNER 	SEGMENT_NAME		BLOCKS   BLOCK_ID
-------------------- -------------- ----------- ------------------- ------- ----------
QINYI			       INDEX		  QIANYI	SYS_C0011165		8		  136

SQL>

2、使用rman恢复恢复损坏的数据块

RMAN> blockrecover datafile 6 block 130;

Starting recover at 2021-03-27 10:10:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1137 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0.4/dbs/ORCL_1_1_1068196709_20210326
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0.4/dbs/ORCL_1_1_1068196709_20210326 tag=TAG20210326T091829
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished recover at 2021-03-27 10:10:13

RMAN>

3、查询验证：

SQL> select * from qianyi.t_stu;

     STUID STUNAME
---------- --------------------
	 1 XIAOHONG
	 2 XIAOMING
#数据库qianyi.t_stu表坏块以修复
SQL>

至此结束，后续会继续补充相关内容
有什么问题欢迎留言，谢谢。

君子不怨天

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录