Recover a datafile from copy

Let’s recover if some datafile is unavailable.

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

[oracle@node214 DB11G]$ pwd
/u01/app/oracle/oradata/DB11G
[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.

SQL>

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.

SQL>

Restore datafile:

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

Recover datafile:

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

SQL>

Checking:

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

Table created.

SQL>

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