Oracle备份与恢复【丢失控制文件的恢复】

为加强在数据库恢复方面的经验，借着空闲的时间来实践一下各种数据文件丢失的恢复：

【模拟单个控制文件丢失】
rm -f control02.ctl
shutdown abort
然后复制一个控制文件重命名为control02.ctl即可。


【模拟全部控制文件丢失】
rm -f control*.ctl
全部控制文件丢失后，
执行shutdown immediate将会报错：
sys@LYON> shutdown immediate
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/lyon/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

此时需要以shutdown abort来关闭：
sys@LYON> shutdown abort
ORACLE instance shut down.

然后启动数据库的时候便会发生如下错误：
sys@LYON> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              79694092 bytes
Database Buffers           79691776 bytes
Redo Buffers                7168000 bytes
ORA-00205: error in identifying control file, check alert log for more info

由于控制文件加载是在mount阶段，此时由于已经缺失，因此可以判断当前数据库已经处于nomount阶段。
开始创建控制文件(如果之前有备份过控制文件更好)：
create controlfile reuse database lyon resetlogs archivelog
    maxlogfiles 16
    maxlogmembers 3
    maxdatafiles 100
    maxinstances 1
    maxloghistory 292
logfile
    group 1 '/data/lyon/redo01.log' size 50m,
    group 2 '/data/lyon/redo02.log' size 50m,
    group 3 '/data/lyon/redo03.log' size 50m
datafile
    '/data/lyon/system01.dbf',
    '/data/lyon/sysaux01.dbf',
    '/data/lyon/undotbs01.dbf',
    '/data/lyon/users01.dbf'
character set we8iso8859p1;

sys@LYON> create controlfile reuse database lyon resetlogs archivelog
  2      maxlogfiles 16
  3      maxlogmembers 3
  4      maxdatafiles 100
  5      maxinstances 1
  6  maxloghistory 292
  7  logfile
  8  group 1 '/data/lyon/redo01.log' size 50m,
  9  group 2 '/data/lyon/redo02.log' size 50m,
  10 group 3 '/data/lyon/redo03.log' size 50m
  11  datafile
  12  '/data/lyon/system01.dbf',
  13  '/data/lyon/sysaux01.dbf',
  14  '/data/lyon/undotbs01.dbf',
  15  '/data/lyon/users01.dbf'
  16 character set we8iso8859p1;

Control file created.

Elapsed: 00:00:01.10

然后开始恢复数据库：
sys@LYON> recover database using backup controlfile ;
但是出现如下报错：
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.log
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
意思是指定的redo log file中的scn与现有控制文件的scn不一致

搜索了下Google和baidu，原来是指定了错误的日志文件：
ORA-00342: archived log does not have expected resetlogs SCN string

Cause: Recovery was given a log that does not belong to current incarnation or one of the parent incarnation. There should be another log that contains the correct redo.

Action: Supply the correct log file.

于是检查了下正确的日志文件：
[oracle@localhost lyon]$ ls -lrt
total 1402680
-rw-r-----  1 oracle oinstall 242229248 Mar  2 07:29 temp01.dbf
-rw-r-----  1 oracle oinstall  52429312 May 24 13:49 redo03.log
-rw-r-----  1 oracle oinstall  52429312 May 24 13:49 redo02.log
-rw-r-----  1 oracle oinstall  52429312 May 24 14:11 redo01.log
-rw-r-----  1 oracle oinstall  52429312 May 24 14:24 redo02.dbf
-rw-r-----  1 oracle oinstall  52429312 May 24 14:24 redo01.dbf
-rw-r-----  1 oracle oinstall  52429312 May 24 14:24 redo03.dbf
-rw-r-----  1 oracle oinstall  13115392 May 24 14:37 users01.dbf
-rw-r-----  1 oracle oinstall  68165632 May 24 14:37 undotbs01.dbf
-rw-r-----  1 oracle oinstall 524296192 May 24 14:37 system01.dbf
-rw-r-----  1 oracle oinstall 251666432 May 24 14:37 sysaux01.dbf
-rw-r-----  1 oracle oinstall   7258112 May 24 14:39 control03.ctl
-rw-r-----  1 oracle oinstall   7258112 May 24 14:39 control02.ctl
-rw-r-----  1 oracle oinstall   7258112 May 24 14:39 control01.ctl
好像是前面一次实验的时候指定了dbf的后缀的redolog。

于是重新指定了下：
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf
ORA-00339: archived log does not contain any redo
ORA-00334: archived log: '/data/lyon/redo01.dbf'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'

发现redo01.dbf不含有所需要的任何redo信息。再尝试一下redo03.dbf
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.

这次成功了，打开数据库（resetlogs）：
sys@LYON> alter database open resetlogs
  2  ;

Database altered.

Elapsed: 00:00:13.40

