We are going to have a session on recovery about corrupted/damaged/lost/canceled NONSYSTEM datafile (except system & sysaux).  Before proceeding to be sure to have a complete backup of your test database and be sure your database is in ARCHIVELOG mode.

And it’s in ARCHIVELOG mode

Connecting through RMAN I can receive information about my datafiles

I’m of course able to query the dictionary tables and see in which datafiles some tables are located.

What does it happens when I delete the datafile where USERS tablespace is based on.

I’m still able to query the dictionary tables for example but…

I obtain an error when I try to select some rows from the HR.EMPLOYEES table.

Looking at the log file, there’s the same clear error: the system is not able to obtain status information on file ‘/home/oracle/app/oracle/oradata/orcl/users01.dbf‘.

If you try to connect with RMAN the REPORT SCHEMA command is now unable to correctly size the USERS tablespace. RMAN says its size is 0.

Because the USERS tablespace is not a system tablespace, we can recover it just putting it in offline mode.

In the alert log you can see how the recovery process proceedes

Just note how long the restore/recover process lasted: it began at Thu Jul 19 07:30:01 2017 to end at Thu Jul 19 07:31:35 2017 for a TOTAL TIME of 1:34 (one minute and 34 seconds). I’ll compare this result with another restore/recover approach in the next recovery scenario. Now I’m able to query again my hr.employees table

and even RMAN is able to size correctly the USERS tablespace


Leave a Reply