Automatic Recovery During Applying logs

With the command RECOVER(which is SQL*Plus command) or ALTER DATABASE RECOVER you can perform media recovery. It is always good to use SQL*plus while recovery of database because with it you can perform automatic recovery. Whenever we use RECOVER command it displays a prompt in order to supply archived redo log. But if they logs are in a location in the disk then you can perform automatic recovery where it will not prompt to provide any location.

There are two ways to perform automatic recovery.

1)Issuing SET AUTORECOVERY ON before issuing the RECOVER command.

SQL>SET AUTORECOVERY ON;
SQL>RECOVER DATABASE;

2)Specifying the AUTOMATIC keyword as an option of the RECOVER command.

SQL>RECOVER AUTOMATIC DATABASE;


Recovering When Archived Logs Are in the Default Location
-------------------------------------------------------------------
Whenever you issue RECOVER command then it will prompt as,

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [ for suggested | AUTO | FROM logsource | CANCEL ]

If you have not changed your archived log destination and the format is in tact then if you specify AUTO it will automatically constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest
value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file.

Recovering When Archived Logs Are in a Nondefault Location
--------------------------------------------------------------------
When archived redo logs is not their default location then you have to following any options from these two.

1) Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.

2) Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command.

1) Edit the LOG_ARCHIVE_DEST_n parameter
-----------------------------------

i)$ cp /backup/arch/* /tmp/
ii)LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'
iii)LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
iv)STARTUP MOUNT
v)RECOVER DATABASE

2)SET statement in SQL*Plus(Override the arhived redo log source)
---------------------------------------
i)$ cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
ii)SET LOGSOURCE "/tmp"
iii)RECOVER AUTOMATIC TABLESPACE users

you can avoid running SET LOGSOURCE and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"

Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.