oracle数据库RMAN恢复篇

最新推荐文章于 2021-04-08 11:55:18 发布

黄宝康

最新推荐文章于 2021-04-08 11:55:18 发布

阅读量1.2k

点赞数

分类专栏： oracle 文章标签： oracle数据库RMAN恢复篇

本文链接：https://blog.csdn.net/huangbaokang/article/details/95611627

版权

oracle 专栏收录该内容

217 篇文章 7 订阅

订阅专栏

对数据库进行验证

RMAN> validate database;

如果发现错误，会把这些错误记录到自动诊断库中，使用list failure命令列出故障。

RMAN> validate database;

Starting validate at 12-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/u01/app/oraData/orcl/CMS69_DATA.dbf
input datafile file number=00001 name=/u01/app/oraData/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oraData/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oraData/orcl/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oraData/undoTbs/hbk_undotbs.dbf
input datafile file number=00005 name=/u01/app/oraData/bak/hbk_data.dbf
input datafile file number=00008 name=/u01/app/oraData/undoTbs/hbk_undotbs_auto
input datafile file number=00004 name=/u01/app/oraData/orcl/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:01:04
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              12686        87041           2006312   
  File Name: /u01/app/oraData/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              58964           
  Index      0              12277           
  Other      0              3113            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              19468        73022           2006297   
  File Name: /u01/app/oraData/orcl/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              15098           
  Index      0              10751           
  Other      0              27643           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              1            9600            1402693   
  File Name: /u01/app/oraData/orcl/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              9599            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              455          640             1701516   
  File Name: /u01/app/oraData/orcl/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              20              
  Index      0              2               
  Other      0              163             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              6208         6400            1886833   
  File Name: /u01/app/oraData/bak/hbk_data.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              22              
  Index      0              1               
  Other      0              169             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              10786        160000          1125031   
  File Name: /u01/app/oraData/orcl/CMS69_DATA.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              3629            
  Index      0              651             
  Other      0              144934          

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              65           8000            2006312   
  File Name: /u01/app/oraData/undoTbs/hbk_undotbs.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              7935            

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     0              1132         1280            1403272   
  File Name: /u01/app/oraData/undoTbs/hbk_undotbs_auto
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              148             

channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
including current control file for validation
including current SPFILE in backup set
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              596             
Finished validate at 12-JUL-19

可以看出我的数据库没有异常。

RMAN> list failure;

举例：如果有故障的情况下，该如何进行修复
在这里插入图片描述
上图可知有两个故障，分别是142和101
通过如下命令，获取故障的详情

RMAN> list failure 142 detail;

得到故障的修复建议

RMAN> advise failure;

也可以指定故障的修复建议

RMAN> advise failure 101;

执行故障修复

RMAN> repair failure;

恢复整个数据库

如果数据库在运行，则强制启动到mount阶段

RMAN> startup force mount;   

Oracle instance started
database mounted

Total System Global Area    6680915968 bytes

Fixed Size                     2213936 bytes
Variable Size               3556771792 bytes
Database Buffers            3087007744 bytes
Redo Buffers                  34922496 bytes

进行还原数据库

RMAN> restore database;

Starting restore at 12-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 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 00001 to /u01/app/oraData/orcl/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oraData/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oraData/orcl/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oraData/orcl/users01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oraData/orcl/CMS69_DATA.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oraData/undoTbs/hbk_undotbs.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oraData/undoTbs/hbk_undotbs_auto
channel ORA_DISK_1: reading from backup piece /home/oracle/rman_back_2_1_1013419522
channel ORA_DISK_1: piece handle=/home/oracle/rman_back_2_1_1013419522 tag=TAG20190712T092522
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:27:06
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 00005 to /u01/app/oraData/bak/hbk_data.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/data_file_hbk_data_5_1_1013422543
channel ORA_DISK_1: piece handle=/home/oracle/data_file_hbk_data_5_1_1013422543 tag=TAG20190712T101543
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-JUL-19

恢复数据库

RMAN> recover database;

Starting recover at 12-JUL-19
using channel ORA_DISK_1

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

Finished recover at 12-JUL-19

