Description:-
In this article we are going to see datafile recovery when no backup exists
We must have RMAN full backup and archivelogs to recover the datafile when no recent backup of datafile exists
Let’s start the Demo:-
Steps to recover datafile when no backup exists
Step 1:- Tablespace creation
SQL> create tablespace dbwrtbs datafile '/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf' size 100M;
Tablespace created.
SQL> select file_name from dba_data_files where tablespace_name='DBWRTBS';
FILE_NAME
--------------------------------------------------
/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbfStep 2:-Take RMAN whole database backup and archivelog
RMAN> backup database plus archivelog;
Starting backup at 16-MAY-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=92 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1008270177
input archived log thread=1 sequence=8 RECID=2 STAMP=1008295179
input archived log thread=1 sequence=9 RECID=3 STAMP=1008377749
input archived log thread=1 sequence=10 RECID=4 STAMP=1008377864
input archived log thread=1 sequence=11 RECID=5 STAMP=1008377981
input archived log thread=1 sequence=12 RECID=6 STAMP=1008380832
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0cu1n5nk_1_1 tag=TAG20190516T183115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=8 STAMP=1008383068
input archived log thread=1 sequence=2 RECID=9 STAMP=1008383167
input archived log thread=1 sequence=3 RECID=10 STAMP=1008383723
input archived log thread=1 sequence=4 RECID=11 STAMP=1008385155
input archived log thread=1 sequence=5 RECID=12 STAMP=1008385247
input archived log thread=1 sequence=6 RECID=13 STAMP=1008440100
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0du1n5nn_1_1 tag=TAG20190516T183115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=7 STAMP=1008382325
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0eu1n5nq_1_1 tag=TAG20190516T183115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=14 STAMP=1008441074
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0fu1n5nu_1_1 tag=TAG20190516T183115 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-MAY-19
Starting backup at 16-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0gu1n5nv_1_1 tag=TAG20190516T183127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 16-MAY-19
Starting backup at 16-MAY-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=15 STAMP=1008441144
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0hu1n5po_1_1 tag=TAG20190516T183224 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-MAY-19
Starting Control File and SPFILE Autobackup at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1337475478-20190516-03 comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-19Step 3:- Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace.
SQL> alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf' size 100m;
Tablespace altered.
SQL> select file_name from dba_data_files where tablespace_name like 'DBWRTBS';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.Step 4:- Create a ‘t1’ table in that tablespace and perform some DML operation. Also after DML operations switch some logfile.
SQL> create table t1(col1 number(10)) tablespace DBWRTBS;
Table created.
SQL> insert into t1 values (&a);
Enter value for a: 10
old 1: insert into t1 values (&a)
new 1: insert into t1 values (10)
1 row created.
SQL> /
Enter value for a: 11
old 1: insert into t1 values (&a)
new 1: insert into t1 values (11)
1 row created.
SQL> /
Enter value for a: 12
old 1: insert into t1 values (&a)
new 1: insert into t1 values (12)
1 row created.
SQL> /
Enter value for a: 13
old 1: insert into t1 values (&a)
new 1: insert into t1 values (13)
1 row created.
SQL> select * from t1;
COL1
----------
10
11
12
13Step 5:- In the OS Level,remove all files of that “DBWRTBS” tablespace including the newly added one (whose backup does not exist).
[oracle@ram ~]$ cd /u01/app/oracle/oradata/DBWR/datafile/
[oracle@ram datafile]$ ls -lrth
total 1.8G
-rw-r-----. 1 oracle oinstall 131M May 16 00:57 o1_mf_temp_g91q9l12_.tmp
-rw-r-----. 1 oracle oinstall 901M May 16 18:35 o1_mf_system_g91ppy2b_.dbf
-rw-r-----. 1 oracle oinstall 101M May 16 18:35 dbwrtbs02.dbf
-rw-r-----. 1 oracle oinstall 66M May 16 18:35 o1_mf_undotbs1_g91q0521_.dbf
-rw-r-----. 1 oracle oinstall 101M May 16 18:35 dbwrtbs01.dbf
-rw-r-----. 1 oracle oinstall 5.1M May 16 18:35 o1_mf_users_g91q08bm_.dbf
-rw-r-----. 1 oracle oinstall 551M May 16 18:36 o1_mf_sysaux_g91pxgmq_.dbf
[oracle@ram datafile]$ rm dbwr*
Step 6:- Try to bring the tablespace offline and we will get error message as follows
SQL> alter tablespace dbwrtbs offline;
alter tablespace dbwrtbs offline
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> alter tablespace dbwrtbs offline immediate;
Tablespace altered.Step 7:- Check the STATUS of tablespace and FILE_ID of datafiles
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DBWRTBS OFFLINE
6 rows selected.
SQL> select file_id,file_name from dba_data_files where tablespace_name like 'DBWRTBS';
FILE_ID FILE_NAME
-------------- ---------------------------------------------------
5 /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
2 /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbfStep 8:- Check RMAN backup of DBWRTBS tablespace
[oracle@ram datafile]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu May 16 18:46:08 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBWR (DBID=1337475478)
RMAN> list backup of datafile 5;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 1.17G DISK 00:00:54 16-MAY-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190516T183127
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0gu1n5nv_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2621828 16-MAY-19 NO /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
RMAN> list backup of datafile 2;
specification does not match any backup in the repositoryNow connect to RMAN and confirm that no backup exists for the newly added datafile FILE_ID 2. Then try to restore the DBWRTBS tablespace, RMAN will create that newly added datafile as a part of the restore process.
Step 9:- Tablespace restore
RMAN> restore tablespace DBWRTBS;
Starting restore at 16-MAY-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=99 device type=DISK
creating datafile file number=2 name=/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf
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 00005 to /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0gu1n5nv_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0gu1n5nv_1_1 tag=TAG20190516T183127
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 16-MAY-19Step 10:- Recover tablespace
RMAN> recover tablespace DBWRTBS;
Starting recover at 16-MAY-19
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 8 is already on disk as file /u01/ARC_BKP/1_8_1008382323.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/ARC_BKP/1_9_1008382323.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u01/ARC_BKP/1_10_1008382323.dbf
archived log for thread 1 with sequence 11 is already on disk as file /u01/ARC_BKP/1_11_1008382323.dbf
archived log file name=/u01/ARC_BKP/1_8_1008382323.dbf thread=1 sequence=8
archived log file name=/u01/ARC_BKP/1_9_1008382323.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 16-MAY-19Step 11:- Bring the Tablespace online
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
DBWRTBS ONLINE
6 rows selected.
SQL> select * from t1;
COL1
----------
10
11
12
13Step 12:- After the tablespace recovery,take a FULL DATABASE BACKUP
RMAN> backup database plus archivelog;
Starting backup at 16-MAY-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=1008270177
input archived log thread=1 sequence=8 RECID=2 STAMP=1008295179
input archived log thread=1 sequence=9 RECID=3 STAMP=1008377749
input archived log thread=1 sequence=10 RECID=4 STAMP=1008377864
input archived log thread=1 sequence=11 RECID=5 STAMP=1008377981
input archived log thread=1 sequence=12 RECID=6 STAMP=1008380832
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ku1n6s5_1_1 tag=TAG20190516T185045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=8 STAMP=1008383068
input archived log thread=1 sequence=2 RECID=9 STAMP=1008383167
input archived log thread=1 sequence=3 RECID=10 STAMP=1008383723
input archived log thread=1 sequence=4 RECID=11 STAMP=1008385155
input archived log thread=1 sequence=5 RECID=12 STAMP=1008385247
input archived log thread=1 sequence=6 RECID=13 STAMP=1008440100
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0lu1n6s9_1_1 tag=TAG20190516T185045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=7 STAMP=1008382325
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0mu1n6sc_1_1 tag=TAG20190516T185045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=14 STAMP=1008441074
input archived log thread=1 sequence=8 RECID=15 STAMP=1008441144
input archived log thread=1 sequence=9 RECID=16 STAMP=1008441306
input archived log thread=1 sequence=10 RECID=17 STAMP=1008441309
input archived log thread=1 sequence=11 RECID=18 STAMP=1008441314
input archived log thread=1 sequence=12 RECID=19 STAMP=1008442244
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0nu1n6sf_1_1 tag=TAG20190516T185045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-MAY-19
Starting backup at 16-MAY-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_system_g91ppy2b_.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_sysaux_g91pxgmq_.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_undotbs1_g91q0521_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DBWR/datafile/o1_mf_users_g91q08bm_.dbf
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1 tag=TAG20190516T185056 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:08
Finished backup at 16-MAY-19
Starting backup at 16-MAY-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=20 STAMP=1008442325
channel ORA_DISK_1: starting piece 1 at 16-MAY-19
channel ORA_DISK_1: finished piece 1 at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0pu1n6ul_1_1 tag=TAG20190516T185205 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-MAY-19
Starting Control File and SPFILE Autobackup at 16-MAY-19
piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1337475478-20190516-05 comment=NONE
Finished Control File and SPFILE Autobackup at 16-MAY-19Step 13:- Now we have an complete RMAN backups which we newly added.
RMAN> list backup of datafile 5;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 1.17G DISK 00:00:54 16-MAY-19
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20190516T183127
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0gu1n5nv_1_1
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2621828 16-MAY-19 NO /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 1.16G DISK 00:01:03 16-MAY-19
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20190516T185056
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 2624094 16-MAY-19 NO /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs01.dbf
RMAN> list backup of datafile 2;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
24 Full 1.16G DISK 00:01:03 16-MAY-19
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20190516T185056
Piece Name: /u01/app/oracle/product/19.0.0/dbhome_1/dbs/0ou1n6sh_1_1
List of Datafiles in backup set 24
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
2 Full 2624094 16-MAY-19 NO /u01/app/oracle/oradata/DBWR/datafile/dbwrtbs02.dbfCatch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba
