This note is aimed at saving time on manual calculation of missing files that need to be restored and avoiding the need to restart the restore process from scratch.

The scenario involves initiating a database restore using RMAN (RESTORE DATABASE), and after some time, encountering an error:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

ORA-19870: error while restoring backup piece 856802_vg1qa4qf_1_1

ORA-19504: failed to create file "+DATA"

ORA-17502: ksfdcre:4 Failed to create file +DATA

ORA-15041: diskgroup "DATA" space exhausted

And basically… after resolving the ORA-15041 issue, we have two options: either start the restore process from scratch (over 40TB), or manually determine what has already been restored (around 1800 files) and only recover the necessary ones (~120 files), constructing a new RMAN script based on the information mentioned above.

However, there is a script available that can be used:

Option 1:

select q'!set newname for DATAFILE !'||datafiled||q'! to '+ASMDG';!' from (
select 
dcopy.file# fcopy,d.file# datafiled 
from 
(
select file# from v$datafile_copy c where c.name is not null
order by c.file#) dcopy right join v$datafile d on dcopy.file#=d.file#)
where fcopy is null
union all
select q'!restore datafile !'||dfiled||q'!;!' from (select 
dcopy.file# fcopy,d.file# dfiled 
from 
(
select file# from v$datafile_copy c where c.name is not null
order by c.file#) dcopy right join v$datafile d on dcopy.file#=d.file#)
where fcopy is null

Option 2(In the last line of the output result, you need to replace the comma with a semicolon):

select q'!set newname for DATAFILE !'||datafiled||q'! to '+ASMDG';!' from (
select 
dcopy.file# fcopy,d.file# datafiled 
from 
(
select file# from v$datafile c where c.bytes=0 
order by c.file#) dcopy left join v$datafile d on dcopy.file#=d.file#)
union all
select q'!restore datafile !' from dual
union all
select dfiled||q'!,!' from (select 
dcopy.file# fcopy,d.file# dfiled 
from 
(
select file# from v$datafile c where c.bytes=0
order by c.file#) dcopy left join v$datafile d on dcopy.file#=d.file#)

Both scripts generate «set newname for DATAFILE» and «restore datafile» commands for each data file that needs to be restored. We wrap the script’s output in a «run» block and execute it. After all the files are restored, we perform a «switch database to copy» and continue with the recovery process.

However, it should be noted that under certain circumstances, the script may not work correctly(Especially option 1). For example, on a normally functioning database in open mode, the script’s output may not match reality because there may be no entries in v$datafile_copy, or conversely, there may be many entries in v$datafile_copy, and both scenarios are normal.

Therefore, to avoid causing any issues with the database, please proceed with caution.