Apply Archive Logs to Cold Backup

Scenario : Apply archive logs to a cold backup to roll forward the database

I have a cold backup taken on the previous day and archive logs of today. So How can I apply the archive logs to the cold backup. If I just restore and start the database the it starts perfectly. What needs to be done in, we need to mount the database and inform the Oracle that the control file is a "backup control file" not the "present control file" then Oracle will allow to perform recovery.

If we use the automatic keyword in the recovery command then Oracle will apply all the available archive logs and prompts for the next archive log that is not generated. We will cancel the recovery after confirming that all available archive logs are applied and open the database with reset logs option.

Why Reset Logs: The SCN's in control files and datafiles are already roll forward, the restored log files (if restored, actually not required) control old change vectors that are no longer required, so Oracle has to flush the log files and start with LSN 1.

1. Keep a note of locations of datafiles, control files and redo log files

select name from v$controlfile;

select name from v$datafile;

select member from v$logfile;

2. Check if database is in archive log mode or not

archive log list

3. Enter the desired location for archive logs and place the database in archive log mode

vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora

log_archive_dest='/u01/CHARAN/oracle/11gR1/oradata/arch'

sqlplus / as sysdba

shut immediate;

startup mount;

alter database archivelog;

archive log list;

select log_mode from v$database;

4. Shut down the database to take cold backup

5. Copy Control Files, Data Files and Redo Logfiles to a different location

6. Start the database and insert some data and perfom log switch.

I created a user called pay and ran demobld.sql script to create demonstartion tables.

Now switch the log file, so that the changes are recorded in archived log files

7. Delete Control Files, Datafiles and Redo Log Files

8. Shut Abort the database

9. Restore the database and perform recovery by applying archive logs

startup mount;

alter database recover automatic using backup controlfile until cancel;

This command automatically applies all the available archive logs to the corresponding dbf's and fails looking for the next archive log. Since the DBA is aware that the archive log, Oracle is looking for is not generated, recovery can be cancelled using below command

recover cancel;

Since the data in restored online redo log files is older than in datafiles (as we forwarded the db by applying archives) we need to open the database with reset logs option to flush redo log files and start with log sequence 1

alter database open resetlogs;

10. Check whether your objects exists or not

11. Since database is opened with reset logs option, take full backup.

That's it!!!

Regards

Charan