对restore database preview显示结果的思考

推荐原创

zhangxuwl 2012-11-28 17:08:27 博主文章分类：oracle ©著作权

文章标签 restore database pre 文章分类 数字化转型

©著作权归作者所有：来自51CTO博客作者zhangxuwl的原创作品，请联系作者获取转载授权，否则将追究法律责任

官方说明：

You can apply RESTORE ... PREVIEW to any RESTORE operation to create a detailed list of every backup to be used in the requested RESTORE operation, and the necessary target SCN for recovery after the RESTORE operation is complete. This command accesses the RMAN repository to query the backup metadata, but does not actually read the backup files to ensure that they can be restored.

上面大致说了两点:

1 restore………preview命令给出恢复restore操作所需要的备份文件列表。

2 restore………preview命令实际上不读取备份文件以确认备份能够恢复。

二：restore database preview 显示结果

RMAN> restore database preview;

Starting restore at 22-NOV-12

using channel ORA_DISK_1

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

5 Full 568.48M DISK 00:00:58 20-NOV-12

BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20121120T084422

Piece Name: /backup/07nqp236_1_1

List of Datafiles in backup set 5

File LV Type Ckp SCN Ckp Time Name

---- -- ---- ---------- --------- ----

1 Full 2150426643 20-NOV-12 /oracle/CRM2/system1.dbf

2 Full 2150426643 20-NOV-12 /oracle/CRM2/zxb.dbf

3 Full 2150426643 20-NOV-12 /oracle/CRM2/CRM/sysaux01.dbf

4 Full 2150426643 20-NOV-12 /oracle/CRM2/CRM/users01.dbf

5 Full 2150426643 20-NOV-12 /oracle/CRM2/zxa.dbf

6 Full 2150426643 20-NOV-12 /oracle/CRM2/CRM/test1.dbf

7 Full 2150426643 20-NOV-12 /oracle/CRM2/zxc.dbf

8 Full 2150426643 20-NOV-12 /oracle/CRM2/CRM/undotbs1.dbf

List of Archived Log Copies

Key Thrd Seq S Low Time Name

------- ---- ------- - --------- ----

35 1 2 A 20-NOV-12 /oracle/archive/1_2_799830099.dbf

36 1 3 A 20-NOV-12 /oracle/archive/1_3_799830099.dbf

37 1 4 A 20-NOV-12 /oracle/archive/1_4_799830099.dbf

38 1 5 A 20-NOV-12 /oracle/archive/1_5_799830099.dbf

39 1 6 A 20-NOV-12 /oracle/archive/1_6_799830099.dbf

40 1 7 A 20-NOV-12 /oracle/archive/1_7_799830099.dbf

41 1 8 A 20-NOV-12 /oracle/archive/1_8_799830099.dbf

42 1 9 A 21-NOV-12 /oracle/archive/1_9_799830099.dbf

43 1 10 A 21-NOV-12 /oracle/archive/1_10_799830099.dbf

44 1 11 A 21-NOV-12 /oracle/archive/1_11_799830099.dbf

45 1 12 A 21-NOV-12 /oracle/archive/1_12_799830099.dbf

46 1 13 A 21-NOV-12 /oracle/archive/1_13_799830099.dbf

47 1 14 A 21-NOV-12 /oracle/archive/1_14_799830099.dbf

48 1 15 A 22-NOV-12 /oracle/archive/1_15_799830099.dbf

49 1 16 A 22-NOV-12 /oracle/archive/1_16_799830099.dbf

50 1 17 A 22-NOV-12 /oracle/archive/1_17_799830099.dbf

51 1 18 A 22-NOV-12 /oracle/archive/1_18_799830099.dbf

52 1 19 A 22-NOV-12 /oracle/archive/1_19_799830099.dbf

53 1 20 A 22-NOV-12 /oracle/archive/1_20_799830099.dbf

54 1 21 A 22-NOV-12 /oracle/archive/1_21_799830099.dbf

55 1 22 A 22-NOV-12 /oracle/archive/1_22_799830099.dbf

56 1 23 A 22-NOV-12 /oracle/archive/1_23_799830099.dbf

57 1 24 A 22-NOV-12 /oracle/archive/1_24_799830099.dbf

58 1 25 A 22-NOV-12 /oracle/archive/1_25_799830099.dbf

59 1 26 A 22-NOV-12 /oracle/archive/1_26_799830099.dbf

60 1 27 A 22-NOV-12 /oracle/archive/1_27_799830099.dbf

Media recovery start SCN is 2150426643

Recovery must be done beyond SCN 2150426643 to clear data files fuzziness

