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.
What happens if I lose all three datafiles ?
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.
Here, too, it only reports the error for the first datafile to fail to open. I must query V$RECOVER_FILE.
So, now I switch to 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.
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 :
I should now be able to bring the datafiles online.
I can query V$RECOVER_FILE now :
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.
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.
.
.
.
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.
.
.
.
2 comments:
excellent Hemant, please keep posting.
PITR would definitely still work but datafile 5 and 6 may or may not be there. Depends on what SCN/timestamp you are recovering to vs what SCN is associated with creation of each file.
Post a Comment