rman不完全恢复
只适用于archivelog模式,只能在mount状态下完成。
基于时间恢复
基于时间恢复是指当出现用户错误(例如误删除表、误截断表)时,恢复到指定时间点的恢复。
示例:
模拟误截断表t_user。
在终端设置环境变量nls_date_format指定日期时间格式。
执行rman,启动数据库到mount状态。
使用set until time命令指定要恢复到的时间点。
在执行了不完全恢复之后,推荐删除早期所有备份,重新备份数据库。如:
run {
delete noprompt backup;
delete noprompt copy;
backup database format='/oracle/10g/oracle/rman/%d_%s.dbf';
sql 'alter system archive log current';
}
基于SCN恢复
基于SCN恢复是指当出现用户错误(例如误删除表、误截断表)时,恢复到指定SCN点的恢复。
模拟误删除表t_user。
执行rman,启动数据库到mount状态。
使用set until scn命令指定要恢复到的scn点。
转储、恢复数据库,并使用resetlogs选项打开数据库。
在执行了不完全恢复之后,推荐删除早期所有备份,重新备份数据库。
只适用于archivelog模式,只能在mount状态下完成。
基于时间恢复
基于时间恢复是指当出现用户错误(例如误删除表、误截断表)时,恢复到指定时间点的恢复。
示例:
模拟误截断表t_user。
在终端设置环境变量nls_date_format指定日期时间格式。
执行rman,启动数据库到mount状态。
使用set until time命令指定要恢复到的时间点。
转储、恢复数据库,并使用resetlogs选项打开数据库。
--模拟误截断表t_user。
SQL> host date
2011年 08月 29日 星期一 21:01:29 CST
SQL> truncate table t_user;
Table truncated.
--在终端设置环境变量nls_date_format指定日期时间格式。
[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
--恢复
[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalog
RMAN> run {
2> startup force mount;
3> set until time='2011-08-29 21:01:00';
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }
--已恢复
SQL> conn sys/oracle@oralife as sysdba
Connected.
SQL> select count(*) from t_user;
COUNT(*)
----------
2
在实际环境下,应该使用LogMiner确定误操作时间点。
在执行了不完全恢复之后,推荐删除早期所有备份,重新备份数据库。如:
run {
delete noprompt backup;
delete noprompt copy;
backup database format='/oracle/10g/oracle/rman/%d_%s.dbf';
sql 'alter system archive log current';
}
基于SCN恢复
基于SCN恢复是指当出现用户错误(例如误删除表、误截断表)时,恢复到指定SCN点的恢复。
模拟误删除表t_user。
执行rman,启动数据库到mount状态。
使用set until scn命令指定要恢复到的scn点。
转储、恢复数据库,并使用resetlogs选项打开数据库。
--模拟误删除表t_user。
SQL> select count(*) from t_user;
COUNT(*)
----------
2
SQL> insert into t_user select 'spring mvc_' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1214281
SQL> drop table t_user;
Table dropped.
--恢复
[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalog
RMAN> run {
2> startup force mount;
3> set until scn=1214281;
4> restore database;
5> recover database;
6> sql 'alter database open resetlogs';
7> }
--已恢复
SQL> select count(*) from t_user;
COUNT(*)
----------
3
在实际环境下,应该使用LogMiner确定误操作SCN点。
在执行了不完全恢复之后,推荐删除早期所有备份,重新备份数据库。
基于日志序列号恢复
基于日志序列号恢复是指恢复数据库到指定日志序列号的状态。
--查看归档日志信息
SQL> select * from t_user;
TEXT
--------------------
java_
spring_
spring mvc_
SQL> insert into t_user select 'oracle_' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#;
SEQUENCE# NAME FIRST_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- -------------
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214498
5q9bh9d_.arc
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214497
5q95ksf_.arc
6 rows selected.
SQL> insert into t_user select 'oracle_seq3' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; --生成日志序列号为2的归档日志
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#;
SEQUENCE# NAME FIRST_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- -------------
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214497
5q95ksf_.arc
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214498
5q9bh9d_.arc
2 /oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog 1216167
SEQUENCE# NAME FIRST_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- -------------
2 /oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog 1216167
2 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_2_7 1216167
5q9cvt1_.arc
9 rows selected.
SQL> insert into t_user select 'oracle_seq3_act' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile; --生成日志序列号为3的归档日志
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select sequence#,name,first_change# from v$archived_log where status='A' order by sequence#;
SEQUENCE# NAME FIRST_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- -------------
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487088.arclog 1214497
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214497
5q95ksf_.arc
1 /oracle/10g/oracle/log/archive_log/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/log/archive_log2/archive_1_1_760487985.arclog 1214498
1 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_1_7 1214498
5q9bh9d_.arc
2 /oracle/10g/oracle/log/archive_log/archive_1_2_760487985.arclog 1216167
SEQUENCE# NAME FIRST_CHANGE#
---------- ---------------------------------------------------------------------------------------------------- -------------
2 /oracle/10g/oracle/log/archive_log2/archive_1_2_760487985.arclog 1216167
2 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_2_7 1216167
5q9cvt1_.arc
3 /oracle/10g/oracle/log/archive_log/archive_1_3_760487985.arclog 1216186
3 /oracle/10g/oracle/log/archive_log2/archive_1_3_760487985.arclog 1216186
3 /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_29/o1_mf_1_3_7 1216186
5q9f4d6_.arc
12 rows selected.
--恢复到日志序列号为3时的状态
[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalog
RMAN> run {
startup force mount;
set until sequence=3;
restore database;
recover database;
sql 'alter database open resetlogs';
}
--查看,可见不包括日志序列号为3的归档日志信息(oracle_seq3_act),即恢复到日志序列号为2的归档日志
SQL> conn sys/oracle@oralife as sysdba
Connected.
SQL> select * from t_user;
TEXT
--------------------
java_
spring_
oracle_
oracle_seq3
spring mvc_
可见不包括日志序列号为3的归档日志信息(oracle_seq3_act),即恢复到日志序列号为2的归档日志。
在执行了不完全恢复之后,推荐删除早期所有备份,重新备份数据库。
基于备份控制文件恢复
基于备份控制文件恢复是指使用备份控制文件恢复数据库的过程。当误删除了表空间或数据库所有控制文件全部损坏时,可以使用这种恢复方法。
模拟误删除了test表空间,因为当前控制文件没有包含该表空间的信息,所以必须使用备份控制文件恢复被误删除的表空间。如果没有使用恢复目录,必须激活控制文件自动备份,否则将不转储控制文件备份。
模拟误删除test表空间。
查看alert日志文件,确定操作时间。
使用rman启动数据库到mount状态。
设置数据库id。
转储控制文件,恢复数据库。
--模拟误删除test表空间。
SQL> create tablespace rtest DATAFILE '/oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf' size 10M;
Tablespace created.
SQL> create table r_test (text varchar(20)) tablespace rtest;
Table created.
SQL> insert into r_test select '00001' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> insert into r_test select '00002' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from r_test;
COUNT(*)
----------
2
SQL> drop tablespace rtest including contents;
Tablespace dropped.
SQL> select dbid from v$database;
DBID
----------
3898974595
SQL> select count(*) from r_test;
select count(*) from r_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> host tail -n 15 /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/alert_oralife.log
Current log# 4 seq# 7 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04.log
Current log# 4 seq# 7 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04_add.log
Tue Aug 30 22:17:30 2011
drop tablespace rtest including contents
Tue Aug 30 22:17:30 2011
Starting control autobackup
Tue Aug 30 22:17:31 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:
Tue Aug 30 22:17:31 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:
Tue Aug 30 22:17:31 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_4751.trc:
Control autobackup written to DISK device
handle '/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_30/o1_mf_s_760573050_75sw7twr_.bkp'
Completed: drop tablespace rtest including contents
--恢复
[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalog
RMAN> startup force nomount;
RMAN> set dbid=3898974595;
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> run {
set until time='2011-08-30 22:17:30';
restore database;
recover database;
sql 'alter database open resetlogs';
}
--查看
SQL> quit
[oracle@localhost ~]$ sqlplus sys/oracle@oralife as sysdba
SQL> select count(*) from r_test;
select count(*) from r_test
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6:
'/oracle/10g/oracle/product/10.2.0/db_1/dbs/MISSING00006'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf
/oracle/10g/oracle/product/10.2.0/oradatabak/undotbs01.dbf
/oracle/10g/oracle/product/10.2.0/oradatabak/sysaux01.dbf
/oracle/10g/oracle/product/10.2.0/oradata/oralife/user01.dbf
/oracle/10g/oracle/product/10.2.0/oradata/oralife/example01.dbf
/oracle/10g/oracle/product/10.2.0/db_1/dbs/MISSING00006
6 rows selected.
恢复失败,失败原因:restore controlfile from autobackup;用于转储自动备份的最新的控制文件,即删除表空间rtest之后自动备份的控制文件。
解决办法:使用restore controlfile from autobackup until time "to_date('2011-08-31 21:26:16','yyyy-mm-dd hh24:mi:ss')"; 查看alert日志文件确定删除表空间rtest之前自动备份控制文件时间,也就是删除表空间rtest之前的时间点。
SQL> create tablespace rtest DATAFILE '/oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf' size 10M;
Tablespace created.
SQL> create table r_test (text varchar(20)) tablespace rtest;
Table created.
SQL> insert into r_test select '00001' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
--删除早期所有备份,重新备份数据库
RMAN> run {
delete noprompt backup;
delete noprompt copy;
backup database format='/oracle/10g/oracle/rman/%d_%s.dbf';
sql 'alter system archive log current';
}
--删除表空间与对应的数据文件
SQL> insert into r_test select '00002' from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from r_test;
COUNT(*)
----------
2
SQL> drop tablespace rtest including contents and datafiles;
Tablespace dropped.
SQL> select dbid from v$database;
DBID
----------
3898974595
SQL> select count(*) from r_test;
select count(*) from r_test
*
ERROR at line 1:
ORA-00942: table or view does not exist
--查看alert日志,以确定操作时间,Wed Aug 31 21:26:17 2011
SQL> host tail -n 20 /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/bdump/alert_oralife.log
Current log# 4 seq# 4 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04.log
Current log# 4 seq# 4 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo04_add.log
Wed Aug 31 21:25:26 2011
Thread 1 advanced to log sequence 5
Current log# 2 seq# 5 mem# 0: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log
Current log# 2 seq# 5 mem# 1: /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02_add.log
Wed Aug 31 21:26:17 2011
drop tablespace rtest including contents and datafiles
Wed Aug 31 21:26:18 2011
Deleted file /oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf
Starting control autobackup
Wed Aug 31 21:26:18 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:
Wed Aug 31 21:26:18 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:
Wed Aug 31 21:26:18 2011
Errors in file /oracle/10g/oracle/product/10.2.0/db_1/admin/oralife/udump/oralife_ora_3646.trc:
Control autobackup written to DISK device
handle '/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656378_75wfmtcm_.bkp'
Completed: drop tablespace rtest including contents and datafiles
--恢复控制文件
[oracle@localhost ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@localhost ~]$ rman target sys/oracle@oralife nocatalog
RMAN> startup force nomount;
RMAN> set dbid=3898974595;
--与下面set until time(即删除表空间rtest的操作时间)相比少了1秒, 以转储正确的控制文件,
--如果这里设置的时间 与alert日志中删除表空间的操作时间相同,会转储最新的控制文件(没有包含表空间rtest信息)导致恢复失败。
RMAN> restore controlfile from autobackup until time "to_date('2011-08-31 21:26:16','yyyy-mm-dd hh24:mi:ss')";
Starting restore at 2011-08-31 21:35:29
using channel ORA_DISK_1
recovery area destination: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area
database name (or database unique name) used for search: ORALIFE
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656297_75wfk9gc_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control01.ctl
output filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control02.ctl
output filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/control03.ctl
Finished restore at 2011-08-31 21:35:33
RMAN> alter database mount;
RMAN> run {
set until time='2011-08-31 21:26:17';
restore database;
recover database;
sql 'alter database open resetlogs';
6> }
executing command: SET until clause
Starting restore at 2011-08-31 21:36:43
Starting implicit crosscheck backup at 2011-08-31 21:36:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2011-08-31 21:36:44
Starting implicit crosscheck copy at 2011-08-31 21:36:44
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2011-08-31 21:36:44
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arc
File Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arc
File Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656297_75wfk9gc_.bkp
File Name: /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/autobackup/2011_08_31/o1_mf_s_760656378_75wfmtcm_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/system01.dbf
restoring datafile 00002 to /oracle/10g/oracle/product/10.2.0/oradatabak/undotbs01.dbf
restoring datafile 00003 to /oracle/10g/oracle/product/10.2.0/oradatabak/sysaux01.dbf
restoring datafile 00004 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/user01.dbf
restoring datafile 00005 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/example01.dbf
restoring datafile 00006 to /oracle/10g/oracle/product/10.2.0/oradata/oralife/rtest.dbf --转储了表空间rtest对应的数据文件
channel ORA_DISK_1: reading from backup piece /oracle/10g/oracle/rman/ORALIFE_98.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/10g/oracle/rman/ORALIFE_98.dbf tag=TAG20110831T212421
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2011-08-31 21:37:20
Starting recover at 2011-08-31 21:37:20
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arc
archive log thread 1 sequence 4 is already on disk as file /oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arc
archive log thread 1 sequence 5 is already on disk as file /oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log
archive log filename=/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_3_75wfkc9w_.arc thread=1 sequence=3
archive log filename=/oracle/10g/oracle/product/10.2.0/db_1/flash_recovery_area/ORALIFE/archivelog/2011_08_31/o1_mf_1_4_75wfl6w2_.arc thread=1 sequence=4
archive log filename=/oracle/10g/oracle/product/10.2.0/oradata/oralife/redo02.log thread=1 sequence=5
media recovery complete, elapsed time: 00:00:03
Finished recover at 2011-08-31 21:37:25
sql statement: alter database open resetlogs
--查看,已恢复
[oracle@localhost ~]$ sqlplus sys/oracle@oralife as sysdba
SQL> select count(*) from r_test;
COUNT(*)
----------
2