RMAN 还原与恢复1(RMAN Complete Recovery)
在Oracle中,三大文件即控制文件,数据文件,日志文件的丢失与破坏都将需要使用还原或恢复来使数据库正常化。而RMAN还原与恢复是实现数据库完整性、可靠性必不可少的手段之一。还原简言之即是将所需的文件从备份中复制到原来文件所在的路径。还原通常可以包括数据库、表空间、数据文件级别的还原。通常还原后的内容会滞后于最新的数据库SCN,因此将归档日志、联机重做日志文件中的内容更新到还原的数据文件中,这个过程称之为恢复。
恢复的几种情形
1.在mount 或open 阶段完成的灾难恢复:非系统表空间,undo表空间,所有的数据文件。
2.在nomount 阶段完成的灾难恢复:控制文件(controlfile)。
3.在mount 阶段完成的灾难恢复:系统表空间,联机重做日志文件。
一、RMAN在归档模式下的还原与恢复的简化例子
1 RMAN> connect target / nocatalog 2 3 RMAN> startup mount; 4 5 RMAN> restore database; 6 7 RMAN> recover database; 8 9 RMAN> alter database open;
二、使用RMAN进行还原与恢复(非catalog方式)
1.数据文件丢失的恢复处理nocatalog(数据库mount状态)
--对数据库做全备
1 RMAN> backup as compressed backupset 2 2> format '/tmp/backup/whole_%U' 3 3> tag =Whole_bak database; 4 RMAN> sql 'alter system archive log current'; 5 sql statement: alter system archive log current 6 RMAN>
--对数据库做0级增量备份
1 RMAN> run { 2 2> allocate channel ch1 type disk; 3 3> backup incremental level 0 database 4 4> format '/tmp/backup/Inc_0_%U' 5 5> tag=Inc_0; 6 6> release channel ch1; 7 7> }
--对表插入新记录并切换日志
1 SQL> select * from t order by 1; 2 3 ID NAME 4 ---------- ---------------- 5 0 oracle 6 1 oracle 7 2 oracle 8 3 oracle 9 4 oracle 10 5 oracle 11 6 oracle 12 13 7 rows selected. 14 15 SQL> insert into t select 7,'myrman' from dual; 16 17 1 row created. 18 19 SQL> commit; 20 21 Commit complete. 22 23 SQL> alter system checkpoint; 24 25 SQL> alter system switch logfile;
--对数据库做1级增量备份
1 RMAN> run{ 2 2> allocate channel ch1 type disk; 3 3> backup incremental level 1 database 4 4> format '/tmp/backup/Inc_1_%U' 5 5> tag=Inc_1; 6 6> release channel ch1; 7 7> }
1 SQL> shutdown immediate; --关闭数据库 2 3 SQL> ho rm $ORACLE_BASE/oradata/sunbak/*.dbf --删除所有的数据文件 */ 4 5 SQL> startup mount
--使用RMAN连接到mount状态的数据库
1 -bash-3.00$ rman target / nocatalog 2 3 Recovery Manager: Release 10.2.0.2.0 - Production on Sat Jan 26 13:24:57 2013 4 5 Copyright (c) 1982, 2005, Oracle. All rights reserved. 6 7 connected to target database: SUN (DBID=1913551800, not open) 8 using target database control file instead of recovery catalog 9 10 RMAN>
--执行数据库还原
1 --注意当存在完整备份也同时存在级增量备份时,Oracle会自动使用级增量备份来还原数据库 2 RMAN> restore database; 3 4 --下面可以看出还原的数据来自级增量备份 5 channel ORA_DISK_1: reading from backup piece /tmp/backup/Inc_0_1fo0cqbb_1_1 6 channel ORA_DISK_1: restored backup piece 1 7 piece handle=/tmp/backup/Inc_0_1fo0cqbb_1_1 tag=INC_0 8 9 10 --执行数据库恢复 11 RMAN> recover database; 12 13 channel ORA_DISK_1: reading from backup piece /tmp/backup/Inc_1_1ho0crhi_1_1 14 channel ORA_DISK_1: restored backup piece 1 15 piece handle=/tmp/backup/Inc_1_1ho0crhi_1_1 tag=INC_1 16 17 --打开数据库验证恢复 18 RMAN> alter database open; 19 20 SQL> select * from t order by 1; 21 22 ID NAME 23 ---------- ---------------- 24 0 oracle 25 1 oracle 26 2 oracle 27 3 oracle 28 4 oracle 29 5 oracle 30 6 oracle 31 7 myrman 32 33 8 rows selected. 34 35 SQL>
2.磁盘损坏导致数据文件无法恢复到原始位置或不想恢复到原始位置的处理(数据库mount状态)
使用set newname 命令将数据文件还原到新路径:set newname for datafile 1 to '<newdir>/system01.dbf'; --为恢复的数据文件指定新路径或重命名
使用switch命令将变更更新到控制文件:switch datafile n | all ; --等同于alter database rename file命令,该命令将恢复的数据文件重命名更新到控制文件
--下面的示例首先删除sun01_1.dbf,接下来将其恢复到oradata目录中
1 --关闭数据库 2 SQL> shutdown immediate 3 SQL> quit 4 5 --删除文件 6 -bash-3.00$ rm -f /u01/oradata/sunbak/sun01_1.dbf 7 8 --启到mount状态 9 SQL> startup mount 10 ORACLE instance started. 11 SQL> 12 13 --查看删除文件 14 SQL> select file#,name,status from v$datafile; 15 16 FILE# NAME STATUS 17 ---------- ----------------------------------- ------- 18 1 /u01/oradata/sunbak/system01.dbf SYSTEM 19 2 /u01/oradata/sunbak/undotbs01.dbf ONLINE 20 3 /u01/oradata/sunbak/sysaux01.dbf ONLINE 21 4 /u01/oradata/sunbak/users01.dbf ONLINE 22 5 /u01/oradata/sunbak/example01.dbf ONLINE 23 6 /u01/oradata/sunbak/sun01_1.dbf ONLINE 24 7 /u01/oradata/sunbak/sun02_1.dbf ONLINE 25 8 /u01/oradata/sunbak/sun03_1.dbf ONLINE 26 27 8 rows selected. 28 29 SQL> 30 31 --连接rman 32 bash-3.00$ rman target / nocatalog 33 connected to target database: SUN (DBID=1913551800, not open) 34 using target database control file instead of recovery catalog 35 RMAN> 36 37 --恢复 38 RMAN> run{ 39 2> set newname for datafile 6 to '/u01/oradata/sunbak/sun01.dbf'; 40 3> restore database; 41 4> switch datafile all; 42 5> recover database; 43 6> alter database open; 44 7> } 45 --验证结果 46 SQL> conn user1/user1 47 Connected. 48 SQL> select * from t order by 1; 49 50 ID NAME 51 ---------- ---------------- 52 0 oracle 53 1 oracle 54 2 oracle 55 3 oracle 56 4 oracle 57 5 oracle 58 6 oracle 59 7 myrman 60 61 8 rows selected. 62 63 SQL>
3.恢复表空间(数据库open状态)
--删除表空间内的数据文件,删除后在针对位于该表空间的表进行插入记录以及实施检查点进程
1 --插入测试数据 2 SQL> insert into user1.t select 8,'inspur' from dual; 3 SQL> commit; 4 SQL> alter system checkpoint; 5 6 System altered. 7 8 SQL> 9 10 --强制检查点后,告警日志出现错误提示,视图v$recover_file给出了故障数据文件 11 [oracle@oradb ~]$ tail -f alert_sun.log 12 Errors in file /u01/admin/sun/bdump/sun_ckpt_4554.trc: 13 ORA-01171: datafile 6 going offline due to error advancing checkpoint 14 ORA-01116: error in opening database file 6 15 ORA-01110: data file 6: '/u01/oradata/sunbak/sun01.dbf' 16 ORA-27041: unable to open file 17 Intel SVR4 UNIX Error: 2: No such file or directory 18 Additional information: 3 19 20 21 22 SQL> select * from v$recover_file; 23 24 FILE# ONLINE ONLINE_ ERROR CHANGE# TIME 25 ---------- ------- ------- ------------------ ---------- --------- 26 6 OFFLINE OFFLINE FILE NOT FOUND 0 27 28 29 SQL> select name,status from v$datafile where file#=6; 30 31 NAME STATUS 32 ----------------------------------- ------- 33 /u01/oradata/sunbak/sun01.dbf RECOVER 34 35 SQL> 36 37 38 --使用RMAN命令恢复数据文件,此时数据库处于OPEN状态,因此首先需要将表空间脱机,恢复完成之后再将其联机 39 RMAN> run { 40 2> sql 'alter tablespace sun01 offline immediate'; 41 3> set newname for datafile 6 to '/u01/oradata/sunbak/sun01_1.dbf'; 42 4> restore tablespace sun01; 43 5> switch datafile all; 44 6> recover tablespace sun01; 45 7> sql 'alter tablespace sun01 online'; 46 8> } 47 48 --检查状态 49 SQL> select name,status from v$datafile where file#=6; 50 51 NAME STATUS 52 ----------------------------------- ------- 53 /u01/oradata/sunbak/sun01_1.dbf ONLINE 54 55 SQL> 56 57 SQL> select * from user1.t order by 1; 58 59 ID NAME 60 ---------- ----------------------------------- 61 0 oracle 62 1 oracle 63 2 oracle 64 3 oracle 65 4 oracle 66 5 oracle 67 6 oracle 68 7 myrman 69 8 inspur 70 71 9 rows selected. 72 73 SQL>
--也可以使用下面的命令完成同样的功能
--注意如果表空间内有多个数据文件,而仅有单个数据文件损坏则采用下面的方式处理更为妥当
1 RMAN> run { 2 2> sql 'alter database datafile 6 offline immediate'; 3 3> set newname for datafile 6 to '/u01/oradata/sunbak/sun01_1.dbf'; 4 4> restore datafile 6; 5 5> switch datafile all; 6 6> recover tdatafile 6; 7 7> sql 'alter database datafile 6 online'; 8 8> }