0

I have an installation of an Oracle XE on Windows 7 (Home Premium). The database worked fine until yesterday when the database could not be started. It reports ORA-01033: oracle initialization or shoutdown in progress. I searched Stack Overflow for similar problems and I found Oracle Shutdown error ORA-01033. Following the instructions provided there, I connected as sysdba with SQL*Plus and executed statements:

SQL> shutdown abort; 
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
SQL> alter database mount;
Total System Global Area  805306368 bytes
Fixed Size                  1289996 bytes
Variable Size             218104052 bytes
Database Buffers          583008256 bytes
Redo Buffers                2904064 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00204: error in reading (block 303, # blocks 1) of control file
ORA-00202: control file: 'E:\MEDIASERVER\ORACLE\ORADATA\XE\CONTROL.DBF'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) ????????????? ?????? ????????? (???????? ???????
??????????).

After this I supposed that the CONTROL.DBF file has been corrupted.

Is there any way the corrupted file can be corrected in order for the database to start and be functional?

If there is no way for the above error to be fixed, I had a backup of the Oracle installation (not an ordinary backup, but a file copy of all the directories of Oracle's installation) that was obtained (unfortunately) with the database services running. So I used this copy and I tried to start the database again with the same method as in the first case. When I executed the last command I got:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'E:\MEDIASERVER\ORACLE\ORADATA\XE\UNDO.DBF'

Can I fix the this error now in order to have the database opened? Of course my first target is to fix the first case as mentioned at the beginning but if there is no way for this I will be satisfied even if use the second case.

3
  • Trying things like replacing the files from your copies could make things worse. Do you have a recent control file backup (e.g. from backup controlfile to trace)? Is there a problem with the E: disk - have you verified the media itself?
    – Alex Poole
    Oct 27, 2014 at 12:09
  • Sorry I was not so sufficient, well I didn’t exactly replaced the files of the oracle installation with the backup, but first I renamed the oracle's directory to something like destr_oracle and then I copied the backup to the same place as the previous installation. Also in this backup, the controlfile exist. Can I use this controlfile in the place of the corrupted?
    – Hb-IT
    Oct 27, 2014 at 12:32
  • Did you already solve your problem?
    – miracle173
    Oct 28, 2014 at 12:44

2 Answers 2

1

1) an Oracle instance has a log file, the so called alert.log. You should check this log file for error. If you login into the database with

sqlplus / as sysdba

If it is not started already start it with

startup nomount

and execute

show parameter background_dump_dest

the path will be displayed where you can find the alert log.

with

show parameter control_files 

all your controlfiles will be displayed

2) before you do something with your database make a copy of all files if you have enough place: datafiles, redo files, control files, spfile, init.ora file

3) check if 'E:\MEDIASERVER\ORACLE\ORADATA\XE\CONTROL.DBF' exists and is readable. Is it on a network drive?

4) if you have more than one control file you can check if they are identical (with windows tools like CMP) and replace the bad one by a good one

5) if you have only one control file or all your control file are damaged you can use a valid backup of a control file. But copying the control file from a running database is not a valid backup of a control file. If you don't have a valid backup of a control file you can recreate a control file. This can be done with the create CREATE CONTROLFILE command.

0

For the first case:

ORA-00202: control file: 'E:\MEDIASERVER\ORACLE\ORADATA\XE\CONTROL.DBF'

if you had multiple copies of the control file, (which you should - Oracle recommends multiplexing the control files; having 3 is a good move), you should have immediately shut down the instance, look at the three control files, and look at the date/timestamp and sizes of the files. Based on your error message, you might just have one control file; in which case recovery would be very difficult. If you do have multiple copies (assuming 3 here), you will generally have 2 files with similar date/timestamps, and same sizes, and same contents. You can check to ensure the contents are the same using the DOS command comp

Generally, you will see that the file listed in the error message has different contents from the other 2; you can then just copy one of the other control files over the "bad" control file, and try restarting.

The control file(s) keep track of all tablespaces and their associated files, and so when you attempted to restore things, you likely copied an UNDO tablespace file that didn't match the control information. The control files are updated constantly when changes happen in the database, so trying to restore an old control file backup (unless done via RMAN -- which you likely aren't using) would be useles..

So if you have just one control file, probably best to start from scratch with a new install, ensuring that you set it up to multiplex the control files. Ideally the control files would be on different drives, but if that isn't possible, better to have 3 control files on one drive than to have one control file on one drive. Recovering with just one control file is pretty complex.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.