DB crash后，restore and recover成功，open失败。。？？研究中。...

ALLSTARS_ORACLE

develop DB  （9204 in redHat as 4)
今天早上由于掉电crash了，redo01.log丢失，经检查是当前online log。
由于是develop DB，没有启用archivelog，只有一个月前cold backup。

准备恢复：

1：查看V$log，发现是当前日志
SQL> select group#,sequence#,archived,status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS
---------- ---------- -------- ----------------
1 1 NO CURRENT
2 2 YES INACTIVE
3 3 YES INACTIVE

2：发现clear不成功
SQL> alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\ORACLE\REDO01.LOG'

3:把数据库down掉
SQL>shutdown immediate

4:、在init<sid>.ora中加入如下参数
_allow_resetlogs_corruption=TRUE

5:重新启动数据库,利用until cancel恢复
SQL>recover database until cancel;
Cancel
出错，不再理会，发出
SQL>alter database open resetlogs;

6:依然失败。。。。（看来运气不佳）


7：于是决定 放弃online log恢复，直接使用cold backup 恢复。

8：rm /oracle/oradata/oracle/*
    // remove all datafiles

9: cp -R /oradata/backup/oracle/*   /oracle/oradata/oracle/*

10:
[oracle@oracle archive]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 17 16:12:17 2006

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

Connected to an idle instance.

SQL> startup mount
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.
SQL> recover database;
Media recovery complete.   
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection force





奇怪的问题，这个cold backup曾经还用过一次recover成功。

现在无论如何都不能open DB......


研究中，，，，有些纳闷了。。。。

ALLSTARS_ORACLE

Ended recovery at
Thread 1: logseq 144, block 3, scn 0.332437
0 data blocks read, 0 data blocks written, 1 redo blocks read
Crash recovery completed successfully
Mon Apr 17 16:13:11 2006
Thread 1 advanced to log sequence 145
Thread 1 opened at log sequence 145
  Current log# 1 seq# 145 mem# 0: /oradata/ora9/oradata/oracle/redo01.log
Successful open of redo thread 1.
Mon Apr 17 16:13:11 2006
ARC0: Media recovery disabled
Mon Apr 17 16:13:11 2006
SMON: enabling cache recovery
Mon Apr 17 16:13:12 2006
Errors in file /oradata/ora9/admin/oracle/udump/oracle_ora_11514.trc:
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type
Mon Apr 17 16:13:12 2006
Error 30012 happened during db open, shutting down database



重建control file：

SQL> shutdown abort
ORACLE instance shut down.
SQL> @/oradata/ora9/oradata/oracle/control.sql
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
29  /

Control file created.
SQL> recover database;
Media recovery complete.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
alert.log:

Ended recovery at
Thread 1: logseq 145, block 3, scn 0.352440
0 data blocks read, 0 data blocks written, 1 redo blocks read
Crash recovery completed successfully
Mon Apr 17 16:58:37 2006
Thread 1 advanced to log sequence 146
Thread 1 opened at log sequence 146
  Current log# 2 seq# 146 mem# 0: /oradata/ora9/oradata/oracle/redo02.log
Successful open of redo thread 1.
Mon Apr 17 16:58:38 2006
ARC0: Media recovery disabled
Mon Apr 17 16:58:38 2006
SMON: enabling cache recovery
Mon Apr 17 16:58:38 2006
Errors in file /oradata/ora9/admin/oracle/udump/oracle_ora_11821.trc:
ORA-30012: undo tablespace 'UNDOTBS' does not exist or of wrong type
Mon Apr 17 16:58:38 2006
Error 30012 happened during db open, shutting down database
USER: terminating instance due to error 30012
Instance terminated by USER, pid = 11821
ORA-1092 signalled during: alter database open...


原因：
曾经切换过UNDOTBS
OK,应该好解了。。。



解决问题：

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
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.
SQL> show parameter UNDO

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS

SQL>alter system set   undo_tablespace='UNDOTBS1';
SQL>show parameter UNDO
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     10800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oradata/ora9/oradata/oracle/system01.dbf'


SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.