一次redolog丢失的处理

机器断电，数据库启动的时候，报错如下：

ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata/xxx/redo02.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL>

 

接到电话，登录机器，看看情况。

SQL> set lines 120
SQL> col MEMBER format a60
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         3         ONLINE  /oradata/xxxxxxx/redo03.log                                  NO
         2         ONLINE  /oradata/xxxxxxx/redo02.log                                  NO
         1         ONLINE  /oradata/xxxxxxx/redo01.log                                  NO

SQL>

结果三个文件一个都没有了，而且是“No Archive Mode”。

 

参考网上的处理方案，用“_allow_resetlogs_corruption=true”，原址如下：

http://space.itpub.net/7177886/viewspace-251670

 

1. 用当前的spfile,生成pfile。

2. 修改pfile, 加入一行：_allow_resetlogs_corruption=true
3. shutdown

4. startup mount pfile='/tmp/a.ora';

5. recover database until cancel;

6. alter database open resetlogs;

 

==== log of step 5:

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: '/oradata/xxxxxxx/system01.dbf'

==== log of step 6:

SQL> alter database open resetlogs;

alter database open resetlogs
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL>

 

到目前，还是启动不了，不过redo文件已经重新生成了。

重新尝试，还可以看到错误：

SQL> startup pfile='/home/oracle/OraHome1/dbs/xxxxx.ora';
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1267212 bytes
Variable Size             704645620 bytes
Database Buffers          520093696 bytes
Redo Buffers               15507456 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error

SQL>

 

现在的问题，是undo segment不对了。那就重建，参考了两个url：

http://bbs.chinaunix.net/thread-854395-1-1.html

http://yangdong.blog.51cto.com/2959198/799515

 

1. 修改pfile, 将其中的一行改成manual。

*.undo_management='manual'
2. 用这个pfile启动，成功

startup pfile='/tmp/a.ora';

3. 新建undo tablespce

create undo tablespace UNDOTBS3 datafile '/oradata/xxxxxxx/undotbs03.dbf' size 512m;
4. 把pfile再改回去，而且多改一行，指定新增的tablespace

*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS3'
5.shutdown

6. 用改好的pfile启动， OK

startup pfile='/tmp/a.ora';

 

====== log of step 6.

SQL> startup pfile='/tmp/a.ora';
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1267212 bytes
Variable Size             704645620 bytes
Database Buffers          520093696 bytes
Redo Buffers               15507456 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS3
SQL>

 

=======

 

后续操作，就不用列了。