接这篇文章:http://blog.itpub.net/26524307/viewspace-1061246/
实验数据也请参考上述文章

4、丢失联机重做日志文件
1）丢失了联机重做日志文件（联机重做日志文件是没有备份的），数据文件是最新的（即从第三次冷备中拷贝），控制文件从第二次冷备中拷贝

因为控制文件是旧的，所以我们通过using backup controlfile来恢复
此时因为没有联机重做日志文件，所以应该是读取数据文件头中最大的SCN来恢复的

关闭数据库，
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 20:36 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 20:47 1_4_797547884.dbf
[oracle@mylinux ~]$ cd /u01/oradata/orcl
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --从第二次冷备中拷贝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_03/*.dbf . --从第三次冷备中拷贝

数据应该是可以完全恢复的，因为数据文件中有最新最全的信息

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             377489288 bytes
Database Buffers          113246208 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file - old control file
--已经提示出错，alert日志中提示了更多的信息，还提示找不到联机重做日志

SQL> recover database using backup controlfile;
ORA-00279: change 2246274 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2246274 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}
auto --这里我直接输入了auto
ORA-00279: change 2246612 generated at 10/27/2012 09:11:18 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_5_797547884.dbf
ORA-00280: change 2246612 for thread 1 is in sequence #5
ORA-00278: log file '/u01/arch/1_4_797547884.dbf' no longer needed for this
recovery

ORA-00308: cannot open archived log '/u01/arch/1_5_797547884.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> alter database open resetlogs;--但是发现/u01/oradata/orcl下面是已经重新生成了联机重做日志文件，但是不知道为什么会出现这些错误，而

且实例，包括后台进程都关闭了
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [2246625], [0],
[2247709], [4194432], [], [], [], [], [], []
Process ID: 6215
Session ID: 1 Serial number: 5

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 30 22:23:44 2012

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup --数据库竟然重新打开了
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             377489288 bytes
Database Buffers          113246208 bytes
Redo Buffers                5922816 bytes
Database mounted.
Database opened.
SQL> select * from hr.fruits;--数据没有丢失

NAME
----------
apple
banana
orange
grape
peach

SQL> select group#,sequence#,status from v$log;--联机重做日志组已经重置

    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          2 CURRENT
         3          0 UNUSED
         4          0 UNUSED

--其实我觉得这次的恢复只是把控制文件中的内容恢复到最新，不必恢复数据文件中的内容，因为数据文件本来就是最新的

2）丢失了联机重做日志文件，数据文件是旧的（从第一次冷备中拷贝），控制文件从第二次冷备中拷贝
看能恢复到什么程度

关闭数据库，
[oracle@mylinux arch]$ ll
total 2064
-rw-r----- 1 oracle oinstall 555520 Oct 28 20:36 1_3_797547884.dbf
-rw-r----- 1 oracle oinstall 1547776 Oct 28 20:47 1_4_797547884.dbf
[oracle@mylinux ~]$ cd /u01/oradata/orcl
[oracle@mylinux orcl]$ rm *.log
[oracle@mylinux orcl]$ rm *.dbf
[oracle@mylinux orcl]$ rm *.ctl
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_02/*.ctl . --从第二次冷备中拷贝
[oracle@mylinux orcl]$ cp /u01/backup/coldbackup/20121027_01/*.dbf . --从第一次冷备中拷贝

SQL> startup --提示需要进行介质恢复
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 497995776 bytes
Fixed Size                  1337464 bytes
Variable Size             377489288 bytes
Database Buffers          113246208 bytes
Redo Buffers                5922816 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'

SQL> recover database;--提示1_3的归档日志需要进行应用
ORA-00279: change 2245011 generated at 10/27/2012 08:46:01 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_3_797547884.dbf
ORA-00280: change 2245011 for thread 1 is in sequence #3

Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '/u01/oradata/orcl/redo04b.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 4 thread 1: '/u01/oradata/orcl/redo04.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01112: media recovery not started

猜想，可能是我们此次恢复的终点是控制文件的最大SCN，而这个终点需要的归档日志文件就是1_3_797547884.dbf，这个归档日志文件应用了之后Oracle

就找联机重做日志文件来应用，而联机重做日志文件没有，他就报错

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database until cancel;--不知道为什么会提示1_4的归档，1_4的归档明明是在第二次冷备之后，也就是说，比控制文件的SCN要大
ORA-00279: change 2245615 generated at 10/27/2012 09:00:51 needed for thread 1
ORA-00289: suggestion : /u01/arch/1_4_797547884.dbf
ORA-00280: change 2245615 for thread 1 is in sequence #4

Specify log: {=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.

--我猜这里应该是提示1-3之后的下一个归档1-4

SQL> alter database open resetlogs;

Database altered.

SQL> select * from hr.fruits;--但是，发现，其实虽然表面上好像应用了1_4的归档日志文件，事实上恢复的终点还是控制文件的最大SCN，即只有三条

记录

NAME
----------
apple
banana
orange

SQL> select group#,sequence#,status from v$log;

    GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          0 UNUSED
         3          0 UNUSED
         4          0 UNUSED

ITPub博客

关于using backup controlfile(丢失联机重做日志文件)

最新文章