roll forward the database using archive logs oracle

How To Roll Forward The Database Using a Old Control File, With Archivfiles? 

Goal  

How to roll forward the database using a old controlfile, with archived redologfiles in disaster situation like bellows

• Crash database
• Drop table or data files accidently   
• Server disk failure

    

Here have done as above diagram scheduled the cold backup to backup server and copy every day creation archive log files to backup server daily      

Work round

I assume here the production instance has been data corruption middle of the week (Tuesday morning)  

So we have already copied the untie Tuesday 2am archive logs   to backup server.

Steps

1 Shutdown down the production instance and  host server  ex  192.168.1.5

2 change the hostname and ip adders on backup server as production server  

vi /etc/hosts

vi /etc/sysconfig/network

3 reboot server

4 take server to mount mode (old Backup server)

su oracle

sqlplus / as sysdba

SQL>startup mount;

5 And now issue the command to start the recovery and apply all the  archivelog files that are newer than any of the control or datafiles that we
restored.

SQL> alter database recover until cancel using backup controlfile;

alter database recover until cancel using backup controlfile
* ORA-00279: change 216252 generated at 04/09/99 16:39:56 needed for thread 1 ORA-00289: suggestion : D:\ORA80\DATABASE\ARCHIVE\ARC85.1
ORA-00280: change 216252 for thread 1 is in sequence #85

6 then applied the new archive logs to database using following command

SQL> alter database recover continue default;

alter database recover continue default

* ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1

ORA-00289: suggestion : /u01/finsys/db/tech_st/flash_recovery_area/PROD/archivelog/ARC86.1

ORA-00280: change 216265 for thread 1 is in sequence#86

ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for this recovery

SQL> alter database recover continue default;

alter database recover continue default

* ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1

ORA-00289: suggestion : /u01/finsys/db/tech_st/flash_recovery_area/PROD/archivelog/ARC87.1

ORA-00280: change 216265 for thread 1 is in sequence#87

ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for this recovery

SQL> alter database recover continue default;

alter database recover continue default

* ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1

ORA-00289: suggestion : /u01/finsys/db/tech_st/flash_recovery_area/PROD/archivelog/ARC88.1

ORA-00280: change 216265 for thread 1 is in sequence#88

ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for this recovery

.

.

.continually have to execute the this command

.

.

.

SQL> alter database recover continue default;

alter database recover continue default

* ORA-00279: change 216265 generated at 04/09/99 16:45:34 needed for thread 1

ORA-00289: suggestion : /u01/finsys/db/tech_st/flash_recovery_area/PROD/archivelog/ARC184.1

ORA-00280: change 216265 for thread 1 is in sequence#184

ORA-00278: log file 'D:\ORA80\DATABASE\ARCHIVE\ARC85.1' no longer needed for this recovery

Note :-  here you will be need execute the above command several times  as you wish to take database to archive log number,  which mean you Archive log number

My case I will have to apply more than new 96 archive logs because my instance lag target has been set to 30 min

(its mean every 30min database  automatically hit the  new archive log to destination location  )

 

We could have stopped after 184 but there is no harm in continuing until the system cannot find the required file.

7 Now we stop the recovery and open the database:

SQL> alter database recover cancel;

alter database recover cancel Statement processed.

8 Now we open the database with reset log

SQL> alter database open resetlogs;

Statement processed.

And, finally, we prove that the new information that was not in the backup and was only stored in the archive log files is now recovered:

9 run autoconfig  on database tire and  apps tire

10 start the application Tire