Error after Restoring Datafiles in Archivelog but missing archive log files

[Former Member]

Hi all,

I have taken backup yesterday monrning in Archive log mode but have lost all the archive log files. Yesterday night i have restored my DB .

I m happy to have my datafile contents till yesterday morning.

But after restoring when i try to open th DB, i m facing the following errors:

SQL> Startup open;

Oracle instance started but in mounted stagell

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Error: ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: 'E:\oracle\'<SID>\sapdata1\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile until cancel;

ORA-00279 : change 26627919 generated at DATE TIMEneeded for thread 1

ORA-00289 : Suggestion E:\ORACLE\<SID>\ORAARCH\<SID>ARCHARC03020_0683715008.001

ORA-00280: change 266277189 for thread 1 is in sequence #3020

ORA - 01112: media recovery not started

SQL> recover;

ORA-00283: recovery session cancelled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Alert Log File:

Starting up ORACLE RDBMS Version: 10.2.0.2.0.

System parameters with non-default values:

processes = 80

sessions = 96

event = 10191 trace name context forever, level 1

sga_max_size = 633339904

shared_pool_size = 301989888

shared_pool_reserved_size= 30183260

filesystemio_options = setall

control_files = E:\ORACLE\IE6\ORIGLOGA\CNTRL\CNTRLIE6.DBF, E:\ORACLE\IE6\ORIGLOGB\CNTRL\CNTRLIE6.DBF, E:\ORACLE\IE6\SAPDATA1\CNTRL\CNTRLIE6.DBF

control_file_record_keep_time= 30

db_block_size = 8192

db_cache_size = 301989888

compatible = 10.2.0

log_archive_dest = E:\oracle\IE6\oraarch\IE6arch

log_buffer = 2854912

log_checkpoint_interval = 0

db_files = 254

log_checkpoints_to_alert = TRUE

dml_locks = 4000

undo_management = AUTO

undo_tablespace = PSAPUNDO

undo_retention = 43200

recyclebin = off

remote_os_authent = TRUE

remote_login_passwordfile= EXCLUSIVE

job_queue_processes = 1

background_dump_dest = E:\ORACLE\IE6\SAPTRACE\BACKGROUND

user_dump_dest = E:\ORACLE\IE6\SAPTRACE\USERTRACE

core_dump_dest = E:\ORACLE\IE6\SAPTRACE\BACKGROUND

optimizer_features_enable= 10.2.0.1

sort_area_size = 2097152

sort_area_retained_size = 0

db_name = IE6

open_cursors = 800

optimpeek_user_binds = FALSE

pga_aggregate_target = 402443468

workarea_size_policy = AUTO

statistics_level = typical

PMON started with pid=2, OS id=804

PSP0 started with pid=3, OS id=9580

MMAN started with pid=4, OS id=5840

DBW0 started with pid=5, OS id=8136

LGWR started with pid=6, OS id=8712

CKPT started with pid=7, OS id=1620

SMON started with pid=8, OS id=7084

RECO started with pid=9, OS id=9576

CJQ0 started with pid=10, OS id=9156

MMON started with pid=11, OS id=6000

MMNL started with pid=12, OS id=2856

Sat Apr 18 04:01:29 2009

ALTER DATABASE MOUNT

Sat Apr 18 04:01:33 2009

Setting recovery target incarnation to 1

Sat Apr 18 04:01:33 2009

Successful mount of redo thread 1, with mount id 1124466585

Sat Apr 18 04:01:33 2009

Database mounted in Exclusive Mode

Completed: ALTER DATABASE MOUNT

Sat Apr 18 04:01:34 2009

ALTER DATABASE OPEN

ORA-1589 signalled during: ALTER DATABASE OPEN...

Sat Apr 18 04:02:13 2009

alter database open resetlogs

Sat Apr 18 04:02:13 2009

ORA-1245 signalled during: alter database open resetlogs...

Sat Apr 18 04:11:05 2009

ALTER DATABASE RECOVER DATABASE UNTIL CANCEL

Sat Apr 18 04:11:05 2009

Media Recovery Start

Sat Apr 18 04:11:06 2009

Media Recovery failed with error 1610

ORA-283 signalled during: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL...

