oracle断电后，数据库无法启动 ora 00354 00353 00312错误

测试机数据库装在linux虚拟机上，主机断电导致数据库无法启动

先启动监听

./lsnrctl start

登陆sqlplus

./sqlplus /nolog

SQL> conn / as sysdba

SQL> starup

默认方式启动数据库，报如下错误

ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 21176 change 5503243 time 09/20/2018
00:49:58
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'

首先清理坏的日志

SQL> alter database clear unarchived logfile group 3 ;

报错如下

alter database clear unarchived logfile group 3
*
ERROR 位于第 1 行:
ORA-01624: 线程3的紧急恢复需要日志1
ORA-00312: 联机日志 3 线程 1: 'D:ORACLEORADATARMANREDO03.LOG'

无法完全恢复，只有执行不完全恢复

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile ;

SQL> alter system set "_allow_terminal_recovery_corruption"=true scope=spfile ;

SQL> shutdown abort ;

SQL>startup

SQL> recover database until cancel;

cancel

SQL> recover database until cancel;

auto

不完全恢复完毕，报错ora 00600还是无法启动

到目前位置，争取以牺牲部分数据作为代价，保证数据库能正常启动，采取重建undo表空间的策略

------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL>startup mount;

ORACLE instance started.

Total System Global Area 5010685952 bytes
Fixed Size                  2691904 bytes
Variable Size            1090522304 bytes
Database Buffers         3909091328 bytes
Redo Buffers                8380416 bytes
Database mounted.
SQL> show parameter undo;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
temp_undo_enabled                    boolean
FALSE
undo_management                      string
AUTO
undo_retention                       integer
900
undo_tablespace                      string
UNDOTBS1
SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup
ORACLE instance started.

Total System Global Area 5010685952 bytes
Fixed Size                  2691904 bytes
Variable Size            1090522304 bytes
Database Buffers         3909091328 bytes
Redo Buffers                8380416 bytes
Database mounted.
Database opened.
SQL>  create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 100m autoextend on maxsize 2g;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

System altered.

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/orcl/undotbs01.dbf' size 100m autoextend on maxsize 2g;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs1 scope=spfile;

System altered.

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

SQL> show parameter undo;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
temp_undo_enabled                    boolean
FALSE
undo_management                      string
MANUAL
undo_retention                       integer
900
undo_tablespace                      string
UNDOTBS1
SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate

SQL> exit
Disconnected
[oracle@localhost bin]$ ./lsnrctl stop

[oracle@localhost bin]$ ./lsnrctl start

[oracle@localhost bin]$ ./sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Sep 20 10:38:51 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5010685952 bytes
Fixed Size                  2691904 bytes
Variable Size            1090522304 bytes
Database Buffers         3909091328 bytes
Redo Buffers                8380416 bytes
Database mounted.
Database opened.