做实验6a碰到的问题

chenghuaming

做实验6a破环时，有个上周五晚上（系统时间2014/12/31 00:44:35 ）建的表空间，没有备份，restore报错，后面就想把数据库恢复到2014/12/31 00:44:35这个时间之前，提示要恢复的时间早于resetlog时间，我就把数据库设置到2号化身，
RMAN> list incarnation;   ------这是现在的状态
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1359978017       PARENT  1          2009-08-13:23:00:48
2       2       ORCL     1359978017       CURRENT 754488     2013-11-22:21:54:46
3       3       ORCL     1359978017       ORPHAN  1990832    2014-12-31:02:40:09
4       4       ORCL     1359978017       ORPHAN  1992181    2014-12-31:03:58:57
开始恢复（中间还有因为归档日志找不到失败一次，我就把恢复的时间又提前了）
run {
sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
set until time '2014/12/30:02:44:35';
restore database;
recover database;
}
最后结果，
starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/03/2015 04:45:59
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'DEC 30 2014 02:44:35' using backup controlfile
ORA-00275: media recovery has already been started

RMAN>
---------------------
请唐老师看下，现在怎么处理

botang

这样的现象是由于4号实验时，你建了一个表空间，而这个表空间是没有备份的。

之后6a实验继续做。

6a是恢复到日志断点。

这样首先不要乱换时间比如：

有个上周五晚上（系统时间2014/12/31 00:44:35 ）建的表空间，没有备份，restore报错，后面就想把数据库恢复到2014/12/31 00:44:35这个时间之前

看视频中6a的做法。分类的每个实验都是典型的。这不是14号实验（删除表空间的恢复），所以不能乱换时间。

用4号实验的方法（alter database create datafile .... as new；）把那个没有备份的错误解决掉后，剩下的就是6a实验的做法。

chenghuaming

执行（alter database create datafile .... as new；）后，恢复
RMAN>  run {
2> set until sequence 7 thread 1;
3> restore database from tag '2T_WHOLE_INCRO';
4> recover database;
5> }

executing command: SET until clause

Starting restore at 2015-01-04:01:47:11
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
using channel ORA_SBT_TAPE_6
using channel ORA_SBT_TAPE_7
using channel ORA_SBT_TAPE_8
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/04/2015 01:47:11
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>
---------
我查list backup 这些文件有备份
RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time   
------- ---------- ----------- ------------ -------------------
36      704.00K    SBT_TAPE    00:00:23     2014-12-29:09:25:00
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1gprcael_1_1   Media: station4-000004

  List of Archived Logs in backup set 36
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    101     1919152    2014-12-29:03:03:34 1923493    2014-12-29:09:24:35

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
37      Incr 0  436.19M    SBT_TAPE    00:01:04     2014-12-29:09:26:07
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1iprcaff_1_1   Media: station4-000005
  List of Datafiles in backup set 37
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  2    0  Incr 1923518    2014-12-29:09:25:03 +DATA/orcl/datafile/sysaux.257.832197065

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
38      Incr 0  596.13M    SBT_TAPE    00:01:10     2014-12-29:09:26:13
        BP Key: 40   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1hprcaff_1_1   Media: station4-000004
  List of Datafiles in backup set 38
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 1923517    2014-12-29:09:25:03 +DATA/orcl/datafile/system.256.832197063

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
39      Incr 0  5.69M      SBT_TAPE    00:01:33     2014-12-29:09:26:37
        BP Key: 41   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1lprcafg_1_1   Media: RMAN-DEFAULT-000005
  List of Datafiles in backup set 39
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 1897631    2014-12-28:07:56:58 +DATA/orcl/datafile/users.259.867485943

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
40      Incr 0  1.75M      SBT_TAPE    00:01:37     2014-12-29:09:26:40
        BP Key: 42   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1jprcaff_1_1   Media: RMAN-DEFAULT-000006
  List of Datafiles in backup set 40
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3    0  Incr 1923519    2014-12-29:09:25:03 +DATA/orcl/datafile/undotbs1.258.832197065

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
41      Incr 0  128.00K    SBT_TAPE    00:01:55     2014-12-29:09:26:59
        BP Key: 43   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1nprcafg_1_1   Media: RMAN-DEFAULT-000006
  SPFILE Included: Modification time: 2014-12-29:09:03:03
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
42      Incr 0  69.63M     SBT_TAPE    00:01:57     2014-12-29:09:27:00
        BP Key: 44   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1kprcaff_1_1   Media: RMAN-DEFAULT-000005
  List of Datafiles in backup set 42
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5    0  Incr 1923520    2014-12-29:09:25:04 +DATA/orcl/datafile/example.265.832197353

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
43      Incr 0  10.25M     SBT_TAPE    00:02:08     2014-12-29:09:27:12
        BP Key: 45   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1mprcafg_1_1   Media: RMAN-DEFAULT-000006
  Control File Included: Ckp SCN: 1923521      Ckp time: 2014-12-29:09:25:04

BS Key  Size       Device Type Elapsed Time Completion Time   
------- ---------- ----------- ------------ -------------------
44      64.00K     SBT_TAPE    00:00:23     2014-12-29:09:27:46
        BP Key: 46   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCRO
        Handle: 1oprcajr_1_1   Media: station4-000004

  List of Archived Logs in backup set 44
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    102     1923493    2014-12-29:09:24:35 1923574    2014-12-29:09:27:22

