控制文件恢复之使用旧的建立新的控制文件实验

场景表述:
 归档的数据库
 1.有控制文件的二进制文件备份
 2.控制文件全部损坏,数据库正在交易崩溃
 3.取回老的二进制控制文件
 4.能起到mount 但控制文件是以前的备份(旧的),数据文件因为是不一致性停库,需要恢复
 5.因为控制文件是旧的 就不能以控制文件内的信息为基准去恢复了.
   告诉数据库控制文件是老的,只能以归档和在线日志为准取恢复(不完全恢复)

 

SQL> alter database backup controlfile to '/u01/oracle/control.bk';

Database altered.

SQL>

 

产生交易
update scott.emp set sal=sal+1;
commit;
alter system switch logfile;

 

非一致性停库 或者将所有控制文件破坏
SQL> shut abort
ORACLE instance shut down.
SQL>
SQL>

如果是破坏了控制文件 切换一次日志 数据就会崩溃
SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE

alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL>

 

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size    113247824 bytes
Database Buffers   167772160 bytes
Redo Buffers      2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

恢复备份的二进制控制文件
SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control01.ctl

SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control02.ctl

SQL> ! cp /u01/oracle/myctl.ctl /u01/oracle/oradata/db20/control03.ctl

SQL>

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

在做resetlogs启数据库 也会因为数据文件内部一致而中止,需要恢复

SQL>
SQL>
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/db254/system01.dbf'

SQL>
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

数据库检测到控制文件和数据文件SCN的对比,发现控制文件是老的
数据库如何发现控制文件是老的?

SQL>

SQL> select name,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

NAME         CHECKPOINT_CHANGE# LAST_CHANGE#
-------------------------------------------------- ------------------ ------------
/db254/system01.dbf           1079166
/db254/undotbs01.dbf           1079166
/db254/sysaux01.dbf           1079166
/db254/users01.dbf           1079166
/db254/mytbs01.dbf           1079166
/db254/ind_tbs01.dbf           1079166
/db254/wb.dbf            1079166

7 rows selected.

SQL>  控制文件中记录的stopSCN是空 说明上一次没有一致性停库

控制文件中的记录比实际文件头中的记录要晚
SQL> select file#,RECOVER,FUZZY,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
  1     YES       1079549
  2     YES       1079549
  3     YES       1079549
  4     YES       1079549
  5     YES       1079549
  6     YES       1079549
  7     YES       1079549

7 rows selected.

SQL> 

SQL> recover database using backup controlfile;
ORA-00279: change 1079347 generated at 03/06/2011 17:14:34 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/1_1_745089274.arc
ORA-00280: change 1079347 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1079526 generated at 03/06/2011 17:20:02 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/2_1_745089274.arc
ORA-00280: change 1079526 for thread 1 is in sequence #2
ORA-00278: log file '/home/oracle/arc1/1_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079528 generated at 03/06/2011 17:20:03 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/3_1_745089274.arc
ORA-00280: change 1079528 for thread 1 is in sequence #3
ORA-00278: log file '/home/oracle/arc1/2_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079536 generated at 03/06/2011 17:20:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/4_1_745089274.arc
ORA-00280: change 1079536 for thread 1 is in sequence #4
ORA-00278: log file '/home/oracle/arc1/3_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079538 generated at 03/06/2011 17:20:16 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/5_1_745089274.arc
ORA-00280: change 1079538 for thread 1 is in sequence #5
ORA-00278: log file '/home/oracle/arc1/4_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079541 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/6_1_745089274.arc
ORA-00280: change 1079541 for thread 1 is in sequence #6
ORA-00278: log file '/home/oracle/arc1/5_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079543 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/7_1_745089274.arc
ORA-00280: change 1079543 for thread 1 is in sequence #7
ORA-00278: log file '/home/oracle/arc1/6_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079545 generated at 03/06/2011 17:20:20 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/8_1_745089274.arc
ORA-00280: change 1079545 for thread 1 is in sequence #8
ORA-00278: log file '/home/oracle/arc1/7_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079547 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/9_1_745089274.arc
ORA-00280: change 1079547 for thread 1 is in sequence #9
ORA-00278: log file '/home/oracle/arc1/8_1_745089274.arc' no longer needed for this recovery

ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10
ORA-00278: log file '/home/oracle/arc1/9_1_745089274.arc' no longer needed for this recovery

ORA-00308: cannot open archived log '/home/oracle/arc1/10_1_745089274.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL>
这个文件还没归档 因为是current组 还没来得及切换日志(切换日志才能归档) 提供redo给它 一组组尝试

SQL> recover database using backup controlfile;
ORA-00279: change 1079549 generated at 03/06/2011 17:20:25 needed for thread 1
ORA-00289: suggestion : /home/oracle/arc1/10_1_745089274.arc
ORA-00280: change 1079549 for thread 1 is in sequence #10

Specify log: {=suggested | filename | AUTO | CANCEL}
/db254/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL>

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

转载于:http://blog.itpub.net/24756465/viewspace-722848/