Finished restore at 22-NOV-12

----------------------------------------------------------------------------------------

对于上面的信息的一点解释：

介质恢复的开始是2150426643：Media recovery start SCN is 2150426643

以及开始应用的归档文件seq 号2：/oracle/archive/1_2_799830099.dbf

都是由restore database后,数据文件头部信息决定，如下为转储数据文件头部信息：

Tablespace #2 - SYSAUX rel_fn:3

Creation at scn: 0x0000.00001896 10/22/2005 21:44:46

Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0

reset logs count:0x2fac7053 scn: 0x0000.802c8c23 reset logs terminal rcv data:0x0 scn: 0x0000.00000000

prev reset logs count:0x2fac6f51 scn: 0x0000.802c3dfd prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000

recovered at 11/22/2012 14:18:39

status:0x0 root dba:0x00000000 chkpt cnt: 1114 ctl cnt:1113

begin-hot-backup file size: 0

Checkpointed at scn: 0x0000.802ce813 11/20/2012 08:44:22 该scn值决定了数据文件恢复的起始 ( scn 0x802ce813转换10进制即为2150426643)

thread:1 rba:(0x2.eb3.10) 此处rba 0x2决定了应用归档开始的seq。

对于上面信息的一点误解：

由于列出来的归档seq号为2到27所以误以为recover会应用归档seq从 2到27，之后再应用current redo log直到其结尾。

三：跟踪recover恢复过程

而实际上recover database 恢复过程应用归档为2到22。

Restore database 后，通过查询视图v$recovery_log可查的恢复过程需要的归档seq为2-22号归档。.

SQL> select * from v$recovery_log;

THREAD# SEQUENCE# TIME ARCHIVE_NAME

---------- ---------- --------- ----------------------------------------

1 2 20-NOV-12 /oracle/archive/1_2_799830099.dbf

1 3 20-NOV-12 /oracle/archive/1_3_799830099.dbf

1 4 20-NOV-12 /oracle/archive/1_4_799830099.dbf

1 5 20-NOV-12 /oracle/archive/1_5_799830099.dbf

1 6 20-NOV-12 /oracle/archive/1_6_799830099.dbf

1 7 20-NOV-12 /oracle/archive/1_7_799830099.dbf

1 8 20-NOV-12 /oracle/archive/1_8_799830099.dbf

1 9 21-NOV-12 /oracle/archive/1_9_799830099.dbf

1 10 21-NOV-12 /oracle/archive/1_10_799830099.dbf

1 11 21-NOV-12 /oracle/archive/1_11_799830099.dbf

1 12 21-NOV-12 /oracle/archive/1_12_799830099.dbf

THREAD# SEQUENCE# TIME ARCHIVE_NAME

---------- ---------- --------- ----------------------------------------

1 13 21-NOV-12 /oracle/archive/1_13_799830099.dbf

1 14 21-NOV-12 /oracle/archive/1_14_799830099.dbf

1 15 22-NOV-12 /oracle/archive/1_15_799830099.dbf

1 16 22-NOV-12 /oracle/archive/1_16_799830099.dbf

1 17 22-NOV-12 /oracle/archive/1_17_799830099.dbf

1 18 22-NOV-12 /oracle/archive/1_18_799830099.dbf

1 19 22-NOV-12 /oracle/archive/1_19_799830099.dbf

1 20 22-NOV-12 /oracle/archive/1_20_799830099.dbf

1 21 22-NOV-12 /oracle/archive/1_21_799830099.dbf

1 22 22-NOV-12 /oracle/archive/1_22_799830099.dbf

21 rows selected.

跟踪了rman的recover过程，应用归档以及日志过程如下：

Start recovery at thread 1 ckpt scn 2150426643 logseq 2 block 3763

*** 2012-11-22 14:50:17.753

Media Recovery add redo thread 1

EXEC #3:c=43993,e=1046443,p=8,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1321842790813978

ERROR #3:err=279 tim=436944789

XCTEND rlbk=0, rd_only=1

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842794851740 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=767,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842794851734

*** 2012-11-22 14:50:21.928

Media Recovery Log /oracle/archive/1_2_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795236155 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=468,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795236151

*** 2012-11-22 14:50:22.321

Media Recovery Log /oracle/archive/1_3_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795265680 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=1000,e=525,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795265675

*** 2012-11-22 14:50:22.352

Media Recovery Log /oracle/archive/1_4_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795285533 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=359,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795285529

*** 2012-11-22 14:50:22.372

Media Recovery Log /oracle/archive/1_5_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795345122 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=342,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795345118

