Oracle 'SYSTEM01.DBF' error

Hi,

I'm facing some problems with oracle 9i on my test machine. It seems the database has badly crashed. While using 'startup mount;' command I was receiving some error for REDO03.LOG file, which I solved somehow by using 'alter database rename file 'old path' to 'new path';' command.

On using 'alter database open resetlogs;' command, I receive error message as below:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ECC\SYSTEM01.DBF'

Please advice how to resolve this error. Secondly, please post your replies with commands as I'm not too used to Oracle.

why did you do the alter database rename

if you dont have the log you will need to do a recovery

Maybe this will work:

Code:

---

ALTER DATABASE RECOVER DATAFILE '{the file you renamed}';

---

If not, you may need to do recovery as davey23uk posted.
;)

I actually renamed that file back to original one.... Do you think I still need to use this recovery command?

SQL> select member from v$logfile;

MEMBER
----------------------------------
C:\ORACLE\ECC\REDO01.LOG
C:\ORACLE\ECC\REDO02.LOG
C:\ORACLE\ECC\REDO03.LOG

Even after this change I receive the same error message:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\ECC\SYSTEM01.DBF'

what do you mean renamed the file back to the original one

what exactly did you do and why

if you overwrote the online log - you are screwed

i used command

"alter database rename file ''C:\ORACLE\ECC\REDO033.LOG' to 'C:\ORACLE\ECC\REDO03.LOG';"

I admit I must have made some serious mistake & do require your assistance for solution....

why did you do the rename - for what purpose?

why did it have the wrong name

Mount the database and recover system datafile.

SQL> alter database recover datafile 1;

As Dave said, if there is any mess-up with redo log files then you will screwed else you will be fine.

Thanks,

Actually in the beginning I was receiving below error msg when executed 'startup;'

startup;
ORACLE instance started.
Total System Global Area 571547208 bytes
Fixed Size 454216 bytes
Variable Size 243269632 bytes
Database Buffers 327155712 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 95218 change 63039931 time 12/19/2008 19:51:05
ORA-00312: online log 3 thread 1: 'C:\ORACLE\ECC\REDO03.LOG'

The original log file is REDO03.LOG. I thought renaming to REDO033.LOG might help, then afterwards when I executed 'startup mount;' command, the database got mounted successfully. But on trying 'alter database open resetlogs;' I started receiving below error:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ECC\SYSTEM01.DBF'

Due to this reason I had to rename the log file back to REDO03.LOG.....

SQL> alter database recover datafile 1;
alter database recover datafile 1
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

you need to do a recovery - unless you ultiplex your redo logs

How? Can you please help?

Dave -

Do you mean complete database recovery?

I am sure he doesn't multiplex redo log files.

Thanks,

call oracle support - they will talk you through it

Dave -

Calling oracle support is the last option for all the DBAs on the earth after putting every attempt from there side. Thanks for your suggestion.

As per the error posted, I understood that one of the redo logs had a block corruption. He didn't mention whether that redo log is current or inactive. He renamed the corrupted log with other name (not sure how far renaming on the same physical location works). So, the work around i suggest is ...

1. Recover until cancel
2. Open resetlogs.

Appreciate comments on this.

Thanks,

Quote:

---

Originally Posted by vnktummala

Dave -

Calling oracle support is the last option for all the DBAs on the earth after putting every attempt from there side. Thanks for your suggestion.

---

WHAT? if you have no clue - oracle support is the first call, thats what you pay them for!

Quote:

---

As per the error posted, I understood that one of the redo logs had a block corruption. He didn't mention whether that redo log is current or inactive. He renamed the corrupted log with other name (not sure how far renaming on the same physical location works). So, the work around i suggest is ...

1. Recover until cancel
2. Open resetlogs.

Appreciate comments on this.

Thanks,

---

if it wasnt the online log then no recovery would be needed would it?

if you are going to say dangerous advice, keep it to yourself!

There you go ... you gave the clue. No need to contact oracle support now :)

Thanks Dave.

Still giving errors.....

SQL> recover until cancel;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

for the last time - call oracle support - it's why you pay them, so they can help you

do you have any full backup?The safe thing would be restore the full database and then recover it and open using reset logs.