BS Key  Size       Device Type Elapsed Time Completion Time   
------- ---------- ----------- ------------ -------------------
45      128.00K    SBT_TAPE    00:00:24     2014-12-30:01:31:15
        BP Key: 47   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1ppre32b_1_1   Media: station4-000004

  List of Archived Logs in backup set 45
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    103     1923574    2014-12-29:09:27:22 1923820    2014-12-30:01:30:46

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
46      Incr 0  436.25M    SBT_TAPE    00:01:03     2014-12-30:01:32:21
        BP Key: 48   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1rpre336_1_1   Media: station4-000005
  List of Datafiles in backup set 46
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  2    0  Incr 1923857    2014-12-30:01:31:18 +DATA/orcl/datafile/sysaux.257.832197065

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
47      Incr 0  596.13M    SBT_TAPE    00:01:17     2014-12-30:01:32:35
        BP Key: 49   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1qpre336_1_1   Media: station4-000004
  List of Datafiles in backup set 47
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1    0  Incr 1923855    2014-12-30:01:31:18 +DATA/orcl/datafile/system.256.832197063

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
48      Incr 0  1.81M      SBT_TAPE    00:01:42     2014-12-30:01:33:00
        BP Key: 50   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1spre336_1_1   Media: RMAN-DEFAULT-000005
  List of Datafiles in backup set 48
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  3    0  Incr 1923858    2014-12-30:01:31:18 +DATA/orcl/datafile/undotbs1.258.832197065

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
49      Incr 0  5.69M      SBT_TAPE    00:01:51     2014-12-30:01:33:09
        BP Key: 51   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1upre336_1_1   Media: RMAN-DEFAULT-000006
  List of Datafiles in backup set 49
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  4    0  Incr 1897631    2014-12-28:07:56:58 +DATA/orcl/datafile/users.259.867485943

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
50      Incr 0  128.00K    SBT_TAPE    00:02:07     2014-12-30:01:33:26
        BP Key: 52   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 20pre337_1_1   Media: RMAN-DEFAULT-000006
  SPFILE Included: Modification time: 2014-12-29:09:03:03
  SPFILE db_unique_name: ORCL

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
51      Incr 0  69.63M     SBT_TAPE    00:02:19     2014-12-30:01:33:37
        BP Key: 53   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1tpre336_1_1   Media: RMAN-DEFAULT-000005
  List of Datafiles in backup set 51
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  5    0  Incr 1923859    2014-12-30:01:31:18 +DATA/orcl/datafile/example.265.832197353

BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
------- ---- -- ---------- ----------- ------------ -------------------
52      Incr 0  10.25M     SBT_TAPE    00:02:32     2014-12-30:01:33:51
        BP Key: 54   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 1vpre337_1_1   Media: RMAN-DEFAULT-000006
  Control File Included: Ckp SCN: 1923859      Ckp time: 2014-12-30:01:31:19

BS Key  Size       Device Type Elapsed Time Completion Time   
------- ---------- ----------- ------------ -------------------
53      64.00K     SBT_TAPE    00:00:24     2014-12-30:01:34:22
        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCRO
        Handle: 21pre386_1_1   Media: station4-000004

  List of Archived Logs in backup set 53
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    104     1923820    2014-12-30:01:30:46 1923920    2014-12-30:01:33:58

RMAN>

botang

RMAN> list incarnation;   ------这是现在的状态
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1359978017       PARENT  1          2009-08-13:23:00:48
2       2       ORCL     1359978017       CURRENT 754488     2013-11-22:21:54:46
3       3       ORCL     1359978017       ORPHAN  1990832    2014-12-31:02:40:09
4       4       ORCL     1359978017       ORPHAN  1992181    2014-12-31:03:58:57

根据你这么多化身的情况，可能要先
reset database to incarnation 2(或3)才能找到备份。

chenghuaming

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL     1359978017       PARENT  1          2009-08-13:23:00:48
2       2       ORCL     1359978017       PARENT  754488     2013-11-22:21:54:46
3       3       ORCL     1359978017       CURRENT 1990832    2014-12-31:02:40:09
4       4       ORCL     1359978017       ORPHAN  1992181    2014-12-31:03:58:57

RMAN>
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/04/2015 02:21:01
RMAN-20506: no backup of archived log found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 104 and starting SCN of 1923820 found to restore

RMAN>

run {
sql "alter session set nls_date_format=''YYYY-MM-DD:HH24:MI:SS''";
set until time ' 2014-12-31:02:42:09';
restore database;
recover database;
}
-----------
用日志序列号恢复，用时间恢复（选择3号化身），都不行， 能不能把库先打开

botang

chenghuaming 发表于 2017-1-2 22:31
RMAN> list incarnation of database;

设成2号看看。
这就是随便做了一个时间点恢复后，人为复杂了环境做成的。

做不完全恢复，本身就有可能对数据库造成新的破坏。所以生产环境中要备份现场再做

chenghuaming

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> run {
2> set until sequence 7 thread 1;
3> restore database ;
4> recover database;
5> }

executing command: SET until clause

Starting restore at 2015-01-04:02:45:18
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_SBT_TAPE_3
using channel ORA_SBT_TAPE_4
using channel ORA_SBT_TAPE_5
using channel ORA_SBT_TAPE_6
using channel ORA_SBT_TAPE_7
using channel ORA_SBT_TAPE_8
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/04/2015 02:45:19
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

RMAN>
-------------
2号化身。不认识备份，