Sat Apr 18 04:11:28 2009

ALTER DATABASE RECOVER DATABASE USING BACKUP CONTROLFILE

Sat Apr 18 04:11:28 2009

Media Recovery Start

parallel recovery started with 3 processes

ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE USING BACKUP CONTROLFILE...

Sat Apr 18 04:17:23 2009

ALTER DATABASE RECOVER database using backup controlfile until cancel

Sat Apr 18 04:17:23 2009

Media Recovery Start

ORA-275 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...

Any suggestions pls..............

Thanks & Regards,

Subbu

[Former Member]

Useful Comments

[Former Member]

Hi Subba,

You have restored online backup and you didnt have archivelogs to apply.

Then i will suggest to take a copy of controlfile from source server, just change SID.

Do not change anything otherthan SID. cotrolfile should be in .trc extension.

Then delete old controlfiles from new server, copy this .trc controlfile to controlfile locations.

go to sql prompt, just open your database with option startup

It will give you hundred lines of errors starting with SP, no need to worry.

It will forcefully opens your database. This is an incomplete recovery.

If you have any doubts please let us know.

Regards

Nick Loy

[Former Member]

Hi All,

Thank u very much for ur valuable replies...

Thanks & Regards,

Subbu

[Former Member]

Please do not open database until recovery is complete and resetlogs.

recover database until point in time ( you can use until cancel/date and time/SCN)

reset logs

open database

Hope this helps.

[stefan_koehler]

Hello Subbu,

> I have taken backup yesterday monrning in Archive log mode but have lost all the archive log files.

You mean you have taken an online backup and lost all your archive log files after that online backup?

SQL> alter database open resetlogs;
Error: ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\oracle\'<SID>\sapdata1\SYSTEM_1\SYSTEM.DATA1'

SQL> recover database using backup controlfile until cancel;
ORA-00279 : change 26627919 generated at DATE TIMEneeded for thread 1
ORA-00289 : Suggestion E:\ORACLE\<SID>\ORAARCH\<SID>ARCHARC03020_0683715008.001
ORA-00280: change 266277189 for thread 1 is in sequence #3020

To perform a recovery with an online backup you will need all archive log files until the end of the online backup. If you have lost them you are not able to recover (or open) your database in a consistent state.

So the only suggestion we can do is:

You need the archive log files that were created during the online backup to perform an incomplete recovery ... if they are gone you can not restore your database.

Regards

Stefan

[Former Member]

Hi Stefan,

Thanks for ur reply..

To perform a recovery with an online backup you will need all archive log files until the end of the online 
backup. If you have lost them you are not able to recover (or open) your database in a consistent state

Yes, u r right that we do not have any archive logs...as u said we cannot recover to a consistent state, can we atleast recover the data from the data files

which are taken backup???

We are not in a need of data from the point of backup (after the backup).....

Could you please suggest??

Thanks & regards,

Subbu

[stefan_koehler]

Hello Subbu,

> can we atleast recover the data from the data files which are taken backup???

No this is not possible, because of oracle can not guarantee a consistent state in the data file itself while performing the online backup (has something to do with the OS / data file block size that is used by copying the data files)

> We are not in a need of data from the point of backup (after the backup).....

But if you still have a backup (and the needed archive log files) before yesterday morning - you can recover to the point before you have started the backup yesterday morning.

Sorry for the bad news.

Regards

Stefan

[Former Member]

There actually is a way to recover a database to an incomplete state.. BUT I WOULD NOT ADVISE DOING THIS UNLESS YOU'VE EXHAUSTED EVERY OTHER ALTERNATIVE.

http://www.dbspecialists.com/files/presentations/missing_logs.html

allowresetlogs_corruption=TRUE

This parameter is a u201Chiddenu201D or undocumented parameteru2014one of those which youu2019re never supposed to use unless told to do so by Oracle Support. We probably should have pointed out earlier that you shouldnu2019t be doing this entire exercise unless youu2019ve failed with everything Oracle Support has told you to do. The description of this parameter reads u201Callow resetlogs even if it will cause corruptionu201D. Some of the caveats to be found on Metalink regarding this parameter include:

Best of luck, I'm off to check my backups!

Regards,

Rich