数据库恢复起点判定(使用新控制文件)

来源：互联网发布：byte数组 0xff 编辑：程序博客网时间：2024/05/21 11:35

下面是本人做的信息截自我做的一次恢复演练，我使用了新的控制文件来恢复数据库(控制文件在数据库全备之后)，通过下面的信息我们可以知道当使用新的控制文件进行数据库恢复时，oracle是如果确定恢复的起始scn，从而确定起始日志的sequence

1.restore控制文件，restore数据文件

2.recover database

SQL> recover database using backup controlfile;ORA-00279: change 308896612927 generated at 04/05/2015 12:55:09 needed forthread 2ORA-00289: suggestion :/data02/archlog/recovertest/recovertest2_104383_843846456.arcORA-00280: change 308896612927 for thread 2 is in sequence #104383Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/data02/archlog/recovertest/recovertest2_104383_843846456.arcORA-00279: change 308896612927 generated at 04/05/2015 12:53:12 needed forthread 1ORA-00289: suggestion :/data02/archlog/recovertest/recovertest1_102008_843846456.arcORA-00280: change 308896612927 for thread 1 is in sequence #102008Specify log: {<RET>=suggested | filename | AUTO | CANCEL}/data02/archlog/recovertest/recovertest1_102008_843846456.arcORA-00279: change 308896641255 generated at 04/05/2015 12:55:16 needed forthread 1ORA-00289: suggestion :/data02/archlog/recovertest/recovertest1_102009_843846456.arcORA-00280: change 308896641255 for thread 1 is in sequence #102009ORA-00278: log file'/data02/archlog/recovertest/recovertest1_102008_843846456.arc' no longerneeded for this recovery

##可以看出需要从实例2的104383号归档开始恢复

查看v$recovery_log结果如下：

select min(sequence#) from v$recovery_log where thread#=1; >>>>结果为 102008select min(sequence#) from v$recovery_log where thread#=2; >>>>结果为 104383

##表示恢复从实例1的102008开始，实例2的104383开始

3.查看log_history中相关日志的scn信息

SQL> col next_change# format 999,999,999,999;         SQL> select thread#,sequence#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE# from v$log_history order by 3;         1     102008  308,896,364,623 2015-04-05 12:53:12  308,896,641,255         2     104383  308,896,365,561 2015-04-05 12:53:13  308,896,642,156         1     102009  308,896,641,255 2015-04-05 12:55:16  308,896,863,658         2     104384  308,896,642,156 2015-04-05 12:55:16  308,896,864,422         1     102010  308,896,863,658 2015-04-05 12:57:07  308,897,781,966         2     104385  308,896,864,422 2015-04-05 12:57:07  308,897,531,840         2     104386  308,897,531,840 2015-04-05 13:03:41  308,899,979,309         1     102011  308,897,781,966 2015-04-05 13:05:46  308,899,542,129         1     102012  308,899,542,129 2015-04-05 13:26:06  308,900,435,156         2     104387  308,899,979,309 2015-04-05 13:33:37  308,900,413,885         2     104388  308,900,413,885 2015-04-05 13:42:40  308,900,619,471         1     102013  308,900,435,156 2015-04-05 13:42:55  308,900,643,362         2     104389  308,900,619,471 2015-04-05 13:44:38  308,900,833,529         1     102014  308,900,643,362 2015-04-05 13:44:53  308,900,903,395.............................................................................................                 2     104497  308,953,124,809 2015-04-05 22:57:47  308,953,312,332         1     102132  308,953,140,168 2015-04-05 22:57:59  308,953,339,089         2     104498  308,953,312,332 2015-04-05 22:59:35  308,954,036,150         1     102133  308,953,339,089 2015-04-05 22:59:53  308,954,036,147         1     102134  308,954,036,147 2015-04-05 23:06:05  308,956,079,240         2     104499  308,954,036,150 2015-04-05 23:06:05  308,956,150,254         1     102135  308,956,079,240 2015-04-05 23:30:15  308,956,150,213##以上为截取的部分信息

4.检查数据库检查点

SQL> col CHECKPOINT_CHANGE# format 999,999,999,999;SQL> col CONTROLFILE_CHANGE# format 999,999,999,999;SQL> select CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# from v$database;CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#------------------ -------------------   308,956,150,213     308,956,466,216

5.检查控制文件中的数据文件检查点

SQL> select file#,status,CHECKPOINT_CHANGE# from v$datafile where status='ONLINE' or status='SYSTEM' order by 3;     FILE# STATUS  CHECKPOINT_CHANGE#---------- ------- ------------------         1 SYSTEM     308,954,036,150      1003 ONLINE     308,954,036,150         3 ONLINE     308,954,036,150         4 ONLINE     308,954,036,150        13 ONLINE     308,954,036,150         2 ONLINE     308,954,036,150       983 ONLINE     308,954,036,150       918 ONLINE     308,954,036,150       856 ONLINE     308,954,036,150       743 ONLINE     308,954,036,150       733 ONLINE     308,954,036,150       486 ONLINE     308,956,150,213       530 ONLINE     308,956,150,213       570 ONLINE     308,956,150,213       660 ONLINE     308,956,150,213       203 ONLINE     308,956,150,213       387 ONLINE     308,956,150,213       336 ONLINE     308,956,150,213       230 ONLINE     308,956,150,213       204 ONLINE     308,956,150,213       439 ONLINE     308,956,150,21321 rows selected.

6.检查数据文件头的数据文件检查点

SQL> select file#,status,CHECKPOINT_CHANGE# from v$datafile_header where status='ONLINE' ORDER BY 3;     FILE# STATUS  CHECKPOINT_CHANGE#---------- ------- ------------------       856 ONLINE     308,896,612,927  >>>>>最小的数据文件scn(包含在实例1的102008开始，实例2的104383归档中       570 ONLINE     308,896,623,428       439 ONLINE     308,896,625,566      1003 ONLINE     308,896,627,156       660 ONLINE     308,905,788,858       530 ONLINE     308,906,056,288         4 ONLINE     308,925,397,029       983 ONLINE     308,925,462,664       204 ONLINE     308,925,585,054       486 ONLINE     308,925,585,054       336 ONLINE     308,926,143,162         3 ONLINE     308,926,143,162         2 ONLINE     308,926,143,162       733 ONLINE     308,933,830,692         1 ONLINE     308,933,830,692       387 ONLINE     308,933,917,122        13 ONLINE     308,933,968,723       230 ONLINE     308,943,247,932       203 ONLINE     308,943,323,261       743 ONLINE     308,943,330,393       918 ONLINE     308,943,330,39321 rows selected.

7.总结

通过上面的分析我们可以看出使用新的控制文件恢复数据库时，恢复的起点是由数据文件头记录的最小的scn决定的，如本例中恢复的起点为856号文件的数据文件头scn

0 0