搞定。
这次问题的解决参考了：http://space.itpub.net/67668/viewspace-353270。
总结一下：
问题的产生：
该问题主要是为了模拟控制文件全部丢失，但是其他数据文件仍然存在的情况。
这时shutdown immediate已经不能关闭数据库，需要shutdown abort。

问题的解决：
1、要知道所有相关数据文件所在的路径或者之前有控制文件备份，因为没有这
些信息便无法重建控制文件。
（备份控制文件：
alter database backup controlfile to trace;
alter database backup controlfile to '/data/backup/controlfile_bak.ctl';
）

2、将数据库启动到nomount阶段，并创建控制文件。
startup nomount

3、开始恢复数据库，这个步骤很关键，要选择正确的文件去进行恢复：
recover database using backup controlfile until cancel;
执行那个以上命令后，会出现一个提示：
sys@LYON> recover database using backup controlfile until cancel
ORA-00279: change 954664 generated at 05/24/2010 14:24:27 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_1_%u_.arc
ORA-00280: change 954664 for thread 1 is in sequence #1


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

接下来是要我们指定合适的日志文件或归档文件来进行恢复。
如果选择了上次恢复前的日志文件，则会报：
ORA-00342: archived log does not have expected resetlogs SCN 954663
ORA-00334: archived log: '/data/lyon/redo01.log'
上面这两个错误。
这时候要看下是不是指定的文件名错了，我这里是文件的后缀指定错了（应该是dbf，上次恢复的时候错误的指定了这个后缀）
于是改成正确的文件名。
此时如果对应的重做日志中不含有恢复所需要的redo信息：
ORA-00339: archived log does not contain any redo
则再换一个，一直换到可以完全恢复：
/data/lyon/redo03.dbf
Log applied.
Media recovery complete.
这时已经表示介质恢复已经完成了。

4、然后就是收尾的工作了：
alter database open resetlogs;
使用resetlogs的作用是重置DB的scn，使得此前所有的一致性备份都没有用了，因此开启数据库后首先要做的事情就是进行完全备份。






---以下是第一次恢复的操作过程：
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'


Elapsed: 00:00:00.03
sys@LYON> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


sys@LYON> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
sys@LYON> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


Elapsed: 00:00:00.01
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


Elapsed: 00:00:00.02
sys@LYON> alter database open noresetlogs;
alter database open noresetlogs
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


Elapsed: 00:00:00.03
sys@LYON> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/data/lyon/system01.dbf'


Elapsed: 00:00:00.02
sys@LYON> recover datafile from backup ;
ORA-02236: invalid file name


sys@LYON> recover datafile from backup '/data/backup_large/full_bak_04lefupi_1_1                                             ';
ORA-02236: invalid file name


sys@LYON> recover datafile from '/data/backup_large/full_bak_04lefupi_1_1';
ORA-02236: invalid file name


sys@LYON> recover datafile using backup '/data/backup_large/full_bak_04lefupi_1_                                             1';
ORA-02236: invalid file name


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


sys@LYON> recover database using backup controlfile;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_                                             mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74


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

ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3



sys@LYON> sys@LYON>
sys@LYON> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
sys@LYON> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              79694092 bytes
Database Buffers           79691776 bytes
Redo Buffers                7168000 bytes
Database mounted.
sys@LYON> recover database ;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_                                             mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74


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

ORA-00308: cannot open archived log
'/arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_mf_1_74_%u_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'


sys@LYON> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------                                             --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          0   52428800          1 YES    UNUSED
            0

         3          1          0   52428800          1 YES    CURRENT
            0

         2          1          0   52428800          1 YES    UNUSED
            0


Elapsed: 00:00:00.05
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_                                             mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf'
ORA-01756: quoted string not properly terminated


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/data/lyon/system01.dbf'


sys@LYON> /

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS
---------- ---------- ---------- ---------- ---------- ------ ------------------                                             --------------
FIRST_CHANGE# FIRST_TIME
------------- ------------
         1          1          0   52428800          1 YES    UNUSED
            0

         3          1          0   52428800          1 YES    CURRENT
            0

         2          1          0   52428800          1 YES    UNUSED
            0


Elapsed: 00:00:00.01
sys@LYON> recover database using backup controlfile until cancel;
ORA-00279: change 953574 generated at 05/24/2010 13:49:04 needed for thread 1
ORA-00289: suggestion : /arch/flash_recovery_area/LYON/archivelog/2010_05_24/o1_                                             mf_1_74_%u_.arc
ORA-00280: change 953574 for thread 1 is in sequence #74


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.dbf
ORA-00308: cannot open archived log '/data/lyon/redo01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
/data/lyon/redo01.log
Log applied.
Media recovery complete.
sys@LYON> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


Elapsed: 00:00:00.01
sys@LYON> alter databse open resetlogs;
alter databse open resetlogs
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


Elapsed: 00:00:00.00
sys@LYON> alter database open resetlogs;

Database altered.

Elapsed: 00:00:10.72

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

转载于:http://blog.itpub.net/12932950/viewspace-663712/