Skip to Main Content
  • Questions
  • Alter Database Recover Vs Recover Database - Reg.

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: July 12, 2001 - 11:11 am UTC

Last updated: August 02, 2012 - 9:05 am UTC

Version: 8.1.7.0.0

Viewed 10K+ times! This question is

You Asked

Hi Tom

When I issued "Recover Database until time '2001-07-12:10:10:10'" it worked fine.

But when i issued "Alter databae recover until time '2001-07-12:10:10:10'" media recovery failed. Why

Working on NT.

When u have to "Alter database recover until time ' '"?

Internaly what is the difference between the two statements.



--- messages displayed at sql prompt


SQL> alter database recover until time '2001-07-12:21:11:59';
alter database recover until time '2001-07-12:21:11:59'
*
ERROR at line 1:
ORA-00279: change 399983 generated at 07/11/2001 20:01:11 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00015.ARC
ORA-00280: change 399983 for thread 1 is in sequence #15


SQL> recover database until time '2001-07-12:21:11:59';
ORA-00279: change 399983 generated at 07/11/2001 20:01:11 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00015.ARC
ORA-00280: change 399983 for thread 1 is in sequence #15


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 420004 generated at 07/12/2001 21:07:50 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00016.ARC
ORA-00280: change 420004 for thread 1 is in sequence #16
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00015.ARC' no
longer needed for this recovery


ORA-00279: change 420049 generated at 07/12/2001 21:08:22 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00017.ARC
ORA-00280: change 420049 for thread 1 is in sequence #17
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00016.ARC' no
longer needed for this recovery


ORA-00279: change 420068 generated at 07/12/2001 21:08:25 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00018.ARC
ORA-00280: change 420068 for thread 1 is in sequence #18
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00017.ARC' no
longer needed for this recovery


ORA-00279: change 420089 generated at 07/12/2001 21:08:28 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00019.ARC
ORA-00280: change 420089 for thread 1 is in sequence #19
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00018.ARC' no
longer needed for this recovery


ORA-00279: change 420108 generated at 07/12/2001 21:08:30 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00020.ARC
ORA-00280: change 420108 for thread 1 is in sequence #20
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00019.ARC' no
longer needed for this recovery


ORA-00279: change 420128 generated at 07/12/2001 21:08:34 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00021.ARC
ORA-00280: change 420128 for thread 1 is in sequence #21
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00020.ARC' no
longer needed for this recovery


ORA-00279: change 420146 generated at 07/12/2001 21:08:35 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00022.ARC
ORA-00280: change 420146 for thread 1 is in sequence #22
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00021.ARC' no
longer needed for this recovery


ORA-00279: change 420166 generated at 07/12/2001 21:08:40 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00023.ARC
ORA-00280: change 420166 for thread 1 is in sequence #23
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00022.ARC' no
longer needed for this recovery


ORA-00279: change 420188 generated at 07/12/2001 21:08:42 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00024.ARC
ORA-00280: change 420188 for thread 1 is in sequence #24
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00023.ARC' no
longer needed for this recovery


ORA-00279: change 420208 generated at 07/12/2001 21:08:47 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00025.ARC
ORA-00280: change 420208 for thread 1 is in sequence #25
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00024.ARC' no
longer needed for this recovery


ORA-00279: change 420230 generated at 07/12/2001 21:08:49 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00026.ARC
ORA-00280: change 420230 for thread 1 is in sequence #26
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00025.ARC' no
longer needed for this recovery


ORA-00279: change 420250 generated at 07/12/2001 21:08:53 needed for thread 1
ORA-00289: suggestion : E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00027.ARC
ORA-00280: change 420250 for thread 1 is in sequence #27
ORA-00278: log file 'E:\ORACLE\ORADATA\BACK1\ARCHIVE\BACK1T001S00026.ARC' no
longer needed for this recovery


Log applied.
Media recovery complete.
SQL>



Regards




and Tom said...

The alter database command allows you to do things manually (which in your case is why it failed). From the sql ref manual for alter database recover:


Note: If you do not have special media requirements, Oracle Corporation recommends that you use the SQL*Plus RECOVER statement.



