Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

urgent recovery help needed

437541Jul 16 2006 — edited Jul 18 2006
Hello I have 817 database on Solaris and I do not have cold back up of the db. The db is in NOARCHIVE mode. I tried to bounce the db but I cannot open the db as I get the following error:
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 2 change 9224405 time 06/05/2006 18:42:20
ORA-00312: online log 3 thread 1: '/u01/oradata/etpdb1/redo03.log'
Here is more info:
SVRMGR> select * from V$logfile;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------------------------------------
1 /u01/oradata/etpdb1/redo01.log
2 /u01/oradata/etpdb1/redo02.log
3 /u01/oradata/etpdb1/redo03.log

SVRMGR> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHAN FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ---------- ---------
1 1 0 5242880 1 YES UNUSED 0
2 1 0 5242880 1 YES UNUSED 0
3 1 1401 5120000 1 NO CURRENT 9223256 05-JUN-06


SVRMGR> alter database open resetlogs;
alter database open resetlogs
*
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

Can anyone advise what can I do not to recover the db. Thanks

Comments

112887
Not a good time for the "you should have...." lecture.

Corrupt logs is quite bad.

The resetlogs option may be the way to go but you will have to run
"recover database until cancel" first
then cancel
then do the alter database open resetlogs

You might want to copy your entires db first before doing this. You have a high probability of having database corruption - if you get the database open you may want to export everything you can and recreate the database.

If you have Oracle support then open a TAR and check with them first

Chris
164995
NO ArchiveLog -- Online Redo Log corrupted.
if you have multiplexing enabled copy the redo log of the other member
and then see if db would open with that.

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14192/strategy002.htm#sthref122

Your case is probably
-> Whether the lost redo log group was current

You have very limited option other than to go back to what ever backups you have.

Engage oracle support is the best option as they may suggest other alternatives, there is a parameter ALLOWRESETLOGS_CORRUPTION but it has serious limitations check with Oracle first.

http://www.sidibe.net/allow_resetlog.html

Good Luck
521427
1 1 0 5242880 1 YES UNUSED 0
2 1 0 5242880 1 YES UNUSED 0
3 1 1401 5120000 1 NO CURRENT 9223256 05-JUN-06

Archived yes and status UNUSED for Group 1 and 2?
Did you try Alter system switch logfile;
437541
Gopal,
Thanks a lot. I can open the db by using "_allow_resetlogs_corruption" parameter. Do I need to do anything special now since the db is up (other than taking full backup off course)
kuljeet singh -
just take full backup of db,create new database and import the backup into newly created db.

Kuljeet
164995
1. Your old db is gone, Larry took it away.
2. Build a new one, exp / imp
3. Ensure that management understands about the consequences of NOARCHIVE log mode.
4. Take a backup after building the new db.

I am glad it worked for you.
437541
Guys, pardon me but I just want to know how is my old db gone. I can still login and check the data etc. Do I have to do imp/exp ?
112887
Yes - you really do need to export
create new database
import into new db

When you run the export you may get a number of corruption errors. Effectively once you have forced the database open as you have, you are on a salvage operation. If you don't do the export/recreate/import then you will spread the corruptions futher and futher through your database.

Chris
437541
Thank you Chris , Gopal for your suggestions. I will get started and get the bloody thing to a new db. Cheers !!
1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 15 2006
Added on Jul 16 2006
#general-database-discussions
9 comments
303 views