1

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? :-)

1
  • 1
    The STATUS column of the v$instance view will tell you your database state. ORA-0600 sounds like a nasty problem and often calls for support. I would try to read alert log and perform complete recovery first, then ask for Oracle support. – YasirA Aug 17 '14 at 21:18
-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/

3
  • after shutdown immediate I fail to STARTUP RESTRICT MOUNT pfile=... due to ORA-12514 "TNS does not know service..." I stop and start the Windows service for ORCL db. I log in again, but do not shutdown. I try to drop undo tbs, but receive ORA-01109 "db is not open". Still need help. – Jakub P Aug 19 '14 at 20:30
  • I tried to follow this link on ORA-12514 and in return I received SQL> STARTUP RESTRICT MOUNT pfile=E:\Oracle\pfileUNDO.ora ORA-01506: missing or illegal database name (notifying @ibre5041) – Jakub P Aug 19 '14 at 20:43
  • -1 because this is nonsense. The undo tablespace holds information necessary to undo the open transactions after the archives/redos are applied.To change the undo tablespace the database must be open and the tablespace must not be damaged. – miracle173 Oct 27 '14 at 7:10
-1

@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)

2
  • RECOVER DATABASE NOPARALLEL does not help. It results with the same error: ORA-10567: REDO inconsistent ORA-10564: tablespace UNDO – Jakub P Aug 19 '14 at 20:50
  • -1 except "contact Oracle support" all this advice is nonsense – miracle173 Oct 27 '14 at 7:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.