Oracle datafile recovery without RMAN

Scenario : In this scenario we will delete a datafile without taking a backup.

TABLESPACE : NON PERMANENT ( To find query select property_name,property_value from database_properties where property_name = ‘DEFAULT_PERMANENT_TABLESPACE’ )

Note: PERMANENT TABLESPACE (for e.g – USERS which is created by default when we create a database using DBCA) cannot be recovered without a backup. Deleting a permanent tablespace datafile cannot be recreated unless an older backup is restored and archives are applied.

a) Create a tablespace
b) Create users
c) create a table and insert values.

CREATE tablespace TEST datafile '/oraDevBin/oradata/test01.dbf' SIZE 100M;
CREATE USER dbasupp IDENTIFIED BY abc DEFAULT tablespace TEST;
GRANT CREATE SESSION TO dbasupp;
GRANT CONNECT,resource TO dbasupp;
CONNECT dbasupp

Enter password:

CREATE TABLE emp (2 eno NUMBER(10),3 ename varchar2(20),4 dept varchar2(10));
INSERT INTO emp 2 VALUES (1,'Nazim','IT');
commit;

DROP THE DATAFILE

$ rm -f test01.dbf

FROM SQL PROMPT TRY TO BRING THE DATAFILE IN OFFLINE STATE

ALTER DATABASE datafile 6 offline; <<< 6 IS the file_id FOR TEST datafile

ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

TRY WITH OFFLINE DROP OPTION

ALTER DATABASE datafile 6 offline DROP;

— CREATE THE DATAFILE

ALTER DATABASE CREATE datafile 6;

RECOVER DATAFILE

recover datafile 6;

BRING THE DATAFILE IN ONLINE STATE

ALTER DATABASE datafile 6 online;

GO TO THE FILE LOCATION ON THE SERVER AND CHECK IF THE DATAFILE EXISTS
CHECK FOR ANY DATA LOSS