打开数据库


RMAN> alter database open;

database opened

恢复单个表空间

举例，比如，我们要恢复HBK_DATA表空间
第一步，使表空间offline

RMAN> sql 'alter tablespace HBK_DATA offline';

sql statement: alter tablespace HBK_DATA offline

第二步，执行如下命令

RMAN> run{
	set newname for datafile '/u01/app/oraData/bak/hbk_data.dbf' 
	to '/u01/app/oraData/bak/hbk_data_new.dbf';#重命名数据文件
	restore tablespace HBK_DATA; #还原表空间
	switch datafile all;#更新控制文件
	recover tablespace HBK_DATA;#执行表空间的恢复
}
executing command: SET NEWNAME

Starting restore at 12-JUL-19
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 00005 to /u01/app/oraData/bak/hbk_data_new.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/data_file_hbk_data_5_1_1013422543
channel ORA_DISK_1: piece handle=/home/oracle/data_file_hbk_data_5_1_1013422543 tag=TAG20190712T101543
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
Finished restore at 12-JUL-19

datafile 5 switched to datafile copy
input datafile copy RECID=2 STAMP=1013440072 file name=/u01/app/oraData/bak/hbk_data_new.dbf

Starting recover at 12-JUL-19
using channel ORA_DISK_1

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

Finished recover at 12-JUL-19

第三步：使表空间online

RMAN> sql 'alter tablespace HBK_DATA online';

sql statement: alter tablespace HBK_DATA online

可以看到，hbk_data.dbf改成了hbk_data_new.dbf

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oraData/orcl/users01.dbf
/u01/app/oraData/orcl/undotbs01.dbf
/u01/app/oraData/orcl/sysaux01.dbf
/u01/app/oraData/orcl/system01.dbf
/u01/app/oraData/bak/hbk_data_new.dbf
/u01/app/oraData/orcl/CMS69_DATA.dbf
/u01/app/oraData/undoTbs/hbk_undotbs.dbf
/u01/app/oraData/undoTbs/hbk_undotbs_auto

8 rows selected.

恢复单个数据文件

如果单个数据文件损坏，不必恢复整个表空间，更不必恢复整个数据库，只需要恢复损坏的数据文件即可。
如果数据库还有这个数据文件，需要将其offline，否则会报如下错误

RMAN> restore datafile '/u01/app/oraData/bak/hbk_data_new.dbf';

Starting restore at 12-JUL-19
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=3 STAMP=1013440072 file name=/u01/app/oraData/bak/hbk_data.dbf
destination for restore of datafile 00005: /u01/app/oraData/bak/hbk_data_new.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/12/2019 15:23:03
ORA-19573: cannot obtain exclusive enqueue for datafile 5
ORA-19600: input file is datafile-copy 3 (/u01/app/oraData/bak/hbk_data.dbf)
ORA-19601: output file is datafile 5 (/u01/app/oraData/bak/hbk_data_new.dbf)

1：从备份中还原数据文件

RMAN> restore datafile '/u01/app/oraData/bak/hbk_data_new.dbf';

2：执行如下命令恢复

RMAN> recover datafile ‘/u01/app/oraData/bak/hbk_data_new.dbf’;

Starting recover at 12-JUL-19
using channel ORA_DISK_1

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

Finished recover at 12-JUL-19

3：使数据文件online

SQL> alter database datafile '/u01/app/oraData/bak/hbk_data_new.dbf' online;

Database altered.

修复数据块

如果数据块冲突，不必关闭数据库，可以在线修复冲突块。
找出冲突块。
方法1：

SQL> select * from v$database_block_corruption;

方法2：这种方法不知道好不好使，我没研究过。

SQL> select name,value from v$diag_info;

使用RMAN进行修复冲突块，如下为修复数据文件7中的坏块3及数据文件2中的坏块235

RMAN> blockrecover datafile 7 block 3 datafile 2 block 235;

也可以使用下面命令修复所有的冲突块

RMAN> recover corruption list;

黄宝康

关注

0
点赞
踩
6

收藏

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

分享到 QQ

分享到新浪微博

扫一扫

专栏目录