标签：主库 备库 ---------- orcl151 oracle 坏块 DISK backup ORA
文档课题：备库数据文件存在坏块，运用主库rman备份进行恢复.
数据库：oracle 11.2.0.4
主机名：主库 leo-oel150 备库：leo-oel151
1、模拟坏块
1.1、备库备份
[oracle@leo-oel151 ~]$ rman target sys/oracle_4U@orcl151
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Feb 1 21:33:31 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL150 (DBID=4073973096)
RMAN> backup as compressed backupset full database format '/home/oracle/rmanbak/data_full_%T_%s_%p' plus archivelog;
1.2、建测试表
--主库建测试表.
HR@orcl150> create table employees01 as select * from employees;
Table created.
HR@orcl150> insert into employees01 select * from employees01;
20 rows created.
--主库查rowid.
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.
--备库查rowid.
HR@orcl151> 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.
说明：主备库rowid以及每行对应的块号均相同.
1.3、损坏数据块
--备库损坏403号块.
RMAN> recover datafile 4 block 403 clear;
Starting recover at 03-FEB-23
using channel ORA_DISK_1
Finished recover at 03-FEB-23
--确认数据块被损坏
RMAN> backup validate check logical tablespace users;
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: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 21 668 1090741
File Name: /u01/app/oracle/oradata/orcl151/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 1 30
Index 0 21
Other 0 568
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl151/orcl151/trace/orcl151_ora_40366.trc for details
Finished backup at 03-FEB-23
SYS@orcl151> set line 200
SYS@orcl151> r
1* select * from v$database_block_corruption
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
-------------------- -------------------- -------------------- -------------------- ---------
4 403 1 139137045603416 FRACTURED
SYS@orcl151> set line 200
SYS@orcl151> 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
------------------------------ -------------------- ------------ ---------------- --------------------
orcl151 READ ONLY WITH APPLY ARCHIVELOG PHYSICAL STANDBY MAXIMUM PERFORMANCE
SYS@orcl151> alter system flush buffer_cache;
System altered.
2、损坏块的恢复
--采用主库的备份集进行恢复.
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/orcl150/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-081jiq2a_1_1.dbf tag=TAG20230203T154202 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-FEB-23
--将备份集传输到备库.
[oracle@leo-oel150 rmanbak]$ scp users-081jiq2a_1_1.dbf [email protected]:/home/oracle/rmanbak
[email protected]'s password:
users-081jiq2a_1_1.dbf
2.2、恢复损坏块
--在备库上进行损坏块的恢复，先停止备库的实时同步.
[oracle@leo-oel151 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 15:45:54 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@orcl151> alter database recover managed standby database cancel;
Database altered.
SYS@orcl151> 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-oel151 ~]$ 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> delete backup；
--将主库传递过来的备份集进行注册.
RMAN> catalog start with '/home/oracle/rmanbak/users-081jiq2a_1_1.dbf';
using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/rmanbak/users-081jiq2a_1_1.dbf
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/rmanbak/users-081jiq2a_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-081jiq2a_1_1.dbf
--将备库启动到mount阶段.
[oracle@leo-oel151 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 15:48:44 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@orcl151> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl151> 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@orcl151> 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-oel151 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Feb 3 15:49:17 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL150 (DBID=4073973096, not open)
--备库数据文件4的恢复.
RMAN> restore datafile 4;
Starting restore at 03-FEB-23
using channel ORA_DISK_1
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/orcl151/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbak/users-081jiq2a_1_1.dbf
channel ORA_DISK_1: piece handle=/home/oracle/rmanbak/users-081jiq2a_1_1.dbf tag=TAG20230203T154202
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
2.3、数据校验
恢复后进行相关检验.
RMAN> backup validate check logical tablespace users;
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: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 21 668 1090797
File Name: /u01/app/oracle/oradata/orcl151/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 30
Index 0 21
Other 0 568
Finished backup at 03-FEB-23
[oracle@leo-oel151 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 3 15:52:07 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@orcl151> select * from v$database_block_corruption;
no rows selected
SYS@orcl151> alter database recover managed standby database disconnect from session;
Database altered.
说明：备库数据文件4成功恢复.
参考网址：https://www.codenong.com/cs106636170/
标签：主库,
备库,
----------,
orcl151,
oracle,
坏块,
DISK,
backup,
ORA
From： https://blog.51cto.com/u_12991611/6035979