ITPub博客

首页 > 数据库 > Oracle > 重建controlfile之后如何快速导入RMAN备份信息

重建controlfile之后如何快速导入RMAN备份信息

原创 Oracle 作者:oliseh 时间:2015-07-29 11:37:29 0 删除 编辑

重建controlfile之后备份信息会丢失,可以使用catalog backuppiece、catalog start with等命令将备份文件信息导回到controlfile,如果备份很多且分散在不同的目录下就需要编辑n条命令来实现导回,而且还会有遗漏;
下面要介绍的resync catalog from controlfilecopy ...命令能够一条命令快速导回所有备份信息,方便快捷且不会遗漏备份信息,这里强烈建议开启controlfile autobackup on,确保控制文件始终包含最新的备份信息


###重建之前,控制文件里包含的备份信息

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time   #Pieces #Copies Compressed Tag
------- -- -- - ----------- ----------------- ------- ------- ---------- ---
31      B  A  A DISK        20150716 15:42:59 1       1       NO         TAG20150716T154257
32      B  F  A DISK        20150716 15:43:34 1       1       NO         TAG20150716T154300
33      B  F  A DISK        20150716 15:43:38 1       1       NO         TAG20150716T154300
34      B  A  A DISK        20150716 15:43:42 1       1       NO         TAG20150716T154341
35      B  A  A DISK        20150716 15:45:16 1       1       NO         TAG20150716T154515
36      B  F  A DISK        20150717 10:10:14 1       1       NO         TAG20150717T100939
37      B  F  A DISK        20150717 10:10:17 1       1       NO         TAG20150717T100939
38      B  A  A DISK        20150717 10:12:21 1       1       NO         TAG20150717T101219
39      B  F  A DISK        20150718 10:24:16 1       1       NO         TAG20150718T102414

RMAN> list backup;


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


BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
31      969.00K    DISK        00:00:00     20150716 15:42:59
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154257
        Piece Name: /oradata06/dupuse/0716/arch_93qc6t43

  List of Archived Logs in backup set 31
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    53      12723364188345 20150716 15:20:30 12723364190001 20150716 15:42:56

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
32      Full    2.01G      DISK        00:00:32     20150716 15:43:34
        BP Key: 32   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154300
        Piece Name: /oradata06/dupuse/0716/whole_94qc6t46
  List of Datafiles in backup set 32
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/system01.dbf
  2       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/users01.dbf
  5       Full 12723364077004 20150708 16:28:01 /oradata06/testaaaaa/ts0422_1.dbf
  6       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/xdbts1.dbf
  9       Full 12723364190030 20150716 15:43:02 /oradata06/omf/TSTDB1/datafile/o1_mf_omftbs1_1kkDQZdsn_.dbf
  10      Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/undotbs3.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
33      Full    13.70M     DISK        00:00:01     20150716 15:43:38
        BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154300
        Piece Name: /oradata06/dupuse/0716/whole_95qc6t59
  SPFILE Included: Modification time: 20150716 13:59:14
  SPFILE db_unique_name: TSTDB1
  Control File Included: Ckp SCN: 12723364190061   Ckp time: 20150716 15:43:34

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
34      26.00K     DISK        00:00:00     20150716 15:43:42
        BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154341
        Piece Name: /oradata06/dupuse/0716/arch_96qc6t5e

  List of Archived Logs in backup set 34
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    54      12723364190001 20150716 15:42:56 12723364190067 20150716 15:43:39

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
35      13.50K     DISK        00:00:00     20150716 15:45:16
        BP Key: 35   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154515
        Piece Name: /oradata06/dupuse/0716/arch_55_59_97qc6t8c

  List of Archived Logs in backup set 35
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    55      12723364190067 20150716 15:43:39 12723364190129 20150716 15:44:19
  1    56      12723364190129 20150716 15:44:19 12723364190133 20150716 15:44:21
  1    57      12723364190133 20150716 15:44:21 12723364190137 20150716 15:44:21
  1    58      12723364190137 20150716 15:44:21 12723364190140 20150716 15:44:21
  1    59      12723364190140 20150716 15:44:21 12723364190143 20150716 15:44:21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
36      Full    2.01G      DISK        00:00:34     20150717 10:10:14
        BP Key: 36   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T100939
        Piece Name: /oradata06/dupuse/0717/whole_db_99qc8tv4
  List of Datafiles in backup set 36
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/system01.dbf
  2       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/users01.dbf
  5       Full 12723364077004 20150708 16:28:01 /oradata06/testaaaaa/ts0422_1.dbf
  6       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/xdbts1.dbf
  9       Full 12723364279194 20150717 10:06:35 /oradata06/omf/TSTDB1/datafile/o1_mf_omftbs1_1kkDQZdsn_.dbf
  10      Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/undotbs3.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
