基于时间的不完全恢复-CSDN博客

首先要在开启归档的前提下
做全库备份
点击(此处)折叠或打开
1. RMAN> backup as compressed backupset full database;
3. Starting backup at 06-APR-17
4. using channel ORA_DISK_1
5. channel ORA_DISK_1: starting compressed full datafile backup set
6. channel ORA_DISK_1: specifying datafile(s) in backup set
7. input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
8. input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
9. input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
10. input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
11. input datafile file number=00007 name=/u01/app/oracle/oradata/PROD1/test02.dbf
12. input datafile file number=00006 name=/u01/app/oracle/oradata/PROD1/test01.dbf
13. input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
14. channel ORA_DISK_1: starting piece 1 at 06-APR-17
15. channel ORA_DISK_1: finished piece 1 at 06-APR-17
16. piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1 tag=TAG20170406T091318 comment=NONE
17. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
18. channel ORA_DISK_1: starting compressed full datafile backup set
19. channel ORA_DISK_1: specifying datafile(s) in backup set
20. including current control file in backup set
21. including current SPFILE in backup set
22. channel ORA_DISK_1: starting piece 1 at 06-APR-17
23. channel ORA_DISK_1: finished piece 1 at 06-APR-17
24. piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/03s10amr_1_1 tag=TAG20170406T091318 comment=NONE
25. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
26. Finished backup at 06-APR-17
创建表t1 ，并将表t1 删除
点击(此处)折叠或打开
1. SQL> create table t1(x int);
3. Table created.
6. SQL> insert into t1 select rownum from dual connect by rownum<=10;
8. 10 rows created.
10. SQL> commit;
12. Commit complete.
14. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
16. TO_CHAR(SYSDATE,'YY
17. -------------------
18. 2017-04-06 09:16:22
20. SQL> drop table t1;
22. Table dropped.
24. SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
26. TO_CHAR(SYSDATE,'YY
27. -------------------
28. 2017-04-06 09:17:01
30. SQL> shutdown immediate;
31. Database closed.
32. Database dismounted.
33. ORACLE instance shut down.
在mount状态下利用rman 做基于时间的恢复
点击(此处)折叠或打开
1. RMAN> startup mount;
3. Oracle instance started
4. database mounted
6. Total System Global Area 217157632 bytes
8. Fixed Size 2251816 bytes
9. Variable Size 159384536 bytes
10. Database Buffers 50331648 bytes
11. Redo Buffers 5189632 bytes
13. RMAN> run{
14. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
15. 3> set until time='2017-04-06 09:16:22';
16. 4> restore database;
17. 5> recover database;
18. 6> alter database open resetlogs;
19. 7> }
21. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
23. executing command: SET until clause
25. Starting restore at 06-APR-17
26. allocated channel: ORA_DISK_1
27. channel ORA_DISK_1: SID=18 device type=DISK
29. channel ORA_DISK_1: starting datafile backup set restore
30. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
31. channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/PROD1/system01.dbf
32. channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/PROD1/sysaux01.dbf
33. channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/PROD1/undotbs01.dbf
34. channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD1/users01.dbf
35. channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/PROD1/example01.dbf
36. channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/PROD1/test01.dbf
37. channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/PROD1/test02.dbf
38. channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1
39. channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/02s10ale_1_1 tag=TAG20170406T091318
40. channel ORA_DISK_1: restored backup piece 1
41. channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
42. Finished restore at 06-APR-17
44. Starting recover at 06-APR-17
45. using channel ORA_DISK_1
47. starting media recovery
48. media recovery complete, elapsed time: 00:00:00
50. Finished recover at 06-APR-17
52. database opened
验证
点击(此处)折叠或打开
1. SQL> select * from t1;
3. X
4. ----------
5. 1
6. 2
7. 3
8. 4
9. 5
10. 6
11. 7
12. 8
13. 9
14. 10
16. 10 rows selected.
此时如果再想恢复到别的时间，会报错
点击(此处)折叠或打开
1. RMAN> run{
2. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
3. 3> set until time='2017-04-06 09:16:21';
4. 4> restore database;
5. 5> recover database;
6. 6> alter database open resetlogs;
7. 7> }
9. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
11. executing command: SET until clause
13. Starting restore at 06-APR-17
14. RMAN-00571: ===========================================================
15. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
16. RMAN-00571: ===========================================================
17. RMAN-03002: failure of restore command at 04/06/2017 09:41:55
18. RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
点击(此处)折叠或打开
1. RMAN> run{
2. 2> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
3. 3> set until time='2017-04-06 09:16:22';
4. 4> restore database;
5. 5> recover database;
6. 6> alter database open resetlogs;
7. 7> }
9. using target database control file instead of recovery catalog
10. sql statement: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"
12. executing command: SET until clause
14. Starting restore at 06-APR-17
15. RMAN-00571: ===========================================================
16. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
17. RMAN-00571: ===========================================================
18. RMAN-03002: failure of restore command at 04/06/2017 09:30:29
19. RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time