Restore Datafile Without Backup - Archivelog Mode ON

Datafile restoration without backup is only possible when archivelog is enabled during the time of datafile dropped. By using archivelog and redo logs we can restore datafile.

SQL> select log_mode from v$database;

                        LOG_MODE

                                ------------

                                ARCHIVELOG

• I have removed the emp.dbf datafile accidently

Oracle@test TEST$  rm emp.dbf

• When I try to insert a new data in employee table present in emp datafile it shows below error

SQL> insert into emp select * from emp;
insert into emp select * from emp
                           
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 6: '/d02/app/oracle/oradata/testdb/emp.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

• To recover the datafile we need to take it offline :

SQL> alter database datafile 6 offline;

• Create a new datafile with same old dropped datafile name/Path

SQL> alter database create datafile '/d02/app/oracle/oradata/testdb/emp.dbf ';

• Now recover the datafile

SQL> recover datafile 8;

• Bring the datafile online :

SQL> alter database datafile 6 online;

• Now insert the values and check

SQL> insert into emp select * from emp;

100 rows created.

To Find Used and Free Space in Datafiles :

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

To reduce/resize the tablespace :

We can resize the datafile like below

ALTER DATABASE DATAFILE '/a01/sysaux01.dbf' RESIZE 1024M;

of course if you already used some space above 1024M then you will get an error

ORA-03297: file contains used data beyond requested RESIZE value

We can use the below querry to find the minimum/least value to reduce a datafile.

SQL >
SELECT  CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.file_name = '/a01/sysaux01.dbf';

   /

                                               OR

SQL >
SELECT  file_name, tablespace_name , CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as "Mb"
FROM dba_data_files f
LEFT
JOIN    (SELECT file_id, max(block_id + blocks - 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE
    f.tablespace_name = 'SYSAUX';

   /