【管理篇備份恢復】rman恢復測試（二） 控制檔案恢復（三）

控制檔案重建

oracle除了備份二進位制控制檔案以外，還可以生成重建控制檔案的指令碼，
下面來看看使用控制檔案重建的方法恢復

SQL> alter database backup controlfile to trace;

Database altered.

使用之前建立的一個檢視檢視當前的trc檔案的位置
SQL> select * from gettrcname;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/tpdata/database/admin/test1/udump/test1_ora_13501.trc

獲取trc檔案中的如下內容來作為建立控制檔案的指令碼：

CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/tpdata/database/oradata/test1/redo01.log'  SIZE 100M,
  GROUP 2 '/tpdata/database/oradata/test1/redo02.log'  SIZE 50M,
  GROUP 3 '/tpdata/database/oradata/test1/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/tpdata/database/oradata/test1/system01.dbf',
  '/tpdata/database/oradata/test1/undotbs01.dbf',
  '/tpdata/database/oradata/test1/sysaux01.dbf',
  '/tpdata/database/oradata/test1/users01.dbf',
  '/tpdata/database/oradata/test1/users02.dbf',
  '/tpdata/database/oradata/test1/taipinglife.dbf',
  '/tpdata/database/oradata/test1/readonly.bak',
  '/tpdata/database/oradata/test1/readonly1.bak'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 1');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_1_%u_.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/tpdata/database/oradata/test1/temp01.dbf'
     SIZE 181403648  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
-- End of tempfile additions.
--

也可以對這個指令碼稍稍更改，將：
CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS  ARCHIVELOG
中的resetlogs 修改為 noresetlogs
這樣建立的控制檔案使用當前日誌中的最高的scn來，而resetlogs控制檔案的scn是來自資料檔案

使用shutdown abort模擬故障
分別使用兩種不同的方式來重建控制檔案

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  591396864 bytes
Fixed Size                  1268800 bytes
Variable Size             322962368 bytes
Database Buffers          260046848 bytes
Redo Buffers                7118848 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST1" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/tpdata/database/oradata/test1/redo01.log'  SIZE 100M,
  9    GROUP 2 '/tpdata/database/oradata/test1/redo02.log'  SIZE 50M,
 10    GROUP 3 '/tpdata/database/oradata/test1/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/tpdata/database/oradata/test1/system01.dbf',
 14    '/tpdata/database/oradata/test1/undotbs01.dbf',
 15    '/tpdata/database/oradata/test1/sysaux01.dbf',
 16    '/tpdata/database/oradata/test1/users01.dbf',
 17    '/tpdata/database/oradata/test1/users02.dbf',
 18    '/tpdata/database/oradata/test1/taipinglife.dbf',
 19    '/tpdata/database/oradata/test1/readonly.bak',
 20    '/tpdata/database/oradata/test1/readonly1.bak'
 21  CHARACTER SET WE8ISO8859P1
 22  ;

Control file created.

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

也可以使用alter database open noresetlogs，oracle這時候預設是使用noresetlogs

或者使用RESETLOGS 來建立控制檔案：

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  591396864 bytes
Fixed Size                  1268800 bytes
Variable Size             322962368 bytes
Database Buffers          260046848 bytes
Redo Buffers                7118848 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "TEST1" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/tpdata/database/oradata/test1/redo01.log'  SIZE 100M,
  9    GROUP 2 '/tpdata/database/oradata/test1/redo02.log'  SIZE 50M,
 10    GROUP 3 '/tpdata/database/oradata/test1/redo03.log'  SIZE 50M
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/tpdata/database/oradata/test1/system01.dbf',
 14    '/tpdata/database/oradata/test1/undotbs01.dbf',
 15    '/tpdata/database/oradata/test1/sysaux01.dbf',
 16    '/tpdata/database/oradata/test1/users01.dbf',
 17    '/tpdata/database/oradata/test1/users02.dbf',
 18    '/tpdata/database/oradata/test1/taipinglife.dbf',
 19    '/tpdata/database/oradata/test1/readonly.bak',
 20    '/tpdata/database/oradata/test1/readonly1.bak'
 21  CHARACTER SET WE8ISO8859P1
 22  ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/tpdata/database/oradata/test1/system01.dbf'

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

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 6410728370986 generated at 06/14/2010 05:48:23 needed for
thread 1
ORA-00289: suggestion :
/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_2_%u_.a
rc
ORA-00280: change 6410728370986 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}
/tpdata/database/oradata/test1/redo01.log
ORA-00310: archived log contains sequence 1; sequence 2 required
ORA-00334: archived log: '/tpdata/database/oradata/test1/redo01.log'

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: '/tpdata/database/oradata/test1/system01.dbf'

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 6410728370986 generated at 06/14/2010 05:48:23 needed for
thread 1
ORA-00289: suggestion :
/tpdata/database/flash_recovery_area/TEST1/archivelog/2010_06_14/o1_mf_1_2_%u_.a
rc
ORA-00280: change 6410728370986 for thread 1 is in sequence #2

Specify log: {=suggested | filename | AUTO | CANCEL}
/tpdata/database/oradata/test1/redo02.log
Log applied.
Media recovery complete.
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL>

注意到上面需要使用 using backup controlfile來recover database，因為是使用resetlogs重建的控制檔案，
oracle給出了使用的歸檔日誌的建議，
聯機日誌還在，可以使用聯機日誌來恢復，嘗試輸入聯機日誌的檔名來做恢復
到redo02.log的時候恢復成功了，然後使用resetlogs開啟資料庫。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-665093/，如需轉載，請註明出處，否則將追究法律責任。