备份恢复- 完全恢复和不完全恢复
来源：互联网 发布：淘宝时间是北京时间吗 编辑：程序博客网 时间：2024/04/19 13:08
备份恢复
恢复分为完全恢复和不完全恢复
不完全恢复可以基于时间，也可以基于scn
在测试的时候发现，恢复的节点必须在list backup中最大节点以后。【还要继续测试】0级 --> 操作（建表1） --> 想恢复的时间节点1 --> 0级 --> 想恢复的时间节点2 --> 操作（建表2） -->想恢复的时间节点3 --> 停库恢复会发现，无法恢复时间节点1，只能恢复0级以后的时间节点。【由于只有1副本，所以只能恢复到最近的0级备份时间点以后】====================================================
在之前是有一个全量备份的
Fri Sep 22 09:14:34 CST 2017 时间后，wxk 用户创建表
SQL> create table rman_test (id int);Table created.SQL> insert into rman_test values ( 1);1 row created.SQL> commit;Commit complete.将数据库恢复到 09:14:34
SQL> shutdown immediate ;Database closed.Database dismounted.ORACLE instance shut down.[oracle@dg1 bak]$ rman target /RMAN> startup mount ;RMAN> list backup;using target database control file instead of recovery catalogList of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1673 Incr 0 6.76M DISK 00:00:27 2017-09-22 08:47:47 BP Key: 1673 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_kksf358o_1_1_20170922 List of Datafiles in backup set 1673 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 3 0 Incr 2353748 2017-09-22 08:47:21 /oracle/app/oradata/dg1/undotbs01.dbf 7 0 Incr 2353748 2017-09-22 08:47:21 /oracle/app/oradata/dg1/deam.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1674 Incr 0 39.52M DISK 00:01:08 2017-09-22 08:48:29 BP Key: 1674 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_knsf358p_1_1_20170922 List of Datafiles in backup set 1674 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 5 0 Incr 2353756 2017-09-22 08:47:21 /oracle/app/oradata/dg1/wxk.dbf 8 0 Incr 2353756 2017-09-22 08:47:21 /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/sdeBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1675 Incr 0 471.99M DISK 00:03:07 2017-09-22 08:50:28 BP Key: 1675 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_kmsf358p_1_1_20170922 List of Datafiles in backup set 1675 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 2 0 Incr 2353753 2017-09-22 08:47:21 /oracle/app/oradata/dg1/sysaux01.dbf 6 0 Incr 2353753 2017-09-22 08:47:21 /oracle/app/oradata/dg1/qsy.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1676 Incr 0 656.73M DISK 00:03:12 2017-09-22 08:50:33 BP Key: 1676 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_klsf358p_1_1_20170922 List of Datafiles in backup set 1676 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 0 Incr 2353750 2017-09-22 08:47:21 /oracle/app/oradata/dg1/system01.dbf 4 0 Incr 2353750 2017-09-22 08:47:21 /oracle/app/oradata/dg1/users01.dbfBS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------1679 2.00K DISK 00:00:00 2017-09-22 08:50:47 BP Key: 1679 Status: AVAILABLE Compressed: NO Tag: ARC_BAK Piece Name: /data/bak/arch_kssf35f7_1_1_20170922 List of Archived Logs in backup set 1679 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 651 2353986 2017-09-22 08:50:45 2353994 2017-09-22 08:50:46BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------1681 5.51M DISK 00:00:00 2017-09-22 08:50:47 BP Key: 1681 Status: AVAILABLE Compressed: NO Tag: ARC_BAK Piece Name: /data/bak/arch_krsf35f7_1_1_20170922 List of Archived Logs in backup set 1681 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 650 2351480 2017-09-22 08:44:00 2353986 2017-09-22 08:50:45BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1684 Full 37.11M DISK 00:00:01 2017-09-22 08:50:55 BP Key: 1684 Status: AVAILABLE Compressed: NO Tag: TAG20170922T085054 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-01 SPFILE Included: Modification time: 2017-09-22 08:45:05 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2354036 Ckp time: 2017-09-22 08:50:54BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1685 Full 37.11M DISK 00:00:03 2017-09-22 09:10:34 BP Key: 1685 Status: AVAILABLE Compressed: NO Tag: TAG20170922T091031 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-02 SPFILE Included: Modification time: 2017-09-22 08:54:14 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2354266 Ckp time: 2017-09-22 09:10:31RMAN> restore database;RMAN> recover database until time "to_date('2017-09-22 09:14:34','yyyy-mm-dd hh24:mi:ss')";RMAN> alter database open resetlogs;SQL> select table_name from user_tables;查看，找不到rman_test 表了，测试成功。
创建表
SQL> create table rman_test2 (name varchar2(10));Table created.SQL> commit ;Commit complete.Fri Sep 22 10:11:37 CST 2017目标：恢复到10:11:37之前 rman_test2 不存在
RMAN> shutdown immediate; RMAN> restore database;这里查看rman 备份集合
结果和恢复后做对比。
RMAN> recover database until time "to_date('2017-09-22 10:00:37','yyyy-mm-dd hh24:mi:ss')";SQL> conn wxk/wxkConnected.SQL> select * from rman_test2;select * from rman_test2 *ERROR at line 1:ORA-00942: table or view does not exist查不到表 rman_test2 ，测试成功
检查rman 备份集合
RMAN> list backup ;List of Backup Sets===================BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1673 Incr 0 6.76M DISK 00:00:27 2017-09-22 08:47:47 BP Key: 1673 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_kksf358o_1_1_20170922 List of Datafiles in backup set 1673 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 3 0 Incr 2353748 2017-09-22 08:47:21 /oracle/app/oradata/dg1/undotbs01.dbf 7 0 Incr 2353748 2017-09-22 08:47:21 /oracle/app/oradata/dg1/deam.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1674 Incr 0 39.52M DISK 00:01:08 2017-09-22 08:48:29 BP Key: 1674 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_knsf358p_1_1_20170922 List of Datafiles in backup set 1674 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 5 0 Incr 2353756 2017-09-22 08:47:21 /oracle/app/oradata/dg1/wxk.dbf 8 0 Incr 2353756 2017-09-22 08:47:21 /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/sdeBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1675 Incr 0 471.99M DISK 00:03:07 2017-09-22 08:50:28 BP Key: 1675 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_kmsf358p_1_1_20170922 List of Datafiles in backup set 1675 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 2 0 Incr 2353753 2017-09-22 08:47:21 /oracle/app/oradata/dg1/sysaux01.dbf 6 0 Incr 2353753 2017-09-22 08:47:21 /oracle/app/oradata/dg1/qsy.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1676 Incr 0 656.73M DISK 00:03:12 2017-09-22 08:50:33 BP Key: 1676 Status: AVAILABLE Compressed: NO Tag: DB_LEV0 Piece Name: /data/bak/db_lev0_klsf358p_1_1_20170922 List of Datafiles in backup set 1676 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ---- 1 0 Incr 2353750 2017-09-22 08:47:21 /oracle/app/oradata/dg1/system01.dbf 4 0 Incr 2353750 2017-09-22 08:47:21 /oracle/app/oradata/dg1/users01.dbfBS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------1679 2.00K DISK 00:00:00 2017-09-22 08:50:47 BP Key: 1679 Status: AVAILABLE Compressed: NO Tag: ARC_BAK Piece Name: /data/bak/arch_kssf35f7_1_1_20170922 List of Archived Logs in backup set 1679 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 651 2353986 2017-09-22 08:50:45 2353994 2017-09-22 08:50:46BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ -------------------1681 5.51M DISK 00:00:00 2017-09-22 08:50:47 BP Key: 1681 Status: AVAILABLE Compressed: NO Tag: ARC_BAK Piece Name: /data/bak/arch_krsf35f7_1_1_20170922 List of Archived Logs in backup set 1681 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 650 2351480 2017-09-22 08:44:00 2353986 2017-09-22 08:50:45BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1684 Full 37.11M DISK 00:00:01 2017-09-22 08:50:55 BP Key: 1684 Status: AVAILABLE Compressed: NO Tag: TAG20170922T085054 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-01 SPFILE Included: Modification time: 2017-09-22 08:45:05 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2354036 Ckp time: 2017-09-22 08:50:54BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1685 Full 37.11M DISK 00:00:03 2017-09-22 09:10:34 BP Key: 1685 Status: AVAILABLE Compressed: NO Tag: TAG20170922T091031 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-02 SPFILE Included: Modification time: 2017-09-22 08:54:14 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2354266 Ckp time: 2017-09-22 09:10:31BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1686 Full 37.11M DISK 00:00:03 2017-09-22 09:46:10 BP Key: 1686 Status: AVAILABLE Compressed: NO Tag: TAG20170922T094607 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-03 SPFILE Included: Modification time: 2017-09-22 09:39:08 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2354873 Ckp time: 2017-09-22 09:46:07BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ -------------------1687 Full 37.11M DISK 00:00:02 2017-09-22 11:46:22 BP Key: 1687 Status: AVAILABLE Compressed: NO Tag: TAG20170922T114620 Piece Name: /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/c-1927319524-20170922-04 SPFILE Included: Modification time: 2017-09-22 10:16:08 SPFILE db_unique_name: DG1_PD Control File Included: Ckp SCN: 2356553 Ckp time: 2017-09-22 11:46:20发现备份的ckpt 又变化了，说明打开数据库后，重新应用了归档。
那么，这样打开后，能不能再重新使用我们的备份集合，再次恢复到2017-09-22 10:00:37 呢？
这时候真实时间已经到了11:46:20 左右，我们要再次恢复到10:00:37
开始测试
RMAN> shutdown immediate ;database closeddatabase dismountedOracle instance shut downRMAN> RMAN> startup mount ;connected to target database (not started)Oracle instance starteddatabase mountedTotal System Global Area 1068937216 bytesFixed Size 2260088 bytesVariable Size 671089544 bytesDatabase Buffers 390070272 bytesRedo Buffers 5517312 bytesRMAN>restore database;这时，我们输入：
RMAN> recover database until time "to_date('2017-09-22 10:00:37','yyyy-mm-dd hh24:mi:ss')";Starting recover at 2017-09-22 12:29:18using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 09/22/2017 12:29:18RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time会报错！！！【继续检查】要怎么去处理呢？
查看
RMAN> list incarnation;List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------1 1 DG1 1927319524 PARENT 1 2017-06-06 12:25:082 2 DG1 1927319524 PARENT 2354170 2017-09-22 09:10:223 3 DG1 1927319524 PARENT 2354785 2017-09-22 09:46:014 4 DG1 1927319524 CURRENT 2356463 2017-09-22 11:46:14RMAN> reset database to incarnation 3;database reset to incarnation 3RMAN> recover database until time "to_date('2017-09-22 10:00:36','yyyy-mm-dd hh24:mi:ss')";Starting recover at 2017-09-22 13:56:55using channel ORA_DISK_1using channel ORA_DISK_2using channel ORA_DISK_3using channel ORA_DISK_4starting media recoveryarchived log for thread 1 with sequence 650 is already on disk as file /oracle/app/oradata/arch/1_650_945951908.dbfarchived log for thread 1 with sequence 651 is already on disk as file /oracle/app/oradata/arch/1_651_945951908.dbfarchived log for thread 1 with sequence 652 is already on disk as file /oracle/app/oradata/arch/1_652_945951908.dbfarchived log for thread 1 with sequence 1 is already on disk as file /oracle/app/oradata/arch/1_1_955357822.dbfarchived log for thread 1 with sequence 2 is already on disk as file /oracle/app/oradata/arch/1_2_955357822.dbfarchived log for thread 1 with sequence 3 is already on disk as file /oracle/app/oradata/arch/1_3_955357822.dbfarchived log for thread 1 with sequence 1 is already on disk as file /oracle/app/oradata/arch/1_1_955359961.dbfarchived log for thread 1 with sequence 2 is already on disk as file /oracle/app/oradata/arch/1_2_955359961.dbfarchived log for thread 1 with sequence 3 is already on disk as file /oracle/app/oradata/arch/1_3_955359961.dbfarchived log file name=/oracle/app/oradata/arch/1_650_945951908.dbf thread=1 sequence=650archived log file name=/oracle/app/oradata/arch/1_651_945951908.dbf thread=1 sequence=651archived log file name=/oracle/app/oradata/arch/1_652_945951908.dbf thread=1 sequence=652archived log file name=/oracle/app/oradata/arch/1_1_955357822.dbf thread=1 sequence=1archived log file name=/oracle/app/oradata/arch/1_2_955357822.dbf thread=1 sequence=2archived log file name=/oracle/app/oradata/arch/1_3_955357822.dbf thread=1 sequence=3archived log file name=/oracle/app/oradata/arch/1_1_955359961.dbf thread=1 sequence=1archived log file name=/oracle/app/oradata/arch/1_2_955359961.dbf thread=1 sequence=2archived log file name=/oracle/app/oradata/arch/1_3_955359961.dbf thread=1 sequence=3media recovery complete, elapsed time: 00:00:03Finished recover at 2017-09-22 13:57:00RMAN> alter database open resetlogs;恢复成功。
原理理解：
我们在做RMAN恢复的时候，可以使用list incarnation 命令查看控制文件包含了哪些对应物。
当在做Media Recover的不完全恢复时，通过resetlogs打开库，则Incarnation（数据库对应物）表示这个数据库的特定的逻辑生存期。
DBA可能有时需要这样的恢复：需要使用上次执行resetlogs命令打开数据库前生成的一个备份来进行还原数据库，或者可能需要还原到执行上一个resetlogs命令之前的时间点。
最后进行一个总结：
在有1个0级备份和n个1级备份的时候我们这样进行不完全恢复【恢复到某个0级备份的后的时间点，通过归档进行恢复的】
恢复到 2017-09-22 09:14:34
SQL> shutdown immediate ;[oracle@dg1 bak]$ rman target /RMAN> startup mount ;RMAN> list backup;RMAN> restore database;RMAN> recover database until time "to_date('2017-09-22 09:14:34','yyyy-mm-dd hh24:mi:ss')";RMAN> alter database open resetlogs;重新进行生产，时间到了11:46:20 以后。再次用该备份恢复到 2017-09-22 10:00:36
SQL> shutdown immediate ;[oracle@dg1 bak]$ rman target /RMAN> list incarnation;List of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------1 1 DG1 1927319524 PARENT 1 2017-06-06 12:25:082 2 DG1 1927319524 PARENT 2354170 2017-09-22 09:10:223 3 DG1 1927319524 PARENT 2354785 2017-09-22 09:46:014 4 DG1 1927319524 CURRENT 2356463 2017-09-22 11:46:14RMAN> reset database to incarnation 3; #恢复到上一个对应物RMAN>restore database;RMAN> recover database until time "to_date('2017-09-22 10:00:36','yyyy-mm-dd hh24:mi:ss')";RMAN> alter database open resetlogs;恢复后会发现一个ORPHAN 说明这个对应物是一个孤立的，那个将无法使用了。
RMAN> list incarnation;using target database control file instead of recovery catalogList of Database IncarnationsDB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time------- ------- -------- ---------------- --- ---------- ----------1 1 DG1 1927319524 PARENT 1 06-JUN-172 2 DG1 1927319524 PARENT 2354170 22-SEP-173 3 DG1 1927319524 PARENT 2354785 22-SEP-175 5 DG1 1927319524 CURRENT 2356462 22-SEP-174 4 DG1 1927319524 ORPHAN 2356463 22-SEP-17阅读全文
0 0
- 备份恢复- 完全恢复和不完全恢复
- 冷备份---不完全恢复
- ORACLE完全恢复和不完全恢复的区别
- RMAN全库【完全恢复/不完全恢复】
- Oracle备份恢复-手动不完全恢复
- 冷备份----不完全恢复cancel
- Oracle备份与不完全恢复
- oracle数据库完全恢复和不完全恢复以及执行用户管理辈分恢复
- 【不完全恢复】
- Oracle备份恢复-手动完全恢复
- oracle rman 完全恢复与不完全恢复（实例讲解）
- 概念区分:完全恢复 不完全恢复有什么区别？
- RMAN全库【完全恢复/不完全恢复】brief版
- 探索ORACLE不完全恢复之--基于备份控制文件恢复
- RMAN备份与恢复—基于时间的不完全恢复
- RMAN备份与恢复—基于SCN的不完全恢复
- Mysql 备份恢复备份和恢复类型
- Mysql增量备份，完全恢复
- 2000:最长公共子上升序列
- zepto框架学习日记
- 【087】深度学习读书笔记：P28奇异值分解的证明
- 家族人员管理系统
- 从源代码到可执行程序
- 备份恢复- 完全恢复和不完全恢复
- 深度学习——被Intel caffe支配的恐惧（一）
- LOJ 1370 Bi-shoe and Phi-shoe（欧拉函数）
- ZooKeeper系列（四）
- Android LayoutInflater原理分析，带你一步步深入了解View(一)
- syslog-ng详解——日志备份
- 走穿java23种设计模式-2工厂方法模式详解
- Centos7安装 Redis
- 周中训练笔记9
