raghavram

 Profile Answers by raghavram 

• Jul 7th, 2008

 

you can recover the datafiles byusing the following command,
mount the database and issues the following comand before openining it,

SQL> alter database recover datafile '< datafile file name.';
SQL> alter database open;

Part 2, if while doing a recovery the archive logs gets corrupted then we are in serious trouble. Rolling forward tranactions need the archive logs to be proper, if they are corrupted we will need some advanced recovery techniques, I forgot what the names of the advanced recovery utilities, but Oracle suggests in such a case the you get in touch with support before using those utilities. But to be more precise and answer your question, I do not think we can recover the database fully with corrupted archive logs, especially if the redo logs have been swtiched.

g.mrunalini

 Profile Answers by g.mrunalini 

• Jul 22nd, 2008

 

If the archive log which was missed has been still exists in the online redo log file. That means the log sequence number for that file in the online redo log files doesnt get overwritten at the time we plan to do recovery, then switch that redo log file again so that we will get the corrupted log file back.

mojidra

 Profile Answers by mojidra Questions by mojidra

• Sep 29th, 2008

 

Your database in archive log mode one of your archive file is missing there is no way to performed complete recovery you can able to do incomplete recovery, recover database till cancelled or there is another way if you have a export copy of that datafile latest than that archive log file missed then import that datafile data loss is lesser as compared to incomplete recovery.

Kamran Agayev A.

 Profile Answers by Kamran Agayev A. 

• Sep 8th, 2009

 

In this situation you're not able to recover the database. Of course, it can be done by using nonsupported and nondocumented parameter "_ALLOW_RESETLOGS_CORRUPTED" but you HAVE NOT to use it without Oracle Support's suggestion

If your database is running, take a whole backup, delete all archived redo log files and start creating archived redo log files again

nvkchaitanya

 Profile Answers by nvkchaitanya 

• Dec 7th, 2010

 

In this critical situation I had a pritical example which follows this.
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 9867098396261 generated at 03/21/2008 13:37:44 needed for
thread 1
ORA-00289: suggestion : /arcredo/XSCLFY/log1_648355446_2093.arc
ORA-00280: change 9867098396261 for thread 1 is in sequence #2093

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’
ORA-01112: media recovery not started

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/u100/oradata/XSCLFY/SYSTEM01_SCLFY.dbf’

After doing some research, we found out one hidden parameter (_ALLOW_RESETLOGS_CORRUPTION=TRUE) will allow us to open database even though it’s not properly recovered.

We forced open the database by setting the _ALLOW_RESETLOGS_CORRUPTION=TRUE. It allows us to open database but instance crashed immediately after open. we checked the alert.log file and found out we have undo tablespace corruption.

Alert log shows below error

Errors in file /u01/XSCLFYDB/admin/XSCLFY/udump/xsclfy_ora_9225.trc:
ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue Mar 25 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340

To resolve undo corruption issue, we changed undo_management to “Manual” in init.ora. Now it allowed us to open database successfully. Once database was up and running, we created new undo tablespace and dropped old corrupted undo tablespace. we changed back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

It resolved our issue and database was up and running without any issue.

_ALLOW_RESETLOGS_CORRUPTION=TRUE allows database to open without consistency checks. This may result in a corrupted database. The database should be recreated.

As per Oracle Metalink, there is no 100% guarantee that setting _ALLOW_RESETLOGS_CORRUPTION=TRUE will open the database. However, once the database is opened, then we must immediately rebuild the database. Database rebuild means doing the following, namely: (1) perform a full-database export, (2) create a brand new and separate database, and finally (3) import the recent export dump. This option can be tedious and time consuming, but once we successfully open the new database, then we expect minimal or perhaps no data loss at all. Before you try this option, ensure that you have a good and valid backup of the current database.Solution:

1) Set _ALLOW_RESETLOGS_CORRUPTION=TRUE in init.ora file.
2) Startup Mount
3) Recover database
4) Alter database open resetlogs.
5) reset undo_management to “manual” in init.ora file.
6) startup database
7) Create new undo tablespace
changed undo_management to “AUTO” and undo_tablespace to “NewTablespace”
9) Bounce database.