文档课题：主库数据文件存在坏块，运用备库rman备份进行恢复.
数据库：oracle 11.2.0.4
主机名：主库 leo-oel150 备库：leo-oel151
1、模拟坏块
1.1、建测试表
--主库建测试表.
HR@orcl150> create table employees01 as select * from employees;
Table created.
HR@orcl150> insert into employees01 select * from employees01;
20 rows created.
HR@orcl150> select rowid,employee_id,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) relative,DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'HR','EMPLOYEES01') absolute,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) blocknum from HR.EMPLOYEES01;
ROWID EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
------------------ ----------- ---------- ---------- ----------
AAAVYNAAEAAAAGTAAA 100 4 4 403
AAAVYNAAEAAAAGTAAB 101 4 4 403
AAAVYNAAEAAAAGTAAC 102 4 4 403
AAAVYNAAEAAAAGTAAD 103 4 4 403
AAAVYNAAEAAAAGTAAE 104 4 4 403
AAAVYNAAEAAAAGTAAF 107 4 4 403
AAAVYNAAEAAAAGTAAG 124 4 4 403
AAAVYNAAEAAAAGTAAH 141 4 4 403
AAAVYNAAEAAAAGTAAI 142 4 4 403
AAAVYNAAEAAAAGTAAJ 143 4 4 403
AAAVYNAAEAAAAGTAAK 144 4 4 403
ROWID EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
------------------ ----------- ---------- ---------- ----------
AAAVYNAAEAAAAGTAAL 149 4 4 403
AAAVYNAAEAAAAGTAAM 174 4 4 403
AAAVYNAAEAAAAGTAAN 176 4 4 403
AAAVYNAAEAAAAGTAAO 178 4 4 403
AAAVYNAAEAAAAGTAAP 200 4 4 403
AAAVYNAAEAAAAGTAAQ 201 4 4 403
AAAVYNAAEAAAAGTAAR 202 4 4 403
AAAVYNAAEAAAAGTAAS 205 4 4 403
AAAVYNAAEAAAAGTAAT 206 4 4 403
AAAVYNAAEAAAAGWAAA 100 4 4 406
AAAVYNAAEAAAAGWAAB 101 4 4 406
ROWID EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
------------------ ----------- ---------- ---------- ----------
AAAVYNAAEAAAAGWAAC 102 4 4 406
AAAVYNAAEAAAAGWAAD 103 4 4 406
AAAVYNAAEAAAAGWAAE 104 4 4 406
AAAVYNAAEAAAAGWAAF 107 4 4 406
AAAVYNAAEAAAAGWAAG 124 4 4 406
AAAVYNAAEAAAAGWAAH 141 4 4 406
AAAVYNAAEAAAAGWAAI 142 4 4 406
AAAVYNAAEAAAAGWAAJ 143 4 4 406
AAAVYNAAEAAAAGWAAK 144 4 4 406
AAAVYNAAEAAAAGWAAL 149 4 4 406
AAAVYNAAEAAAAGWAAM 174 4 4 406
ROWID EMPLOYEE_ID RELATIVE ABSOLUTE BLOCKNUM
------------------ ----------- ---------- ---------- ----------
AAAVYNAAEAAAAGWAAN 176 4 4 406
AAAVYNAAEAAAAGWAAO 178 4 4 406
AAAVYNAAEAAAAGWAAP 200 4 4 406
AAAVYNAAEAAAAGWAAQ 201 4 4 406
AAAVYNAAEAAAAGWAAR 202 4 4 406
AAAVYNAAEAAAAGWAAS 205 4 4 406
AAAVYNAAEAAAAGWAAT 206 4 4 406
40 rows selected.
1.2、损坏数据块
--主库损坏403号块.
[oracle@leo-oel150 admin]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl150/users01.dbf bs=8192 count=1 seek=403
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000332917 s, 24.6 MB/s
--确认数据块被损坏
SYS@orcl150> alter system flush buffer_cache;
System altered.
SYS@orcl150> select * from hr.employees01;
select * from hr.employees01
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 403)
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl150/users01.dbf'
[oracle@leo-oel150 admin]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 3 18:04:38 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL150 (DBID=4073973096)
RMAN> backup validate check logical tablespace users;
Starting backup at 03-FEB-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=199 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/03/2023 18:04:53
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl150/users01.dbf'
ORA-01200: actual file size of 640 is smaller than correct size of 640 blocks
--此处查试图v$database_block_corruption无返回值.
SYS@orcl150> select * from v$database_block_corruption;
no rows selected
SYS@orcl150> set line 200
SYS@orcl150> select db_unique_name,open_mode,log_mode,database_role,protection_mode from v$database;
DB_UNIQUE_NAME OPEN_MODE LOG_MODE DATABASE_ROLE PROTECTION_MODE
------------------------------ -------------------- ------------ ---------------- --------------------
orcl150 READ WRITE ARCHIVELOG PRIMARY MAXIMUM PERFORMANCE
2、损坏块的恢复
--采用备库的rman备份进行恢复.
2.1、备库备份
--在备库对损坏的数据文件进行备份.
RMAN> backup datafile 4 format '/home/oracle/rmanbak/users-%U.dbf';
Starting backup at 03-FEB-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl151/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-FEB-23
channel ORA_DISK_1: finished piece 1 at 03-FEB-23
piece handle=/home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf tag=TAG20230203T204533 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-FEB-23
--将备份集传输到主库.
[oracle@leo-oel151 rmanbak]$ scp users-0h1jjbrd_1_1.dbf oracle@192.168.133.150:/home/oracle/rmanbak
oracle@192.168.133.150's password:
users-0h1jjbrd_1_1.dbf
2.2、恢复损坏块
--在主库上进行损坏块的恢复.
[oracle@leo-oel150 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 3 15:46:53 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL150 (DBID=4073973096)
RMAN> catalog start with '/home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf';
searching for all files that match the pattern /home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 2.50M DISK 00:00:00 03-FEB-23
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20230203T204533
Piece Name: /home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 1254955 03-FEB-23 /u01/app/oracle/oradata/orcl150/users01.dbf
--将主库启动到mount阶段.
[oracle@leo-oel150 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 22:27:50 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl150> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl150> startup mount;
ORACLE instance started.
Total System Global Area 1586708480 bytes
Fixed Size 2253624 bytes
Variable Size 989859016 bytes
Database Buffers 587202560 bytes
Redo Buffers 7393280 bytes
Database mounted.
SYS@orcl150> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@leo-oel150 admin]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 3 22:28:24 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL150 (DBID=4073973096, not open)
RMAN> restore datafile 4;
Starting restore at 03-FEB-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl150/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf
channel ORA_DISK_1: piece handle=/home/oracle/rmanbak/users-0h1jjbrd_1_1.dbf tag=TAG20230203T204533
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-FEB-23
RMAN> recover datafile 4;
Starting recover at 03-FEB-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 03-FEB-23
RMAN> alter database open;
database opened
2.3、数据校验
恢复后进行相关检验.
[oracle@leo-oel150 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 22:29:52 2023
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl150> select count(*) from hr.employees01;
COUNT(*)
----------
40
说明：主库数据文件4成功恢复.
主库数据文件存在坏块，运用备库rman备份进行恢复
原创Liujun_Deng 博主文章分类：Oracle ©著作权
文章标签 主库坏块 运用备库rman备份恢复 文章分类 Oracle 数据库
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
运用主库数据文件rman备份对备库单个坏块的恢复—blockrecover
运用主库数据文件rman备份对备库单个坏块的恢复—blockrecover
oracle blockrecover 索引块损坏 -
运用备库数据文件rman备份对另一个备库单个坏块的恢复
运用备库数据文件rman备份对另一个备库单个坏块的恢复
oracle 坏块修复 ora-19566 -
oracle数据库坏块恢复—存在rman备份
oracle数据库坏块恢复—存在rman备份
oracle数据库坏块恢复 rman备份 ORA-01578 -
运用增量备份恢复DG中的gap—主库新增数据文件
运用增量备份恢复DG中的gap—主库新增数据文件
增量备份恢复备库 主库新增数据文件 -
运用物理备库恢复主库truncate表的数据
运用物理备库恢复主库truncate表的数据
truncate表的恢复 物理备库 快照备库 闪回 -
运用rman备份集搭建物理备库
运用rman备份集搭建物理备库
oracle rman备份集 搭建物理备库 -
模拟备库单个索引坏块，主库进行rebuild后测试备库是否能正常备份
模拟备库单个索引坏块，主库进行rebuild后测试备库是否能正常备份
oracle 索引坏块 rebuild 备库备份
