-- Thanks for the question regarding "Alter Database Recover Vs Recover Database - Reg.", version 8.1.7.0.0

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 we 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.


 

Reviews

July 16, 2001 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  cleared my confusion Alter Database Recover Vs Recover Database - Reg   July 16, 2001 - 9pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  It was very useful for me and recently i faced the same problem. Thanks TOM.   November 17, 2003 - 7am Central time zone Bookmark | Bottom | Top Reviewer: Yanis Siminelakis from Athens, HELLAS (Greece) Totally confused   December 11, 2003 - 12am Central time zone Bookmark | Bottom | Top Reviewer: Anil Pant from Hyderabad hi, Your response has confused me. Can u explain it ? Followup   December 11, 2003 - 5am Central time zone: 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   June 3, 2004 - 2pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  So, if I am scripting the recovery, I have no choice but to use the ALTER DATABASE RECOVER, right? Followup   June 3, 2004 - 2pm Central time zone: depends on what you use to "script" the recovery. you can use sqlplus commands (recover), rman commands, or straight sql (alter)   June 4, 2004 - 6am Central time zone Bookmark | Bottom | Top Reviewer: Shivdeep Modi from India Cleared the doubt between the two commands recover command that worked ...   August 24, 2004 - 9am Central time zone Bookmark | Bottom | Top Reviewer: A reader  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

Write a Review