[转帖][分享]restore/recovery后数据库不能以readonly模式打开

5dm · 发表于 2012-12-21 13:05:10

restore/recovery后数据库不能以readonly模式打开

BR><span id="sp" class="sp"></span>Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.1.0.4
Information in this document applies to any platform.
本文阐述数据库以readonly模式打开失败的原因，并讨论解决的方法
Scope and Application
这主要是针对DBA和ORACLE 的技术支持人员
Unable to open the database in read only mode after restore/recovery
当执行完不完全恢复并正式以read write模式打开数据库之前，需要以read only模式打开数据库检查和核实数据。本文讨论了打开数据库成功和失败的两种情况
1）运用当前的控制文件挂载数据库，应用之前的冷备恢复数据文件，并且使用"RECOVER DATABASE UNTIL CANCEL"前滚数据库
SQL> alter database open read only; fails with alter database open read only
ERROR at line 1:
ORA-16005: database requires recovery
原因：，"Recover Database "命令应用当前的控制文件，不能够使得预期recovery marker结束，如果执行了不完全恢复，数据库就会在以read only模式打开时会失败。要先以read write模式打开数据库，再尝试使用read only模式打开
2）当控制文件被重建，数据文件通过冷备进行恢复
create controlfile with resetlogs / NORESETLOGS 再执行不完全恢复
recover database using backup controlfile until cancel；
应用归档日志
Database fails to open read only .. as
SQL> alter database open read only;失败
ERROR at line 1:
ORA-16008: indeterminate control file checkpoint
原因：当控制文件被重建时，当前controlfile_checkpoint_change#不存在时，不能够以只读模式打开数据库，除非打开前先使用readwrite模式
3）控制文件在进行冷备时早于数据文件的备份
此时，数据文件早于控制文件从备份中恢复
这种情况我们可以通过以下的查询来检查数据文件的header
SQL> select HXFIL File_num,substr(HXFNM,1,40) File_name, FHSCN SCN, FHSTA status ,FHRBA_SEQ Sequence from XKCVFH;
FILE_NUM FILE_NAME SCN STATUS SEQUENCE
---------------------------------------------------------------------------------
1 /opt/oracle/oradata/stby92/system01.dbf 8252208200463 0 24
2 /opt/oracle/oradata/stby92/undo01.dbf 8252208200463 0 24
3 /opt/oracle/oradata/stby92/users01.dbf 8252208200463 0 24
4 /opt/oracle/oradata/stby92/test.dbf 8252208200463 0 24
但是我们使用的冷备的控制文件，oracle会认为它是有效地，甚至当我们使用
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
恢复需要控制文件认为需要（而不是数据文件需要）的归档日志序列
SQL> select checkpoint_change# from vdatabase；
CHECKPOINT_CHANGE#
------------------
8252208200205 ==> coming from controlfile. where as Datafile headers are showing SCN 8252208200463
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 8252208200205 generated at 07/07/2005 16:18:46 needed for
thread 1
ORA-00289: suggestion : /opt/oracle/oradata/stby92/arch/1_22.dbf
ORA-00280: change 8252208200205 for thread 1 is in sequence #22
Specify log: {=suggested | filename | AUTO | CANCEL}
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 8252208200205 generated at 07/07/2005 16:18:46 needed for
thread 1
ORA-00289: suggestion : /opt/oracle/oradata/stby92/arch/1_22.dbf
ORA-00280: change 8252208200205 for thread 1 is in sequence #22
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 8252208200450 generated at 07/07/2005 16:24:09 needed for
thread 1
ORA-00289: suggestion : /opt/oracle/oradata/stby92/arch/1_23.dbf
ORA-00280: change 8252208200450 for thread 1 is in sequence #23
ORA-00278: log file '/opt/oracle/oradata/stby92/arch/1_22.dbf' no longer needed
for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/stby92/system01.dbf'
原因：现在可以说用户要在控制文件和数据文件在对归档日志的需求达到一致之前停止数据库的恢复
在这个例子中，控制文件从Logseq=22开始但是数据文件的每一个查询都已经处在Logseq=23并预计24将作为下一个被应用。
在这个状态下，我们也不可能在read only模式下打开数据库
SQL> alter database open read only;
alter database open read only
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/opt/oracle/oradata/stby92/system01.dbf'
我们需要使用第23个归档文件来满足控制文件和数据文件，这也就意味着：
controlfile_change# of vdatabase = checkpoint_change# of vdatafile_header
（控制文件中的SCN号要与数据文件头部记录的SCN号保持一致）这样就可以以制度的模式打开数据库。
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 8252208200450 generated at 07/07/2005 16:24:09 needed for
thread 1
ORA-00289: suggestion : /opt/oracle/oradata/stby92/arch/1_23.dbf
ORA-00280: change 8252208200450 for thread 1 is in sequence #23
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 8252208200463 generated at 07/07/2005 16:24:44 needed for
thread 1
ORA-00289: suggestion : /opt/oracle/oradata/stby92/arch/1_24.dbf
ORA-00280: change 8252208200463 for thread 1 is in sequence #24
ORA-00278: log file '/opt/oracle/oradata/stby92/arch/1_23.dbf' no longer needed
for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
取消介质恢复
SQL> alter database open read only;
Database altered。打开成功
结论：
要在执行不完全恢复后以制度的模式打开数据库必须要使用"RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL"命令语句
只有当控制文件与之前进行的冷备保持一致或者是使用最新的控制文件以上的执行操作才能成功
当使用重建控制文件无论是以resetlogs 还是noresetlogs形式打开，以上的命令都不可能成功。
Errors
ORA-1110; ORA-1152; ORA-1547; ORA-16004; ORA-16005; ORA-16008; ORA-278; ORA-279; ORA-280; ORA-289

5dm · 发表于 2012-12-21 15:57:31

先设置为read write,再设置为read only就行了

帐号		自动登录	找回密码
密码			注册