*** 2012-11-22 14:50:22.433

Media Recovery Log /oracle/archive/1_6_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795363994 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=0,e=391,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795363990

*** 2012-11-22 14:50:22.452

Media Recovery Log /oracle/archive/1_7_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842795384043 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=332,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842795384040

*** 2012-11-22 14:50:22.473

Media Recovery Log /oracle/archive/1_8_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842804457560 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=370,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842804457556

*** 2012-11-22 14:50:31.764

Media Recovery Log /oracle/archive/1_9_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842812587561 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=0,e=507,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842812587556

*** 2012-11-22 14:50:40.089

Media Recovery Log /oracle/archive/1_10_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842812693837 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=1000,e=44536,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842812693833

*** 2012-11-22 14:50:40.198

Media Recovery Log /oracle/archive/1_11_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842812721209 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=753,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842812721202

*** 2012-11-22 14:50:40.226

Media Recovery Log /oracle/archive/1_12_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842812743052 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=1000,e=526,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842812743048

*** 2012-11-22 14:50:40.249

Media Recovery Log /oracle/archive/1_13_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842812816218 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=358,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842812816213

*** 2012-11-22 14:50:40.323

Media Recovery Log /oracle/archive/1_14_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842814087856 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=451,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842814087850

*** 2012-11-22 14:50:41.626

Media Recovery Log /oracle/archive/1_15_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842815929158 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=0,e=446,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842815929153

*** 2012-11-22 14:50:43.511

Media Recovery Log /oracle/archive/1_16_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842816552606 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=394,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842816552602

*** 2012-11-22 14:50:44.150

Media Recovery Log /oracle/archive/1_17_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842816961431 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=380,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842816961427

*** 2012-11-22 14:50:44.568

Media Recovery Log /oracle/archive/1_18_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842818513605 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=0,e=356,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842818513601

*** 2012-11-22 14:50:46.158

Media Recovery Log /oracle/archive/1_19_799830099.dbf

=====================

PARSING IN CURSOR #2 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842820154621 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #2:c=0,e=504,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842820154616

*** 2012-11-22 14:50:47.838

Media Recovery Log /oracle/archive/1_20_799830099.dbf

=====================

PARSING IN CURSOR #1 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842822606041 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #1:c=0,e=540,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842822606036

*** 2012-11-22 14:50:50.348

Media Recovery Log /oracle/archive/1_21_799830099.dbf

=====================

PARSING IN CURSOR #3 len=44 dep=0 uid=0 oct=35 lid=0 tim=1321842823929608 hv=2522010750 ad=\'72d39c08\'

ALTER DATABASE RECOVER CONTINUE DEFAULT

END OF STMT

PARSE #3:c=1000,e=842,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1321842823929598

*** 2012-11-22 14:50:51.704

Media Recovery Log /oracle/archive/1_22_799830099.dbf

*** 2012-11-22 14:50:51.949

Recovery of Online Redo Log: Thread 1 Group 5 Seq 23 Reading mem 0

*** 2012-11-22 14:50:53.872

Recovery of Online Redo Log: Thread 1 Group 6 Seq 24 Reading mem 0

*** 2012-11-22 14:50:56.779

Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0

*** 2012-11-22 14:50:56.891

Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0

*** 2012-11-22 14:50:57.413

Recovery of Online Redo Log: Thread 1 Group 3 Seq 27 Reading mem 0

*** 2012-11-22 14:50:58.499

Recovery of Online Redo Log: Thread 1 Group 4 Seq 28 Reading mem 0

很清晰的显示了recover过程，应用归档2-22，联机日志23-28。recover的结尾为当前联机日志的最后一个重做记录。

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#

------------------

2150707413

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

------------------

2150707413

----- Redo read statistics for thread 1 -----

Read rate (ASYNC): 74309Kb in 40.78s => 1.78 Mb/sec

Total physical reads: 77642Kb

Longest record: 23Kb, moves: 0/186197 (0%)

Change moves: 72487/353963 (20%), moved: 33Mb

Longest LWN: 2004Kb, moves: 13/2879 (0%), moved: 6Mb

Last redo scn: 0x0000.803130d4 (2150707412)

总结：

1 restore database后recover 应用归档的结尾：所有联机日志中最小seq号的前一个

2 restore database 后recover应用归档的开始seq号由restore后数据文件头部信息决定

3 restore……preview 虽然对列出恢复所需的文件列表不进行有效性检查，不过我们可以通过命令如 restore validate database|restore validate archivelog sequence between xx and xx 对给出的恢复文件列表进行验证。