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/PROD18C/dbwrtbs01.dbf' size 100M; Tablespace created. SQL> select file_name from dba_data_files where tablespace_name='DBWRTBS'; FILE_NAME ----------------------------------------------------------- /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf
Step 2:-Take RMAN whole database backup and archivelog
RMAN> backup database plus archivelog; Starting backup at 15-AUG-18 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=4 RECID=1 STAMP=984173730 input archived log thread=1 sequence=5 RECID=2 STAMP=984173864 input archived log thread=1 sequence=6 RECID=3 STAMP=984173913 input archived log thread=1 sequence=7 RECID=4 STAMP=984174371 input archived log thread=1 sequence=8 RECID=5 STAMP=984174523 input archived log thread=1 sequence=9 RECID=6 STAMP=984174572 input archived log thread=1 sequence=10 RECID=7 STAMP=984174759 input archived log thread=1 sequence=11 RECID=8 STAMP=984174809 input archived log thread=1 sequence=12 RECID=9 STAMP=984175128 input archived log thread=1 sequence=13 RECID=10 STAMP=984175163 input archived log thread=1 sequence=14 RECID=11 STAMP=984180797 input archived log thread=1 sequence=15 RECID=12 STAMP=984185142 channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T004542_fq6blyqq_.bkp tag=TAG20180815T004542 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:18 Finished backup at 15-AUG-18 Starting backup at 15-AUG-18 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/PROD18C/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PROD18C/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PROD18C/undotbs01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/PROD18C/users01.dbf channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp tag=TAG20180815T004600 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 Finished backup at 15-AUG-18 Starting backup at 15-AUG-18 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=16 RECID=13 STAMP=984185216 channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T004657_fq6bo96w_.bkp tag=TAG20180815T004657 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-AUG-18 Starting Control File and SPFILE Autobackup at 15-AUG-18 piece handle=/u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984185218_fq6bobtt_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-18
Step 3:- Add a New Datafile to that Tablespace and verify that the new file is now a member of that tablespace.
alter tablespace dbwrtbs add datafile '/u01/app/oracle/oradata/PROD18C/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/PROD18C/dbwrtbs01.dbf /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf SQL> alter system switch logfile; System altered. SQL> / 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: 20 old 1: insert into t1 values (&a) new 1: insert into t1 values (20) 1 row created. SQL> / Enter value for a: 30 old 1: insert into t1 values (&a) new 1: insert into t1 values (30) 1 row created. SQL> select * from t1; COL1 ---------- 10 20 30
Step 5:- In the OS Level,remove all files of that “DBWRTBS” tablespace including the newly added one (whose backup does not exist).
[oracle@18c ~]$ cd /u01/app/oracle/oradata/PROD18C/ [oracle@18c PROD18C]$ ls -lrt total 2607036 -rw-r----- 1 oracle oinstall 33562624 Aug 15 01:06 temp01.dbf -rw-r----- 1 oracle oinstall 209715712 Aug 15 01:14 redo02.log -rw-r----- 1 oracle oinstall 209715712 Aug 15 01:15 redo03.log -rw-r----- 1 oracle oinstall 891297792 Aug 15 01:16 system01.dbf -rw-r----- 1 oracle oinstall 104865792 Aug 15 01:16 dbwrtbs02.dbf -rw-r----- 1 oracle oinstall 319823872 Aug 15 01:16 undotbs01.dbf -rw-r----- 1 oracle oinstall 104865792 Aug 15 01:16 dbwrtbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Aug 15 01:16 users01.dbf -rw-r----- 1 oracle oinstall 587210752 Aug 15 01:16 sysaux01.dbf -rw-r----- 1 oracle oinstall 209715712 Aug 15 01:18 redo01.log -rw-r----- 1 oracle oinstall 10600448 Aug 15 01:18 control01.ctl -rw-r----- 1 oracle oinstall 10600448 Aug 15 01:18 control02.ctl [oracle@18c PROD18C]$ 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/PROD18C/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/PROD18C/dbwrtbs01.dbf
2 /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
Step 8:- Check RMAN backup of DBWRTBS tablespace
[oracle@18c PROD18C]$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Wed Aug 15 01:27:29 2018 Version 18.3.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved. connected to target database: PROD18C (DBID=3984767297) 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 ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 1.13G DISK 00:00:52 15-AUG-18 BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20180815T004600 Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp List of Datafiles in backup set 18 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 5 Full 1573623 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf RMAN> list backup of datafile 2; specification does not match any backup in the repository
Now 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 15-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
creating datafile file number=2 name=/u01/app/oracle/oradata/PROD18C/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/PROD18C/dbwrtbs01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T004600_fq6bmk7b_.bkp tag=TAG20180815T004600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-AUG-18
Step 10:- Recover tablespace
RMAN> recover tablespace DBWRTBS; Starting recover at 15-AUG-18 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 16 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_16_fq6bo8o1_.arc archived log for thread 1 with sequence 17 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_17_fq6d9rnf_.arc archived log for thread 1 with sequence 18 is already on disk as file /u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_18_fq6d9wkm_.arc archived log file name=/u01/fra/PROD18C/archivelog/2018_08_15/o1_mf_1_16_fq6bo8o1_.arc thread=1 sequence=16 media recovery complete, elapsed time: 00:00:01 Finished recover at 15-AUG-18
Step 11:- Bring the Tablespace online
SQL> alter tablespace DBWRTBS online;
Tablespace altered.
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
20
30
Step 12:- After the tablespace recovery,take a FULL DATABASE BACKUP
RMAN> backup database plus archivelog; Starting backup at 15-AUG-18 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=4 RECID=1 STAMP=984173730 input archived log thread=1 sequence=5 RECID=2 STAMP=984173864 input archived log thread=1 sequence=6 RECID=3 STAMP=984173913 input archived log thread=1 sequence=18 RECID=15 STAMP=984186900 input archived log thread=1 sequence=19 RECID=16 STAMP=984189157 input archived log thread=1 sequence=20 RECID=17 STAMP=984189333 channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T015533_fq6goxds_.bkp tag=TAG20180815T015533 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 15-AUG-18 Starting backup at 15-AUG-18 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/PROD18C/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/PROD18C/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/PROD18C/undotbs01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/PROD18C/users01.dbf channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp tag=TAG20180815T015536 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35 Finished backup at 15-AUG-18 Starting backup at 15-AUG-18 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=21 RECID=18 STAMP=984189371 channel ORA_DISK_1: starting piece 1 at 15-AUG-18 channel ORA_DISK_1: finished piece 1 at 15-AUG-18 piece handle=/u01/fra/PROD18C/backupset/2018_08_15/o1_mf_annnn_TAG20180815T015611_fq6gq42z_.bkp tag=TAG20180815T015611 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 15-AUG-18 Starting Control File and SPFILE Autobackup at 15-AUG-18 piece handle=/u01/fra/PROD18C/autobackup/2018_08_15/o1_mf_s_984189373_fq6gq5hk_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 15-AUG-18
Step 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 ------- ---- -- ---------- ----------- ------------ --------------- 25 Full 1.13G DISK 00:00:25 15-AUG-18 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20180815T015536 Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp List of Datafiles in backup set 25 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 5 Full 1584601 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs01.dbf RMAN> list backup of datafile 2; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 25 Full 1.13G DISK 00:00:25 15-AUG-18 BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20180815T015536 Piece Name: /u01/fra/PROD18C/backupset/2018_08_15/o1_mf_nnndf_TAG20180815T015536_fq6gp0o1_.bkp List of Datafiles in backup set 25 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 2 Full 1584601 15-AUG-18 NO /u01/app/oracle/oradata/PROD18C/dbwrtbs02.dbf
Catch 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
