Oracle
Backup and Restore Case Scenarios - Case Study 6
Case
6: Magic Restore: Recover a lost datafile without backup
If a datafile has been dropped mistakenly before the backup,
it can be brought back. RMAN restores it from online log and archive log. This is done through RMAN new feature
RMAN Automatic Datafile Creation.
SQL>
select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status from dba_data_files order by file_id;
FILE_ID
FILE_NAME TABLESPACE_NAME AUT STATUS
--------
------------------------------------------------- ------------------ --- ----
1 /u01/app/oracle/oradata/ORADB1/system01.dbf SYSTEM YES AVAILABLE
1 /u01/app/oracle/oradata/ORADB1/system01.dbf SYSTEM YES AVAILABLE
2
/u01/app/oracle/oradata/ORADB1/sysaux01.dbf SYSAUX YES
AVAILABLE
3
/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
UNDOTBS1 YES
AVAILABLE
4
/u01/app/oracle/oradata/ORADB1/users01.dbf USERS YES
AVAILABLE
5
/u01/app/oracle/oradata/ORADB1/example01.dbf
EXAMPLE YES
AVAILABLE
CREATE NEW
TABLESPACE
SQL>
CREATE TABLESPACE tbs_restore DATAFILE
'/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf' SIZE 20M ONLINE;
Tablespace
created.
TAKE RMAN FULL BACKUP
OF DATABASE
[oracle@localhost
ORADB1]$ rman target /
Recovery
Manager: Release 11.2.0.4.0 - Production on Mon Sep 25 14:56:29 2017
Copyright
(c) 1982, 2011, Oracle and/or its affiliates.
All rights reserved.
connected
to target database: ORADB1 (DBID=2294695355)
RMAN>
backup database plus archivelog;
Starting
backup at 25-SEP-2017 14:56:40
current
log archived
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=35 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=48 device type=DISK
channel
ORA_DISK_1: starting compressed archived log backup set
channel
ORA_DISK_1: specifying archived log(s) in backup set
input
archived log thread=1 sequence=13 RECID=1 STAMP=955328309
input
archived log thread=1 sequence=14 RECID=2 STAMP=955328309
input
archived log thread=1 sequence=15 RECID=3 STAMP=955328320
input
archived log thread=1 sequence=16 RECID=4 STAMP=955383829
input
archived log thread=1 sequence=17 RECID=5 STAMP=955383888
input
archived log thread=1 sequence=18 RECID=13 STAMP=955400510
input
archived log thread=1 sequence=19 RECID=11 STAMP=955400510
input
archived log thread=1 sequence=20 RECID=12 STAMP=955400510
channel
ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:41
channel
ORA_DISK_2: starting compressed archived log backup set
channel
ORA_DISK_2: specifying archived log(s) in backup set
input
archived log thread=1 sequence=1 RECID=14 STAMP=955466468
input
archived log thread=1 sequence=2 RECID=15 STAMP=955541254
input
archived log thread=1 sequence=3 RECID=16 STAMP=955552300
channel
ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:41
channel
ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:48
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykl7_.bkp
tag=TAG20170925T145641 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel
ORA_DISK_1: starting compressed archived log backup set
channel
ORA_DISK_1: specifying archived log(s) in backup set
input
archived log thread=1 sequence=4 RECID=17 STAMP=955637365
input
archived log thread=1 sequence=5 RECID=18 STAMP=955637511
input
archived log thread=1 sequence=6 RECID=19 STAMP=955637569
input
archived log thread=1 sequence=7 RECID=20 STAMP=955637800
channel
ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:48
channel
ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:56:48
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklykmf_.bkp
tag=TAG20170925T145641 comment=NONE
channel
ORA_DISK_2: backup set complete, elapsed time: 00:00:07
channel
ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:56:49
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145641_dwklyrq7_.bkp
tag=TAG20170925T145641 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished
backup at 25-SEP-2017 14:56:49
Starting
backup at 25-SEP-2017 14:56:49
using
channel ORA_DISK_1
using
channel ORA_DISK_2
channel
ORA_DISK_1: starting compressed full datafile backup set
channel
ORA_DISK_1: specifying datafile(s) in backup set
input
datafile file number=00001 name=/u01/app/oracle/oradata/ORADB1/system01.dbf
input
datafile file number=00004 name=/u01/app/oracle/oradata/ORADB1/users01.dbf
input
datafile file number=00005 name=/u01/app/oracle/oradata/ORADB1/example01.dbf
channel
ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:56:50
channel
ORA_DISK_2: starting compressed full datafile backup set
channel
ORA_DISK_2: specifying datafile(s) in backup set
input
datafile file number=00002 name=/u01/app/oracle/oradata/ORADB1/sysaux01.dbf
input
datafile file number=00003 name=/u01/app/oracle/oradata/ORADB1/undotbs01.dbf
input
datafile file number=00006
name=/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel
ORA_DISK_2: starting piece 1 at 25-SEP-2017 14:56:50
channel
ORA_DISK_2: finished piece 1 at 25-SEP-2017 14:57:45
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp
tag=TAG20170925T145649 comment=NONE
channel
ORA_DISK_2: backup set complete, elapsed time: 00:00:55
channel
ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:05
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklyt3t_.bkp
tag=TAG20170925T145649 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished
backup at 25-SEP-2017 14:58:05
Starting
backup at 25-SEP-2017 14:58:05
current
log archived
using
channel ORA_DISK_1
using
channel ORA_DISK_2
channel
ORA_DISK_1: starting compressed archived log backup set
channel
ORA_DISK_1: specifying archived log(s) in backup set
input
archived log thread=1 sequence=8 RECID=21 STAMP=955637885
channel
ORA_DISK_1: starting piece 1 at 25-SEP-2017 14:58:05
channel
ORA_DISK_1: finished piece 1 at 25-SEP-2017 14:58:06
piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_annnn_TAG20170925T145805_dwkm15dy_.bkp
tag=TAG20170925T145805 comment=NONE
channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished
backup at 25-SEP-2017 14:58:06
Starting
Control File and SPFILE Autobackup at 25-SEP-2017 14:58:06
piece
handle=/u01/backup/rman/ctl_c-2294695355-20170925-01 comment=NONE
Finished
Control File and SPFILE Autobackup at 25-SEP-2017 14:58:07
RMAN>
Add Datafile to new
tablespace tbs_restore
SQL>
ALTER TABLESPACE tbs_restore ADD DATAFILE
'/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf' SIZE 20M AUTOEXTEND ON;
Tablespace
altered.
Simulate Failure by
removing datafiles for tbs_resore tablespace from disk
[oracle@localhost
ORADB1]$ ls -ltr tbs_restore*
-rw-r-----.
1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_01.dbf
-rw-r-----.
1 oracle oinstall 20979712 Sep 25 15:02 tbs_restore_02.dbf
[oracle@localhost
ORADB1]$ rm tbs_restore*
[oracle@localhost
ORADB1]$ ls -ltr tbs_restore*
ls:
cannot access tbs_restore*: No such file or directory
Bring the tablespace
offline.
SQL>
alter tablespace tbs_restore offline ;
alter
tablespace tbs_restore offline
*
ERROR
at line 1:
ORA-01116:
error in opening database file 6
ORA-01110:
data file 6: '/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf'
ORA-27041:
unable to open file
Linux-x86_64
Error: 2: No such file or directory
Additional
information: 3
Tablespace wont go
offline as datafiles have been dropped. Make it offline using IMMEDIATE clause.
SQL>
alter tablespace tbs_restore offline immediate;
Tablespace
altered.
Restore datafile 6
(tbs_restore_01.dbf) which was backed up.
RMAN>
restore datafile 6;
Starting
restore at 25-SEP-2017 15:05:30
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: SID=34 device type=DISK
allocated
channel: ORA_DISK_2
channel
ORA_DISK_2: SID=46 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 00006 to
/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
channel
ORA_DISK_1: reading from backup piece
/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp
channel
ORA_DISK_1: piece
handle=/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T145649_dwklywhn_.bkp
tag=TAG20170925T145649
channel
ORA_DISK_1: restored backup piece 1
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished
restore at 25-SEP-2017 15:05:32
Restore the tablespace
tbs_restore.
You can see that
datafile 7 which was not backed up has been restored.
First is preview
session to see when RMAN is creating the datafile not backed up.
(Preview output is
captured from different session, so timings are different.)
This RMAN feature
was introduced in 10g and enhanced in 12c
RMAN>
restore tablespace tbs_restore preview;
Starting
restore at 25-SEP-2017 15:56:07
using
channel ORA_DISK_1
using
channel ORA_DISK_2
datafile 7 will be created automatically during restore operation
List
of Backup Sets
===================
BS
Key Type LV Size Device Type Elapsed Time Completion
Time
-------
---- -- ---------- ----------- ------------ --------------------
28 Full
81.22M DISK 00:00:29 25-SEP-2017 15:45:39
BP Key: 28 Status: AVAILABLE Compressed: YES Tag: TAG20170925T154538
Piece Name:
/u01/app/oracle/fast_recovery_area/ORADB1/backupset/2017_09_25/o1_mf_nnndf_TAG20170925T154538_dwkotbyx_.bkp
List of Datafiles in backup set 28
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------------------
----
6
Full 1298356 25-SEP-2017
15:45:38 /u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
List
of Archived Log Copies for database with db_unique_name ORADB1
=====================================================================
Key Thrd Seq S Low Time
-------
---- ------- - --------------------
23 1
10 A 25-SEP-2017 15:45:29
Name:
/u01/app/oracle/oradata/ORADB1/archive/1_10_955400510.dbf
Media
recovery start SCN is 1298356
Recovery
must be done beyond SCN 1298686 to clear datafile fuzziness
Finished
restore at 25-SEP-2017 15:56:07
RMAN>
RMAN>
restore tablespace tbs_restore;
Starting
restore at 25-SEP-2017 15:06:04
using
channel ORA_DISK_1
using
channel ORA_DISK_2
creating
datafile file number=7 name=/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf
skipping
datafile 6; already restored to file
/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf
restore
not done; all files read only, offline, or already restored
Finished
restore at 25-SEP-2017 15:06:04
Recover tablespace
tbs_store.
RMAN>
RECOVER TABLESPACE tbs_restore;
Starting
recover at 25-SEP-2017 15:06:30
using
channel ORA_DISK_1
using
channel ORA_DISK_2
starting
media recovery
media
recovery complete, elapsed time: 00:00:00
Finished
recover at 25-SEP-2017 15:06:31
RMAN>
Make the tablespace
online.
SQL>
alter tablespace tbs_restore online;
Tablespace
altered.
Check and verify
whether both datafiles are restored back.
SQL>
select file_id, file_name ,tablespace_name,AUTOEXTENSIBLE,status from dba_data_files order by file_id;
FILE_ID
FILE_NAME TABLESPACE_NAME AUT STATUS
--------
------------------------------------------------- ------------------ --- ----
1 /u01/app/oracle/oradata/ORADB1/system01.dbf SYSTEM YES AVAILABLE
1 /u01/app/oracle/oradata/ORADB1/system01.dbf SYSTEM YES AVAILABLE
2
/u01/app/oracle/oradata/ORADB1/sysaux01.dbf SYSAUX YES
AVAILABLE
3
/u01/app/oracle/oradata/ORADB1/undotbs01.dbf UNDOTBS1 YES
AVAILABLE
4
/u01/app/oracle/oradata/ORADB1/users01.dbf USERS YES
AVAILABLE
5
/u01/app/oracle/oradata/ORADB1/example01.dbf EXAMPLE YES
AVAILABLE
6
/u01/app/oracle/oradata/ORADB1/tbs_restore_01.dbf TBS_RESTORE NO AVAILABLE
7
/u01/app/oracle/oradata/ORADB1/tbs_restore_02.dbf TBS_RESTORE YES
AVAILABLE
No comments:
Post a Comment
Leave a Reply...