Let’s recover if some datafile is unavailable.

Let’s delete users01.dbf datafile which is in USERS tablespace.

[oracle@node214 DB11G]$ pwd
[oracle@node214 DB11G]$
[oracle@node214 DB11G]$ mv users01.dbf users01.dbf.bak #as backup file.
[oracle@node214 DB11G]$

Let’s try to create a table name A in tablespace Users.

SQL> create table a(id number) tablespace USERS
ERROR at line 1:

SQL> select file#,name from v$datafile;
      FILE#  NAME
---------- ------------------------------------------------------------
	 1 /u01/app/oracle/oradata/DB11G/system01.dbf
	 2 /u01/app/oracle/oradata/DB11G/sysaux01.dbf
	 3 /u01/app/oracle/oradata/DB11G/undotbs01.dbf
	 4 /u01/app/oracle/oradata/DB11G/users01.dbf
	 5 /u01/app/oracle/oradata/DB11G/example01.dbf
	 6 /u01/app/oracle/oradata/DB11G/test_assm01.dbf
	 7 /u01/app/oracle/oradata/DB11G/test_mssm01.dbf
	 8 /stripe/d1/system02.dbf

8 rows selected.


We need to restore and recover the datafile. Restore means copy from backup, recover means applying changes since the backup is taken.

SQL> alter database datafile 4 offline;
Database altered.


Restore datafile:

[oracle@node214 DB11G]$ mv users01.dbf.bak users01.dbf
[oracle@node214 DB11G]$

Recover datafile:

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.



SQL> create table a(id number) tablespace USERS;

Table created.


Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.