in Oracle

Recovering a Datafile created after the last Backup

Suppose you added a datafile to the database after the last backup and do not yet have a backup of the datafile when the file is lost / corrupt.

How does Oracle RMAN handle the RESTORE / RECOVER?

Here’s the situation : Tablespace HEMANT has three datafiles, of which file id 5 and 6 have been added after the last backup. (This scenario tested in 11.2.0.4)
Note : Some of you may know that Oracle can reuse file ids. So file ids 5 and 6 which may have been for some other tablespace / datafiles that had been dropped in the past were reused for these two datafiles added to the tablespace in September 17.

SQL> select v.file#,v.creation_time, v.name
2 from v$datafile v, v$tablespace t
3 where v.ts#=t.ts#
4 and t.name = 'HEMANT'
5 order by 2;

FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf

6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf


SQL>
RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 230.29M DISK 00:00:42 07-SEP-17
BP Key: 68 Status: AVAILABLE Compressed: YES Tag: TAG20170907T230339
Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 7608466 07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN>

What happens if I lose all three datafiles ?

[oracle@ora11204 datafile]$ pwd
/u01/app/oracle/oradata/ORCL/datafile
[oracle@ora11204 datafile]$ ls -l *hemant*
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktv2jd_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktvw02_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:35 o1_mf_hemant_dvkvg01y_.dbf
[oracle@ora11204 datafile]$ rm *hemant*
[oracle@ora11204 datafile]$ ls -l *hemant*
ls: cannot access *hemant*: No such file or directory
[oracle@ora11204 datafile]$
[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 16:40:02 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>

The SHUTDOWN doesn’t report an error for all three files, it only reports the error for the first datafile to “fail”.

Let me try to startup and open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'


SQL>

Here, too, it only reports the error for the first datafile to fail to open. I must query V$RECOVER_FILE.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
5 ONLINE ONLINE
FILE NOT FOUND 0


6 ONLINE ONLINE
FILE NOT FOUND 0


11 ONLINE ONLINE
FILE NOT FOUND 0



SQL>

So, now I switch to RMAN.

RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 230.29M DISK 00:00:42 07-SEP-17
BP Key: 68 Status: AVAILABLE Compressed: YES Tag: TAG20170907T230339
Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 7608466 07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN>
RMAN> restore datafile 11;

Starting restore at 13-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf
channel ORA_DISK_1: reading from backup piece /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
channel ORA_DISK_1: piece handle=/u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp tag=TAG20170907T230339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13-SEP-17

RMAN> restore datafile 5;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> restore datafile 6;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=6 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN>

Ignore the “restore not done; all files …” error message. It’s misleading. But note how the RESTORE command actually did a “CREATING DATAFILE” operation. Also note that these are OMF Files.

RMAN> recover datafile 11;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 180 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc
archived log for thread 1 with sequence 181 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc
archived log for thread 1 with sequence 182 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc
archived log for thread 1 with sequence 183 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc
archived log for thread 1 with sequence 184 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc
archived log for thread 1 with sequence 185 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc
archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc thread=1 sequence=180
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc thread=1 sequence=181
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc thread=1 sequence=182
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc thread=1 sequence=183
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc thread=1 sequence=184
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc thread=1 sequence=185
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN>
RMAN> recover datafile 5;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN>
RMAN> recover datafile 6;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 13-SEP-17

RMAN>

For datafile 5, I had all the ArchiveLogs (and Online Redo Logs) since the datafile was created (on 07-Sep).
For datafile 6, since it had only been recently created, the only redo to be applied was from the Online Redo Log (not yet archived). See the alert log message :

alter database recover if needed
datafile 6
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 199 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORCL/clone_o1_mf_1_91zfcp2o_.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 6

I should now be able to bring the datafiles online.

RMAN> sql 'alter database datafile 11 online';

sql statement: alter database datafile 11 online

RMAN> sql 'alter database datafile 5 online ';

sql statement: alter database datafile 5 online

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>
RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>

I can query V$RECOVER_FILE now :

SQL> select * from v$recover_file;

no rows selected

SQL>

So, even though I did not have backups of datafiles 5 and 6, as long as I had all the redo (ArchiveLogs and Online Redo Logs) for actions against these datafiles, I could restore and recover them.

SQL> select v.file#, v.creation_time,  v.name
2 from v$datafile v, v$tablespace t
3 where v.ts#=t.ts#
4 and t.name = 'HEMANT'
5 order by 2;

FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvyx0y_.dbf

5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvz8tz_.dbf

6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvzhnq_.dbf


SQL>

As these are OMF Files, the actual file name created can well be different from what it was earlier.

Question : What if I had to do a Database Point In Time Recovery ? Would this method still work ? Test it yourself and come back with your comments.

.
.
.