使用RESETLOGS重建控制檔案恢復資料庫（二）

控制檔案丟失，但資料檔案及聯機日誌檔案還在，並且資料庫是正常關閉。

-- 模擬交易產生
22:20:46 SYS@wailon> select group#,sequence#,status from v$log;

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

22:21:23 SYS@wailon> create table scott.a3 as select * from scott.a;

Table created.

22:21:54 SYS@wailon> alter system switch logfile;

System altered.

22:22:01 SYS@wailon> insert into scott.a3 select * from scott.a3;

23 rows created.

22:22:51 SYS@wailon> commit;

Commit complete.

22:22:55 SYS@wailon> alter system switch logfile;

System altered.

-- 備份CONTROLFILE TO TRACE可以從裡面查到控制檔案的建立語句
22:23:15 SYS@wailon> alter database backup controlfile to trace;

Database altered.

22:23:31 SYS@wailon> select * from v$diag_info;

  1 Default Trace File
/u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_20993.trc
 
22:23:38 SYS@wailon> host view /u01/app/oracle/diag/rdbms/wailon/wailon/trace/wailon_ora_20993.trc

-- 省略部分輸出

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/system01.dbf',
  '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/users01.dbf',
  '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/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;
-- No tempfile entries found to add.
--

22:24:23 SYS@wailon> select * from v$controlfile;

STATUS  NAME                                          IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- --------------------------------------------- --- ---------- --------------
        /u01/app/oracle/oradata/wailon/control01.ctl  NO       16384            614
        /u01/app/oracle/oradata/wailon/control02.ctl  NO       16384            614

-- 正常關閉資料庫，刪除所有控制檔案
22:24:35 SYS@wailon> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
22:24:49 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

22:25:08 SYS@wailon> host ls /u01/app/oracle/oradata/wailon/control*.ctl
ls: cannot access /u01/app/oracle/oradata/wailon/control*.ctl: No such file or directory

-- 啟動資料庫到NOMOUNT狀態
22:25:16 SYS@wailon> startup nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             360713520 bytes
Database Buffers           46137344 bytes
Redo Buffers                8466432 bytes

-- 使用RESETLOGS建立控制檔案
22:25:37 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
22:25:41   2      MAXLOGFILES 16
22:25:41   3      MAXLOGMEMBERS 3
22:25:41   4      MAXDATAFILES 100
22:25:41   5      MAXINSTANCES 8
22:25:41   6      MAXLOGHISTORY 292
22:25:41   7  LOGFILE
22:25:41   8    GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log'  SIZE 50M BLOCKSIZE 512,
22:25:41   9    GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log'  SIZE 50M BLOCKSIZE 512
22:25:41  11  -- STANDBY LOGFILE
22:25:41  12  DATAFILE
22:25:41  13    '/u01/app/oracle/oradata/system01.dbf',
22:25:41  14    '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/users01.dbf',
  '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
22:25:41  18  CHARACTER SET ZHS16GBK
22:25:41  19  ;

Control file created.

-- 控制檔案建立成功後，直接使用RESETLOGS開啟資料庫；由於正常關閉資料庫，所以不需要recover
22:25:45 SYS@wailon> alter database open resetlogs;

Database altered.

22:26:06 SYS@wailon> select status from v$instance;

STATUS
------------
OPEN

-- 日誌序號從1開始編號
22:29:59 SYS@wailon> select group#,sequence#,status from v$Log;

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

 

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