Oracle Database Crash During Hot Backup

November 12, 2016 by S4

Filed under Oracle

Last modified November 12, 2016

Oracle Database Crash During Hot Backup

There can be many reasons for the database to crash during a hot backup—a power outage or rebooting of the server, for example. If these were to happen during a hot backup, chances are that tablespace would be left in backup mode. In that case you must manually recover the files involved, and the recovery operation would end the backup of tablespace. It’s important to check the status of the files as soon as you restart the instance and end the backup for the tablespace if it’s in backup mode.
Sql>select,b.status from v$datafile a, v$backup b
where a.file#=b.file# and b.status=’ACTIVE’;
sql>select a.tablespace_name,a.file_name,b.status from dba_data_files a, v$backup b where a.file_id=b.file# and b.status=’ACTIVE’;
This statement lists files with ACTIVE status. If the file is in ACTIVE state, the corresponding tablespace is in backup mode. The second statement gives the tablespace name also, but this can’t be used unless the database is open. You need to end the backup mode of the tablespace with the following command:
Sql>alter tablespace tablespace_name end backup;

@ nomount stage,

Sql>select * from v$backup;
Sql>alter database datafile 1,13 end backup;
Sql>recover database;
Sql>alter database open;


1. If you use RMAN for the hot backup, you don’t need to do the ‘begin/end backup’ stuff anymore.
2. Create a trigger to check the file status and changes the status to end backup mode.

create trigger t1 after startup on database IS
cursor c_list is
select file# from v$backup_datafile;
for c_r in c_list loop
execute immediate ‘alter database datafile ‘|| c_r.file#||’ end backup;’;
end LOOP;

Leave a Comment