Oracle RMAN 备份恢复实战-优快云博客

本文链接：https://blog.youkuaiyun.com/ONLYONE_HTLIU/article/details/5631217

本文详细介绍了Oracle RMAN备份与恢复的各种场景实践，包括完全与不完全恢复、基于时间与SCN的恢复、控制文件恢复等，提供了丰富的案例和解决方法。

create   table   test(
       id   number(4),
       dt   date   default   sysdate);

       create or replace procedure MYPROC IS
　　　　begin
　　　　 insert   into   test(id,dt)   values(test_sequence.NEXTVAL,(select sysdate from dual))
          commit;
　　　end;
　　　/

---------------------------------------
CREATE SEQUENCE tes_sequence.
     INCREMENT BY 1   -- 每次加1个
     START WITH 1     -- 从1开始计数
     NOMAXVALUE       -- 不设置最大值
     NOCYCLE          -- 一直累加，不循环
     CACHE 10;


--------------------------------------------------
begin
sys.dbms_job.submit(job => :job,
                      what => 'MYPROC;',
                      next_date => to_date('21-05-2010 16:57:43', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => '(sysdate) + 1/ (24*60)');
commit;
end;
/
------------------------------------------------------

run{

allocate channel cha1 type disk;

backup

incremental level 0

format '/u01/rmanbak/inc0_%u_%T'

tag monday_inc0

database;

release channel cha1;
--delete noprompt obsolete;
}

---------------------------------------------backup database
/u01/app/oracle/product/10.1/bin/rman target sys/sys123 cmdfile=/u01/rmanbak/bakl0 --加上报错msglog=/u01/rmanbak/bakl0.log

/u01/app/oracle/product/10.1/bin/rman target sys/sys123 cmdfile=/u01/rmanbak/bakl1 --加上报错msglog=/u01/rmanbak/bakl1.log

/u01/app/oracle/product/10.1/bin/rman target sys/sys123 cmdfile=/u01/rmanbak/bakl2 --加上报错msglog=/u01/rmanbak/bakl2.log

----------------------------------------------------rman 配置------------------------------------------------------------------------------
RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.1/dbs/snapcf_sxtsioms.f'; # default

------------------------------ rman备份-----
select * from v$archived_log
select * from v$log_history
select group#,sequence#,archived,status from v$log;
select * from v$logfile

执行了0,1级备份后：
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
------- ---- -- ---------- ----------- ------------ ---------------
31      Incr 0 540.09M    DISK        00:01:00     27-MAY-10
        BP Key: 31   Status: AVAILABLE Compressed: NO Tag: MONDAY_INC0
        Piece Name: /u01/rmanbak/inc0_18lenhpe_20100527
List of Datafiles in backup set 31
File LV Type Ckp SCN    Ckp Time Name
---- -- ---- ---------- --------- ----
1    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/system01.dbf
2    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/undotbs01.dbf
3    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/sysaux01.dbf
4    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/users01.dbf
5    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/TSSAFAULT.dbf
6    0 Incr 728961     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/TSSAPUB.dbf

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
32      Incr 0 6.80M      DISK        00:00:02     27-MAY-10
        BP Key: 32   Status: AVAILABLE Compressed: NO Tag: MONDAY_INC0
        Piece Name: /u01/rmanbak/inc0_19lenhrg_20100527
Control File Included: Ckp SCN: 728992       Ckp time: 27-MAY-10
SPFILE Included: Modification time: 27-MAY-10

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
33      Incr 1 752.00K    DISK        00:00:33     27-MAY-10
        BP Key: 33   Status: AVAILABLE Compressed: NO Tag: MONDAY_INC1
        Piece Name: /u01/rmanbak/inc1_1alenich_20100527
List of Datafiles in backup set 33
File LV Type Ckp SCN    Ckp Time Name
---- -- ---- ---------- --------- ----
1    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/system01.dbf
2    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/undotbs01.dbf
3    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/sysaux01.dbf
4    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/users01.dbf
5    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/TSSAFAULT.dbf
6    1 Incr 729429     27-MAY-10 /u01/app/oracle/oradata/sxtsioms/TSSAPUB.dbf

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
34      Incr 1 6.80M      DISK        00:00:03     27-MAY-10
        BP Key: 34   Status: AVAILABLE Compressed: NO Tag: MONDAY_INC1
        Piece Name: /u01/rmanbak/inc1_1blenidl_20100527
Control File Included: Ckp SCN: 729445       Ckp time: 27-MAY-10
SPFILE Included: Modification time: 27-MAY-10

注意：此备份只备份了数据文件控制文件 spfile 但没有备份redo和archivelog 并且备份datafile和confile备份集的scn也不一致

----------------------------------------------完全恢复----------------------------------
一、所有数据文件删除
1、所有数据文件全部被删除
无法正常关闭
SQL> shutdown abort
ORACLE instance shut down.
-------
2、此时数据库只能启动到mount状态，查询需要恢复的数据文件
SQL> select file#,error from v$recover_file;

FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
2 FILE NOT FOUND
3 FILE NOT FOUND
4 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND

6 rows selected.

3、恢复数据库
RMAN> restore database ;

Starting restore at 27-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/sxtsioms/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/sxtsioms/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/sxtsioms/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/sxtsioms/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/sxtsioms/TSSAFAULT.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/sxtsioms/TSSAPUB.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/inc0_18lenhpe_20100527
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmanbak/inc0_18lenhpe_20100527 tag=MONDAY_INC0
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
Finished restore at 27-MAY-10

RMAN> recover database;

Starting recover at 27-MAY-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/sxtsioms/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/sxtsioms/undotbs01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/sxtsioms/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/sxtsioms/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/sxtsioms/TSSAFAULT.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata/sxtsioms/TSSAPUB.dbf
channel ORA_DISK_1: reading from backup piece /u01/rmanbak/inc1_1alenich_20100527
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/rmanbak/inc1_1alenich_20100527 tag=MONDAY_INC1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

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

Finished recover at 27-MAY-10

RMAN> sql'alter database open';

sql statement: alter database open

二、所有数据文件及控制文件删除
1、试图转储备份
RMAN> restore database;

Starting restore at 27-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/27/2010 11:08:33
ORA-01507: database not mounted

RMAN> restore controlfile from autobackup;

Starting restore at 27-MAY-10
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: SXTSIOMS
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/27/2010 11:12:07
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

都不可以看来还是要配置CONFIGURE CONTROLFILE AUTOBACKUP on，此时只好把重命名的控制文件还原，后续待测

2、启动到mount
RMAN> sql'alter database mount';

3、此时突发奇想，我只转储而不恢复看看什么效果
RMAN> restore database;

Starting restore at 27-MAY-10
using channel ORA_DISK_1

sql statement: alter database open
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/27/2010 11:19:05
RMAN-11003: failure during parse/execution of SQL statement: alter database open
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/sxtsioms/system01.dbf'
恍然大悟，肯定是当前controlfile和datadile scn不一致造成的，查询语句如下
select checkpoint_change# from v$database;
select name,checkpoint_change# from v$datafile;
select name,checkpoint_change# from v$datafile_header;
select name,last_change# from v$datafile;
判断正确，怎么搞，还是先恢复完，把剩下的实验做完再回来收拾这些顽固子弟！

三、rman真牛，连物理介质损坏的情况都考虑到了，我可不能为了一个实验把磁盘搞坏吧，呵呵先记录下来吧
rman>run{
set newname for datafile 1 to 'c:/demo/system01.dbf;
..................................................
restore database ;
switch datafile all;
recover database;
sql'alter datbase open';
}

假如一个坏了
rman>run{
set newname for datafile 1 to 'c:/demo/system01.dbf;
restore datafile 1;
switch datafile 1;
recover datafile 1;
sql'alter datbase open';
}
哎既然不能实验就到此为止吧，next

四、恢复system表空间的数据文件只能在mount状态下
1、删除system01.dbf,自然不必多说，“cpu出事了”
2、mount状态
3、恢复

RMAN> run{
2> restore datafile 1;
3> recover datafile 1;
4> sql'alter database open';
5> }

五、丢失其它数据文件恢复的通用方法如下：可以在mount下，也可在open下
1、启动到mount
2、rman>sql'alter datafile 2 offline';
   rman>sql'alter database open';
   rman>restore datafile 2;
   rman>recover datafile 2;
   rman>sql'alter datafile 2 online';
其实无论是在open状态还是shutdown状态下丢失非system数据文件都可以使用此方法恢复，
如果是在open状态下丢失可以省略sql'alter database open';这一步书上分的太细

假如一个介质坏了，通用方法是在mount下
rman>run{
sql'alter datafile 2 offline';
sql'alter datbase open';
set newname for datafile 2 to 'c:/demo/datafile2.dbf;
restore datafile 2;
switch datafile 2;
recover datafile 2;
sql'alter datafile 2 online';

}

六、表空间恢复（表空间里面所有数据文件均失败）--类似数据文件
rman>run{
sql'alter tablespace users offline for recover';
restore tablespace uesrs;
recover tablespace users;
sql'alter tablespace users online;
}

真不知道干嘛分的那么细。恢复数据文件不一样吗。还有所在磁盘介质损坏下表空间的恢复真懒得实验了。

六、数据块介质恢复
如果数据文件只有出现部分数据块损坏，那么就不需要恢复整个数据文件只要恢复损坏的数据块即可：
当数据块出现error：
ora-01578:oracle data block corrupted(file #5,block #21)
恢复：rman>BLOCKRECOVER DEVICE TYPE DISK DATAFILE 5 BLOCK 21,23;

---------------------------------------------不完全恢复----------------------------------------
必要条件：mount、archivelog
一、基于时间
delete from test where id<900

通过闪回查询也可根据logminer查出 delete 时间为2010-5-27 14:44:02

2、启动到mount状态
RMAN> run{
2> restore database;
3> recover database until time "to_date('2010-5-27 14:43:02','yyyy-mm-dd hh24:mi:ss')";
4> sql'alter database open resetlogs';
5> }

Starting restore at 27-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=158 devtype=DISK

skipping datafile 1; already restored to file /u01/app/oracle/oradata/sxtsioms/system01.dbf
skipping datafile 2; already restored to file /u01/app/oracle/oradata/sxtsioms/undotbs01.dbf
skipping datafile 3; already restored to file /u01/app/oracle/oradata/sxtsioms/sysaux01.dbf
skipping datafile 4; already restored to file /u01/app/oracle/oradata/sxtsioms/users01.dbf
skipping datafile 5; already restored to file /u01/app/oracle/oradata/sxtsioms/TSSAFAULT.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/sxtsioms/TSSAPUB.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 27-MAY-10

starting media recovery

archive log thread 1 sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_27/o1_mf_1_15_5zvr39f4_.arc
archive log thread 1 sequence 16 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_27/o1_mf_1_16_5zvstb7x_.arc
archive log thread 1 sequence 17 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_27/o1_mf_1_17_5zw1629p_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_27/o1_mf_1_15_5zvr39f4_.arc thread=1 sequence=15
media recovery complete, elapsed time: 00:00:07
Finished recover at 27-MAY-10

sql statement: alter database open resetlogs

注意：此时日志序列被重置为1 select * from v$archived_log 也就是说数据已经不能恢复到以前状态，不信可以试试。

当前之际需要重新备份：先删除没用备份 delete backup；再备份做个0级备份

二、基于scn，类似时间恢复但在测试过程中丢失2条数据，不知道为什么
RMAN> run{
2> restore database;
3> recover database until scn =801990;
4> sql'alter database open resetlogs';
5> }

执行完整新备份

三、一个有趣的测试
select * from test
1 1235
2 1236
执行备份后再插入数据
select * from test
1 1237
2 1238
3 1235
4 1236

delete from test where id<1237 2010-5-27 17:14:28

恢复到2010-5-27 17:14:28后查询
select * from test
1 1237
2 1238
3 1235
4 1236

总结：1、rman可以恢复到备份前的状态2、不完全恢复也不会丢失数据

四、基于撤销seq日志序列
select * from v$archived_log
select group#,sequence#,archived,status from v$log;

insert into test(id,dt) values(test_sequence.NEXTVAL,(select sysdate from dual));
commit;
select * from test order by id
开始试验-----------------
做个0级备份
当前seq是5 v$archived_log 是1234 test表数据如下：
1 1235 2010-5-27 17:09:36
2 1236 2010-5-27 17:10:41
3 1237 2010-5-27 17:11:47
4 1238 2010-5-27 17:12:52
插入数据：
5 1245 2010-5-28 9:43:33

切换归档当前日志5，当前日志为6
插入数据：
6 1246 2010-5-28 11:25:46
切换归档当前日志6，当前日志为7 查询7以前都被归档。

假如此刻删除test表所有数据，并且把日志序列为6的归档日志也删除。
delete from test
commit
select dbms_flashback.get_system_change_number from dual
select versions_xid, versions_operation from test versions between scn 830000 and 834100 order by 2
select xid,commit_scn,commit_timestamp,operation,undo_sql from flashback_transaction_query q where q.xid
='05000D0063010000'

通过上面语句查出删除时间为2010-5-28 11:35:46，我们先用基于时间的恢复看看嘛结果？
RMAN> recover database until time "to_date('2010-5-28 11:35:46','yyyy-mm-dd hh24:mi:ss')";

Starting recover at 28-MAY-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_28/o1_mf_1_2_5zy7koh2_.arc
archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_28/o1_mf_1_3_5zy7ndpr_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_28/o1_mf_1_4_5zy7s9jh_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/SXTSIOMS/archivelog/2010_05_28/o1_mf_1_5_5zyfn8jw_.arc
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/28/2010 11:50:35
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 6 lowscn 833307 found to restore

哈哈哈结果出来了！看来基于日志的恢复可以登场了
RMAN> recover database;
RMAN> recover database until sequence=6;
RMAN> sql'alter database open resetlogs';
一切ok 接下来看看数据吧
1 1235 2010-5-27 17:09:36
2 1236 2010-5-27 17:10:41
3 1237 2010-5-27 17:11:47
4 1238 2010-5-27 17:12:52
5 1245 2010-5-28 9:43:33
以上为当前数据
6 1246 2010-5-28 11:25:46数据丢失。

四、基于控制文件的恢复
一般是误删除表空间或者数据控制文件全部损坏时运用此方法恢复
都网上搜了一下，都是按照书上照搬的多啊，尽信书不如无书，bymyself
RMAN> show all
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default 看到是默认状态没有自动备份controlfile，后来才明白原来只要备份system就会自动产生。
当前test表如下：
1 1235 2010-5-27 17:09:36
2 1236 2010-5-27 17:10:41
3 1237 2010-5-27 17:11:47
4 1238 2010-5-27 17:12:52
5 1245 2010-5-28 9:43:33

create table testdd tablespace tssafault as select * from test

drop tablespace tssafault including contents;

查看alterlog：
Fri May 28 15:23:30 2010
Completed: drop tablespace tssafault including contents

执行恢复：在nomount下
RMAN> restore controlfile from autobackup;
channel ORA_DISK_1: no autobackups found in the recovery area
autobackup search outside recovery area not attempted because DBID was not set
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/28/2010 15:47:31
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

看来必须开启自动备份控制文件了：RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;
从新全备备份看来自动备份的控制文件会默认放置在闪回区
执行恢复：
RMAN> restore controlfile from autobackup;

Starting restore at 28-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: SXTSIOMS
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/SXTSIOMS/autobackup/2010_05_28/o1_mf_s_720201585_5zyxzlmv_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/sxtsioms/control01.ctl
output filename=/u01/app/oracle/oradata/sxtsioms/control02.ctl
output filename=/u01/app/oracle/oradata/sxtsioms/control03.ctl
Finished restore at 28-MAY-10

RMAN> sql'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> restore database;

RMAN> recover database until time "to_date('2010-5-28 15:23:30','yyyy-mm-dd hh24:mi:ss')";

RMAN> sql'alter database open resetlogs';

五、基于控制文件的恢复---删除表空间和全部控制文件

应用以上方法ok，此时所有删除的表空间级全部数据文件已经完全恢复