DB crash后，restore and recover成功，open失败。。？？研究中。...
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:
$ 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......
研究中，，，，有些纳闷了。。。。
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.
页:
[1]