Oracle備份與恢復【丟失控制檔案的恢復】

regonly1發表於2010-05-25
為加強在資料庫恢復方面的經驗,藉著空閒的時間來實踐一下各種資料檔案丟失的恢復:

【模擬單個控制檔案丟失】
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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章