使用NORESETLOGS重建控制檔案恢復資料庫

-- 備份控制檔案
RMAN> backup datafile 1;

Starting backup at 29-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: starting piece 1 at 29-SEP-13
channel ORA_DISK_1: finished piece 1 at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/backupset/2013_09_29/o1_mf_nnndf_TAG20130929T174837_94hxvp14_.bkp tag=TAG20130929T174837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-SEP-13
channel ORA_DISK_1: finished piece 1 at 29-SEP-13
piece handle=/u01/app/oracle/flash_recovery_area/WAILON/backupset/2013_09_29/o1_mf_ncsnf_TAG20130929T174837_94hxwjhq_.bkp tag=TAG20130929T174837 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-13

-- 模擬交易產生並切換日誌
17:50:13 SYS@wailon> create table scott.a1 tablespace wailon as select * from scott.a;

Table created.

17:51:36 SYS@wailon> alter system switch logfile;

System altered.

17:51:45 SYS@wailon> create table scott.b1 tablespace wailon as select * from scott.b;

Table created.

17:51:53 SYS@wailon> alter system switch logfile;

System altered.

17:52:10 SYS@wailon> alter system checkpoint;

System altered.

17:52:15 SYS@wailon> select count(*) from scott.a1;

  COUNT(*)
----------
        23

17:52:20 SYS@wailon> select count(*) from scott.b1;

  COUNT(*)
----------
        23

17:52:25 SYS@wailon> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          7 CURRENT
         3          6 INACTIVE
         2          5 INACTIVE

17:52:30 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

-- 模擬意外斷電，並且控制檔案丟失
17:52:41 SYS@wailon> shutdown abort;
ORACLE instance shut down.
17:52:47 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

17:53:22 SYS@wailon> host ls /u01/app/oracle/oradata/wailon/control*
ls: cannot access /u01/app/oracle/oradata/wailon/control*: No such file or directory

-- 啟動資料庫報錯，找不到控制檔案
17:53:30 SYS@wailon> startup
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             352324912 bytes
Database Buffers           54525952 bytes
Redo Buffers                8466432 bytes
ORA-00205: error in identifying control file, check alert log for more info

-- 關閉資料庫，啟動到NOMOUNT狀態
17:53:38 SYS@wailon> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
17:53:55 SYS@wailon> startup nomount;
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2228944 bytes
Variable Size             352324912 bytes
Database Buffers           54525952 bytes
Redo Buffers                8466432 bytes

-- 由於所有聯機日誌檔案可用，使用NORESETLOGS重建控制檔案
17:54:01 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" NORESETLOGS FORCE LOGGING ARCHIVELOG
17:54:06   2    MAXLOGFILES 16
17:54:06   3    MAXLOGMEMBERS 3
17:54:06   4    MAXDATAFILES 100
17:54:06   5    MAXINSTANCES 8
17:54:06   6    MAXLOGHISTORY 292
17:54:06   7  LOGFILE
17:54:06   8    GROUP 1 '/u01/app/oracle/oradata/wailon/redo01.log'  SIZE 50M BLOCKSIZE 512,
17:54:06   9    GROUP 2 '/u01/app/oracle/oradata/wailon/redo02.log'  SIZE 50M BLOCKSIZE 512,
17:54:06  10    GROUP 3 '/u01/app/oracle/oradata/wailon/redo03.log'  SIZE 50M BLOCKSIZE 512
17:54:06  11  -- STANDBY LOGFILE
17:54:06  12  DATAFILE
17:54:06  13    '/u01/app/oracle/oradata/system01.dbf',
17:54:06  14    '/u01/app/oracle/oradata/sysaux01.dbf',
17:54:06  15    '/u01/app/oracle/oradata/undotbs01.dbf',
17:54:06  16    '/u01/app/oracle/oradata/users01.dbf',
17:54:06  17    '/u01/app/oracle/oradata/wailon/WAILON/datafile/o1_mf_wailon_94g6p2k8_.dbf'
17:54:06  18  CHARACTER SET ZHS16GBK
17:54:06  19  ;

Control file created.

-- 由於資料檔案不一致，需要進行介質恢復
17:54:08 SYS@wailon> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf'

17:54:37 SYS@wailon> recover database;
Media recovery complete.

17:54:48 SYS@wailon> alter database open;

Database altered.
-- 成功開啟資料庫

-- 驗證資料
17:55:08 SYS@wailon> select count(*) from scott.a1;

  COUNT(*)
----------
        23

17:55:28 SYS@wailon> select count(*) from scott.b1;

  COUNT(*)
----------
        23

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