37      Full    13.70M     DISK        00:00:01     20150717 10:10:17
        BP Key: 37   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T100939
        Piece Name: /oradata06/dupuse/0717/whole_db_9aqc8u08
  SPFILE Included: Modification time: 20150717 10:06:48
  SPFILE db_unique_name: TSTDB1
  Control File Included: Ckp SCN: 12723364279194   Ckp time: 20150717 10:06:35

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
38      9.67M      DISK        00:00:00     20150717 10:12:21
        BP Key: 38   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T101219
        Piece Name: /oradata06/dupuse/0717/arch_9bqc8u45

  List of Archived Logs in backup set 38
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    61      12723364271220 20150717 07:44:29 12723364279690 20150717 10:11:52
  1    62      12723364279690 20150717 10:11:52 12723364279693 20150717 10:11:52
  1    63      12723364279693 20150717 10:11:52 12723364279697 20150717 10:11:54

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
39      Full    13.67M     DISK        00:00:02     20150718 10:24:16
        BP Key: 39   Status: AVAILABLE  Compressed: NO  Tag: TAG20150718T102414
        Piece Name: /oradata06/fra/TSTDB1/backupset/2015_07_18/o1_mf_ncnnf_TAG20150718T102414_1kwmdzy70_.bkp
  Control File Included: Ckp SCN: 12723364364980   Ckp time: 20150718 10:24:14


###重建controlfile

shutdown immediate
startup nomount

****使用control trace file进行重建
@/home/tstdb1/ctl.trc.0716                <----由alter database backup controlfile to trace as ... noresetlogs生成的重建脚本


###重建以后备份信息全部丢失

rman target /

RMAN> list backupset;

using target database control file instead of recovery catalog
specification does not match any backup in the repository

rman target / catalog rman/773946@tstdb2                   <---连接catalog后也无法列出备份信息

RMAN> list backup;                                  

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/18/2015 10:43:30
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 07/18/2015 10:43:30
RMAN-20052: invalid datafile create SCN

###连接catalog获取最近一次的controlfile备份信息
rman catalog rman/773946@tstdb2

RMAN> set dbid 2051793563

executing command: SET DBID
database name is "TSTDB1" and DBID is 2051793563

RMAN> list backup of controlfile;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
107942  Full    13.70M     DISK        00:00:01     20150716 15:43:38
        BP Key: 107945   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154300
        Piece Name: /oradata06/dupuse/0716/whole_95qc6t59
  Control File Included: Ckp SCN: 12723364190061   Ckp time: 20150716 15:43:34

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
110506  Full    13.70M     DISK        00:00:01     20150717 10:10:17
        BP Key: 110513   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T100939
        Piece Name: /oradata06/dupuse/0717/whole_db_9aqc8u08
  Control File Included: Ckp SCN: 12723364279194   Ckp time: 20150717 10:06:35

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
110970  Full    13.67M     DISK        00:00:02     20150718 10:24:16
        BP Key: 110974   Status: AVAILABLE  Compressed: NO  Tag: TAG20150718T102414
        Piece Name: /oradata06/fra/TSTDB1/backupset/2015_07_18/o1_mf_ncnnf_TAG20150718T102414_1kwmdzy70_.bkp
  Control File Included: Ckp SCN: 12723364364980   Ckp time: 20150718 10:24:14


####将包含有最新信息的
controlfile恢复到临时路径下
rman target /
RMAN> restore controlfile to '/tmp/ctl_tstdb1.bin' from '/oradata06/fra/TSTDB1/backupset/2015_07_18/o1_mf_ncnnf_TAG20150718T102414_1kwmdzy70_.bkp';

Starting restore at 20150718 10:37:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=200 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20150718 10:37:35

RMAN> host "ls -l /tmp/ctl_tstdb1.bin";

-rw-r-----    1 oracle   oinstall   14303232 Jul 18 10:37 /tmp/ctl_tstdb1.bin
host command complete

####resync catalog报错,因为重建的控制文件SCN信息与RMAN里保存的原有控制文件的SCN信息不一致
rman target / catalog rman/773946@tstdb2

RMAN> resync catalog from controlfilecopy '/tmp/ctl_tstdb1.bin';

starting partial resync of recovery catalog
partial resync complete

RMAN> list backup summary;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/18/2015 10:47:22
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 07/18/2015 10:47:22
RMAN-20052: invalid datafile create SCN

RMAN> list backup;

starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/18/2015 10:47:30
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 07/18/2015 10:47:30
RMAN-20052: invalid datafile create SCN

###unregister->register,再次resync catalog from controlfilecopy ...
rman target / catalog rman/773946@tstdb2

RMAN> unregister database;

database name is "TSTDB1" and DBID is 2051793563

Do you really want to unregister the database (enter YES or NO)? y
database unregistered from the recovery catalog

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> resync catalog from controlfilecopy '/tmp/ctl_tstdb1.bin';

starting partial resync of recovery catalog
partial resync complete

***备份信息成功被找回
RMAN> list backupset;

starting full resync of recovery catalog
full resync complete

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


BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
111451  969.00K    DISK        00:00:00     20150716 15:42:59
        BP Key: 111459   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154257
        Piece Name: /oradata06/dupuse/0716/arch_93qc6t43

  List of Archived Logs in backup set 111451
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    53      12723364188345 20150716 15:20:30 12723364190001 20150716 15:42:56

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
111452  Full    2.01G      DISK        00:00:32     20150716 15:43:34
        BP Key: 111460   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154300
        Piece Name: /oradata06/dupuse/0716/whole_94qc6t46
  List of Datafiles in backup set 111452
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/system01.dbf
  2       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/users01.dbf
  5       Full 12723364077004 20150708 16:28:01 /oradata06/testaaaaa/ts0422_1.dbf
  6       Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/xdbts1.dbf
  9       Full 12723364190030 20150716 15:43:02 /oradata06/omf/TSTDB1/datafile/o1_mf_omftbs1_1kkDQZdsn_.dbf
  10      Full 12723364190030 20150716 15:43:02 /oradata06/testaaaaa/undotbs3.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
111453  Full    13.70M     DISK        00:00:01     20150716 15:43:38
        BP Key: 111461   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154300
        Piece Name: /oradata06/dupuse/0716/whole_95qc6t59
  SPFILE Included: Modification time: 20150716 13:59:14
  SPFILE db_unique_name: TSTDB1
  Control File Included: Ckp SCN: 12723364190061   Ckp time: 20150716 15:43:34

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
111454  26.00K     DISK        00:00:00     20150716 15:43:42
        BP Key: 111462   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154341
        Piece Name: /oradata06/dupuse/0716/arch_96qc6t5e

  List of Archived Logs in backup set 111454
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    54      12723364190001 20150716 15:42:56 12723364190067 20150716 15:43:39

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
111455  13.50K     DISK        00:00:00     20150716 15:45:16
        BP Key: 111463   Status: AVAILABLE  Compressed: NO  Tag: TAG20150716T154515
        Piece Name: /oradata06/dupuse/0716/arch_55_59_97qc6t8c

  List of Archived Logs in backup set 111455
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    55      12723364190067 20150716 15:43:39 12723364190129 20150716 15:44:19
  1    56      12723364190129 20150716 15:44:19 12723364190133 20150716 15:44:21
  1    57      12723364190133 20150716 15:44:21 12723364190137 20150716 15:44:21
  1    58      12723364190137 20150716 15:44:21 12723364190140 20150716 15:44:21
  1    59      12723364190140 20150716 15:44:21 12723364190143 20150716 15:44:21

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
111456  Full    2.01G      DISK        00:00:34     20150717 10:10:14
        BP Key: 111464   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T100939
        Piece Name: /oradata06/dupuse/0717/whole_db_99qc8tv4
  List of Datafiles in backup set 111456
  File LV Type Ckp SCN    Ckp Time          Name
  ---- -- ---- ---------- ----------------- ----
  1       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/system01.dbf
  2       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/sysaux01.dbf
  3       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/undotbs01.dbf
  4       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/users01.dbf
  5       Full 12723364077004 20150708 16:28:01 /oradata06/testaaaaa/ts0422_1.dbf
  6       Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/xdbts1.dbf
  9       Full 12723364279194 20150717 10:06:35 /oradata06/omf/TSTDB1/datafile/o1_mf_omftbs1_1kkDQZdsn_.dbf
  10      Full 12723364279194 20150717 10:06:35 /oradata06/testaaaaa/undotbs3.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 
------- ---- -- ---------- ----------- ------------ -----------------
111457  Full    13.70M     DISK        00:00:01     20150717 10:10:17
        BP Key: 111465   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T100939
        Piece Name: /oradata06/dupuse/0717/whole_db_9aqc8u08
  SPFILE Included: Modification time: 20150717 10:06:48
  SPFILE db_unique_name: TSTDB1
  Control File Included: Ckp SCN: 12723364279194   Ckp time: 20150717 10:06:35

BS Key  Size       Device Type Elapsed Time Completion Time 
------- ---------- ----------- ------------ -----------------
111458  9.67M      DISK        00:00:00     20150717 10:12:21
        BP Key: 111466   Status: AVAILABLE  Compressed: NO  Tag: TAG20150717T101219
        Piece Name: /oradata06/dupuse/0717/arch_9bqc8u45

  List of Archived Logs in backup set 111458
  Thrd Seq     Low SCN    Low Time          Next SCN   Next Time
  ---- ------- ---------- ----------------- ---------- ---------
  1    61      12723364271220 20150717 07:44:29 12723364279690 20150717 10:11:52
  1    62      12723364279690 20150717 10:11:52 12723364279693 20150717 10:11:52
  1    63      12723364279693 20150717 10:11:52 12723364279697 20150717 10:11:54
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/53956/viewspace-1755176/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
不仅仅专注Oracle database技术, member of SHOUG

注册时间:2014-04-06

  • 博文量
    128
  • 访问量
    1617113