无RMAN备份集情况下的坏块恢复

测试的环境是没有可用的RMAN备份集，但是有数据文件的热备，下面来看测试：
--创建测试用户和测试表
[Oracle@ora10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 16 16:01:02 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining optionsSQL> create user zlm identified by zlm;User created.SQL> alter user zlm default tablespace zlm;User altered.
SQL> grant dba to zlm;
Grant succeeded.
SQL> conn zlm/zlmConnected.
SQL> create table corrupt_test （id number（10）,name varchar2（15））;
Table created.
SQL> insert into corrupt_test values（1,"aaron8219"）;
1 row created.
SQL> commit;
Commit complete.
SQL> set lin 130SQL> col segment_name for a20SQL> col tablespace_name for a20
SQL> select segment_name,tablespace_name from dba_segments where segment_name="CORRUPT_TEST"; SEGMENT_NAME TABLESPACE_NAME
-------------------- --------------------
CORRUPT_TEST ZLM
SQL> col name for a45
SQL> select a.segment_name,a.tablespace_name,b.file#,b.name from dba_segments a,v$datafile b where a.header_file=b.file# and a.segment_name="CORRUPT_TEST";
SEGMENT_NAME TABLESPACE_NAME FILE# NAME
-------------------- -------------------- ---------- ---------------------------------------------
CORRUPT_TEST ZLM 6 /u01/app/oracle/oradata/ora10g/zlm01.dbf
由于之前做过RMAN备份，所以先把备份集删除
[oracle@ora10g ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on 16 16:06:47 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G （DBID=4175411955）
RMAN> list backupset; using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
286 Full 880.50M DISK 00:01:35 2014-11-12
BP Key: 286 Status: AVAILABLE Compressed: NO Tag: TAG20141112T141548
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp
List of Datafiles in backup set 286
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------- ----
1 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/system01.dbf
2 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/undotbs01.dbf
3 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/sysaux01.dbf
4 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/users01.dbf
5 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/example01.dbf
6 Full 1202813 2014-11-12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
302 42.17M DISK 00:00:27 2014-11-21
BP Key: 302 Status: AVAILABLE Compressed: YES Tag: ARC_BAK
Piece Name: /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227317_351.arc
List of Archived Logs in backup set 302
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ---------- ---------- ---------
1 39 1234835 2014-11-18 1247748 2014-11-21
1 40 1247748 2014-11-21 1249682 2014-11-21
1 41 1249682 2014-11-21 1250181 2014-11-21
1 42 1250181 2014-11-21 1258063 2014-11-21
1 43 1258063 2014-11-21 1260208 2014-11-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
303 Full 164.91M DISK 00:01:52 2014-11-21
BP Key: 303 Status: AVAILABLE Compressed: YES Tag: DB_BAK
Piece Name: /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227354_352.db
List of Datafiles in backup set 303
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------- ----
1 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/system01.dbf
2 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/undotbs01.dbf
3 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/sysaux01.dbf
4 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/users01.dbf
5 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/example01.dbf
6 Full 1260226 2014-11-21 /u01/app/oracle/oradata/ora10g/zlm01.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
304 19.50K DISK 00:00:01 2014-11-21
BP Key: 304 Status: AVAILABLE Compressed: YES Tag: ARC_BAK
Piece Name: /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227471_353.arc
List of Archived Logs in backup set 304
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ---------- ---------- ---------
1 44 1260208 2014-11-21 1260277 2014-11-21
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
305 Full 7.23M DISK 00:00:01 2014-11-21
BP Key: 305 Status: AVAILABLE Compressed: NO Tag: TAG20141121T151114
Piece Name: /u01/orabackup/backupsets/ora10g-c-4175411955-20141121-04.ctl
Control File Included: Ckp SCN: 1260283 Ckp time: 2014-11-21
SPFILE Included: Modification time: 2014-11-21
RMAN> exit
Recovery Manager complete.
[oracle@ora10g ~]$ cd /u01/orabackup/backupsets/
[oracle@ora10g backupsets]$ ll -lrth
total 215M
-rw-r----- 1 oracle oinstall 43M Nov 21 15:09 ora10g-4175411955_20141121_864227317_351.arc
-rw-r----- 1 oracle oinstall 165M Nov 21 15:11 ora10g-4175411955_20141121_864227354_352.db
-rw-r----- 1 oracle oinstall 20K Nov 21 15:11 ora10g-4175411955_20141121_864227471_353.arc
-rw-r----- 1 oracle oinstall 7.3M Nov 21 15:11 ora10g-c-4175411955-20141121-04.ctl
--删除RMAN备份集
[oracle@ora10g backupsets]$ rm -f *
[oracle@ora10g backupsets]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on 16 16:07:59 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G （DBID=4175411955）
RMAN> crosscheck backup; using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp recid=286 stamp=863446548
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227317_351.arc recid=302 stamp=864227318
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227354_352.db recid=303 stamp=864227356
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227471_353.arc recid=304 stamp=864227472
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/u01/orabackup/backupsets/ora10g-c-4175411955-20141121-04.ctl recid=305 stamp=864227475
Crosschecked 5 objects
RMAN> delete noprompt expired backupset;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
302 302 1 1 EXPIRED DISK /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227317_351.arc
303 303 1 1 EXPIRED DISK /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227354_352.db
304 304 1 1 EXPIRED DISK /u01/orabackup/backupsets/ora10g-4175411955_20141121_864227471_353.arc
305 305 1 1 EXPIRED DISK /u01/orabackup/backupsets/ora10g-c-4175411955-20141121-04.ctl
deleted backup piece
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227317_351.arc recid=302 stamp=864227318
deleted backup piece
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227354_352.db recid=303 stamp=864227356
deleted backup piece
backup piece handle=/u01/orabackup/backupsets/ora10g-4175411955_20141121_864227471_353.arc recid=304 stamp=864227472
deleted backup piece
backup piece handle=/u01/orabackup/backupsets/ora10g-c-4175411955-20141121-04.ctl recid=305 stamp=864227475
Deleted 4 EXPIRED objects
现在把由RMAN脚本生成的备份集删除了，再查看一次
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
286 Full 880.50M DISK 00:01:35 2014-11-12
BP Key: 286 Status: AVAILABLE Compressed: NO Tag: TAG20141112T141548
Piece Name: /u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp
List of Datafiles in backup set 286
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ---------- ----
1 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/system01.dbf
2 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/undotbs01.dbf
3 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/sysaux01.dbf
4 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/users01.dbf
5 Full 1202813 2014-11-12 /u01/app/oracle/oradata/ora10g/example01.dbf
6 Full 1202813 2014-11-12
还有一个备份集在fra中做的全库备份，也将其删除
RMAN> host; [oracle@ora10g backupsets]$ cd /u01/app/oracle/flash_recovery_area/ORA10G/backupset/
[oracle@ora10g backupset]$ ll
total 4
drwxr-x--- 2 oracle oinstall 4096 Nov 12 14:15 2014_11_12
[oracle@ora10g backupset]$ rm -rf *
[oracle@ora10g backupset]$ exit
exit
host command complete
RMAN> crosscheck backup; using channel ORA_DISK_1
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp recid=286 stamp=863446548
Crosschecked 1 objects
RMAN> delete noprompt expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
286 286 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ORA10G/backupset/2014_11_12/o1_mf_nnndf_TAG20141112T141548_b65yrnkg_.bkp recid=286 stamp=863446548
Deleted 1 EXPIRED objects
RMAN> list backup summary;
RMAN> list backup;
RMAN> exit
好了，现在数据库的RMAN备份彻底没有了，继续我们的测试：
--开启测试表空间热备份模式
SQL> alter tablespace zlm begin backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
----- ------------------ ---------- ----------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 ACTIVE 1317685 2014-11-26
6 rows selected.
此时可以看到，开启热备模式以后，6号文件的状态从NOT ACTIVE变成了ACTIVE
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
--------------------------------------------- ------------------
/u01/app/oracle/oradata/ora10g/system01.dbf 1306748
/u01/app/oracle/oradata/ora10g/undotbs01.dbf 1306748
/u01/app/oracle/oradata/ora10g/sysaux01.dbf 1306748
/u01/app/oracle/oradata/ora10g/users01.dbf 1306748
/u01/app/oracle/oradata/ora10g/example01.dbf 1306748
/u01/app/oracle/oradata/ora10g/zlm01.dbf 1319387
6 rows selected.
SCN也比其他文件的要大，因为相当于对6号文件单独进行存档了，只不过SCN还没有写进数据文件头，这个时候这个数据文件是废的，要保持一致性，必须要依靠归档来实现
--OS级别热备份6号数据文件
SQL> ！cp $ORACLE_BASE/oradata/zlm01.dbf /u01/zlm01_bak.dbf
cp: cannot stat `/u01/app/oracle/oradata/zlm01.dbf": No such file or directory
SQL> ！cp $ORACLE_BASE/oradata/ora10g/zlm01.dbf /u01/zlm01_bak.dbf
--关闭热备模式
SQL> alter tablespace zlm end backup;
Tablespace altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ----------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 1319387 2014-11-26
6 rows selected.
现在6号文件的状态又变回了NOT ACTIVE，说明热备结束了
SQL> select name,checkpoint_change# from v$datafile;
NAME CHECKPOINT_CHANGE#
--------------------------------------------- ------------------
/u01/app/oracle/oradata/ora10g/system01.dbf 1306748
/u01/app/oracle/oradata/ora10g/undotbs01.dbf 1306748
/u01/app/oracle/oradata/ora10g/sysaux01.dbf 1306748
/u01/app/oracle/oradata/ora10g/users01.dbf 1306748
/u01/app/oracle/oradata/ora10g/example01.dbf 1306748
/u01/app/oracle/oradata/ora10g/zlm01.dbf 1319387
6 rows selected.
数据文件的SCN依然是之前的，还没有变化
SQL> select header_block from dba_segments where segment_name="CORRUPT_TEST"; HEADER_BLOCK
------------
11
通过dba_segments视图，得知6号文件的段头块是11
--模拟出现坏块
SQL> ！
[oracle@ora10g backupsets]$ dd of=/u01/app/oracle/oradata/ora10g/zlm01.dbf bs=8192 conv=notrunc seek=12 <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
11 bytes （11 B） copied, 0.000168204 seconds, 65.4 kB/s
seek=12表示跳过12个block开始写入，因为我不想破坏段头块，只是在文件尾部写了废数据“corruption”，那么这个块就会标识为逻辑坏块
[oracle@ora10g backupsets]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 16 15:52:41 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.
SQL> conn zlm/zlm
Connected.
SQL> select * from corrupt_test;
ID NAME
---------- ---------------
1 aaron8219
此时由于测试表corrupt_test里数据块中的行数据还在内存中，所以还是可以查询到行记录的
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from corrupt_test;
select * from corrupt_test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted （file # 6, block # 12）
ORA-01110: data file 6: "/u01/app/oracle/oradata/ora10g/zlm01.dbf"
但是一旦我们把它刷到磁盘，就报ORA-01578的错误了，提示6号文件的第12个块损坏了，就是之前指定的那个数据块
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@ora10g backupsets]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on 16 16:30:19 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.
connected to target database: ORA10G （DBID=4175411955）
RMAN> blockrecover datafile 6 block 12;
Starting blockrecover at 2014-11-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 11/26/2014 16:30:51
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
此时直接用blockrecover来恢复坏块是不行的，首先我们没有可用的备份集，其次，控制文件中也不知道从哪里去找可用的备份文件，那么我们就要先把之前做过的热备文件catalog到控制文件中
RMAN> catalog datafilecopy "/u01/zlm01_bak.dbf";
cataloged datafile copy
datafile copy filename=/u01/zlm01_bak.dbf recid=17 stamp=864664486
RMAN> blockrecover datafile 6 block 12;
Starting blockrecover at 2014-11-26
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block（s） from datafile copy /u01/zlm01_bak.dbf
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 2014-11-26
RMAN> exitRecovery Manager complete.
再做一次blockrecover，现在就顺利地介质恢复完了
[oracle@ora10g backupsets]$ sqlplus zlm/zlm
SQL*Plus: Release 10.2.0.1.0 - Production on 16 16:35:23 2014
Copyright （c） 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from corrupt_test;
ID NAME
---------- ---------------
1 aaron8219
SQL>可以看到，之前丢失的数据，又回来了总结：虽然在没有RMAN备份集的情况下，通过热备文件可以把丢失的数据恢复出来，但这毕竟还是很不靠谱的。在生产环境中，我们几乎不可能经常去对某个数据文件做热备，也不会知道什么时候，哪个文件就会出现坏块。所以，平时做好RMAN全备还是非常非常重要的，只要有备份集和归档，我们的数据就不会丢失。当执行blockrecover datafile xxx block xxx时，Oracle会直接去RMAN备份集中恢复，不需要额外的catalog步骤，也不用我们过多地人为干预。--------------------------------------推荐阅读 --------------------------------------RMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htm--------------------------------------分割线 --------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址