ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block"

Question

I faced an error while logging to database with regular user (non SYS/DBA):

C:\Users\user>sqlplus tester@orcl
ORA-01033 ORACLE initialization or shutdown in progress

However I can connect via sysman@orcl as sysdba user. Before I start with solving the issue, below are the errors that can tell what state my orcl database is in:

SQL> select * from dba_data_files;
ORA-01219: database not open: queries allowed on fixed tables/views only

So I use recover database and alter database open commands and they point at the same logfile and block:

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [3], [192], [12583104], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 192, file offset is 1572864 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'

SQL> alter database open;
ORA-01172: recovery of thread 1 stuck at block 192 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I did also that:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

After restarting oracle.exe service and diagnosing the database, the "redo" error has a bit changed (block 705):

ORA-10567: Redo is inconsistent with data block (file# 3, block# 705, file offset is 5775360 bytes)

My log files:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                IS_
---------- ------- ------- ------------------------------------- ---
         3         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO03.LOG     NO
         2         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO02.LOG     NO
         1         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO01.LOG     NO

I'm in a point where I cannot use my database at full even by a sysman user. Please help, advise.

EDIT: After following this guide which ideally addresses my situation, I get different error and cannot follow it anymore:

SQL> alter system switch logfile;
ORA-01109: database not open

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Question aside: Who can tell if this db is open or not? :-)

Answer 1

Since the problem is in your undo tablespace. You can drop it and create a new one. Steps in this article:

http://www.my-whiteboard.com/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files/

Answer 2

@user129400 is right - replacing UNDO tbs is a good idea. You can also try:

• contact Oracle support
• disable parallel media recovery
• If you have at least two copies of redo logs you can also check whether they are identical. If not remove one of the copies
• bypass media recovery completely, start the database, export application schemas and import them into newly created database

PS: you database is not open, but probably is still mounted(the previous state before open)