今天发现 control file的问题,和datafile 里的checkpoint 不一致,导致database 不能open ,下面来说说啥时要用到using backup controlfile:
SQL> select checkpoint_change# from v$datafile;
CHECKPOINT_CHANGE#
------------------
752853
752853
752853
752853
752853
SQL> select checkpoint_change# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
752526
752526
752526
752526
752526
以上说明control file 比datafile 新,所以不要用 using backup controlfile.
下面说说control 文件比较旧的恢复 :
SQL> select CHECKPOINT_CHANGE# from v$datafile;
CHECKPOINT_CHANGE#
------------------
752853
752853
752853
752853
752853
SQL> select CHECKPOINT_CHANGE# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
754954
754954
754954
754954
754954
SQL> recover database ;
ORA-00283: 恢复会话因错误而取消
ORA-19909: 数据文件 1 属于孤立的原型
ORA-01110: 数据文件 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF'
SQL> recover database using backup controlfile;
ORA-00283: 恢复会话因错误而取消
ORA-19909: 数据文件 1 属于孤立的原型
ORA-01110: 数据文件 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF'
SQL> recover database until cancel;
ORA-00283: 恢复会话因错误而取消
ORA-01610: 使用 BACKUP CONTROLFILE 选项的恢复必须已完成
SQL> recover database until cancel using backup controlfile;
ORA-00283: 恢复会话因错误而取消
ORA-19909: 数据文件 1 属于孤立的原型
ORA-01110: 数据文件 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SHENG\SYSTEM01.DBF'
解决方法:
1: 重建controlfile:
2:
SQL> select CHECKPOINT_CHANGE# from v$datafile;
CHECKPOINT_CHANGE#
------------------
754954
754954
754954
754954
754954
SQL> select CHECKPOINT_CHANGE# from v$datafile_header;
CHECKPOINT_CHANGE#
------------------
754954
754954
754954
754954
754954
SQL> recover database until cancel using backup controlfile;
ORA-00279: ?? 754954 (? 01/09/2012 11:28:21 ??) ???? 1 ????
ORA-00289: ??: D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100001772108706.ARC
ORA-00280: ?? 754954 (???? 1) ??? #1 ?
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100001772108706.ARC
ORA-00308: ????????
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100001772108706.ARC'
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: ????????
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100001772108706.ARC'
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
ORA-00308: ????????
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCH\00100001772108706.ARC'
ORA-27041: ??????
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????
SQL> alter database open resetlogs;
数据库已更改。
SQL> select * from demo.a;
I
----------
1
2
3
4
5
6
已选择6行。
发现数据没有少,很成功!