控制文件丢失恢复实例(2) - 控制文件备份后物理结构未变化-优快云博客

本文详细记录了一次使用Oracle数据库进行崩溃恢复与控制文件备份的过程，包括插入数据、提交事务、切换日志文件、备份控制文件、崩溃、恢复及重新打开数据库等步骤。通过实际操作演示了数据库在遇到故障时的正确处理方式。

SQL> insert into tony.test04 values (23,'CMM');

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter system switch logfile;

SQL> alter database backup controlfile to 'd:\control.bak' ;

数据库已更改。

SQL> insert into tony.test04 values (24,'CMM');

已创建 1 行

SQL> commit;

提交完成。

SQL>

SQL> alter system switch logfile;

System altered.

SQL> select file# , CHECKPOINT_CHANGE#, Last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1             597163
         2             597163
         3             597163
         4             597163
         5             597163
         6             597163

6 rows selected.

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             597163
         2             597163
         3             597163
         4             597163
         5             597163
         6             597163

6 rows selected.

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL01.CTL

SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL02.CTL

SQL> host del D:\oracle\product\10.2.0\oradata\test\CONTROL03.CTL

SQL> startup
ORACLE 例程已经启动。

Total System Global Area 524288000 bytes
Fixed Size                  1249920 bytes
Variable Size             159387008 bytes
Database Buffers          356515840 bytes
Redo Buffers                7135232 bytes
ORA-00205: ?????????, ??????, ???????

SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL01.CTL
已复制 1 个文件。

SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL02.CTL
已复制 1 个文件。

SQL> host copy d:\CONTROL.BAK D:\oracle\product\10.2.0\oradata\test\CONTROL03.CTL
已复制 1 个文件。

SQL> alter database mount;

SQL> select file# , CHECKPOINT_CHANGE#, Last_change# from v$datafile;

6 rows selected.

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             600233
         2             600233
         3             600233
         4             600233
         5             600233
         6             600233

6 rows selected.

SQL> recover database using backup controlfile;
ORA-00279: change 596757 generated at 04/18/2011 11:40:20 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748784275.001
ORA-00280: change 596757 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 596802 generated at 04/18/2011 11:42:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00004_0748784275.001
ORA-00280: change 596802 for thread 1 is in sequence #4
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00003_0748784275.001' no longer
needed for this recovery

ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00004_0748784275.001'
ORA-27041: unable to open file
OSD-04002: ????????????
O/S-Error: (OS 2) ??????????????????????

SQL> select file# , CHECKPOINT_CHANGE#, Last_change# from v$datafile;

6 rows selected.

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

6 rows selected.

SQL> recover database using backup controlfile;
ORA-00279: change 596802 generated at 04/18/2011 11:42:00 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\ARCH\ARC00004_0748784275.001
ORA-00280: change 596802 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
D:\oracle\product\10.2.0\oradata\test\REDO03.LOG
Log applied.
Media recovery complete.
SQL>

SQL> select file# , CHECKPOINT_CHANGE#, Last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1             597163       600233
         2             597163       600233
         3             597163       600233
         4             597163       600233
         5             597163       600233
         6             597163       600233

6 rows selected.

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

6 rows selected.

SQL> alter database open resetlogs;

Database altered.
SQL> select file# , CHECKPOINT_CHANGE#, Last_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1             600235
         2             600235
         3             600235
         4             600235
         5             600235
         6             600235

6 rows selected.

SQL> select file# , CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1             600235
         2             600235
         3             600235
         4             600235
         5             600235
         6             600235

6 rows selected.

SQL>

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

转载于:http://blog.itpub.net/9390331/viewspace-703865/