關機後redo檔案丟失重建方法

[oracle@oracle9ivm ~]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Sep 29 16:01:36 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected.
備份控制檔案
SQL> alter database backup controlfile to '/u01/admin/ora10g/udump/controlbackup.bin';

Database altered.

[root@oracle9ivm udump]# ll
total 1980
-rw-r-----  1 oracle oinstall 2023424 Sep 29 16:03 controlbackup.bin

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

刪除redo日誌
[root@oracle9ivm ora10g]# ll
total 1271348
drwxr-xr-x  2 oracle oinstall      4096 Sep 29 11:20 arch
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:04 control01.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:04 control02.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:04 control03.ctl
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:04 cwmlite01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:04 drsys01.dbf
-rw-r--r--  1 oracle oinstall 156639232 Sep 29 16:04 example01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:04 indx01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:04 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Sep 29 16:04 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Sep 29 11:13 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Sep 29 11:20 redo03.log
-rw-r--r--  1 oracle oinstall 398467072 Sep 29 16:04 system01.dbf
-rw-r--r--  1 oracle oinstall  41951232 May 27  2012 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Sep 29 16:04 tools01.dbf
-rw-r--r--  1 oracle oinstall 209723392 Sep 29 16:04 undotbs01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:04 users01.dbf
-rw-r--r--  1 oracle oinstall  47194112 Sep 29 16:04 xdb01.dbf
[root@oracle9ivm ora10g]# rm -f redo0*
[root@oracle9ivm ora10g]# ll
total 963824
drwxr-xr-x  2 oracle oinstall      4096 Sep 29 11:20 arch
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:06 control01.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:06 control02.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:06 control03.ctl
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 cwmlite01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 drsys01.dbf
-rw-r--r--  1 oracle oinstall 156639232 Sep 29 16:06 example01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:06 indx01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 odm01.dbf
-rw-r--r--  1 oracle oinstall 398467072 Sep 29 16:06 system01.dbf
-rw-r--r--  1 oracle oinstall  41951232 May 27  2012 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Sep 29 16:06 tools01.dbf
-rw-r--r--  1 oracle oinstall 209723392 Sep 29 16:06 undotbs01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:06 users01.dbf
-rw-r--r--  1 oracle oinstall  47194112 Sep 29 16:06 xdb01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/oradata/ora10g/redo01.log'

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> recover database until cancel using backup controlfile ;
ORA-00279: change 462769 generated at 09/29/2011 16:04:24 needed for thread 1
ORA-00289: suggestion : /u01/oradata/ora10g/arch/1_8.dbf
ORA-00280: change 462769 for thread 1 is in sequence #8

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

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

SQL>

據觀察，redo檔案已生成
[root@oracle9ivm ora10g]# ll
total 1271348
drwxr-xr-x  2 oracle oinstall      4096 Sep 29 11:20 arch
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:07 control01.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:07 control02.ctl
-rw-r-----  1 oracle oinstall   2023424 Sep 29 16:07 control03.ctl
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 cwmlite01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 drsys01.dbf
-rw-r--r--  1 oracle oinstall 156639232 Sep 29 16:06 example01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:06 indx01.dbf
-rw-r--r--  1 oracle oinstall  20979712 Sep 29 16:06 odm01.dbf
-rw-r-----  1 oracle oinstall 104858112 Sep 29 16:07 redo01.log
-rw-r-----  1 oracle oinstall 104858112 Sep 29 16:07 redo02.log
-rw-r-----  1 oracle oinstall 104858112 Sep 29 16:07 redo03.log
-rw-r--r--  1 oracle oinstall 398467072 Sep 29 16:06 system01.dbf
-rw-r--r--  1 oracle oinstall  41951232 May 27  2012 temp01.dbf
-rw-r--r--  1 oracle oinstall  10493952 Sep 29 16:06 tools01.dbf
-rw-r--r--  1 oracle oinstall 209723392 Sep 29 16:06 undotbs01.dbf
-rw-r--r--  1 oracle oinstall  26222592 Sep 29 16:06 users01.dbf
-rw-r--r--  1 oracle oinstall  47194112 Sep 29 16:06 xdb01.dbf
[root@oracle9ivm ora10g]#

日誌檔案的sequence#重新從0開始計算
SQL> set linesize 400
SQL> select * from v$log;
SQL> /

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          1  104857600          1 NO  CURRENT                 462770 29-SEP-11
         2          1          0  104857600          1 YES UNUSED                       0
         3          1          0  104857600          1 YES UNUSED                       0

SQL>