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

-- 模擬交易產生
22:44:06 SYS@wailon> create table scott.a4 as select * from scott.a;

Table created.

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

System altered.

22:45:30 SYS@wailon> select group#,sequence#,status from v$log;

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

22:45:41 SYS@wailon> insert into scott.a4 select * from scott.a4;   -- 此事務未提交

23 rows created.

22:46:07 SYS@wailon> alter system switch logfile;

System altered.

22:46:15 SYS@wailon> select group#,sequence#,status from v$log;

    GROUP#  SEQUENCE# STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         3          3 CURRENT
         2          2 ACTIVE

22:46:18 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

-- 插入a4表的23行未提交，資料庫意外斷電，並且控制檔案丟失
22:47:06 SYS@wailon> shutdown abort;
ORACLE instance shut down.
22:47:14 SYS@wailon> host rm /u01/app/oracle/oradata/wailon/control*.ctl

22:47:28 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:47:32 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:47:41 SYS@wailon> CREATE CONTROLFILE REUSE DATABASE "WAILON" RESETLOGS FORCE LOGGING ARCHIVELOG
22:47:45   2      MAXLOGFILES 16
22:47:45   3      MAXLOGMEMBERS 3
22:47:45   4      MAXDATAFILES 100
22:47:45   5      MAXINSTANCES 8
22:47:45   6      MAXLOGHISTORY 292
22:47:45   7  LOGFILE
22:47:45   8    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
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
22:47:46  19  ;

Control file created.

-- 嘗試載入資料庫，建立控制檔案後已經自動載入
22:47:51 SYS@wailon> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted

-- 開啟資料庫，提示需要恢復
22:47:58 SYS@wailon> 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: '/u01/app/oracle/oradata/system01.dbf'

-- 使用USING BACKUP CONTROLFILE恢復，由於有活動事務未提交，需要指定聯機日誌執行不完全恢復
22:48:16 SYS@wailon> recover database using backup controlfile;
ORA-00279: change 3028602 generated at 09/29/2013 22:25:59 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_1_94jh8b2c_.arc
ORA-00280: change 3028602 for thread 1 is in sequence #1

22:48:24 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo01.log
ORA-00279: change 3030164 generated at 09/29/2013 22:45:29 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_2_94jh9qxy_.arc
ORA-00280: change 3030164 for thread 1 is in sequence #2
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo01.log' no longer needed for this recovery

22:48:58 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo02.log
ORA-00279: change 3030195 generated at 09/29/2013 22:46:15 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/WAILON/archivelog/2013_09_29/o1_mf_1_3_%u_.arc
ORA-00280: change 3030195 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/oradata/wailon/redo02.log' no longer needed for this recovery

22:49:08 Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/wailon/redo03.log
Log applied.
Media recovery complete.

-- 恢復完成後，使用OPEN RESETLOGS開啟資料庫
22:49:12 SYS@wailon> alter database open resetlogs;

Database altered.

-- 未提交的資料已經丟失
22:52:59 SYS@wailon> select count(*) from scott.a4;

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

-- 新增臨時表空間
22:41:02 SYS@wailon> select * from v$tempfile;

no rows selected

22:42:00 SYS@wailon> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP01
WAILON

6 rows selected.

22:42:33 SYS@wailon> select name from v$datafile;

NAME
---------------------------------------------
/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/datafil
e/o1_mf_wailon_94g6p2k8_.dbf

22:43:46 SYS@wailon> alter tablespace temp01 add tempfile '/u01/app/oracle/oradata/wailon/temp01.dbf' size 10m reuse;

Tablespace altered.

22:43:57 SYS@wailon> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TIM        TS#     RFILE# STATUS  ENABLED         BYTES     BLOCKS CREATE_BYTES
---------- ---------------- ------------ ---------- ---------- ------- ---------- ---------- ---------- ------------
BLOCK_SIZE NAME
---------- ---------------------------------------------
         1          3030098 29-SEP-13             7          1 ONLINE  READ WRITE   10485760       1280     10485760
      8192 /u01/app/oracle/oradata/wailon/temp01.dbf

 

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