Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 2 July 2013

How to recover your database when newly added datafiles with old backup?

Scenario: How to perform complete database recovery when the backup was taken before adding the datafiles, and you are rolling forward through the recovery process where yours recovery stops at a point where you added datafiles?
In this scenario you are performing a recovery by restoring the control file from backup. All data files are current and online redo logs are available. You had added few data files recently after the control file backup was taken, so control file backup has no awareness of the newly added data files. Thus during the recovery of controlfile, oracle database stops recovery when it encounters a CREATE TABLESPACE or ALTER TABLESPACE ADD DATAFILE operation indicating that controlfile has no awareness of the newly added datafiles with the following error:
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 15: ‘D:\oracle\oradata\sadhan\TMIS01.DBF’
ORA-01110: data file 16: ‘D:\oracle\oradata\sadhan\TMIS01.DBF’
At this stage you need to find the temporary and actual name of newly added datafile and rename the temporary name (usually indicating with error message) with actual name (check in alert.log).
SQL>SELECT FILE#,NAME FROM V$DATAFILE;
1     D:\ORACLE\ORADATA\SADHAN\SYSTEM01.DBF
2     D:\ORACLE\ORADATA\SADHAN\UNDOTBS01.DBF
3     D:\ORACLE\ORADATA\SADHAN\EXAMPLE01.DBF
4     D:\ORACLE\ORADATA\SADHAN\INDX01.DBF
5     D:\ORACLE\ORADATA\SADHAN\TOOLS01.DBF
6     D:\ORACLE\ORADATA\SADHAN\USERS01.DBF
7     D:\ORACLE\ORADATA\SADHAN\SDH_TIMS01.DBF
8     D:\ORACLE\ORADATA\SADHAN\SDH_TIMS02.DBF
9     G:\ORA_DBF_EXTENDED\SDH_HRMS01.DBF
10    F:\ORACLE\SADHAN\SDH_EDSS01.DBF
11    G:\ORA_DBF_EXTENDED\SDH_SHTR01.DBF
12    G:\ORA_DBF_EXTENDED\SDH_FIN01.DBF
13    F:\ORACLE\SADHAN\SDH_EDSS02.DBF
14    D:\ORACLE\ORADATA\SADHAN\INDX02.DBF
15    D:\ORACLE\ORADATA\SADHAN\UNNAMED00001
16    D:\ORACLE\ORADATA\SADHAN\UNNAMED00001
To identify the exact name of the data file check the error message in the alert log for the corresponding data File#. Once you identify the exact name of the data file, rename the file using alter database rename file command.
SQL>Alter database rename file ‘D:\ORACLE\ORADATA\SADHAN\UNNAMED00001’ TO ‘D:\ORACLE\ORADATA\SADHAN\TIMES01.DBF’;
SQL>Alter database rename file ‘D:\ORACLE\ORADATA\SADHAN\UNNAMED00002’ TO ‘D:\ORACLE\ORADATA\SADHAN\TIMES02.DBF’;
Then continue recovery by issuing the recovery statement:
RMAN>RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
You can choose to roll forward the database to any point in time or to complete recovery.  Then open the database in resetlogs option.

RMAN>Alter database open resetlogs;

1 comments:

  1. QUANTUM BINARY SIGNALS

    Professional trading signals delivered to your cell phone every day.

    Follow our signals today and gain up to 270% daily.

    ReplyDelete