控制檔案丟失恢復例項(2) - 控制檔案備份後物理結構未變化

 

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;

     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             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;

     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             600233
         2             600233
         3             600233
         4             600233
         5             600233
         6             600233

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;

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

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>