RollForward Cold database backup (Includes creating a datafile added after backup)

Sometimes it you may face a situation where you face a crash on your database and unfortunately, your recent backups are corrupt or lost. However, if you have an cold backup of your database and all the archive logs generated since that cold backup, you can restore and recover your database without losing any data. Today i’ll show you the steps of achieving this.
The problem with rolling forward a cold backup is that cold backup is consistent in itself and it does not need any recovery, hence when you try to open the database with cold backup, it opens without asking for any recovery. However, it has the old data and it is missing any transactions which took place after you took this cold backup. So we need to apply archives over this cold backup to get all the missing data.

ORACLE_SID = BOTI

ORACLE_HOME = /app01/oratest/TEST/db/tech_st/11.1.0

DBFs, CTLs and Redo Logs location: /app01/oratest/oradata/BOTI/BOTI

Archive Logs location: /app01/oratest/archives

Cold Backups stored at: /app02/backup/oradata/BOTI/BOTI

Assume that you faced a crash and all your DBFs, CTLs and Redo Logs are lost. However, you have a cold backup(DBFs, CTLs and Redo Logs) and all the all the Archive Logs generated after you took this cold backup.
Here are the steps:
Step 1: Restore the backups of DBFs, CTLs and Redo Logs from cold backup:

bash $ cp /app02/backup/oradata/BOTI/BOTI/* /app01/oratest/oradata/BOTI/BOTI

Step 2: Mount the Instance:

bash $ sqlplus / as sysdba

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2109352 bytes
Variable Size             272633944 bytes
Database Buffers          788529152 bytes
Redo Buffers                5980160 bytes
Database mounted.

Start Recovery:

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 1059106 generated at 01/12/2011 11:15:49 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_53_739623496.dbf
ORA-00280: change 1059106 for thread 1 is in sequence #53

SQL>

It will come out and SQL prompt will be returned. Now run:

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059486 generated at 01/12/2011 11:18:55 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_54_739623496.dbf
ORA-00280: change 1059486 for thread 1 is in sequence #54
ORA-00278: log file '/app01/oratest/archives/1_53_739623496.dbf' no longer
needed for this recovery

Keep running this command, untill you get the message: ORA-00308: cannot open archived log

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059489 generated at 01/12/2011 11:18:57 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_55_739623496.dbf
ORA-00280: change 1059489 for thread 1 is in sequence #55
ORA-00278: log file '/app01/oratest/archives/1_54_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059494 generated at 01/12/2011 11:19:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_56_739623496.dbf
ORA-00280: change 1059494 for thread 1 is in sequence #56
ORA-00278: log file '/app01/oratest/archives/1_55_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059778 generated at 01/12/2011 11:19:55 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_57_739623496.dbf
ORA-00280: change 1059778 for thread 1 is in sequence #57
ORA-00278: log file '/app01/oratest/archives/1_56_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059781 generated at 01/12/2011 11:19:56 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_58_739623496.dbf
ORA-00280: change 1059781 for thread 1 is in sequence #58
ORA-00278: log file '/app01/oratest/archives/1_57_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059786 generated at 01/12/2011 11:20:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_59_739623496.dbf
ORA-00280: change 1059786 for thread 1 is in sequence #59
ORA-00278: log file '/app01/oratest/archives/1_58_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059789 generated at 01/12/2011 11:20:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_60_739623496.dbf
ORA-00280: change 1059789 for thread 1 is in sequence #60
ORA-00278: log file '/app01/oratest/archives/1_59_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059792 generated at 01/12/2011 11:20:03 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_61_739623496.dbf
ORA-00280: change 1059792 for thread 1 is in sequence #61
ORA-00278: log file '/app01/oratest/archives/1_60_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00308: cannot open archived log
'/app01/oratest/archives/1_61_739623496.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

When you recieve the above messages, it means that all the available archive logs have been applied:
Now issue:

SQL> alter database recover cancel;

Database altered.

& …

SQL> alter database open resetlogs;

Database altered.

That’s it. You have got your Database back.
Note: Please note that we could have issued “alter database recover cancel;” without applying all the archives and still be able to open the database.
However, in that case we, would be missing some transactions.
****************************************************
Special Case: Recovering Through an added datafile
Consider the case when archive logs are applied and the cold backup which you are restoring, rolls forward through a “create tablespace” or a “alter tablespace add datafile” scenario. In this case, you need to do the following:
Assume that after taking the cold backup, you created a tablespace “MTS” with a datafile “mts01.dbf” on production.
When you try to recover the DB from cold backup, the recovery will fail when it goes through the archives which create the TABLESPACE MTS.

bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jan 12 11:33:17 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1069252608 bytes
Fixed Size                  2109352 bytes
Variable Size             272633944 bytes
Database Buffers          788529152 bytes
Redo Buffers                5980160 bytes
Database mounted.

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 1059106 generated at 01/12/2011 11:15:49 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_53_739623496.dbf
ORA-00280: change 1059106 for thread 1 is in sequence #53

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059486 generated at 01/12/2011 11:18:55 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_54_739623496.dbf
ORA-00280: change 1059486 for thread 1 is in sequence #54
ORA-00278: log file '/app01/oratest/archives/1_53_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059489 generated at 01/12/2011 11:18:57 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_55_739623496.dbf
ORA-00280: change 1059489 for thread 1 is in sequence #55
ORA-00278: log file '/app01/oratest/archives/1_54_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059494 generated at 01/12/2011 11:19:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_56_739623496.dbf
ORA-00280: change 1059494 for thread 1 is in sequence #56
ORA-00278: log file '/app01/oratest/archives/1_55_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/app01/oratest/oradata/BOTI/BOTI/mts02.dbf'

Recovery Session cancelled when recovery goes through an ADD DATAFILE scenatrio.
Start the recovery again.

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u2/oratest/db/tech_st/11.1.0/dbs/UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u2/oratest/db/tech_st/11.1.0/dbs/UNNAMED00006'

Here Oracle creates a placeholder(/u2/oratest/db/tech_st/11.1.0/dbs/UNNAMED00006) in controlfile for the missing datafile. Oracle will create one placeholder for each added datafile and polaceholder is named as “UNNAMED00006″ and by default behavior, created in $ORACLE_HOME/dbs.
Now, do the following:
Come out of the SQL temprarily and create the missing DBF at OS level.

SQL> !
bash-3.00$ touch /app01/oratest/oradata/BOTI/BOTI/mts02.dbf
bash-3.00$ exit
exit

Then run the following command:

SQL> ALTER DATABASE CREATE DATAFILE '/u2/oratest/db/tech_st/11.1.0/dbs/UNNAMED00006' AS '/app01/oratest/oradata/BOTI/BOTI/mts02.dbf';

Database altered.

Note: If the recovery go through more than one added datafiles, you need to identify that which UNNAMES000XX corresponds to which DBF. For example, if you added two DBFs “mts01.dbf” and “mts02.dbf”, Oracle will create two placeholders in controlfile e.g. UNNAMED00006 and UNNAMED00007. You need to identify which placeholder is created for which DBF. For this, you need to check the alert log file. In alert log, Oracle clearly mentions which placeholder entry belongs to which DBF. e.g. alert log will show messages like the one below:

alter database recover continue default
Media Recovery Log /app01/oratest/archives/1_56_739623496.dbf
File #6 added to control file as 'UNNAMED00006'. Originally created as:
'/app01/oratest/oradata/BOTI/BOTI/mts02.dbf'

Now, Start the Recovery again:

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00279: change 1059513 generated at 01/12/2011 11:19:50 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_56_739623496.dbf
ORA-00280: change 1059513 for thread 1 is in sequence #56

SQL> alter database recover until cancel using backup controlfile;
alter database recover until cancel using backup controlfile
*
ERROR at line 1:
ORA-00275: media recovery has already been started

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059778 generated at 01/12/2011 11:19:55 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_57_739623496.dbf
ORA-00280: change 1059778 for thread 1 is in sequence #57
ORA-00278: log file '/app01/oratest/archives/1_56_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059781 generated at 01/12/2011 11:19:56 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_58_739623496.dbf
ORA-00280: change 1059781 for thread 1 is in sequence #58
ORA-00278: log file '/app01/oratest/archives/1_57_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059786 generated at 01/12/2011 11:20:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_59_739623496.dbf
ORA-00280: change 1059786 for thread 1 is in sequence #59
ORA-00278: log file '/app01/oratest/archives/1_58_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059789 generated at 01/12/2011 11:20:02 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_60_739623496.dbf
ORA-00280: change 1059789 for thread 1 is in sequence #60
ORA-00278: log file '/app01/oratest/archives/1_59_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00279: change 1059792 generated at 01/12/2011 11:20:03 needed for thread 1
ORA-00289: suggestion : /app01/oratest/archives/1_61_739623496.dbf
ORA-00280: change 1059792 for thread 1 is in sequence #61
ORA-00278: log file '/app01/oratest/archives/1_60_739623496.dbf' no longer
needed for this recovery

SQL> alter database recover continue default;
alter database recover continue default
*
ERROR at line 1:
ORA-00308: cannot open archived log
'/app01/oratest/archives/1_61_739623496.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

SQL> alter database recover cancel;

Database altered.

SQL> alter database open resetlogs;

Database altered.

Thats it. You are done !!!
****************************************************
This procedure can be used to safeguard your production database if you are not using DR or Replication.
You need to follow the following steps:
1. On another server, install Oracle Home with the same version and patchset level as it is on production box.
2. Please make sure that the directory structure used for Oracle Home, DBF, CTL and Redo Logs location, Archive Logs location etc. should be exactly same on this machine as on production. You may use soft links if needed.
3. Copy the init.ora from production and place in ORACLE_HOME/dbs.
4. Copy all the DBFs, CTLs and Redo Logs from cold backup of production.
5. Startup Database in mount mode.
6. Apply the archive logs manually as described in the procedure mentioned above.
7. Don’t open the database and keep applying archive logs from production manually.
8. If you face any crash on production database and you are not able to recover it within affordable time limit, just apply all the remaining logs from production to this standby database. You may also have to apply Redo logs, so transfer the Redo logs from production as well.
9. Once all the archives have been applied, you can open the database with resetlogs.
10. Use this database as your production database now.
**********************************************************************************
I have tested this procedure successfully. Hope this will be helpful for everyone.
Thanks for your time.
Refereneces: MOS Note: [ID 161742.1] – How To Roll Forward The Database Using a Old Control File, With Archivfiles?