You see, RECOVER is not even a SQL command, rather it is a SQLPlus command (RECOVER isn't documented in the SQL reference, its not SQL). It is an automated command to do the "normal" recovery steps and works in most cases.

When the alter database raised an error, it also suggested the logfile to apply. You could then use the alter database command once again to apply that redo log file using the LOGFILE clause. You would continue on applying logs until it was done.

Or, you can use the RECOVER sqlplus command and let it do the work for you.




Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 16, 2001 - 2:03 pm UTC

cleared my confusion

Alter Database Recover Vs Recover Database - Reg

A reader, July 16, 2001 - 9:52 pm UTC

It was very useful for me and recently i faced the same problem. Thanks TOM.

Yanis Siminelakis, November 17, 2003 - 7:44 am UTC


Totally confused

Anil Pant, December 11, 2003 - 12:07 am UTC

hi,
Your response has confused me. Can u explain it ?

Tom Kyte
December 11, 2003 - 5:43 am UTC

it is relatively straightforward

a) use ALTER DATABASE if you want to do the recoverying step by step, manually -- issueing each and every single solitary command to recover the database (apply logfiles).

b) use the SQLPLUS 'recover' command to have sqlplus automate the steps in a) for you.

Scripting

A reader, June 03, 2004 - 2:13 pm UTC

So, if I am scripting the recovery, I have no choice but to use the ALTER DATABASE RECOVER, right?

Tom Kyte
June 03, 2004 - 2:49 pm UTC

depends on what you use to "script" the recovery.

you can use sqlplus commands (recover), rman commands, or straight sql (alter)

Shivdeep Modi, June 04, 2004 - 6:31 am UTC

Cleared the doubt between the two commands

recover command that worked ...

A reader, August 24, 2004 - 9:42 am UTC

ALTER DATABASE RECOVER automatic database until time '2004-08-10-01:00:00' using backup controlfile;

this worked for me. Only different thing I had to do was put 'automatic' there.

thanks

Using "Alter Database Recover Vs Recover Database - Reg.", version 8.1.7.0.0

Alex, August 01, 2012 - 8:56 pm UTC

Hi Tom,
we had a crash on our Server Oracle (8.1.7 on W2K o.s.) during the night (no electricity for a while and the ups switch off after time). We do not have archivelog mode on and our backup is crashed too. Is it possible to use the "alter database recover vs recover data" functionality in this example? or it works only under archivelog mode? one more tip: Oracle istance can be started, mount but can't open database.
I'm sorry for my question, but I'm trainer. If it is not correct to post here my question, pls can you tell me where to post it.
Thanks in advanced and regards,
Alex

Tom Kyte
August 02, 2012 - 9:05 am UTC

if you are in noarchivelog mode, there is no recovery from media failure at all - none.

what error do you get when you try to open the database?

Using "Alter Database Recover Vs Recover Database - Reg.", version 8.1.7.0.0

Alex, August 03, 2012 - 2:04 am UTC

Hi,
When I try to open database I have ORA-01113 "necessary to repair support 2" and ORA-01110 "file of data 2: path\RBS01.DBF". Then I start the "recover datafile 2", but it asking/searching for a file "ARC56199.001" that there isn't anywhere in the support.
If you need the log, pls tell me.
Thnks a lot,
Alex

Dragutin Jastrebic, August 11, 2012 - 6:10 am UTC

Since Tom seems to be to busy these days, I will
try to help you, if you are still on the problem (and I hope
Tom don't mind)
You probably won't be able to recover (because you're
in noarchivelog) , but let's
try to put some light on what's happening with your
database.

Can you execute the commands below and post the result:


alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';


select * from v$log;


select file#,checkpoint_time,to_char(checkpoint_change#) from v$datafile_header;


select to_char(checkpoint_change#) from v$database;

Dragutin

Dragutin Jastrebic, August 11, 2012 - 6:27 am UTC

I wanted to type "too busy" and "Tom does not mind"

Using "Alter Database Recover Vs Recover Database - Reg.", version 8.1.7.0.0

Alex, October 07, 2012 - 11:39 am UTC

First of all, I would like to excuse myself to Mr. Dragutin for the delay of my answer: I'M VERY SORRY!
At the end we solved the problem: we contacted an Oracle consultant and after 5 hours of hard work, he resumed the database and saved all the data.
Thank you very much for your support. Your forum always give us good tips!
A big "ciao",
Alex