文档课题：主库数据文件存在坏块，运用备库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成功恢复.