How to verify a restored backup

The scenario is this one: we need to clone an instance to another machine and different directories, and we need to verify that all the datafiles are copied anywhere in the destination machine, in order to avoid the invalidation when we recover de instance with missing datafiles.

The solution showed here is based in:

1) Having a listing with all the datafiles in the destination machine, showing the filename in a single line.
2) Create an external table in the source instance to the datafiles listing.
3) Issue a select command to find the datafiles not still copied.

1) Listing with all the datafiles

I do that simply this way:

dir G:\datafiles > DF.txt
dir H:\datafiles >> DF.txt
...and so on

Next I edit the txt and delete the left colums before the file name, and that's enough. We don't need to delete extra lines. We can avoid this action complicating the select, but I find it's quicker this way.

2) Create an external table

First I copy the datafile listing to some directory in the source machine, E:\BBDD\ in this example.

Next I declare an oracle directory in order to read the datafile listing from inside the source database, and declare the external table.

create directory toni as 'E:\BBDD\';

create table df_kk
(texto varchar2 (120 char) null)
organization external(
type oracle_loader
default directory toni
access parameters (records delimited by newline)
location (toni:'DF.txt')
)
reject limit 1000;


3) Verify that all the source files have been copied.

For every datafile in dba_data_files I look for it in the external table.

col bytes format 999,999,999,999,999
select distinct substr(file_name,instr(file_name,'\',-1)+1,length(file_name)) DF, substr(file_name, 1,3), bytes
from dba_data_files, df_kk
where substr(file_name,instr(file_name,'\',-1)+1,length(file_name)) not in (select texto from df_kk where texto like '%'||substr(file_name,instr(file_name,'\',-1)+1,length(file_name))||'%')
order by 1;


That's all.