It is possible to recover datafile when accidentally removed or deleted and when there is no backup at all , We can recover the datafile without any issues ,now i am stimulating a error , removing datafile physically and showing how to recover it, Note : Most important is we should have full backup and archive log entries to recover the datafile when no recent backups available, Steps: Creating a tablespace named TBS and the datafile as TBS1.dbf , SQL> create tablespace tbs datafile 'tbs1.dbf' size 100m; Tablespace created. SQL> select file_name,tablespace_name from dba_data_files; FILE_NAME TABLESPACE_NAME ---------------------------------------- -------------------- +DATA/TRICHYCLOUD/DATAFILE/system.257.10 SYSTEM 71146273 +DATA/TRICHYCLOUD/DATAFILE/sysaux.258.10 SYSAUX 71146327 /u01/app/oracle/product/12.2.0.1/dbhome_ TBS 1/dbs/tbs1.dbf +DATA/TRICHYCLOUD/DATAFILE/users.260.107 USERS 1146353 +DATA/TRICHYCLOUD/DATAFILE/undotbs1.259. UNDOTBS1 1071146353 For demo purpose i am taking full backup of database plus archive log, RMAN> backup database plus archivelog; Starting backup at 30-MAY-21 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=143 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=30 RECID=30 STAMP=1073775857 input archived log thread=1 sequence=31 RECID=31 STAMP=1073777682 input archived log thread=1 sequence=32 RECID=32 STAMP=1073808991 input archived log thread=1 sequence=33 RECID=33 STAMP=1073832418 input archived log thread=1 sequence=34 RECID=34 STAMP=1073856445 input archived log thread=1 sequence=35 RECID=35 STAMP=1073868466 input archived log thread=1 sequence=36 RECID=36 STAMP=1073904502 input archived log thread=1 sequence=37 RECID=37 STAMP=1073929729 input archived log thread=1 sequence=38 RECID=38 STAMP=1073941890 input archived log thread=1 sequence=39 RECID=39 STAMP=1073942702 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/annnf0_tag20210530t212503_0.271.1073942705 tag=TAG20210530T212503 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 30-MAY-21 Starting backup at 30-MAY-21 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=00003 name=+DATA/TRICHYCLOUD/DATAFILE/sysaux.258.1071146327 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.301.1073942719 tag=TAG20210530T212519 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 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=+DATA/TRICHYCLOUD/DATAFILE/system.257.1071146273 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.300.1073942727 tag=TAG20210530T212519 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf input datafile file number=00004 name=+DATA/TRICHYCLOUD/DATAFILE/undotbs1.259.1071146353 input datafile file number=00007 name=+DATA/TRICHYCLOUD/DATAFILE/users.260.1071146353 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.299.1073942733 tag=TAG20210530T212519 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-MAY-21 Starting backup at 30-MAY-21 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=40 RECID=40 STAMP=1073942735 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/annnf0_tag20210530t212535_0.297.1073942735 tag=TAG20210530T212535 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-MAY-21 Starting Control File and SPFILE Autobackup at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/AUTOBACKUP/2021_05_30/s_1073942736.296.1073942737 comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-21 Now i am adding a new datafile TBS2.dbf and checking it is online or not, SQL> alter tablespace tbs add datafile 'tbs2.dbf' size 100m; Tablespace altered. SQL> select file_name from dba_data_files where tablespace_name like 'TBS'; FILE_NAME ------------------------------------------------------------------------------- /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs2.dbf Switching current log files to next log files using the below command, SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. Creating a table in the tablespace TBS , SQL> create table emp(id number(10)) tablespace TBS; Table created. SQL> insert into emp values(1); 1 row created. SQL> insert into emp values (&a); Enter value for a: 2 old 1: insert into emp values (&a) new 1: insert into emp values (2) 1 row created. SQL> / Enter value for a: 3 old 1: insert into emp values (&a) new 1: insert into emp values (3) 1 row created. SQL> / Enter value for a: 4 old 1: insert into emp values (&a) new 1: insert into emp values (4) 1 row created. SQL> / Enter value for a: 5 old 1: insert into emp values (&a) new 1: insert into emp values (5) 1 row created. Removing the datafile physically , [oracle@trichy ~]$ cd /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs [oracle@trichy dbs]$ ls -lrt total 215228 -rw-r--r--. 1 oracle oinstall 3079 May 15 2015 init.ora -rw-r-----. 1 oracle asmadmin 24 Apr 29 12:37 lkTRICHYCLOUD -rw-r-----. 1 oracle asmadmin 24 Apr 29 12:39 lkTRICHYCL -rw-r-----. 1 oracle oinstall 3584 Apr 29 12:40 orapwtrichycloud -rw-r-----. 1 oracle oinstall 6144 May 26 19:15 orapwtestdb.ora -rw-rw----. 1 oracle asmadmin 1544 May 28 23:57 hc_trichycloud.dat -rw-r-----. 1 oracle asmadmin 104865792 May 30 21:34 tbs2.dbf -rw-r-----. 1 oracle asmadmin 10633216 May 30 21:39 snapcf_trichycloud.f -rw-r-----. 1 oracle asmadmin 104865792 May 30 21:45 tbs1.dbf [oracle@trichy dbs]$ rm tbs1.dbf tbs2.dbf [oracle@trichy dbs]$ ls hc_trichycloud.dat lkTRICHYCL orapwtestdb.ora snapcf_trichycloud.f init.ora lkTRICHYCLOUD orapwtrichycloud Trying to bring the tablespace after removing datafiles physically but it throws ora error’s: for avoiding this error we can give tablespace immediate option, SQL> alter tablespace TBS offline; alter tablespace TBS offline * ERROR at line 1: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> alter tablespace TBS offline immediate; Tablespace altered. Verifying the status of file_id,tablespace of the datafiles, SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS OFFLINE 6 rows selected. SQL> select file_id,file_name from dba_data_files where tablespace_name like 'TBS'; FILE_ID ---------- FILE_NAME -------------------------------------------------------------------------------- 5 /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf 2 /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs2.dbf 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 ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 6.80M DISK 00:00:01 30-MAY-21 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20210530T212519 Piece Name: +DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.299.1073942733 List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 5 Full 3037967 30-MAY-21 NO /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf For datafile 2 is not available in the rman backup, RMAN> list backup of datafile 2; specification does not match any backup in the repository Now we can connect to RMAN after confirming that no backup exists for the newly added datafile FILE_ID 2. Then we can restore the TBS tablespace, RMAN automatically create our newly added datafile as a part of the restore process RMAN> restore tablespace TBS; Starting restore at 30-MAY-21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=133 device type=DISK creating datafile file number=2 name=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs2.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/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf channel ORA_DISK_1: reading from backup piece +DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.299.1073942733 channel ORA_DISK_1: piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.299.1073942733 tag=TAG20210530T212519 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 Finished restore at 30-MAY-21 RMAN> recover tablespace TBS; Starting recover at 30-MAY-21 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 40 is already on disk as file +DATA/TRICHYCLOUD/ARCHIVELOG/2021_05_30/thread_1_seq_40.298.1073942735 archived log for thread 1 with sequence 41 is already on disk as file +DATA/TRICHYCLOUD/ARCHIVELOG/2021_05_30/thread_1_seq_41.291.1073943065 archived log for thread 1 with sequence 42 is already on disk as file +DATA/TRICHYCLOUD/ARCHIVELOG/2021_05_30/thread_1_seq_42.290.1073943071 archived log file name=+DATA/TRICHYCLOUD/ARCHIVELOG/2021_05_30/thread_1_seq_40.298.1073942735 thread=1 sequence=40 media recovery complete, elapsed time: 00:00:01 Finished recover at 30-MAY-21 Tablespace restoration has completed . Now we can 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 TBS OFFLINE 6 rows selected. SQL> alter tablespace TBS online; Tablespace altered. SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE TBS ONLINE 6 rows selected. SQL> select * from emp; ID ---------- 1 2 3 4 5 We can take full database backup plus archive log with newly added datafile, RMAN> backup database plus archivelog; Starting backup at 30-MAY-21 current log archived using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=386 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=30 RECID=30 STAMP=1073775857 input archived log thread=1 sequence=31 RECID=31 STAMP=1073777682 input archived log thread=1 sequence=32 RECID=32 STAMP=1073808991 input archived log thread=1 sequence=33 RECID=33 STAMP=1073832418 input archived log thread=1 sequence=34 RECID=34 STAMP=1073856445 input archived log thread=1 sequence=35 RECID=35 STAMP=1073868466 input archived log thread=1 sequence=36 RECID=36 STAMP=1073904502 input archived log thread=1 sequence=37 RECID=37 STAMP=1073929729 input archived log thread=1 sequence=38 RECID=38 STAMP=1073941890 input archived log thread=1 sequence=39 RECID=39 STAMP=1073942702 input archived log thread=1 sequence=40 RECID=40 STAMP=1073942735 input archived log thread=1 sequence=41 RECID=41 STAMP=1073943064 input archived log thread=1 sequence=42 RECID=42 STAMP=1073943070 input archived log thread=1 sequence=43 RECID=43 STAMP=1073946046 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/annnf0_tag20210530t222047_0.286.1073946049 tag=TAG20210530T222047 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 Finished backup at 30-MAY-21 Starting backup at 30-MAY-21 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=00003 name=+DATA/TRICHYCLOUD/DATAFILE/sysaux.258.1071146327 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t222103_0.285.1073946063 tag=TAG20210530T222103 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15 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=+DATA/TRICHYCLOUD/DATAFILE/system.257.1071146273 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t222103_0.275.1073946079 tag=TAG20210530T222103 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00002 name=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs2.dbf input datafile file number=00005 name=/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf input datafile file number=00004 name=+DATA/TRICHYCLOUD/DATAFILE/undotbs1.259.1071146353 input datafile file number=00007 name=+DATA/TRICHYCLOUD/DATAFILE/users.260.1071146353 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t222103_0.271.1073946085 tag=TAG20210530T222103 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-MAY-21 Starting backup at 30-MAY-21 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=44 RECID=44 STAMP=1073946087 channel ORA_DISK_1: starting piece 1 at 30-MAY-21 channel ORA_DISK_1: finished piece 1 at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/annnf0_tag20210530t222127_0.306.1073946087 tag=TAG20210530T222127 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 30-MAY-21 Starting Control File and SPFILE Autobackup at 30-MAY-21 piece handle=+DATA/TRICHYCLOUD/AUTOBACKUP/2021_05_30/s_1073946088.305.1073946089 comment=NONE Finished Control File and SPFILE Autobackup at 30-MAY-21 RMAN> list backup of datafile 5; List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 6.80M DISK 00:00:01 30-MAY-21 BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20210530T212519 Piece Name: +DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t212519_0.299.1073942733 List of Datafiles in backup set 10 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 5 Full 3037967 30-MAY-21 NO /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 18 Full 24.27M DISK 00:00:01 30-MAY-21 BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20210530T222103 Piece Name: +DATA/TRICHYCLOUD/BACKUPSET/2021_05_30/nnndf0_tag20210530t222103_0.271.1073946085 List of Datafiles in backup set 18 File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name ---- -- ---- ---------- --------- ----------- ------ ---- 5 Full 3045601 30-MAY-21 NO /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/tbs1.dbf
Recent Posts
