I am going through some testing of possible scenarios of recovery. I cam across a blog where if I had lost my control files and the backup of the control files I could a script like below and restore the datafiles manually.
set serveroutput on
set verify off
declare
v_dev varchar2(30) ;
v_rest_ok boolean;
v_df_num number := 1;
v_df_max number := 30;
v_bck_piece varchar2(256) := '&1';
v_rest_folder varchar2(226) := '+DATA/TESTDB/';
v_rest_df varchar2(256);
begin
v_dev := dbms_backup_restore.deviceallocate;
dbms_output.put_line(' ');
dbms_output.put_line(' Allocating device.... ');
dbms_output.put_line(' Specifying datafiles... ');
while v_df_num <= v_df_max loop
v_rest_df := v_rest_folder||'DF_'||lpad(v_df_num,4,'0');
dbms_backup_restore.restoreSetDatafile;
dbms_backup_restore.restoreDataFileTo(dfnumber=>v_df_num,toname=>v_rest_df);
dbms_output.put_line(' Restoring ... ');
BEGIN
dbms_backup_restore.restoreBackupPiece(done=>v_rest_ok,handle=>v_bck_piece);
EXCEPTION
WHEN OTHERS
THEN
v_rest_ok := FALSE;
-- dbms_output.put_line('Datafile '||v_df_num||' is not in this piece');
END;
if v_rest_ok THEN
dbms_output.put_line('Datafile '||v_df_num||' is restored : '||v_rest_df);
end if;
v_df_num := v_df_num + 1;
end loop;
dbms_backup_restore.deviceDeallocate;
end;
/
exit;
Well it all works but it sure takes a long time and I am not sure if it is the time it takes to pull the file from our NFS mount or what. I looked in the alert log and i get this.
2024-08-15T12:50:15.264407-05:00
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
2024-08-15T13:21:52.734438-05:00
Full restore complete of datafile 11 to datafile copy +DATAC1/newtestdb/df_0011. Elapsed time: 0:31:36
checkpoint is 225713845168
last deallocation scn is 225714103382
Undo Optimization current scn is 225702551174
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
Restore from backup piece /backup/testdb_9u2vr329_43326_1_2
2024-08-15T15:46:18.162207-05:00
Full restore complete of datafile 16 to datafile copy +DATAC1/newtestdb/df_0016. Elapsed time: 0:41:59
checkpoint is 225713845168
last deallocation scn is 225701707141
notice the elapsed time for datafile 16 was 41 minutes but the time between datafile 11 and datafile 16, which is the next datafile to recover from this backup file is over 2 hours. Is this normal or is it due to the fact it pulls the backup file from my NFS share.