How to restore datafiles while Oracle Database is running

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How do we collect the actual execution plan from queries using Extended Event and how do we read its data - 2 December 2024
• How do we find what permissions a user has on an Oracle database - 1 November 2024
• How we enable Unified Auditing in Oracle Database - 7 October 2024

In this article we will see how in Oracle Database we can restore a datafile that has been deleted or has become corrupt while the database remains "open" and continues its operation. The data to carry out the process comes from the RMAN backups.

The prerequisites

The database should be located at Archive Log Mode, if it is not found, connect from a terminal with the oracle user (with which the Oracle Database has been installed):

sqlplus / as sysdba

To make the change you will need to restart the database by running the following:

shutdown immediate;

alter database archivelog mode;

startup;

We should also be application aware RMAN database backups and keep them Archive Logs.

We connect to RMAN:

rman target /

To get the backup of the database we run the following:

backup database;

Alternatively, we can backup the archive logs at the same time as the database, but this is not required for the example:

backup database plus archivelog;

The example

For example we have created a tablespace consisting of two datafiles:

CREATE TABLESPACE corrupt_ts 
   DATAFILE '/oracle/app/oracle/oradata/ORACLEDB/datafile/corrupt_ts_data1.dbf'size 10m autoextend on next 64m maxsize 32767m,
   '/oracle/app/oracle/oradata/ORACLEDB/datafile/corrupt_ts_data2.dbf'size 10m autoextend on next 64m maxsize 32767m;

We can see which datafiles the tablespace consists of and what file_id it has with the following query:

SELECT 
   tablespace_name, 
   file_id,
   file_name, 
   bytes / 1024/ 1024  MB,
   online_status
FROM
   dba_data_files
WHERE TABLESPACE_NAME='CORRUPT_TS';

We create a table under a user that will belong to this tablespace, but first we give the user permission to write to the tablespace:

ALTER USER stratos quota unlimited on CORRUPT_TS;

CREATE TABLE stratos.users(
   id INT GENERATED ALWAYS AS IDENTITY, 
   username VARCHAR2(50)
) TABLESPACE corrupt_ts;


insert into stratos.users (username) values ('Stratos');
insert into stratos.users (username) values ('Giorgos');
insert into stratos.users (username) values ('Nikos');
insert into stratos.users (username) values ('Vasilis');
commit;

We check to see that the table contains the entries:

select * from stratos.users;

To be sure for example that the information of the table has been written to the datafiles, we restart the database:

shutdown immediate;
startup;

Alternatively we checkpoint the database:

alter system checkpoint;

Now that we have done all of the above, we go to the location where one of the tablespace datafiles containing the table is located and delete it:

If we go to read the table again we will get the following message:

select * from stratos.users;

How do we fix the problem?

For starters, we turn the tablespace to offline:

sqlplus / as sysdba

alter tablespace corrupt_ts offline immediate;
exit;

We connect to RMAN:

rman target /

And we do restore the number of the datafile that showed a problem:

restore datafile 5;

Then we do recover the datafile so that the logs are applied and we reach the datafile at the most recent point in time before deleting it:

recovery datafile 5;

exit;

When the process is complete, we return the tablespace to online:

sqlplus / as sysdba

alter tablespace corrupt_ts online;

Now if we select the table we will again see the entries of the table without problem:

select * from stratos.users;

How do we restore all the datafiles that make up the tablespace

We return as before the tablespace to offline and we connect to RMAN:

sqlplus / as sysdba

alter tablespace corrupt_ts offline immediate;
exit;

rman target /

By connecting to RMAN we do restore all the datafiles that make up the tablespace with the following command:

restore tablespace corrupt_ts;

Then we do recover the datafiles of the tablespace so that the logs are applied and we reach the datafile at the most recent point in time before deleting it:

recover tablespace corrupt_ts;
exit;

We return the tablespace to online to complete the process and make the tablespace accessible:

sqlplus / as sysdba

alter tablespace corrupt_ts online;

Sources:

• RMAN RESTORE: Restoring Lost Database Files from Backup
• Getting Started with RMAN
• Changing the Database Archiving Mode